一個好的索引對數據庫系統(tǒng)尤其重要，索引可以說是數據庫中的一個大心臟了，如果說一個數據庫少了索引，那么數據庫本身存在的意義就不大了，和普通的文件沒什么兩樣。今天來說說mysql索引，從細節(jié)和實際業(yè)務的角度看看在mysql中b+樹索引好處，以及我們在使用索引時需要注意的知識點。

合理利用索引

在工作中，我們可能判斷數據表中的一個字段是不是需要加索引的最直接辦法就是：這個字段會不會經常出現在我們的where條件中。從宏觀的角度來說，這樣思考沒有問題，但是從長遠的角度來看，有時可能需要更細致的思考，比如我們是不是不僅僅需要在這個字段上建立一個索引？多個字段的聯合索引是不是更好？以一張用戶表為例，用戶表中的字段可能會有用戶的姓名、用戶的身份證號、用戶的家庭地址等等。

1.普通索引的弊端

現在有個需求需要根據用戶的身份證號找到用戶的姓名，這時候很顯然想到的第一個辦法就是在id_card上建立一個索引，嚴格來說是唯一索引，因為身份證號肯定是唯一的，那么當我們執(zhí)行以下查詢的時候：

它的流程應該是這樣的：

• 先在id_card索引樹上搜索，找到id_card對應的主鍵id
• 通過id去主鍵索引上搜索，找到對應的name

從效果上來看，結果是沒問題的，但是從效率上來看，似乎這個查詢有點昂貴，因為它檢索了兩顆b+樹，假設一顆樹的高度是3，那么兩顆樹的高度就是6，因為根節(jié)點在內存里（此處兩個根節(jié)點），所以最終要在磁盤上進行io的次數是4次，以一次磁盤隨機io的時間平均耗時是10ms來說，那么最終就需要40ms。這個數字一般，不算快。

2.主鍵索引的陷阱

既然問題是回表，造成了在兩顆樹都檢索了，那么核心問題就是看看能不能只在一顆樹上檢索。這里從業(yè)務的角度你可能發(fā)現了一個切入點，身份證號是唯一的，那么我們的主鍵是不是可以不用默認的自增id了，我們把主鍵設置成我們的身份證號，這樣整個表的只需要一個索引，并且通過身份證號可以查到所有需要的數據包括我們的姓名，簡單一想似乎有道理，只要每次插入數據的時候，指定id是身份證號就行了，但是仔細一想似乎有問題。

這里要從b+樹的特點來說，b+樹的數據都存在葉子節(jié)點上，并數據是頁式管理的，一頁是16k，這是什么意思呢？哪怕我們現在是一行數據，它也要占用16k的數據頁，只有當我們的數據頁寫滿了之后才會寫到一個新的數據頁上，新的數據頁和老的數據頁在物理上不一定是連續(xù)的，而且有一點很關鍵，雖然數據頁物理上是不連續(xù)的，但是數據在邏輯上是連續(xù)的。

也許你會好奇，這和我們說的身份證號當主鍵id有什么關系？這時你應該關注連續(xù)這個關鍵字，身份證號不是連續(xù)的，這意味著什么？當我們插入一條不連續(xù)的數據的時候，為了保持連續(xù)，需要移動數據，比如原來在一頁上的數據有1->5，這時候插入了一條3，那么就需要把5移到3后面，也許你會說這也沒多少開銷，但是如果當新的數據3造成這個頁a滿了，那么就要看它后面的頁b是否有空間，如果有空間，這時候頁b的開始數據應該是這個從頁a溢出來的那條，對應的也要移動數據。

如果此時頁b也沒有足夠的空間，那么就要申請新的頁c，然后移一部分數據到這個新頁c上，并且會切斷頁a與頁b之間的關系，在兩者之間插入一個頁c，從代碼的層面來說，就是切換鏈表的指針。

總結來說，不連續(xù)的身份證號當主鍵可能會造成頁數據的移動、隨機io、頻繁申請新頁相關的開銷。如果我們用的是自增的主鍵，那么對于id來說一定是順序的，不會因為隨機io造成數據移動的問題，在插入方面開銷一定是相對較小的。

