MySQL SQL 語句優化核心原則：減少 IO、降低 CPU、改變執行計劃的 11 條鐵律

MySQL SQL 語句優化核心原則：減少 IO、降低 CPU、改變執行計劃的 11 條鐵律

SQL 優化的本質只有一個：**改變執行計劃，讓查詢走捷徑**。本文確立「減少 IO、降低 CPU」雙目標，揭露 5 大常見誤區，制定 11 條優化鐵律，並深度解析 COUNT、SELECT *、ORDER BY、JOIN、子查詢、OR、UNION 等關鍵點優化策略。

一、 優化雙目標

1. 減少 IO 次數：IO 佔 90%+ 響應時間，優先級最高。
2. 降低 CPU 計算：排序、分組、去重消耗 CPU，IO 優化到位後的第二目標。

唯一手段：改變 SQL 執行計劃（Execution Plan）。

二、 5 大常見誤區（避坑指南）

三、 11 條優化鐵律

1. 盡量少 JOIN：MySQL 優化器統計信息有限，多表 JOIN 易產生惡劣計劃。單表查詢性能極強。
2. 盡量少排序：利用索引順序、減少參與排序行數、非必要不排序。
3. 盡量避免 SELECT *：影響覆蓋索引判斷、增加排序內存開銷、網絡傳輸量。
4. 盡量用 JOIN 代替子查詢：MySQL 子查詢執行計劃歷史包袱重，JOIN 更穩定高效。
5. 盡量少 OR：WHERE 中 OR 導致優化器難用索引，改用 UNION ALL 分開查詢合併。
6. 盡量用 UNION ALL 代替 UNION：UNION 需去重排序，UNION ALL 無去重，極快。
7. 盡量早過濾：索引列順序、JOIN 先過濾小表再關聯大表、分頁先過濾主表再 JOIN。
8. 避免類型轉換：WHERE 列類型與參數不一致導致隱式轉換，**索引失效**。轉換應在參數側進行。
9. 優先優化高併發 SQL：高併發慢 SQL 摧毀系統的速度遠超低頻大 SQL。
10. 從全局出發優化：調整索引必須評估所有 SQL 影響，不能因小失大。
11. 對每條 SQL 執行 EXPLAIN：心中有數，發現執行計劃問題才能優化。

四、 關鍵點深度解析

1. COUNT(*) 最佳實踐

• InnoDB 無觸發器/外鍵時，COUNT(*) 走聚簇索引掃描，並行讀取。
• 頻繁統計總行數：維護計數表 或 用 Redis 緩存，定期同步。

2. 索引覆蓋優化 SELECT 列

-- 僅查詢索引包含的列，避免回表
SELECT id, name FROM users WHERE status=1; -- 若有 idx_status_name(id, name, status) 可覆蓋

3. ORDER BY 索引優化

-- 索引 (a, b) 支持 ORDER BY a, b / ORDER BY a DESC, b DESC
-- 不支持 ORDER BY a ASC, b DESC (混合排序) 或 ORDER BY b (非最左前綴)

4. 子查詢轉 JOIN 範例

-- 子查詢
SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE cond);
-- JOIN 改寫
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.t1_id WHERE t2.cond;

5. OR 改 UNION ALL

-- OR 可能全表掃描
SELECT * FROM t WHERE a=1 OR b=2;
-- UNION ALL 走各自索引
SELECT * FROM t WHERE a=1
UNION ALL
SELECT * FROM t WHERE b=2 AND a!=1; -- 避免重複

五、 總結

SQL 優化無銀彈，有的是對執行計劃的深度理解、對業務訪問模式的精準把控、對「全局視角」的堅守。將 11 條鐵律內化為代碼審查清單，配合持續的 EXPLAIN 審計，讓慢 SQL 無處遁形。