第六章：MySQL 性能测试

6.1 概述

MySQL 是 Sysbench 最常用的测试目标数据库。本章将深入介绍如何使用 Sysbench 对 MySQL 进行全面的性能测试，包括连接配置、常用测试场景、参数调优验证等。

6.2 MySQL 连接配置

6.2.1 基本连接选项

选项	默认值	说明
`--mysql-host`	127.0.0.1	MySQL 服务器地址
`--mysql-port`	3306	MySQL 端口
`--mysql-user`	root	用户名
`--mysql-password`	(空)	密码
`--mysql-db`	sbtest	测试数据库名
`--mysql-socket`	(无)	Unix Socket 路径
`--mysql-ssl`	off	启用 SSL 连接

# TCP 连接
sysbench oltp_read_write \
  --mysql-host=192.168.1.100 \
  --mysql-port=3306 \
  --mysql-user=sbench \
  --mysql-password=secret \
  --mysql-db=sbtest \
  run

# Unix Socket 连接（本机测试，性能更高）
sysbench oltp_read_write \
  --mysql-socket=/var/run/mysqld/mysqld.sock \
  --mysql-user=root \
  --mysql-password=secret \
  run

6.2.2 创建测试专用用户

-- 创建专用的测试用户（推荐，避免使用 root）
CREATE USER 'sbench'@'%' IDENTIFIED BY 'strong_password_here';

-- 授予权限
GRANT ALL PRIVILEGES ON sbtest.* TO 'sbench'@'%';

-- 如果需要创建数据库的权限
GRANT CREATE ON *.* TO 'sbench'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

6.2.3 创建测试数据库

CREATE DATABASE IF NOT EXISTS sbtest
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

6.2.4 SSL 连接测试

# 启用 SSL
sysbench oltp_read_write \
  --mysql-host=127.0.0.1 \
  --mysql-user=sbench \
  --mysql-password=secret \
  --mysql-ssl=on \
  --tables=16 \
  --table-size=100000 \
  --threads=16 \
  --time=60 \
  run

6.2.5 多主机连接（ProxySQL / MySQL Router）

# 通过 ProxySQL 连接
sysbench oltp_read_write \
  --mysql-host=127.0.0.1 \
  --mysql-port=6033 \
  --mysql-user=proxysql_user \
  --mysql-password=secret \
  --mysql-db=sbtest \
  --tables=16 \
  --table-size=1000000 \
  --threads=32 \
  --time=300 \
  run

6.3 MySQL 版本兼容性

6.3.1 MySQL 5.7 vs 8.0 vs 8.4 特性差异

特性	MySQL 5.7	MySQL 8.0	MySQL 8.4
默认字符集	latin1	utf8mb4	utf8mb4
默认排序规则	latin1_swedish_ci	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
查询缓存	支持（已弃用）	不支持	不支持
Redo Log 配置	innodb_log_file_size	innodb_redo_log_capacity	innodb_redo_log_capacity
窗口函数	不支持	支持	支持
CTE	不支持	支持	支持
Hash Join	不支持	不支持	支持
直方图统计	不支持	支持	支持

6.3.2 MySQL 8.0+ 配置调整

# MySQL 8.0+ 需要注意的配置
mysql -e "SET GLOBAL innodb_redo_log_capacity = 2147483648;"  -- 2GB
mysql -e "SET GLOBAL innodb_buffer_pool_size = 8589934592;"    -- 8GB

# MySQL 8.0 默认使用 caching_sha2_password
# sysbench 需要 mysql_native_password 或客户端支持
mysql -e "ALTER USER 'sbench'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';"

6.4 常用 MySQL 测试场景

6.4.1 场景一：基线性能测试

确定当前 MySQL 实例的基准性能：

#!/bin/bash
# mysql_baseline.sh - MySQL 基线性能测试

HOST="127.0.0.1"
USER="root"
PASS="secret"
TABLES=16
TABLE_SIZE=1000000
DURATION=300
RESULT_DIR="./mysql_baseline_$(date +%Y%m%d_%H%M%S)"
mkdir -p "$RESULT_DIR"

echo "=== MySQL 基线性能测试 ==="
echo "时间: $(date)"
echo ""

