21 條 MySQL 生產環境調優鐵律：從硬件選型到參數配置的系統級優化指南

硬件與基礎設施層（1-7）

1. 內存：數據庫生命線

• Buffer Pool 佔物理內存 60-80%
• 熱數據全內存：核心業務表/索引 data_size + index_size < 0.8 * innodb_buffer_pool_size
• 內存不足時優先加內存，而非調參數

2. CPU：核心數 > 主頻

• MySQL 5.6+ 支持 64 核擴展，單 Query 單核執行
• 高並發選擇多核（32-64 核），單核主頻 2.8GHz+ 即可
• 關閉超線程（BIOS 層面），避免上下文切換開銷

3. 作業系統：穩定優於新潮

• 首選：CentOS 7/8、RHEL 7/8、Oracle Linux 8（內核 3.10+、4.x+）
• 生產禁用 Windows（IO 調度、內存管理、鎖機制劣勢明顯）
• 文件系統：XFS（元數據並行、大文件優勢）> ext4（data=ordered）

4. 系統參數：三大必調

# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535

# /etc/sysctl.conf
vm.swappiness = 1                    # 極力避免 swap
vm.zone_reclaim_mode = 0             # 禁用 NUMA 本地分配
kernel.numa_balancing = 0            # 禁用 NUMA 平衡
# 或啟動時：numactl --interleave=all mysqld

5. 內存分配器：jemalloc / tcmalloc

# my.cnf
[mysqld_safe]
malloc-lib = /usr/lib64/libjemalloc.so.2
# 或 tcmalloc
# 驗證：SHOW VARIABLES LIKE 'version_malloc_lib';

減少內存碎片、提升高並發分配效率，Percona/Oracle 官方構建已集成。

6. 存儲：NVMe SSD / 持久化內存

• 隨機 IOPS 決定 MySQL 寫入極限，NVMe 500K+ IOPS 碾壓 SATA SSD
• RAID 卡：帶電緩存 (BBWC/FBWC)，策略 WriteBack
• 數據/日誌/臨時文件分盤：datadir、innodb_log_group_home_dir、tmpdir 分別掛載不同物理設備

7. 文件系統掛載參數

# XFS
/dev/nvme0n1 /data xfs rw,noatime,nodiratime,nobarrier,inode64 0 0

# ext4
/dev/nvme1n1 /logs ext4 rw,noatime,nodiratime,nobarrier,data=ordered 0 0

# SSD 專用
# noatime/nodiratime：關閉訪問時間更新
# nobarrier：RAID 卡有電緩存時安全關閉寫入屏障

MySQL 核心參數層（8-16）

8. IO 調度器：deadline / mq-deadline / none

# 查看
cat /sys/block/nvme0n1/queue/scheduler
# 設置（臨時）
echo mq-deadline > /sys/block/nvme0n1/queue/scheduler
# 永久（grub cmdline）
elevator=mq-deadline

SSD/NVMe 用 none 或 mq-deadline，機械盤用 deadline。

9. RAID 卡緩存策略：WriteBack + 掉電保護

Redo log、Binlog、Data file 寫入均受益。無電緩存嚴禁 WriteBack。

10. 禁用 Query Cache（5.7 默認關，8.0 移除）

query_cache_type = 0
query_cache_size = 0

InnoDB Buffer Pool 已緩存數據頁，QC 只緩存結果集，寫入時需失效檢查，反而拖慢寫入。

11. 啟用 Thread Pool（高並發必備）

# Percona / MariaDB / AliSQL / TencentSQL 內建
thread_pool_size = 16          # 組數，通常 = CPU 核心數
thread_pool_oversubscribe = 3  # 每組活躍線程數
# 連接數 > 200 時顯著降低上下文切換、鎖競爭

12. 連接內存控制：緩存線程、縮小緩衝區

thread_cache_size = 100        # 避免頻繁創建/銷毀線程
# 每連接只讀緩衝區（按需分配，別設太大）
read_buffer_size = 256K
read_rnd_buffer_size = 512K
sort_buffer_size = 512K
join_buffer_size = 256K
binlog_cache_size = 32K
# 總連接內存 = connections * (上述之和) < 10% 物理內存

13. Buffer Pool：核心中的核心

