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

合理利用索引

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

1.普通索引的弊端

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

它的流程應(yīng)該是這樣的：

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

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

2.主鍵索引的陷阱

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

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

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

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

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

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

3.聯(lián)合索引的矛與盾

由上面兩條結(jié)論可以得出：

• 盡量不要去回表
• 身份證號不適合當(dāng)主鍵索引

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

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

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

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

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

4.前綴索引的短小精悍

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

5.唯一索引的快與慢

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

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

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

6.不要盲目加索引

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

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

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

7.索引失效那些事

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

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

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

索引優(yōu)化

1.change buffer

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

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

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

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

2.索引下推

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

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

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

3.刷新鄰接頁

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

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

4.mrr

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

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

最后

以上就是mysql數(shù)據(jù)庫索引的坑及合理利用的詳細(xì)內(nèi)容，更多關(guān)于mysql索引坑及合理利用的資料請關(guān)注服務(wù)器之家其它相關(guān)文章！

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