MySQL 優化器選擇索引與 JOIN 順序深度剖析：範圍分析、貪婪搜尋與 Bad Case 構造

優化器兩大自由度

1. JOIN 順序：哪張表做驅動表
2. 單表訪問方式：全表掃描 vs 索引掃描

核心流程

1. Range Analysis（範圍分析）

評估等值/範圍條件的 found_records（預估命中行數），供後續成本計算使用。InnoDB 透過索引頁採樣估算，無直方圖統計時精度有限。

表 A: A.LastName = 'zhou'  → found_records: 51
表 B: B.DepartmentName = 'TBX' → found_records: 1

2. JOIN Order 選擇：窮舉 Left-Deep Tree

MySQL 列舉所有左深樹，尋找最低總成本。表數 < optimizer_search_depth（預設 62）時全量窮舉。

3. 排序啟發式

先按 found_records 升序排表，記錄少的優先嘗試做驅動表。本例順序：B → A。

詳細成本計算演示

方案 1：驅動表 B（最優）

• B 表：無法用關聯索引 IND_D，只能用 IND_DN（DepartmentName='TBX'），IO 成本 1，總成本 1.2
• A 表：可選 IND_L_D (LastName='zhou', IO 成本 15→修正 25.2) 或 IND_DID (DepartmentID=B.DepartmentID, 前表 1 行 → 預估 1 行, IO 成本 1, 總成本 1.2)
• 選 IND_DID，總成本 1.2 + 1.2 = 2.4

方案 2：驅動表 A

• A 表：只能用 IND_L_D，成本 25.2 已超過最優 2.4，直接剪枝

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

# 構造資料分布傾斜
INSERT INTO employee VALUES ("zhou", 27760);  -- 1 筆
INSERT INTO department VALUES (27760, "TBX");  -- 10 筆同 DepartmentName
INSERT INTO department VALUES (27760, random); -- 1000 筆同 DepartmentID

導致優化器選擇 IND_D（DepartmentID）而非 IND_DN，實際掃描 1010 行 vs 最優 10 行。

根因與對策

• InnoDB 無直方圖，依賴 Cardinality 均勻假設
• MariaDB 10.0+ / MySQL 8.0 已支援直方圖統計
• 臨時對策：STRAIGHT_JOIN、USE INDEX、ANALYZE TABLE 更新統計