慢SQL排查与优化：EXPLAIN执行计划与索引失效陷阱

1. 慢SQL排查的常见误区与正确思路刚入行的开发人员遇到慢查询时第一反应往往是数据库性能不行。但根据我处理过300慢查询案例的经验真正由数据库硬件或配置导致的问题不足20%。更多时候问题出在我们对SQL执行机制的理解不足。上周排查的一个典型案例某电商平台订单查询接口平均响应时间从200ms飙升到2秒。DBA团队坚持是数据库负载过高但监控显示CPU利用率仅40%。最终我们用EXPLAIN结合采样日志分析发现是应用程序错误地循环执行了相同查询。这种认知偏差在慢查询排查中非常普遍。2. EXPLAIN执行计划深度解析2.1 关键指标解读方法论拿到EXPLAIN输出时我通常会按以下顺序重点检查EXPLAIN SELECT * FROM orders WHERE user_id 100 AND status paid;重点关注type字段ALL全表扫描立即报警index全索引扫描range索引范围扫描ref非唯一索引查找eq_ref主键/唯一索引查找去年优化过一个物流系统查询type从ALL优化到ref后响应时间从1800ms降到23ms。2.2 索引失效的六大陷阱隐式类型转换VARCHAR字段用数字查询-- user_id是varchar但用数字查询 SELECT * FROM users WHERE user_id 100;前导模糊查询LIKE %keyword无法使用索引函数操作字段WHERE DATE(create_time) 2023-01-01OR条件未全覆盖WHERE a1 OR b2只有a或b有索引时不符合最左前缀联合索引(a,b,c)但条件只有b,c索引选择性差性别字段建索引只有两个取值3. 采样日志分析实战技巧3.1 慢查询日志配置黄金参数推荐配置MySQLslow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 # 超过1秒记录 log_queries_not_using_indexes 1 # 记录未走索引查询 log_throttle_queries_not_using_indexes 10 # 限制每分钟记录数量3.2 日志分析三板斧pt-query-digest工具pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt时间维度分析SELECT DATE_FORMAT(start_time, %Y-%m-%d %H:00) AS hour, COUNT(*) AS query_count FROM mysql.slow_log GROUP BY hour;模式识别相同SQL模板出现的频率执行时间分布是否集中在特定时段参数变化趋势突然出现大量新参数值4. 全链路瓶颈定位方法论4.1 应用层问题特征相同SQL短时间内重复执行不合理的ORM批量操作事务未及时提交导致锁等待连接池配置不当最大连接数过小曾遇到一个Java应用因连接池maxActive20导致大量请求排队表象却是数据库响应慢。4.2 网络层排查要点# 检查网络延迟 tcpping db-server 3306 # 检查包重传率 netstat -s | grep -i retransmit4.3 数据库服务器检查清单磁盘IOiostat -x 1 # 关注%util和awaitCPU调度top -H -p $(pgrep mysqld) # 查看MySQL线程CPU占用内存压力free -h vmstat 15. 经典案例复盘5.1 分页查询优化优化前耗时1.8sSELECT * FROM large_table ORDER BY id LIMIT 1000000, 10;优化方案SELECT * FROM large_table WHERE id (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 10;5.2 订单统计报表优化原方案每天凌晨跑批SELECT COUNT(*) FROM orders WHERE create_time BETWEEN ? AND ? GROUP BY product_category;改为建立汇总表定时更新使用物化视图对历史数据预计算6. 性能优化工具箱6.1 必备监控指标QPS/TPS波动连接数使用率缓存命中率锁等待时间临时表创建频率6.2 推荐分析工具工具名称适用场景安装方式pt-query-digest慢日志分析percona-toolkit包mysqlsla查询分类统计gem install mysqlslavividcortex实时性能监控SaaS服务Prometheus自定义指标收集与告警二进制部署7. 避坑指南与最佳实践索引创建原则单表索引不超过5个联合索引字段不超过3个避免在更新频繁的字段建索引SQL编写规范-- 反例 SELECT * FROM table; -- 正例 SELECT id, name FROM table WHERE status 1;连接池配置经验值初始连接数 平均QPS * 平均响应时间(秒)最大连接数不超过应用服务器数 × 50事务设计要点事务持续时间100ms避免在事务中进行网络调用批量操作使用分批提交最后分享一个真实教训某次优化将单条SQL从2s降到200ms很满意后来发现该SQL被循环执行1000次——优化应该先减少执行次数再优化单次性能。性能优化要有系统视角这也是为什么EXPLAIN要配合全链路分析才能真正解决问题。