InnoDB學習（七）之索引結構

索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定資訊。可以將資料庫索引和書的目錄進行類比，通過書的目錄我們可以快速查詢到章節位置，如果沒有目錄就只能一頁頁翻書查詢了。

索引資料結構

可以用於提升查詢效率的索引結構很多，常見的有B樹索引、雜湊索引和B+樹索引。接下來我們會對這些索引一一進行介紹，並說明InnoDB為什麼採用B+樹作為索引。

磁碟IO

檔案是儲存在硬碟上面的。當下硬碟的讀取速度十分有限，所以在進行查詢定位某個資料的時候，應該儘可能地減少磁碟I/O次數。

磁碟預讀

由於儲存介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分之一，因此為了提高效率，要儘量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論依據是電腦科學中著名的區域性性原理：當一個資料被用到時，其附近的資料也通常會馬上被使用。程式執行期間所需要的資料通常比較集中。

區域性性原理：CPU訪問儲存器時，無論是存取指令還是存取資料，所訪問的儲存單元都趨於聚集在一個較小的連續區域中。

預讀的長度一般為頁（page）的整倍數。頁是計算機管理儲存器的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁的大小通常為4k），主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續執行。

合理利用磁碟預讀

一般來說，索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。這樣的話，索引查詢過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高几個數量級，所以評價一個資料結構作為索引的優劣最重要的指標就是在查詢過程中磁碟I/O操作次數。換句話說，索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數。

如果我們能合理使用磁碟預讀的特性，使每次磁碟IO讀到的頁中的資料都是有用的，就可以大大提升資料的查詢效率。

B樹索引

B樹可以看作是對二叉查詢樹的一種擴充套件，B樹允許每個節點有M-1個子節點，B樹有以下特點：

1. 根節點至少有兩個子節點；
2. 每個節點包含M-1條資料，節點中的資料安裝索引遞增順序排序；
3. 節點中有最多有M個指標指向下一層節點，這些指標位於節點的多個資料之間，下一層節點的所有資料值大於指標左側的資料，小於指標右側的資料；
4. 每個節點至少包含M/2條資料；

接下來我們用下表示例的使用者資料來構建B樹，如表所示，使用者資料包含姓名、性別、年齡三個欄位，我們把使用者年齡作為資料庫主鍵(假設年齡具有唯一性)，那麼構建出來的B樹的結構如下圖所示。

|||||||||||
|--|--|--|--|--|--|--|--|--|--|--|
|姓名|陳爾|張散|李思|王舞|趙流|孫期|周跋|吳酒|鄭史|
|性別|男|男|女|女|男|男|男|女|男|
|年齡|5|10|20|28|35|56|25|80|90|

![B樹索引]

相比較與常見的二叉樹，B樹的一個節點中存放了更多的資料，這樣做可以有效的減少一次資料查詢過程中的磁碟IO次數：

• 二叉樹每個節點只存放一個資料，節點之間用指標關聯，節點之間的空間是離散的，所以每個節點都對應一次磁碟IO，查詢一次資料的IO次數為O($log_2$N)；
• B樹的節點可以存放M-1個資料，如果這M-1個資料剛好可以放到一個頁中，那麼B樹查詢一次資料的IO次數為O($log_M$N)；

雜湊索引

雜湊索引基於雜湊表實現，只有精確匹配索引所有列的查詢才有效。雜湊表是一種以鍵-值(Key-Value)儲存資料的結構，使用者可以在O(1)時間複雜度內按照Key查詢到對應的Value。

雜湊表通常是一個陣列，資料在陣列中的位置可以按照索引的值安裝雜湊演算法進行計算，如果兩個資料的索引值計算出來的位置相同，那麼通常可以採用鏈地址法解決衝突（其它解決地址衝突的方法還有開放定製法，鏈地址法，公共溢位區法，再雜湊法等）。

如下表資料所示，我們依舊按照使用者的年齡為使用者資料建立索引(假設使用者年齡不會相同)，我們採用的雜湊演算法為 addr=age%10，我們可以建立長度為10的陣列作為雜湊表，按照雜湊函式一一把使用者放入雜湊表，按照使用者年齡查詢使用者時，可以直接計算出使用者所在的位置，從而得到使用者資訊，最終得到的雜湊表以及查詢流程如下圖所示。

姓名	陳爾	張散	李思	王舞	趙流	孫期	周跋	吳酒	鄭史
性別	男	男	女	女	男	男	男	女	男
年齡	5	10	20	28	35	56	25	80	90

雜湊索引有以下優點：

1. 佔用的額外空間小，為資料新建一個雜湊索引需要的額外空間為O(N)，和索引欄位長度無關；
2. 查詢速度極快，雜湊函式合理的情況下，程式可以在O(1)的磁碟IO次數內查詢到資料；

雜湊索引有以下缺點：

1. 無法進行範圍查詢，雜湊過程中已經丟失了索引的順序性；
2. 無法對資料進行排序查詢，比如查詢年齡最大的使用者；
3. 無法使用部分索引查詢，比如字首查詢等；
4. 雜湊函式不合理的情況下，會導致雜湊衝突問題，造成查詢效率變低；

B+樹索引

InnoDB使用的索引的資料結構是B+樹，資料庫表定義中的每一個索引對應一顆B+樹，預設的聚簇索引也是一顆B+樹，B+樹有以下特徵：

