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

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

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

場景1：主鍵索引樹

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

select * from student;

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

select * from student where uid<5;

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

select * from student where uid=5;

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

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

select * from student where name='linfeng';

場景2：二級索引樹

uid是主鍵，以name創建了普通索引（二級索引）

以name為索引構建的索引樹，稱為輔助索引樹，也叫做二級索引樹。key是輔助索引字段name的值，然后還有外加uid主鍵的值

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

分析語句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，會影響效率，如果業務只需要uid、name，就別寫select *了，這樣可以避免回表

分析語句4：

我們刪除name的索引后執行以下語句

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

沒有用到索引，還使用外部排序了。此外我們還看到using filesort，這時需要優化了。

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

可以看到，age命中索引了，查詢age所在的索引樹。由于我們寫的是select *，依然存在回表。還有using filesort，因為使用age=20查詢到的結果是多個，然而name此時是沒有順序的，所以還需要再進行外部排序。

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

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

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

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

再次select *

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

先用age=20在輔助索引樹上查找，如果數據足夠會找到多個結果，這個結果就是已經排好序的，不需要再using filesort

我們現在直接用第二個字段name作為過濾條件

我們看到這里沒有用到索引，因為我們用(age,name)創建索引，是先按age排序，再按name排序。如果我們只用name作為過濾條件，這就沒有辦法使用索引匹配了，因為是優先用age排序。

所以我們經常說，多列索引一定要使用到第1個字段，這樣才能用到索引！

在建立(age,name)聯合索引的情況下，以下操作不回表（到二級索引樹上搜索，再去主索引樹上搜索）：

• select age

• select age, name

• select uid,age,name

以下操作要回表

• select *

• select age,name,sex

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