线上告警响起来的时候我盯着告警内容看了好几遍一条执行超过1000ms的SQL。打开慢查询日志找到那条语句看起来人畜无害——从一张一万多行的订单小表里按主键id取一条记录。第一反应和几乎所有同事都一样纳尼这也能磨蹭1秒但事后复盘真正有收获的不是SQL本身有多复杂而是排查过程中踩过的那些认知误区。这篇文章把我处理这类慢SQL的完整思路、定位方法、常见根因和优化动作整理出来希望能帮你在下一次遇到不可能慢的SQL时少走弯路。不管是MySQL、PostgreSQL还是SQL Server慢SQL优化的底层逻辑是共通的先确认时间从哪来再看执行计划最后才是改SQL或加索引。适合开发、运维以及所有需要跟数据库打交道的同学阅读。1. 别急着改SQL先搞清1000ms是怎么来的1.1 客户端耗时和执行耗时是两码事很多刚接触性能问题的同学把一条SQL执行超过1000ms直接理解为数据库执行耗时1000ms这是一个非常大的误区。从你点击查询按钮到看到结果中间要经历客户端连接池获取连接、SQL发送、数据库解析优化、执行、结果集网络传输、驱动或ORM框架对象映射这几个环节。假设SQL在数据库内部只跑了50ms但连接被某个未提交事务占住排队等了900ms客户端感知到的总耗时照样超过1秒。所以拿到告警之后第一步不是急着打开编辑器而是先问一句这个1000ms统计来自哪里不同来源对耗时的定义差别很大。MySQL慢查询日志里的Query_time是服务器端执行完SQL后的时间不包含客户端网络传输它最接近数据库自身的真实耗时APM工具比如SkyWalking、Zipkin上报的往往是全链路耗时包含网络、RPC调用、数据库驱动处理等数据库审计日志或Oracle AWR报告里的SQL耗时则包含数据库内部等待和执行的总时间。确认计时源之后再开始定位方向才不会跑偏。1.2 1秒阈值代表什么1秒是用户可以感知到卡顿的临界值。很多团队把MySQL的long_query_time设为1秒于是凡是超过1秒的SQL都会触发告警。这个阈值本身没问题但要明白它只代表这条SQL跑得不快该看看了不代表系统一定出了问题。不同业务场景对耗时的接受度差异极大一条后台报表的聚合查询可能需要5秒调度任务半夜跑没人会在意一条支付回调里的查库操作如果超过500ms用户的手指可能已经在屏幕上狂点了。所以当超过1000ms的告警出现在眼前先评估这条SQL处在什么链路里、影响范围多大。是用户同步感知的核心接口还是异步任务里的边缘查询如果核心接口出现慢SQL哪怕只有几百毫秒也要重视如果是凌晨的分析任务那更需要关注的可能是资源消耗而非单次耗时。慢查询日志的阈值只是信号最终是否要优化要结合业务诉求来判断。2. 排查一条慢SQL的完整路径2.1 用EXPLAIN ANALYZE抓出真实执行路径当确认一百毫秒级以上的耗时确实落在数据库执行阶段后第一个动作就是把真实执行计划抓出来。不是猜不是看SQL文本脑补而是拿到优化器实际选择的执行路径和每一步的耗时。在MySQL里对慢查询日志里的SQL做EXPLAIN ANALYZE能看到每一步的实际行数和实际耗时PostgreSQL同样支持EXPLAIN ANALYZESQL Server则用SET STATISTICS IO ON、SET STATISTICS TIME ON配合图形化执行计划Oracle有DBMS_XPLAN.DISPLAY_CURSOR可以直接查看对应SQL游标的执行计划。以MySQL为例我在处理慢SQL时通常会这样操作EXPLAIN ANALYZE SELECT id, user_name, order_no FROM t_order WHERE user_id 20240601 AND create_time 2024-06-01 00:00:00;重点关注EXPLAIN ANALYZE输出里的几个关键项目table和rows优化器预估扫描的行数如果预估行数和实际行数差太多说明统计信息可能有问题。filtered过滤比例越低越说明还有进一步利用索引或下推条件的空间。actual time每一步的实际耗时能定位最耗时的那一步。possible_keys和key是有哪些可用索引和实际用了哪个索引两者对比能直接看出索引有没有被选用。这里要专门提醒一句千万别只做EXPLAIN不做ANALYZE。EXPLAIN只输出优化器的计划但计划是它打算怎么跑EXPLAIN ANALYZE打印的是它实际跑了多久、扫描了多少行、每一步真实耗时多少两者结合才能真正判断慢在哪里。MySQL从8.0.18开始支持EXPLAIN ANALYZE8.0.21之后更稳定如果你还在用5.7可以用EXPLAIN FORMATJSON再加profile去分析。MariaDB的EXPLAIN ANALYZE也能直接使用。2.2 锁等待和会话状态往往藏在高耗时的背后执行计划看着很正常走了索引预估行数也不大可SQL还是很慢这种情况下多半不是查询本身的问题而是资源竞争。最常见的就是锁等待。在MySQL InnoDB引擎下可以执行下面两条SQL查看当前事务和锁等待情况SELECT * FROM information_schema.innodb_trx\G SELECT * FROM information_schema.innodb_lock_waits\G关注trx_state字段是不是LOCK WAIT以及blocking_trx_id是被哪个事务卡住了。还有一种很隐蔽的情况同一行数据被某个长事务持有排他锁查询在二级索引上已经找到了目标记录但要回表读取主键数据时必须等锁释放于是阻塞在那里。这种场景下光看执行计划永远找不到问题所在。SQL Server可以查sys.dm_exec_requests重点看wait_type、wait_time、blocking_session_id三个字段Oracle则看v$session里的event如果event是enq: TX - row lock contention那就是典型的行锁等待。除了锁等待数据库全局负载也要扫一眼。MySQL下可以用SHOW GLOBAL STATUS LIKE Threads_connected看连接数是否接近max_connections用SHOW ENGINE INNODB STATUS看buffer pool命中率和最近死锁信息。如果发现服务器CPU、磁盘IO已经接近饱和那这条SQL可能只是压垮骆驼的最后一根稻草真正的元凶是整体负载过高。这个问题非常常见单独改一条SQL往往解决不了得先做资源维度的降载。2.3 能不能复现决定排查效率处理慢SQL最怕的是拿静态SQL文本在开发库上纸上谈兵。你最好能在目标环境上复现但注意不要直接在高峰期生产库里乱执行。如果架构里有只读实例或从库放到那边验证是最稳妥的。复现时要注意两点一是传入参数必须相同。查询条件值的分布往往会直接影响执行计划比如user_id1可能只有一条记录user_id2下面挂了三千万条订单优化器对这两种情况可能选择完全不同的执行方式。二是数据分布和统计信息要和线上一致否则复现结果根本没有说服力。我通常会先做一个小范围复现比如给SQL加上LIMIT 10先确认基础扫描逻辑如果发现加LIMIT和不加LIMIT行为差异巨大那往往是排序或临时表带来的问题。还有一个比较实用的技巧是看慢查询日志里的Rows_examined、Rows_sent、Rows_affected这几个字段的比值。如果Rows_examined是十万行Rows_sent只有一条说明这条SQL看着简单实际上扫描了很多行问题多半出在索引或者过滤条件下推上。3. 为什么简单的SQL也会踩坑3.1 索引失效的四个高频场景除了完全没有索引外更坑的是明明有索引却用不上。我总结过四个高频场景几乎每个项目里都能遇到。一是索引列上套函数或表达式。比如WHERE DATE(create_time) 2024-06-01即使create_time列有索引也无法走索引范围扫描因为每一行都要先算一次DATE()函数索引本身帮不上忙。正确的写法是create_time 2024-06-01 AND create_time 2024-06-02这样才能利用索引范围定位。有些数据库支持函数索引比如Oracle可以针对DATE(create_time)建函数索引但在MySQL里最普遍的情况还是直接失效写的时候就要避开。二是以%开头的前缀模糊查询。LIKE %abc%这种写法索引无法直接定位基本都会退化成全表扫描或全索引扫描。如果业务真的频繁需要包含匹配应该考虑全文索引、倒排索引或像Elasticsearch这类搜索引擎方案而不是试着在普通B树索引上做文章。三是隐式类型转换。字段是varchar类型查询参数却写成数字WHERE phone 13800138000MySQL会先把索引列做一次CAST索引随之失效。反过来字段是int类型参数写成字符串也会带来额外的转换成本。这条在MySQL、PostgreSQL、SQL Server里细节略有差异但多一步隐式转换总是亏的尤其在大表上很容易把毫秒级查询拖成秒级。四是字符集和排序规则不一致导致的跨表索引失效。两张表JOIN时如果关联字段一个是utf8mb4一个是utf8或者排序规则一个是utf8mb4_general_ci一个是utf8mb4_0900_ai_ci优化器无法直接用索引做连接可能会先做隐式转换然后放弃索引。这个问题在SQL Server里同样常见varchar和nvarchar互相JOIN时经常出现。我拿一张20万行的用户表实测过几种写法视觉效果非常直接。查询写法是否走索引扫描行数耗时WHERE phone 13800138000是1行约2msWHERE phone 13800138000否20万行约400msWHERE DATE(created_at) 2024-06-01否20万行约380ms一条看起来功能一模一样的查询因为写法差异耗时从2毫秒变成400毫秒。这也是为什么拿到慢SQL后一定要看执行计划——不能靠眼睛判断简单。3.2 统计信息过期让优化器做出错误判断优化器做决定依赖的是统计信息。统计信息过期的意思是表从10万行涨到了1000万行某些字段上的数据分布也彻底变了但元数据里记录的仍然是10万行、某列选择性极高的旧信息。此时优化器可能认为走某个索引只需要查几百行实际却扫了几百万行。最典型的表现是同样的SQL白天快得离谱某次全量更新数据之后的晚上突然变慢而且持续很久。处理方式分成两支。一是及时更新统计信息MySQL里执行ANALYZE TABLE t_orderPostgreSQL执行ANALYZE t_orderSQL Server执行UPDATE STATISTICS t_order。二是如果统计信息刚更新完还是慢那就说明优化器对字段选择性本身的判断就不符合真实分布要在SQL层面做保守设计。比如用FORCE INDEXMySQL或索引HINT强制走某个索引。但注意FORCE INDEX是双刃剑一旦数据分布又变强制索引反而可能更糟。我一直建议只在优化器误判的时候临时用常规做法还是要维护好统计信息、优化索引结构。这里还要提一个数据倾斜的问题。即使统计信息是最新的如果查询字段存在严重数据倾斜优化器依然会走错计划。比如订单状态字段90%都是已完成剩下10%是处理中。查询处理中状态时优化器按均匀分布估算认为这个条件要返回大量行于是选择全表扫描实际返回的行却很少。这种场景下统计信息再多也救不了需要HINT或改写条件来引导。3.3 参数嗅探SQL Server的坑MySQL也会遇到类似情况参数嗅探这个话题用SQL Server和Oracle的人肯定不陌生MySQL生态相对少见但预编译语句同样会有类似现象。简单解释一下存储过程或参数化查询第一次执行时SQL Server会根据当时的参数值生成执行计划并缓存起来后续不管传入什么参数值只要SQL文本匹配都复用同一个执行计划。如果第一次执行的参数碰巧是个低选择性的值生成了全表扫描计划后续即使传入一个高选择性的参数仍然沿用全表扫描计划执行时间就会异常。我实际遇到过的一个案例很有代表性。某个报表系统的分页查询按订单状态筛选。凌晨时分第一次有人查了全部订单status IS NOT NULL系统生成了一个扫描计划并缓存早上运营同学按待审核状态去筛选命中的行数其实只有几百条却一直沿用全表扫描计划单次查询超过2秒。排查时看缓存计划SQL文本一模一样只是参数值不同问题一下就清晰了。解决方案有几种在存储过程或SQL语句中使用WITH RECOMPILE让SQL Server每次重新编译牺牲编译成本换取计划准确性使用OPTIMIZE FOR让优化器按指定值做计划适合参数值分布不极端的情况把参数化SQL改成非参数化的动态SQL让不同值更容易获得不同计划但也会引入SQL注入风险需要自己权衡在关键场景使用计划向导Plan Guide固定执行计划相当于强制盖棺定论。MySQL里虽然不叫参数嗅探但Prepare Statement的预编译计划也会被复用。如果一条预编译SQL因为某个参数值生成了糟糕的计划后续一直很慢可以尝试在SQL里加一个不影响语义的注释让文本不一致从而让优化器重新生成计划。这种方式不优雅但偶尔真的管用。3.4 数据倾斜与数据增长让曾经的快SQL变慢还有一个很常见的场景某条SQL昨天还能接受今天突然慢到不可接受——不是代码变了不是数据库版本变了而是数据量涨了。十万行时全表扫描耗时200ms优化器判断走索引省下的时间不够排序成本于是选择全表扫描这是完全合理的等到三千万行时同样的优化器逻辑还试图用全表扫描耗时就从200ms变成了3秒。这种计划没变数据变了的情况靠索引优化往往能解决但也要重新审视数据库的容量规划和分区方案。如果查询条件连接了几个大表过滤后结果集不大但中间结果膨胀严重联合索引列的顺序就需要重新设计。我常建议在OLTP场景下尽量让查询条件里的每一个等值条件都对应联合索引的连续列范围条件只能放在等值列之后否则索引利用率会打折扣。很多人建了索引却觉得没效果很大一部分原因就在索引列顺序上。4. 慢SQL优化实操手册4.1 联合索引设计顺序决定成败我不会建议一上来就直接丢一个索引。下面是我实际使用的五步法你可以直接抄作业第一步确认慢查询日志里的完整SQL文本和运行参数不要裁剪不要只看ORM拼出来的片段第二步用EXPLAIN ANALYZE拿到真实步骤耗时确认是哪个环节扫描行数最多、耗时最大第三步查看表相关的统计信息和已有索引判断是缺失索引还是索引设计不佳第四步根据查询条件设计联合索引重点分析索引字段的顺序与查询选择性的匹配关系第五步上线索引之前用EXPLAIN预演执行计划上线后观察实际效果并结合慢查询日志阈值调优。关于联合索引顺序有一个非常实用的规则等值条件放最前面范围条件放后面如果排序字段能被索引覆盖再放到最后。举个例子SELECT order_no, amount, status FROM t_order WHERE user_id ? AND create_time BETWEEN ? AND ? ORDER BY amount DESC;联合索引建议是(user_id, create_time, amount)。原因是user_id是等值匹配必须放最前面create_time负责范围定位跟在等值条件后amount参与排序放在索引尾部可以避免回表和文件排序。如果把amount放前面索引利用率会明显下降。这里也得泼一盆冷水别为一条每秒只跑一次的报表SQL去新增索引。索引维护是有成本的表上索引越多写入就越慢。数据量大的表在做索引变更时还要特别注意锁表时间MySQL的常规DDL在部分版本会阻塞DML上线前务必确认执行方式。高并发大表建议用gh-ost或pt-online-schema-change这类在线变更工具在低峰期操作并且准备好回滚方案。4.2 改写SQL取舍和边界改写SQL是慢SQL优化里最容易被高估的环节。很多人喜欢炫技但普通写法的性价比其实最高。我总结出几条基本原则在查询结果可控的情况下使用SELECT具体列而不是SELECT *。很多人说数据库会自动裁剪列但一旦涉及覆盖索引和回表场景列裁剪不会自动让索引生效。只取需要的列既减少网络传输量也可能让覆盖索引直接命中。WHERE条件里尽量少出现OR。OR很容易让优化器放弃索引合并尤其是在历史版本的MySQL上。如果OR两边的分支都能走索引而且当前数据库版本已支持Index Merge那保持OR也没问题。判断标准永远只有一个实测对比而不是凭感觉。深翻页分页查询一定要避免。LIMIT 100000, 10这种写法MySQL会先把前100010行扫出来再丢弃。常见做法是改成基于游标的分页WHERE id 上一次位置 ORDER BY id LIMIT 10或者直接在查询里加一个范围条件减少扫描量。多表JOIN时尽量以过滤性最强的表作为驱动表并确保被驱动表连接字段上有索引。现在的优化器大多数时候能自动选对但统计信息不准时也会选错。MySQL里的STRAIGHT_JOIN、Oracle里的JOIN HINT可以在关键时刻兜底。改写SQL有一条底线必须保留原始语义。我见过为了性能把LEFT JOIN改成INNER JOIN结果某些没有关联记录的行从此查不出来业务报表数字对不上。任何改写上线前都应该用等价结果集和历史数据做一次比对确认输出完全一致再切换。4.3 缓存、分区、读写分离把慢SQL挪出核心链路如果索引也建了、SQL也改了还是慢就要考虑架构层面的方案。最简单的第一层是加缓存把热点查询结果放到Redis或本地缓存里TTL根据业务实时性要求设置。但缓存不是万能的缓存失效瞬间的缓存击穿往往会激增数据库压力需要配合互斥锁或提前预热机制。第二层是分区表。按日期对订单表做RANGE分区可以让查询自动裁剪到某个分区扫描量大幅下降。但分区不能替代索引分区裁剪之后的每个分区内部仍需要有合理索引。第三层是读写分离。慢SQL大多来自报表类或统计类查询把它们放到只读从库上即使CPU被打满也不会影响主库写入。读写分离虽然老生常谈但对于一条简单SQL超过1000ms这种偶发问题很实用偶尔的重查询不会挤占主库资源。还有一点值得单独说OLTP和OLAP的服务要区分。如果一条SQL需要几百毫秒才算正常那它就不应该出现在OLTP核心链路里。能把慢SQL从核心链路挪到分析型数据库或离线数仓也是一种优化。在业务上这往往比在代码里抠几个索引更有价值。5. 常见问题与排查技巧实录5.1 高发问题速查表把这些年遇到的高频问题整理成一张速查表遇到类似现象时可以对号入座。现象可能原因第一时间排查同一SQL时快时慢锁等待、网络抖动、缓存未命中查看锁等待信息与服务器负载加了索引仍不快没走新索引、统计信息过期、隐式转换EXPLAIN看key列实际使用的索引凌晨大数据量任务后变慢统计信息失效ANALYZE TABLE之后复测参数不同耗时差异巨大参数嗅探或数据倾斜尝试RECOMPILE或OPTIMIZE FOR查询条件简单但扫描行数多缺少过滤条件下的索引查看Rows_examined与Rows_sent比值这张表不能替代完整排查但能帮你节省不少定位时间。5.2 日常巡检工具与习惯日常工作中我离不开下面几类工具慢查询日志是第一步MySQL的slow_query_log、PostgreSQL的log_min_duration_statement、SQL Server的Slow Query报告都要物尽其用。分析方面Percona Toolkit里的pt-query-digest非常推荐它能解析MySQL慢日志把同类SQL聚合成报告按总耗时和平均耗时排序让你清楚知道该优先处理哪些Top N语句。监控大盘方面Prometheus Grafana mysqld_exporter是我常用的组合关注QPS、Threads_running、缓冲池命中率、慢查询数量等核心指标。数据库原生视图也可以直接用MySQL的performance_schema.events_statements_summary_by_digest、PostgreSQL的pg_stat_statements直接查看语句级别的累计耗时与执行次数比值非常方便。一个值得坚持的日常习惯是每周抽一点时间扫一遍慢查询日志只看排行前10的SQL检查每一条是否能用执行计划解释清楚它为什么慢。慢SQL优化不是一次性的救火行动而是一个持续收敛的过程。今天耗时1000ms的SQL不加干预数据量增长后可能就是2000ms、5000ms。等它变成事故再处理代价完全不一样。5.3 优化验证与回滚的实战细节任何索引变更、SQL改写、参数调整都应该有验证和回滚路径。索引建立之前先记录基线执行耗时、扫描行数、CPU消耗。建立之后用相同参数跑一遍耗时并观察一周的慢日志。如果一周内没有明显改善可以考虑删除索引不必碍于已经建了就留着的心态。预留索引每一刻都在产生写入成本。回滚方式也要提前想好。我在负责订单中心时有个习惯所有索引变更都放在低峰期预案里写明如果出现锁竞争加剧或写入变慢回滚直接删索引。有一次就是新索引上线后发现插入操作的锁竞争明显增加按预案回滚后恢复平稳。这个例子说明单条慢SQL优化不能只盯着查询侧写入侧的成本同样需要纳入考量。最后说一个我自己的小习惯每次优化完慢SQL我都会把优化前后的执行计划截图存下来标注好日期、版本和数据量。一年后再翻出来看会发现很多当时的灵机一动其实是在绕圈子也会看到数据量变化给同一个执行计划带来的巨大影响。慢SQL优化靠的不是灵感而是证据链。希望你下一次看到超过1000ms的告警时能比我第一次处理时淡定一些有理有据地把问题拆掉。
慢SQL优化实战：从执行计划到索引设计的完整排查指南
线上告警响起来的时候我盯着告警内容看了好几遍一条执行超过1000ms的SQL。打开慢查询日志找到那条语句看起来人畜无害——从一张一万多行的订单小表里按主键id取一条记录。第一反应和几乎所有同事都一样纳尼这也能磨蹭1秒但事后复盘真正有收获的不是SQL本身有多复杂而是排查过程中踩过的那些认知误区。这篇文章把我处理这类慢SQL的完整思路、定位方法、常见根因和优化动作整理出来希望能帮你在下一次遇到不可能慢的SQL时少走弯路。不管是MySQL、PostgreSQL还是SQL Server慢SQL优化的底层逻辑是共通的先确认时间从哪来再看执行计划最后才是改SQL或加索引。适合开发、运维以及所有需要跟数据库打交道的同学阅读。1. 别急着改SQL先搞清1000ms是怎么来的1.1 客户端耗时和执行耗时是两码事很多刚接触性能问题的同学把一条SQL执行超过1000ms直接理解为数据库执行耗时1000ms这是一个非常大的误区。从你点击查询按钮到看到结果中间要经历客户端连接池获取连接、SQL发送、数据库解析优化、执行、结果集网络传输、驱动或ORM框架对象映射这几个环节。假设SQL在数据库内部只跑了50ms但连接被某个未提交事务占住排队等了900ms客户端感知到的总耗时照样超过1秒。所以拿到告警之后第一步不是急着打开编辑器而是先问一句这个1000ms统计来自哪里不同来源对耗时的定义差别很大。MySQL慢查询日志里的Query_time是服务器端执行完SQL后的时间不包含客户端网络传输它最接近数据库自身的真实耗时APM工具比如SkyWalking、Zipkin上报的往往是全链路耗时包含网络、RPC调用、数据库驱动处理等数据库审计日志或Oracle AWR报告里的SQL耗时则包含数据库内部等待和执行的总时间。确认计时源之后再开始定位方向才不会跑偏。1.2 1秒阈值代表什么1秒是用户可以感知到卡顿的临界值。很多团队把MySQL的long_query_time设为1秒于是凡是超过1秒的SQL都会触发告警。这个阈值本身没问题但要明白它只代表这条SQL跑得不快该看看了不代表系统一定出了问题。不同业务场景对耗时的接受度差异极大一条后台报表的聚合查询可能需要5秒调度任务半夜跑没人会在意一条支付回调里的查库操作如果超过500ms用户的手指可能已经在屏幕上狂点了。所以当超过1000ms的告警出现在眼前先评估这条SQL处在什么链路里、影响范围多大。是用户同步感知的核心接口还是异步任务里的边缘查询如果核心接口出现慢SQL哪怕只有几百毫秒也要重视如果是凌晨的分析任务那更需要关注的可能是资源消耗而非单次耗时。慢查询日志的阈值只是信号最终是否要优化要结合业务诉求来判断。2. 排查一条慢SQL的完整路径2.1 用EXPLAIN ANALYZE抓出真实执行路径当确认一百毫秒级以上的耗时确实落在数据库执行阶段后第一个动作就是把真实执行计划抓出来。不是猜不是看SQL文本脑补而是拿到优化器实际选择的执行路径和每一步的耗时。在MySQL里对慢查询日志里的SQL做EXPLAIN ANALYZE能看到每一步的实际行数和实际耗时PostgreSQL同样支持EXPLAIN ANALYZESQL Server则用SET STATISTICS IO ON、SET STATISTICS TIME ON配合图形化执行计划Oracle有DBMS_XPLAN.DISPLAY_CURSOR可以直接查看对应SQL游标的执行计划。以MySQL为例我在处理慢SQL时通常会这样操作EXPLAIN ANALYZE SELECT id, user_name, order_no FROM t_order WHERE user_id 20240601 AND create_time 2024-06-01 00:00:00;重点关注EXPLAIN ANALYZE输出里的几个关键项目table和rows优化器预估扫描的行数如果预估行数和实际行数差太多说明统计信息可能有问题。filtered过滤比例越低越说明还有进一步利用索引或下推条件的空间。actual time每一步的实际耗时能定位最耗时的那一步。possible_keys和key是有哪些可用索引和实际用了哪个索引两者对比能直接看出索引有没有被选用。这里要专门提醒一句千万别只做EXPLAIN不做ANALYZE。EXPLAIN只输出优化器的计划但计划是它打算怎么跑EXPLAIN ANALYZE打印的是它实际跑了多久、扫描了多少行、每一步真实耗时多少两者结合才能真正判断慢在哪里。MySQL从8.0.18开始支持EXPLAIN ANALYZE8.0.21之后更稳定如果你还在用5.7可以用EXPLAIN FORMATJSON再加profile去分析。MariaDB的EXPLAIN ANALYZE也能直接使用。2.2 锁等待和会话状态往往藏在高耗时的背后执行计划看着很正常走了索引预估行数也不大可SQL还是很慢这种情况下多半不是查询本身的问题而是资源竞争。最常见的就是锁等待。在MySQL InnoDB引擎下可以执行下面两条SQL查看当前事务和锁等待情况SELECT * FROM information_schema.innodb_trx\G SELECT * FROM information_schema.innodb_lock_waits\G关注trx_state字段是不是LOCK WAIT以及blocking_trx_id是被哪个事务卡住了。还有一种很隐蔽的情况同一行数据被某个长事务持有排他锁查询在二级索引上已经找到了目标记录但要回表读取主键数据时必须等锁释放于是阻塞在那里。这种场景下光看执行计划永远找不到问题所在。SQL Server可以查sys.dm_exec_requests重点看wait_type、wait_time、blocking_session_id三个字段Oracle则看v$session里的event如果event是enq: TX - row lock contention那就是典型的行锁等待。除了锁等待数据库全局负载也要扫一眼。MySQL下可以用SHOW GLOBAL STATUS LIKE Threads_connected看连接数是否接近max_connections用SHOW ENGINE INNODB STATUS看buffer pool命中率和最近死锁信息。如果发现服务器CPU、磁盘IO已经接近饱和那这条SQL可能只是压垮骆驼的最后一根稻草真正的元凶是整体负载过高。这个问题非常常见单独改一条SQL往往解决不了得先做资源维度的降载。2.3 能不能复现决定排查效率处理慢SQL最怕的是拿静态SQL文本在开发库上纸上谈兵。你最好能在目标环境上复现但注意不要直接在高峰期生产库里乱执行。如果架构里有只读实例或从库放到那边验证是最稳妥的。复现时要注意两点一是传入参数必须相同。查询条件值的分布往往会直接影响执行计划比如user_id1可能只有一条记录user_id2下面挂了三千万条订单优化器对这两种情况可能选择完全不同的执行方式。二是数据分布和统计信息要和线上一致否则复现结果根本没有说服力。我通常会先做一个小范围复现比如给SQL加上LIMIT 10先确认基础扫描逻辑如果发现加LIMIT和不加LIMIT行为差异巨大那往往是排序或临时表带来的问题。还有一个比较实用的技巧是看慢查询日志里的Rows_examined、Rows_sent、Rows_affected这几个字段的比值。如果Rows_examined是十万行Rows_sent只有一条说明这条SQL看着简单实际上扫描了很多行问题多半出在索引或者过滤条件下推上。3. 为什么简单的SQL也会踩坑3.1 索引失效的四个高频场景除了完全没有索引外更坑的是明明有索引却用不上。我总结过四个高频场景几乎每个项目里都能遇到。一是索引列上套函数或表达式。比如WHERE DATE(create_time) 2024-06-01即使create_time列有索引也无法走索引范围扫描因为每一行都要先算一次DATE()函数索引本身帮不上忙。正确的写法是create_time 2024-06-01 AND create_time 2024-06-02这样才能利用索引范围定位。有些数据库支持函数索引比如Oracle可以针对DATE(create_time)建函数索引但在MySQL里最普遍的情况还是直接失效写的时候就要避开。二是以%开头的前缀模糊查询。LIKE %abc%这种写法索引无法直接定位基本都会退化成全表扫描或全索引扫描。如果业务真的频繁需要包含匹配应该考虑全文索引、倒排索引或像Elasticsearch这类搜索引擎方案而不是试着在普通B树索引上做文章。三是隐式类型转换。字段是varchar类型查询参数却写成数字WHERE phone 13800138000MySQL会先把索引列做一次CAST索引随之失效。反过来字段是int类型参数写成字符串也会带来额外的转换成本。这条在MySQL、PostgreSQL、SQL Server里细节略有差异但多一步隐式转换总是亏的尤其在大表上很容易把毫秒级查询拖成秒级。四是字符集和排序规则不一致导致的跨表索引失效。两张表JOIN时如果关联字段一个是utf8mb4一个是utf8或者排序规则一个是utf8mb4_general_ci一个是utf8mb4_0900_ai_ci优化器无法直接用索引做连接可能会先做隐式转换然后放弃索引。这个问题在SQL Server里同样常见varchar和nvarchar互相JOIN时经常出现。我拿一张20万行的用户表实测过几种写法视觉效果非常直接。查询写法是否走索引扫描行数耗时WHERE phone 13800138000是1行约2msWHERE phone 13800138000否20万行约400msWHERE DATE(created_at) 2024-06-01否20万行约380ms一条看起来功能一模一样的查询因为写法差异耗时从2毫秒变成400毫秒。这也是为什么拿到慢SQL后一定要看执行计划——不能靠眼睛判断简单。3.2 统计信息过期让优化器做出错误判断优化器做决定依赖的是统计信息。统计信息过期的意思是表从10万行涨到了1000万行某些字段上的数据分布也彻底变了但元数据里记录的仍然是10万行、某列选择性极高的旧信息。此时优化器可能认为走某个索引只需要查几百行实际却扫了几百万行。最典型的表现是同样的SQL白天快得离谱某次全量更新数据之后的晚上突然变慢而且持续很久。处理方式分成两支。一是及时更新统计信息MySQL里执行ANALYZE TABLE t_orderPostgreSQL执行ANALYZE t_orderSQL Server执行UPDATE STATISTICS t_order。二是如果统计信息刚更新完还是慢那就说明优化器对字段选择性本身的判断就不符合真实分布要在SQL层面做保守设计。比如用FORCE INDEXMySQL或索引HINT强制走某个索引。但注意FORCE INDEX是双刃剑一旦数据分布又变强制索引反而可能更糟。我一直建议只在优化器误判的时候临时用常规做法还是要维护好统计信息、优化索引结构。这里还要提一个数据倾斜的问题。即使统计信息是最新的如果查询字段存在严重数据倾斜优化器依然会走错计划。比如订单状态字段90%都是已完成剩下10%是处理中。查询处理中状态时优化器按均匀分布估算认为这个条件要返回大量行于是选择全表扫描实际返回的行却很少。这种场景下统计信息再多也救不了需要HINT或改写条件来引导。3.3 参数嗅探SQL Server的坑MySQL也会遇到类似情况参数嗅探这个话题用SQL Server和Oracle的人肯定不陌生MySQL生态相对少见但预编译语句同样会有类似现象。简单解释一下存储过程或参数化查询第一次执行时SQL Server会根据当时的参数值生成执行计划并缓存起来后续不管传入什么参数值只要SQL文本匹配都复用同一个执行计划。如果第一次执行的参数碰巧是个低选择性的值生成了全表扫描计划后续即使传入一个高选择性的参数仍然沿用全表扫描计划执行时间就会异常。我实际遇到过的一个案例很有代表性。某个报表系统的分页查询按订单状态筛选。凌晨时分第一次有人查了全部订单status IS NOT NULL系统生成了一个扫描计划并缓存早上运营同学按待审核状态去筛选命中的行数其实只有几百条却一直沿用全表扫描计划单次查询超过2秒。排查时看缓存计划SQL文本一模一样只是参数值不同问题一下就清晰了。解决方案有几种在存储过程或SQL语句中使用WITH RECOMPILE让SQL Server每次重新编译牺牲编译成本换取计划准确性使用OPTIMIZE FOR让优化器按指定值做计划适合参数值分布不极端的情况把参数化SQL改成非参数化的动态SQL让不同值更容易获得不同计划但也会引入SQL注入风险需要自己权衡在关键场景使用计划向导Plan Guide固定执行计划相当于强制盖棺定论。MySQL里虽然不叫参数嗅探但Prepare Statement的预编译计划也会被复用。如果一条预编译SQL因为某个参数值生成了糟糕的计划后续一直很慢可以尝试在SQL里加一个不影响语义的注释让文本不一致从而让优化器重新生成计划。这种方式不优雅但偶尔真的管用。3.4 数据倾斜与数据增长让曾经的快SQL变慢还有一个很常见的场景某条SQL昨天还能接受今天突然慢到不可接受——不是代码变了不是数据库版本变了而是数据量涨了。十万行时全表扫描耗时200ms优化器判断走索引省下的时间不够排序成本于是选择全表扫描这是完全合理的等到三千万行时同样的优化器逻辑还试图用全表扫描耗时就从200ms变成了3秒。这种计划没变数据变了的情况靠索引优化往往能解决但也要重新审视数据库的容量规划和分区方案。如果查询条件连接了几个大表过滤后结果集不大但中间结果膨胀严重联合索引列的顺序就需要重新设计。我常建议在OLTP场景下尽量让查询条件里的每一个等值条件都对应联合索引的连续列范围条件只能放在等值列之后否则索引利用率会打折扣。很多人建了索引却觉得没效果很大一部分原因就在索引列顺序上。4. 慢SQL优化实操手册4.1 联合索引设计顺序决定成败我不会建议一上来就直接丢一个索引。下面是我实际使用的五步法你可以直接抄作业第一步确认慢查询日志里的完整SQL文本和运行参数不要裁剪不要只看ORM拼出来的片段第二步用EXPLAIN ANALYZE拿到真实步骤耗时确认是哪个环节扫描行数最多、耗时最大第三步查看表相关的统计信息和已有索引判断是缺失索引还是索引设计不佳第四步根据查询条件设计联合索引重点分析索引字段的顺序与查询选择性的匹配关系第五步上线索引之前用EXPLAIN预演执行计划上线后观察实际效果并结合慢查询日志阈值调优。关于联合索引顺序有一个非常实用的规则等值条件放最前面范围条件放后面如果排序字段能被索引覆盖再放到最后。举个例子SELECT order_no, amount, status FROM t_order WHERE user_id ? AND create_time BETWEEN ? AND ? ORDER BY amount DESC;联合索引建议是(user_id, create_time, amount)。原因是user_id是等值匹配必须放最前面create_time负责范围定位跟在等值条件后amount参与排序放在索引尾部可以避免回表和文件排序。如果把amount放前面索引利用率会明显下降。这里也得泼一盆冷水别为一条每秒只跑一次的报表SQL去新增索引。索引维护是有成本的表上索引越多写入就越慢。数据量大的表在做索引变更时还要特别注意锁表时间MySQL的常规DDL在部分版本会阻塞DML上线前务必确认执行方式。高并发大表建议用gh-ost或pt-online-schema-change这类在线变更工具在低峰期操作并且准备好回滚方案。4.2 改写SQL取舍和边界改写SQL是慢SQL优化里最容易被高估的环节。很多人喜欢炫技但普通写法的性价比其实最高。我总结出几条基本原则在查询结果可控的情况下使用SELECT具体列而不是SELECT *。很多人说数据库会自动裁剪列但一旦涉及覆盖索引和回表场景列裁剪不会自动让索引生效。只取需要的列既减少网络传输量也可能让覆盖索引直接命中。WHERE条件里尽量少出现OR。OR很容易让优化器放弃索引合并尤其是在历史版本的MySQL上。如果OR两边的分支都能走索引而且当前数据库版本已支持Index Merge那保持OR也没问题。判断标准永远只有一个实测对比而不是凭感觉。深翻页分页查询一定要避免。LIMIT 100000, 10这种写法MySQL会先把前100010行扫出来再丢弃。常见做法是改成基于游标的分页WHERE id 上一次位置 ORDER BY id LIMIT 10或者直接在查询里加一个范围条件减少扫描量。多表JOIN时尽量以过滤性最强的表作为驱动表并确保被驱动表连接字段上有索引。现在的优化器大多数时候能自动选对但统计信息不准时也会选错。MySQL里的STRAIGHT_JOIN、Oracle里的JOIN HINT可以在关键时刻兜底。改写SQL有一条底线必须保留原始语义。我见过为了性能把LEFT JOIN改成INNER JOIN结果某些没有关联记录的行从此查不出来业务报表数字对不上。任何改写上线前都应该用等价结果集和历史数据做一次比对确认输出完全一致再切换。4.3 缓存、分区、读写分离把慢SQL挪出核心链路如果索引也建了、SQL也改了还是慢就要考虑架构层面的方案。最简单的第一层是加缓存把热点查询结果放到Redis或本地缓存里TTL根据业务实时性要求设置。但缓存不是万能的缓存失效瞬间的缓存击穿往往会激增数据库压力需要配合互斥锁或提前预热机制。第二层是分区表。按日期对订单表做RANGE分区可以让查询自动裁剪到某个分区扫描量大幅下降。但分区不能替代索引分区裁剪之后的每个分区内部仍需要有合理索引。第三层是读写分离。慢SQL大多来自报表类或统计类查询把它们放到只读从库上即使CPU被打满也不会影响主库写入。读写分离虽然老生常谈但对于一条简单SQL超过1000ms这种偶发问题很实用偶尔的重查询不会挤占主库资源。还有一点值得单独说OLTP和OLAP的服务要区分。如果一条SQL需要几百毫秒才算正常那它就不应该出现在OLTP核心链路里。能把慢SQL从核心链路挪到分析型数据库或离线数仓也是一种优化。在业务上这往往比在代码里抠几个索引更有价值。5. 常见问题与排查技巧实录5.1 高发问题速查表把这些年遇到的高频问题整理成一张速查表遇到类似现象时可以对号入座。现象可能原因第一时间排查同一SQL时快时慢锁等待、网络抖动、缓存未命中查看锁等待信息与服务器负载加了索引仍不快没走新索引、统计信息过期、隐式转换EXPLAIN看key列实际使用的索引凌晨大数据量任务后变慢统计信息失效ANALYZE TABLE之后复测参数不同耗时差异巨大参数嗅探或数据倾斜尝试RECOMPILE或OPTIMIZE FOR查询条件简单但扫描行数多缺少过滤条件下的索引查看Rows_examined与Rows_sent比值这张表不能替代完整排查但能帮你节省不少定位时间。5.2 日常巡检工具与习惯日常工作中我离不开下面几类工具慢查询日志是第一步MySQL的slow_query_log、PostgreSQL的log_min_duration_statement、SQL Server的Slow Query报告都要物尽其用。分析方面Percona Toolkit里的pt-query-digest非常推荐它能解析MySQL慢日志把同类SQL聚合成报告按总耗时和平均耗时排序让你清楚知道该优先处理哪些Top N语句。监控大盘方面Prometheus Grafana mysqld_exporter是我常用的组合关注QPS、Threads_running、缓冲池命中率、慢查询数量等核心指标。数据库原生视图也可以直接用MySQL的performance_schema.events_statements_summary_by_digest、PostgreSQL的pg_stat_statements直接查看语句级别的累计耗时与执行次数比值非常方便。一个值得坚持的日常习惯是每周抽一点时间扫一遍慢查询日志只看排行前10的SQL检查每一条是否能用执行计划解释清楚它为什么慢。慢SQL优化不是一次性的救火行动而是一个持续收敛的过程。今天耗时1000ms的SQL不加干预数据量增长后可能就是2000ms、5000ms。等它变成事故再处理代价完全不一样。5.3 优化验证与回滚的实战细节任何索引变更、SQL改写、参数调整都应该有验证和回滚路径。索引建立之前先记录基线执行耗时、扫描行数、CPU消耗。建立之后用相同参数跑一遍耗时并观察一周的慢日志。如果一周内没有明显改善可以考虑删除索引不必碍于已经建了就留着的心态。预留索引每一刻都在产生写入成本。回滚方式也要提前想好。我在负责订单中心时有个习惯所有索引变更都放在低峰期预案里写明如果出现锁竞争加剧或写入变慢回滚直接删索引。有一次就是新索引上线后发现插入操作的锁竞争明显增加按预案回滚后恢复平稳。这个例子说明单条慢SQL优化不能只盯着查询侧写入侧的成本同样需要纳入考量。最后说一个我自己的小习惯每次优化完慢SQL我都会把优化前后的执行计划截图存下来标注好日期、版本和数据量。一年后再翻出来看会发现很多当时的灵机一动其实是在绕圈子也会看到数据量变化给同一个执行计划带来的巨大影响。慢SQL优化靠的不是灵感而是证据链。希望你下一次看到超过1000ms的告警时能比我第一次处理时淡定一些有理有据地把问题拆掉。
