深入理解InnoDB（4）

1. 索引的代價

在了解索引的代價之前，需要再次回顧一下索引的數據結構B+樹

如上圖，是一顆b+樹，關于b+樹的定義可以參見B+樹，這里只說一些重點，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數據項（深藍色所示）和指針（黃色所示），如磁盤塊1包含數據項17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實的數據存在于葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點只不存儲真實的數據，只存儲指引搜索方向的數據項，如17、35并不真實存在于數據表中。

1.1 b+樹的查找過程

如圖所示，如果要查找數據項29，那么首先會把磁盤塊1由磁盤加載到內存，此時發生一次IO，在內存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內存時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內存，發生第三次IO，同時內存中做二分查找找到29，結束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的數據，如果上百萬的數據查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數據項都要發生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。

空間上的代價

從索引的數據結構可以得出，每建立一個索引都要為它建立一棵B+樹，每一棵B+樹的每一個節點都是一個數據頁，一個頁默認會占用16KB的存儲空間，一棵很大的B+樹由許多數據頁組成，那可是很大的一片存儲空間呢。

時間上的代價

每次對表中的數據進行增、刪、改操作時，都需要去修改各個B+樹索引。而且我們講過，B+樹每層節點都是按照索引列的值從小到大的順序排序而組成了雙向鏈表。不論是葉子節點中的記錄，還是內節點中的記錄（也就是不論是用戶記錄還是目錄項記錄）都是按照索引列的值從小到大的順序而形成了一個單向鏈表。而增、刪、改操作可能會對節點和記錄的排序造成破壞，所以存儲引擎需要額外的時間進行一些記錄移位，頁面分裂、頁面回收啥的操作來維護好節點和記錄的排序。

1.2 什么時候要使用索引？

只要使用到索引都是有代價的，因此我們不能盲目的使用索引

主鍵自動建立主鍵索引
頻繁作為查詢條件在WHERE
查詢中與其他表關聯的字段，外鍵關系建立索引
作為排序的列要建立索引，排序字段通過索引去訪問，會大大提高排序速度
高并發條件下傾向組合索引；
查詢中統計或者分組的字段或者用于聚合函數的列可以建立索引，例如使用了max(column_1)或者count(column_1)時的column_1就需要建立索引

1.3 什么時候盡量不要建立索引

表記錄太少（全表掃描也很快，沒有必要）
經常增刪改的字段上不要建立索引
有大量重復且分布均勻的數據的列不建立索引

2. 多列索引

在上面的例子中，都是以單個列作為索引的

而多列索引指的是組合索引，組合多個列創建一個索引，很多人對多列索引理解不夠，常見的就是為每一個列創建獨立的索引，或者按照錯誤的順序創建組合索引。

在多個列上建立單列索引大部分情況下并不能提高MySQL的查詢性能。MySQL5.0和更新的版本引入了一種“索引合并”（index merge）的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。

在MySQL’更早的版本中只能使用其中某一個單列索引。但在MySQL5.0和更新的版本中，查詢能夠同時使用多個單列索引進行掃描，并將結果進行合并。
該特新主要應用于以下三種場景：

對OR語句求并集，如查詢SELECT * FROM TB1 WHERE c1=“xxx” OR c2="“xxx"時，如果c1和c2列上分別有索引，可以按照c1和c2條件進行查詢，再將查詢結果合并（union）操作，得到最終結果
對AND語句求交集，如查詢SELECT * FROM TB1 WHERE c1=“xxx” AND c2=”"xxx"時，如果c1和c2列上分別有索引，可以按照c1和c2條件進行查詢，再將查詢結果取交集（intersect）操作，得到最終結果
對AND和OR組合語句求結果

2.1 索引適用的查詢

全值匹配
如果我們的搜索條件中的列和索引列一致的話，這種情況就稱為全值匹配
匹配左邊的列
其實在我們的搜索語句中也可以不用包含全部聯合索引中的列，只包含左邊的就行
匹配列前綴
對于字符串類型的索引列來說，我們只匹配它的前綴也是可以快速定位記錄的
匹配范圍值
b+樹的所有記錄都是按照索引列的值從小到大的順序排好序的，所以這極大的方便我們查找索引列的值在某個范圍內的記錄，不過在使用聯合進行范圍查找的時候需要注意，如果對多個列同時進行范圍查找的話，只有對索引最左邊的那個列進行范圍查找的時候才能用到B+樹索引。

2.2 使用聯合索引進行排序注意事項

對于聯合索引有個問題需要注意，ORDER BY的子句后邊的列的順序也必須按照索引列的順序給出

2.3 不可以使用索引進行排序的幾種情況

