《RESAR 性能工程实战》第 3 篇：容量场景实战 —— MySQL 索引优化与 sysbench 梯度压测

《RESAR 性能工程实战》第 3 篇：容量场景实战 —— MySQL 索引优化与 sysbench 梯度压测

📚系列目录（全部源码与原始实验日志：GitCode 仓库 https://gitcode.com/cpyaxjq/resar-perf-in-action ）
① 开篇：一小时四台 ECS 搭起完整性能实验场 ② 基准场景：wrk 压测与软中断证据链 ③ 容量场景：MySQL 索引优化与 sysbench 梯度压测 ④ 稳定性与异常：Redis 混沌工程四连击 ⑤ 性能结论：生产配置建议

系列导航：第 1 篇 性能工程总览 · 第 2 篇 基准与环境 · 本篇 容量场景与数据库优化 · 下篇预告见文末
关键词：容量场景 / 最大 TPS / MySQL 索引 / sysbench 梯度压测 / 拐点判断 / 配置审计

一、前言：性能测试要对结果负责

很多团队做性能测试，最终的产出只是一张「TPS 1000、响应时间 200ms」的截图，然后报告里写一句「系统性能良好」。

这远远不够。

在 RESAR 性能工程体系（课程第 23–25 讲）里，我们反复强调一个核心命题：性能测试必须对结果负责。所谓「负责」，至少包含三层含义：

1. 能不能扛住生产？—— 这需要一个「容量场景」来回答，得到系统的最大 TPS，以及它在什么并发下开始「变脸」。
2. 慢的根因是什么？—— 不能只报「慢」，而要给出从现象到证据的完整链路（EXPLAIN、统计、复验）。
3. 上线前该改什么？—— 不能只报 TPS，必须给出可落地的配置/索引建议，否则这份报告对生产没有任何决策价值。

本篇就是一次完整示范：在华为云 8C16G 的 MySQL 8.0 上，我们既做了慢查询的索引优化证据链，又做了sysbench 梯度容量压测，最后还做了一轮配置审计——把「只报数」变成「对结果负责」。

二、容量场景方法论：我们到底在测什么

容量场景（Capacity Test）的本质，是回答一个问题：在可控 SLA（如 P95 < 50ms）约束下，系统到底能扛多少吞吐？

方法论分四步：

1. 铺底数据：造足量的、贴近真实的业务数据（本文 100 万行订单 + 40 万行基准表）。
2. 梯度加压：从低并发到高并发逐步拉满线程数（4 / 16 / 64），观察 TPS、延迟、资源占用。
3. 拐点判断：找到「TPS 增速放缓 + 延迟恶化 + 资源逼近饱和」的临界点——这就是容量上限。
4. 优化闭环：对发现的瓶颈（缺索引、低配参数）做优化并复验，给出生产建议。

容量场景不是「压到挂」的破坏性测试，而是为了定位拐点、量化上限、指导调优。这一点务必和生产「稳定性/破坏性」测试区分开。

三、环境与铺底数据

业务表结构（造数脚本有意省略了user_id上的索引，用来复现「生产常见慢查询」）：

