MySQL索引原理、失效情况
聲明：本文是小編在學習過程中，東拼西湊整理，如有雷同，純屬借鑒。
Mysql5.7的版本, InnoDB引擎
目錄
1 mysql索引知識
1.1 B+Tree索引
1.2 主鍵索引和普通索引的區別
1.3 唯一索引vs普通索引
2 mysql索引優化
2.1 查看索引使用情況
2.2 mysql索引使用策略
2.3 mysql索引使用原則
1 mysql索引知識
1.1 B+Tree索引
在InnoDB中，表都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表（IOT），InnoDB使用B+樹索引模型，數據都是存儲在B+樹中的。
假設，有一個表的主鍵列為ID，字段為k，并且在k上有索引。表中R1~R5的（ID,k）值分別為（100,1）、（200,2）、（300,3）、（500,5）、（600,6），每一個索引在InnoDB里面對應一棵B+樹，兩棵樹的簡意示意圖如下：
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
1.2 主鍵索引和普通索引的區別
-
主鍵索引的葉子節點存的是整行數據。主鍵索引也被稱為聚簇索引(clustered index)
-
非主鍵索引的葉子節點內容是主鍵的值。非主鍵索引也被稱為二級索引(secondary index)
如果語句是select * from T where ID=500，即主鍵查詢方式，則只需要搜索ID這棵B+樹；
如果語句是select * from T where k=5，即普通索引查詢方式，則需要先搜索k索引樹，得到ID的值為500，再到ID索引樹搜索一次，這個過程稱為回表！
也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹，因此，我們在應用中應該盡量使用主鍵查詢。
1.3 唯一索引vs普通索引
從查詢上來說
-
對于普通索引來說，查找到滿足條件的第一個記錄后，需要查找下一個記錄，直到碰到第一個不滿足條件的記錄。
-
對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄后，就會停止繼續檢索。
從更新上來說
A??如果目標頁在內存中：
-
對于唯一索引來說，找到3和5之間的位置，判斷有沒有沖突，插入這個值，語句執行結束；
-
對于普通索引來說，找到3和5之間的位置，插入這個值，語句執行結束。
B??如果目標頁在不在內存中：
-
對于唯一索引來說，需要將數據頁讀入內存，判斷到沒有沖突，插入這個值，語句執行結束；
-
對于普通索引來說，則是將更新記錄在change buffer，語句執行就結束了。
從這里可以看到，查詢上普通索引只是比唯一索引多了一個一次指針尋找和一次計算，由于數據是按頁讀取的，數據幾乎都在內存中，所以性能相差不大。
? 但從更新上來看，如果數據不在內存中，唯 一索引需要將數據從磁盤上讀取到內存中，這樣會引發隨機讀，導致IO消耗增多，而普通索引可以利用change buffer，IO上邊要節省很多。性能相差會很多，所以如果可以在業務端保證數據的唯一性，那就可以使用普通索引。
2 mysql索引優化
2.1 查看索引使用情況
使用方法：在select語句前加上explain
示例：EXPLAIN SELECT surname,first_name form a,b WHERE a.id=b.id
EXPLAIN列的解釋：
- table：顯示這一行的數據是關于哪張表的。
- type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、index和ALL。
- possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引?？梢詾橄嚓P的域從WHERE語句中選擇一個合適的語句。
- key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MySQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MySQL忽略索引。
- key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好。
- ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。
- rows：MySQL認為必須檢查的用來返回請求數據的行數。
- Extra：關于MySQL如何解析查詢的額外信息。
Extra列返回的描述的意義：
Distinct: 一旦MySQL找到了與行相聯合匹配的行，就不再搜索了。Not exists: MySQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了。Range checked for each Record（index map:#）: 沒有找到理想的索引，因此對于從前面表中來的每一個行組合，MySQL檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一。Using filesort: 看到這個的時候，查詢就需要優化了。MySQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行。Using index: 列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候。Using temporary: 看到這個的時候，查詢需要優化了。這里，MySQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上。Where used: 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題不同連接類型的解釋（按照效率高低的順序排序）。system: 表只有一行：system表。這是const連接類型的特殊情況。const: 表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MySQL先讀這個值然后把它當做常數來對待。eq_ref: 在連接中，MySQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用。ref: 這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發生。對于之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴于根據索引匹配的記錄多少—越少越好。range: 這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西時發生的情況。index: 這個連接類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小于表數據）。ALL: 這個連接類型對于前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免。2.2 mysql索引使用策略
2.3 mysql索引使用原則
1、復合索引：選擇索引列的順序
????? 1)盡量把字段長度小的列放在聯合索引的最左側（因為字段長度越小，一頁能存儲的數據量越大，IO性能也就越好）???
????? 2)區分度最高的放在聯合索引的最左側（區分度=列中不同值的數量/列的總行數）
????? 3)使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）
2、表關聯查詢
????? 1)類型和大小要相同，可以使用索引。
????????? VARCHAR(10)和?CHAR(10)大小相同，但?VARCHAR(10)與?CHAR(15)不相同。
????? 2)字符串列之間比較，兩列應使用相同的字符集。例如，將utf8列與?latin1列進行比較會不使用索引。
????? 3)將字符串列與時間或數字列進行比較時，在沒有轉換情況下,不使用索引。
3、常見的索引列建議
????? 1)? WHERE 字段
????? 2) ORDER BY、GROUP BY、DISTINCT 中的字段不要將符合1和2中字段的列都建立一個索引，通常將1、2中的字段建立聯合索引效果更好
????? 3)多表join的關聯列
4、通過索引掃描的行記錄數超過全表的10%~30%左右,優化器不會走索引，而變成全表掃描
5、避免使用雙%號的查詢條件。 （如果無前置%,只有后置%，是可以用到列上的索引的）
? 覆蓋索引、前綴索引、索引下推，在滿足語句需求的情況下，盡量少地訪問資源是數據庫設計的重要原則之一。我們在使用數據庫的時候，尤其是在設計表結構時，也要以減少資源消耗為目標。
?
添加公眾號「信息技術智庫」：
🍅 硬核資料：20G，8大類資料，關注即可領取（PPT模板、簡歷模板、技術資料）
🍅 技術互助：技術群大佬指點迷津，你的問題可能不是問題，求資源在群里喊一聲。
🍅 面試題庫：由各個技術群小伙伴們共同投稿，熱乎的大廠面試真題，持續更新中。
🍅 知識體系：含編程語言、算法、大數據生態圈組件（Mysql、Hive、Spark、Flink）、數據倉庫、前端等。
👇👇送書抽獎丨技術互助丨粉絲福利👇👇
一索引 or 普通索引
總結
以上是生活随笔為你收集整理的MySQL索引原理、失效情况的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Android之WebView学习
- 下一篇： 7套干货，Python常用技术学习知识图