2.3.1 ASC、DESC混用

對于使用聯合索引進行排序的場景，我們要求各個排序列的排序順序是一致的，也就是要么各個列都是ASC規則排序，要么都是DESC規則排序。

2.3.2 排序列包含非同一個索引的列

有時候用來排序的多個列不是一個索引里的，這種情況也不能使用索引進行排序

2.3.3 排序列使用了復雜的表達式

要想使用索引進行排序操作，必須保證索引列是以單獨列的形式出現，而不是修飾過的形式

2.4 多列索引分組

如果沒有索引的話，分組過程全部需要在內存里實現，而如果有了索引的話，恰巧這個分組順序又和我們的B+樹中的索引列的順序是一致的，而我們的B+樹索引又是按照索引列排好序的，這不正好么，所以可以直接使用B+樹索引進行分組。

2.5 回表的代價

查詢優化器會事先對表中的記錄計算一些統計數據，然后再利用這些統計數據根據查詢的條件來計算一下需要回表的記錄數，需要回表的記錄數越多，就越傾向于使用全表掃描，反之傾向于使用二級索引 + 回表的方式。一般情況下，限制查詢獲取較少的記錄數會讓優化器更傾向于選擇使用二級索引 + 回表的方式進行查詢，因為回表的記錄越少，性能提升就越高

2.6 覆蓋索引

為了徹底告別回表操作帶來的性能損耗，我們建議：最好在查詢列表里只包含索引列。

2.6.1 覆蓋索引是非常有用的工具，能夠極大的提高性能

索引條目通常遠遠小于數據行操作，索引如果只需要讀取索引，那么MySQL就會極大的減少數據訪問量
因為索引是按照列值順序存儲的（至少單個頁內如此），所以對于I/O密集型的范圍查詢會比隨機從磁盤讀取每一行數據的I/O要少的多

一些存儲引擎如MyISAM的內存中只緩存索引，數據則依賴操作系統來緩存，因此訪問數據需要一次系統調用。這會導致嚴重的性能問題

由于InnoDB的聚簇索引，覆蓋索引對于InnoDB表特別有用。由于InnoDB的二級索引在葉子結點中保存了行的主鍵值，如果二級主鍵能夠覆蓋查詢，則可以便面對主鍵索引的二次查詢

注意：不是所有的索引都可以成為覆蓋索引，覆蓋索引必須要存儲索引列的值，索引哈希索引、空間索引、全文索引的都不存儲索引列的值，所以MySQL只能使用B-Tree索引做覆蓋索引

3. 如何挑選索引

3.1 只為用于搜索、排序或分組的列創建索引

只為出現在WHERE子句中的列、連接子句中的連接列，或者出現在ORDER BY或GROUP BY子句中的列創建索引。而出現在查詢列表中的列就沒必要建立索引了

3.2 考慮列的基數

列的基數指的是某一列中不重復數據的個數，比方說某個列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，雖然有9條記錄，但該列的基數卻是3。也就是說，在記錄行數一定的情況下，列的基數越大，該列中的值越分散，列的基數越小，該列中的值越集中。這個列的基數指標非常重要，直接影響我們是否能有效的利用索引。假設某個列的基數為1，也就是所有記錄在該列中的值都一樣，那為該列建立索引是沒有用的，因為所有值都一樣就無法排序，無法進行快速查找了～而且如果某個建立了二級索引的列的重復值特別多，那么使用這個二級索引查出的記錄還可能要做回表操作，這樣性能損耗就更大了。所以結論就是：最好為那些列的基數大的列建立索引，為基數太小列的建立索引效果可能不好。

3.3 索引列的類型盡量小

如果我們想要對某個整數列建立索引的話，在表示的整數范圍允許的情況下，盡量讓索引列使用較小的類型，比如我們能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT～這是因為：

數據類型越小，在查詢時進行的比較操作越快（這是CPU層次的東東）

數據類型越小，索引占用的存儲空間就越少，在一個數據頁內就可以放下更多的記錄，從而減少磁盤I/O帶來的性能損耗，也就意味著可以把更多的數據頁緩存在內存中，從而加快讀寫效率。

3.4 索引字符串值的前綴

只對字符串的前幾個字符進行索引也就是說在二級索引的記錄中只保留字符串前幾個字符。這樣在查找記錄時雖然不能精確的定位到記錄的位置，但是能定位到相應前綴所在的位置，然后根據前綴相同的記錄的主鍵值回表查詢完整的字符串值，再對比就好了。這樣只在B+樹中存儲字符串的前幾個字符的編碼，既節約空間，又減少了字符串的比較時間，還大概能解決排序的問題