快捷導(dǎo)航

一文弄懂什么是MySQL的回表

更新時(shí)間：2023年06月06日 10:16:20 作者：逆流°只是風(fēng)景-bjhxcc

本文主要介紹了一文弄懂什么是MySQL的回表，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、背景

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

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

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

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

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

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

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

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

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

注意：所以主鍵查詢非常快，直接定位行記錄。

二、什么是回表查詢？

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

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

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

三、可以舉一個(gè)簡單的例子

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

字段名	類型	說明
id	bigint(20)	主鍵ID
username	varchar(20)	用戶名
password	varchar(20)	密碼

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

SELECT * FROM user WHERE username = 'admin'

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

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

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

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

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

四、解決辦法

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

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

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

或

SELECT password FROM user WHERE username = 'admin'

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