MySQL 效能調校三部曲：硬體、參數與查詢優化實戰

MySQL 效能優化層級與優先順序

資料庫效能調校遵循「硬體 → 參數 → 查詢」三層次，效果遞減但成本也遞減。本文聚焦參數調校與慢查詢識別，查詢優化另文詳述。

第一層：硬體資源 (效果最大、成本最高)

• CPU：高主頻 > 多核心 (MySQL 單查詢單執行緒)
• 記憶體：越大越好，InnoDB 建議 70-80% 給 innodb_buffer_pool_size
• 儲存：NVMe SSD > SATA SSD > HDD，RAID 10 平衡效能與冗餘
• 網路：萬兆內網、低延遲、啟用 Jumbo Frames

第二層：MySQL 參數調校 (核心變數)

InnoDB 關鍵參數

連線與快取參數

第三層：慢查詢識別與分析

啟用慢查詢日誌

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # 超過 1 秒記錄
log_queries_not_using_indexes = 1  # 記錄無索引查詢
log_slow_admin_statements = 1      # 記錄管理指令
min_examined_row_limit = 100       # 忽略掃描少於 100 行的查詢

分析工具鏈

1. mysqldumpslow：內建彙總工具

   mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

2. pt-query-digest (Percona Toolkit)：專業分析

   pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{fingerprint} !~ /^SET/'

3. EXPLAIN 分析單一查詢：

   EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
   -- 重點看：type (ref/range/ALL)、key、rows、Extra (Using where/index/temporary/filesort)

慢查詢優化標準流程

1. 從慢查詢日誌取出 Top 10 指紋
2. 對每條執行 EXPLAIN，確認是否全表掃描 (type=ALL)
3. 檢查索引覆蓋度：SHOW INDEX FROM table
4. 補建索引或重寫查詢 (避免 SELECT *、拆分大查詢、優化 JOIN 順序)
5. 驗證優化後執行計畫與實測時間

監控指標儀表板 (建議 Grafana + Prometheus)

• QPS / TPS 趨勢
• InnoDB 緩衝池命中率 (> 99%)
• 執行緒運行狀態 (Running/Connected)
• 慢查詢數量/秒
• 磁碟 I/O 使用率、等待時間
• 複寫延遲 (若有主從)

調校是持續迭代過程：「監控 → 發現瓶頸 → 調整參數/索引/查詢 → 驗證 → 監控」。建議建立變更管理流程，所有參數變更留存紀錄與回滾方案。