什么是回表查詢？

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

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

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

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

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

聚集索引（Clustered Index）

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

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

非聚集索引（Secondary Index）

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

在補充了InnoDB引擎的聚集索引和非聚集索引理論之后，下面我們來看回表查詢的過程。

回表查詢的過程

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

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

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

案例場景

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

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

SELECT * FROM users WHERE id = 3;

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

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

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

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

CREATE INDEX idx_name ON users (name);

查詢：

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

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

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

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

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

CREATE INDEX idx_users_name_age ON users (name, age);

對于如下查詢：

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

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

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

因為查詢的字段 address 不在索引中，數(shù)據(jù)庫需要通過索引定位到數(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);

覆蓋索引之所以能夠避免回表，是因為只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)，就無需回表查詢了。常見的方法就是將被查詢的字段，建立到聯(lián)合索引中。

2. 減少查詢字段

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

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

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

總結(jié)

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

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

最新評論