MySQL WHERE 子句優化鐵律：常數摺疊、索引下推、臨時表避免與快速查詢模式

WHERE 優化主要用於 SELECT，同樣適用 DELETE/UPDATE。MySQL 內建優化規則：

核心優化規則

• 移除多餘括號：((a AND b) AND c OR ...) → (a AND b AND c) OR (a AND b AND c AND d)
• 常數摺疊：a < b AND b = c AND a = 5 → b > 5 AND b = c AND a = 5
• 刪除常數條件：(B>=5 AND B=5) OR (B=6 AND 5=5) → B=5 OR B=6
• 索引常數表達式單次計算
• 單表無 WHERE COUNT(*) 直接從表資訊取得
• 無效常數表達式早期偵測：快速判斷無結果查詢
• 無 GROUP BY 時 HAVING 合併至 WHERE
• 子連結簡化 WHERE：加速計算、盡早跳過記錄
• 常數表優先讀取：空表、單行表、主鍵/唯一鍵等值查詢
• 嘗試所有連接順序找最佳組合；ORDER BY/GROUP BY 來自同表優先
• SQL_SMALL_RESULT → 記憶體臨時表
• ORDER BY/GROUP BY 跨表 → 磁碟臨時表

索引使用策略

• 每表索引評估，掃描 < 30% 行用索引，否則全表掃描
• 覆蓋索引：查詢欄位全在索引中 → 僅讀索引樹，不讀資料檔
• HAVING 不匹配行輸出前跳過

極速查詢模式範例

SELECT COUNT(*) FROM tbl;
SELECT MIN(key1), MAX(key1) FROM tbl;
SELECT MAX(key2) FROM tbl WHERE key1 = const;
SELECT ... FROM tbl ORDER BY key1, key2 LIMIT 10;
SELECT ... FROM tbl ORDER BY key1 DESC, key2 DESC LIMIT 10;

僅用索引樹解決 (索引欄位為數值)

SELECT key1, key2 FROM tbl WHERE key1 = val;
SELECT COUNT(*) FROM tbl WHERE key1=val1 AND key2=val2;
SELECT key2 FROM tbl GROUP BY key1;

索引排序避免額外排序

SELECT ... FROM tbl ORDER BY key1, key2...
SELECT ... FROM tbl ORDER BY key1 DESC, key2 DESC...