聯合索引 abc 均范圍掃描時的索引生效情況

場景：表 t1 建立聯合索引 (a, b, c)，在 where a < ? and b > ? and c < ? 中哪些索引生效。

無回表 + 表數據量非常少

場景準備：聯合索引 (a, b, c) 已經是完整的數據記錄，可以使用覆蓋索引，表數據量非常少的意思是只有 0 或 1 條記錄（測試發現）。

DROP TABLE IF EXISTS t1;CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY,a INT NOT NULL,b INT NOT NULL,c INT NOT NULL,INDEX idx_a_b_c(a, b, c)
);drop procedure if exists GenerateTestData;DELIMITER //CREATE PROCEDURE GenerateTestData()
BEGINDECLARE i INT DEFAULT 0;WHILE i < 1 DOINSERT INTO t1 (a, b, c)VALUES (FLOOR(1 + RAND() * 100),  -- 生成 1-100 的隨機整數FLOOR(1 + RAND() * 100),FLOOR(1 + RAND() * 100));SET i = i + 1;END WHILE;
END //DELIMITER ;call GenerateTestData;

查看當前表信息：

SELECT * FROM t1;

接著分析此場景下的聯合索引生效情況：

EXPLAIN
SELECT *
FROM t1
WHERE a < 2 AND b > 3 AND c < 5; 

分析：

• type = index 表示可以使用覆蓋索引，但需要掃描全部的索引記錄。
• key = idx_a_b_c 表示實際用到的索引為聯合索引 (a, b, c)。
• key_len = 12 表示實際用到的索引長度（字節數）為 12，這是衡量聯合索引字段生效的重要參考。
• Extra = Using where 表示在 Server 層進行了條件過濾。
• Extra = Using index 表示使用到了覆蓋索引。

🤔 為什么這個 where 條件明明不滿足最左前綴原則，key_len 的長度還為 12 呢？

首先說明的是，a、b、c 都是 4 字節的 int 類型，因此 $索引字段數\times 字段長度=3\times 4=12=key\_len$ 說明實際用到了 a、b、c。但這并不表明 a、b、c 索引生效！因為 type = index 表示優化器選擇了全索引掃描（遍歷整個索引），所以才呈現了 key_len = 12 的情況。

也就是說，a、b、c 沒有一個索引生效，即沒有在存儲引擎層利用索引進行條件過濾，實際的條件過濾是由 Server 層進行的。

為了進一步驗證，還可以使用 EXPLAIN ANALYZE。EXPLAIN ANALYZE 是 MySQL 8.0.18 及以上版本引入的一個調試工具，用于分析 SQL 查詢的實際執行過程。它不僅顯示優化器預估的執行計劃（類似常規的 EXPLAIN），還會實際執行查詢并返回詳細的運行時統計信息（如實際耗時、處理行數等），幫助開發者精準定位性能瓶頸。

EXPLAIN analyze
SELECT *
FROM t1
WHERE a < 2 AND b > 3 AND c < 5;

分析：

1. Covering 表示使用到了覆蓋索引，需要查詢的所有字段都在索引搜索樹 (a, b, c) 中可以找到，無需回表查詢。
2. index scan 表示進行了全索引掃描。
3. Filter: ((t1.a < 2) and (t1.b > 3) and (t1.c < 5)) 表示存儲引擎層在全索引掃描后，Server 層將結果集在內存中按照 a < 2 AND b > 3 AND c < 5 進行條件過濾。

無回表 + 表數據量多

場景準備：聯合索引 (a, b, c) 已經是完整的數據記錄，可以使用覆蓋索引，表數據量多的意思是多于 1 條記錄（測試發現）。

DROP TABLE IF EXISTS t1;CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY,a INT NOT NULL,b INT NOT NULL,c INT NOT NULL,INDEX idx_a_b_c(a, b, c)
);drop procedure if exists GenerateTestData;DELIMITER //CREATE PROCEDURE GenerateTestData()
BEGINDECLARE i INT DEFAULT 0;WHILE i < 1000 DOINSERT INTO t1 (a, b, c)VALUES (FLOOR(1 + RAND() * 100),  -- 生成 1-100 的隨機整數FLOOR(1 + RAND() * 100),FLOOR(1 + RAND() * 100));SET i = i + 1;END WHILE;
END //DELIMITER ;call GenerateTestData;

接著分析此場景下的聯合索引生效情況：

EXPLAIN
SELECT *
FROM t1
WHERE a < 2 AND b > 3 AND c < 5; 

分析：

• type = range 表示使用索引進行范圍查找。
• key = idx_a_b_c 表示實際用到的索引為聯合索引 (a, b, c)。
• key_len = 4 表示實際用到的索引長度（字節數）為 4，也就是只有 a 字段索引生效。
• Extra = Using where 表示在 Server 層進行了條件過濾。
• Extra = Using index 表示使用到了覆蓋索引。

