1. 索引的代價

在了解索引的代價之前，需要再次回顧一下索引的數(shù)據(jù)結(jié)構(gòu)B+樹

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

1.1 b+樹的查找過程

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

空間上的代價

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

時間上的代價

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

1.2 什么時候要使用索引？

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

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

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

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

2. 多列索引

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

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

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

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

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

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

對AND和OR組合語句求結(jié)果

2.1 索引適用的查詢

• 全值匹配
  如果我們的搜索條件中的列和索引列一致的話，這種情況就稱為全值匹配

• 匹配左邊的列
  其實在我們的搜索語句中也可以不用包含全部聯(lián)合索引中的列，只包含左邊的就行

• 匹配列前綴
  對于字符串類型的索引列來說，我們只匹配它的前綴也是可以快速定位記錄的

• 匹配范圍值
  b+樹的所有記錄都是按照索引列的值從小到大的順序排好序的，所以這極大的方便我們查找索引列的值在某個范圍內(nèi)的記錄，不過在使用聯(lián)合進行范圍查找的時候需要注意，如果對多個列同時進行范圍查找的話，只有對索引最左邊的那個列進行范圍查找的時候才能用到B+樹索引。

2.2 使用聯(lián)合索引進行排序注意事項

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

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

2.3.1 ASC、DESC混用

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

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

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

2.3.3 排序列使用了復(fù)雜的表達式

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

2.4 多列索引分組

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

2.5 回表的代價

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

2.6 覆蓋索引

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

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

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

一些存儲引擎如MyISAM的內(nèi)存中只緩存索引，數(shù)據(jù)則依賴操作系統(tǒng)來緩存，因此訪問數(shù)據(jù)需要一次系統(tǒng)調(diào)用。這會導(dǎo)致嚴(yán)重的性能問題

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

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

3. 如何挑選索引

3.1 只為用于搜索、排序或分組的列創(chuàng)建索引

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

3.2 考慮列的基數(shù)

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

3.3 索引列的類型盡量小

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

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

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

3.4 索引字符串值的前綴

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

• 優(yōu)點：這樣可以大大節(jié)約索引的空間，從而提高索引的效率。
• 缺點：使用前綴索引會降低索引的選擇性，而且無法使用前綴索引做ORDER BY和GROUP BY，也無法使用前綴索引做覆蓋掃描

3.4.1 如何選則前綴索引長度

選擇前綴的索引的原則是要選擇足夠的長度保證索引較高的選擇性，前綴索引的選擇性應(yīng)該接近于索引的整個列，但同時又不能太長。

可以根據(jù)，前綴的基數(shù)應(yīng)該接近于完整列的基數(shù)，來確定基數(shù)的長度，我們可以通過截取不同長度的字符和完整列進行比較，找到合適的長度
另外一個辦法就是計算完整列的選擇性，并使用前綴的選擇性接近完整列的選擇性

3.5 讓索引列在比較表達式中單獨出現(xiàn)

如果索引列在比較表達式中不是以單獨列的形式出現(xiàn)，而是以某個表達式，或者函數(shù)調(diào)用形式出現(xiàn)的話，是用不到索引的

3.6 主鍵插入順序

如果我們插入的記錄的主鍵值是依次增大的話，那我們每插滿一個數(shù)據(jù)頁就換到下一個數(shù)據(jù)頁繼續(xù)插，而如果我們插入的主鍵值忽大忽小的話，這就比較麻煩了。如果數(shù)據(jù)頁已經(jīng)滿了啊，再插進來咋辦呢？我們需要把當(dāng)前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新創(chuàng)建的這個頁中

總結(jié)

以上是生活随笔為你收集整理的深入理解InnoDB（4）—索引使用的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。