圖片

在開發和運維中，要想設計高效的數據庫系統，要想優化提升SQL查詢性能，都離不開一個理論知識：回表查詢。本篇文章我們以具體的案例來介紹一下MySQL中，回表查詢相關的知識、案例以及優化方案。

現在我們開始。

什么是回表查詢？

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

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

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

回表查詢發生的過程

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

聚集索引（Clustered Index）

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

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

非聚集索引（Secondary Index）

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

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

回表查詢的過程

當一個查詢使用非聚集索引時，數據庫會先通過非聚集索引找到符合條件的記錄。非聚集索引的葉子節點包含索引鍵值以及對應的聚集索引鍵值（主鍵值）。

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

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

案例場景

1. 根據主鍵查詢，不會回表

使用表的主鍵（聚集索引）查詢數據，不會發生回表操作：

SELECT * FROM users WHERE id = 3;

根據關于聚集索引的理論，由于聚集索引的葉子節點存儲的是完整的行記錄，所以不需要進行回表。

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

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

假設有以下索引覆蓋：

CREATE INDEX idx_name ON users (name);

查詢：

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

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

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

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

例如，在 users 表中創建以下覆蓋索引：

CREATE INDEX idx_users_name_age ON users (name, age);

對于如下查詢：

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

索引 idx_users_name_age 已經覆蓋了 name 和 age，索引本身已經可以滿足查詢結果了，此時不會產生回表。而以下查詢：

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

因為查詢的字段 address 不在索引中，數據庫需要通過索引定位到數據表中的記錄，再去基表檢索 address 數據，從而觸發回表。

如何避免回表查詢

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

1. 創建覆蓋索引

盡量創建覆蓋索引，使查詢所需的字段盡可能包含在索引中。例如，如果經常查詢某些字段，可以在它們上創建聯合索引：

CREATE INDEX idx_users_name_age ON users (name, age);

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

2. 減少查詢字段

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

3. 分析執行計劃

使用數據庫的執行計劃工具（如MySQL的 EXPLAIN）分析查詢性能，確認是否發生了回表查詢，可以據此優化索引設計和查詢模式。

總結

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