Mysql索引結(jié)合explain分析示例
簡(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)文章
MySQL中臨時(shí)表的基本創(chuàng)建與使用教程
這篇文章主要介紹了MySQL中臨時(shí)表的基本創(chuàng)建與使用教程,注意臨時(shí)表中數(shù)據(jù)的清空問題,需要的朋友可以參考下2015-12-12MySQL 查找價(jià)格最高的圖書經(jīng)銷商的幾種SQL語句
不同的圖書,在不同的經(jīng)銷商的價(jià)格不同,我們這里要找到每種圖書最高的經(jīng)銷商是誰? 找最低的類似了。2009-07-07MySQL中用戶授權(quán)以及刪除授權(quán)的方法
這篇文章主要介紹了MySQL中用戶授權(quán)以及刪除授權(quán)的方法的相關(guān)資料,需要的朋友可以參考下2015-12-12實(shí)例驗(yàn)證MySQL|update字段為相同的值是否會(huì)記錄binlog
這篇文章主要介紹了實(shí)例驗(yàn)證MySQL|update字段為相同的值是否會(huì)記錄binlog,幫助大家更好的理解和學(xué)習(xí)MySQL數(shù)據(jù)庫(kù),感興趣的朋友可以了解下2020-10-10MySQL對(duì)中文進(jìn)行排序詳解及實(shí)例
這篇文章主要介紹了MySQL對(duì)中文進(jìn)行排序詳解及實(shí)例的相關(guān)資料,需要的朋友可以參考下2017-05-05MySQL高效模糊搜索之內(nèi)置函數(shù)locate instr position find_in_set使用詳解
在MySQL中一般進(jìn)行模糊搜索都是使用LIKE配合通配符進(jìn)行查詢的,在性能上一定的影響,下面給大家分享MYSQL自帶的內(nèi)置模糊搜索函數(shù),除最后一個(gè)外其它三個(gè)性能上要比Like快些2018-09-09MySQL下將一個(gè)表的數(shù)據(jù)插入到另外一個(gè)表的實(shí)現(xiàn)語句
開發(fā)中,我們經(jīng)常需要將一個(gè)表的數(shù)據(jù)插入到另外一個(gè)表,有時(shí)還需要指定導(dǎo)入字段,雖然這個(gè)實(shí)現(xiàn)起來非常簡(jiǎn)單,但是還是會(huì)困擾許多新手,因此專門發(fā)一篇文章備查。2009-09-09SQL?CREATE?INDEX提高數(shù)據(jù)庫(kù)檢索效率的關(guān)鍵步驟詳解
這篇文章主要為大家介紹了SQL?CREATE?INDEX提高數(shù)據(jù)庫(kù)檢索效率的關(guān)鍵步驟詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-12-12MySQL 5.5的max_allowed_packet屬性的修改方法
今天在部署一個(gè)實(shí)驗(yàn)系統(tǒng)的時(shí)候,報(bào)錯(cuò)提示需要修改一下MySQL的配置文件,在修改的時(shí)候是有技巧的,大家可以參考下本文嘗試操作下2013-08-08