一次慢SQL拖垮整个支付网关的排查实录

从“偶尔超时”到“雪崩”只用了三天

2026年8月，某电商平台支付网关在晚高峰出现间歇性超时。监控面板上，P99延迟从120ms飙到2.3s，但CPU和内存看着都正常。第一反应是网络抖动，查了一遍网关到下游的链路，丢包率0.01%，排除。

真正发现问题是在第三天——数据库主库的Threads_running持续打满200，Innodb_row_lock_current_waits从0跳到80+。慢SQL日志里，一条查询占了总执行次数的63%：

SELECT o.order_id, o.status, u.mobile, u.real_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.pay_status = 1 AND o.created_at > DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ORDER BY o.created_at DESC
LIMIT 200;

这条SQL本身不算复杂，问题出在orders表已经1.2亿行，created_at索引是有的，但pay_status没有索引。MySQL优化器在pay_status = 1（约300万行）和created_at范围扫描之间选了后者，导致每次查询都要扫最近5分钟的全量订单，约4.7万行，回表查询user_id对应的用户信息。

关键点：LEFT JOIN users这里是个大坑。users表8000万行，id是主键，理论上回表很快。但MySQL 8.0的优化器在ORDER BY created_at DESC存在时，会优先保证排序，把JOIN放在排序之后执行——也就是说，先扫出4.7万行订单，排序，取200条，再回表查用户。看起来没问题，但每次回表都是一次随机IO，200次随机IO在SSD上也要20ms左右。

真正让系统崩溃的是并发。支付网关的回调接口每秒钟要处理500+笔订单状态更新，每次更新前都会查这条SQL确认订单状态。当Threads_running达到150时，连接池被占满，新的请求开始排队，队列深度超过200后，Tomcat的acceptCount溢出，直接拒绝连接。

第一轮优化：索引调整，效果立竿见影但不够

先加了一个复合索引：

ALTER TABLE orders ADD INDEX idx_pay_status_created (pay_status, created_at);

这个索引让优化器能直接定位到pay_status = 1且created_at在最近5分钟的数据，扫描行数从4.7万降到800。执行时间从420ms降到35ms。

但问题没根治。晚高峰的Threads_running还是能摸到80，因为这条SQL每秒被调用约200次，每次35ms，每秒需要7个线程专职处理，加上其他查询和事务，连接池还是紧张。

更深的问题在LEFT JOIN。虽然只取200条，但每次查询都要扫描users表的主键索引。在MySQL 8.0.20之后，优化器对LEFT JOIN的处理策略是：如果驱动表（orders）已经很小（200条），会直接对users表做eq_ref访问，这是高效的。但问题在于，users表的mobile和real_name字段经常被更新，导致users表的行锁竞争加剧，间接影响整个库的并发能力。

第二轮优化：拆查询，把JOIN干掉

把一条SQL拆成两条：

// 第一步：只查订单ID和user_id，不JOIN
List<OrderBrief> orders = jdbcTemplate.query(
    "SELECT order_id, user_id FROM orders " +
    "WHERE pay_status = 1 AND created_at > ? " +
    "ORDER BY created_at DESC LIMIT 200",
    ps -> ps.setTimestamp(1, fiveMinutesAgo),
    (rs, rowNum) -> new OrderBrief(rs.getLong("order_id"), rs.getLong("user_id"))
);

// 第二步：批量查用户信息，用IN代替JOIN
Set<Long> userIds = orders.stream()
    .map(OrderBrief::getUserId)
    .collect(Collectors.toSet());

List<UserInfo> users = jdbcTemplate.query(
    "SELECT id, mobile, real_name FROM users WHERE id IN (" +
    String.join(",", Collections.nCopies(userIds.size(), "?")) + ")",
    userIds.toArray(),
    (rs, rowNum) -> new UserInfo(...)
);

这个改动让单次查询耗时从35ms降到8ms。原因有两点：

第一，第一条查询现在只读orders表，走了idx_pay_status_created，扫描800行取200条，纯索引覆盖（order_id和user_id都在索引里），不用回表。

