SQL 語句優化四大鐵律：避開索引失效陷阱，讓查詢飛起來

鐵律 1：別在 WHERE 條件左側對欄位做運算

-- ❌ 差：索引失效，全表掃描
WHERE -col1 = 5
WHERE col1 + 10 > 20
WHERE YEAR(create_time) = 2023

-- ✅ 好：運算移到右側常數，索引生效
WHERE col1 = -5
WHERE col1 > 10
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'

原理：左側運算導致優化器無法使用索引範圍掃描，只能逐行計算後比較。

鐵律 2：隱性類型轉換是效能殺手

-- ❌ 差：col1 為數值型，卻用字串比較
WHERE col1 = '2'

-- ✅ 好：型別一致
WHERE col1 = 2

原理：資料庫需將雙方轉為同一型別（通常為雙精度浮點），導致索引無法匹配。

鐵律 3：慎用函數包裝欄位，善用範圍查詢替代

-- ❌ 差：DATEDIFF 導致索引失效
WHERE DATEDIFF(day, col1, GETDATE()) = 0

-- ✅ 好：等價範圍查詢，索引生效
WHERE col1 >= '2023-10-26' AND col1 < '2023-10-27'

原理：任何包裹欄位的函數（UPPER、SUBSTRING、ISNULL、COALESCE 等）都會阻斷索引求值。

鐵律 4：OR 條件謹慎使用，優先考慮 UNION ALL 改寫

-- ❌ 風險：優化器常選擇全表掃描
WHERE col1 = 1 OR col2 = 2

-- ✅ 好：拆分為兩次索引查詢合併
SELECT * FROM t WHERE col1 = 1
UNION ALL
SELECT * FROM t WHERE col2 = 2;

原理：OR 跨欄位條件難以單一索引覆蓋，優化器傾向放棄索引。UNION ALL 可分別走各自最優索引，再合併結果（注意去重需用 UNION）。

進階補充：其他常見索引失效場景

• LIKE 以萬用字元開頭：WHERE name LIKE '%張%' → 全掃；WHERE name LIKE '張%' → 索引生效
• IS NULL / IS NOT NULL：部分資料庫不走索引，可用 WHERE col = 0 或預設值替代
• 隱性轉換：WHERE varchar_col = 123 → 數字轉字串，索引失效
• 複合索引最左前綴：INDEX(a,b,c)，查詢必須包含 a 才能生效

驗證工具

• SET STATISTICS IO ON / SET STATISTICS TIME ON：觀察邏輯讀數與 CPU 時間
• SET SHOWPLAN_ALL ON / 圖形化執行計畫：確認 Index Seek vs Index Scan vs Table Scan
• sys.dm_db_index_usage_stats：長期監控索引實際使用情況