CREATETABLE`t_order`(`id`intNOTNULLAUTO_INCREMENT,`user_id`intDEFAULTNULL,`amount`decimal(10,2)DEFAULTNULL,`status`tinyintDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=1048561DEFAULTCHARSET=utf8mb4;

数据量核实（exp_mysql_probe.log原始回显）：

mysql> SELECT COUNT(*) FROM perfdb.t_order; 1000000 mysql> SELECT COUNT(*) FROM sbtest.sbtest1; 100000

注意：t_order有且仅有一个主键id，user_id上没有二级索引。这条「看似无害」的缺失，正是后面慢查询的根因。

四、索引优化证据链：从「现象」到「复验」

性能工程最有说服力的，不是结论，而是证据链。我们用三步把「慢」讲清楚。

4.1 现象：20 次聚合查询要 2.4 秒

用一个贴近业务的聚合查询（按用户统计订单数与金额），循环 20 次计时：

$time(foriin$(seq120);do\mysql perfdb-N-e"SELECT COUNT(*),SUM(amount) FROM t_order WHERE user_id=$((RANDOM%100000))">/dev/null;\done)real 0m2.432s user 0m0.043s sys 0m0.056s

单次约 120ms，20 次 2.4 秒——对一个「点查 + 聚合」来说，明显偏慢。

4.2 EXPLAIN：根因是百万行全表扫描

exp_mysql_1.log原始回显（EXPLAIN ... \G）：

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_order partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 998412 filtered: 10.00 Extra: Using where

关键证据：

• type: ALL——全表扫描；
• possible_keys: NULL/key: NULL—— 优化器根本没索引可用；
• rows: 998412—— 预计扫描近100 万行（表总共 100 万行，等于全扫）；
• Extra: Using where—— 在扫描完后再逐行过滤。

结论：每次查询都把整张 100 万行的表扫一遍。user_id缺失索引 = 贴着生产最常见的「慢查询」模板。

4.3 优化：在线加索引只用了 3.1 秒

$ mysql perfdb-e"ALTER TABLE t_order ADD INDEX idx_uid(user_id)"---exit=0elapsed=3.1s ---

MySQL 8.0 的在线 DDL（Instant / Inplace 算法）让加索引不锁表、仅 3.1 秒即可完成，业务几乎无感。

4.4 复验：type=ref、rows=14、整体快 28 倍

加完索引后再 EXPLAIN（exp_mysql_2.log）：

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_order partitions: NULL type: ref possible_keys: idx_uid key: idx_uid key_len: 5 ref: const rows: 14 filtered: 100.00 Extra: NULL

• type: ref—— 从全扫升级为索引查找；
• key: idx_uid—— 命中我们刚加的索引；
• rows: 14—— 扫描行数从 998412 降到14（降了 7 万倍）；
• filtered: 100.00/Extra: NULL—— 无需回表后二次过滤。

再跑一遍同样的 20 次计时：

$time(foriin$(seq120);do\mysql perfdb-N-e"SELECT COUNT(*),SUM(amount) FROM t_order WHERE user_id=$((RANDOM%100000))">/dev/null;\done)real 0m0.086s user 0m0.036s sys 0m0.044s

2.432s → 0.086s，加速 28.3 倍（2.432 / 0.086 ≈ 28.3）。

证据链闭环：现象（慢，2.4s）→ EXPLAIN（全表扫，rows≈100 万）→ 优化（加idx_uid，在线 3.1s）→ 复验（ref、rows=14、28×）。这就是性能工程该有的「讲清楚」。

图：t_order（100万行）user_id 索引优化前后——扫描行数从 998,412 降到 14，查询耗时从 2.43 秒降到 0.086 秒，加速 28.3 倍

五、sysbench 梯度容量：完整统计块 + 表格

容量场景的核心动作：梯度加压。我们用oltp_read_write（读写混合），固定 45 秒，线程数取 4 / 16 / 64，每档都同步采集mpstat/vmstat/iostat。

安全说明：以下命令中密码以******脱敏。

5.1 线程=4（低并发）

$ sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=perf\--mysql-password=****** --mysql-db=sbtest\--tables=4--table-size=100000--threads=4--time=45--report-interval=15run

中间采样：

[ 15s ] thds: 4 tps: 549.85 qps: 11001.32 (r/w/o: 7701.42/2199.93/1099.97) lat (ms,95%): 10.27 [ 30s ] thds: 4 tps: 557.40 qps: 11148.42 (r/w/o: 7803.75/2229.87/1114.80) lat (ms,95%): 10.27 [ 45s ] thds: 4 tps: 558.93 qps: 11178.33 (r/w/o: 7825.00/2235.47/1117.87) lat (ms,95%): 10.09

汇总块：

SQL statistics: queries performed: read: 349958 write: 99988 other: 49994 total: 499940 transactions: 24997 (555.39 per sec.) queries: 499940 (11107.77 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 45.0078s total number of events: 24997 Latency (ms): min: 3.52 avg: 7.20 max: 46.78 95th percentile: 10.27 sum: 179984.14

5.2 线程=16（中并发）

汇总块（节选关键行）：

[ 15s ] thds: 16 tps: 1568.48 qps: 31389.78 lat (ms,95%): 13.70 [ 30s ] thds: 16 tps: 1550.33 qps: 30998.69 lat (ms,95%): 13.70 [ 45s ] thds: 16 tps: 1558.40 qps: 31176.21 lat (ms,95%): 13.70 transactions: 70175 (1559.07 per sec.) queries: 1403500 (31181.49 per sec.) ignored errors: 0 (0.00 per sec.) Latency (ms): min: 4.20 avg: 10.26 max: 53.04 95th percentile: 13.70

5.3 线程=64（高并发）

汇总块（节选关键行）：

[ 15s ] thds: 64 tps: 2699.81 qps: 54068.69 lat (ms,95%): 36.24 [ 30s ] thds: 64 tps: 2683.29 qps: 53666.75 lat (ms,95%): 36.89 [ 45s ] thds: 64 tps: 2672.08 qps: 53434.12 lat (ms,95%): 36.24 transactions: 120893 (2683.86 per sec.) queries: 2417860 (53677.30 per sec.) ignored errors: 0 (0.00 per sec.) Latency (ms): min: 5.39 avg: 23.83 max: 86.31 95th percentile: 36.24

5.4 三档汇总表

每线程吞吐：@4 ≈ 139 TPS/线程、@16 ≈ 97、@64 ≈ 42。并发越高，单线程效率越低——这是典型的多线程争用（锁、上下文切换、CPU 调度）信号，而非「线程越多越便宜」。

图：sysbench oltp_read_write 三档梯度——TPS（蓝线）在 4→16 线程近线性增长，16→64 增速放缓；P95 延迟（红虚线）与 CPU 占用率（紫点线）在 64 线程急剧恶化；黄色区域为拐点区间

六、拐点分析：容量上限在哪

把三档数据画成「趋势」来看：

• 4 → 16 线程：TPS 从 555 涨到 1559，+180%，几乎线性；CPU 仅 33% → 62%；P95 从 10.3ms 微升到 13.7ms（+33%）。这一段是「健康的扩容红利区」。
• 16 → 64 线程：TPS 从 1559 涨到 2684，仅 +72%；但 CPU 从 62% 飙升到92%，P95 从 13.7ms 恶化到36.2ms（+2.6 倍），avg 延迟从 10.3ms 翻倍到 23.8ms。

拐点判断三要素在这里同时亮灯：

1. TPS 增速明显放缓（180% → 72%）；
2. P95 延迟急剧恶化（2.6 倍）；
3. CPU 逼近饱和（92%，8 vCPU 已无余量）。

由此判定：真实拐点约在 16–32 线程之间。超过 64 线程，CPU 必然打满、上下文切换与锁竞争加剧，延迟会继续恶化而 TPS 几乎不再增长（甚至回落）。

一个有趣的旁证——iowait 随并发变化：

• 低并发（4 线程）iowait=17.24%，CPU 还有大量空闲（idle 66.7%），此时瓶颈在磁盘 IO，CPU 在等 IO；
• 高并发（64 线程）iowait降到 2.59%，CPU idle 只剩 7.6%——IO 等待被 CPU 并行「掩盖」了，瓶颈从 IO 转移到了CPU 计算/调度。

exp_mysql_3a_mon.log中 4 线程的vmstat实测（wa 列=17，与 mpstat iowait 吻合）：

procs -----------memory---------- ---swap-- -----io---- -system-- -------cpu------- r b swpd free buff cache si so bi bo in cs us sy id wa st gu 0 2 0 12739416 99780 1895632 0 0 0 28834 40255 73646 10 6 66 17 0 0 0 1 0 12736528 99784 1900600 0 0 0 28636 40363 74124 10 6 66 17 0 0

而 64 线程时vmstat的 wa 已降到 3、CPU 跑满：

r b swpd free buff cache si so bi bo in cs us sy id wa st gu 56 2 0 12208584 99796 2189480 0 0 0 42994 37669 160061 59 28 10 3 0 0 48 2 0 12160908 99796 2237196 0 0 0 43294 36532 160778 59 28 9 3 0 0

结论：拐点不是拍脑袋，而是 TPS 曲线 + 延迟曲线 + 资源曲线三者的交叉点。本报告给出的 16–32 线程拐点，是「在 P95 仍可接受（< ~20ms）前提下的最大收益区间」。

七、最大能力：纯点查能跑多快

读写混合之外，我们还单独测了「纯点查」oltp_point_select（线程=64、30 秒），看 MySQL 在最理想读路径下的天花板（exp_mysql_4.log）：

SQL statistics: queries performed: read: 2548405 write: 0 other: 0 total: 2548405 transactions: 2548405 (84882.01 per sec.) queries: 2548405 (84882.01 per sec.) ignored errors: 0 (0.00 per sec.) Latency (ms): min: 0.06 avg: 0.75 max: 19.61 95th percentile: 1.82

• 最大点查 QPS = 84,882（P95 仅 1.82ms，avg 0.75ms）。
• 对比同线程读写混合 QPS 53,677，纯读约为读写混合的 1.58 倍——印证「写（redo/undo/刷盘）才是读写混合场景的主要成本」。

读路径（命中内存、无写开销）的吞吐能力，是评估「缓存命中率提升空间」的重要基线。

八、配置审计与生产建议：对结果负责的关键一步

压完一轮，如果不看配置，等于白压。我们对运行参数做了快照（exp_mysql_5.log）：

Variable_name Value innodb_buffer_pool_size 134217728 innodb_flush_log_at_trx_commit 1 innodb_io_capacity 200 max_connections 151

Variable_name Value Threads_cached 8 Threads_connected 1 Threads_created 127 Threads_running 2

8.1 两个「默认低配陷阱」

证据联动：正是 buffer pool 太小，导致第 4 档（低并发）iowait高达17%——CPU 明明空闲 66%，却在等磁盘把数据读进那可怜的 128MB 缓存。把 buffer pool 调大到 8–10G 后，热点数据常驻内存，iowait 会显著下降，低并发吞吐与高并发拐点都会上移。

8.2 其他参数评价

• innodb_flush_log_at_trx_commit = 1：最安全（每次事务提交都刷盘），生产推荐保留；若对丢数据零容忍又追求更高写吞吐，可在「主从 + 业务可接受」前提下评估改 2，但本文不建议动。
• max_connections = 151：压测中Threads_connected仅 1、running2、created127，连接数远未触顶，当前默认够用，无需调大（盲目调大反而增加内存与上下文切换开销）。
• 线程状态cached=8说明连接池复用正常。

这一步，才是「对结果负责」的真正落点：不只报 TPS，还告诉生产「该改什么、改成多少、为什么」。

九、踩坑速查表（可直接收藏）

十、总结与下篇预告

本篇用一条完整的证据链，示范了「容量场景 + 数据库优化」如何落地：

1. 索引优化：user_id缺索引导致百万行全扫（rows 998412）；加idx_uid仅 3.1s，查询从 2.432s 降到 0.086s，28.3 倍加速。证据链：现象 → EXPLAIN → 优化 → 复验。
2. 容量拐点：读写混合 TPS 在 4/16/64 线程分别为 558 / 1559 / 2684，CPU 33% / 62% / 92%，P95 10.3 / 13.7 / 36.2ms。拐点约 16–32 线程，超过后 TPS 收益骤减、延迟恶化。
3. 最大能力：纯点查 64 线程达84,882 QPS（读写混合的 1.58 倍）。
4. 配置审计：buffer_pool 128MB（仅占 0.8%）、io_capacity 200是默认低配陷阱，建议调到 8–10G / 2000+，并联动低并发 17% iowait 证据。

核心一句话：性能测试要对结果负责——拿到最大 TPS 只是起点，给出「为什么慢、怎么优化、生产怎么配」才是交付。

下篇预告（第 4 篇）：当容量拐点已现，如何下钻到 MySQL 内部瓶颈？我们将用perf/pt-query-digest/ InnoDB 指标做火焰图与慢日志下钻，并结合本篇的 buffer pool 调优做「调优前后对比实验」，验证 8G buffer pool 能否把拐点推到 32 线程以上。

本文实验数据均来自真实环境实操，AI 辅助整理成文。