在 MySQL 的查詢優化過程中，優化器的決策直接影響執行計劃的效率。本文通過一個典型面試題，深入探討優化器的索引選擇邏輯、prefer_ordering_index 的作用，以及複雜查詢場景下的索引使用策略。

問題背景與場景復現

題目要求禁用 prefer_ordering_index 優化策略後，分析以下查詢的執行計劃：

SET optimizer_switch = 'prefer_ordering_index=off';
EXPLAIN
SELECT * FROM sbtest1
WHERE k + @ = 4
ORDER BY id DESC
LIMIT 1;

關鍵點分析：

1. prefer_ordering_index 的作用：該參數控制優化器是否優先選擇能夠避免 ORDER BY 或 GROUP BY 排序的索引。關閉後，優化器不再強制依賴排序索引，轉而基於成本模型選擇最優執行計劃。
2. 查詢結構：WHERE 條件涉及表達式 k + @ = 4，ORDER BY 主鍵 id，且僅需返回一條記錄。

索引選擇的核心邏輯

1. WHERE 條件與索引的兼容性

• 表達式對索引的破壞：如果 WHERE 條件中對字段進行了運算（如 k + @ = 4），MySQL 通常無法直接使用該字段的索引（如 k 上的索引），因為索引存儲的是原始值而非表達式結果。
• 用户變量的不確定性：@ 是用户變量，其值可能在運行時變化，導致優化器無法提前計算 k = 4 - @ 的等價形式，進一步阻礙索引使用。

2. ORDER BY 與主鍵索引的天然優勢

• 主鍵索引的聚簇特性：在 InnoDB 中，主鍵索引（PRIMARY）是聚簇索引，數據按主鍵順序物理存儲。因此，ORDER BY id DESC 只需反向掃描主鍵索引即可獲得有序結果，無需額外排序。
• LIMIT 1 的優化效果：當查詢僅需返回一條記錄時，優化器可能選擇直接遍歷主鍵索引（從最大 id 開始），逐行檢查 WHERE 條件，直到找到第一條匹配的記錄，避免全表掃描。

3. prefer_ordering_index 禁用後的影響

• 關閉後的行為變化：禁用 prefer_ordering_index 後，優化器不再強制優先選擇排序索引，但主鍵索引的聚簇特性仍使其成為最有效的訪問路徑。此時，優化器可能基於以下邏輯決策：

  • 成本估算：全表掃描主鍵索引的成本可能低於其他索引（如 k 索引）的隨機訪問。
  • 天然有序性：主鍵索引本身已按 id 排序，直接利用其順序可避免 Using filesort。

執行計劃解析

通過 EXPLAIN 分析，可以得出以下結論：

1. 使用的索引：主鍵索引（PRIMARY）。

2. 執行過程：

   • 按 id 降序掃描主鍵索引。
   • 逐行檢查 WHERE 條件 k + @ = 4。
   • 找到第一條匹配記錄後立即終止掃描（LIMIT 1）。

3. Extra 列信息：

   • Using where：表示需要逐行過濾數據。
   • 無 Using filesort：主鍵索引的天然有序性避免了額外排序。

擴展知識點

1. 索引條件下推（ICP）

如果 k 字段有索引且 WHERE 條件可轉換為 k = 4 - @，啓用 ICP 後，存儲引擎層會直接過濾數據，減少回表次數。但本例中由於表達式問題，ICP 無法生效。

2. 覆蓋索引與回表代價

若查詢僅需 id 和 k 字段，且 k 索引包含這兩列，優化器可能選擇 k 索引來避免回表。但本例需要所有字段（SELECT *），且 WHERE 條件無法使用 k 索引，因此主鍵索引仍是更優選擇。

3. 優化器的成本模型

優化器通過 innodb_stats_persistent 和 innodb_stats_sample_pages 等參數維護統計信息，估算不同索引的 I/O 和 CPU 成本。關閉 prefer_ordering_index 後，成本模型更傾向於選擇總成本最低的執行計劃。

總結與最佳實踐

1. 避免在 WHERE 條件中使用表達式：儘量將字段獨立於運算，例如改寫為 k = 4 - @，以利用索引。
2. 理解聚簇索引的優勢：主鍵索引在排序和範圍查詢中具有天然優勢，尤其是結合 LIMIT 時。
3. 合理配置優化器開關：在複雜查詢中，可通過調整 optimizer_switch 參數（如關閉 prefer_ordering_index）觀察執行計劃變化，找到最佳平衡點。

推薦 🌟🌟🌟🌟🌟

🔍 dblens for MySQL - 下一代智能數據庫管理與開發工具

🚀 免費下載 | 開箱即用 | AI賦能 | 全鏈路SQL開發

🌟 核心亮點功能

🤖 AI 智能引擎

• AI自然語言對話：用日常語言描述需求，自動生成精準SQL語句
• SQL智能優化器：AI深度解析執行計劃，提供性能優化建議
• 測試數據工廠：智能生成海量仿真測試數據，支持複雜業務規則
• 大模型定製中心：支持配置接入/訓練專屬領域大模型

🛠️ 智能開發套件

• 可視化表設計器：設計表，實時DDL同步

• AI SQL編輯器：

  • 智能語法高亮
  • 智能語法補全
  • 動態錯誤檢測 + 一鍵修復
  • 多窗口對比調試
• AI對象生成：自動創建表/視圖/存儲過程/函數

📊 數據管理矩陣

• 智能SQL篩選器：可視化條件組合生成複雜查詢
• 數據字典中心：自動生成文檔，支持PDF
• 雲原生數據庫沙箱：預置測試實例，5秒快速連接
• 異構數據遷移：支持Excel/CSV/JSON ↔ 數據庫雙向同步

🚄 效率加速器

• 自然語言轉SQL：業務人員也能輕鬆操作數據庫
• SQL歷史版本對比：智能識別語法差異
• 跨平台工作區：Windows/macOS/Linux全支持
• 多語言界面：中文/英文自由切換

🎯 適用場景

✅ 敏捷開發團隊快速迭代
✅ DBA智能運維管理
✅ 數據分析師自助查詢
✅ 教學培訓SQL編程
✅ 企業級數據資產管理

⚡ 即刻體驗

→ [立即下載] https://sourceforge.net/projects/dblens-for-mysql