其實不推薦用身份證號當主鍵的還有另外一個原因：身份證號作為數字來說太大了，得用bigint來存，正常來說一個學校的學生用int已經足夠了，我們知道一頁可以存放16k，當一個索引本身占用的空間越大時，會導致一頁能存放的數據越少，所以在一定數據量的情況下，使用bigint要比int需要更多的頁也就是更多的存儲空間。

3.聯合索引的矛與盾

由上面兩條結論可以得出：

• 盡量不要去回表
• 身份證號不適合當主鍵索引

所以自然而然地想到了聯合索引，創(chuàng)建一個【身份證號+姓名】的聯合索引，注意聯合索引的順序，要符合最左原則。這樣當我們同樣執(zhí)行以下sql時：

不需要回表就可以得到我們需要的name字段，然而還是沒有解決身份證號本身占用空間過大的問題，這是業(yè)務數據本身的問題，如果你要解決它的話，我們可以通過一些轉換算法將原本大的數據轉換成小的數據，比如crc32：

可以將原本需要8個字節(jié)存儲空間的身份證號用4個字節(jié)的crc碼替代，因此我們的數據庫需要再加個字段crc_id_card，聯合索引也從【身份證號+姓名】變成了【crc32(身份證號)+姓名】，聯合索引占的空間變小了。但是這種轉換也是有代價的：

• 每次額外的crc，導致需要更多cpu資源
• 額外的字段，雖然讓索引的空間變小了，但是本身也要占用空間
• crc會存在沖突的概率，這需要我們查詢出來數據后，再根據id_card過濾一下，過濾的成本根據重復數據的數量而定，重復越多，過濾越慢。

關于聯合索引存儲優(yōu)化，這里有個小細節(jié)，假設現在有兩個字段a和b，分別占用8個字節(jié)和20個字節(jié)，我們在聯合索引已經是[a,b]的情況下，還要支持b的單獨查詢，因此自然而然我們在b上也建立個索引，那么兩個索引占用的空間為 8+20+20=48，現在無論我們通過a還是通過b查詢都可以用到索引，如果在業(yè)務允許的條件下，我們是否可以建立[b,a]和a索引，這樣的話，不僅滿足單獨通過a或者b查詢數據用到索引，還可以占用更小的空間：20+8+8=36。

4.前綴索引的短小精悍

有時候我們需要索引的字段是字符串類型的，并且這個字符串很長，我們希望這個字段加上索引，但是我們又不希望這個索引占用太多的空間，這時可以考慮建立個前綴索引，以這個字段的前一部分字符建立個索引，這樣既可以享受索引，又可以節(jié)省空間，這里需要注意的是在前綴重復度較高的情況下，前綴索引和普通索引的速度應該是有差距的。

5.唯一索引的快與慢

在說唯一索引之前，我們先了解下普通索引的特點，我們知道對于b+樹而言，葉子節(jié)點的數據是有序的。

假設現在我們要查詢2這條數據，那么在通過索引樹找到2的時候，存儲引擎并沒有停止搜索，因為可能存在多個2，這表現為存儲引擎會在葉子節(jié)點上接著向后查找，在找到第二個2之后，就停止了嗎？答案是否，因為存儲引擎并不知道后面還有沒有更多的2，所以得接著向后查找，直至找到第一個不是2的數據，也就是3，找到3之后，停止檢索，這就是普通索引的檢索過程。

唯一索引就不一樣了，因為唯一性，不可能存在重復的數據，所以在檢索到我們的目標數據之后直接返回，不會像普通索引那樣還要向后多查找一次，從這個角度來看，唯一索引是要比普通索引快的，但是當普通索引的數據都在一個頁內的話，其實也并不會快多少。在數據的插入方面，唯一索引可能就稍遜色，因為唯一性，每次插入的時候，都需要將判斷要插入的數據是否已經存在，而普通索引不需要這個邏輯，并且很重要的一點是唯一索引會用不到change buffer（見下文）。

6.不要盲目加索引

