MySQL 效能優化實戰清單：架構、索引、SQL、配置、硬體八大維度

MySQL 效能優化實戰清單：架構、索引、SQL、配置、硬體八大維度

MySQL 優化是系統工程，涵蓋邏輯設計、物理設計、執行計畫、實例配置、作業系統與硬體。本文整理八大維度檢查清單，並附贈狀態監控、慢查詢配置、EXPLAIN 分析、慢查詢日誌啟用等實操技巧。

八大優化維度

1. 表設計正規化：符合 3NF，減少冗餘與異常。
2. 適當索引：普通、主鍵、唯一、全文四類；覆蓋索引、前綴索引、複合索引最左前綴。
3. 分表技術：水平分割（分片）、垂直分割（冷熱分離）、中介層路由。
4. 讀寫分離：主寫從讀，ProxySQL/中介層路由，延遲監控。
5. 預存程序：封裝高頻多步邏輯，減少網路 RTT（慎用，難除錯）。
6. 實例配置調優：max_connections、innodb_buffer_pool_size、query_cache（5.7+ 建議關閉）、thread_pool。
7. 硬體升級：SSD/NVMe、大記憶體、多核 CPU、網路帶寬。
8. 定期維護：清理歷史資料、OPTIMIZE TABLE 整理碎片（MyISAM）、統計資訊更新 ANALYZE TABLE。

實用監控指令

1. 顯示狀態計數器

SHOW STATUS LIKE 'Uptime';                    -- 運行時間
SHOW STATUS LIKE 'Com_select';                -- SELECT 次數
SHOW STATUS LIKE 'Com_insert';                -- INSERT 次數
SHOW STATUS LIKE 'Com_update';                -- UPDATE 次數
SHOW STATUS LIKE 'Com_delete';                -- DELETE 次數
SHOW STATUS LIKE 'Connections';               -- 總連線數
SHOW STATUS LIKE 'Slow_queries';              -- 慢查詢次數

-- 會話級 vs 全域級
SHOW SESSION STATUS LIKE '...';   -- 當前連線
SHOW GLOBAL STATUS LIKE '...';    -- 啟動至今

2. 慢查詢閾值動態調整

SHOW VARIABLES LIKE 'long_query_time';  -- 顯示預設 10 秒
SET GLOBAL long_query_time = 1;         -- 即時生效，無需重啟
-- 永久生效需寫入 my.cnf: long_query_time = 1

3. 變更語句結束符

DELIMITER $$

4. EXPLAIN 執行計畫分析

EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status=1;

重點欄位：type（ALL/index/range/ref/eq_ref/const/system）、key（實際用到的索引）、rows（預估掃描行數）、Extra（Using where/Using index/Using temporary/Using filesort）。

5. 啟用慢查詢日誌

MySQL 5.5+ (my.cnf)：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

舊版 (5.0/5.1) 啟動參數：

mysqld --log-slow-queries=/var/log/mysql/slow.log

日誌預設存放於 datadir，可用 pt-query-digest 分析 Top SQL。

優化執行順序建議

1. 開啟慢查詢日誌，收集 Top 20 慢 SQL。
2. EXPLAIN 分析執行計畫，確認是否走索引、是否全表掃描。
3. 補齊缺失索引、改寫 SQL（避免 SELECT *、LIKE '%...'、隱式轉換）。
4. 調整實例參數（Buffer Pool、連線數、執行緒池）。
5. 架構層面：讀寫分離、分庫分表、冷熱資料歸檔。
6. 硬體層面：IOPS 瓶頸上 SSD、記憶體不足加記憶體。

避坑提醒

• 盲目加索引反而拖慢寫入，單表索引建議 ≤ 5 個。
• query_cache 在高併發寫入場景下常成熱點，5.7 建議關閉，8.0 已移除。
• 大表 ALTER TABLE 鎖表久，用 pt-online-schema-change 或 8.0 Instant DDL。
• 分頁深翻頁優化：WHERE id > last_id LIMIT 20 替代 OFFSET。