簡(jiǎn)介

Mysql 在我們項(xiàng)目中使用是非常廣的，當(dāng)我們數(shù)據(jù)量大的時(shí)候，就需要考慮建立索引了，我感覺這也是一種以空間換時(shí)間的方式；在我們查詢的時(shí)候，通過使用索引來(lái)提高速度；那么，我們?cè)谑褂玫倪^程中，怎么判定有沒有走索引呢？有一個(gè)explain語(yǔ)句來(lái)進(jìn)行分析，根據(jù)阿里的Java編程規(guī)范，至少類型要提升到range;我那時(shí)候就在想為什么要提升到range呢？后來(lái)結(jié)合Mysql的索引終于知道explain和Mysql底層B+樹的對(duì)應(yīng)關(guān)系；注：以下內(nèi)容都是基于InnoDB引擎；

1.索引分類

索引分為聚簇索引和非聚簇索引；那么，我們先來(lái)探討一下聚簇索引；

聚簇索引

那么在InnoDB中，如果沒有定義主鍵，那么會(huì)怎么辦？

首先，他會(huì)看你有沒有定義唯一鍵；如果有唯一鍵，那么就會(huì)把這個(gè)唯一鍵當(dāng)作主鍵來(lái)建立索引；如果連唯一鍵也沒有的話，就會(huì)默認(rèn)創(chuàng)建一個(gè)隱藏列 row_id 通過這個(gè)row_id來(lái)建立索引；所以，由于有這個(gè)機(jī)制（這個(gè)機(jī)制是為了配合普通索引的），使用Innodb的話，還是，需要有一個(gè)主鍵 最好是遞增主鍵；不用白不用；（還有，就是主鍵盡量小一點(diǎn)，如果像UUID一樣，問題很多 第一：主鍵被其他普通索引葉子使用，占用空間 第二：插入的時(shí)候，需要隨機(jī)訪問I/O，并且，容易導(dǎo)致頁(yè)分裂）

聚簇索引的結(jié)構(gòu) 假設(shè)，我們主鍵遞增，它的結(jié)構(gòu)示意圖如下：

這是一個(gè)簡(jiǎn)單示意圖：一頁(yè)數(shù)據(jù)能存16k 所以，第一層節(jié)點(diǎn)數(shù)據(jù)肯定比這多多了； 但是，我們可以得到一個(gè)結(jié)論： 對(duì)于非葉子節(jié)點(diǎn)存的是主鍵 + 指針 對(duì)于葉子節(jié)點(diǎn)存的是 主鍵 + 真實(shí)的數(shù)據(jù)；

普通索引的結(jié)構(gòu) 假設(shè)以 create index idx_t1_bcd on t1(b, c, d)來(lái)建立索引; 其示意圖如下：

對(duì)于普通的索引來(lái)說(shuō)，采用的也是B+樹結(jié)構(gòu)，但是： 對(duì)于 非葉子節(jié)點(diǎn)來(lái)說(shuō)存的是 創(chuàng)建索引的字段（b,c,d） + 指向數(shù)據(jù)指針 對(duì)于葉子節(jié)點(diǎn)來(lái)說(shuō) 存的是 創(chuàng)建索引的字段(b,c,d) + 主鍵的指針；

這里由于存的是主鍵的指針出現(xiàn)會(huì)導(dǎo)致回表：普通索引為什么需要這么設(shè)計(jì)（為什么不存數(shù)據(jù)）？ 個(gè)人理解原因如下：

• 如果，普通索引也要存下數(shù)據(jù)的話，那么需要內(nèi)存空間太大了；
• 如果，普通索引也存數(shù)據(jù)的話，當(dāng)發(fā)生修改的話，就需要修改全部的數(shù)據(jù)；

所以，很明顯這個(gè)普通索引是比聚簇索引占用空間小很多的，這個(gè)特性在count(*) 的時(shí)候會(huì)用到；

那么，為了解決回表問題，覆蓋索引來(lái)進(jìn)行解決；

為什么選擇B+樹

剛剛已經(jīng)介紹了Mysql 聚簇索引和普通索引的特征；那么，現(xiàn)在問題來(lái)了？為什么要選擇B+樹呢？

原因：和Mysql的特性所致：針對(duì)磁盤來(lái)說(shuō) IO是它的一大瓶頸，索引的出現(xiàn)是為了快速找到對(duì)應(yīng)的數(shù)據(jù)，所以說(shuō)：IO越少效率越好，（就是磁盤頁(yè)加載到內(nèi)存次數(shù)越少越好） 那么，為什么使用B+樹就會(huì)加載的少呢？

舉例說(shuō)明： 我們假設(shè)待存儲(chǔ)數(shù)據(jù)一行大小是1k; 所以，我們一頁(yè)可以存16行數(shù)據(jù)；假設(shè)我們的主鍵id為bigint類型，長(zhǎng)度為8字節(jié)（如果是int 4字節(jié)），而指針大小為6字節(jié)；一頁(yè) 為 16k 16 * 1024 /14 = 1170，所以，我們非節(jié)點(diǎn)頁(yè)，可以存放1170個(gè)主鍵 + 指針；綜上：如果是2層節(jié)點(diǎn)的B+樹；可以存的數(shù)據(jù)是 1170 * 16 = 18720行數(shù)據(jù)；

