MySQL 性能調優全維度指南：架構設計、SQL優化、參數配置、OS調優、硬件規劃

調優五層模型

1. 業務與架構設計：數據模型、分庫分表、讀寫分離、緩存策略
2. SQL 與索引優化：執行計劃分析、索引設計、查詢重寫
3. MySQL 實例參數配置：內存分配、並發控制、日誌刷盤、緩存優化
4. 操作系統與網絡調優：文件系統、內核參數、網卡、中斷親和性
5. 硬件資源升級：CPU、內存、存儲（NVMe SSD）、網絡帶寬

一、存儲引擎與表設計

• 核心業務表強制 InnoDB：事務、行鎖、MVCC、崩潰恢復
• 唯讀/日誌/統計表可用 MyISAM/Archive/ColumnStore
• 大表分區：RANGE/LIST/HASH/KEY，配合分區裁剪
• 字符集統一 utf8mb4，校對 utf8mb4_general_ci
• 主鍵設計：自增整型優於 UUID，避免頁分裂

二、SQL 優化核心：EXPLAIN 實戰

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

關鍵列解讀

索引設計黃金法則

1. 最左前綴原則：組合索引 (a,b,c) 支持 a/a,b/a,b,c
2. 高選擇性列前置：WHERE status=1 AND user_id=100 → 索引 (user_id, status)
3. 覆蓋索引：SELECT id, name FROM t WHERE status=1 → 索引 (status, id, name) 避免回表
4. 避免索引失效：函數操作、隱式轉換、!=/NOT IN/%like、OR 非索引列

三、核心參數配置優化

InnoDB 內存核心

innodb_buffer_pool_size = 物理內存 60-80%  # 核心緩存，數據+索引+鎖+自適應哈希
innodb_buffer_pool_instances = 8  # 多實例減少爭用，建議 1GB/實例
innodb_log_file_size = 512M       # 大事務、減少 checkpoint 壓力
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2  # 1最安全，2性能最好(每秒刷盤)，0最快但可能丟1秒
innodb_flush_method = O_DIRECT    # 繞過 OS cache，避免雙重緩存
innodb_file_per_table = 1         # 獨立表空間，便於管理、回收空間
innodb_io_capacity = 2000         # SSD 可設更高，HDD 200
innodb_read_io_threads = 8
innodb_write_io_threads = 8

連接與並發

max_connections = 2000-5000       # 根據業務峰值，配合 thread_cache_size
thread_cache_size = 300           # 緩存線程，減少創建銷毀開銷
back_log = 300                    # 監聽隊列長度
table_open_cache = 4000           # 表緩存，監控 Open_tables 指標
open_files_limit = 65535          # 系統級限制，需 ulimit 配合

查詢緩存（5.7 建議關閉，8.0 已移除）

query_cache_type = 0
query_cache_size = 0
# 高併發寫入下查詢緩存鎖爭用極大，建議應用層 Redis 替代

臨時表與排序

tmp_table_size = 64M
max_heap_table_size = 64M         # 超過自動轉磁盤 MyISAM 表
sort_buffer_size = 2M             # 每線程分配，慎調大
join_buffer_size = 2M             # 無索引 JOIN 時使用
read_rnd_buffer_size = 4M         # 隨機讀緩存

Binlog 與同步

binlog_format = ROW               # 行級，數據一致性最強
sync_binlog = 1                   # 每次提交刷盤，最安全
expire_logs_days = 7              # 自動清理
gtid_mode = ON
enforce_gtid_consistency = ON

四、慢查詢分析體系

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1               # 秒級，核心業務可設 0.5
log_queries_not_using_indexes = 1 # 記錄無索引查詢
log_slow_admin_statements = 1
log_slow_slave_statements = 1

分析工具鏈

• pt-query-digest：Percona Toolkit 分析慢日誌 TOP SQL
• mysqldumpslow：內置工具簡單聚合
• sys.schema：sys.schema_table_statistics_with_buffer 等視圖
• 監控平台：Prometheus + Grafana + mysqld_exporter

五、操作系統調優

# /etc/sysctl.conf
vm.swappiness = 1                 # 盡量不使用 swap
vm.dirty_ratio = 80
vm.dirty_background_ratio = 5
fs.file-max = 1000000
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_tw_reuse = 1

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

# 文件系統：XFS/ext4，掛載參數 noatime,nodiratime
# RAID：RAID10 最佳，RAID5 寫入罰
# NUMA：numactl --interleave=all mysqld 或 BIOS 關閉 NUMA
# 網卡：多隊列、RSS、RPS/RFS、中斷綁核

六、監控關鍵指標儀表盤

調優避坑指南

1. 不要盲目複製網上配置，必須基於實際負載基線測試
2. 單次調整一個參數，觀測效果，建立基線對比
3. 關注業務指標（QPS、RT、錯誤率）而非單純 MySQL 內部指標
4. 大表 DDL 使用 pt-online-schema-change 或 8.0 瞬間 DDL
5. 定期 ANALYZE TABLE 更新統計信息，防止執行計劃劣化