MySQL索引的使用原則

本篇內(nèi)容主要講解“MySQL索引的使用原則”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL索引的使用原則”吧!

一、存儲(chǔ)引擎的比較

注：上面提到的B樹(shù)索引并沒(méi)有指出是B-Tree和B+Tree索引，但是B-樹(shù)和B+樹(shù)的定義是有區(qū)別的。

在 MySQL 中，主要有四種類型的索引，分別為： B-Tree 索引， Hash 索引， Fulltext 索引和 R-Tree 索引。

B-Tree 索引是 MySQL 數(shù)據(jù)庫(kù)中使用最為頻繁的索引類型，除了 Archive 存儲(chǔ)引擎之外的其他所有的存儲(chǔ)引擎都支持 B-Tree 索引。Archive 引擎直到 MySQL 5.1 才支持索引，而且只支持索引單個(gè) AUTO_INCREMENT 列。

不僅僅在 MySQL 中是如此，實(shí)際上在其他的很多數(shù)據(jù)庫(kù)管理系統(tǒng)中B-Tree 索引也同樣是作為最主要的索引類型，這主要是因?yàn)?B-Tree 索引的存儲(chǔ)結(jié)構(gòu)在數(shù)據(jù)庫(kù)的數(shù)據(jù)檢索中有非常優(yōu)異的表現(xiàn)。

一般來(lái)說(shuō)， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結(jié)構(gòu)來(lái)存儲(chǔ)的，也就是所有實(shí)際需要的數(shù)據(jù)都存放于 Tree 的 Leaf Node(葉子節(jié)點(diǎn)) ，而且到任何一個(gè) Leaf Node 的最短路徑的長(zhǎng)度都是完全相同的，所以我們大家都稱之為 B-Tree 索引。

當(dāng)然，可能各種數(shù)據(jù)庫(kù)（或 MySQL 的各種存儲(chǔ)引擎）在存放自己的 B-Tree 索引的時(shí)候會(huì)對(duì)存儲(chǔ)結(jié)構(gòu)稍作改造。

如 Innodb 存儲(chǔ)引擎的 B-Tree 索引實(shí)際使用的存儲(chǔ)結(jié)構(gòu)實(shí)際上是 B+Tree，也就是在 B-Tree 數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上做了很小的改造，在每一個(gè)Leaf Node 上面出了存放索引鍵的相關(guān)信息之外，還存儲(chǔ)了指向與該 Leaf Node 相鄰的后一個(gè) LeafNode 的指針信息（增加了順序訪問(wèn)指針），這主要是為了加快檢索多個(gè)相鄰 Leaf Node 的效率考慮。

InnoDB是Mysql的默認(rèn)存儲(chǔ)引擎(Mysql5.5.5之前是MyISAM）

接下來(lái)我們先看看B-樹(shù)、B+樹(shù)的概念。弄清楚，為什么加了索引查詢速度會(huì)加快？

二、B-樹(shù)、B+樹(shù)概念

B樹(shù)

即二叉搜索樹(shù)：

1、所有非葉子結(jié)點(diǎn)至多擁有兩個(gè)兒子（Left和Right）；

2、所有結(jié)點(diǎn)存儲(chǔ)一個(gè)關(guān)鍵字；

3、非葉子結(jié)點(diǎn)的左指針指向小于其關(guān)鍵字的子樹(shù)，右指針指向大于其關(guān)鍵字的子樹(shù)；

如：

B-樹(shù)

是一種多路搜索樹(shù)（并不是二叉的）：

1、定義任意非葉子結(jié)點(diǎn)最多只有M個(gè)兒子；且M>2；

2、根結(jié)點(diǎn)的兒子數(shù)為[2, M]；

3、除根結(jié)點(diǎn)以外的非葉子結(jié)點(diǎn)的兒子數(shù)為[M/2, M]；

4、每個(gè)結(jié)點(diǎn)存放至少M(fèi)/2-1（取上整）和至多M-1個(gè)關(guān)鍵字；（至少2個(gè)關(guān)鍵字）

5、非葉子結(jié)點(diǎn)的關(guān)鍵字個(gè)數(shù)=指向兒子的指針個(gè)數(shù)-1；

6、非葉子結(jié)點(diǎn)的關(guān)鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

7、非葉子結(jié)點(diǎn)的指針：P[1], P[2], …, P[M]；其中P[1]指向關(guān)鍵字小于K[1]的子樹(shù)，P[M]指向關(guān)鍵字大于K[M-1]的子樹(shù)，其它P[i]指向關(guān)鍵字屬于(K[i-1], K[i])的子樹(shù)；

8、所有葉子結(jié)點(diǎn)位于同一層；

如：（M=3）

B-樹(shù)的搜索，從根結(jié)點(diǎn)開(kāi)始，對(duì)結(jié)點(diǎn)內(nèi)的關(guān)鍵字（有序）序列進(jìn)行二分查找，如果命中則結(jié)束，否則進(jìn)入查詢關(guān)鍵字所屬范圍的兒子結(jié)點(diǎn)；重復(fù)，直到所對(duì)應(yīng)的兒子指針為空，或已經(jīng)是葉子結(jié)點(diǎn)；

B-樹(shù)的特性：

1、關(guān)鍵字集合分布在整顆樹(shù)中；

2、任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)結(jié)點(diǎn)中；

