MySQL WHERE 子句優化機制深度解析：常數摺疊、無效條件剪除、索引選擇與臨時表策略

MySQL 查詢最佳化器對 WHERE 子句執行一系列自動重寫與優化。理解這些機制有助於撰寫高效 SQL 並預測執行計畫。本文系統整理官方文件中的核心優化規則。

核心重寫規則

1. 刪除多餘括號： ((a AND b) AND c) → (a AND b AND c)
2. 常數摺疊/傳播： (a < b AND a = 5) → (b > 5 AND a = 5)
3. 移除恆真/恆假條件： (B>=5 AND B=5) → B=5；(B=7 AND 5=6) → 移除
4. 索引使用的常數運算式僅計算一次。
5. 無 WHERE 的 COUNT(*) 直接讀取表統計資訊 (MyISAM/MEMORY)；單表 NOT NULL 條件同理。
6. 早期偵測不可能條件： 快速返回空結果集。
7. HAVING 合併至 WHERE (無 GROUP BY/聚合函數時)。
8. 子聯結簡化： 為每個子聯結構建更簡單的 WHERE，加速評估並盡早過濾列。

常數表優先讀取策略

最佳化器優先讀取「常數表」，類型包括：

• 空表或單列表。
• 搭配 PRIMARY KEY 或 UNIQUE 索引之 WHERE 條件 (所有索引欄位為常數運算式且 NOT NULL)。

範例：SELECT * FROM t WHERE primary_key=1、SELECT * FROM t1,t2 WHERE t1.pk=1 AND t2.pk=t1.id。

聯結順序與臨時表決策

• 嘗試所有聯結排列組合尋找最佳順序。
• 若 ORDER BY 與 GROUP BY 欄位來自同一表，該表優先聯結。
• SQL_SMALL_RESULT 提示 → 使用記憶體臨時表。
• ORDER BY 與 GROUP BY 不一致、或涉及非第一張表欄位 → 建立磁碟臨時表。
• DISTINCT 轉為全欄位 GROUP BY，搭配 ORDER BY 常觸發臨時表。

索引使用閾值

• 預估存取列 < 30% → 使用索引。
• 否則 → 全表掃描 (通常更快)。
• 覆蓋索引： 查詢欄位皆為數值型且包含於索引 → 僅掃描索引樹，不讀資料檔。

HAVING 過濾時機

每列輸出前評估 HAVING，不符合者直接跳過，減少網路傳輸。

極速查詢模式範例

SELECT COUNT(*) FROM tbl_name
SELECT MIN(key_part1), MAX(key_part1) FROM tbl_name
SELECT MAX(key_part2) FROM tbl_name WHERE key_part1 = constant
SELECT ... FROM tbl_name ORDER BY key_part1, key_part2 LIMIT 10
SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC LIMIT 10

總結

MySQL WHERE 優化核心在於**「常數傳播簡化邏輯」、「常數表優先聯結」、「30% 索引閾值」、「覆蓋索引避免回表」、「臨時表策略」**。善用 EXPLAIN 驗證執行計畫，配合 LIMIT、SQL_SMALL_RESULT、覆蓋索引設計，可榨乾查詢效能。