萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

MySQL InnoDB 索引選擇機制揭秘:為何 SELECT * 不走主鍵而走二級索引?

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

二、 根因深度解析

1. InnoDB 叢集索引 (Clustered Index) 架構

  • **主鍵即叢集索引**:資料行**物理儲存於主鍵 B+ 樹葉節點** (Data = Index)。
  • **二級索引葉節點不存資料行**,僅存 **索引鍵值 + 主鍵值 (Bookmark/Row ID)**。
  • 查詢走二級索引時:掃描二級索引葉節點 → 取得主鍵值 → **回表** 到叢集索引查找完整行 (Lookup/Bookmark Lookup)。

2. 優化器成本模型:為何選擇二級索引?

優化器目標:**最小化總成本 (I/O + CPU)**。對 `SELECT *` (需所有欄位):

  • 掃描叢集索引 (PK):掃描所有葉節點 (含完整行數據) → **讀取頁數 = 叢集索引大小**。
  • 掃描二級索引 + 回表:掃描二級索引葉節點 (僅含 Type + Id,**極窄**,頁面容納更多行) → 對每行回表查叢集索引。
  • 關鍵判斷:若二級索引**極窄** (如單 `INT` 欄位),其大小遠小於叢集索引 (含所有欄位)。**掃描二級索引的 I/O 成本 + 回表成本 < 直接掃描叢集索引 I/O 成本**,優化器便選擇二級索引。

3. 結果順序由存取路徑決定

  • 走二級索引 (`IDX_Type`) → 按 `Type` 排序掃描 → 回表 → 結果按 `Type` 順序輸出。
  • 同 `Type` 值時,二級索引內部按主鍵 `Id` 排序 (InnoDB 為保證唯一性,隱式附加 PK) → 同組內按 `Id` 排序。故觀察到 `(1,1), (4,1)` 同 Type=1 時按 Id 排序。

三、 如何強制走主鍵 (獲得 Id 排序)?

方法 1:FORCE INDEX (PRIMARY) —— 立竿見影

SELECT * FROM test_b FORCE INDEX (PRIMARY);

結果按 `Id` 排序:`(1,1), (2,6), (3,2), (7,3), (4,1)`。
適用:臨時查詢、測試、明確知道 PK 掃描更優。

方法 2:覆蓋索引 (Covering Index) —— 根治

若查詢固定模式 (如常查 `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 硬幹。

方法 3:显式 ORDER BY (語義正確)

SELECT * FROM test_b ORDER BY Id; -- 強制排序,優化器可能選 PK 掃描 或 二級索引+Filesort

**語義上正確**:無 `ORDER BY` 時,**結果順序不保證** (僅是當前執行計畫的副產品)。生產代碼**永遠加 `ORDER BY`**。

四、 進階避坑:NOT IN 陷阱與 NULL

原文未提但極相關:`WHERE Type NOT IN (SELECT Type FROM ...)` 若子查詢含 `NULL`,整個結果為空。改用 `NOT EXISTS` 或 `LEFT JOIN ... WHERE IS NULL`。

五、 總結:索引選擇的決策邏輯

  1. 分析 `WHERE`/`JOIN`/`ORDER BY`/`GROUP BY` 欄位。
  2. 評估候選索引:**寬度 (大小)**、 **選擇性**、 **是否覆蓋查詢欄位** (避免回表)。
  3. 計算成本:`Index Scan Cost + Lookup Cost` vs `Clustered Index Scan Cost`。
  4. 選擇最低成本路徑。

**核心洞見**:InnoDB **叢集索引即資料本身**,二級索引是「指向資料的指標」。窄二級索引掃描+回表,往往比掃描寬叢集索引更便宜。理解此架構,索引選擇行為即變得透明可預測。

copyright © 萬盛學電腦網 all rights reserved