使用 SQL 索引和分區解決瓶頸

在 SQL 索引解釋的第一課中，我們了解到當數據已經按特定列的值排序時， SELECT查詢會更快。

在第二課中，我們學習了 B 樹索引的基本結構以及如何使用它們來減少執行查詢時訪問的數據量。 我們還弄清楚瞭如何實現連接多個表的查詢，以及索引如何加速此類查詢。

我們還強調了在 SQL 中使用索引很有幫助的兩個場景。 當索引覆蓋索引時，包含查詢中的所有列——來自WHERE條件、 JOIN條件和SELECT列表——我們避免完全讀取相應的表。 或者，當索引將訪問的數據塊數量減少到表大小的一小部分時，它們會有所幫助。

否則，掃描整個表比從索引中讀取並隨機地來回跳轉到相應的表行更有效。

SQL 範圍查詢

可以利用索引的查詢通常包括顯著減少一個或多個列可以採用的可能值範圍的條件。 範圍查詢根據“A 列的值必須在 X 和 Y 之間”之類的條件限制數據。

一個很好的例子是第二課練習 4 中的查詢：

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND r.HotelID = 3;

這裡我們有兩個範圍。 第一個是日期範圍，即 2020 年 8 月 13 日到 2020 年 8 月 14 日之間的時間段。第二個是可能的最小數字範圍。 該條件相當於r.HotelID BETWEEN 3 AND 3 。

練習 1：期間（日期和時間範圍查詢）

讓我們在Reservations表中添加一個名為CheckInTime的列。 您可以在此電子表格中查看示例數據。 請注意，有一個索引同時涵蓋CheckInTime和ClientId 。

編寫一個查詢，返回 2020 年 8 月 15 日簽到的客戶的姓名。

沒有經驗的 SQL 開發人員通常會編寫以下查詢：

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE TO_DATE(r.CheckInTime, 'YYYY-MM-DD') = '2020-08-15';

他們假設查詢執行如下所示：

 Get first row from IX_CheckInTime_ClientID where TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' While found and TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName Get next row from IX_CheckInTime_ClientID

問題是在撰寫本文時沒有一個 RDBMS 能夠生成這樣的執行計劃。 他們將TO_DATE （Oracle 語法）視為將CheckInTime列的值轉換為未索引的值的函數。 因此，他們傾向於生成的執行計劃如下所示：

 For each row from IX_CheckInTime_ClientID If TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' then Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName

執行此操作將比從Reservations表中讀取所有行更快，因為索引行比表行窄。 較小的行意味著需要從磁盤訪問的塊更少。

但是，我們知道第一個執行計劃會更有效率。 為了說服我們的 RDBMS 使用這種方法，我們需要重寫查詢：

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.CheckInTime >= TO_DATE('2020-08-15 00:00:00', 'YYYY-MM-DD HH:MI:SS') AND r.CheckInTime < TO_DATE('2020-08-16 00:00:00', 'YYYY-MM-DD HH:MI:SS');

這是一個合適的範圍查詢，每個優秀的 RDBMS 都能理解。 我們的 RDBMS 發現我們想要來自Reservations表的數據，其中CheckInTime的值（而不是從它派生的東西）屬於明確定義的範圍。 它生成的執行計劃更像是：

 Get first row from IX_CheckInTime_ClientID where CheckInTime >= '2020-08-15 00:00:00' While found and CheckInTime < '2020-08-16 00:00:00' Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName Get next row from IX_CheckInTime_ClientID

這就是我們真正想要的：不僅利用索引本身，而且利用它已排序的事實。

練習 2： LIKE在開頭使用通配符

這次我們的偵探帶著關於嫌疑人的模糊信息來到酒店：只是姓氏以“-son”結尾。 偵探想要所有這些客人的名字和姓氏：

 SELECT FirstName, LastName FROM Clients WHERE LastName LIKE '%son';

對於Clients表和LastName上的索引，我們將使用此電子表格。 寫下查詢將返回的結果。 想想你可以應用的不同方法。

表掃描方法

最簡單的策略是讀取表中的所有數據，並在客人的姓氏以“-son”結尾時記下他們的姓名：

 For each row from Clients If LastName like '%son' then write down FirstName, LastName

在這裡，我們必須按順序讀取整個表。

使用索引

讓我們嘗試利用LastName列上的索引。 轉到 IX_LastName 表，使用它找到所有滿足給定標準的客戶，並寫下他們的名字。

事實證明，您必須閱讀整個索引才能從表中找到所有 Andersons、Robinsons 和 Thompsons。 這比使用表掃描更好嗎？ 除了讀取整個索引之外，您還必須使用rowAddress值從表中找到每個匹配條目的相應行，然後從那裡記下FirstName ：

 For each row from IX_LastName If LastName like '%son' then Fetch Clients.* where RowAddress = IX_LastName.RowAddress Write down FirstName, LastName

