SQL 索引解釋，Pt。 1

如果使用得當，SQL 數據庫索引會非常有效，以至於看起來像是魔術。 但是下面的一系列練習將表明，大多數 SQL 索引的邏輯——以及正確使用它們——是非常簡單的。

在本系列SQL Indexes Explained中，我們將介紹使用索引來訪問數據以及以所有現代 RDBMS 的方式設計索引的動機。 然後，我們將查看用於返回特定查詢模式的數據的算法。

您不必對索引有太多了解就能夠遵循SQL Indexes Explained 。 只有兩個前提：

• SQL 基礎知識
• 任何編程語言的基本知識

SQL 索引解釋將涉及的主要主題是：

• 為什麼我們需要 SQL 數據庫索引； 使用索引可視化執行計劃
• 索引設計：哪些索引使查詢快速高效
• 我們如何編寫查詢以有效地使用索引
• SQL中使用索引對讀寫效率的影響
• 覆蓋索引
• 分區，它對讀寫的影響，以及何時使用它

這不僅僅是一個 SQL 索引教程——它深入了解了索引的底層機制。

我們將通過練習和分析我們解決問題的方法來弄清楚 RDBMS 如何使用索引。 我們的練習材料包含只讀的 Google 表格。 要進行練習，您可以復制 Google 表格（文件 → 製作副本）或將其內容複製到您自己的 Google 表格中。

在每個練習中，我們將展示一個使用 Oracle 語法的 SQL 查詢。 對於日期，我們將使用 ISO 8601 格式YYYY-MM-DD 。

練習 1：客戶的所有預訂

第一個任務——暫時不要這樣做——是從 Reservation 電子表格中為酒店預訂系統的特定客戶查找所有行，並將它們複製到您自己的電子表格中，模擬以下查詢的執行：

 SELECT * FROM Reservations WHERE ClientID = 12;

但是我們想遵循一種特定的方法。

方法一：不排序，不過濾

第一次嘗試時，不要使用任何排序或過濾功能。 請記錄所花費的時間。 生成的工作表應包含 73 行。

此偽代碼說明了無需排序即可完成任務的算法：

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

在這種情況下，我們必須檢查所有 841 行以返回並複制滿足條件的 73 行。

方法 2：僅排序

對於第二次嘗試，根據ClientID列的值對工作表進行排序。 不要使用過濾器。 記錄時間，並與不排序數據完成任務所花費的時間進行比較。

排序後，方法如下所示：

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

這一次，我們必須“僅”檢查 780 行。 如果我們能以某種方式跳到第一行，那將花費更少的時間。

但是如果我們必須為這個任務開發一個程序，這個解決方案會比第一個解決方案還要慢。 那是因為我們必須首先對所有數據進行排序，這意味著每一行都必須至少訪問一次。 僅當工作表已按所需順序排序時，此方法才有效。

練習 2：從給定日期開始的預訂數量

現在的任務是統計 2020 年 8 月 16 日的簽到人數：

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

使用練習 1 中的電子表格。測量和比較完成任務所花費的時間（有和沒有排序）。 正確的計數是 91。

對於沒有排序的方法，算法與練習 1 中的算法基本相同。

排序方法也類似於上一個練習中的方法。 我們將把循環分成兩部分：

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

練習 3：刑事調查

警察檢查員要求查看 2020 年 8 月 13 日至 14 日抵達酒店的客人名單。

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

方法 1：僅按日期排序

檢查員希望快速列出清單。 我們已經知道我們最好根據到達日期對錶格/電子表格進行排序。 如果我們剛剛完成練習 2，我們很幸運，表格已經排序。 因此，我們應用類似於練習 2 中的方法。

請嘗試記錄時間，您必須閱讀的行數以及列表中的項目數。

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

使用這種方法，我們必須讀取 511 行來編譯 46 位客人的列表。 如果我們能夠精確地向下滑動，我們實際上不必從重複循環中執行 324 次讀取來定位 8 月 13 日的第一個到達。 但是，我們仍然需要讀取 100 多行來檢查客人是否以3的HotelID到達酒店。

檢查員等了那麼久，但並不高興：我們只提供了一個無意義的 ID 列表，而不是客人的姓名和其他相關數據。

我們將在本系列的稍後部分回到這個方面。 讓我們首先找到一種更快地準備列表的方法。

方法二：先按酒店，再按日期

要根據HotelID然後DateFrom對行進行排序，我們可以選擇所有列，然後使用 Google 表格菜單選項Data → Sort range 。

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

在將第一個到達我們酒店之前，我們不得不跳過前 338 個到達。 在那之後，我們在 8 月 13 日找到了 103 名較早到達的人。 最後，我們複製了ClientID的 46 個連續值。 它幫助我們在第三步中復制了一個連續的 ID 塊。 太糟糕了，我們不能以某種方式從那個街區跳到第一行。

方法3：僅按酒店排序

現在使用僅按HotelID排序的電子表格嘗試相同的練習。

應用於僅按HotelID排序的表的算法效率低於我們按HotelID和DateFrom （按該順序）排序時的效率：

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

在這種情況下，我們必須讀取HotelID為3的所有 166 次到達酒店，並檢查每一次的DateFrom屬於請求的時間間隔。

方法 4：按日期排序，然後按酒店排序

我們是HotelID再按DateFrom排序，反之亦然，這真的很重要嗎？ 讓我們找出答案：嘗試DateFrom排序，然後按HotelID 。

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

我們找到了第一行的相關日期，然後閱讀更多內容，直到我們找到第一個到達酒店的人。 之後，對於許多行，兩個條件都得到了滿足，即正確的日期和正確的酒店。 但是，在到達 3 號酒店後，我們在同一天到達了 4 號、5 號等酒店。 在他們之後，我們不得不再次讀取第二天的酒店 1 和 2 的行，直到我們能夠讀取連續到達我們感興趣的酒店的信息。

正如我們所看到的，所有方法在完整的行集中間都有一個連續的數據塊，代表部分匹配的數據。 方法 2 和 4 是唯一允許我們在部分匹配結束之前完全停止算法的方法。

方法 4 在兩個塊中具有完全匹配的數據，但方法 2 是唯一一種目標數據都在一個連續塊中的方法。

從數字上看，很明顯方法 2 在這種情況下具有最大的優勢。

SQL 索引解釋：結論和下一步

做這些練習應該使以下幾點變得清晰：

1. 從正確排序的表中讀取更快。
2. 如果一個表還沒有排序，排序比從一個未排序的表中讀取要花費更多的時間。
3. 找到一種方法來跳轉到與排序表中的搜索條件匹配的第一行將節省大量讀取。
4. 提前對錶格進行排序會很有幫助。
5. 為最頻繁的查詢維護表的排序副本會很有幫助。

現在，表的排序副本聽起來幾乎就像數據庫索引。 SQL Indexes Explained中的下一篇文章介紹了基本的索引實現。 謝謝閱讀！