InnoDB主鍵索引樹和二級索引樹的示例分析

這篇文章將為大家詳細(xì)講解有關(guān)InnoDB主鍵索引樹和二級索引樹的示例分析，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

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

場景1：主鍵索引樹

uid是主鍵，其他字段沒有添加任何索引

select * from student;

如果是這樣查詢，這表示整表搜索，從左到右遍歷葉子節(jié)點鏈表，從小到大訪問

select * from student where uid<5;

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

select * from student where uid=5;

如果是這樣查詢，這表示等值查詢，在索引樹上進行二分查找即可

由于name沒有索引，于是做整表搜索

select * from student where name='linfeng';

場景2：二級索引樹

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

以name為索引構(gòu)建的索引樹，稱為輔助索引樹，也叫做二級索引樹。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字段構(gòu)建的二級索引樹上去查找。但二級索引樹沒有linfeng這個人所有的信息，所以完整的查詢過程應(yīng)該是這樣的：

• 用linfeng到二級索引樹上進行匹配，拿到二級索引樹上存儲的uid

• 然后拿著這個uid去主索引樹上去匹配，最后拿到linfeng的所有信息（回表）

而這個回表意味著更多的磁盤I/O，會影響效率，如果業(yè)務(wù)只需要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查詢到的結(jié)果是多個，然而name此時是沒有順序的，所以還需要再進行外部排序。

那能不能通過給name加載索引來解決問題呢？

不能，因為一次SQL執(zhí)行只能用到1個索引，搜索了這個字段的索引樹就不會再去搜索另一個字段的索引樹了，因為加載索引是要耗費磁盤I/O的，查找多個索引樹就太慢了！

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

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

再次select *

這時候就使用到聯(lián)合索引了，而且沒有using filesort，這次是這樣查詢的：

先用age=20在輔助索引樹上查找，如果數(shù)據(jù)足夠會找到多個結(jié)果，這個結(jié)果就是已經(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

關(guān)于“InnoDB主鍵索引樹和二級索引樹的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。