SQL 優化核心原則：避開導致全表掃描的 7 大 WHERE 子句寫法陷阱

核心觀念：優化器依賴索引，寫法決定索引生死

Oracle/MySQL 優化器根據統計資訊選擇執行計畫。WHERE 子句寫錯 → 索引失效 → 全表掃描 → 效能崩潰。海量資料下，優質與劣質 SQL 差距可達百倍以上。

七大索引殺手寫法與改寫方案

1. IS NULL / IS NOT NULL

-- ❌ 索引失效
WHERE status IS NULL
WHERE status IS NOT NULL

-- ✅ 改寫：使用預設值或特殊標記值
WHERE status = 0          -- 0 代表未處理
WHERE status != 0         -- 非 0 代表已處理
-- 或建立函數索引（MySQL 8.0+）
CREATE INDEX idx_status_null ON orders ((status IS NULL));

原理：B+ 樹索引預設不儲存 NULL 值（或難以定位），優化器直接放棄索引。

2. 欄位聯接/運算（隱含轉換、函數、拼接）

-- ❌ 索引失效
WHERE first_name || ' ' || last_name = 'Bill Clinton'
WHERE YEAR(created_at) = 2024
WHERE UPPER(email) = 'TEST@EXAMPLE.COM'
WHERE price + tax > 100

-- ✅ 改寫：將運算移至常數側
WHERE first_name = 'Bill' AND last_name = 'Clinton'
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE email = 'test@example.com'  -- 連線校對不區分大小寫時
WHERE price > 100 - tax

原理：索引儲存原始欄位值，任何運算破壞索引順序，優化器無法利用。

3. LIKE 前導萬用字元

-- ❌ 全表掃描
WHERE name LIKE '%keyword%'
WHERE name LIKE '%keyword'

-- ✅ 方案
-- 前綴匹配可用索引
WHERE name LIKE 'keyword%'

-- 含頭尾萬用字元必須用 FULLTEXT 或外部搜尋引擎

4. OR 連接不同索引欄位

-- ❌ 通常全表掃描
WHERE indexed_a = 1 OR indexed_b = 2

-- ✅ 改寫：UNION ALL（去重用 UNION）
SELECT * FROM t WHERE indexed_a = 1
UNION ALL
SELECT * FROM t WHERE indexed_b = 2 AND indexed_a != 1;
-- 或建立複合索引覆蓋查詢

5. 隱含型別轉換

-- ❌ VARCHAR 欄位比較數字
WHERE varchar_col = 123
WHERE char_col = 1

-- ✅ 明確字串字面值
WHERE varchar_col = '123'
WHERE char_col = '1'

6. NOT IN / NOT EXISTS 子查詢（大資料集）

-- ❌ 可能全表掃描
WHERE id NOT IN (SELECT id FROM other_table)
WHERE NOT EXISTS (SELECT 1 FROM other_table WHERE other_table.id = t.id)

-- ✅ 改寫：LEFT JOIN ... IS NULL
SELECT t.* FROM t
LEFT JOIN other_table o ON t.id = o.id
WHERE o.id IS NULL;

7. 複合索引最左前綴違反

-- 索引：INDEX (a, b, c)
-- ❌ 無法使用
WHERE b = 1
WHERE c = 1
WHERE b = 1 AND c = 2

-- ✅ 可使用
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

驗證工具：EXPLAIN 讀懂執行計畫

EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';
-- 關鍵欄位：
-- type: 目標 ref / range / eq_ref（避免 ALL=全表掃描）
-- key: 實際使用的索引名
-- rows: 預估掃描行數（越小越好）
-- Extra: Using index（覆蓋索引）、Using where、Using temporary/filesort（需優化）

持續優化流程

1. 開啟慢查詢日誌：slow_query_log=1, long_query_time=1。
2. 定期 pt-query-digest 分析 Top 10 慢查詢。
3. 對每條慢查詢執行 EXPLAIN，識別索引缺失/失效。
4. 補索引 / 改寫 SQL / 調整參數 / 分區 / 快取，驗證效果。
5. 上線前在測試環境壓測驗證。