欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Mysql索引結(jié)合explain分析示例

 更新時(shí)間:2022年02月18日 09:40:59   作者:功夫熊貓阿寶  
本文主要介紹了Mysql索引結(jié)合explain分析示例,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下

簡(jiǎn)介

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

1.索引分類

索引分為聚簇索引非聚簇索引;那么,我們先來探討一下聚簇索引;

聚簇索引

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

首先,他會(huì)看你有沒有定義唯一鍵;如果有唯一鍵,那么就會(huì)把這個(gè)唯一鍵當(dāng)作主鍵來建立索引;如果連唯一鍵也沒有的話,就會(huì)默認(rèn)創(chuàng)建一個(gè)隱藏列 row_id 通過這個(gè)row_id來建立索引;所以,由于有這個(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)來建立索引; 其示意圖如下:

對(duì)于普通的索引來說,采用的也是B+樹結(jié)構(gòu),但是: 對(duì)于 非葉子節(jié)點(diǎn)來說存的是 創(chuàng)建索引的字段(b,c,d) + 指向數(shù)據(jù)指針 對(duì)于葉子節(jié)點(diǎn)來說 存的是 創(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ì)用到;

那么,為了解決回表問題,覆蓋索引來進(jìn)行解決;

為什么選擇B+樹

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

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

舉例說明: 我們假設(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)是千萬條數(shù)據(jù)了)而且,第一層或者第二層非葉子節(jié)點(diǎn)一般是是緩存在內(nèi)存中的,其實(shí)千萬條數(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),我們可以來講解explain了;

這是explain的字段,我記得我剛剛開始的時(shí)候是怎么都記不?。缓髞砗退饕⒙?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)橥ǔ碚f索引文件比數(shù)據(jù)文件小;
all : 全表掃描

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

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

比如說: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)行遍歷 ,比如說:我創(chuàng)建了普通索引 user(姓名,身份證號(hào)) 我想把所有的身份證號(hào)找出來,這個(gè)時(shí)候,就可能使用基于索引樹的全表掃描了,因?yàn)?,索引樹相?duì)來說內(nèi)容小一點(diǎn),如果,全部掃描的話,內(nèi)存中沒有對(duì)應(yīng)數(shù)據(jù)頁(yè)還得都去找出來; 通過,這樣推理,可以感受到 ref > range > index;

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

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

select * from t1 where c = 1;

但是,針對(duì)下面這條語句:從索引層面這個(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)文章希望大家以后多多支持腳本之家!

相關(guān)文章

最新評(píng)論