在工作中，你可能會遇到這樣的情況：這個字段我需不需要加索引？。對于這個問題，我們常用的判斷手段就是：查詢會不會用到這個字段，如果這個字段經常在查詢的條件中，我們可能會考慮加個索引。但是如果只根據這個條件判斷，你可能會加了一個錯誤的索引。我們來看個例子：假設有張用戶表，大概有100w的數據，用戶表中有個性別字段表示男女，男女差不多各占一半，現在我們要統(tǒng)計所有男生的信息，然后我們給性別字段加了索引，并且我們這樣寫下了sql：

如果不出意外的話，innodb是不會選擇性別這個索引的。如果走性別索引，那么一定是需要回表的，在數據量很大的情況下，回表會造成什么樣的后果？我貼一張和上面一樣的圖想必大家都知道了：

主要就是大量的io，一條數據需要4次，那么50w的數據呢？結果可想而知。因此針對這種情況，mysql的優(yōu)化器大概率走全表掃描，直接掃描主鍵索引，因為這樣性能可能會更高。

7.索引失效那些事

某些情況下，因為我們自己使用的不當，導致mysql用不到索引，這一般很容易發(fā)生在類型轉換方面，也許你會說，mysql不是已經支持隱式轉換了嗎？比如現在有個整型的user_id索引字段，我們因為查詢的時候沒注意，寫成了：

注意這里是字符的1234，當發(fā)生這種情況下，mysql確實足夠聰明，會把字符的1234轉成數字的1234，然后愉快的使用了user_id索引。 但是如果我們有個字符型的user_id索引字段，還是因為我們查詢的時候沒注意，寫成了：

這時候就有問題了，會用不到索引，也許你會問，這時mysql為什么不會轉換了，把數字的1234轉成字符型的1234不就行了？ 這里需要解釋下轉換的規(guī)則了，當出現字符串和數字比較的時候，要記?。簃ysql會把字符串轉換成數字。也許你又會問：為什么把字符型user_id字段轉換成數字就用不到索引了? 這又要說到b+樹索引的結構了，我們知道b+樹的索引是按照索引的值來分叉和排序的，當我們把索引字段發(fā)生類型轉換時會發(fā)生值的變化，比如原來是a值，如果執(zhí)行整型轉換可能會對應一個b值（int(a)=b）,這時這顆索引樹就不能用了，因為索引樹是按照a來構造的，不是b，所以會用不到索引。

索引優(yōu)化

1.change buffer

我們知道在更新一條數據的時候，要先判斷這條數據的頁是否在內存里，如果在的話，直接更新對應的內存頁，如果不在的話，只能去磁盤把對應的數據頁讀到內存中來，然后再更新，這會有什么問題呢？

• 去磁盤的讀這個動作稍顯的有點慢
• 如果同時更新很多數據，那么即有可能發(fā)生很多離散的io

為了解決這種情況下的速度問題，change buffer出現了，首先不要被buffer這個單詞誤導，change buffer除了會在公共的buffer pool里之外，也是會持久化到磁盤的。當有了change buffer之后，我們更新的過程中，如果發(fā)現對應的數據頁不在內存里的話，也不去磁盤讀取相應的數據頁了，而是把要更新的數據放入到change buffer中，那change buffer的數據何時被同步到磁盤上去？如果此時發(fā)生讀動作怎么辦？首先后臺有個線程會定期把change buffer的數據同步到磁盤上去的，如果線程還沒來得及同步，但是又發(fā)生了讀操作，那么也會觸發(fā)把change buffer的數據merge到磁盤的事件。

需要注意的是并不是所有的索引都能用到changer buffer，像主鍵索引和唯一索引就用不到，因為唯一性，所以它們在更新的時候要判斷數據存不存在，如果數據頁不在內存中，就必須去磁盤上把對應的數據頁讀到內存里，而普通索引就沒關系了，不需要校驗唯一性。change buffer越大，理論收益就越大，這是因為首先離散的讀io變少了，其次當一個數據頁上發(fā)生多次變更，只需merge一次到磁盤上。當然并不是所有的場景都適合changer buffer，如果你的業(yè)務是更新之后，需要立馬去讀，changer buffer會適得其反，因為需要不停地觸發(fā)merge動作，導致隨機io的次數不會變少，反而增加了維護changer buffer的開銷。

