title: 加完索引 QPS 反而掉了 18%执行计划里被我们看漏的 rows、filtered 和回表tags: MySQL,慢查询优化,索引,执行计划,Javacategory: 后端一次优化把接口做慢了先说结论那次上线之后订单列表接口的 P99 从 420ms 涨到了 510msQPS 从 1100 掉到 900。而我们那天做的唯一改动就是给一张 2400 万行的订单表加了一个索引。背景是这样。订单列表的查询长这样SELECT id, order_no, user_id, shop_id, status, total_amount, created_at FROM t_order WHERE shop_id 88123 AND status IN (1, 2) AND created_at 2026-01-01 00:00:00 ORDER BY created_at DESC LIMIT 20 OFFSET 0;原来表上只有idx_shop_created(shop_id, created_at)。有人在慢查询日志里看到这条 SQL 偶尔跑到 1.2 秒于是加了一个更全的索引ALTER TABLE t_order ADD INDEX idx_shop_status_created (shop_id, status, created_at);想法很朴素三个 WHERE 条件都进索引肯定更快。上线后大部分店铺确实快了但头部大店铺反而变慢了整体 QPS 掉了 18%。EXPLAIN 里那三列到底在说什么我们把两个索引的执行计划都拉出来对比。MySQL 8.0.32头部店铺 shop_id88123 有 41 万条订单。用旧索引FORCE INDEX(idx_shop_created)id select_type table type key possible_keys rows filtered Extra 1 SIMPLE t_order range idx_shop_created idx_shop_created 38214 33.33 Using index condition; Using where用新索引id select_type table type key key_len rows filtered Extra 1 SIMPLE t_order range idx_shop_status_created 14 76428 100.00 Using index condition; Using where; Using filesort关键在最后一列的Using filesort。原因是索引列顺序。idx_shop_status_created(shop_id, status, created_at)在status IN (1,2)这种范围条件之后created_at就不能再保持有序了——索引里的顺序是先按 status 分组组内按 created_at 排而查询要的是跨 status 的全局时间倒序。MySQL 只能把 status1 和 status2 两段各取出来再做一次排序。而旧索引(shop_id, created_at)虽然要多扫一些不匹配 status 的行但created_at天然有序ORDER BY created_at DESC LIMIT 20可以扫到 20 条就停。rowsMySQL 估算要扫描的行数。新索引 76428 是旧索引的两倍这里就已经能看出苗头了。filtered估算扫描后剩下的百分比。旧索引 33.33% 是因为 status 条件没进索引要回表过滤新索引 100% 说明索引已经过滤干净。ExtraUsing filesort意味着有额外排序这是这次翻车的直接原因。很多人只看type和key觉得走了索引就万事大吉。真正决定成败的往往是Extra那一列。这是我从这次事故里学到的最实在的一条。覆盖索引的真实收益修正方案不是回滚而是把索引改成覆盖索引ALTER TABLE t_order DROP INDEX idx_shop_status_created; ALTER TABLE t_order ADD INDEX idx_shop_created_cover (shop_id, created_at, status, total_amount);执行计划变成id select_type table type key key_len rows filtered Extra 1 SIMPLE t_order range idx_shop_created_cover 14 412 100.00 Using where; Using indexUsing index出现了意味着不需要回表。rows从 76428 降到 412。为什么能降这么多因为LIMIT 20的下推。索引有序时MySQL 沿着索引倒序扫每扫一条判断 status 是否在 (1,2) 里——这个判断直接在索引页上完成不需要回表读聚簇索引。扫到第 20 条满足条件的就停。412 是估算值实际扫描量取决于 status 的分布。但id, order_no, user_id, shop_id, status, total_amount, created_at这些字段里order_no和user_id不在索引里为什么还能Using index答案是并不能。上面那个执行计划是我把 SELECT 列表裁剪成id, created_at, status, total_amount之后跑出来的。完整的 SELECT 列表下仍然要回表。这就引出覆盖索引的现实约束要覆盖就得把所有 SELECT 的列都塞进索引索引会迅速膨胀。我们算过一笔账索引列单行索引大小估2400 万行总大小idx_shop_createdshop_id, created_at约 22 字节约 620 MBidx_shop_created_cover status, total_amount约 33 字节约 930 MB全覆盖含 order_no, user_id varchar(32), bigint约 78 字节约 2.1 GB全覆盖的索引比原索引大三倍多写入时每次 INSERT/UPDATE 都要维护订单表的写入 TPS 会明显下降。我们最后选了中间方案列表页只查覆盖索引里有的字段order_no这类展示字段通过第二次按主键批量查补齐。Java 侧的实现public PageResultOrderVO pageOrders(OrderQuery query) { // 第一步走覆盖索引只捞主键和排序字段不回表 ListOrderBrief briefs orderMapper.selectBriefByCover( query.getShopId(), query.getStatusList(), query.getStartTime(), query.getOffset(), query.getSize()); if (briefs.isEmpty()) { return PageResult.empty(); } // 第二步按主键批量回捞完整行聚簇索引点查每条约 0.05ms ListLong ids briefs.stream().map(OrderBrief::getId).collect(Collectors.toList()); ListOrder orders orderMapper.selectBatchByIds(ids); // 第三步按第一步的顺序重排不能依赖 IN 查询的返回顺序 MapLong, Order map orders.stream() .collect(Collectors.toMap(Order::getId, Function.identity())); ListOrderVO vos briefs.stream() .map(b - OrderVO.of(map.get(b.getId()))) .collect(Collectors.toList()); return PageResult.of(vos, query); }逐段说明第 3-5 行的selectBriefByCover只 SELECTid, created_at完全走覆盖索引Extra是Using index。第 11 行按主键 IN 批量查20 个主键在聚簇索引上是 20 次点查B 树高度 3 层全在 Buffer Pool 里实测总耗时 1ms 出头。第 13-18 行必须重排。WHERE id IN (...)的返回顺序由 MySQL 决定通常是主键升序和我们要的时间倒序不一致。这个坑我们线上出过一次列表页顺序随机跳被用户投诉了。对应的 MapperSelect(script SELECT id, created_at FROM t_order WHERE shop_id #{shopId} AND created_at gt; #{startTime} AND status IN foreach items collectionstatusList open( separator, close)#{s}/foreach ORDER BY created_at DESC LIMIT #{offset}, #{size} /script) ListOrderBrief selectBriefByCover(Param(shopId) Long shopId, Param(statusList) ListInteger statusList, Param(startTime) LocalDateTime startTime, Param(offset) int offset, Param(size) int size);这里有个细节status IN必须写在created_at范围条件之后吗在 SQL 文本里顺序无所谓优化器会自己重排。真正决定索引使用方式的是索引里列的顺序不是 WHERE 子句里条件的书写顺序。这一点很多人搞混。深分页OFFSET 100000 的代价同一个接口还有一个隐藏问题。运营后台支持跳页有人点到第 5000 页... ORDER BY created_at DESC LIMIT 20 OFFSET 100000;这条 SQL 在 2400 万行的表上要 3.8 秒。原因是 MySQL 必须先扫描并丢弃前 100000 行再返回 20 行。OFFSET 越大越慢和索引好不好没关系。我们用游标分页改掉了大部分场景public CursorPageOrderVO scrollOrders(Long shopId, String cursor, int size) { LocalDateTime lastTime null; Long lastId null; if (StringUtils.hasText(cursor)) { String[] parts CursorCodec.decode(cursor).split(_); // 时间戳_主键 lastTime LocalDateTime.ofEpochSecond(Long.parseLong(parts[0]), 0, ZoneOffset.ofHours(8)); lastId Long.parseLong(parts[1]); } // SQL: WHERE shop_id? AND (created_at ? OR (created_at ? AND id ?)) // ORDER BY created_at DESC, id DESC LIMIT ? ListOrderBrief briefs orderMapper.scrollByCursor(shopId, lastTime, lastId, size 1); boolean hasNext briefs.size() size; if (hasNext) { briefs briefs.subList(0, size); } String nextCursor hasNext ? CursorCodec.encode(last(briefs).getCreatedAt().toEpochSecond(ZoneOffset.ofHours(8)) _ last(briefs).getId()) : null; return CursorPage.of(fill(briefs), nextCursor, hasNext); }几个关键点第 9-10 行的复合条件(created_at ? OR (created_at ? AND id ?))是为了处理同一秒内多条订单的情况。只用created_at 会漏数据只用id 在时间倒序下不成立。第 11 行查size 1条用多出来的那一条判断有没有下一页省掉一次COUNT(*)。这张表上的COUNT(*)要 6 秒以上能不查就不查。第 18-20 行把游标编码成不透明字符串返回给前端避免前端自己拼时间和主键。游标分页的限制很明确不能跳页。运营那边确实需要跳页我们的折中是跳页只允许在最近 7 天的范围内7 天的数据量在单店铺下通常几千条OFFSET 再大也不痛。超过 7 天强制用导出功能走离线任务。怎么先发现慢查询我们没有等 MySQL 慢日志而是在应用侧加了一层拦截因为慢日志的long_query_time设成 1 秒很多 300-800ms 的温水查询根本不进日志但它们的总量才是压垮连接池的元凶。Intercepts({Signature(type Executor.class, method query, args {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})}) public class SlowSqlInterceptor implements Interceptor { private static final long THRESHOLD_MS 200; Override public Object intercept(Invocation invocation) throws Throwable { MappedStatement ms (MappedStatement) invocation.getArgs()[0]; long start System.nanoTime(); try { return invocation.proceed(); } finally { long costMs (System.nanoTime() - start) / 1_000_000; if (costMs THRESHOLD_MS) { // statementId 形如 com.xx.OrderMapper.selectBriefByCover Metrics.timer(mybatis.slow.query, stmt, ms.getId()) .record(costMs, TimeUnit.MILLISECONDS); log.warn(slow sql cost{}ms stmt{} traceId{}, costMs, ms.getId(), MDC.get(traceId)); } } } }第 5 行阈值定 200ms比 MySQL 慢日志的 1 秒严格 5 倍。第 16-18 行同时上报指标这样 Grafana 上能按stmt维度看哪个 Mapper 方法慢的次数最多而不是只有一堆散落的日志。第 19 行带上 traceId能直接跳到链路里看这条 SQL 在整个请求中的占比。上线一周这个拦截器捞出来 17 个从没进过慢日志的语句其中 3 个是每次请求都跑、单次 250ms 左右的隐形杀手。复盘数据完整改造覆盖索引 两段式查询 游标分页之后指标改造前错误索引版本最终版本列表接口 P99420ms510ms86ms列表接口 QPS11009002600深分页第 5000 页3.8s3.9s不支持跳页/游标 40mst_order 索引总大小1.9 GB2.6 GB2.2 GB订单写入 TPS320028503050写入 TPS 从 3200 降到 3050这是多一个索引列的固定代价大约 4.7%。读性能提升 2.3 倍我们认为这个交换是划算的——这张表读写比大约是 30:1。我的几条判断不要为了让 WHERE 里每个字段都进索引而建索引。索引的价值排序应该是先满足等值过滤和排序再考虑覆盖。等值列放前面、范围列放后面、排序列紧跟等值列这个顺序比字段全不全重要得多。加索引前一定要在生产数据量级上跑 EXPLAIN而且要挑最极端的参数。我们那次翻车就是因为在开发库3 万行上测怎么测都快。头部店铺 41 万条订单的分布在小数据集上完全体现不出来。现在我们的规范是任何索引变更必须提供在生产影子库上对 top10 大客户参数的 EXPLAIN 结果。我不建议给低区分度的列单独建索引。比如status只有 5 个值2400 万行里 status1 占 60%单独给它建索引优化器大概率不会用反而白白增加写入成本。它只适合作为组合索引的后置列。最后抛个问题我们现在的两段式查询先覆盖索引取主键再按主键回捞本质上把一次查询拆成了两次网络往返。在跨机房部署、RTT 有 3-5ms 的场景下这个拆分还划算吗我倾向于认为在 RTT 超过 5ms 时应该退回单次查询 更宽的覆盖索引但没有实测数据支撑。你们有跨机房的实践经验吗评论区聊聊。 加完索引 QPS 反而掉了 18%：执行计划里被我们看漏的 rows、filtered 和回表
title: 加完索引 QPS 反而掉了 18%执行计划里被我们看漏的 rows、filtered 和回表tags: MySQL,慢查询优化,索引,执行计划,Javacategory: 后端一次优化把接口做慢了先说结论那次上线之后订单列表接口的 P99 从 420ms 涨到了 510msQPS 从 1100 掉到 900。而我们那天做的唯一改动就是给一张 2400 万行的订单表加了一个索引。背景是这样。订单列表的查询长这样SELECT id, order_no, user_id, shop_id, status, total_amount, created_at FROM t_order WHERE shop_id 88123 AND status IN (1, 2) AND created_at 2026-01-01 00:00:00 ORDER BY created_at DESC LIMIT 20 OFFSET 0;原来表上只有idx_shop_created(shop_id, created_at)。有人在慢查询日志里看到这条 SQL 偶尔跑到 1.2 秒于是加了一个更全的索引ALTER TABLE t_order ADD INDEX idx_shop_status_created (shop_id, status, created_at);想法很朴素三个 WHERE 条件都进索引肯定更快。上线后大部分店铺确实快了但头部大店铺反而变慢了整体 QPS 掉了 18%。EXPLAIN 里那三列到底在说什么我们把两个索引的执行计划都拉出来对比。MySQL 8.0.32头部店铺 shop_id88123 有 41 万条订单。用旧索引FORCE INDEX(idx_shop_created)id select_type table type key possible_keys rows filtered Extra 1 SIMPLE t_order range idx_shop_created idx_shop_created 38214 33.33 Using index condition; Using where用新索引id select_type table type key key_len rows filtered Extra 1 SIMPLE t_order range idx_shop_status_created 14 76428 100.00 Using index condition; Using where; Using filesort关键在最后一列的Using filesort。原因是索引列顺序。idx_shop_status_created(shop_id, status, created_at)在status IN (1,2)这种范围条件之后created_at就不能再保持有序了——索引里的顺序是先按 status 分组组内按 created_at 排而查询要的是跨 status 的全局时间倒序。MySQL 只能把 status1 和 status2 两段各取出来再做一次排序。而旧索引(shop_id, created_at)虽然要多扫一些不匹配 status 的行但created_at天然有序ORDER BY created_at DESC LIMIT 20可以扫到 20 条就停。rowsMySQL 估算要扫描的行数。新索引 76428 是旧索引的两倍这里就已经能看出苗头了。filtered估算扫描后剩下的百分比。旧索引 33.33% 是因为 status 条件没进索引要回表过滤新索引 100% 说明索引已经过滤干净。ExtraUsing filesort意味着有额外排序这是这次翻车的直接原因。很多人只看type和key觉得走了索引就万事大吉。真正决定成败的往往是Extra那一列。这是我从这次事故里学到的最实在的一条。覆盖索引的真实收益修正方案不是回滚而是把索引改成覆盖索引ALTER TABLE t_order DROP INDEX idx_shop_status_created; ALTER TABLE t_order ADD INDEX idx_shop_created_cover (shop_id, created_at, status, total_amount);执行计划变成id select_type table type key key_len rows filtered Extra 1 SIMPLE t_order range idx_shop_created_cover 14 412 100.00 Using where; Using indexUsing index出现了意味着不需要回表。rows从 76428 降到 412。为什么能降这么多因为LIMIT 20的下推。索引有序时MySQL 沿着索引倒序扫每扫一条判断 status 是否在 (1,2) 里——这个判断直接在索引页上完成不需要回表读聚簇索引。扫到第 20 条满足条件的就停。412 是估算值实际扫描量取决于 status 的分布。但id, order_no, user_id, shop_id, status, total_amount, created_at这些字段里order_no和user_id不在索引里为什么还能Using index答案是并不能。上面那个执行计划是我把 SELECT 列表裁剪成id, created_at, status, total_amount之后跑出来的。完整的 SELECT 列表下仍然要回表。这就引出覆盖索引的现实约束要覆盖就得把所有 SELECT 的列都塞进索引索引会迅速膨胀。我们算过一笔账索引列单行索引大小估2400 万行总大小idx_shop_createdshop_id, created_at约 22 字节约 620 MBidx_shop_created_cover status, total_amount约 33 字节约 930 MB全覆盖含 order_no, user_id varchar(32), bigint约 78 字节约 2.1 GB全覆盖的索引比原索引大三倍多写入时每次 INSERT/UPDATE 都要维护订单表的写入 TPS 会明显下降。我们最后选了中间方案列表页只查覆盖索引里有的字段order_no这类展示字段通过第二次按主键批量查补齐。Java 侧的实现public PageResultOrderVO pageOrders(OrderQuery query) { // 第一步走覆盖索引只捞主键和排序字段不回表 ListOrderBrief briefs orderMapper.selectBriefByCover( query.getShopId(), query.getStatusList(), query.getStartTime(), query.getOffset(), query.getSize()); if (briefs.isEmpty()) { return PageResult.empty(); } // 第二步按主键批量回捞完整行聚簇索引点查每条约 0.05ms ListLong ids briefs.stream().map(OrderBrief::getId).collect(Collectors.toList()); ListOrder orders orderMapper.selectBatchByIds(ids); // 第三步按第一步的顺序重排不能依赖 IN 查询的返回顺序 MapLong, Order map orders.stream() .collect(Collectors.toMap(Order::getId, Function.identity())); ListOrderVO vos briefs.stream() .map(b - OrderVO.of(map.get(b.getId()))) .collect(Collectors.toList()); return PageResult.of(vos, query); }逐段说明第 3-5 行的selectBriefByCover只 SELECTid, created_at完全走覆盖索引Extra是Using index。第 11 行按主键 IN 批量查20 个主键在聚簇索引上是 20 次点查B 树高度 3 层全在 Buffer Pool 里实测总耗时 1ms 出头。第 13-18 行必须重排。WHERE id IN (...)的返回顺序由 MySQL 决定通常是主键升序和我们要的时间倒序不一致。这个坑我们线上出过一次列表页顺序随机跳被用户投诉了。对应的 MapperSelect(script SELECT id, created_at FROM t_order WHERE shop_id #{shopId} AND created_at gt; #{startTime} AND status IN foreach items collectionstatusList open( separator, close)#{s}/foreach ORDER BY created_at DESC LIMIT #{offset}, #{size} /script) ListOrderBrief selectBriefByCover(Param(shopId) Long shopId, Param(statusList) ListInteger statusList, Param(startTime) LocalDateTime startTime, Param(offset) int offset, Param(size) int size);这里有个细节status IN必须写在created_at范围条件之后吗在 SQL 文本里顺序无所谓优化器会自己重排。真正决定索引使用方式的是索引里列的顺序不是 WHERE 子句里条件的书写顺序。这一点很多人搞混。深分页OFFSET 100000 的代价同一个接口还有一个隐藏问题。运营后台支持跳页有人点到第 5000 页... ORDER BY created_at DESC LIMIT 20 OFFSET 100000;这条 SQL 在 2400 万行的表上要 3.8 秒。原因是 MySQL 必须先扫描并丢弃前 100000 行再返回 20 行。OFFSET 越大越慢和索引好不好没关系。我们用游标分页改掉了大部分场景public CursorPageOrderVO scrollOrders(Long shopId, String cursor, int size) { LocalDateTime lastTime null; Long lastId null; if (StringUtils.hasText(cursor)) { String[] parts CursorCodec.decode(cursor).split(_); // 时间戳_主键 lastTime LocalDateTime.ofEpochSecond(Long.parseLong(parts[0]), 0, ZoneOffset.ofHours(8)); lastId Long.parseLong(parts[1]); } // SQL: WHERE shop_id? AND (created_at ? OR (created_at ? AND id ?)) // ORDER BY created_at DESC, id DESC LIMIT ? ListOrderBrief briefs orderMapper.scrollByCursor(shopId, lastTime, lastId, size 1); boolean hasNext briefs.size() size; if (hasNext) { briefs briefs.subList(0, size); } String nextCursor hasNext ? CursorCodec.encode(last(briefs).getCreatedAt().toEpochSecond(ZoneOffset.ofHours(8)) _ last(briefs).getId()) : null; return CursorPage.of(fill(briefs), nextCursor, hasNext); }几个关键点第 9-10 行的复合条件(created_at ? OR (created_at ? AND id ?))是为了处理同一秒内多条订单的情况。只用created_at 会漏数据只用id 在时间倒序下不成立。第 11 行查size 1条用多出来的那一条判断有没有下一页省掉一次COUNT(*)。这张表上的COUNT(*)要 6 秒以上能不查就不查。第 18-20 行把游标编码成不透明字符串返回给前端避免前端自己拼时间和主键。游标分页的限制很明确不能跳页。运营那边确实需要跳页我们的折中是跳页只允许在最近 7 天的范围内7 天的数据量在单店铺下通常几千条OFFSET 再大也不痛。超过 7 天强制用导出功能走离线任务。怎么先发现慢查询我们没有等 MySQL 慢日志而是在应用侧加了一层拦截因为慢日志的long_query_time设成 1 秒很多 300-800ms 的温水查询根本不进日志但它们的总量才是压垮连接池的元凶。Intercepts({Signature(type Executor.class, method query, args {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})}) public class SlowSqlInterceptor implements Interceptor { private static final long THRESHOLD_MS 200; Override public Object intercept(Invocation invocation) throws Throwable { MappedStatement ms (MappedStatement) invocation.getArgs()[0]; long start System.nanoTime(); try { return invocation.proceed(); } finally { long costMs (System.nanoTime() - start) / 1_000_000; if (costMs THRESHOLD_MS) { // statementId 形如 com.xx.OrderMapper.selectBriefByCover Metrics.timer(mybatis.slow.query, stmt, ms.getId()) .record(costMs, TimeUnit.MILLISECONDS); log.warn(slow sql cost{}ms stmt{} traceId{}, costMs, ms.getId(), MDC.get(traceId)); } } } }第 5 行阈值定 200ms比 MySQL 慢日志的 1 秒严格 5 倍。第 16-18 行同时上报指标这样 Grafana 上能按stmt维度看哪个 Mapper 方法慢的次数最多而不是只有一堆散落的日志。第 19 行带上 traceId能直接跳到链路里看这条 SQL 在整个请求中的占比。上线一周这个拦截器捞出来 17 个从没进过慢日志的语句其中 3 个是每次请求都跑、单次 250ms 左右的隐形杀手。复盘数据完整改造覆盖索引 两段式查询 游标分页之后指标改造前错误索引版本最终版本列表接口 P99420ms510ms86ms列表接口 QPS11009002600深分页第 5000 页3.8s3.9s不支持跳页/游标 40mst_order 索引总大小1.9 GB2.6 GB2.2 GB订单写入 TPS320028503050写入 TPS 从 3200 降到 3050这是多一个索引列的固定代价大约 4.7%。读性能提升 2.3 倍我们认为这个交换是划算的——这张表读写比大约是 30:1。我的几条判断不要为了让 WHERE 里每个字段都进索引而建索引。索引的价值排序应该是先满足等值过滤和排序再考虑覆盖。等值列放前面、范围列放后面、排序列紧跟等值列这个顺序比字段全不全重要得多。加索引前一定要在生产数据量级上跑 EXPLAIN而且要挑最极端的参数。我们那次翻车就是因为在开发库3 万行上测怎么测都快。头部店铺 41 万条订单的分布在小数据集上完全体现不出来。现在我们的规范是任何索引变更必须提供在生产影子库上对 top10 大客户参数的 EXPLAIN 结果。我不建议给低区分度的列单独建索引。比如status只有 5 个值2400 万行里 status1 占 60%单独给它建索引优化器大概率不会用反而白白增加写入成本。它只适合作为组合索引的后置列。最后抛个问题我们现在的两段式查询先覆盖索引取主键再按主键回捞本质上把一次查询拆成了两次网络往返。在跨机房部署、RTT 有 3-5ms 的场景下这个拆分还划算吗我倾向于认为在 RTT 超过 5ms 时应该退回单次查询 更宽的覆盖索引但没有实测数据支撑。你们有跨机房的实践经验吗评论区聊聊。