MySQL 的 回表（Back to Table） 是指在使用 二級索引（非主鍵索引） 查詢數據時，需要通過索引找到主鍵值，再根據主鍵值回到主鍵索引（聚集索引）中查找完整行數據的過程。回表會增加額外的 I/O 操作，可能影響查詢性能。

1. 回表發生的原因

MySQL 的索引結構決定了回表的必要性：

• 主鍵索引（聚集索引）：葉子節點存儲完整的行數據。
• 二級索引（非主鍵索引）：葉子節點存儲主鍵的值（而不是行數據）。

當使用二級索引查詢時，若需要獲取的字段不在二級索引中，則必須通過主鍵值回到主鍵索引中查找完整數據，這就是回表。

2. 回表示例

假設有一張用户表 user，結構如下：

CREATE TABLE user (
    id INT PRIMARY KEY,          -- 主鍵索引（聚集索引）
    name VARCHAR(20),
    age INT,
    INDEX idx_age (age)          -- 二級索引（非主鍵索引）
);

場景 1：觸發回表

執行查詢：

SELECT * FROM user WHERE age = 25;

執行過程：

1. 通過二級索引 idx_age 找到 age=25 對應的主鍵值 id。
2. 根據主鍵值 id 回到主鍵索引中查找完整的行數據（包括 name 和 id）。

問題：由於 idx_age 索引未包含 name 字段，必須回表查詢完整數據。

場景 2：避免回表（覆蓋索引）

執行查詢：

SELECT id, age FROM user WHERE age = 25;

執行過程：

1. 通過二級索引 idx_age 找到 age=25 對應的主鍵值 id。
2. 由於 id 和 age 均存在於 idx_age 索引中，無需回表，直接返回結果。

優化：通過 覆蓋索引（Covering Index） 避免回表。

3. 如何判斷是否發生回表？

通過 EXPLAIN 查看執行計劃：

• 如果 Extra 列顯示 Using index，説明查詢使用了覆蓋索引，未發生回表。
• 如果 Extra 列顯示 Using index condition 或為空，説明需要回表。

示例：

EXPLAIN SELECT id, age FROM user WHERE age = 25;  -- Using index（覆蓋索引）
EXPLAIN SELECT * FROM user WHERE age = 25;        -- 無 Using index（需要回表）

4. 如何避免回表？

方法 1：使用覆蓋索引

確保查詢的字段全部包含在索引中：

-- 創建聯合索引（覆蓋 age 和 name）
ALTER TABLE user ADD INDEX idx_age_name (age, name);

-- 查詢時直接使用索引中的字段
SELECT age, name FROM user WHERE age = 25;  -- 無需回表

方法 2：減少查詢字段

僅查詢必要的字段，避免 SELECT *：

-- 回表
SELECT * FROM user WHERE age = 25;

-- 避免回表（僅查詢索引字段）
SELECT id, age FROM user WHERE age = 25;

方法 3：索引下推（Index Condition Pushdown, ICP）

在 MySQL 5.6+ 中，索引下推可以將過濾條件下推到存儲引擎層，減少回表次數（但無法完全避免回表）：

-- 假設索引為 idx_age_name (age, name)
SELECT * FROM user WHERE age = 25 AND name LIKE '張%';

-- 存儲引擎層直接過濾 name，減少回表次數

5. 回表的性能影響

• 少量數據：回表對性能影響較小。
• 大量數據：頻繁回表會導致大量隨機 I/O，顯著降低查詢速度。
  優化建議：對高頻查詢的核心字段建立覆蓋索引。

總結

理解回表機制是 SQL 優化的關鍵一步，合理設計索引可以顯著提升查詢性能。