6.7.9 典型场景调优与压测验证

在典型场景中进行调优时，应以业务特征、数据规模、并发模型为输入，建立可复现实验与对照基线，遵循“先观测—再假设—后验证”的流程。常见场景包括高并发读、写入密集、读写混合、批量导入、复杂排序与分组、热点表与热点行、长事务与报表类查询等。针对每类场景先明确目标指标（QPS/TPS、P95/P99延迟、锁等待、IOPS、CPU占用、Buffer Pool命中率），再选择对应的参数与架构手段进行优化。

典型调优与压测验证闭环如下：

高并发读场景优先优化查询与索引，必要时采用覆盖索引、减少回表、合理分页与分区；参数上关注 innodb_buffer_pool_size、innodb_read_io_threads、table_open_cache、open_files_limit 与并行度设置。示例：

-- 示例：覆盖索引减少回表
EXPLAIN SELECT order_id, status 
FROM t_orders 
WHERE user_id=123 AND status='PAID' 
ORDER BY order_id DESC LIMIT 20;
-- 期望：Extra 中出现 "Using index"

-- 建议索引
ALTER TABLE t_orders 
ADD INDEX idx_user_status_order (user_id, status, order_id);

# /etc/my.cnf.d/tuning.cnf
[mysqld]
innodb_buffer_pool_size=8G
innodb_read_io_threads=8
table_open_cache=4096
open_files_limit=65535

写入密集场景关注 redo/undo、刷盘与自适应刷新策略，结合 innodb_log_file_size、innodb_log_buffer_size、innodb_flush_log_at_trx_commit、sync_binlog 与双1或半同步策略权衡一致性与性能。示例：

# /etc/my.cnf.d/tuning.cnf
[mysqld]
innodb_log_file_size=1G
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=2
sync_binlog=100

读写混合场景需平衡 Buffer Pool 与脏页刷新、控制长事务与锁冲突，结合 innodb_io_capacity、innodb_lru_scan_depth、innodb_flush_neighbors 等参数稳定延迟：

# /etc/my.cnf.d/tuning.cnf
[mysqld]
innodb_io_capacity=1000
innodb_lru_scan_depth=1024
innodb_flush_neighbors=0

批量导入与离线计算场景建议采用分批提交、关闭或延后非关键索引与约束、使用 LOAD DATA 或批量插入，并在业务低峰执行；必要时临时调整 binlog_format、innodb_doublewrite 与并发度，完成后回滚到稳定配置。示例：

-- 导入前：关闭唯一性检查与外键（会话级）
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;

-- 使用 LOAD DATA
LOAD DATA INFILE '/data/orders.csv'
INTO TABLE t_orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(order_id,user_id,amount,status,created_at);

-- 导入后恢复
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;

# /etc/my.cnf.d/import.cnf（导入窗口临时生效）
[mysqld]
binlog_format=ROW
innodb_doublewrite=0

复杂排序与分组场景要减少临时表落盘，关注 tmp_table_size、max_heap_table_size、sort_buffer_size、join_buffer_size，并通过索引与分区降低排序代价。示例：

-- 观察是否使用临时表/文件排序
EXPLAIN SELECT user_id, COUNT(*) 
FROM t_orders 
WHERE status='PAID' 
GROUP BY user_id 
ORDER BY COUNT(*) DESC LIMIT 20;

# /etc/my.cnf.d/sort.cnf
[mysqld]
tmp_table_size=256M
max_heap_table_size=256M
sort_buffer_size=8M
join_buffer_size=8M

热点表与热点行可通过分片、行级缓存、热点拆分、增加冗余与排队限流缓解争用。示例（热点拆分）：

-- 将热点行拆分为多行累加
CREATE TABLE t_counter_shard(
  id INT PRIMARY KEY,
  counter BIGINT NOT NULL
);

-- 读：聚合
SELECT SUM(counter) FROM t_counter_shard;
-- 写：随机更新分片
UPDATE t_counter_shard SET counter=counter+1 WHERE id=FLOOR(RAND()*10)+1;

压测验证需建立可追踪的基线与回归标准，明确数据集规模、并发模型、读写比例、事务长度、SQL分布与稳定时长。压测工具可选 sysbench、mysqlslap、tpcc-mysql 或业务回放；压测过程中收集系统与 MySQL 指标，包括 CPU、内存、IO、网络、InnoDB等待、锁冲突、慢查询分布。结果评估以吞吐与延迟双维度，结合错误率与资源使用率判断收益。

基线准备与压测示例（sysbench）：

# 1) 准备数据
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=pass \
  --mysql-db=sbtest --tables=10 --table-size=1000000 prepare

# 2) 预热（短时运行）
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=pass \
  --mysql-db=sbtest --threads=32 --time=60 --report-interval=10 run

# 3) 稳态压测
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=pass \
  --mysql-db=sbtest --threads=64 --time=300 --report-interval=10 run

# 4) 清理
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=pass \
  --mysql-db=sbtest cleanup

压测过程中观测关键指标（命令与解释）：

# MySQL 线程与锁等待
mysql -uroot -p -e "SHOW PROCESSLIST\G"
mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G"

# 关键性能指标
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';"
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';"

# 计算 Buffer Pool 命中率（期望高）
# 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

压测流程建议为：准备数据与统计信息 → 预热缓存 → 分阶段升压 → 稳态运行 → 退压与冷却 → 结果分析。每次调整仅变更一组参数或一个策略，记录配置、指标与结论，形成调优矩阵与回滚方案。对关键业务建议在预生产环境进行影子压测与容量评估，建立容量模型，形成“并发量—资源消耗—性能指标”的映射关系，确保上线变更可量化、可回退、可复现。

常见排错清单（示例命令）：

# 1) QPS 低但 CPU 不高：检查锁与等待
mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | grep -A5 "LATEST DETECTED DEADLOCK"

# 2) 延迟抖动：检查脏页与刷盘
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';"
mysql -uroot -p -e "SHOW GLOBAL VARIABLES LIKE 'innodb_io_capacity';"

# 3) 排序过多落盘：检查临时表
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';"

练习（可执行）：

使用 sysbench 生成 100 万行数据，记录基线 QPS 与 P95 延迟。
调整 innodb_buffer_pool_size 与 innodb_io_capacity 进行二次压测，比较命中率与延迟变化。
在排序场景中增加合适索引，观察 EXPLAIN 的变化与临时表数量是否下降。