InnoDB主鍵索引樹和二級索引樹的場景分析

2022-03-11 13:01:31

我們這裡討論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';

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

而這個回表意味著更多的磁碟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)聯合索引的情況下，以下操作不回表（到二級索引樹上搜尋，再去主索引樹上搜尋）：

以下操作要回表

到此這篇關於InnoDB主鍵索引樹和二級索引樹的文章就介紹到這了,更多相關InnoDB索引樹內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！