快捷導(dǎo)航

InnoDB主鍵索引樹(shù)和二級(jí)索引樹(shù)的場(chǎng)景分析

更新時(shí)間：2022年03月11日 10:44:36 作者：BugMaker-shen

本文通過(guò)場(chǎng)景分析給大家介紹了InnoDB主鍵索引樹(shù)和二級(jí)索引樹(shù)的問(wèn)題,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

我們這里討論InnoDB存儲(chǔ)引擎，數(shù)據(jù)和索引存儲(chǔ)在同一個(gè)文件student.ibd

場(chǎng)景1：主鍵索引樹(shù)

uid是主鍵，其他字段沒(méi)有添加任何索引

select * from student;

如果是這樣查詢(xún)，這表示整表搜索，從左到右遍歷葉子節(jié)點(diǎn)鏈表，從小到大訪(fǎng)問(wèn)

select * from student where uid<5;

如果是這樣查詢(xún)，這表示范圍查詢(xún)，就直接在有序鏈表中遍歷搜索就可以了，直到遍歷到第一個(gè)不小于5的key結(jié)束遍歷

select * from student where uid=5;

如果是這樣查詢(xún)，這表示等值查詢(xún)，在索引樹(shù)上進(jìn)行二分查找即可

由于name沒(méi)有索引，于是做整表搜索

select * from student where name='linfeng';

場(chǎng)景2：二級(jí)索引樹(shù)

uid是主鍵，以name創(chuàng)建了普通索引（二級(jí)索引）

以name為索引構(gòu)建的索引樹(shù)，稱(chēng)為輔助索引樹(shù)，也叫做二級(jí)索引樹(shù)。key是輔助索引字段name的值，然后還有外加uid主鍵的值

在輔助索引樹(shù)上，key是輔助索引的值，也就是name；data數(shù)據(jù)值是所在記錄行的主鍵值（PRIMARY KEY），也就是uid（并不是表的一行數(shù)據(jù)）

分析語(yǔ)句1：

select name from student where name='linfeng';

因?yàn)檫^(guò)濾字段是name且 只select了name一個(gè)字段，name有索引，索引樹(shù)上直接就有，所以從name的二級(jí)索引樹(shù)上去等值匹配linfeng

分析語(yǔ)句2：

select uid,name from student where name='linfeng';

這種情況select的是name和uid，而這些在二級(jí)索引樹(shù)上也是直接就有，所以搜索二級(jí)索引樹(shù)就完事了。

分析語(yǔ)句3：

select * from student where name='linfeng';

這種情況下就涉及到回表了，這是一個(gè)很重要的概念。由于name字段有索引，所以我們會(huì)到name字段構(gòu)建的二級(jí)索引樹(shù)上去查找。但二級(jí)索引樹(shù)沒(méi)有linfeng這個(gè)人所有的信息，所以完整的查詢(xún)過(guò)程應(yīng)該是這樣的：

用linfeng到二級(jí)索引樹(shù)上進(jìn)行匹配，拿到二級(jí)索引樹(shù)上存儲(chǔ)的uid
然后拿著這個(gè)uid去主索引樹(shù)上去匹配，最后拿到linfeng的所有信息（回表）

而這個(gè)回表意味著更多的磁盤(pán)I/O，會(huì)影響效率，如果業(yè)務(wù)只需要uid、name，就別寫(xiě)select *了，這樣可以避免回表

分析語(yǔ)句4：

我們刪除name的索引后執(zhí)行以下語(yǔ)句

select * from student where age=20 order by name;

沒(méi)有用到索引，還使用外部排序了。此外我們還看到using filesort，這時(shí)需要優(yōu)化了。

我們的過(guò)濾條件是age，先給age添加索引，看看行不行

可以看到，age命中索引了，查詢(xún)age所在的索引樹(shù)。由于我們寫(xiě)的是select *，依然存在回表。還有using filesort，因?yàn)槭褂胊ge=20查詢(xún)到的結(jié)果是多個(gè)，然而name此時(shí)是沒(méi)有順序的，所以還需要再進(jìn)行外部排序。

那能不能通過(guò)給name加載索引來(lái)解決問(wèn)題呢？

不能，因?yàn)橐淮蜸QL執(zhí)行只能用到1個(gè)索引，搜索了這個(gè)字段的索引樹(shù)就不會(huì)再去搜索另一個(gè)字段的索引樹(shù)了，因?yàn)榧虞d索引是要耗費(fèi)磁盤(pán)I/O的，查找多個(gè)索引樹(shù)就太慢了！

分析：既然索引樹(shù)上只能存自己建立的索引字段以及主鍵，那我們把需要查詢(xún)的字段都設(shè)置成索引不就好了？

解決方法：我們可以在二級(jí)索引樹(shù)上的key：age+name，形成聯(lián)合索引，先按age排序，age相同了，再按name排序