共計 4002 個字符，預計需要花費 11 分鐘才能閱讀完成。
本篇內容主要講解“MySQL 索引的使用原則”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“MySQL 索引的使用原則”吧!
一、存儲引擎的比較
注：上面提到的 B 樹索引并沒有指出是 B -Tree 和 B +Tree 索引，但是 B - 樹和 B + 樹的定義是有區別的。
在 MySQL 中，主要有四種類型的索引，分別為：B-Tree 索引，Hash 索引，Fulltext 索引和 R-Tree 索引。
B-Tree 索引是 MySQL 數據庫中使用最為頻繁的索引類型，除了 Archive 存儲引擎之外的其他所有的存儲引擎都支持 B-Tree 索引。Archive 引擎直到 MySQL 5.1 才支持索引，而且只支持索引單個 AUTO_INCREMENT 列。
不僅僅在 MySQL 中是如此，實際上在其他的很多數據庫管理系統中 B -Tree 索引也同樣是作為最主要的索引類型，這主要是因為 B-Tree 索引的存儲結構在數據庫的數據檢索中有非常優異的表現。
一般來說，MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結構來存儲的，也就是所有實際需要的數據都存放于 Tree 的 Leaf Node(葉子節點)，而且到任何一個 Leaf Node 的最短路徑的長度都是完全相同的，所以我們大家都稱之為 B-Tree 索引。
當然，可能各種數據庫（或 MySQL 的各種存儲引擎）在存放自己的 B-Tree 索引的時候會對存儲結構稍作改造。
如 Innodb 存儲引擎的 B-Tree 索引實際使用的存儲結構實際上是 B+Tree，也就是在 B-Tree 數據結構的基礎上做了很小的改造，在每一個 Leaf Node 上面出了存放索引鍵的相關信息之外，還存儲了指向與該 Leaf Node 相鄰的后一個 LeafNode 的指針信息（增加了順序訪問指針），這主要是為了加快檢索多個相鄰 Leaf Node 的效率考慮。
InnoDB 是 Mysql 的默認存儲引擎(Mysql5.5.5 之前是 MyISAM）
接下來我們先看看 B - 樹、B+ 樹的概念。弄清楚，為什么加了索引查詢速度會加快？
二、B- 樹、B+ 樹概念
B 樹
即二叉搜索樹：
1、所有非葉子結點至多擁有兩個兒子（Left 和 Right）；
2、所有結點存儲一個關鍵字；
3、非葉子結點的左指針指向小于其關鍵字的子樹，右指針指向大于其關鍵字的子樹；
如：
B- 樹
是一種多路搜索樹（并不是二叉的）：
1、定義任意非葉子結點最多只有 M 個兒子；且 M 2；
2、根結點的兒子數為[2, M]；
3、除根結點以外的非葉子結點的兒子數為[M/2, M]；
4、每個結點存放至少 M /2-1（取上整）和至多 M - 1 個關鍵字；（至少 2 個關鍵字）
5、非葉子結點的關鍵字個數 = 指向兒子的指針個數 -1；
6、非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且 K[i] K[i+1]；
7、非葉子結點的指針：P[1], P[2], …, P[M]；其中 P[1]指向關鍵字小于 K[1]的子樹，P[M]指向關鍵字大于 K[M-1]的子樹，其它 P[i]指向關鍵字屬于 (K[i-1], K[i]) 的子樹；
8、所有葉子結點位于同一層；
如：（M=3）
B- 樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬范圍的兒子結點；重復，直到所對應的兒子指針為空，或已經是葉子結點；
B- 樹的特性：
1、關鍵字集合分布在整顆樹中；
2、任何一個關鍵字出現且只出現在一個結點中；
3、搜索有可能在非葉子結點結束；
4、其搜索性能等價于在關鍵字全集內做一次二分查找；
5、自動層次控制；
由于限制了除根結點以外的非葉子結點，至少含有 M / 2 個兒子，確保了結點的至少利用率。
所以 B - 樹的性能總是等價于二分查找（與 M 值無關），也就沒有 B 樹平衡的問題；
由于 M / 2 的限制，在插入結點時，如果結點已滿，需要將結點分裂為兩個各占 M / 2 的結點；刪除結點時，需將兩個不足 M / 2 的兄弟結點合并；
B+ 樹
B+ 樹是 B - 樹的變體，也是一種多路搜索樹：
1、其定義基本與 B - 樹同，除了：
2、非葉子結點的子樹指針與關鍵字個數相同；
3、非葉子結點的子樹指針 P[i]，指向關鍵字值屬于 [K[i], K[i+1]) 的子樹（B- 樹是開區間）；
5、為所有葉子結點增加一個鏈指針；
6、所有關鍵字都在葉子結點出現；
如：（M=3）
B+ 的搜索與 B - 樹也基本相同，區別是 B + 樹只有達到葉子結點才命中（B- 樹可以在
非葉子結點命中），其性能也等價于在關鍵字全集做一次二分查找；
B+ 的特性：
1、所有關鍵字都出現在葉子結點的鏈表中（稠密索引），且鏈表中的關鍵字恰好是有序的；
2、不可能在非葉子結點命中；
3、非葉子結點相當于是葉子結點的索引（稀疏索引），葉子結點相當于是存儲（關鍵字）數據的數據層；
4、更適合文件索引系統；
了解 B -/B+ 樹的概念之后，我們繼續分析 B + 樹提高效率的原理。
三、B+ 樹索引原理
如上圖，是一顆 b + 樹，關于 b + 樹的定義可以參見 B + 樹，這里只說一些重點，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數據項（深藍色所示）和指針（黃色所示），如磁盤塊 1 包含數據項 17 和 35，包含指針 P1、P2、P3，P1 表示小于 17 的磁盤塊，P2 表示在 17 和 35 之間的磁盤塊，P3 表示大于 35 的磁盤塊。真實的數據存在于葉子節點即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點只不存儲真實的數據，只存儲指引搜索方向的數據項，如 17、35 并不真實存在于數據表中。
b+ 樹的查找過程
如圖所示，如果要查找數據項 29，那么首先會把磁盤塊 1 由磁盤加載到內存，此時發生一次 IO，在內存中用二分查找確定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，內存時間因為非常短（相比磁盤的 IO）可以忽略不計，通過磁盤塊 1 的 P2 指針的磁盤地址把磁盤塊 3 由磁盤加載到內存，發生第二次 IO，29 在 26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過指針加載磁盤塊 8 到內存，發生第三次 IO，同時內存中做二分查找找到 29，結束查詢，總計三次 IO。真實的情況是，3 層的 b + 樹可以表示上百萬的數據，如果上百萬的數據查找只需要三次 IO，性能提高將是巨大的，如果沒有索引，每個數據項都要發生一次 IO，那么總共需要百萬次的 IO，顯然成本非常非常高。
b+ 樹性質
1、通過上面的分析，我們知道 IO 次數取決于 b + 數的高度 h，假設當前數據表的數據為 N，每個磁盤塊的數據項的數量是 m，則有 h =㏒(m+1)N，當數據量 N 一定的情況下，m 越大，h 越小；而 m = 磁盤塊的大小 / 數據項的大小，磁盤塊的大小也就是一個數據頁的大小，是固定的，如果數據項占的空間越小，數據項的數量越多，樹的高度越低。這就是為什么每個數據項，即索引字段要盡量的小，比如 int 占 4 字節，要比 bigint8 字節少一半。這也是為什么 b + 樹要求把真實的數據放到葉子節點而不是內層節點，一旦放到內層節點，磁盤塊的數據項會大幅度下降，導致樹增高。當數據項等于 1 時將會退化成線性表。
2、當 b + 樹的數據項是復合的數據結構，比如 (name,age,sex) 的時候，b+ 數是按照從左到右的順序來建立搜索樹的，比如當 (張三,20,F) 這樣的數據來檢索的時候，b+ 樹會優先比較 name 來確定下一步的所搜方向，如果 name 相同再依次比較 age 和 sex，最后得到檢索的數據；但當 (20,F) 這樣的沒有 name 的數據來的時候，b+ 樹就不知道下一步該查哪個節點，因為建立搜索樹的時候 name 就是第一個比較因子，必須要先根據 name 來搜索才能知道下一步去哪里查詢。比如當 (張三,F) 這樣的數據來檢索時，b+ 樹可以用 name 來指定搜索方向，但下一個字段 age 的缺失，所以只能把名字等于張三的數據都找到，然后再匹配性別是 F 的數據了，這個是非常重要的性質，即索引的最左匹配特性。
慢查詢優化
關于 MySQL 索引原理是比較枯燥的東西，大家只需要有一個感性的認識，并不需要理解得非常透徹和深入。我們回頭來看看一開始我們說的慢查詢，了解完索引原理之后，大家是不是有什么想法呢？先總結一下索引的幾大基本原則
四、建索引的幾大原則
1、最左前綴匹配原則，非常重要的原則，mysql 會一直向右匹配直到遇到范圍查詢 (、、between、like) 就停止匹配，比如 a 1= and= b= 2 c= 3 and d = 4 如果建立 (a,b,c,d) 順序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引則都可以用到，a,b,d 的順序可以任意調整。
2、= 和 in 可以亂序，比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意順序，mysql 的查詢優化器會幫你優化成索引可以識別的形式
3、盡量選擇區分度高的列作為索引, 區分度的公式是 count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是 1，而一些狀態、性別字段可能在大數據面前區分度就是 0，那可能有人會問，這個比例有什么經驗值嗎？使用場景不同，這個值也很難確定，一般需要 join 的字段我們都要求是 0.1 以上，即平均 1 條掃描 10 條記錄
4、索引列不能參與計算，保持列“干凈”，比如 from_unixtime(create_time) =’2014-05-29’就不能使用到索引，原因很簡單，b+ 樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成 create_time = unix_timestamp(’2014-05-29’);
5、盡量的擴展索引，不要新建索引。比如表中已經有 a 的索引，現在要加 (a,b) 的索引，那么只需要修改原來的索引即可
到此，相信大家對“MySQL 索引的使用原則”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
