InnoDB主鍵索引樹和二級索引樹的場景分析
我們這里討論InnoDB存儲引擎,數(shù)據(jù)和索引存儲在同一個文件student.ibd
場景1:主鍵索引樹
uid是主鍵,其他字段沒有添加任何索引
select * from student;
如果是這樣查詢,這表示整表搜索,從左到右遍歷葉子節(jié)點鏈表,從小到大訪問
select * from student where uid<5;
如果是這樣查詢,這表示范圍查詢,就直接在有序鏈表中遍歷搜索就可以了,直到遍歷到第一個不小于5的key結束遍歷
select * from student where uid=5;
如果是這樣查詢,這表示等值查詢,在索引樹上進行二分查找即可
由于name沒有索引,于是做整表搜索
select * from student where name='linfeng';
場景2:二級索引樹
uid是主鍵,以name創(chuàng)建了普通索引(二級索引)
以name為索引構建的索引樹,稱為輔助索引樹,也叫做二級索引樹。key是輔助索引字段name的值,然后還有外加uid主鍵的值
在輔助索引樹上,key是輔助索引的值,也就是name;data數(shù)據(jù)值是所在記錄行的主鍵值(PRIMARY KEY),也就是uid(并不是表的一行數(shù)據(jù))
分析語句1:
select name from student where name='linfeng';
因為過濾字段是name且 只select了name一個字段,name有索引,索引樹上直接就有,所以從name的二級索引樹上去等值匹配linfeng
分析語句2:
select uid,name from student where name='linfeng';
這種情況select的是name和uid,而這些在二級索引樹上也是直接就有,所以搜索二級索引樹就完事了。
分析語句3:
select * from student where name='linfeng';
這種情況下就涉及到回表了,這是一個很重要的概念。由于name字段有索引,所以我們會到name字段構建的二級索引樹上去查找。但二級索引樹沒有linfeng這個人所有的信息,所以完整的查詢過程應該是這樣的:
- 用linfeng到二級索引樹上進行匹配,拿到二級索引樹上存儲的uid
- 然后拿著這個uid去主索引樹上去匹配,最后拿到linfeng的所有信息(回表)
而這個回表意味著更多的磁盤I/O,會影響效率,如果業(yè)務只需要uid、name,就別寫select *了,這樣可以避免回表
分析語句4:
我們刪除name的索引后執(zhí)行以下語句
select * from student where age=20 order by name;
沒有用到索引,還使用外部排序了。此外我們還看到using filesort,這時需要優(yōu)化了。
我們的過濾條件是age,先給age添加索引,看看行不行
可以看到,age命中索引了,查詢age所在的索引樹。由于我們寫的是select *
,依然存在回表。還有using filesort,因為使用age=20查詢到的結果是多個,然而name此時是沒有順序的,所以還需要再進行外部排序。
那能不能通過給name加載索引來解決問題呢?
不能,因為一次SQL執(zhí)行只能用到1個索引,搜索了這個字段的索引樹就不會再去搜索另一個字段的索引樹了,因為加載索引是要耗費磁盤I/O的,查找多個索引樹就太慢了!
分析:既然索引樹上只能存自己建立的索引字段以及主鍵,那我們把需要查詢的字段都設置成索引不就好了?
解決方法:我們可以在二級索引樹上的key:age+name,形成聯(lián)合索引,先按age排序,age相同了,再按name排序
再次select *
這時候就使用到聯(lián)合索引了,而且沒有using filesort,這次是這樣查詢的:
先用age=20在輔助索引樹上查找,如果數(shù)據(jù)足夠會找到多個結果,這個結果就是已經(jīng)排好序的,不需要再using filesort
我們現(xiàn)在直接用第二個字段name作為過濾條件
我們看到這里沒有用到索引,因為我們用(age,name)創(chuàng)建索引,是先按age排序,再按name排序。如果我們只用name作為過濾條件,這就沒有辦法使用索引匹配了,因為是優(yōu)先用age排序。
所以我們經(jīng)常說,多列索引一定要使用到第1個字段,這樣才能用到索引!
在建立(age,name)聯(lián)合索引的情況下,以下操作不回表(到二級索引樹上搜索,再去主索引樹上搜索):
- select age
- select age, name
- select uid,age,name
以下操作要回表
- select *
- select age,name,sex
到此這篇關于InnoDB主鍵索引樹和二級索引樹的文章就介紹到這了,更多相關InnoDB索引樹內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站!
版權聲明:本站文章來源標注為YINGSOO的內(nèi)容版權均為本站所有,歡迎引用、轉載,請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像,否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學習參考,不代表本站立場,如有內(nèi)容涉嫌侵權,請聯(lián)系alex-e#qq.com處理。