高效能 SQL 撰寫指南：避免全表掃描的 5 大關鍵技巧

前言

開發初期資料量小感覺不出 SQL 效能差異，上線後資料增長，劣質 SQL 與優質 SQL 速度可差上百倍。優化核心在於讓優化器使用索引，避免全表掃描。

五大導致索引失效的寫法與修正

1. IS NULL / IS NOT NULL

問題：任何包含 NULL 的欄位不會被納入索引 (即使複合索引，只要一欄有 NULL 整列排除)。WHERE 條件用 IS NULL 或 IS NOT NULL 直接導致全表掃描。

對策：

• 設計階段盡量 NOT NULL DEFAULT '' 或預設值
• 查詢改用 col = '' 或 col = 0 等具體值比較
• 必須查 NULL 時，考慮建立函數索引 (MySQL 8.0+) 或維護標記欄位

2. 欄位聯接運算

問題：WHERE first_name || ' ' || last_name = 'Bill Clinton' — 即使 last_name 有索引，聯接運算導致索引失效。

修正：

-- 拆解為獨立條件，各自命中索引
WHERE first_name = 'Bill' AND last_name = 'Clinton'

變數情況：應用層拆分變數再傳參數，勿在 SQL 內用 SUBSTR/INSTR 處理索引欄位。

3. LIKE 通配符前置 %

問題：WHERE last_name LIKE '%clinton%' — 開頭 % 導致索引失效。

可用索引：WHERE last_name LIKE 'c%' — 通配符在後方可用索引。

對策：

• 全文搜尋需求用 MyISAM FULLTEXT 索引或專用搜尋引擎
• 前綴搜尋 (如自動完成) 確保條件為 前綴% 形式

4. ORDER BY 非索引欄位或運算式

問題：ORDER BY col + 1、ORDER BY UPPER(name)、ORDER BY 非索引欄位 會觸發 filesort，大量資料極慢。

對策：

• ORDER BY 僅用索引欄位
• 需排序運算結果時，建立對應函數索引或物化視圖
• 分頁查詢優化：WHERE id > last_id ORDER BY id LIMIT N 替代 OFFSET

5. NOT 邏輯否定

問題：WHERE NOT (status = 1)、WHERE col != 'value'、WHERE col NOT IN (...) 常導致優化器放棄索引。

修正：

• 改用正向條件：WHERE status IN (0,2,3)、WHERE col = 'other_value'
• NOT EXISTS 通常比 NOT IN 效能好 (尤其子查詢回傳 NULL 時)
• 必要時用 UNION ALL 重寫

通用優化原則

1. EXPLAIN 分析：每條關鍵 SQL 必跑 EXPLAIN 確認 type、key、rows
2. 最左前綴原則：複合索引順序 = WHERE/Order By/Group By 欄位順序
3. 覆蓋索引：SELECT 索引欄位 FROM ... 避免回表
4. 適度反正規化：關鍵查詢路徑冗餘欄位換查詢速度
5. 定期 ANALYZE TABLE：更新統計資訊讓優化器決策更準