3、搜索有可能在非葉子結(jié)點(diǎn)結(jié)束；

4、其搜索性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找；

5、自動(dòng)層次控制；

由于限制了除根結(jié)點(diǎn)以外的非葉子結(jié)點(diǎn)，至少含有M/2個(gè)兒子，確保了結(jié)點(diǎn)的至少利用率。

所以B-樹(shù)的性能總是等價(jià)于二分查找（與M值無(wú)關(guān)），也就沒(méi)有B樹(shù)平衡的問(wèn)題；

由于M/2的限制，在插入結(jié)點(diǎn)時(shí)，如果結(jié)點(diǎn)已滿，需要將結(jié)點(diǎn)分裂為兩個(gè)各占M/2的結(jié)點(diǎn)；刪除結(jié)點(diǎn)時(shí)，需將兩個(gè)不足M/2的兄弟結(jié)點(diǎn)合并；

B+樹(shù)

B+樹(shù)是B-樹(shù)的變體，也是一種多路搜索樹(shù)：

1、其定義基本與B-樹(shù)同，除了：

2、非葉子結(jié)點(diǎn)的子樹(shù)指針與關(guān)鍵字個(gè)數(shù)相同；

3、非葉子結(jié)點(diǎn)的子樹(shù)指針P[i]，指向關(guān)鍵字值屬于[K[i], K[i+1])的子樹(shù)（B-樹(shù)是開(kāi)區(qū)間）；

5、為所有葉子結(jié)點(diǎn)增加一個(gè)鏈指針；

6、所有關(guān)鍵字都在葉子結(jié)點(diǎn)出現(xiàn)；

如：（M=3）

B+的搜索與B-樹(shù)也基本相同，區(qū)別是B+樹(shù)只有達(dá)到葉子結(jié)點(diǎn)才命中（B-樹(shù)可以在

非葉子結(jié)點(diǎn)命中），其性能也等價(jià)于在關(guān)鍵字全集做一次二分查找；

B+的特性：

1、所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中（稠密索引），且鏈表中的關(guān)鍵字恰好是有序的；

2、不可能在非葉子結(jié)點(diǎn)命中；

3、非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引（稀疏索引），葉子結(jié)點(diǎn)相當(dāng)于是存儲(chǔ)（關(guān)鍵字）數(shù)據(jù)的數(shù)據(jù)層；

4、更適合文件索引系統(tǒng)；

了解B-/B+樹(shù)的概念之后，我們繼續(xù)分析B+樹(shù)提高效率的原理。

三、B+樹(shù)索引原理

如上圖，是一顆b+樹(shù)，關(guān)于b+樹(shù)的定義可以參見(jiàn)B+樹(shù)，這里只說(shuō)一些重點(diǎn)，淺藍(lán)色的塊我們稱之為一個(gè)磁盤(pán)塊，可以看到每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤(pán)塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3，P1表示小于17的磁盤(pán)塊，P2表示在17和35之間的磁盤(pán)塊，P3表示大于35的磁盤(pán)塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。