由于進行了范圍查找，不滿足最左前綴原則，因此只有 a 字段索引生效，后續的 b、c 都未生效，并在 Server 層進行 a、b、c 的條件過濾。

🤔 Server 層為什么還會對 a 進行過濾呢，存儲引擎層不是已經過濾了 a 嗎？

這是因為存儲引擎對 Server 層是“透明”的，Server 層不假設存儲引擎的行為完全可靠，因此會重新驗證數據。

為了進一步驗證，還可以使用 EXPLAIN ANALYZE。

EXPLAIN analyze
SELECT *
FROM t1
WHERE a < 2 AND b > 3 AND c < 5;

分析：

1. Covering 表示使用到了覆蓋索引，需要查詢的所有字段都在索引搜索樹 (a, b, c) 中可以找到，無需回表查詢。
2. range scan ... over (a < 2) 表示對 a < 2 進行了范圍掃描，僅 a 字段索引生效，b、c 未生效。
3. Filter: ((t1.a < 2) and (t1.b > 3) and (t1.c < 5)) 表示存儲引擎層在范圍掃描后，Server 層將結果集在內存中按照 a < 2 AND b > 3 AND c < 5 進行條件過濾。

有回表

場景準備：聯合索引 (a, b, c) 不是完整的數據記錄，需要回表掃描，這里不強調表數據量大小的原因是無論數量為 0、1 或更多都會回表掃描（測試發現）。

DROP TABLE IF EXISTS t1;CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(255) NOT NULL DEFAULT '',a INT NOT NULL,b INT NOT NULL,c INT NOT NULL,INDEX idx_a_b_c(a, b, c)
);DROP PROCEDURE IF EXISTS GenerateTestData;DELIMITER //CREATE PROCEDURE GenerateTestData()
BEGINDECLARE i INT DEFAULT 0;WHILE i < 1000 DOINSERT INTO t1 (a, b, c)VALUES (FLOOR(1 + RAND() * 100),  -- 生成 1-100 的隨機整數FLOOR(1 + RAND() * 100),FLOOR(1 + RAND() * 100));SET i = i + 1;END WHILE;
END //DELIMITER ;CALL GenerateTestData;

接著分析此場景下的聯合索引生效情況：

EXPLAIN
SELECT *
FROM t1
WHERE a < 2 AND b > 3 AND c < 5; 

分析：

• type = range 表示使用索引進行范圍查找。
• key = idx_a_b_c 表示實際用到的索引為聯合索引 (a, b, c)。
• key_len = 4 表示實際用到的索引長度（字節數）為 4，也就是只有 a 字段索引生效。
• Extra = Using index condition 表示在存儲引擎層使用 b、c 進行了索引下推。

由于進行了范圍查找，不滿足最左前綴原則，因此只有 a 字段索引生效，后續的 b、c 都未生效，但由于需要回表查詢，因此還可以使用 b、c 進行索引下推，且在 Server 層不會再進行條件過濾了（因為沒有提示 Extra = Using where）。

為了進一步驗證，還可以使用 EXPLAIN ANALYZE。

EXPLAIN analyze
SELECT *
FROM t1
WHERE a < 2 AND b > 3 AND c < 5;

分析：

1. range scan ... over (a < 2) 表示對 a < 2 進行了范圍掃描，僅 a 字段索引生效，b、c 未生效。
2. index condition: (a < 2 and b > 3 and c < 5) 表示在索引范圍掃描的基礎上，存儲引擎進一步應用索引下推，檢查索引條目是否滿足 a < 2、b > 3 和 c < 5 再進行回表掃描。

🤔 為什么生效的索引字段 a 還會作為索引下推條件呢？

雖然 a < 2 已用于范圍掃描，但 ICP 仍會重新檢查所有下推條件。也就是說，ICP 的條件列表中可能包含了已經被范圍掃描處理的條件，這是因為在索引掃描的過程中，存儲引擎可能需要再次確認這些條件，尤其是在聯合索引中，可能存在多個范圍的條目，需要逐條檢查。

總結

這里只分析了目前能想到的常見情況，其實還有很多，這是由于查詢優化器會對查詢進行優化，包括重寫查詢、決定表的讀寫順序、選擇合適的索引等，綜合考慮數據量、是否回表、回表成本、索引區分度等因素生成查詢成本最小的執行計劃。

對以上的三種情況做一個總結：

1. 無回表 + 表數據量非常少：使用覆蓋索引進行全索引掃描，索引字段 a、b、c 都未生效（未使用索引進行條件過濾）。
2. 無回表 + 表數據量多：使用聯合索引進行范圍查找，但只有索引字段 a 生效，Server 層使用 a、b、c 進行條件過濾（盡管存儲引擎層已經過濾了 a，但 Server 層不認為存儲引擎層行為完全可靠）。
3. 有回表：使用聯合索引進行范圍查找，但只有索引字段 a 生效，存儲引擎層使用 a、b、c 進行索引下推（盡管大多數 ICP 場景只有 b、c 才能真正過濾掉部分數據）。