本文系統梳理 SQL 優化全鏈路：從 SHOW STATUS 定位高頻操作類型，通過慢查詢日誌與 SHOW PROCESSLIST 鎖定問題語句，用 EXPLAIN 剖析執行計劃，再結合索引原理、寫法重構、架構模式給出 34 條可落地的優化鐵律。
一、 優化前的「體檢」三步走
- 掌握負載畫像：
SHOW GLOBAL STATUS LIKE 'Com_%' 統計增刪改查比例；Innodb_rows_% 精確到行級增量；Com_commit/rollback 暴露事務異常。
- 定位慢 SQL：開啟
--log-slow-queries 記錄超時語句；實時排查用 SHOW PROCESSLIST 看鎖等待與執行狀態。
- 解讀執行計劃：
EXPLAIN 關注 type（目標：ref/range 以上）、key（實際用索引）、rows（掃描行數）、Extra（避免 Using filesort/temporary）。
二、 索引使用的「生死線」
- 最左前綴原則：複合索引
(a,b,c)，條件必須從 a 開始連續匹配。
- 優化器放棄索引的 6 大場景：全表掃描更快、Heap 表非等值查詢、OR 條件兩側索引不全、非前綴列、
LIKE '%...'、字符串不加引號隱式轉換。
- 監控指標：
Handler_read_key 高 = 索引生效；Handler_read_rnd_next 高 = 全掃描嚴重。
三、 查詢寫法重構 34 條鐵律（精華版）
| 類別 | 反模式 → 最佳實踐 | 核心原理 |
|---|
| WHERE 條件 | col IS NULL → 設默認值 0/-1 查 col=0 | NULL 不入索引，IS NULL 導致全掃 |
col != val / <> val → 改用 < > = BETWEEN IN | 不等值不走索引 |
WHERE a=1 OR b=2 → UNION ALL 合併 | OR 導致全掃（MyISAM 例外需所有列有獨立索引） |
IN (1,2,3) 連續值 → BETWEEN 1 AND 3 | IN 列表大時優化器可能放棄索引 |
LIKE '%abc' → 全文索引或 LIKE 'abc%' | 前導通配符殺死索引 |
| 類別 | 反模式 → 最佳實踐 | 核心原理 |
|---|
| 運算與函數 | WHERE col/2=100 → WHERE col=200 | 列上運算導致索引失效 |
WHERE SUBSTRING(col,1,3)='abc' → WHERE col LIKE 'abc%' | 函數破壞索引列有序性 |
WHERE DATEDIFF(day, dt, '2005-11-30')=0 → WHERE dt>='2005-11-30' AND dt<'2005-12-01' | 範圍查詢利用索引有序性 |
參數化查詢 @var → WITH (INDEX(...)) 強制或動態 SQL | 編譯時未知值，優化器無法用直方圖估算 |
| 類別 | 反模式 → 最佳實踐 | 核心原理 |
|---|
| 索引設計 | 複合索引列順序 = WHERE 平等條件 → 範圍條件 → ORDER BY 列 | 最左前綴 + 預排序 |
| 單表索引 ≤ 6 個，高頻更新表慎加索引 | 每增一索引，INSERT/UPDATE 多 4-5 次 I/O 維護 |
| 避免更新聚簇索引列 | 聚簇索引順序 = 物理存儲順序，更新觸發頁分裂/重組 |
| 類別 | 反模式 → 最佳實踐 | 核心原理 |
|---|
| 數據類型 | 純數字業務鍵 → 數值型（INT/BIGINT） | 字符比較逐字符，數值一次比較 |
| 固定長 CHAR → 變長 VARCHAR | 節省空間，縮小掃描範圍 |
SELECT * → 顯式列出所需列 | 減少網絡傳輸、緩存污染、覆蓋索引機會 |
| 類別 | 反模式 → 最佳實踐 | 核心原理 |
|---|
| 子查詢與集合 | IN (subquery) → EXISTS (subquery) | EXISTS 半連接，滿足即返回，不產生中間結果集 |
UNION 去重 → UNION ALL 無去重 | UNION 需排序去重，消耗 SORT_AREA_SIZE |
DISTINCT → EXISTS 或分組 | DISTINCT 觸發排序，大結果集極慢 |
| 類別 | 反模式 → 最佳實踐 | 核心原理 |
|---|
| 臨時表/游標/事務 | 臨時表 → 表變量（小數據）或導出表（CTE） | 減少系統表鎖定，表變量僅主鍵索引 |
| 游標循環 → 集合操作（JOIN/窗口函數） | 游標單行處理，1 萬行以上必改寫 |
| 大事務 → 拆小事務/批次提交 | 長鎖定殺並發，增大回滾段壓力 |
| 類別 | 反模式 → 最佳實踐 | 核心原理 |
|---|
| 聚合與排序 | COUNT(1)/COUNT(pk) → COUNT(*) | 引擎對 COUNT(*) 有特殊優化（MyISAM 直接讀元數據，InnoDB 掃描最小索引） |
GROUP BY 隱式排序 → ORDER BY NULL 禁止 | 避免不必要的排序開銷 |
ORDER BY col 無索引 → 建複合索引 (where_col, order_col) | WHERE 用掉索引後無索引可排序 |
四、 EXPLAIN 關鍵列深度解讀
type：system > const > eq_ref > ref > ref_or_null > range > index > ALL（目標至少 range）。key_len：越短越好（不損失精度前提下），複合索引驗證用了幾列。Extra 紅旗：Using filesort（排序未用索引）、Using temporary（產生臨時表）、Using where（Server 層過濾，非存儲引擎層）。
五、 核心 DML 範式速查
-- 批量插入（單語句多 VALUES）
INSERT INTO t(a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9);
-- 清空表（極速，不記錄日誌，重置自增）
TRUNCATE TABLE t;
-- SELECT 插入（跨表複製/備份）
INSERT INTO target(a,b) SELECT a,b FROM source WHERE cond;
-- 整表複製結構+數據
SELECT * INTO new_table FROM old_table;
-- 結構變更技巧：SELECT INTO 新表(含新結構) → 改名替換舊表
優化無銀彈，但有鐵律：先測量、再診斷、後手術、必複查。將這 34 條內化為代碼審查清單，性能問題將在萌芽狀態被消滅。