2.索引下推

前面我們說了聯合索引，聯合索引要滿足最左原則，即在聯合索引是[a,b]的情況下，我們可以通過以下的sql用到索引：

其實聯合索引也可以使用最左前綴的原則，即：

但是這里需要注意的是，因為使用了a的一部分，在mysql5.6之前，上面的sql在檢索出所有a是“趙”開頭的數據之后，就立馬回表（使用的select *），然后再對比b是不是“上海市”這個判斷，這里是不是有點懵？為什么b這個判斷不直接在聯合索引上判斷，這樣的話回表的次數不就少了嗎？造成這個問題的原因還是因為使用了最左前綴的問題，導致索引雖然能使用部分a，但是完全用不到b，看起來是有點“傻”，于是在mysql5.6之后，就出現了索引下推這個優(yōu)化（index condition pushdown）,有了這個功能以后，雖然使用的是最左前綴，但是也可以在聯合索引上搜索出符合a%的同時也過濾非b的數據，大大減少了回表的次數。

3.刷新鄰接頁

在說刷新鄰接頁之前，我們先說下臟頁，我們知道在更新一條數據的時候，得先判斷這條數據所在的頁是否在內存中，如果不在的話，需要把這個數據頁先讀到內存中，然后再更新內存中的數據，這時會發(fā)現內存中的頁有最新的數據，但是磁盤上的頁卻依然是老數據，那么此時這條數據所在的內存中的頁就是臟頁，需要刷到磁盤上來保持一致。所以問題來了，何時刷？每次刷多少臟頁才合適？如果每次變更就刷，那么性能會很差，如果很久才刷，臟頁就會堆積很多，造成內存池中可用的頁變少，進而影響正常的功能。所以刷的速度不能太快但要及時，mysql有個清理線程會定期執(zhí)行，保證了不會太快，當臟頁太多或者redo log已經快滿了，也會立刻觸發(fā)刷盤，保證了及時。

在臟頁刷盤的過程中，innodb這里有個優(yōu)化：如果要刷的臟頁的鄰居頁也臟了，那么就順帶一起刷，這樣的好處就是可以減少隨機io，在機械磁盤的情況下，優(yōu)化應該挺大，但是這里可能會有坑，如果當前臟頁的鄰居臟頁在被一起刷入后，鄰居頁立馬因為數據的變更又變臟了，那此時是不是有種多此一舉的感覺，并且反而浪費了時間和開銷。更糟糕的是如果鄰居頁的鄰居也是臟頁...，那么這個連鎖反應可能會出現短暫的性能問題。

4.mrr

在實際業(yè)務中，我們可能會被告知盡量使用覆蓋索引，不要回表，因為回表需要更多io，耗時更長，但是有時候我們又不得不回表，回表不僅僅會造成過多的io，更嚴重的是過多的離散io。

現在要查詢成績在60-70之間的用戶信息，于是我們的sql寫成上面的那樣，當然我們的grade字段是有索引的，按照常理來說，會先在grade索引上找到grade=60這條數據，然后再根據grade=60這條數據對應的id去主鍵索引上找，最后再次回到grade索引上，不停的重復同樣的動作...， 假設現在grade=60對應的id=1，數據是在page_no_1上，grade=61對應的id=10，數據是在page_no_2上，grade=62對應的id=2，數據是在page_no_1上，所以真實的情況就是先在page_no_1上找數據，然后切到page_no_2，最后又切回page_no_1上，但其實id=1和id=2完全可以合并，讀一次page_no_1即可，不僅節(jié)省了io，同時避免了隨機io，這就是mrr。當使用mrr之后，輔助索引不會立即去回表，而是將得到的主鍵id，放在一個buffer中，然后再對其排序，排序后再去順序讀主鍵索引，大大減少了離散的io。

最后

以上就是mysql數據庫索引的坑及合理利用的詳細內容，更多關于mysql索引坑及合理利用的資料請關注服務器之家其它相關文章！

原文鏈接：https://blog.csdn.net/shanwu1/article/details/121422785