對我們來說，按順序讀取表格更簡單、更快捷。 對於我們的 RDBMS，它將取決於滿足條件的行的百分比。 如果一個大表中只有少數幾個 Andersons、Robinsons 和 Thompsons，即使在找到匹配項時它必須從表中讀取幾個塊，RDBMS 也會從更窄的索引條目中讀取更少的數據塊。 否則，表掃描將花費更少的時間。

對索引中的數據進行排序不會對此類查詢提供任何幫助。 較小的索引行可能會有所幫助——但只是有時。

練習 3： LIKE結尾帶有通配符

下次我們的偵探來的時候，我們需要找到所有姓氏以“Rob-”開頭的客戶。

 SELECT FirstName, LastName FROM Clients WHERE LastName LIKE 'Rob%';

嘗試從同一個電子表格中提取與查詢匹配的數據。

如果您使用表掃描方法，您將錯過充分利用索引IX_LastName的機會。 從“Rob-”（羅伯茨）開始的索引中定位第一個條目，讀取後續行（羅伯茨和羅賓遜），並在LastName不再符合條件時停止，要快得多：

 Get first row from IX_LastName where LastName <= 'Rob' While found and LastName < 'Roc' Fetch Clients.* where rowAddress = IX_LastName.rowAddress Write down FirstName, LastName Get next from IX_LastName

在這種情況下，在 B 樹查找第一個條目之後，我們只讀取滿足條件的條目。 一旦我們讀到一個不符合標準的名字，我們就會停止閱讀。

解決 B-tree 擴展問題

通常，當我們部署一個新數據庫時，會有一些填充的查找表和空的事務表。 系統從一開始就運行平穩，特別是如果我們通過規範化表來尊重數據庫設計的良好實踐； 創建主鍵、外鍵和唯一鍵； 並支持具有相應索引的外鍵。

幾個月或幾年後，當數據量顯著增加系統和數據庫的複雜性時，我們開始注意到性能下降。 出現了關於為什麼系統一直在變慢以及如何處理它的意見。

流行的觀點通常認為數據庫的大小是罪魁禍首。 解決方案似乎是刪除我們每天不需要的歷史數據，並將其放在單獨的數據庫中進行報告和分析。

讓我們首先探討主要假設。

SQL 範圍查詢：執行時間是否取決於表大小？

考慮來自單個表的典型範圍查詢：

 SELECT Column1, …, ColumnN FROM Table WHERE Column BETWEEN X AND Y;

假設Column上有一個索引，那麼最優的執行計劃是：

 Get first row from IX_Column where Column between X and Y While found and Column <= Y Fetch Table.* where rowAddress = IX_Column.rowAddress Write down Column1, …, ColumnN Get next row from IX_Column

讓我們計算一下 RDBMS 必須讀取哪些塊才能返回此數據。

Get first row部分由我們在第二課中介紹的 B-tree 查找實現。 它必須讀取的塊數等於 B 樹的深度。 之後，我們從索引的葉級讀取後續項。

對於 OLTP 查詢，通常所有結果都將在一個索引塊中找到（有時兩個，但很少更多）。 除此之外，對於每個索引條目，我們都可以訪問表中的一個塊，以根據其地址找到相應的行。 一些表行可能在我們已經加載的同一個表塊中，但為了簡化估計，假設我們每次都加載一個新塊。

所以公式是：

B = D + 1 + R

B 是讀取的總塊數，D 是 B 樹深度，R 是查詢返回的行數。

唯一取決於表中行數的參數是 D，即 B 樹深度。

為了簡化計算並說明問題，假設 1,000 個索引條目適合一個塊。 只要表中的行數少於 1,000，D = 1。 對於保存業務事務的表，可能是系統部署後的第一個工作日。 很快，B-tree 的深度就會增加。 只要表中的行數少於 100 萬，索引將由兩個級別組成。

如果我們被緩慢的數據庫響應時間所困擾並將其歸咎於數據量，請注意事務表通常只有數百萬行。 由於只有 100 萬行適合兩級 B 樹索引，因此深度必須至少為 3。 除非表中的行數超過 10 億，否則深度不會增長到四。 現在我們有一個更精確的估計：

B = 4 + R

如果 R 很小，將 B-tree 深度降低到 2 會顯著加快查詢速度。 當我們通過主鍵值或唯一鍵值進行搜索時，系統將讀取四個塊而不是五個，這是 20% 的改進。 如果查詢返回更多行，則改進可能不明顯。 問題是，對於許多應用程序，我們可能無法通過在數據庫中保留少於 100 萬個事務來支持必要的業務操作。