# 记录 MySQL 版本和配置
mysql -h $HOST -u $USER -p$PASS -e "SELECT VERSION();" > "$RESULT_DIR/mysql_info.txt"
mysql -h $HOST -u $USER -p$PASS -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';" >> "$RESULT_DIR/mysql_info.txt"
mysql -h $HOST -u $USER -p$PASS -e "SHOW VARIABLES LIKE 'innodb_log%';" >> "$RESULT_DIR/mysql_info.txt"

# 准备数据
echo ">>> Preparing data..."
sysbench oltp_read_write \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  prepare

# 预热
echo ">>> Warming up..."
sysbench oltp_read_write \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  --threads=16 --time=60 \
  run > /dev/null 2>&1

# 读写混合测试
echo ">>> Running oltp_read_write..."
for threads in 1 2 4 8 16 32 64; do
  echo "  Threads: $threads"
  sysbench oltp_read_write \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --tables=$TABLES --table-size=$TABLE_SIZE \
    --threads=$threads --time=$DURATION \
    --percentile=99 --histogram \
    --json="$RESULT_DIR/oltp_rw_t${threads}.json" \
    run 2>&1 | grep -E "(transactions|queries|95th|avg:)" | tee "$RESULT_DIR/oltp_rw_t${threads}.txt"
  sleep 5
done

# 只读测试
echo ">>> Running oltp_read_only..."
for threads in 1 4 8 16 32 64; do
  echo "  Threads: $threads"
  sysbench oltp_read_only \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --tables=$TABLES --table-size=$TABLE_SIZE \
    --threads=$threads --time=$DURATION \
    --json="$RESULT_DIR/oltp_ro_t${threads}.json" \
    run 2>&1 | grep -E "(transactions|queries|95th|avg:)" | tee "$RESULT_DIR/oltp_ro_t${threads}.txt"
  sleep 5
done

# 点查询测试
echo ">>> Running oltp_point_select..."
sysbench oltp_point_select \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  --threads=64 --time=120 \
  --json="$RESULT_DIR/oltp_ps_t64.json" \
  run 2>&1 | grep -E "(transactions|queries|95th)" | tee "$RESULT_DIR/oltp_ps_t64.txt"

# 清理
echo ">>> Cleaning up..."
sysbench oltp_read_write \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES cleanup

echo ""
echo "=== 测试完成 ==="
echo "结果保存在: $RESULT_DIR"

6.4.2 场景二：Buffer Pool 调优

验证不同 innodb_buffer_pool_size 对性能的影响：

#!/bin/bash
# bp_tuning.sh - Buffer Pool 调优测试

HOST="127.0.0.1"
USER="root"
PASS="secret"
TABLES=32
TABLE_SIZE=5000000  # 每张表约 1.2GB，32 张表约 38GB
DURATION=300

# Buffer Pool 大小列表（字节）
BP_SIZES=(
  "1073741824"     # 1GB
  "2147483648"     # 2GB
  "4294967296"     # 4GB
  "8589934592"     # 8GB
  "17179869184"    # 16GB
)

# 准备数据（只做一次）
sysbench oltp_read_write \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  prepare

for bp in "${BP_SIZES[@]}"; do
  bp_mb=$((bp / 1048576))
  echo ""
  echo "==========================================="
  echo "Buffer Pool Size: ${bp_mb}MB"
  echo "==========================================="
  
  # 动态修改 Buffer Pool 大小（MySQL 5.7+ 支持在线调整）
  mysql -h $HOST -u $USER -p$PASS -e "SET GLOBAL innodb_buffer_pool_size = $bp;"
  
  # 等待调整完成
  echo "Waiting for buffer pool resize..."
  sleep 30
  
  # 预热
  sysbench oltp_read_write \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --tables=$TABLES --table-size=$TABLE_SIZE \
    --threads=16 --time=60 \
    run > /dev/null 2>&1
  
  # 正式测试
  sysbench oltp_read_write \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --tables=$TABLES --table-size=$TABLE_SIZE \
    --threads=16 --time=$DURATION \
    --json="bp_${bp_mb}mb.json" \
    run 2>&1 | grep -E "(transactions|95th)"
  
  sleep 5
done

# 清理
sysbench oltp_read_write \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES cleanup

6.4.3 场景三：读写分离测试

分别测试主库和从库的性能：

#!/bin/bash
# rw_split_test.sh - 读写分离性能测试

MASTER_HOST="192.168.1.100"
SLAVE_HOST="192.168.1.101"
USER="sbench"
PASS="secret"
TABLES=16
TABLE_SIZE=1000000

