MySQL 5.6 慢查詢優化實戰 (二)：EXPLAIN 深度解析與 SQL 改寫技巧

慢查詢定位回顧

前篇已通過 mysqldumpslow、pt-query-digest 定位出 Top N 慢 SQL。本篇聚焦 **單條 SQL 深度診斷與改寫**。

EXPLAIN 輸出字段逐項解讀

mysql> EXPLAIN SELECT * FROM test.t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL                  -- ⚠️ 核心指標：全表掃描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

經典優化模式

1. 消除 Using filesort

-- 原始：WHERE status=1 ORDER BY created_at DESC LIMIT 20
-- 索引：INDEX idx_status_created (status, created_at DESC)  -- 8.0 支持降序索引
-- EXPLAIN Extra: Using index condition (無 filesort)

2. 消除 Using temporary

-- 原始：SELECT category, COUNT(*) FROM products GROUP BY category ORDER BY COUNT(*) DESC
-- 問題：GROUP BY 非索引列 + ORDER BY 聚合結果 → 臨時表 + filesort
-- 優化：覆蓋索引 INDEX idx_category (category) + 強制索引
-- 或應用層緩存聚合結果

3. 覆蓋索引 (Using index)

-- 查詢：SELECT id, name FROM users WHERE email='x@y.com'
-- 索引：INDEX idx_email_name (email, name)
-- EXPLAIN: key=idx_email_name, Extra=Using index
-- 回表為零，極快

4. 範圍查詢優化

-- 原始：WHERE created_at > '2023-01-01' AND created_at < '2023-02-01'
-- 索引：INDEX idx_created (created_at)
-- 注意：> < >= <= BETWEEN 走 range，type=range
-- 避免函數列運算：WHERE DATE(created_at) = '2023-01-01' → 全表掃描
-- 改寫：WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'

5. OR 條件索引合併

-- 原始：WHERE name='Alice' OR email='alice@x.com'
-- 索引：idx_name(name), idx_email(email)
-- EXPLAIN: type=index_merge, Extra=Using union(idx_name,idx_email); Using where
-- 優化：UNION ALL 分開查詢再合並，或建立組合索引 (name,email) 配合應用層邏輯

優化後驗證閉環

1. EXPLAIN 確認 type 提升、key 命中、Extra 無 filesort/temporary。
2. FLUSH STATUS; SELECT ...; SHOW SESSION STATUS LIKE 'Handler_%'; 觀察 Handler_read_key (索引讀) vs Handler_read_rnd_next (全表掃描)。
3. 壓測工具 (sysbench/tpcc) 對比 QPS、RT、CPU、IOPS。
4. 持續監控 performance_schema.events_statements_summary_by_digest 確認無回歸。

避坑提醒：優化是雙刃劍

• 新增索引 → 寫入變慢 (每 INSERT/UPDATE/DELETE 維護索引)、空間增大、備份時間變長。
• 強制索引 FORCE INDEX → 數據分佈變化導致優化器更優選擇被屏蔽。
• 拆表/分區 → 應用層複雜度上升、跨分區查詢性能不可控。

鐵律：測量 → 優化 → 驗證 → 持續監控，每一步都有數據說話，拒絕「感覺快了」式優化。