SQL 語句優化三大黃金法則：快、省 CPU、少 I/O

優化目標：三維平衡

編寫高性能 SQL 的終極目標只有三個：

1. 快速響應時間：用戶可感知的延遲最低。
2. 最少 CPU 消耗：減少複雜運算、排序、哈希連接開銷。
3. 最少 I/O 操作：減少磁盤讀寫，善用內存緩衝池。

三者往往相輔相成：少 I/O → 少 CPU 處理數據 → 快響應。

核心優化提示清單

1. 索引不是萬能藥：何時選全表掃描？

當優化器預估索引掃描需訪問的數據頁 ≥ 全表掃描頁數時（通常超過表行數的 20%-30%），全表掃描反而更快。不要強制索引，讓基於成本的優化器（CBO）根據統計信息決策。

2. 子查詢優先優化，能 JOIN 就 JOIN

• 子查詢執行效率低 → 主查詢拖累。
• 關聯子查詢（Correlated Subquery）逐行執行，CPU 開銷極大。
• 改寫策略：IN (SELECT ...) → JOIN；EXISTS 通常比 IN 更穩定；衍生表物化（MySQL 5.6+）可緩解部分問題。

3. NOT EXISTS 替代 NOT IN

-- ❌ NOT IN：若子查詢返回 NULL，整個結果為空（三值邏輯陷阱）
SELECT * FROM t1 WHERE id NOT IN (SELECT t1_id FROM t2);

-- ✅ NOT EXISTS：語義清晰，無 NULL 陷阱，優化器易轉為 Anti-Join
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.t1_id = t1.id);

4. LIKE 前綴查詢利用索引，SUBSTR 導致失效

-- ✅ 走索引（前綴匹配）
WHERE name LIKE 'Zhang%'

-- ❌ 全表掃描（通配符開頭）
WHERE name LIKE '%Zhang%'

-- ❌ 函數殺死索引
WHERE SUBSTR(name, 1, 5) = 'Zhang'
-- Oracle 8i+ 可建函數索引修復，MySQL 8.0 支持函數索引

5. 其他高頻優化模式

工具鏈：從 EXPLAIN 到 Profile

1. EXPLAIN FORMAT=JSON SELECT ...：查看 cost_info、used_columns、attached_condition。
2. SHOW PROFILE FOR QUERY query_id;：細粒度 CPU/IO/鎖/網絡耗時（需啟用 profiling）。
3. OPTIMIZER_TRACE：MySQL 5.6+ 追蹤優化器決策全過程。
4. 慢查詢日誌（long_query_time=0 + log_queries_not_using_indexes）配合 pt-query-digest 分析 Top SQL。

總結

SQL 優化無銀彈，但遵循「少掃描、少交互、少計算、善用索引、避開陷阱」五字訣，配合持續的慢查詢審計與 EXPLAIN 驗證，即可解決 90% 性能問題。記住：最好的優化是不寫爛 SQL，其次是寫對索引，最後才是調參數。