所以結論似乎是表大小無關緊要； 換句話說，移動歷史數據是浪費時間和資源。

但不是那麼快：讓我們更多地了解 B 樹索引的結構以及數據更改如何影響它。

B-tree 索引實現細節

在第二課中我們對 B 樹索引的介紹中，我們看到平衡樹的所有級別（物理上）都按鍵列值排序。 但是，當我們想要插入、更新或刪除一個項目時，通常我們必須移動大量數據來保持順序。

假設我們正在插入一個恰好已滿的塊的中間。 我們必須拆分塊，重新排列數據，有時甚至更新指向當前 B-tree 級別的數據。

為了使這種情況更有效，每個索引項都包含指向前一行和下一行的指針，使其成為雙鏈接。 對於一般的插入，這意味著我們只需將新項目寫入盡可能接近前一個項目並更正指針。

當我們也需要拆分一個塊時，我們必須在之前的 B-tree 級別中寫入一個新項。 這只是更正幾個指針的問題——不需要重寫樹的大部分。 拆分後，兩個數據塊都大約是半滿的。 根據磁盤上的可用空間位置，“相鄰”塊在物理上可能相距甚遠。

一段時間後，索引碎片增加，查詢執行速度變慢變得明顯。 隨著 RDBMS 以我們描述的方式執行查詢，項目的順序和接近度的假設變得越來越不正確，導致讀取次數更多。 在最壞的情況下，所有數據塊都是半空的，系統必須讀取兩倍的數據塊。

B樹索引維護

解決這個問題的方法是索引碎片整理（或“重新索引”）。 每個 RDBMS 都提供了重新創建整個索引的功能； 重新索引後，索引再次物理排序。

重新索引是一個非常快的操作，即使它讀取和寫入大量數據。 現代 RDBMS 通常提供兩種重新索引模式，其中更快的一種需要在處理期間鎖定表。 無論哪種方式，最好在非高峰時間重新索引。 否則，處理可能會降低數據庫性能。

刪除歷史數據

當我們有數十億甚至數億行的表時，在非高峰時間完成重新索引操作可能不可行。

為避免這種情況，將歷史數據移出 OLTP 數據庫可能是一種解決方案。 但是，如果我們只是刪除早於特定閾值的行，我們會使索引更加碎片化，並且我們需要更頻繁地重新索引它們。

SQL 分區救援？

有一種方法可以避免由於刪除歷史數據而導致的碎片，在生產數據庫中只保留“活動”事務。 所有主要 RDBMS 實現的想法是將表拆分為更小的塊（稱為分區）並提供添​​加、刪除它們甚至在表之間切換它們的能力（例如，從活動表到具有相同歷史記錄的表）結構體）。

讓我們看一下此電子表格中分區的Reservations表。 該表按月拆分，分區名稱映射到日期期間和其他電子表格。 要了解如何對分區表執行查詢，我們將做一些練習。

練習 4：SQL 中的分區查詢

從上面鏈接的電子表格中，嘗試提取以下查詢請求的數據——不使用任何索引：

 SELECT HotelID, ReservationID, ClientID, DateFrom, DateTo FROM Reservations WHERE DateFrom BETWEEN TO_DATE('2021-03-01','YYYY-MM-DD') AND TO_DATE('2021-03-03');

您可能已經想到，您首先要查看分區映射表，找到包含 2021 年 3 月以來預留的分區。然後，您打開相應的分區，順序讀取數據，並過濾掉不滿足的行健康）狀況。

雖然直截了當，但您可能不喜歡在閱讀了這麼多行之後保留這麼少的行。 讀取 March 分區比讀取整個預留表要好，但仍不理想。 索引呢？

全球索引

RDBMS 允許我們創建一個全局索引來覆蓋分區表的所有分區。 但是全局索引和常規索引在下面的工作方式沒有區別：全局索引不支持分區。 因此，使用全局索引的 CRUD 查詢不涉及其表的分區映射。

我們只需要在刪除整個分區時更新分區映射。 然後，我們必須從索引中刪除指向已刪除分區的所有行。 這意味著需要重建整個全局索引。

中斷窗口仍然是必要的，因為在刪除過時的項目之前無法使用索引。 如果我們可以定期刪除分區，限制活動分區的數量，那麼重新索引操作可能適合中斷窗口。 因此，通過縮短維護任務（包括維護全局索引）所需的時間，使用分區確實有助於解決原始問題。

但是，如果我們仍然無法承受停電怎麼辦？

全局分區索引

這種策略解決了這個問題：我們只需像分區表一樣對索引進行分區。 在分區電子表格鏈接到的電子表格中，每個分區都包含它的Reservations表部分和一個名為 IX_DateFrom 的索引表，兩者均由DateFrom分區。