# 准备数据（在主库上）
sysbench oltp_read_write \
  --mysql-host=$MASTER_HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE prepare

# 主库：写测试
echo "=== 主库：写入测试 ==="
sysbench oltp_write_only \
  --mysql-host=$MASTER_HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  --threads=16 --time=300 \
  run 2>&1 | grep -E "(transactions|queries|95th)"

# 从库：读测试
echo "=== 从库：只读测试 ==="
sysbench oltp_read_only \
  --mysql-host=$SLAVE_HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  --threads=32 --time=300 \
  run 2>&1 | grep -E "(transactions|queries|95th)"

# 清理
sysbench oltp_read_write \
  --mysql-host=$MASTER_HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES cleanup

6.4.4 场景四：存储引擎对比

#!/bin/bash
# engine_comparison.sh - 存储引擎对比测试

HOST="127.0.0.1"
USER="root"
PASS="secret"
TABLE_SIZE=1000000
DURATION=300

for engine in innodb myisam tokudb rocksdb; do
  echo ""
  echo "=== Storage Engine: $engine ==="
  
  sysbench oltp_read_write \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --table-engine=$engine \
    --tables=8 --table-size=$TABLE_SIZE \
    prepare
  
  sysbench oltp_read_write \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --table-engine=$engine \
    --tables=8 --table-size=$TABLE_SIZE \
    --threads=16 --time=$DURATION \
    run 2>&1 | grep -E "(transactions|95th)"
  
  sysbench oltp_read_write \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --tables=8 cleanup
  
  sleep 5
done

6.4.5 场景五：连接数上限测试

#!/bin/bash
# max_connections_test.sh - 最大连接数测试

HOST="127.0.0.1"
USER="root"
PASS="secret"
TABLES=16
TABLE_SIZE=1000000

# 准备数据
sysbench oltp_read_write \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE prepare

for threads in 50 100 200 500 1000 2000; do
  echo ""
  echo "=== Threads: $threads ==="
  
  # 调整 MySQL 最大连接数
  mysql -h $HOST -u $USER -p$PASS -e "SET GLOBAL max_connections = $((threads + 100));"
  
  sysbench oltp_read_write \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --tables=$TABLES --table-size=$TABLE_SIZE \
    --threads=$threads --time=120 \
    run 2>&1 | grep -E "(transactions|95th|errors|reconnects)"
  
  sleep 10
done

sysbench oltp_read_write \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=$TABLES cleanup

6.5 MySQL 关键参数测试

6.5.1 innodb_flush_log_at_trx_commit

这是影响 MySQL 写入性能和数据安全性的最关键参数：

值	说明	性能	安全性
0	每秒写入并刷新 Redo Log	最高	最低（可能丢 1 秒数据）
1	每次事务提交都刷新（默认）	最低	最高（ACID 完整）
2	每次事务写入，每秒刷新	中等	中等（OS 崩溃可能丢数据）

#!/bin/bash
# flush_log_test.sh - Redo Log 刷新策略测试

HOST="127.0.0.1"
USER="root"
PASS="secret"

# 准备数据
sysbench oltp_write_only \
  --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
  --tables=16 --table-size=1000000 prepare

for val in 0 1 2; do
  echo "=== innodb_flush_log_at_trx_commit = $val ==="
  mysql -h $HOST -u $USER -p$PASS -e "SET GLOBAL innodb_flush_log_at_trx_commit = $val;"
  sleep 5
  
  sysbench oltp_write_only \
    --mysql-host=$HOST --mysql-user=$USER --mysql-password=$PASS \
    --tables=16 --table-size=1000000 \
    --threads=32 --time=120 \
    run 2>&1 | grep -E "(transactions|95th)"
done

# 恢复默认值
mysql -h $HOST -u $USER -p$PASS -e "SET GLOBAL innodb_flush_log_at_trx_commit = 1;"

6.5.2 sync_binlog

值	说明
0	禁用 Binlog 同步（由操作系统决定）
1	每次事务提交同步 Binlog（最安全）
N	每 N 次事务同步一次

#!/bin/bash
# sync_binlog_test.sh

for val in 0 1 100; do
  echo "=== sync_binlog = $val ==="
  mysql -e "SET GLOBAL sync_binlog = $val;"
  sleep 5
  
  sysbench oltp_write_only \
    --mysql-host=127.0.0.1 --mysql-user=root \
    --tables=16 --table-size=1000000 \
    --threads=32 --time=120 \
    run 2>&1 | grep -E "(transactions|95th)"
