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。
最後
以上就是MySQL數據庫索引的坑及合理利用的詳細內容，更多關於MySQL索引坑及合理利用的資料請關註WalkonNet其它相關文章！