MySQL 執行計劃不準確的常見原因與應對策略

為什麼 MySQL EXPLAIN 執行計劃經常不準？核心因素與應對

EXPLAIN 輸出的 rows、filtered、key 等往往與實際執行偏差巨大，導致優化方向錯誤。本文梳理核心原因與緩解手段。

一、統計信息滯後

• information_schema.TABLES/STATISTICS 非實時，默認每 10 秒或 1/16 表變更時後台更新。
• 大量 DML 後統計失真，導致優化器誤判選擇率。
• 對策：ANALYZE TABLE t; 手動更新統計；關鍵表設置 innodb_stats_persistent=ON + innodb_stats_auto_recalc=ON；大表適當降低 innodb_stats_sample_pages 提高採樣精度。

二、成本模型與硬件不匹配

• MySQL 成本常數（io_cost/cpu_cost）基於傳統 HDD 校準，SSD/NVMe 隨機讀寫成本遠低於順序，導致優化器傾向全表掃描而非索引查找。
• 對策：MySQL 5.7+ mysql.server_cost/mysql.engine_cost 表可調整成本常數；或使用 FORCE INDEX 強制走索引。

三、參數配置影響

• optimizer_switch 開關（如 index_merge, mrr, batched_key_access）改變優化器行為。
• join_buffer_size、sort_buffer_size 影響 Block Nested Loop / 排序成本估算。
• 對策：按負載調優參數，避免全局關閉優化特性。

四、查詢結構複雜度

• 多表 JOIN、子查詢、派生表、CTE 導致搜索空間爆炸，優化器採用貪心/啟發式剪枝，可能錯過最優路徑。
• 對策：拆分大查詢、使用 STRAIGHT_JOIN 固定 JOIN 順序、物化中間結果到臨時表。

五、數據分布偏斜

• 直方圖統計（8.0 ANALYZE TABLE ... UPDATE HISTOGRAM）未收集或桶數不足，無法識別熱點值。
• 對策：核心列收集直方圖；高基數列用 FORCE INDEX 繞過誤判。

六、環境與並發干擾

• 鎖等待、IO 競爭、CPU 抢佔導致實際耗時與靜態成本估算脫節。
• 對策：結合 EXPLAIN ANALYZE（8.0.18+）獲取實際執行時間、循環次數、I/O，對比靜態估算。

七、應對總結清單

1. 例行 ANALYZE TABLE（低峰期）。
2. 核心列收集直方圖（8.0+）。
3. SSD 環境調整 engine_cost。
4. 關鍵慢查詢用 EXPLAIN ANALYZE 驗證實際 vs 估算。
5. 必要時 FORCE INDEX / STRAIGHT_JOIN 干預優化器。
6. 監控 optimizer_trace 觀察優化器決策路徑（調試用）。

總結

執行計劃是基於統計信息與成本模型的靜態估算，而非運行時真相。建立「統計維護 -> 計劃驗證 -> 干預優化」閉環，才能在不準確的計劃中做出正確優化決策。