innodb_buffer_pool_size = 物理內存 * 0.7
innodb_buffer_pool_instances = 8  # 1GB 一個實例，減少鎖競爭
innodb_buffer_pool_chunk_size = 128M  # 擴容粒度
# 監控：Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 1%

14. Redo Log：大文件、組提交

innodb_log_file_size = 2G        # 總大小 4G+ (2 files * 2G)
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1  # 最安全，配合組提交性能可接受
# 5.7+ 組提交自動開啟，binlog_group_commit_sync_delay=0 可微調

原則：Redo Log 總大小 > 1 小時寫入量，避免頻繁 checkpoint 刷髒頁。

15. Binlog：同步策略與格式

sync_binlog = 1                  # 每次提交刷盤，最安全
binlog_format = ROW              # ROW 格式：主從一致性最強，支援並行複製
binlog_row_image = MINIMAL       # 只記錄變更列，減小 binlog 體積
binlog_expire_logs_seconds = 604800  # 7 天自動清理

16. 刷盤方式：O_DIRECT 繞過雙重緩存

innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0       # SSD 不需刷鄰近頁
innodb_page_size = 4K            # 4K 頁匹配 SSD 4K 物理塊（需實例級設置）

引擎與架構層（17-19）

17. 僅用 InnoDB

• 事務、行鎖、MVCC、崩潰恢複、在線 DDL、全文索引、Memcached API
• MyISAM 僅用於唯讀、無事務、全表掃描的日誌/統計表

18. 獨立表空間

innodb_file_per_table = 1
-- 支持：TRUNCATE 釋放空間、表空間傳輸 (TTS)、單表備份、碎片整理
-- 5.7+ 通用表空間支持多表共享，按業務劃分

19. 並發控制參數

# 有 Thread Pool 時
innodb_thread_concurrency = 0    # 0 = 無限制，交給 Thread Pool

# 無 Thread Pool 時
# 5.5: 16-32
# 5.6/5.7: 36-64
# 8.0: 0 (自適應) 或 64-128
# 監控：Innodb_row_lock_waits / Innodb_row_lock_time_avg

事務與隔離層（20）

20. 降低隔離級別換性能

transaction_isolation = READ-COMMITTED
binlog_format = ROW              # RC 必須 ROW 格式

RC 級別：無 Gap Lock、鎖更少、並發更高、語句級一致性讀（非事務級），適合高並發業務。
代價：不可重複讀、幻讀（應用層接受或補償）。

運維與監控層（21）

21. 全鏈路監控：無監控即裸奔

核心指標體系：

維度	關鍵指標	告警閾值
可用性	Uptime、連接數、Slave_Running	Down、連接數 > 80% max_connections、Slave_IO/SQL=No
性能	QPS、TPS、慢查詢數、延遲 P99	QPS 突變、慢查詢 > 10/min、P99 > 500ms
存儲	磁盤使用率、IOPS、IO 延遲、Binlog 增長	磁盤 > 80%、IOPS 滿、延遲 > 10ms
內存	Buffer Pool 命中率、內存使用率、Swap 使用	命中率 < 99%、Swap > 0
複製	Seconds_Behind_Master、Relay_Log_Space	延遲 > 30s、Relay Log 暴增
鎖/事務	行鎖等待、死鎖數、活躍事務數、歷史長度	死鎖 > 5/min、歷史長度 > 10萬

工具棧：Prometheus + mysqld_exporter + Grafana（儀表盤模板：Percona MySQL Overview、Orchestrator）
日誌分析：pt-query-digest 慢查詢報告、mysqlbinlog 異常事件審計。

總結：調優路線圖

1. 硬件：NVMe + 大內存 + 多核 + XFS + deadline
2. 系統：limits、swappiness、NUMA 關閉、jemalloc
3. MySQL：Buffer Pool 70%、Redo Log 4G+、ROW Binlog、O_DIRECT、Thread Pool
4. 架構：InnoDB + 獨立表空間 + RC 隔離 + 讀寫分離 + 分庫分表
5. 運維：全指標監控、慢查詢日誌分析、定期演練故障切換

鐵律：硬件是底座，參數是杠桿，架構是上限，監控是保命符。沒有銀彈，只有系統工程。