背景：optimizer_switch 讓你掌控優化器
MySQL 5.1 引入 optimizer_switch 系統變量，通過開關控制優化器行為。5.5/5.6 陸續新增開關，至 5.6 達到 20+ 項。合理使用可繞過優化器缺陷、強制走最優路徑、排查執行計劃異常。
查看與修改
-- 查看當前開關
SHOW VARIABLES LIKE 'optimizer_switch';
-- 會話級修改 (推薦測試)
SET SESSION optimizer_switch='index_merge=off,mrr=on';
-- 全局修改 (需重啟持久化)
SET GLOBAL optimizer_switch='index_merge=off,mrr=on';
版本演進全景圖
MySQL 5.1 新增：Index Merge 系列
| 開關 | 適用場景 | 原理 | 典型收益 |
|---|
| index_merge_intersection | 多單列索引 AND 條件 | 分別掃描索引，取 RowID 交集，回表 | 避免全表掃描 |
| index_merge_union | 多單列索引 OR 條件 | 掃描所有相關索引，取 RowID 並集，回表 | OR 查詢加速 |
| index_merge_sort_union | OR 條件 + 需排序 | Union 後按 RowID 排序，再回表 | 減少 filesort |
注意：Index Merge 適用於無組合索引、單列索引選擇率高場景。組合索引通常更優，優化器會自動選擇。
MySQL 5.5 新增：NDB 專用
| 開關 | 作用 |
|---|
| engine_condition_pushdown | ON：WHERE 條件下推到 Data Node 過濾後再發送到 SQL Node，大幅減少網絡傳輸。僅 NDB 引擎生效。 |
MySQL 5.6 新增：核心優化器重構
1. MRR (Multi-Range Read) 系列
| 開關 | 核心機制 | 適用場景 | 收益 |
|---|
| mrr | 範圍掃描時，按主鍵排序 RowID，順序回表 | 大數據量範圍查詢，Buffer Pool 不足 | 隨機 I/O → 順序 I/O，吞吐提升 2-10 倍 |
| mrr_cost_based | 基於成本模型決定是否啟用 MRR | 默認 ON，優化器自動權衡 | 避免小結果集 MRR 開銷 |
2. BKA (Batched Key Access)
| 開關 | 機制 | 前置條件 |
|---|
| batched_key_access | Join Buffer 掃描內表後，批量收集主鍵交給 MRR 接口一次性回表 | mrr=on + join_buffer_size 足夠 |
適用於 NLJ (Nested Loop Join) 內表大、需回表 場景，減少 Join Buffer 與存儲引擎交互次數。
3. BNL (Block Nested Loop)
| 開關 | 機制 | 收益 |
|---|
| block_nested_loop | Join Buffer 記錄「是否匹配」標記，下輪外表值跳過已匹配行 | 減少 CPU 比較次數，加速大表關聯 |
4. ICP (Index Condition Pushdown)
| 開關 | OFF (傳統) | ON (ICP) | 收益 |
|---|
| index_condition_pushdown | 組合索引 (A,B)，僅用 A 定位，回表過濾 B | 索引樹內直接過濾 B，僅回表符合行 | 減少回表 I/O，不改變掃描順序 |
經典案例：WHERE a=1 AND b LIKE 'xyz%'，索引 (a,b)，ICP 可在索引層過濾 b。
5. Use Index Extensions
| 開關 | 原理 | 適用 |
|---|
| use_index_extensions | InnoDB 二級索引隱含主鍵列，優化器將索引 (c) 視為 (c, pk1, pk2...) | 查詢同時用到二級索引列與主鍵前綴列 |
6. Semi-Join 子查詢優化族
| 開關 | 策略 | 核心邏輯 |
|---|
| semijoin | 總開關 | 將 IN/EXISTS 子查詢轉為半連接，內表去重 |
| firstmatch | FirstMatch | 內表僅取第一匹配行，後續跳過 |
| loosescan | LooseScan | 按索引分組，每組取第一行 |
| materialization | 物化表 | 子查詢結果去重建臨時表+索引，外表關聯 |
| subquery_materialization_cost_based | 成本決策 | 基於成本選擇是否物化 |
半連接本質：SELECT * FROM a WHERE a.id IN (SELECT b.a_id FROM b) → 只關心 b 中「是否存在」匹配 a.id，不關心匹配幾行，故可去重。
實戰調優決策樹
- EXPLAIN 看到 Using temporary; Using filesort → 嘗試
mrr=on, batched_key_access=on（大表範圍/關聯）。
- OR 條件走全表掃描 →
index_merge_union=on 或建組合索引。
- 子查詢極慢 →
semijoin=on, materialization=on（5.6+ 默認開啟）。
- 組合索引回表多 →
index_condition_pushdown=on（5.6+ 默認開啟）。
- NLJ 關聯大表 →
block_nested_loop=on, batched_key_access=on + 增大 join_buffer_size。
避坑指南
- 不要全局關閉 index_merge：某些場景它是唯一出路，建議會話級測試。
- MRR 需足夠 join_buffer_size/read_rnd_buffer_size：內存不足會退化性能。
- ICP 不支持全文/空間索引，不支持覆蓋索引查詢（無回表）。
- Semi-Join 物化表無統計信息：優化器可能誤估行數，導致次優計劃。
- 版本差異：5.6 默認開啟 mrr/icp/semijoin/materialization；5.7/8.0 進一步完善成本模型。
總結
optimizer_switch 是 DBA 手中的「微調旋鈕」。理解每個開關的適用邊界、前置條件、副作用，結合 EXPLAIN 與實測，才能在優化器「犯迷糊」時精準糾偏。建議：默認保持開啟，會話級關閉/開啟驗證，效果確定再全局固化。
