数据库连接池被慢查询拖垮的调优过程

线上服务突然开始大面积报警，接口响应时间从正常的50毫秒飙升到30秒，用户反馈页面打不开，运维那边已经在群里喊了。登录服务器一看，线程池满、CPU飙高，数据库连接池的监控显示活跃连接数直接打满，等待队列排了几百个请求。这种场面，十有八九是数据库连接池被拖垮了，而背后的元凶，往往就是几条没有被关注到的慢查询。

先还原一下当时的场景。我们用的是HikariCP连接池，配置了最大连接数50，这个数值平时完全够用。出问题的时候，数据库端的活跃会话数也是50，并且全部处于"Executing"状态，持续时间超过20秒。这意味着连接池里的所有连接都被占用了，新的请求拿不到连接，只能在应用端排队等待，直到超时。

数据库连接池的本质是一个资源池，它维护着一组到数据库的TCP长连接。当业务请求需要访问数据库时，就从池子里借用一个连接，用完再还回去。如果所有连接都被长时间占用，池子就空了，后续请求要么阻塞等待，要么直接报错。慢查询之所以能拖垮连接池，就是因为它会让连接长时间无法释放，一个连接被占用20秒，50个连接全部被占满，整个服务就瘫痪了。

第一件事不是重启，而是抓现场。重启能暂时恢复，但问题还会复现。我们需要知道到底是哪些SQL在作恶。先看数据库的慢查询日志，MySQL里设置long_query_time为2秒，结果发现大量超过10秒的查询，集中在三张业务表上。再看应用层的数据库中间件监控，找到平均执行时间突增的SQL指纹。

定位到一条典型的慢查询，SQL大致是这样的：

SELECT o.id, o.order_no, o.user_id, o.amount, o.status,
       u.name, u.phone,
       p.product_name, p.price
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.create_time BETWEEN '2024-01-01' AND '2024-06-30'
AND o.status IN (1,2,3)
ORDER BY o.create_time DESC
LIMIT 20;

这条SQL看起来人畜无害，还加了LIMIT 20，但实际执行却要十几秒。用EXPLAIN一看，type是ALL，全表扫描了orders表，扫描行数接近500万。原因是create_time字段虽然有索引，但查询条件里还混了status的IN条件，优化器选择了错误的执行计划，导致索引失效。

很多人以为加了索引就万事大吉，实际上索引是否生效取决于多个因素。在这个案例里，orders表的create_time索引确实存在，但status字段的过滤条件改变了优化器的成本估算。当IN列表里的值对应的数据量较大时，优化器可能认为回表取数据的代价太高，不如直接全表扫描。

还有一个隐藏问题，LEFT JOIN的驱动表选择。这条SQL以orders为驱动表去关联users和products，如果orders表返回的数据量被错误估算，整个JOIN的性能就会崩塌。我们通过强制使用索引FORCE INDEX( idx_create_time )临时解决了问题，执行时间从15秒降到0.1秒。但这不是最终方案，因为FORCE INDEX太生硬，数据分布变化后可能再次失效。

在修复慢查询的同时，连接池本身的配置也需要重新审视。HikariCP有几个关键参数：maximumPoolSize最大连接数、connectionTimeout连接超时时间、idleTimeout空闲超时、maxLifetime最大生命周期。最大连接数不是越大越好，数据库能承受的并发连接数是有限的，MySQL一般建议不超过CPU核数的两倍。我们当时把最大连接数从50调到100，只是让问题暴露得慢一点，并没有解决根本问题。

真正有用的参数是connectionTimeout，它决定了应用等待连接的最大时间。我们把它从默认的30秒调到了3秒，并配合熔断机制，当获取连接超时时直接返回降级响应，而不是让调用方一直等待。这样即使连接池满了，也不会造成雪崩效应。另外，leakDetectionThreshold参数可以检测连接泄漏，设置成60秒，如果某个连接被持有超过60秒还没归还，就会打印警告日志，帮助发现代码里没有正确关闭连接的地方。

回到SQL本身，我们需要一个不依赖FORCE INDEX的稳定优化方案。分析业务场景后发现，这个查询是给后台管理系统用的，用户通常按时间范围筛选订单，状态筛选是可选的。于是我们做了两件事：第一，把create_time的索引调整为联合索引，覆盖查询字段，避免回表；第二，根据status是否传值，动态构建SQL，走不同的索引。

