MySQL WHERE 條件查詢完整指南：運算子、索引匹配、隱式轉換陷阱與效能優化

WHERE 子句是查詢的「過濾器」，決定掃描多少行、能否用索引、執行計劃好壞。本文系統整理運算子優先級、索引匹配原則、隱式類型轉換殺手、LIKE 模糊查詢優化、IN/EXISTS 選擇與效能調優清單。

一、常用運算子與優先級

二、索引匹配核心原則

1. 最左前綴：複合索引 (a,b,c) 僅當 WHERE 包含 a、a,b、a,b,c 且無範圍中斷時可用。
2. 等值優先： a=1 AND b=2 優於 a>1 AND b=2（範圍查詢中斷後綴）。
3. 覆蓋索引： SELECT a,b FROM t WHERE c=1 有索引 (c,a,b) → Using index，零回表。
4. 常數傳遞： WHERE a=1 AND b=2 優化器將常數下推到存儲引擎（ICP），減少回表。

三、隱式類型轉換：索引殺手

-- ❌ 字符欄位比較數字：MySQL 將字符轉數字，索引失效
WHERE varchar_col = 123;  -- 等同 WHERE CAST(varchar_col AS UNSIGNED) = 123

-- ✅ 正確：數字轉字符
WHERE varchar_col = '123';

-- ❌ 日期欄位比較字符串格式不標準
WHERE date_col = '2024-1-1';  -- 可能隱式轉換

-- ✅ 正確：標準格式或 STR_TO_DATE
WHERE date_col = '2024-01-01';
WHERE date_col = STR_TO_DATE('2024/1/1', '%Y/%m/%d');

💡 開啟 sql_mode=STRICT_ALL_TABLES 可報錯而非靜默轉換，強制修正代碼。

四、LIKE 模糊查詢優化

五、IN vs EXISTS 選擇指南

-- 外表小、內表大、內表有索引 → IN / JOIN
SELECT * FROM small_table WHERE id IN (SELECT small_id FROM big_table WHERE ...);

-- 外表大、內表小、外表有索引 → EXISTS
SELECT * FROM big_table b WHERE EXISTS (SELECT 1 FROM small_table s WHERE s.id = b.id);

-- NOT IN 含 NULL 陷阱 → 改用 NOT EXISTS
SELECT * FROM a WHERE id NOT IN (SELECT cid FROM b);  -- 若 b.cid 有 NULL，結果為空
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.cid = a.id);

六、效能優化清單

• ✅ EXPLAIN 確認 type=ref/range、key=預期索引、rows 預估合理。
• ✅ 避免 SELECT *，只取必要欄位，爭取覆蓋索引。
• ✅ 大表範圍查詢加 LIMIT 或分頁，避免全表掃描回傳海量數據。
• ✅ 定期 ANALYZE TABLE 更新統計資訊，防止優化器誤判。
• ✅ 監控 Slow_queries、Handler_read_rnd_next（全表掃描）、Select_full_join（無索引連接）。

WHERE 寫得好，索引才用得上；索引用得上，查詢才快。將每一條慢查詢的 WHERE 視為優化切入點，系統性消除隱式轉換、前導萬用字元、不當 OR/IN 等索引殺手。