那么，如果是三層的B+樹呢？這時(shí)候第一頁(yè)非葉子節(jié)點(diǎn)，可以存1170個(gè)主鍵+ 指針（指向的是非葉子節(jié)點(diǎn)） 第二頁(yè)非葉子節(jié)點(diǎn)，也可以存1170個(gè)主鍵 + 指針（指向葉子節(jié)點(diǎn)）；第三層 每一頁(yè) 可以存16行數(shù)據(jù)；所以，總共可以存 1170 * 1170 * 16行數(shù)據(jù)；（這已經(jīng)是千萬(wàn)條數(shù)據(jù)了）而且，第一層或者第二層非葉子節(jié)點(diǎn)一般是是緩存在內(nèi)存中的，其實(shí)千萬(wàn)條數(shù)據(jù)找一或兩次就可以了；其他的以此類推； 如果，采用B樹，因?yàn)樗姆侨~子節(jié)點(diǎn)中也是存數(shù)據(jù)的層級(jí)會(huì)高過B+樹；

原因2：使用B+樹結(jié)構(gòu)時(shí)，因?yàn)閿?shù)據(jù)是存在葉子節(jié)點(diǎn)中，所以，對(duì)于訪問查詢找到第一個(gè)值，就可以通過葉子節(jié)點(diǎn)的雙向鏈表進(jìn)行遍歷查詢；而如果B樹，就需要采用中序遍歷；

綜上： 1.B+樹層級(jí)會(huì)比較低 2.對(duì)范圍查詢效率比較高；

explain

介紹完Mysql索引結(jié)構(gòu)，我們可以來(lái)講解explain了；

這是explain的字段，我記得我剛剛開始的時(shí)候是怎么都記不??；后來(lái)和索引建立聯(lián)系以后就記住了；
id :表示表的加載順序，id 越大越先查詢 用于大表驅(qū)動(dòng)小表；如果相關(guān)，就從上到下執(zhí)行；
type: 查詢使用了那種類型 從最好到最差 system > const > eq_ref > ref > range > index > ALL;
const：只匹配一次 出行在主鍵索引或者唯一索引
ref : 非唯一性索引掃描，返回匹配某個(gè)單個(gè)值的所在行； 就是通過一個(gè)where 條件找到一條或多條數(shù)據(jù)；
range： 范圍查詢時(shí)使用到；最低標(biāo)準(zhǔn)了；
index: 只遍歷索引樹 比全表掃描好一點(diǎn)點(diǎn) 因?yàn)橥ǔ?lái)說(shuō)索引文件比數(shù)據(jù)文件?。?br />all : 全表掃描

row :通過采用函數(shù)推算出來(lái)的要讀的條數(shù)，涉及索引的選擇，正常情況下誤差不會(huì)很大； extra: Using filesort 文件排序 需要對(duì)找出來(lái)的數(shù)據(jù)進(jìn)行外部排序，不能使用表內(nèi)索引完成排序； 慢 需要優(yōu)化
Using temporary : 使用了臨時(shí)表來(lái)保存中間結(jié)果 更慢 需要優(yōu)化
Using index : 使用了覆蓋索引 Using where 使用了Where 這兩個(gè)不用優(yōu)化
如果，對(duì)Using filesort Using temporary為什么慢感興趣的同學(xué)，可以查看我的另一篇文章 Using filesort Using temporary為什么這么慢

從索引樹的角度分析為什么ref>range>index

比如說(shuō)：where key = 4 這個(gè)時(shí)候，它首先在第一頁(yè)進(jìn)行查找（這里它對(duì)鏈表處理過，引入了數(shù)組，為了查找快速，使用的是二分查找） 然后，找到數(shù)據(jù)指針是0005，所以就去0005數(shù)據(jù)頁(yè)中，進(jìn)行查找，（在頁(yè)中查找也是使用二分查找）找到了第一條數(shù)據(jù)key = 4,然后，只要找下一條，看看是不是key != 4 如果，不等于4，那么，就找完了；這是ref級(jí)別的過程；
然后，where key >= 4;同理，先找到key = 4，然后，因?yàn)榇笥?，所以，就按照葉子節(jié)點(diǎn)中的指針向后找，找到底，這個(gè)是type = range 的情況；
至于 type = index 其實(shí)就是對(duì)整個(gè)索引樹進(jìn)行遍歷 ，比如說(shuō)：我創(chuàng)建了普通索引 user(姓名，身份證號(hào)) 我想把所有的身份證號(hào)找出來(lái)，這個(gè)時(shí)候，就可能使用基于索引樹的全表掃描了，因?yàn)?，索引樹相?duì)來(lái)說(shuō)內(nèi)容小一點(diǎn)，如果，全部掃描的話，內(nèi)存中沒有對(duì)應(yīng)數(shù)據(jù)頁(yè)還得都去找出來(lái)； 通過，這樣推理，可以感受到 ref > range > index；

最左前綴原則理解 我們都知道有最左前綴原則，那么，為什么會(huì)有這個(gè)原則呢？

還是以他為例：因?yàn)锽+樹先是按照b列的值排序的，在b列的值相同的情況下才使用c列進(jìn)行排序；也就是說(shuō)b列的值不同的記錄中c的值可能是無(wú)序的。而現(xiàn)在你跳過b列直接根據(jù)c的值去查找，這是做不到的。 所以說(shuō)：下面sql語(yǔ)句是沒有用的；

select * from t1 where c = 1;

但是，針對(duì)下面這條語(yǔ)句：從索引層面這個(gè)C是用不上的，從系統(tǒng)性能角度，C又是用的上的，它這叫做索引下推，因?yàn)樗梢愿鶕?jù)b = 1的雙向鏈表相后推的時(shí)候，直接把不符合條件的C排除掉了;不用先回表查出數(shù)據(jù)，在進(jìn)行排除；

select * from t1 where b > 1 and c > 1;

到此這篇關(guān)于Mysql索引結(jié)合explain分析示例的文章就介紹到這了,更多相關(guān)Mysql explain 索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論