核心原則：讓優化器用上索引
優化器根據統計資訊決定掃描策略。寫法不當導致索引失效 → 全表掃描 → 效能指數級下降。海量資料下，劣質 SQL 與優質 SQL 差距可達百倍。
六大索引失效模式與改寫
| 模式 | 失效寫法 | 根因 | 優化寫法 |
|---|
| IS NULL/IS NOT NULL | WHERE col IS NULL | B+ 樹不儲存 NULL (單列索引) | 預設值 + WHERE col = 'default'、或位圖索引 (其他引擎) |
| 欄位運算/函數 | WHERE YEAR(date_col) = 2023 | 索引存原始值，運算後無法匹配 | WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01' |
| 隱含型別轉換 | WHERE varchar_col = 123 | 字串轉數字導致全掃描 | WHERE varchar_col = '123' |
| LIKE 通配符前置 | WHERE name LIKE '%abc%' | 無法利用索引前綴 | 全文索引、前綴匹配 LIKE 'abc%'、應用層搜尋引擎 |
| OR 跨欄位無聯合索引 | WHERE a=1 OR b=2 | 無 (a,b) 聯合索引時各自掃描合併 | UNION ALL 分開查詢、或建立聯合索引 |
| 複合索引最左前綴違反 | 索引 (a,b,c) 查 WHERE b=1 | 未包含最左列 a | 補查 WHERE a=... AND b=1、或建 (b) 索引 |
ORDER BY 效能殺手
ORDER BY non_index_col → filesort (磁碟/記憶體排序)。ORDER BY indexed_col DESC + LIMIT 10 → 反向掃描索引，極快。ORDER BY expr(col) → 無法用索引。- 解決：覆蓋索引包含 ORDER BY 列、或應用層排序 (小結果集)。
NOT/!=/<> 索引失效
-- 失效
WHERE salary <> 5000
WHERE NOT (salary = 5000)
-- 可用索引 (範圍查詢)
WHERE salary < 5000 OR salary > 5000
-- 或應用層邏輯判斷
IN vs EXISTS：執行邏輯差異
| 特性 | IN (子查詢) | EXISTS (相關子查詢) |
|---|
| 執行順序 | 先執行子查詢 → 物化結果集 → 外部查詢掃描匹配 | 外部查詢驅動 → 每行執行子查詢 → 找到即停止 |
| 臨時表 | 建立索引臨時表儲存子查詢結果 | 無臨時表 |
| 適用場景 | 子查詢結果極小、外部表大 | 外部表小、子查詢有索引支援 |
| NULL 陷阱 | NOT IN 含 NULL → 全假 | NOT EXISTS 安全 |
現代優化器 (5.6+)：自動將 IN 轉半連接、EXISTS 同等對待。建議統一用 EXISTS/NOT EXISTS，語意清晰、避免 NULL 陷阱。
實戰優化清單
EXPLAIN FORMAT=JSON 觀察 access_type (ref/range/index/ALL)、key、rows、filtered。- 覆蓋索引：
SELECT id, name FROM t WHERE status=1 → 索引 (status, id, name) 避免回表。
- 延遲關聯：大表分頁
SELECT * FROM t ORDER BY id LIMIT 100000, 20 → SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 20) AS d USING (id)。
- 統計資訊鮮度：
ANALYZE TABLE 定期更新，innodb_stats_persistent=ON。
結語
高效能 SQL 核心在於理解優化器決策邏輯、避開索引失效模式、善用覆蓋索引與延遲關聯。將 EXPLAIN 納入 Code Review 標準流程，每條上線 SQL 必須經過執行計畫審視，才能在海量資料下保持穩定高效能。
- 上一篇文章： MySQL 10 個鮮為人知的實用技巧：XML 輸出、快速重建索引、壓縮表、CSV 匯出、Strict 模式與監控命令
- 下一篇文章： MySQL 欄位型別選擇對查詢效能的影響：定長 vs 變長、數值型別精度、ENUM 優化與 BLOB 隔離策略
Copyright ©
程式碼之家 All Rights Reserved