done

mysql -e "SET GLOBAL sync_binlog = 1;"

6.5.3 innodb_io_capacity

控制 InnoDB 后台任务（如脏页刷新、Change Buffer 合并）的 I/O 能力：

#!/bin/bash
# io_capacity_test.sh

for val in 200 1000 2000 5000 10000; do
  echo "=== innodb_io_capacity = $val ==="
  mysql -e "SET GLOBAL innodb_io_capacity = $val; SET GLOBAL innodb_io_capacity_max = $((val * 2));"
  sleep 10
  
  sysbench oltp_read_write \
    --mysql-host=127.0.0.1 --mysql-user=root \
    --tables=32 --table-size=5000000 \
    --threads=32 --time=300 \
    run 2>&1 | grep -E "(transactions|95th)"
done

6.5.4 innodb_thread_concurrency

#!/bin/bash
# thread_concurrency_test.sh

# 0 = 不限制（由 InnoDB 自动管理）
for val in 0 8 16 32 64 128; do
  echo "=== innodb_thread_concurrency = $val ==="
  mysql -e "SET GLOBAL innodb_thread_concurrency = $val;"
  sleep 5
  
  sysbench oltp_read_write \
    --mysql-host=127.0.0.1 --mysql-user=root \
    --tables=16 --table-size=1000000 \
    --threads=64 --time=120 \
    run 2>&1 | grep -E "(transactions|95th)"
done

6.6 MySQL 性能监控

6.6.1 运行时监控脚本

在 Sysbench 测试期间监控 MySQL 状态：

#!/bin/bash
# monitor_mysql.sh - 测试期间的 MySQL 监控

INTERVAL=5
DURATION=300
LOG_FILE="mysql_monitor_$(date +%Y%m%d_%H%M%S).log"

echo "time,QPS,TPS,threads_running,threads_connected,buffer_pool_hit_rate,innodb_rows_read,innodb_rows_written" > "$LOG_FILE"

