一、背景

先要從 InnoDB 的索引實現(xiàn)說起，InnoDB 有兩大類索引：

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

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

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

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

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

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

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

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

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

二、什么是回表查詢？

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

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

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

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

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

假如現(xiàn)在有一個用戶名為admin，密碼為123的用戶要登錄，那我會先找出username為admin的那條用戶數(shù)據(jù)

SELECT * FROM user WHERE username = 'admin'

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

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

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

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

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

四、解決辦法

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

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

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

或

SELECT password FROM user WHERE username = 'admin'

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

五、更多如下圖：

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

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

六、總結(jié)

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

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

最新評論