第4篇：59 倍性能差距的索引瓶颈定位——一次教科书级的全表扫描调优

第4篇59 倍性能差距的索引瓶颈定位——一次教科书级的全表扫描调优系列《RESAR 性能工程实战——从 0 到 40000 TPS 的调优全记录》环境4 台华为云 FlexusX8C16GUbuntu 24.04内网千兆JMeter 5.6.3 Spring Boot 3.2.5 mall-demo MySQL 8 Redis 7 Prometheus/Grafana数据来源results/test_data_summary.md本篇所有数字均为真实压测结果引言上一篇我们测了首页最大 51,000 TPS和登录10 线程 10,645 TPS一切健康。但基准场景最迷人的地方就是它会把藏得很深的单点瓶颈一把揪出来。同一轮压测、同样 10 线程登录 10,645 TPS商品查询只有 180.5 TPS——差了整整 59 倍平均 RT 高达 53.3ms。一个接口慢了 59 倍生产上就是首页秒开、点商品转圈的真实用户体验灾难。这一篇我们严格走 RESAR 七步分析法把这次全表扫描瓶颈的定位与调优拆成可以照抄的标准动作。它也是整个系列里最值得你收藏的一篇——因为加个索引谁都会说但怎么用证据链把根因钉死、又怎么用回归验证收口才是性能工程师和拍脑袋侠的分水岭。读完本篇你能收获一个59 倍差距的真实现象以及它背后同线程不同命的本质RESAR七步分析法完整套用从场景数据 → 架构分析 → 全局监控 → 定向监控 → EXPLAIN → 证据链 → 调优回归SHOW FULL PROCESSLIST与EXPLAIN的真实输出怎么读哪里是铁证深度原理为什么全表扫描烧的是 us CPU 而不是 io wait数量级估算10 万行 × 180 TPS 每秒 1800 万行扫描性能分析决策树中db us CPU 高分支的完整展开三条可落地的生产 SQL 治理建议。一、现象同 10 线程59 倍的鸿沟先把刺眼的数字摆出来10 线程基准每组 60s接口TPSavg RTP99app idledb us CPU登录/mall/login10,6450.9ms2ms74%低商品查询/mall/product180.553.3ms85ms98.8%80.8%TPS 差距10,645 ÷ 180.5 ≈ 59 倍RT 差距53.3ms vs 0.9ms慢了~59 倍恰好同比例说明瓶颈是每条请求固定很贵最关键的指向性信号商品查询的应用机 idle 高达 98.8%应用几乎闲着而数据库 db us CPU 80.8%DB 在猛烧 CPU一个接口慢 59 倍但慢不在应用、在数据库——而且数据库不是 IO 忙是CPU 忙。这就把我们的分析方向一把锁死在DB 的 CPU 型瓶颈上。二、七步分析法一步步走RESAR 的性能分析有固定套路不靠灵感。我们严格走七步。第 1 步压力场景数据锁定表象先精确描述病状不带任何猜测TPS 180.5远低于同线程其他接口avg RT 53.3msP99 85ms错误率 0不是报错是纯慢划重点0 错误 高 RT 低 TPS这是典型的单请求重特征而不是连接断了/超时了。方向立刻偏向单条 SQL 太贵。第 2 步架构分析画出请求链路商品查询的请求链路JMeter → Tomcat(200线程) → HikariCP(10) → MySQL 8 └─ Redis 7此时缓存开关关闭关键事实本轮 Redis 缓存开关是关闭的也就是说商品查询每次都打到 MySQL没有缓存兜底。链路简化成应用把 SQL 发给 DBDB 自己算。这让我们能把注意力完全集中在MySQL 怎么执行这条 SQL上。这也是基准场景的纪律故意关掉缓存、隔离变量才能看清 DB 自身的真实能力。否则缓存命中会掩盖SQL 很烂的事实。第 3 步全局监控方向锁定 DB看两台机器的整机资源来自 Prometheus/node_exporter 抓取应用机 appidle 98.8%→ 应用几乎全闲CPU、内存都不是瓶颈数据库机 dbus 80.8%idle 18.5%→ DB 的用户态 CPU 被吃满。决策推论瓶颈不在应用在数据库且是 CPU 型us 高而非 IO 型wa 高瓶颈。这一步的价值很多人一看到 RT 高就猛加应用机器、猛调 JVM结果白忙。先看整机 idle半分钟就能排除应用侧——我们这一步直接省掉了 80% 的无效调优。第 4 步定向监控SHOW FULL PROCESSLIST方向锁定 DB 后进数据库看现在在干嘛。真实执行的命令与典型输出SHOWFULLPROCESSLIST;观察到的真实现象大量连接处于Sleep状态真正Query状态的连接很少且每条Query执行时间极短毫秒级就释放。这传递了两个关键信息连接多为 Sleep→ 说明没有长事务卡死“锁等待堆积”连接是快进快出的每条 Query 都短但 DB CPU 却满→ 说明单条 SQL 本身看起来快、但代价极高它每条都执行得很快结束但每条都把 DB CPU 烧掉一大块。直觉陷阱有人看到Sleep多就以为是连接池问题。错。这里 Sleep 多恰恰是请求频繁、每条都秒回的表现真正的异常是秒回的 SQL 为什么能把 CPU 烧到 80%。答案只能去 SQL 执行计划里找。第 5 步EXPLAIN——全表扫描铁证对商品查询的 SQLWHERE sku_code ?执行真实 EXPLAINEXPLAINSELECT*FROMt_productWHEREsku_codexxx;真实输出要点type ALL # 全表扫描 possible_keys NULL # 没有任何可用索引 key NULL rows 99683 # 预计扫描约 10 万行 Extra Using where四个字定性type ALL possible_keys NULL这就是全表扫描的铁证。MySQL 在t_product10 万行上找不到sku_code的任何索引只能逐行比对——每查一次商品就把整张 10 万行表扫一遍。rows 99683直白地告诉我们这条看似简单的点查实际成本是读 10 万行。对比登录接口走typeref索引成本是常数级一两行。这就是 59 倍差距的执行层根因。第 6 步证据链总结把根因钉死现在把前面所有证据串成一条不可反驳的链TPS 低(180) RT 高(53ms) └─ 0 错误 → 不是报错是单请求重 └─ app idle 98.8% → 应用不背锅 └─ db us CPU 80.8% → DB CPU 型瓶颈 └─ PROCESSLIST 多为 Sleep → 单条 SQL 快进快出但每条都贵 └─ EXPLAIN typeALL, rows99683, possible_keysNULL └─ 根因sku_code 无索引每次全表扫描 10 万行每一步都有数据支撑没有一处我感觉。这就是性能分析该有的样子。第 7 步调优动作与效果回归必须验证不可拍脑袋调优动作简单到只有一行ALTERTABLEt_productADDINDEXidx_sku(sku_code);但关键在回归验证。加完索引后先 EXPLAIN 确认计划真的变了EXPLAINSELECT*FROMt_productWHEREsku_codexxx;-- type ref-- key idx_sku-- rows 1-- Extra Using index # 覆盖索引连回表都不用rows从 99683 降到1type从 ALL 变ref且Using index表示索引覆盖。计划层确认无误后再上 JMeter 同条件复测仍是 10 线程、60s指标调优前调优后变化TPS180.510,66059 倍avg RT53.3ms0.9ms-98%P9985ms2ms-98%db us CPU80.8%10%-71ptTPS 从 180.5 拉到10,66059 倍提升与差距倍数精确吻合db us CPU 从 80.8% 掉到10%。证据链闭环加索引 → 执行计划变 → TPS 回升 → DB CPU 回落根因确凿、疗效确切。反例警示很多团队加完索引就以为完了不去复测。结果索引建在错列、或 SQL 写法导致索引失效隐式类型转换、函数包裹列TPS 纹丝不动还以为索引没用。调优动作必须回归验证这是 RESAR 的硬性纪律。三、深度延伸为什么全表扫描烧的是 us CPU不是 io wait这里有个反直觉点值得展开。按直觉扫 10 万行不是应该读磁盘、io wait 高吗但我们的监控里 db 是us 80.8%、wa 很低。原因3.1 buffer pool 已缓存整张表MySQL 的innodb_buffer_pool_size 4G而t_product仅 10 万行几十 MB 量级。压测一跑整张表立刻被装进buffer pool内存。后续的全表扫描根本不碰磁盘而是在内存里逐行比对sku_code。内存里的逐行字符串比较是纯 CPU 计算→ 全部计入us用户态 CPU。所以你看到的是 us 高、wa 低。重要推论如果你的全表扫描没被缓存表很大、buffer pool 装不下那就会表现为io wait 高。本项目因为表小buffer pool 大才呈现CPU 型全表扫描。两种表象根因都是扫了不该扫的行数但监控切入点不同——这正是分析时要结合表大小与 buffer pool 一起看的原因。3.2 数量级估算每秒 1800 万行扫描我们来算一笔账感受无索引的代价单条查询扫描行数 ≈ 100,000 行全表 查询 QPS ≈ 180 TPS单接口 每秒扫描总行数 100,000 × 180 ≈ 18,000,000 行/秒每秒 1800 万行在内存里被逐行比较且不干别的——这就是 db us CPU 被烧到 80% 的真相。如果并发再高点、或表涨到千万级这数字会直接把 DB 打挂。这个估算方法很有用当你看到DB CPU 高 某接口慢用全表行数 × QPS粗算扫描量能快速判断是不是在空转扫大表。四、方法论升华性能分析决策树的db us CPU 高分支把这次经验固化成决策树的一个分支未来遇到db us CPU 高直接照走DB 整机 CPU 高us 高、wa 低 ├─ 是否有慢查询 │ ├─ SHOW FULL PROCESSLIST 看当前 SQL │ └─ 开启 slow_query_log抓执行慢的语句 ├─ 对慢 SQL 执行 EXPLAIN │ ├─ type ALL / index → 全表/全索引扫描 │ │ ├─ possible_keys NULL → 缺索引 → ADD INDEX回归验证 │ │ └─ 有索引但没用 → SQL 写法导致失效函数包裹/隐式转换/前导模糊 │ ├─ type ref/range 但 rows 大 → 选择性差 / 数据量治理 │ └─ Extra 有 Using filesort / Using temporary → 排序/分组未走索引 ├─ 非单 SQL 问题 → SQL 改写拆查询/批处理/避免 SELECT * └─ 数据量本身大 → 分库分表 / 归档冷数据 / 读写分离本案例命中第一分支typeALL possible_keysNULL → 缺索引 → ADD INDEX → 回归验证。为什么加索引必须回归而不是拍脑袋前面强调过了这里再点三个真实会翻车的场景建错列以为sku_code有索引实际索引建在id上——EXPLAIN 一验证就露馅索引失效SQL 写成WHERE DATE(create_time)?或WHERE sku_code 123字符串列传数字隐式转换——有索引也用不上选错类型建了普通索引但查询是范围排序仍filesort——需要联合索引或调整顺序。不回归你永远不知道自己是真调好了还是以为调好了。五、生产建议把全表扫描挡在上线前这次 59 倍差距根因是上线前没审查 SQL 索引。为此给出三条可落地的生产治理建议5.1 建立 SQL 上线索引审查机制所有上线 SQL尤其是WHERE/JOIN/ORDER BY涉及的列必须附EXPLAIN结果硬性红线EXPLAIN type ALL或type index无 WHERE 限定的全索引扫描禁止上线把这条写进 CR/MR 的提审模板由 DBA 或性能负责人把关。5.2 EXPLAIN typeALL 禁止上线的工程化可以把审查做成自动化在测试环境用影子流量或回放 SQL批量EXPLAIN凡出现typeALL且rows超过阈值如 1 万的CI 直接标红阻断。本项目正是因为没有这层拦截t_product的sku_code才裸奔上线。5.3 慢查询日志常态化监控# my.cnf slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 0.5 # 超过 0.5s 即记录 log_queries_not_using_indexes 1 # 未走索引的也记配合 Prometheus Grafana 对Slow_queries计数器做告警让无索引扫描在上线后也能被持续发现而不是等压测或故障才暴露。一句话索引问题最好的解决时点是写代码时和上线前最差的解决时点是生产宕机时。本案例用 59 倍差距提醒我们这两者之间可能只差一道 SQL 审查。六、小结同 10 线程登录 10,645 TPS vs 商品查询 180.5 TPS 59 倍差距根因不在应用app idle 98.8%而在 DBdb us 80.8%七步分析法闭环场景数据 → 架构缓存关 → 全局监控锁 DB → PROCESSLIST 多 Sleep →EXPLAIN typeALL/rows99683/possible_keysNULL→ 证据链 → 加索引回归调优后 EXPLAINtyperef/rows1/Using index复测TPS 10,66059 倍、RT 0.9ms、db us 81%→10%原理深坑表小 buffer pool 大 → 全表扫描在内存完成 → 烧的是us CPU而非 io wait数量级10万行 × 180 TPS ≈ 每秒 1800 万行扫描决策树固化db us 高分支加索引必须回归验证防建错列/索引失效三条生产建议SQL 上线索引审查、typeALL禁止上线工程化、慢查询日志常态化监控。下篇预告首页、登录、商品查询的单接口最大 TPS已全部标定。下一步进入 RESAR 的容量场景把它们按生产比例混合首页 37.5% / 商品 25% / 登录 10% …看系统整体能扛多少、拐点又在哪里。下一篇《容量场景实战——40000 TPS 的混合容量天花板》我们见系统级的真章。数据来源RESAR 实战项目results/test_data_summary.md。所有 TPS/RT/CPU 数字、EXPLAIN与PROCESSLIST结论均为真实压测与诊断结果未经编造。