b+樹(shù)的查找過(guò)程

如圖所示，如果要查找數(shù)據(jù)項(xiàng)29，那么首先會(huì)把磁盤(pán)塊1由磁盤(pán)加載到內(nèi)存，此時(shí)發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤(pán)塊1的P2指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P(pán)的IO）可以忽略不計(jì)，通過(guò)磁盤(pán)塊1的P2指針的磁盤(pán)地址把磁盤(pán)塊3由磁盤(pán)加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤(pán)塊3的P2指針，通過(guò)指針加載磁盤(pán)塊8到內(nèi)存，發(fā)生第三次IO，同時(shí)內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO。真實(shí)的情況是，3層的b+樹(shù)可以表示上百萬(wàn)的數(shù)據(jù)，如果上百萬(wàn)的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒(méi)有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬(wàn)次的IO，顯然成本非常非常高。

b+樹(shù)性質(zhì)

1、通過(guò)上面的分析，我們知道IO次數(shù)取決于b+數(shù)的高度h，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N，每個(gè)磁盤(pán)塊的數(shù)據(jù)項(xiàng)的數(shù)量是m，則有h=㏒(m+1)N，當(dāng)數(shù)據(jù)量N一定的情況下，m越大，h越?。欢鴐 = 磁盤(pán)塊的大小 / 數(shù)據(jù)項(xiàng)的大小，磁盤(pán)塊的大小也就是一個(gè)數(shù)據(jù)頁(yè)的大小，是固定的，如果數(shù)據(jù)項(xiàng)占的空間越小，數(shù)據(jù)項(xiàng)的數(shù)量越多，樹(shù)的高度越低。這就是為什么每個(gè)數(shù)據(jù)項(xiàng)，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。這也是為什么b+樹(shù)要求把真實(shí)的數(shù)據(jù)放到葉子節(jié)點(diǎn)而不是內(nèi)層節(jié)點(diǎn)，一旦放到內(nèi)層節(jié)點(diǎn)，磁盤(pán)塊的數(shù)據(jù)項(xiàng)會(huì)大幅度下降，導(dǎo)致樹(shù)增高。當(dāng)數(shù)據(jù)項(xiàng)等于1時(shí)將會(huì)退化成線性表。

2、當(dāng)b+樹(shù)的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候，b+數(shù)是按照從左到右的順序來(lái)建立搜索樹(shù)的，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來(lái)檢索的時(shí)候，b+樹(shù)會(huì)優(yōu)先比較name來(lái)確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒(méi)有name的數(shù)據(jù)來(lái)的時(shí)候，b+樹(shù)就不知道下一步該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂?shù)的時(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來(lái)搜索才能知道下一步去哪里查詢。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來(lái)檢索時(shí)，b+樹(shù)可以用name來(lái)指定搜索方向，但下一個(gè)字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性。

慢查詢優(yōu)化

關(guān)于MySQL索引原理是比較枯燥的東西，大家只需要有一個(gè)感性的認(rèn)識(shí)，并不需要理解得非常透徹和深入。我們回頭來(lái)看看一開(kāi)始我們說(shuō)的慢查詢，了解完索引原理之后，大家是不是有什么想法呢？先總結(jié)一下索引的幾大基本原則

四、建索引的幾大原則

1、最左前綴匹配原則，非常重要的原則，mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a 1="" and="" b="2" c=""> 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

2、=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式

3、盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會(huì)問(wèn)，這個(gè)比例有什么經(jīng)驗(yàn)值嗎？使用場(chǎng)景不同，這個(gè)值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄

4、索引列不能參與計(jì)算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡(jiǎn)單，b+樹(shù)中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語(yǔ)句應(yīng)該寫(xiě)成create_time = unix_timestamp(’2014-05-29’);

5、盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來(lái)的索引即可

到此，相信大家對(duì)“MySQL索引的使用原則”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！