mysql聚集索引、輔助索引、覆蓋索引、聯合索引的使用

目錄

• 聚集索引（Clustered Index）
• 輔助索引(Secondary Index)
• 覆蓋索引（Covering index）
• 聯合索引

《MySQL技術內幕 InnoDB存儲引擎》學習筆記

聚集索引（Clustered Index）

聚集索引就是按照每張表的主鍵構造一棵B+樹，同時葉子節點中存放的即為整張表的行記錄數據。

舉個例子，直觀感受下聚集索引。

創建表t，并以人為的方式讓每個頁只能存放兩個行記錄（不清楚怎么人為控制每頁只存放兩個行記錄）：

最后《MySQL技術內幕》的作者通過分析工具得到這棵聚集索引樹的大致構造如下：

聚集索引的葉子節點稱為數據頁，每個數據頁通過一個雙向鏈表來進行鏈接，而且數據頁按照主鍵的順序進行排列。

如圖所示，每個數據頁上存放的是完整的行記錄，而在非數據頁的索引頁中，存放的僅僅是鍵值及指向數據頁的偏移量，而不是一個完整的行記錄。

如果定義了主鍵，InnoDB會自動使用主鍵來創建聚集索引。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替主鍵。如果沒有唯一的非空索引，InnoDB會隱式定義一個主鍵來作為聚集索引。

輔助索引(Secondary Index)

輔助索引，也叫非聚集索引。和聚集索引相比，葉子節點中并不包含行記錄的全部數據。葉子節點除了包含鍵值以外，每個葉子節點的索引行還包含了一個書簽（bookmark），該書簽用來告訴InnoDB哪里可以找到與索引相對應的行數據。

還是以《MySQL技術內幕》中的例子，來直觀感受下輔助索引的模樣。

還是以上面的表t為例，在列c上創建非聚集索引：

然后作者通過分析工作得到輔助索引和聚集索引的關系圖：

可以看到輔助索引idx_c的葉子節點中包含了列c的值和主鍵的值。

以Key為7fffffff為例，7是0111,0代表負數，真實的值應該取反加1，是-1，這是列c的值。Pointer是80000001,8是1000，1代表正數，所以80000001代表1，是主鍵的值。

覆蓋索引（Covering index）

InnoDB存儲引擎支持覆蓋索引，即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。

使用覆蓋索引有啥好處？

• 可以減少大量的IO操作

上圖中我們知道，如果要查詢輔助索引中不含有的字段，得先遍歷輔助索引，再遍歷聚集索引，而如果要查詢的字段值在輔助索引上就有，就不用再查聚集索引了，這顯然會減少IO操作。

比如上圖中，以下sql可以直接使用輔助索引，

• 有助于統計

假設存在如下表：

如果在該表上執行：

優化器會怎么處理？

遍歷聚集索引和輔助索引都可以統計出結果，但輔助索引要遠小于聚集索引，所以優化器會選擇輔助索引來統計。執行explain命令：

key和Extra顯示使用了idx_name這個輔助索引。

還有，假設執行以下sql：

因為聯合索引idx_school_age的字段順序是先school再age，按照age做條件查詢，通常不走索引：

但是，如果保持條件不變，查詢所有字段改為查詢條目數：

優化器會選擇這個聯合索引：

聯合索引

聯合索引是指對表上的多個列進行索引。

以下為創建聯合索引idx_a_b的示例：

聯合索引的內部結構：

聯合索引也是一棵B+樹，其鍵值數量大于等于2。鍵值都是排序的，通過葉子節點可以邏輯上順序的讀出所有數據。數據（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）是按照（a，b）先比較a再比較b的順序排列。

基于上面的結構，對于以下查詢顯然是可以使用（a，b）這個聯合索引的：

但是對于下面的sql是不能使用這個聯合索引的，因為葉子節點的b值，1,2,1,4,1,2顯然不是排序的。

聯合索引的第二個好處是對第二個鍵值已經做了排序。舉個例子：

當執行

時，優化器會選擇key(userid)；但是當執行以下sql：

時，優化器會選擇key(userid, buy_date)，因為buy_date是在userid排序的基礎上做的排序。

如果把key（userid,buy_date）刪除掉，再執行：

優化器會選擇key（userid），但是對查詢出來的結果會進行一次filesort，即按照buy_date重新排下序。所以聯合索引的好處在于可以避免filesort排序。