MySQL索引的坑怎么解決

147次閱讀

共計 6701 個字符，預計需要花費 17 分鐘才能閱讀完成。

今天丸趣 TV 小編給大家分享一下 MySQL 索引的坑怎么解決的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

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

合理利用索引

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

「1. 普通索引的弊端」

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

SELECT name FROM user WHERE id_card=xxx

它的流程應該是這樣的：

先在 id_card 索引樹上搜索，找到 id_card 對應的主鍵 id

通過 id 去主鍵索引上搜索，找到對應的 name

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

「2. 主鍵索引的陷阱」

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

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

也許你會好奇，這和我們說的身份證號當主鍵 ID 有什么關系？這時你應該關注連續這個關鍵字，身份證號不是連續的，這意味著什么？當我們插入一條不連續的數據的時候，為了保持連續，需要移動數據，比如原來在一頁上的數據有 1 - 5，這時候插入了一條 3，那么就需要把 5 移到 3 后面，也許你會說這也沒多少開銷，但是如果當新的數據 3 造成這個頁 A 滿了，那么就要看它后面的頁 B 是否有空間，如果有空間，這時候頁 B 的開始數據應該是這個從頁 A 溢出來的那條，對應的也要移動數據。如果此時頁 B 也沒有足夠的空間，那么就要申請新的頁 C，然后移一部分數據到這個新頁 C 上，并且會切斷頁 A 與頁 B 之間的關系，在兩者之間插入一個頁 C，從代碼的層面來說，就是切換鏈表的指針。

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

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

「3. 聯合索引的矛與盾」

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

盡量不要去回表

身份證號不適合當主鍵索引

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

select name from user where id_card=xxx

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

crc32.ChecksumIEEE([]byte( 341124199408203232))

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

每次額外的 crc，導致需要更多 cpu 資源

額外的字段，雖然讓索引的空間變小了，但是本身也要占用空間

crc 會存在沖突的概率，這需要我們查詢出來數據后，再根據 id_card 過濾一下，過濾的成本根據重復數據的數量而定，重復越多，過濾越慢。

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

「4. 前綴索引的短小精悍」

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

alter table xx add index(name(7));#name 前 7 個字符建立索引
select xx from xx where name= JamesBond

「5. 唯一索引的快與慢」

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

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

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

「6. 不要盲目加索引」

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

select * from user where sex= 男

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

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

「7. 索引失效那些事」

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

select xx from user where user_id= 1234

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

select xx from user where user_id=1234

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

索引優化「1.change buffer」

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

去磁盤的讀這個動作稍顯的有點慢

如果同時更新很多數據，那么即有可能發生很多離散的 IO

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

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

「2. 索引下推」

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

select * from table where A= xx 
select * from table where A= xx  AND B= xx

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

select * from table where A like  趙 %  AND B= 上海市

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

「3. 刷新鄰接頁」

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

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

「4.MRR」

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

select * from user where grade between 60 and 70

現在要查詢成績在 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 即可，不僅節省了 IO，同時避免了隨機 IO，這就是 MRR。當使用 MRR 之后，輔助索引不會立即去回表，而是將得到的主鍵 id，放在一個 buffer 中，然后再對其排序，排序后再去順序讀主鍵索引，大大減少了離散的 IO。