要執行練習 4 中的查詢，RDBMS 將首先查看索引分區映射並確定哪些分區包含範圍內的日期。 （在我們的例子中，它只是一個索引分區。）之後，它將使用 B-tree 查找，循環到葉級別，最後使用相應的行地址訪問表。

當我們從表中刪除一個分區時，從索引中刪除相應的分區就足夠了。 無需停機。

本地索引

全局分區索引的主要缺點是我們必須同時刪除表和相應的索引分區。 讀取和維護索引分區映射本身只需要一點額外成本。

本地索引涉及類似但略有不同的方法。 我們不是對單個全局索引進行分區，而是在每個表分區內創建一個本地索引。 這樣做時，本地索引共享全局分區索引的主要優點——即沒有停機時間——同時避免了它們的缺點。

這似乎是一個完美的解決方案。 但在我們慶祝之前，讓我們調查一些查詢的可能執行計劃。

練習 5：本地分區索引

嘗試再次運行查詢，這次使用DateFrom上的本地分區索引。

你可能使用過這個執行計劃：

 For all partitions where [StartDateFrom, StartDateTo) intersects ['2021-03-01', '2021-03-03'] Get first row from IX_DateFrom where DateFrom between '2021-03-01' and '2021-03-03' While found and DateFrom < '2021-03-04' Fetch Reservations.* where RowAddress = IX_DateFrom.RowAddress Write down HotelID, ReservationID, ClientID, DateFrom, DateTo Get next row from IX_DateFrom

我們很幸運所有日期都屬於一個分區，所以我們只需要遍歷一個本地索引。 如果這段時間是六個月，我們將不得不閱讀六個本地索引。

練習 6：對比

您的任務是再次使用 Reservations 分區圖，這次是列出客戶 124 訪問酒店 1 的時間段：

 SELECT DateFrom, DateTo FROM Reservations WHERE ClientID = 124 AND HotelID = 1;

在這裡，我們可以看到本地索引的主要缺點。 我們必須從Reservations表的每個分區中讀取本地索引表 IX_HotelID_CientID：

 For all partitions Get first row from IX_HotelID_ClientID where ClientID = 124 and HotelID = 1 While found and ClientID = 124 and HotelID = 1 Fetch Reservations.* where RowAddress = IX_HotelID_ClientID.RowAddress Write down DateFrom, DateTo Get next row from IX_HotelID_ClientID

與未分區表相比，此執行顯然會讀取更多塊並花費更多時間。

因此，雖然我們確實找到了在非高峰期保持索引健康的方法，但該策略也使我們的一些查詢變慢了。

如果我們的業務模型允許我們保留少量分區，或者至少最頻繁的查詢包含允許 RDBMS 僅讀取一個或兩個分區的條件，那麼這個解決方案可能就是我們所需要的。 否則，我們最好避免分區並努力改進數據模型、索引和查詢——並增強數據庫服務器。

SQL 中的索引：接下來要學習什麼

這是我們旅程的終點。 在 SQL Indexes Explained 中，我重點介紹了所有現代 RDBMS 通用的索引實現。 我還專注於應用程序開發人員感興趣的主題，而忽略了通常與數據庫管理員相關的主題。 後者可以很好地研究填充因子對索引碎片的影響，但是兩個角色的人可能會發現進一步閱讀以下內容很有用：

• 數據和索引緩存
• 非 B 樹索引結構，例如哈希、GiST、位圖和列存儲索引
• 聚集索引（在 Oracle 中稱為索引組織表）
• 功能指標
• 部分索引

我們討論的分區方法是范圍分區。 它是最常用的分區類型，但還有其他類型，例如散列分區和列表分區。 此外，一些 RDBMS 提供多級分區選項。

最後，SQL 開發人員可以很好地探索圍繞 RDBMS 查詢執行的其他重要主題——首先是查詢解析，然後是基於成本的執行計劃編譯、緩存和重用。

對於我使用過的四個 RDMBS，我推薦以下資源作為後續步驟：

甲骨文

• 優化器概述
• 索引和索引組織表
• 管理索引
• 分區概述
• 分區指南
• 問湯姆

PostgreSQL

• 查詢處理
• PostgreSQL 中的索引
• PostgreSQL 中的索引（官方文檔）
• 緩衝區管理
• 表分區
• 分區指南

微軟 SQL 服務器

• 查詢處理架構
• 索引
• 分區表和索引

MySQL/MariaDB

• 了解查詢執行計劃
• 優化和索引
• 分區 - 基礎知識
• 分區 - 文檔
• MariaDB 文檔：查詢優化和索引