調優優先級金字塔
- 查詢優化 (ROI 最高)：索引設計、SQL 改寫、架構調整 — 解決根本問題
- 配置調優：記憶體分配、緩衝池、線程池 — 發揮硬件潛力
- 硬件升級：CPU、記憶體、SSD、網絡 — 最後手段，成本最高
「快的機器跑爛 SQL 也會死；爛機器跑神 SQL 也能活。」
核心配置調優清單
| 參數 | 建議值/原則 | 監控指標 |
|---|
innodb_buffer_pool_size | 專用 DB 物理內存 60-80% | Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads (命中率 > 99%) |
innodb_log_file_size | 256MB - 1GB (總大小 = 文件數 × 大小) | Innodb_log_waits = 0 |
innodb_flush_log_at_trx_commit | 1 (ACID) / 2 (高寫入可接受秒級丟失) | 磁碟 IOPS、延遲 |
max_connections | 1000-5000 (配合 open_files_limit=65535) | Max_used_connections / Aborted_connects |
table_open_cache | 4000-10000 | Opened_tables 增長趨勢 |
thread_cache_size | 100-200 | Threads_created / Connections (命中率 > 95%) |
sort_buffer_size | 2-8MB (每線程，慎設大) | Sort_merge_passes = 0 |
join_buffer_size | 2-8MB (無索引 JOIN 才用) | Select_full_join = 0 |
read_rnd_buffer_size | 1-4MB | Select_range_check |
慢查詢日誌：發現優化目標的雷達
[mysqld]
# 啟用慢查詢日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 記錄超過 1 秒的查詢 (生產建議 0.5-1)
log_queries_not_using_indexes = 1 -- 記錄無索引查詢 (MySQL 5.1+)
# 可選：只記錄管理員查詢
# log_slow_admin_statements = 1
分析工具鏈
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log — 按時間排序 TOP 10pt-query-digest /var/log/mysql/slow.log — Percona Toolkit 深度分析 (聚合、指標、EXPLAIN)EXPLAIN SELECT ... — 手工驗證執行計劃
查詢優化核心清單
| 優化點 | 檢查方法 | 修正動作 |
|---|
| 缺失索引 | EXPLAIN type=ALL / key=NULL | 加覆蓋索引、複合索引 (最左前綴) |
| 隱式類型轉換 | EXPLAIN 輸出 CAST / 函數 | 應用層參數類型匹配、欄位類型一致 |
| SELECT * | 代碼掃描 / 慢查詢輸出 | 顯式列出需要的列 |
| 大事務/長事務 | information_schema.INNODB_TRX time > 5s | 拆分事務、移出非 DB 操作 |
| 深分頁 | LIMIT 100000, 20 | 延遲關聯 / 游標分頁 / 搜索引擎 |
| OR/IN 導致索引失效 | EXPLAIN range → ALL | UNION ALL 拆分、重寫為 EXISTS |
硬件升級決策矩陣
| 瓶頸指標 | 升級方向 | 預期收益 |
|---|
| CPU 系統佔用 > 70% (非 IO wait) | 更高主頻 CPU / 更多核心 | 並行查詢、複雜計算加速 |
| IOPS 滿載 / IO wait > 20% | NVMe SSD / RAID 10 / 更多磁碟 | 隨機讀寫、Redo Log 刷盤、備份加速 |
| 內存不足 (Swap 使用 > 0) | 加大內存 (線性擴展 Buffer Pool) | 緩存命中率提升、減少磁碟讀取 |
| 網絡帶飽和 | 萬兆網卡 / 壓縮協議 / 讀寫分離 | 主從同步延遲降低、客戶端響應提升 |
總結
MySQL 調優 = 慢查詢日誌啟動 → pt-query-digest 定位 TOP SQL → 索引/SQL 重寫 → 配置參數匹配負載 → 硬件補齊短板。建立「每周慢查詢審視、每月參數複盤、每季容量規劃」機制，將調優從救火變為常態。