具体操作是创建了一个覆盖索引：

ALTER TABLE orders ADD INDEX idx_create_time_cover 
(create_time, status, id, order_no, user_id, amount);

这个索引把查询需要的字段都包含了，优化器在做执行计划时，发现可以只扫描索引就能拿到所有数据，不再需要回表，成本大幅降低。同时，在应用代码里判断status参数是否为空，如果为空就去掉IN条件，让查询只走时间范围扫描。这样优化后，无论status传什么值，执行时间都稳定在100毫秒以内。

慢查询不只是SELECT语句，未提交的事务和行锁等待同样是连接池杀手。我们在排查过程中发现，有一个批量更新操作在事务里执行了多次SELECT FOR UPDATE，并且事务迟迟没有提交。这个事务持有行锁，其他需要修改相同行的请求就被阻塞，连接一直挂着不释放。

排查方法是查看数据库的锁等待情况：

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

发现有一个事务已经运行了超过15分钟，源头是一个后台导出任务。解决方案是把大事务拆小，每次只处理1000条数据，及时提交，释放锁资源。同时给事务设置超时时间，MySQL的innodb_lock_wait_timeout默认50秒，我们调整到10秒，避免长时间锁等待。

数据库层面的优化做好了，但应用层还需要一道防线。慢查询可能因为数据量增长再次出现，或者某个新上线的功能引入低效SQL。我们不能每次都等到连接池被打满才响应。引入熔断机制，当慢查询比例超过阈值时，主动切断对数据库的调用，返回降级数据。

我们用的是Sentinel做流量控制，针对数据库查询的耗时设置了熔断规则：当1分钟内慢调用比例超过20%时触发熔断，熔断时长30秒。这30秒内所有数据库请求直接走降级逻辑，比如返回缓存数据或默认空列表。同时配置了线程池隔离，把核心业务和非核心业务的数据库操作分到不同的线程池，避免一个慢查询拖垮整个服务。

有些查询本身就不应该每次都打到数据库。比如那个订单列表查询，后台管理系统虽然对实时性有一定要求，但完全可以接受秒级的延迟。我们引入了Redis缓存，把查询条件作为key，结果集序列化后存进去，过期时间设5秒。这样即使有多个管理员同时查询，数据库压力也被大幅削减。

缓存的key设计需要注意粒度，我们把查询条件里的时间范围、状态、分页参数组合成MD5值作为key，确保缓存命中率。同时设置了缓存穿透的保护，当查询结果为空时也缓存一个空标记，避免恶意请求穿透到数据库。

问题处理完了，但更重要的是怎么提前发现。我们补了三层监控：第一层是数据库层面，监控慢查询数量、锁等待数量、活跃连接数，设置告警阈值；第二层是连接池层面，监控HikariCP的活跃连接数、等待连接数、连接创建速率；第三层是应用层面，监控接口的P99响应时间、错误率。

特别提一下HikariCP的Metrics，它暴露了很详细的指标，包括连接获取时间、使用时间、等待时间。我们把这些指标接入Prometheus，用Grafana做了大盘，一旦等待连接数超过5个就发告警。这样在连接池即将被打满的前几分钟，我们就能收到通知，提前介入。

最后不得不提代码规范。连接泄漏是慢查询之外的另一大元凶。我们在代码审查中发现，有些地方获取了Connection但没有在finally块里关闭，或者使用了连接池之后还在手动管理事务，导致连接归还异常。统一规定必须使用try-with-resources语法，确保连接、Statement、ResultSet都能自动关闭。

对于事务操作，要求事务尽可能短，不要在事务里做RPC调用、文件IO等耗时操作。如果一个方法同时涉及数据库操作和外部服务调用，必须把外部调用移到事务之外。这些规范看似基础，但在实际项目中违反的情况比比皆是，每一次违反都可能成为下一次故障的导火索。

数据库连接池被慢查询拖垮，表面上是连接池的问题，实际上是整个数据访问链路的综合体现。从SQL写法、索引设计、事务管理，到连接池配置、应用层防御、监控告警，每一个环节都可能成为短板。单点优化只能缓解，系统性加固才能根治。这次调优过程让我深刻认识到，性能优化不是一次性工作，而是需要持续关注和迭代的工程实践。