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這個人所有的資訊，是以完整的查詢過程應該是這樣的：

1. 用linfeng到二級索引樹上進行比對，拿到二級索引樹上存儲的uid
2. 然後拿着這個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個字段，這樣才能用到索引！

• select age
• select age, name
• select uid,age,name

• select *
• select age,name,sex