慢查询排查的核心是建立系统化的定位链路,从应用日志、数据库状态、执行计划到索引优化逐层推进,任何单一工具都无法覆盖全部场景。在一次真实的线上事故中,我们通过全链路日志和慢查询日志的交叉比对,在30分钟内定位到一条漏掉联合索引的SQL,最终将接口响应时间从2.3秒降到80毫秒。慢查询排查不是碰运气,而是有章可循的工程方法,本文总结的实战经验可直接应用于生产环境。
事故发生在一次大促活动期间,订单查询接口出现大面积超时,监控面板显示P99延迟从200毫秒飙升到3秒。我们立即查看应用日志,发现大量SQL执行时间超过1秒,同时数据库连接池打满,导致新请求排队。据[云智慧]《数据库运维白皮书》数据显示:超过67%的应用性能问题最终根因是数据库慢查询。
第一反应是查看慢查询日志,但发现日志中只记录了执行超过1秒的语句,而很多实际拖慢系统的SQL执行时间在500毫秒左右,并未被捕获。这说明慢查询日志的阈值设置不合理,同时也提醒我们,慢查询排查不能仅限于慢查询日志本身,还要结合数据库实时会话和性能视图。
慢查询日志是排查的第一手资料,但默认配置往往不够用。MySQL中long_query_time的默认值是10秒,这个阈值在生产环境几乎没有意义,因为超过10秒的SQL通常已经被应用层超时拦截了。我们建议将阈值设置为1秒,对于高并发核心链路甚至设置为200毫秒。
[mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = ON log_throttle_queries = 10
其中log_queries_not_using_indexes会记录所有未使用索引的查询,这对发现潜在慢查询非常有效。但需要注意,某些全表扫描的查询可能执行很快,却被记录下来,产生大量日志,因此建议配合log_throttle_queries限制每分钟记录条数,避免日志暴涨。
获取到慢查询日志后,不能靠肉眼看,需要工具辅助聚合分析。常用的有mysqldumpslow和pt-query-digest。pt-query-digest功能更强大,能按查询指纹聚合,输出执行次数、平均耗时、响应时间占比等指标。
pt-query-digest /var/log/mysql/slow.log --limit 20
通过分析,我们立刻发现一条针对订单表的查询占据了70%的响应时间。该查询使用了ORDER BY create_time DESC,但SQL中却对user_id和status做了等值筛选,而索引设计只有idx_user_id,导致排序需要额外执行filesort,数据量大时性能急剧下降。
对具体慢SQL执行EXPLAIN,能看到全貌。我们执行EXPLAIN SELECT * FROM orders WHERE user_id = '123' AND status = 1 ORDER BY create_time DESC LIMIT 20,发现type为ref,rows预估为5万行,且Extra中有Using filesort。这就是性能瓶颈的核心——排序未走索引。
解决方案是创建一个联合索引(user_id, status, create_time),这样等值条件和排序都能命中索引,MySQL可以直接按索引顺序返回数据,跳过filesort。优化后再次执行EXPLAIN,type变为ref,rows降到几十行,Extra不再出现filesort。
对于带等值条件+排序的查询,索引设计应遵循“等值列前置,排序列靠后”的规则。具体来说,将WHERE中的等值比较列放在索引前面,ORDER BY列放在后面。这样MySQL可以利用索引的有序性避免排序操作,但必须注意:如果等值条件包含范围查询(如>、<),那么范围列之后的索引列将无法用于排序。
| 场景 | 索引设计 | 是否走排序 |
|---|---|---|
| 等值+排序 | 复合索引(等值列, 排序列) | 利用索引顺序 |
| 范围+排序 | 范围列不能后接排序 | 可能filesort |
| 多等值+排序 | 所有等值列+排序 | 利用索引顺序 |
上面的例子就是典型的“等值+排序”,所以联合索引(user_id, status, create_time)完美契合。
索引失效是慢查询的常见原因,通过EXPLAIN中的type列可以快速判断。常见失效场景包括:对索引列使用函数或表达式,如WHERE DATE(create_time) = '2023-01-01';隐式类型转换,如字符串列与数字比较;LIKE模式以通配符开头,如LIKE '%keyword';OR条件导致无法使用索引合并;以及IS NULL在部分情况下无法利用索引。这些场景的通用解决思路是改写SQL,让索引列参与原始比较,或者创建函数索引。
慢SQL不只是单条语句的问题,它会引发连锁反应。在我们的案例中,一条慢查询占用了数据库的大量IO和CPU,导致其他正常查询也变慢。通过监控数据库的活跃会话数和慢查询数量,我们发现慢查询阻塞了连接池,进而拖垮整个应用。在排查过程中,我们使用了SHOW ENGINE INNODB STATUS查看锁等待情况,同时结合performance_schema中的events_statements_summary_by_digest表,按延迟排序查看所有SQL的响应分布。
慢查询排查可以归纳为五步:第一步,开启并合理配置慢查询日志,阈值设为1秒以下;第二步,用pt-query-digest等工具聚合分析,定位TOP N慢SQL;第三步,对每一条慢SQL执行EXPLAIN,检查是否有全表扫描、文件排序、索引失效等性能陷阱;第四步,针对性能瓶颈创建或调整索引,并验证执行计划的改善;第五步,建立监控告警,观察优化效果,同时关注连接池和数据库整体负载。每一步都有明确的目标和工具,形成一个闭环。
慢查询排查的最终目标是建立预防机制,而不是每次等事故再救火。建议将慢查询日志收集到集中式日志系统,并设置基于慢查询数量或延迟增长的告警。同时,定期审查慢查询日志是一种投资,据[Percona]《数据库性能优化实践》报告:定期审查慢查询日志并优化索引,可以减少70%以上的数据库性能问题。因此,将慢查询排查纳入日常巡检,是保障系统稳定性的长期之策。
评论
加载中...