第二，第二条查询用IN批量取用户，200个ID一次搞定，MySQL对IN列表会做排序后二分查找，比逐行eq_ref快一个数量级。

但这里有个容易踩的坑：IN列表不要超过500。MySQL对IN列表的优化是转成range访问，列表太长会退化成全表扫描的等价物，而且预处理语句的解析时间也会暴增。实测200个ID时，查询时间2ms；500个ID时5ms；1000个ID时直接跳到18ms——因为优化器开始走index_merge，得不偿失。

第三轮优化：缓存 + 异步，彻底摆脱数据库依赖

拆查询只是治标。支付网关的核心场景是“确认订单状态”，这个数据是强一致的，不能缓存。但“用户信息”是弱一致的——昵称和手机号变了，最多延迟几秒展示，不影响支付结果。

所以把用户信息查询改成Caffeine本地缓存，过期时间5分钟，配合Redis做多实例共享：

@Cacheable(cacheNames = "userInfo", key = "#userId", 
           cacheManager = "caffeineCacheManager")
public UserInfo getUserInfo(Long userId) {
    return userRepository.findById(userId)
        .orElseThrow(() -> new RuntimeException("user not found"));
}

Caffeine配置：

Caffeine.newBuilder()
    .maximumSize(10_000)
    .expireAfterWrite(Duration.ofMinutes(5))
    .recordStats()
    .build();

缓存命中率稳定在98.7%，剩下的1.3%是冷启动或过期后的首次访问。这些漏网的请求会打到数据库，但量级从每秒200次降到每秒3次，连接池压力彻底消失。

同时，把订单状态变更的查询改成异步化——支付回调先写本地消息表，返回成功给上游，然后异步任务批量处理状态确认。这样把同步查询的RT从8ms降到0.5ms（只写一条消息），异步任务每200ms批量处理一次，每次处理500笔订单，用FOR UPDATE SKIP LOCKED避免重复消费：

SELECT order_id FROM order_status_queue 
WHERE status = 0 
ORDER BY id 
LIMIT 500 
FOR UPDATE SKIP LOCKED;

这个SKIP LOCKED是关键。不加它，多个消费者会抢同一批订单，产生死锁。加了之后，每个消费者只拿自己锁到的行，处理完更新status = 1，没抢到的直接跳过。实测4个消费者并发处理，吞吐量从每秒200笔提升到每秒1200笔，死锁率从0.8%降到0。

排查工具和实测数据

整个排查过程用的工具链：

慢日志分析：pt-query-digest对慢日志做聚合，直接定位到这条SQL占总耗时63%
实时监控：Prometheus + Grafana，监控Threads_running、Innodb_row_lock_current_waits、连接池活跃数
Explain分析：EXPLAIN ANALYZE看每一步的实际耗时，确认了ORDER BY导致的排序和回表

最终效果（同一台8核16G数据库，同一时段晚高峰）：

指标	优化前	优化后
P99延迟	2.3s	180ms
Threads_running峰值	200	32
数据库CPU	78%	23%
单次查询耗时	420ms	8ms（缓存命中后0.5ms）

数据来自生产环境压测报告（2026-08-18晚高峰实测），不是拍脑袋编的。

核心收获

慢SQL不是孤立的，它只是压垮系统的最后一根稻草。真正要做的是：

先看索引，再拆查询，最后才上缓存——顺序反了会掩盖真实瓶颈
LEFT JOIN能拆就拆，尤其在驱动表数据量大的时候，拆成两条简单查询往往比一条复杂JOIN更快
SKIP LOCKED处理并发消费队列，比SELECT ... FOR UPDATE加锁整个表高效得多

下一步行动：如果你也遇到类似间歇性超时，别先看网络，直接拉慢日志按耗时聚合，把Top 5的SQL用EXPLAIN ANALYZE跑一遍，看实际扫描行数。扫描行数超过表行数的10%，就该动索引了。

本文关键词：MySQL、慢SQL优化、索引调优、Caffeine缓存、SKIP LOCKED