MySQL 查詢優化器深度解析：原始碼邏輯、常數傳遞、JOIN 排序、索引合併與分區裁剪

優化器架構概覽

MySQL 優化器入口：sql/sql_select.cc → JOIN::optimize()。核心流程：

handle_select()
  → mysql_select()
    → JOIN::prepare()      -- 上下文分析、語句轉換
    → JOIN::optimize()     -- 核心優化邏輯
      → optimize_cond()    -- 條件簡化、常數傳遞
      → make_join_statistics() -- 統計資訊收集
      → choose_plan()      -- 存取路徑選擇
        → best_access_path()
        → greedy_search() / find_best() -- 連接順序搜尋
    → JOIN::exec()         -- 執行

一、首要優化：常數關係處理

1. 常數等值傳遞

WHERE a = b AND b = 5
→ WHERE a = 5 AND b = 5

適用運算子：=, <, >, <=, >=, <>, <=>, LIKE。不適用 BETWEEN。
原始碼：propagate_cond_constants()、change_cond_ref_to_const()。

2. 死碼剔除

• WHERE 0=0 AND col=1 → WHERE col=1 (Always TRUE 移除)
• WHERE 0=1 AND col=1 → WHERE col=1 (Always FALSE 子句移除)
• WHERE not_null_col IS NULL → Impossible WHERE (直接返回空結果)

原始碼：remove_eq_conds()。

3. 常數摺疊

WHERE col = 1 + 2 → WHERE col = 3

4. 常數表識別

符合任一條件即為常數表 (最多 1 行)：

1. 空表或單行表。
2. PRIMARY KEY 或 UNIQUE NOT NULL 所有欄位出現在 WHERE col=const 中。

優化器優先讀取常數表，將值內插查詢，甚至直接判定 Impossible WHERE。

二、JOIN 優化：連接順序與存取路徑

1. JOIN 類型層級 (最好→最差)

2. 驅動表選擇邏輯

優化器評估每個表的「最佳存取路徑成本」，選成本最低者為驅動表。
WHERE 條件欄位有索引 → 該表極大機率成為驅動表。

3. 搜尋算法：貪婪搜尋 vs 窮盡搜尋

• optimizer_search_depth 控制深度 (預設 62)。
• 表數 ≤ optimizer_search_depth：find_best() 窮盡搜尋 (動態規劃)，保證最優。
• 表數 > 閾值：greedy_search() 貪婪搜尋，從當前最佳擴展，不保證全域最優但快。

原始碼：sql/sql_select.cc find_best()、greedy_search()。

4. 直連 JOIN (STRAIGHT_JOIN) 與 LEFT JOIN 特殊處理

• STRAIGHT_JOIN 強制按 FROM 順序連接，跳過順序搜尋。
• LEFT/RIGHT JOIN 語義限制連接順序，優化器可能轉換為 INNER JOIN (若 WHERE 條件拒絕 NULL)。

三、進階存取路徑

1. RANGE 存取

條件：BETWEEN, IN, >, <, >=, <=, LIKE 'prefix%'。
優化器構建 SEL_TREE 區間樹，評估各索引範圍掃描成本，選最低成本索引。
LIKE '%suffix' 無法使用 RANGE。

2. INDEX MERGE (索引合併)

觸發條件：WHERE key1=1 OR key2=2 (不同索引的 OR 條件)。
三種演算法：

• Intersection：AND 條件多索引交集 (罕見)。
• Union：OR 條件多索引聯集，去重 (Unique 暫存行 ID)。
• Sort-Union：非唯一索引聯集，需排序去重。

限制：若單一索引能覆蓋所有條件 (如 WHERE (key1=1 OR key1=2) AND key2=3)，優化器傾向單索引 Range，不觸發 Merge。
原始碼：sql/opt_range.cc imerge_list_and_list(), SEL_IMERGE 類。

3. 覆蓋索引 (Index Only Scan)

SELECT indexed_col FROM t WHERE indexed_col = 1 → Extra: Using index。
查詢欄位全在索引中，無需回表。

四、其他優化規則

• 換位：5 = col → col = 5 (不支援 5 = -col)。
• AND 條件：有索引者優先，平等則第一個建立的索引勝出。
• OR 條件：預設全表掃描，符合 Index Merge 條件才用合併。
• ORDER BY 消除：若驅動表索引天然有序，跳過 Filesort。
  test_if_skip_sort_order()、test_if_order_by_key()。
• GROUP BY 優化：用索引分組 (Using index for group-by) 或臨時表+排序。
  DISTINCT → GROUP BY 轉換 (條件：單表、無 WHERE、無 LIMIT、可用索引)。
• MAX/MIN 優化：索引欄位直接取最左/最右葉節點值，O(1)。
• NOT IN / <> 不走索引：需手動改寫。

五、分區裁剪

僅針對分區表。優化器分析 WHERE 條件與分區函數，推導需掃描的分區集合。
步驟：

1. 構建區間圖 (SEL_ARG 圖) 表示分區/子分區條件。
2. 單點區間：partition_col = const 直接計算分區。
3. 區間游走：const1 <= col <= const2 轉換為連續單點區間 (上限 10 個分區，MAX_RANGE_TO_WALK)。
4. 區間映射：RANGE/LIST 分區根據分區函數單調性映射區間。
5. 彙總所有需訪問分區，其餘裁剪。

原始碼：sql/opt_range.cc prune_partitions()、sql/sql_partition.cc。

六、NULL 值過濾優化

• Early Null Reject：tblY.key = tblX.col → 推導 tblX.col IS NOT NULL，提前過濾驅動表 NULL 行，避免無效 JOIN。
• Late Null Reject：索引查找前檢查關鍵值是否為 NULL，是則直接返回無匹配，不下發索引查找。

實現：update_ref_and_keys() 標記 null_rejecting，add_not_null_conds() 注入條件，join_read_always_key() 執行期檢查。

總結

MySQL 優化器基於**成本模型**、以**動態規劃/貪婪搜尋**決定連接順序，核心優化點在於**常數傳遞簡化條件**、**索引存取路徑選擇 (Range/Ref/Index Merge)**、**排序/分組消除**、**分區裁剪**與 **NULL 過濾下推**。理解原始碼邏輯 (sql_select.cc, opt_range.cc) 能幫助 DBA 預測執行計畫、設計更優索引、撰寫優化器友好的 SQL。