END_TIME=$((SECONDS + DURATION))
while [ $SECONDS -lt $END_TIME ]; do
  STATUS=$(mysql -N -e "
    SELECT 
      VARIABLE_VALUE 
    FROM performance_schema.global_status 
    WHERE VARIABLE_NAME IN ('Queries', 'Com_commit', 'Com_rollback', 
      'Threads_running', 'Threads_connected',
      'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
      'Innodb_rows_read', 'Innodb_rows_written')
    ORDER BY VARIABLE_NAME;
  ")
  
  echo "$(date +%H:%M:%S),$STATUS" >> "$LOG_FILE"
  sleep $INTERVAL
done

6.6.2 查看当前运行状态

-- 查看当前活跃查询
SELECT * FROM information_schema.processlist 
WHERE command != 'Sleep' AND info IS NOT NULL;

-- 查看 InnoDB 状态摘要
SHOW ENGINE INNODB STATUS\G

-- Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

-- 查看当前 QPS
SHOW GLOBAL STATUS LIKE 'Queries';
-- 等待几秒后再次查看，差值/秒 = QPS

6.7 MySQL 8.0+ 特有测试

6.7.1 Hash Join 性能测试

# MySQL 8.0.18+ 支持 Hash Join
# 对比 Nested Loop 与 Hash Join

# 禁用 Hash Join
mysql -e "SET optimizer_switch='hash_join=off';"
sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-user=root \
  --tables=16 --table-size=1000000 --threads=16 --time=120 run | grep -E "(transactions|95th)"

# 启用 Hash Join
mysql -e "SET optimizer_switch='hash_join=on';"
sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-user=root \
  --tables=16 --table-size=1000000 --threads=16 --time=120 run | grep -E "(transactions|95th)"

6.7.2 InnoDB Parallel Read Threads

# MySQL 8.0.14+ 支持并行读
mysql -e "SET GLOBAL innodb_parallel_read_threads = 1;"
sysbench oltp_read_only --threads=16 --time=120 run | grep transactions

mysql -e "SET GLOBAL innodb_parallel_read_threads = 4;"
sysbench oltp_read_only --threads=16 --time=120 run | grep transactions

mysql -e "SET GLOBAL innodb_parallel_read_threads = 16;"
sysbench oltp_read_only --threads=16 --time=120 run | grep transactions

6.8 生产环境注意事项

6.8.1 不要在生产环境直接测试

# ✗ 危险！在生产数据库上运行 Sysbench 可能导致：
#   - 耗尽连接数
#   - CPU/IO 过载
#   - 锁等待超时
#   - 业务受影响

# ✓ 应该在独立的测试环境中测试
# ✓ 如果必须在生产环境，使用极低的线程数和短时间

6.8.2 与生产环境保持一致

# 测试环境应尽量与生产环境一致
# 至少保持以下配置相同：
# - innodb_buffer_pool_size
# - innodb_log_file_size / innodb_redo_log_capacity
# - innodb_flush_log_at_trx_commit
# - sync_binlog
# - max_connections
# - 硬件配置（CPU、内存、存储类型）
# - 网络延迟

6.8.3 数据量匹配

# 生产环境的数据量应该在测试中体现
# 如果生产有 1000 张表、每张 1000 万行
# 测试也应该设置相应的 --tables 和 --table-size

# 但是，可以使用更少的表 + 更大的表来近似
# 生产：1000 表 × 1000万行 = 100 亿行
# 测试：64 表 × 1亿行 = 64 亿行（近似）
sysbench oltp_read_write \
  --tables=64 --table-size=100000000 \
  prepare

6.9 MySQL 测试结果模板

# 生成测试报告

cat > report.md << EOF
# MySQL Sysbench 性能测试报告

## 测试环境
- MySQL 版本: $(mysql -V)
- 操作系统: $(uname -a)
- CPU: $(lscpu | grep "Model name")
- 内存: $(free -h | grep Mem)
- 存储: $(lsblk -d -o NAME,SIZE,ROTA,MODEL)

## 测试配置
- 测试表数: 16
- 每表行数: 1,000,000
- 测试时长: 300 秒
- 测试时间: $(date)

## 测试结果

| 测试项 | 线程数 | TPS | QPS | P95(ms) | P99(ms) |
|--------|--------|-----|-----|---------|---------|
| oltp_read_write | 8 | ... | ... | ... | ... |
| oltp_read_write | 16 | ... | ... | ... | ... |
| oltp_read_write | 32 | ... | ... | ... | ... |
| oltp_read_only | 8 | ... | ... | ... | ... |
| oltp_read_only | 16 | ... | ... | ... | ... |
| oltp_read_only | 32 | ... | ... | ... | ... |

## 结论
...

EOF

6.10 小结

要点	说明
连接方式	TCP 和 Unix Socket，本机测试推荐 Socket
安全性	使用专用测试用户，避免 root
版本注意	MySQL 8.0+ 的 Redo Log 参数名变化
关键参数	innodb_flush_log_at_trx_commit、sync_binlog
测试场景	基线测试、参数调优、读写分离、引擎对比
监控配合	测试期间监控 MySQL 状态变量

强曰为道

第六章：MySQL 性能测试

第六章：MySQL 性能测试

6.1 概述

6.2 MySQL 连接配置

6.2.1 基本连接选项

6.2.2 创建测试专用用户

6.2.3 创建测试数据库

6.2.4 SSL 连接测试

6.2.5 多主机连接（ProxySQL / MySQL Router）

6.3 MySQL 版本兼容性

6.3.1 MySQL 5.7 vs 8.0 vs 8.4 特性差异

6.3.2 MySQL 8.0+ 配置调整

6.4 常用 MySQL 测试场景

6.4.1 场景一：基线性能测试

6.4.2 场景二：Buffer Pool 调优

6.4.3 场景三：读写分离测试

6.4.4 场景四：存储引擎对比

6.4.5 场景五：连接数上限测试

6.5 MySQL 关键参数测试

6.5.1 innodb_flush_log_at_trx_commit

6.5.2 sync_binlog

6.5.3 innodb_io_capacity

6.5.4 innodb_thread_concurrency

6.6 MySQL 性能监控

6.6.1 运行时监控脚本

6.6.2 查看当前运行状态

6.7 MySQL 8.0+ 特有测试

6.7.1 Hash Join 性能测试

6.7.2 InnoDB Parallel Read Threads

6.8 生产环境注意事项

6.8.1 不要在生产环境直接测试

6.8.2 与生产环境保持一致

6.8.3 数据量匹配

6.9 MySQL 测试结果模板

6.10 小结

扩展阅读