1. 所有節點關鍵字是按遞增次序排列，並遵循左小右大原則；
2. 非葉節點的子節點數在1到M之間(下圖中M為3)，空樹除外；
3. 非葉節點的索引數目大於等於ceil(M/2)個且小於等於M個；
4. 所有葉子節點均在同一層，葉子節點之間有從左到右的指標；
5. 資料儲存在葉子節點，非葉子節點只儲存索引；

接下來我們用幾條示例的使用者資料來構建B+樹，如表所示，使用者資料包含姓名、性別、年齡三個欄位，我們把使用者年齡作為資料庫主鍵(假設年齡具有唯一性)，那麼構建出來的B+樹的結構如下圖所示。

姓名	陳爾	張散	李思	王舞	趙流	孫期	周跋	吳酒	鄭史
性別	男	男	女	女	男	男	男	女	男
年齡	5	10	20	28	35	56	25	80	90

B+樹索引資料結構有以下列出的幾種優勢：

1. 查詢效能穩定，查詢一條資料需要的IO次數往往是樹的高度次；
2. 範圍查詢效率高，安裝索引範圍查詢時，可以先查詢的第一個滿足要求的資料，然後向後遍歷，直到第一個不滿足條件的資料為止，中間的資料都符合要求；
3. 查詢效率高，往往一次資料查詢只需要2~3次磁碟IO；
4. 葉子節點儲存所有資料，不需要去B+樹之外找資料；

InnoDB為什麼採用B+樹

在InnoDB引擎中，我們為資料庫建立的索引都是以B+樹的形式存在，為什麼InnoDB不採用雜湊索引或者B樹索引呢？主要是基於以下原因：

• 資料庫查詢經常會出現非等值查詢，雜湊索引在這種情況下無法工作；
• 相比於B樹，B+樹索引非葉子節點不存放資料，從而磁碟一次IO可以讀取更多的索引資料，有效減少磁碟IO次數；
• 資料庫查詢經常會出現範圍查詢，B+樹底層的葉子節點之間按照順序排列，可以更有效的實現範圍查詢；

自增主鍵

通過上文我們知道，B+樹需要維護索引的有序性。

1. 當使用者向B+樹插入資料，如果插入點對應的節點有空餘位置，那麼只需要挪動節點中的資料，並把需要插入的資料放入B+樹即可；
2. 當使用者向B+樹插入資料，如果插入點對應的節點沒有空餘位置，那麼就需要生成一個新的節點，並把一部分資料挪過去；這種情況不僅會影響插入效率，由於分裂出來的節點只有部分資料，所以會導致空間的利用率降低；
3. 當使用者刪除B+樹中的資料時，如果節點或相鄰節點的資料量很少，那麼只需要直接刪除資料，並按挪動節點中的其它資料即可；
4. 當使用者刪除B+樹中的資料時，如果節點和相鄰節點的資料量很少，那麼在刪除之後，可能需要把節點和相鄰節點合併，從而提高空間利用率；

基於B+樹需要維護索引有序性的特點，我們對索引欄位提出以下建議：

1. 對於資料插入比較多的場景，主鍵索引欄位最好是遞增的。遞增的主鍵每次插入一條新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂。
2. 主鍵索引的長度應當儘量小，主鍵長度越小，普通索引的葉子節點就越小，普通索引佔用的空間也就越小。

在InnoDB中，我們應當儘量使用自增主鍵，自增主鍵有插入效率高、佔用空間小等優勢。

資料空洞與重建索引

資料空洞

當你對InnoDB進行修改操作時，例如刪除一些行，這些行只是被標記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒有真的被釋放回收。InnoDB的Purge執行緒會非同步的來清理這些沒用的索引鍵和行，但是依然沒有把這些釋放出來的空間還給作業系統重新使用，因而會導致頁面中存在很多空洞。如果表結構中包含動態長度欄位，那麼這些空洞甚至可能不能被InnoDB重新用來存新的行，因為空間空間長度不足。

資料空洞帶來的問題：

1. 刪除表中的資料後，表佔用的空間不會變小，造成空間浪費；
2. 會降低資料查詢的速度，因為空洞會佔用頁空間；

我們可以通過以下SQL來檢視資料庫中的空洞大小，執行語句如下所示，返回結果中的DATA_FREE表示表中空閒資料塊的大小。

select data_length,data_free from information_schema.tables where table_schema='test' and table_name='test';

重建索引

當一張表的索引中的資料空洞過多時，會影響SQL語句的執行效率，此時我們就需要清理這些資料空洞。

清理資料空洞比較好的辦法是重建索引，因為重建索引的過程中，會按照索引的大小排序後建立索引，建立出來的索引比較緊湊。

有什麼辦法可以重建索引呢？我們比較直觀的想法肯定是先刪除索引，再重建索引。然而不論是刪除主鍵還是建立主鍵，都會將整個表重建。所以連著執行這兩個語句的話，第一個語句就白做了。

alter table user_info drop primary key;
alter table user_info add primary key(id);

InnoDB中可以通過以下轉換資料引擎的語句來重建表的所有索引。這是因為在轉換資料引擎（即使沒有真正轉換）的過程中，會讀取表中所有的資料，再重新寫入，這個過程中，會釋放空洞。需要注意的是，通過這種方法重建索引耗時比較長。

alter table test engine=innodb

本文最先發布至微信公眾號，版權所有，禁止轉載！