一、背景

先要從 InnoDB 的索引實現說起，InnoDB 有兩大類索引：

• 聚集索引 (clustered index)
• 普通索引 (secondary index)

InnoDB 聚集索引和普通索引有什么差異？

InnoDB 普通索引 的葉子節(jié)點存儲主鍵值。

注意：只有 InnoDB 普通索引才存儲主鍵值，MyISAM 的二級索引都是直接指向數據塊的。

InnoDB 聚集索引 的葉子節(jié)點存儲行記錄，因此，InnoDB 必須要有，且只有一個聚集索引：

如果表定義了主鍵，則主鍵就是聚集索引；

如果表沒有定義主鍵，則第一個 not null 的 unique 列是聚集索引；

否則，InnoDB 會創(chuàng)建一個隱藏的 row-id 作為聚集索引；

注意：所以主鍵查詢非?？欤苯佣ㄎ恍杏涗洝?/p>

二、什么是回表查詢？

通俗的講就是，如果索引的列在 select 所需獲得的列中（因為在 mysql 中索引是根據索引列的值進行排序的，所以索引節(jié)點中存在該列中的部分值）或者根據一次索引查詢就能獲得記錄就不需要回表，如果 select 所需獲得列中有大量的非索引列，索引就需要到表中找到相應的列的信息，這就叫回表。

InnoDB聚集索引的葉子節(jié)點存儲行記錄，因此， InnoDB必須要有，且只有一個聚集索引：

（1）如果表定義了主鍵，則PK就是聚集索引；
（2）如果表沒有定義主鍵，則第一個非空唯一索引（not NULL unique）列是聚集索引；
（3）否則，InnoDB會創(chuàng)建一個隱藏的row-id作為聚集索引；

三、可以舉一個簡單的例子

我有一張用于用戶登錄的user表：

假如現在有一個用戶名為admin，密碼為123的用戶要登錄，那我會先找出username為admin的那條用戶數據

SELECT * FROM user WHERE username = 'admin'

再根據查出來的user信息去對比密碼是否正確

這時你發(fā)現username字段是唯一的又經常作為where條件所以可以給username字段建一個索引，于是就給username建了一個普通的B+Tree索引。

這時候就出問題的，因為MySQL的InnoDB使用聚簇索引，具體的數據只和主鍵索引放在一起，其他的索引只存儲了數據的地址（主鍵id）。

比如上面的例子中，我根據username索引找到的只是一個username為admin這條數據的id而不是這條數據信息，所以要找到整條數據信息要根據得到的id再去找。

看完上面的流程，你應該已經發(fā)現問題了，我要通過username找到id，再根據id找整條數據，這里有兩個查找過程，這是影響效率的。就像上面的兩個查找過程就是回表了。

四、解決辦法

使用覆蓋索引可以解決上面所說的回表的問題。
還是拿上面上面登錄的例子來說，其實登錄只需要判斷用戶名和密碼，如果user表中有其他用戶信息也是不需要的那我們能不能只查詢一次就找到這個用戶名對應的密碼呢。

這個是可以的，上面所說的分兩步查找，第一步根據username查找是肯定不能少的，那我們只要把password和索引username放到一起就可以了。我們可以建立一個（username、password）的組合索引，這里username一定要放在前面，然后我們把sql語句改一下

SELECT username, password FROM user WHERE username = 'admin'

或

SELECT password FROM user WHERE username = 'admin'

這樣建立組合索引后根據username查找password，只要一步查找就可以查找到，因為password已經是username索引的一部分了，直接可以查出來，不再需要通過id找對應的整條數據。覆蓋索引就是覆蓋了多個列（字段）的索引。

五、更多如下圖：

（1）先通過普通索引定位到主鍵值id=5；
（2）在通過聚集索引定位到行記錄；

這就是所謂的回表查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。

六、總結

使用聚集索引（主鍵或第一個唯一索引）就不會回表，普通索引就會回表。

到此這篇關于一文弄懂什么是MySQL的回表的文章就介紹到這了,更多相關MySQL 回表內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論