快捷導(dǎo)航

一文弄懂什么是MySQL的回表

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

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

一、背景

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

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

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

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

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

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

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

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

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

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

二、什么是回表查詢(xún)？

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

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

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

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

我有一張用于用戶(hù)登錄的user表：

字段名	類(lèi)型	說(shuō)明
id	bigint(20)	主鍵ID
username	varchar(20)	用戶(hù)名
password	varchar(20)	密碼

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

SELECT * FROM user WHERE username = 'admin'

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

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

這時(shí)候就出問(wèn)題的，因?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)問(wèn)題了，我要通過(guò)username找到id，再根據(jù)id找整條數(shù)據(jù)，這里有兩個(gè)查找過(guò)程，這是影響效率的。就像上面的兩個(gè)查找過(guò)程就是回表了。

四、解決辦法

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

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

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

或

SELECT password FROM user WHERE username = 'admin'

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