MySQL InnoDB 的索引選擇邏輯常讓開發者困惑:明明有主鍵,`SELECT *` 卻走二級索引?本文從儲存架構、優化器成本模型、叢集索引特性三個維度,深度解析這一行為背後的必然邏輯,並給出強制走主鍵的最佳實踐。
CREATE TABLE test_b (
Id INT NOT NULL,
Type INT DEFAULT NULL,
PRIMARY KEY (Id), -- 叢集索引
KEY IDX_Type (Type) -- 二級索引 (非叢集)
) ENGINE=InnoDB;
INSERT INTO test_b VALUES (1,1),(2,6),(3,2),(7,3),(4,1);
-- 無 Hint 查詢
SELECT * FROM test_b;
-- 結果順序:(1,1), (4,1), (3,2), (7,3), (2,6) ← 依 Type 排序!而非 Id
優化器目標:**最小化總成本 (I/O + CPU)**。對 `SELECT *` (需所有欄位):
SELECT * FROM test_b FORCE INDEX (PRIMARY);
結果按 `Id` 排序:`(1,1), (2,6), (3,2), (7,3), (4,1)`。
適用:臨時查詢、測試、明確知道 PK 掃描更優。
若查詢固定模式 (如常查 `Id, Type`),建立包含所需欄位的二級索引:
CREATE INDEX idx_type_id ON test_b (Type, Id); -- 或 (Id, Type) 視查詢模式
-- SELECT Id, Type FROM test_b; → 直接走 idx_type_id,**無需回表**,順序按索引鍵 (Type, Id)
這是**正規優化手段**,而非 Hint 硬幹。
SELECT * FROM test_b ORDER BY Id; -- 強制排序,優化器可能選 PK 掃描 或 二級索引+Filesort
**語義上正確**:無 `ORDER BY` 時,**結果順序不保證** (僅是當前執行計畫的副產品)。生產代碼**永遠加 `ORDER BY`**。
原文未提但極相關:`WHERE Type NOT IN (SELECT Type FROM ...)` 若子查詢含 `NULL`,整個結果為空。改用 `NOT EXISTS` 或 `LEFT JOIN ... WHERE IS NULL`。
**核心洞見**:InnoDB **叢集索引即資料本身**,二級索引是「指向資料的指標」。窄二級索引掃描+回表,往往比掃描寬叢集索引更便宜。理解此架構,索引選擇行為即變得透明可預測。