什么是回表查詢？

回表查詢（Table Lookup或Back to Table）是數(shù)據(jù)庫(kù)查詢中的一個(gè)過(guò)程，指在使用非聚集索引（Secondary Index或Non-Clustered Index）定位數(shù)據(jù)時(shí)，由于索引節(jié)點(diǎn)中不包含查詢所需的全部列，數(shù)據(jù)庫(kù)需要根據(jù)索引找到數(shù)據(jù)行的位置（通常是主鍵或行標(biāo)識(shí)符），然后回到聚集索引或數(shù)據(jù)表中讀取完整的數(shù)據(jù)行。

這種行為通常發(fā)生在查詢的字段未被索引覆蓋，索引不足以直接滿足查詢需求時(shí)。例如，在MySQL中，如果索引列無(wú)法完全滿足查詢字段，則數(shù)據(jù)庫(kù)會(huì)通過(guò)索引找到記錄位置后回表讀取非索引列的數(shù)據(jù)。

如果對(duì)上面的概念理解的還不夠透徹，先不著急，我們下面逐步拆解，并通過(guò)案例逐步分析講解。

回表查詢發(fā)生的過(guò)程

這里我們以 MySQL 的InnoDB存儲(chǔ)引擎為例來(lái)進(jìn)行講解。要想理解回表查詢的過(guò)程，首先需要了解InnoDB的兩種類型索引——聚集索引（Clustered Index）和非聚集索引（Secondary Index）。

聚集索引（Clustered Index）

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

• 如果表定義了主鍵（Primary Key），則主鍵默認(rèn)就是聚集索引；
• 如果表未定義主鍵，但存在非空的唯一索引（NOT NULL UNIQUE），則第一個(gè)滿足條件的唯一索引將被用作聚集索引；
• 如果上述條件都不滿足，InnoDB會(huì)自動(dòng)創(chuàng)建一個(gè)隱藏的 row_id 列作為聚集索引。

非聚集索引（Secondary Index）

非聚集索引，也稱為普通索引或二級(jí)索引，是指除聚集索引之外的其他索引。在InnoDB中，非聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的是索引鍵值和其對(duì)應(yīng)的聚集索引鍵值（而不是行指針）。這與MyISAM不同，MyISAM的普通索引葉子節(jié)點(diǎn)存儲(chǔ)的是記錄指針而非主鍵值。

在補(bǔ)充了InnoDB引擎的聚集索引和非聚集索引理論之后，下面我們來(lái)看回表查詢的過(guò)程。

回表查詢的過(guò)程

當(dāng)一個(gè)查詢使用非聚集索引時(shí)，數(shù)據(jù)庫(kù)會(huì)先通過(guò)非聚集索引找到符合條件的記錄。非聚集索引的葉子節(jié)點(diǎn)包含索引鍵值以及對(duì)應(yīng)的聚集索引鍵值（主鍵值）。

如果查詢需要的字段不完全在非聚集索引中，則數(shù)據(jù)庫(kù)引擎會(huì)根據(jù)非聚集索引中的聚集索引鍵值，再通過(guò)聚集索引定位到完整的行記錄，以獲取查詢所需的字段數(shù)據(jù)。這種操作過(guò)程，就是回表查詢的基本過(guò)程。

需要注意，回表查詢發(fā)生的場(chǎng)景是很常見(jiàn)的，尤其是當(dāng)查詢字段包含不在非聚集索引中的列時(shí)（即非覆蓋索引的情況）。在接下來(lái)的案例中，我們來(lái)看看哪些場(chǎng)景會(huì)發(fā)生回表，哪些場(chǎng)景又不會(huì)發(fā)生回表。

案例場(chǎng)景

1. 根據(jù)主鍵查詢，不會(huì)回表

使用表的主鍵（聚集索引）查詢數(shù)據(jù)，不會(huì)發(fā)生回表操作：

SELECT * FROM users WHERE id = 3;

根據(jù)關(guān)于聚集索引的理論，由于聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的是完整的行記錄，所以不需要進(jìn)行回表。

2. 索引列和查詢字段不匹配

如果查詢中使用了索引列，但查詢結(jié)果中還包括非索引列（字段不完全在索引），依然會(huì)觸發(fā)回表。例如：

假設(shè)有以下索引覆蓋：

CREATE INDEX idx_name ON users (name);

查詢：

SELECT name, age FROM users WHERE name = 'John';

盡管索引可以快捷地定位記錄，但如果查詢的 age 列不在索引中，會(huì)回表讀取數(shù)據(jù)。

3. 存在覆蓋索引但查詢的字段超出覆蓋范圍

覆蓋索引指的是索引本身已經(jīng)完全包含了查詢所需的字段，在這種情況下不會(huì)發(fā)生回表查詢；否則會(huì)發(fā)生。

例如，在 users 表中創(chuàng)建以下覆蓋索引：

CREATE INDEX idx_users_name_age ON users (name, age);

對(duì)于如下查詢：

SELECT name, age FROM users WHERE name = 'John';

索引 idx_users_name_age 已經(jīng)覆蓋了 name 和 age，索引本身已經(jīng)可以滿足查詢結(jié)果了，此時(shí)不會(huì)產(chǎn)生回表。而以下查詢：

SELECT name, age, address FROM users WHERE name = 'John';

因?yàn)椴樵兊淖侄?address 不在索引中，數(shù)據(jù)庫(kù)需要通過(guò)索引定位到數(shù)據(jù)表中的記錄，再去基表檢索 address 數(shù)據(jù)，從而觸發(fā)回表。

如何避免回表查詢

既然我們已經(jīng)了解了回表查詢的存在，那么就需要防微杜漸。通常，為了優(yōu)化查詢性能，減少回表查詢，可以嘗試以下方法：

1. 創(chuàng)建覆蓋索引

盡量創(chuàng)建覆蓋索引，使查詢所需的字段盡可能包含在索引中。例如，如果經(jīng)常查詢某些字段，可以在它們上創(chuàng)建聯(lián)合索引：

CREATE INDEX idx_users_name_age ON users (name, age);

覆蓋索引之所以能夠避免回表，是因?yàn)橹恍枰谝豢盟饕龢?shù)上就能獲取SQL所需的所有列數(shù)據(jù)，就無(wú)需回表查詢了。常見(jiàn)的方法就是將被查詢的字段，建立到聯(lián)合索引中。

2. 減少查詢字段

對(duì)于性能敏感的場(chǎng)景，可以減少查詢中不必要的字段，只使用關(guān)鍵字段，以便避免索引范圍之外的值返回到基表。這個(gè)最常見(jiàn)的建議就是盡量少用SELECT * FROM來(lái)查詢，而是需要什么字段只查對(duì)應(yīng)字段。

3. 分析執(zhí)行計(jì)劃

使用數(shù)據(jù)庫(kù)的執(zhí)行計(jì)劃工具（如MySQL的 EXPLAIN）分析查詢性能，確認(rèn)是否發(fā)生了回表查詢，可以據(jù)此優(yōu)化索引設(shè)計(jì)和查詢模式。

總結(jié)

回表查詢是由于索引無(wú)法完全覆蓋查詢字段而發(fā)生的數(shù)據(jù)表回查行為。在優(yōu)化查詢時(shí)，可以通過(guò)創(chuàng)建覆蓋索引或減少查詢字段的方式來(lái)盡量避免回表查詢，從而提高性能。分析執(zhí)行計(jì)劃是確定是否發(fā)生回表的有效手段。

以上就是MySQL中回表查詢避免和優(yōu)化指南的詳細(xì)內(nèi)容，更多關(guān)于MySQL回表查詢避免和優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論