MySQL 查詢優化器（Query Optimizer）負責決定如何執行一個查詢，它通過選擇最優的 執行計劃 來實現查詢的高效執行。查詢優化器的目標是根據查詢的複雜性、表的大小、索引的可用性等因素選擇最優的執行路徑。下面將詳細介紹 MySQL 查詢優化器是如何選擇執行計劃的。

1. 執行計劃概述

執行計劃 是 MySQL 查詢優化器選擇的一系列操作步驟，指明如何掃描表、如何使用索引、如何連接多個表等。它包括：

• 掃描操作：如全表掃描、索引掃描、範圍掃描等。
• 連接操作：如嵌套循環連接（Nested Loops）、合併連接（Merge Join）、哈希連接（Hash Join）等。
• 排序和聚合操作：如排序、分組等操作。

查詢優化器會通過評估不同執行計劃的代價來選擇代價最低的執行計劃。

2. 優化器的工作流程

MySQL 查詢優化器的工作可以分為幾個階段：

1) 解析（Parsing）

首先，MySQL 會對查詢語句進行 語法分析 和 語義分析，將 SQL 查詢轉化為一棵 查詢樹（Query Tree）。

2) 重寫（Rewrite）

在這個階段，優化器會對查詢語句進行一些常見的優化，例如：

• 移除不必要的子查詢。
• 扁平化查詢（將複雜的查詢轉換為簡單的查詢）。
• 合併相同的子查詢。
• 使用簡化的條件表達式（例如，TRUE AND col 會被簡化為 col）。

3) 生成候選執行計劃（Plan Generation）

優化器會基於查詢樹生成多個 候選執行計劃，這些計劃可能涉及不同的掃描方式、連接方式、索引選擇等。候選執行計劃的生成考慮了以下因素：

• 掃描方式：全表掃描、索引掃描、範圍掃描等。
• 連接方式：嵌套循環連接、排序合併連接、哈希連接等。
• 索引選擇：是否使用索引，使用哪個索引。
• 排序方式：是否需要對結果進行排序。

4) 代價估算（Cost Estimation）

優化器通過 代價估算 來評估每個候選執行計劃的代價。代價估算基於以下幾個因素：

• IO代價：掃描數據的成本。
• CPU代價：執行排序、連接、計算的成本。
• 網絡代價：如果涉及分佈式查詢，則網絡延遲和數據傳輸的成本。

優化器會根據統計信息（如表的大小、索引選擇性等）估算出每個執行計劃的代價。代價通常是通過 CPU 時間 和 I/O 操作次數 來度量的，優化器會選擇代價最低的執行計劃。

5) 執行計劃選擇（Plan Selection）

在候選執行計劃的代價計算後，優化器會選擇代價最低的執行計劃作為最終的執行計劃。

3. 優化器選擇執行計劃的關鍵因素

以下是優化器選擇執行計劃時考慮的主要因素：

1) 表掃描策略

• 全表掃描（Full Table Scan）：當表的行數比較少，或者沒有合適的索引時，優化器會選擇全表掃描。
• 索引掃描（Index Scan）：如果查詢條件與某個索引匹配，優化器通常會選擇索引掃描。索引掃描通常比全表掃描更高效，尤其在查詢只涉及部分列或範圍查詢時。

• 範圍掃描（Range Scan）：如果查詢條件包含範圍（如 BETWEEN 或 >），則優化器會使用範圍掃描，掃描索引中的一個子區間。
• 覆蓋索引（Covering Index）：如果查詢的所有列都可以通過索引來獲取，則優化器會選擇使用覆蓋索引，這樣可以避免訪問表中的數據。

2) 連接策略

• 嵌套循環連接（Nested Loop Join）：這是最常見的連接策略，尤其是對於較小的表。在外部表中每一行都會去匹配內部表的每一行。通常用於沒有索引或者索引選擇性較差的情況。
• 排序合併連接（Sort Merge Join）：當兩個表都已經排序時，優化器會使用排序合併連接。它首先對兩個表進行排序，然後逐行比較。
• 哈希連接（Hash Join）：在某些情況下，優化器會選擇哈希連接。它通過哈希表的方式來處理連接操作，適用於大表的連接。

3) 索引選擇

優化器會評估查詢是否能利用索引，通常通過以下方式：

• 單列索引：當查詢條件與索引的某個列匹配時，優化器會選擇使用該索引。
• 聯合索引（Composite Index）：當查詢條件中涉及多個列，並且這些列出現在複合索引中時，優化器會選擇使用複合索引。
• 索引的選擇性：選擇性是指一個索引的唯一性。索引選擇性越高，越能夠有效縮小掃描的數據範圍。如果查詢條件與高選擇性的索引匹配，優化器會選擇使用該索引。

4) 統計信息

MySQL 查詢優化器依賴於統計信息來評估表和索引的大小、數據的分佈、列的選擇性等因素。統計信息可以通過 ANALYZE TABLE 命令進行更新。優化器根據這些統計信息來估算執行計劃的代價，從而選擇最優的執行策略。

5) 索引覆蓋

如果一個查詢的所有字段都包含在索引中（即 覆蓋索引），優化器將優先選擇這個索引，因為這樣可以避免讀取表中的實際數據，直接通過索引返回結果。

6) 子查詢優化

對於包含子查詢的查詢，優化器可以選擇不同的執行計劃策略：

• 將子查詢轉換為連接。
• 使用 半連接（Semi Join） 或 反連接（Anti Join） 來優化查詢。

7) 並行執行（Parallel Execution）

在一些特殊情況下（如大數據量的查詢），MySQL 查詢優化器可能會嘗試並行執行查詢，以提高性能。這通常是通過 MySQL 8.0 引入的 並行查詢 功能來實現的。

4. 常見優化器選擇策略

• EXPLAIN 命令：通過執行 EXPLAIN 查詢語句，用户可以查看優化器選擇的執行計劃。這對於查詢性能調優非常有幫助。EXPLAIN 返回的信息包括訪問方法（全表掃描、索引掃描等）、使用的索引、連接類型等。

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

• USE INDEX 和 FORCE INDEX：用户可以通過 USE INDEX 或 FORCE INDEX 顯式告訴優化器使用特定的索引。這樣可以在優化器選擇不當時提供幫助。

SELECT * FROM employees USE INDEX (idx_department_id) WHERE department_id = 5;

5. 小結

MySQL 查詢優化器通過以下幾個主要步驟來選擇執行計劃：

1. 解析和重寫查詢，生成查詢樹。
2. 生成候選執行計劃，包括不同的掃描和連接策略。
3. 代價估算，根據查詢表的統計信息估算不同執行計劃的代價。
4. 選擇最優執行計劃，根據代價選擇最合適的執行路徑。