簡介

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

1.索引分類

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

聚簇索引

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

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

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

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

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

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

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

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

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

那么，為了解決回表問題，覆蓋索引來進行解決；

為什么選擇B+樹

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

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

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

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

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

綜上： 1.B+樹層級會比較低 2.對范圍查詢效率比較高；

explain

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

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

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

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

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

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

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

select * from t1 where c = 1;

但是，針對下面這條語句：從索引層面這個C是用不上的，從系統(tǒng)性能角度，C又是用的上的，它這叫做索引下推，因為它可以根據(jù)b = 1的雙向鏈表相后推的時候，直接把不符合條件的C排除掉了;不用先回表查出數(shù)據(jù)，在進行排除；

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

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

最新評論