您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 效能診斷與調優完整方法論：從索引到架構

編輯：MySQL 資料庫相關知識

效能問題四大根因

索引缺失或設計不當
SQL 寫法過於複雜
配置參數錯誤
硬體資源瓶頸

實戰中 80% 以上問題集中在前兩項，配置與硬體通常是最後手段。

第一步：現場蒐證 —— SHOW FULL PROCESSLIST

mysql> SHOW FULL PROCESSLIST;

關注欄位：

Time：執行秒數，長時間運行者優先排查。
State：Locked、Sending data、Copying to tmp table、Sorting result 等。
Info：實際 SQL 文本（可能被截斷）。

正常狀態下應幾乎看不到活躍查詢；若持續看到大量 SQL，即有效能問題。

第二步：索引優化核心 —— EXPLAIN 深度解析

關鍵欄位含義

欄位	重點
type	從好到壞：`system > const > eq_ref > ref > range > index > ALL`。避免 `ALL`（全表掃描）。
key	實際使用的索引；`NULL` 代表未用索引。
rows	預估掃描行數，越小越好。
Extra	`Using index`（覆蓋索引）、`Using where`、`Using filesort`（需排序）、`Using temporary`（臨時表）。

實戰案例：ORDER BY + LIMIT 導致 filesort

-- 原始慢查詢
SELECT * FROM imgs WHERE userid='admin' ORDER BY clicks DESC LIMIT 10;
-- EXPLAIN 結果：type=ALL, rows=12506, Extra=Using where; Using filesort

嘗試 1：單欄索引 userid

CREATE INDEX idx_userid ON imgs(userid);
-- EXPLAIN：type=ref, rows=2944, Extra=Using where; Using filesort
-- 結果集仍 2944 行，需在記憶體排序。

嘗試 2：單欄索引 clicks + 強制使用

CREATE INDEX idx_clicks ON imgs(clicks);
SELECT * FROM imgs USE INDEX (idx_clicks) WHERE userid='admin' AND clicks>1000 ORDER BY clicks DESC LIMIT 10;
-- EXPLAIN：type=range, rows=312, Extra=Using where
-- 效能可接受，但需人工決定閾值（1000），不同 userid 閾值不同，難以通用。

最佳解：複合索引 (userid, clicks)

CREATE INDEX idx_userid_clicks ON imgs(userid, clicks);
-- EXPLAIN：type=ref, rows=2944, Extra=Using where
-- 關鍵：filesort 消失！索引已按 clicks 排序，直接取前 10 筆。

複合索引設計原則

等值查詢欄位在前（userid），排序/範圍欄位在後（clicks）。
避免過多索引：寫入效能下降、磁碟空間暴增、統計資訊不準。
定期 ANALYZE TABLE 更新統計資訊，讓優化器選擇最優索引。

第三步：SQL 重構技巧

拆分大查詢：避免超大 JOIN、子查詢，拆為多步驟在應用層組裝。
善用覆蓋索引：SELECT id, name FROM t WHERE status=1 若有 INDEX(status, id, name) 可 Using index。
避免 SELECT *：增加 I/O、破壞覆蓋索引、網路傳輸浪費。
分頁優化：LIMIT 100000, 10 極慢，改用 WHERE id > last_id ORDER BY id LIMIT 10（游標式分頁）。
OR 改 UNION ALL：WHERE a=1 OR b=2 常導致全掃描，拆為兩查詢 UNION ALL 可分別走索引。

第四步：關鍵配置參數（重點調整項）

參數	建議起始值	調整依據
innodb_buffer_pool_size	實體記憶體 60~80%	核心緩衝，命中率 > 99%
key_buffer_size	32~128M	MyISAM 索引/內部臨時表
sort_buffer_size	2~8M	每連接排序緩衝，過大反害
query_cache_type/size	0 / 0	寫入頻繁必關，唯讀才開
max_connections	200~500	視應用連接池設定，過大致 OOM
thread_concurrency	CPU核心 × 2	僅 Solaris 生效
wait_timeout / interactive_timeout	30~60	快速釋放閒置連接

第五步：架構層面擴展（當單機極限）

讀寫分離：主庫寫、從庫讀，中介軟體（ProxySQL、MyCat）或應用層路由。
快取層：Redis/Memcached 緩存熱點資料，降低 DB QPS。
垂直拆分：依業務拆庫，不同服務獨立 DB。
水平分表：大表按 tenant_id、日期、hash 分片。
異構方案：熱資料遷至 ClickHouse、TiDB、ES 等 OLAP/搜尋引擎。

避坑指南

不要盲目加索引，先 EXPLAIN 再決定。
不要依賴 Query Cache 解決寫入密集場景效能。
不要用 LIMIT offset, n 做深分頁，改用游標/ID 續傳。
不要在生產環境直接 ALTER TABLE 大表，用 pt-online-schema-change 或 gh-ost。
資料庫最核心指標是穩定性與資料正確性，效能其次。

總結流程圖

發現慢 → SHOW PROCESSLIST → 擷取慢 SQL → EXPLAIN 分析
    → 缺索引？→ 加索引/複合索引 → 驗證
    → SQL 複雜？→ 重構/拆分/覆蓋索引 → 驗證
    → 參數不當？→ 調整核心參數 → 驗證
    → 單機極限？→ 讀寫分離/分庫分表/異構 → 驗證

遵循此方法論，系統化解決 MySQL 效能問題，避免盲目調參走彎路。