本篇文章介紹一個非常核心的數據庫問題。MySQL 選擇最佳執行路徑（即“查詢優化”）的過程是由其查詢優化器（Query Optimizer） 完成的。

簡單來說，優化器的目標是：在多種可能的執行方案中，選擇一個它認為執行成本（Cost）最低的方案。

這個決策過程非常復雜，但可以概括為以下幾個核心步驟和原則：

---

一、核心流程：優化器如何工作？

1. 解析與重寫查詢

• MySQL 首先解析 SQL 語句，生成一個解析樹。
• 然后對解析樹進行重寫，包括一些語義優化，例如：
  • 視圖展開：將視圖引用替換為視圖的定義。
  • 簡化條件：移除不必要的括號、簡化表達式（如 5=5 AND a>0 被簡化為 a>0）。
  • 常量傳遞：利用等式的傳遞性進行簡化（如 a = 5 AND b = a => a = 5 AND b = 5）。

2. 生成可能的執行計劃

• 對于同一個查詢，通常有多種執行方式。例如：
  • 表訪問方式：應該全表掃描（ALL）還是使用索引？（const, ref, range, index）。
  • 多表連接（JOIN）的順序：先讀哪張表，后讀哪張表？(A JOIN B 還是 B JOIN A)。
  • 多表連接的算法：使用 Nested-Loop Join、Hash Join (MySQL 8.0+)，還是 Batched Key Access Join？
  • 子查詢優化：將子查詢轉換為更高效的 JOIN 操作。
  • 索引合并：是否需要對多個索引的掃描結果進行合并（index_merge）。

優化器會枚舉出許多（但不是全部，因為窮舉所有可能代價太高）可能的執行路徑。

3. 基于成本（Cost）評估執行計劃

• 這是最核心的一步。優化器是一個基于成本的優化器（Cost-Based Optimizer, CBO）。
• 它會為每個執行計劃計算一個預估成本（Cost）。成本是一個相對值，主要基于以下統計信息：
  • I/O 成本：將數據從磁盤加載到內存的代價。全表掃描的成本主要來自這里。
  • CPU 成本：處理數據（比較記錄、排序、計算等）的代價。
  • 內存/資源成本：使用臨時表、排序等的代價。

4. 選擇成本最低的計劃

• 優化器會比較所有生成的執行計劃的預估成本，并選擇它認為成本最低的那個。
• 最終，這個被選中的計劃會被交給執行引擎去執行。

---

二、優化器依賴的關鍵信息

優化器的成本計算并非憑空猜測，它嚴重依賴于數據庫的統計信息：

1. 表統計信息

   • TABLE_ROWS：表的粗略行數。
   • DATA_LENGTH：表的數據大小。
   • 這些信息可以通過 ANALYZE TABLE table_name; 命令來更新，存儲在 information_schema.TABLES 中。

2. 索引統計信息（至關重要）

   • 基數（Cardinality）：索引中唯一值的估計值。這是一個非常關鍵的指標。
     • 高選擇性（High Cardinality）：索引列的唯一值很多（如主鍵、用戶名），意味著索引非常有效。
     • 低選擇性（Low Cardinality）：索引列的唯一值很少（如性別、狀態標志），使用索引可能不如全表掃描。
   • 索引信息存儲在 information_schema.STATISTICS 中。

示例：SELECT * FROM users WHERE gender = 'F';

• 如果 gender 列的基數很低（只有 ‘M’/‘F’ 兩個值），優化器知道即使用了索引，也要返回約50%的數據。此時全表掃描的成本可能更低。
• 如果查詢是 SELECT * FROM users WHERE user_id = 123;，user_id 是主鍵，基數極高，優化器會毫不猶豫地選擇主鍵索引進行查找。

---

三、開發者如何協助和干預優化器？

雖然優化器很強大，但它的決策基于統計信息，而統計信息可能是過時或不準確的。開發者可以這樣做：

1. 提供合適的索引（最重要的手段）

   • 為 WHERE, JOIN ... ON, ORDER BY, GROUP BY 子句中的列創建索引。
   • 使用覆蓋索引（Covering Index），即索引包含了查詢所需的所有字段，避免回表操作，極大提升性能。

2. 及時更新統計信息

   • 在執行了大量 INSERT, UPDATE, DELETE 操作后，運行 ANALYZE TABLE table_name; 來更新統計信息，幫助優化器做出更準確的判斷。

3. 優化SQL寫法

   • 避免使用 SELECT *，只選擇需要的列。
   • 謹慎使用 OR，它常常會導致索引失效，可以考慮使用 UNION 改寫。
   • 避免在索引列上使用函數或計算，這會導致索引失效（例如 WHERE YEAR(create_time) = 2023 不如 WHERE create_time >= '2023-01-01'）。

4. 使用優化器提示（Optimizer Hints）

   • 如果你確信優化器選錯了計劃，可以使用提示來強制干預。例如：
     • SELECT /*+ INDEX(table_name index_name) */ ...：強制使用某個索引。
     • SELECT /*+ NO_INDEX(table_name index_name) */ ...：強制忽略某個索引。
   • 注意： 這是一種高級且危險的操作，只有在充分理解和測試后才使用，因為數據分布變化后，強制提示可能反而會變差。

5. 使用 EXPLAIN 分析計劃

   • 這是最強大的調試工具。在SQL語句前加上 EXPLAIN 或 EXPLAIN FORMAT=TREE (MySQL 8.0+)，可以查看優化器選擇的執行計劃。
   • 你需要學會看 EXPLAIN 的輸出（尤其是 type, key, rows, Extra 字段），來判斷索引是否被有效利用、是否有全表掃描、是否使用了臨時表等。

---

總結

MySQL 通過查詢優化器選擇最佳執行路徑，其核心是：

1. 基于成本模型：估算不同執行計劃的 I/O、CPU 成本。
2. 依賴統計信息：表的行數、索引的基數等是其決策的依據。
3. 目標是成本最低：選擇它認為執行最快的方案。

作為開發者，我們的職責是：

• 提供準確的信息：通過創建合適的索引和更新統計信息來“幫助”優化器。
• 驗證和干預：使用 EXPLAIN 工具驗證優化器的選擇，并在極少數情況下使用提示進行干預。
• 編寫優化器友好的SQL：避免寫出讓優化器“困惑”的語句。