MySQL 實戰優化兩則：USE INDEX 消除 filesort、去除冗餘 DISTINCT 提升 10 倍性能

MySQL 實戰優化兩則：USE INDEX 消除 filesort、去除冗餘 DISTINCT 提升 10 倍性能

慢查詢優化不靠猜，靠 EXPLAIN 看執行計劃。本文記錄兩個真實優化案例：一條 ORDER BY 因索引選擇錯誤導致 Using temporary; Using filesort，通過 USE INDEX 強制走正確索引，耗時從 1.5s 降至 0.015s（100 倍）；一條一對一 JOIN 多餘 DISTINCT 導致臨時表，去除後提升 10 倍。並總結優化通用方法論。

案例一：ORDER BY 索引選擇失誤

現象

• 接口：無線終端頻道頁列表
• 症狀：高並發下 MySQL 宕機，運維未給具體 QPS
• 慢查詢日志：同一條 SQL 大量出現，耗時 1.5s+

SQL 與表結構

SELECT * FROM channel
WHERE status = 1
ORDER BY zj_lastupdate DESC
LIMIT 20;

表 channel 有多個聯合索引，zj_lastupdate 也有單列索引。

EXPLAIN 診斷

id: 1
table: channel
type: ref
possible_keys: idx_status, idx_zj_lastupdate, idx_status_type...
key: idx_status          -- 優化器選了 status 索引
key_len: 2
ref: const
rows: 50000
Extra: Using where; Using temporary; Using filesort  -- 關鍵紅旗

優化器選擇 idx_status 過濾後需再排序，觸發臨時表+文件排序。

優化動作

SELECT * FROM channel USE INDEX (idx_zj_lastupdate)
WHERE status = 1
ORDER BY zj_lastupdate DESC
LIMIT 20;

優化後 EXPLAIN

key: idx_zj_lastupdate
Extra: Using where  -- 無 temporary、filesort

效果

• 耗時：1.5s → 0.015s（100 倍提升）
• 宕機現象消失，無需架構重構

案例二：一對一 JOIN 多餘 DISTINCT

原 SQL

SELECT DISTINCT movie_id AS id, ...
FROM movie m
JOIN movie_detail d ON m.id = d.movie_id
WHERE ...;

EXPLAIN 診斷

table: m
type: ALL
Extra: Using temporary  -- DISTINCT 強制去重產生臨時表

分析

• movie 主鍵 id，movie_detail 主鍵 movie_id，嚴格一對一
• JOIN 後行數不變，DISTINCT 純屬畫蛇添足

優化

SELECT movie_id AS id, ...
FROM movie m
JOIN movie_detail d ON m.id = d.movie_id
WHERE ...;

效果

• 性能提升約 10 倍

優化方法論總結

1. 拿到慢 SQL 先跑 EXPLAIN，關注 type、key、rows、Extra
2. 消除 Using filesort：索引列順序匹配 ORDER BY、避免跨表排序、必要時 USE INDEX/FORCE INDEX
3. 消除 Using temporary：GROUP BY/ORDER BY 走同一索引、覆蓋索引、去除冗餘 DISTINCT
4. 覆蓋索引：SELECT 列全部在索引中，實現 Using index
5. 統計信息新鮮度：定期 ANALYZE TABLE，防止優化器誤判
6. SQL 改寫優於架構調整：很多性能問題通過索引、SQL 改寫即可解決，避免過度設計
7. 驗證結果正確性：優化前後對比數據集、邏輯等價

常用優化手段清單

工具鏈推薦

• pt-query-digest：慢查詢日志分析 Top N
• sys.schema_* 視圖：表統計、索引使用率、冗余索引檢測
• EXPLAIN ANALYZE (8.0.18+)：實際執行耗時、循環次數
• Optimizer Trace：優化器決策細節