MySQL 優化器索引選擇與 JOIN 順序決策機制深度解析

前言

MySQL 優化器只有兩個自由度：JOIN 順序選擇與單表訪問方法選擇。本文通過一個具體案例，詳細追蹤優化器的決策過程。

測試環境

CREATE TABLE `department` (
  `DepartmentID` INT(11) DEFAULT NULL,
  `DepartmentName` VARCHAR(20) DEFAULT NULL,
  KEY `IND_D` (`DepartmentID`),
  KEY `IND_DN` (`DepartmentName`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

CREATE TABLE `employee` (
  `LastName` VARCHAR(20) DEFAULT NULL,
  `DepartmentID` INT(11) DEFAULT NULL,
  KEY `IND_L_D` (`LastName`),
  KEY `IND_DID` (`DepartmentID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

測試 SQL：

EXPLAIN SELECT * FROM employee AS A, department AS B
WHERE A.LastName = 'zhou' AND B.DepartmentID = A.DepartmentID AND B.DepartmentName = 'TBX';

優化器決策流程

1. Range Analysis（範圍分析）

評估等值條件的「found records」（預估命中行數）：

• 表 A：A.LastName = 'zhou' → found records: 51
• 表 B：B.DepartmentName = 'TBX' → found records: 1

此值存儲供後續 ref 訪問方法評估使用。InnoDB 每次調用 records_in_range 會採樣索引頁，較消耗效能（其他資料庫用直方圖統計避免）。

2. JOIN 順序與訪問方法選擇：窮舉搜尋

MySQL 列舉所有 left-deep 樹（搜尋空間），尋找最優執行計劃。

2.1 排序

按 found records 升序排列表：B (1行) → A (51行)。記錄少的優先作為驅動表。

2.2 貪婪搜尋 / 窮舉

表數少於 search_depth (預設 63) 時直接窮舉。

3. 具體決策推演

路徑一：JOIN 順序 B → A（最優）

• 第一表 B：無法用 IND_D（需 A 表值），只能用 IND_DN (DepartmentName='TBX')
  • Index 成本：1.2 (IO=1)
  • 全表掃描 IO=2 → 捨棄
  • 訪問方法：ref，索引 IND_DN
• 第二表 A：可用索引 IND_L_D (LastName='zhou') 或 IND_DID (B.DepartmentID=A.DepartmentID)
  • IND_L_D：found records=51 → IO=51 → 修正後 15 → 總成本 25.2
  • IND_DID：B 表僅 1 行，Cardinality=1349 → 每值 1 行 → found records=1 → IO=1 → 總成本 1.2
  • 選擇 IND_DID，成本 1.2
• 總成本：1.2 + 1.2 = 2.4

路徑二：JOIN 順序 A → B

• 第一表 A：只能用 IND_L_D，成本 25.2
• 已超過當前最優成本 2.4 → 剪枝放棄

最終執行計劃

JOIN 順序：B → A，B 表用 IND_DN，A 表用 IND_DID，總成本 2.4。

Bad Case 構造：統計資訊誤導優化器

調整資料分佈：

• DepartmentName='TBX' 有 10 行（非 1 行）
• LastName='zhou' 只有 1 行
• DepartmentID=27760 有 1010 行

優化器誤選 A → B 順序，A 表用 IND_L_D，B 表用 IND_D，實際掃描 1010 行再過濾。若選 B 表用 IND_DN 僅 10 行再關聯則更優。原因：統計資訊不準導致成本估算偏差。

結論

優化器基於統計資訊做成本模型決策，資料分佈不均時易誤判。理解決策流程有助於通過 Hint、統計資訊維護、索引設計來引導優化器選擇更優計劃。