h5打開以查看

“回表查詢”通常發生在使用二級索引（Secondary Index）的查詢中。當查詢所需的數據列并不全部包含在二級索引中時，即使使用了索引，MySQL 也需要根據索引記錄中的主鍵值，回到聚簇索引（Clustered Index）的主鍵 B+Tree 中去查找完整的行數據，這個過程就叫做“回表”。

---

核心方法：使用?EXPLAIN?命令

檢測回表查詢最主要、最直接的工具就是 MySQL 的?EXPLAIN?命令。你需要重點關注?EXPLAIN?輸出中的以下幾個字段：

1.?type?(訪問類型)

這個字段顯示了 MySQL 決定如何查找表中的行。

• eq_ref，?ref，?range，?index_scan： 這些通常是好的類型，表示使用了索引的有效查找。

• index：?這是一個關鍵信號！?type = index?通常意味著 MySQL 正在掃描整個二級索引（全索引掃描）。這通常發生在需要從索引中獲取大量數據，然后回表的情況下。它比全表掃描（ALL）快，但依然不高效。

• ALL： 最壞的情況，全表掃描，根本沒用上索引。

2.?key?和?key_len?(使用的索引)

• key： 顯示 MySQL 實際決定使用的索引。

• key_len： 顯示使用的索引鍵的長度。通過這個長度，你可以判斷索引是否被完全使用（覆蓋了查詢條件的所有列）。如果?key_len?小于索引定義的長度，說明只使用了索引的前面一部分，這可能不是最優的。

3.?Extra?(額外信息)

這是判斷回表最重要的字段。

• Using index：?這是最理想的情況，表示出現了“覆蓋索引”（Covering Index）。查詢的所有字段都包含在使用的索引中，MySQL 只需要讀取索引即可返回結果，完全不需要回表。這是優化的重要目標。

• Using index condition： 表示使用了 Index Condition Pushdown (ICP) 優化，服務器層將部分條件（WHERE?子句）下推給存儲引擎層進行過濾，但仍然可能需要回表。

• Using where：?這通常意味著需要回表。表示存儲引擎從索引中讀取行后，需要在 MySQL 服務器層再根據?WHERE?條件進行過濾。服務器層過濾的數據，就是已經從聚簇索引中取回的完整行數據。

---

實戰演示：如何一步步檢測

假設我們有一張用戶表?users：

sql

CREATE TABLE `users` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(100) DEFAULT NULL,`age` int DEFAULT NULL,`city` varchar(100) DEFAULT NULL,`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`),KEY `idx_name_age` (`name`,`age`) -- 一個聯合索引
) ENGINE=InnoDB;

場景 1：覆蓋索引，無需回表（理想情況）

sql

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

分析?EXPLAIN?結果：

• type:?ref?(使用了索引等值查詢)

• key:?idx_name_age?(使用了我們創建的聯合索引)

• Extra:?Using index

  • 結論：太好了！查詢的?name?和?age?字段都包含在?idx_name_age?索引中。MySQL 只需讀取索引文件，無需回表，性能極高。

場景 2：需要回表的查詢（常見情況）

sql

EXPLAIN SELECT * FROM users WHERE name = 'John';
-- 或者
EXPLAIN SELECT name, age, city FROM users WHERE name = 'John'; -- city 不在索引中

分析?EXPLAIN?結果：

• type:?ref?(依然使用了索引)

• key:?idx_name_age?(使用了索引來快速定位記錄)

• Extra:?NULL?或者?Using where

  • 結論：發生了回表。idx_name_age?索引中只有?(name, age, id)（id是主鍵，會自動附加到二級索引中），但沒有?city?字段。為了獲取?city?和?created_at?等所有字段，MySQL 必須根據找到的?id?值，回到聚簇索引中去查找完整的行數據。

場景 3：全索引掃描，然后回表

sql

EXPLAIN SELECT * FROM users WHERE age > 20;

分析?EXPLAIN?結果：

• type:?index

• key:?idx_name_age

• Extra:?Using where

  • 結論：這是一個非常典型的低效查詢。我們的索引是?(name, age)，但查詢條件從?age?開始，無法使用索引的最左前綴原則。因此，MySQL 會選擇掃描整個?idx_name_age?索引，對每一條索引記錄檢查?age > 20?的條件，然后為每一個匹配的索引記錄回表獲取完整數據。性能很差。

---

總結：檢測與優化回表查詢的步驟

1. 使用?EXPLAIN： 對任何性能存疑的查詢都使用?EXPLAIN?分析。

2. 查看?Extra?列：

   • 如果看到?Using index，恭喜你，沒有回表。

   • 如果看到?Using where?且?type?是?ref?或?index，很可能發生了回表。

3. 查看?type?列： 如果值是?index，說明正在全索引掃描，通常伴隨著大量回表，需要優化。

4. 優化策略：

   • 創建覆蓋索引： 如果回表查詢很頻繁，考慮創建一個“覆蓋索引”，將查詢中涉及的所有字段（SELECT 和 WHERE 中的字段）都包含在索引中。例如，對于?SELECT id, name, city FROM users WHERE name = ?，可以創建索引?(name, city)?或?(name, city, id)?來覆蓋查詢。

   • 避免?SELECT *： 只獲取你真正需要的列，減少需要回表獲取的數據量，也更容易實現覆蓋索引。

   • 使用索引的最左前綴原則： 確保查詢條件能有效利用索引。

通過系統性地使用?EXPLAIN?并關注上述關鍵字段，你可以準確地識別和優化數據庫中的回表查詢，從而極大提升查詢性能。

h5打開以查看