做后台开发和数据库维护这些年我几乎每天都会和慢查询打交道。所谓的慢查询就是执行耗时超过你容忍阈值的 SQL 语句它们会被数据库单独记录在慢查询日志里等着你去处理。定位慢查询、分析 SQL 执行缓慢的原因是数据库性能优化里最基础也最实用的一环。这篇文章会从怎么把慢 SQL 捞出来、怎么看懂它的执行计划再到锁等待和系统资源层面的排查完整梳理一遍我的实操经验。适合刚接触数据库调优的后端开发、运维同学也适合已经处理过一些慢 SQL 但还缺一套系统思路的同行。1. 定位慢查询先让数据库把慢 SQL“说出来”1.1 开启慢查询日志的配置细节慢查询日志是 MySQL 默认提供的诊断工具开启之后执行时间超过阈值的 SQL 会自动落到日志文件里。很多新手遇到线上 SQL 变慢第一反应是打开数据库客户端手动执行几条 SQL 凭感觉猜这种做法既不系统也不可复现。正确思路是先让数据库自己开口把所有超时的 SQL 全部记录下来。MySQL 中慢查询日志默认是关闭的先用下面的命令确认当前状态SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE slow_query_log_file;如果slow_query_log是OFF可以用下面的方式临时开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;这里有个很容易忽略的细节long_query_time的单位是秒支持小数。线上环境我一般建议从1开始调也就是 1 秒以上的 SQL 全记录。如果你的业务本身压力很大、日志量惊人可以逐步调到2或者5但不建议一开始就设成0.1这种值否则慢日志文件会被每秒执行的小查询刷爆。还需要确认一个参数min_examined_row_limit。这个参数表示扫描行数达到多少才记录默认是0。如果你的库里有大量小表全表扫描但执行很快的查询你可能会在慢日志里看到一堆“没有价值”的记录。把它适当调大比如1000能过滤掉那些扫描行数太少的噪声记录让慢日志更有参考价值。另外补充一个运维细节。MySQL 的慢日志默认输出到文件但也支持写入mysql.slow_log表通过log_output TABLE设置。文件方式效率更高、解析更方便我长期使用文件方式不推荐把日志写到表里因为表本身也需要写入反而会影响数据库性能。1.2 真正读懂慢日志里的每一行开启慢日志之后你需要能看懂它记录的内容。一条典型的慢日志长这样# Query_time: 4.213284 Lock_time: 0.000122 Rows_sent: 10 Rows_examined: 1234567 SET timestamp1710000000; SELECT * FROM orders WHERE user_id 123 AND created_at 2024-01-01;四个关键字段Query_timeSQL 从开始执行到返回的总耗时单位秒。这是你判断是否超时的直接依据。Lock_time等待获取锁的时间。如果这个值很大说明 SQL 卡在锁等待上而不是真正在执行计算排查方向要转向锁问题。Rows_sent实际返回给客户端的行数。Rows_examined执行过程中扫描过的行数。我最看重的是Rows_examined和Rows_sent的比例。像日志里这条扫描了 123 万行只返回 10 行典型的大范围扫描撞上过滤条件索引设计有问题的可能性极高。反过来如果Rows_examined本身不大但Query_time却很高那就要考虑锁等待、网络延迟、CPU 资源竞争等外部因素。慢日志还记录了 SQL 执行时的timestamp你可以通过FROM_UNIXTIME()把它转换成可读时间用来判断慢 SQL 是否集中在业务高峰时段。2. 日志到手之后快速找出最值得优化的 SQL2.1 用自带的 mysqldumpslow 做初步聚合慢日志一旦开起来文件很快就变大里面可能躺着几千条记录。这时候如果一条条去看效率太低。MySQL 自带的mysqldumpslow工具就是用来做初步汇总的。它的基本用法是mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log参数含义-s t按查询耗时排序-s c是按出现次数排序-s l按锁定时间排序。-t 10只看前 10 条。mysqldumpslow会把结构相似、只有参数不同的 SQL 聚合到一起比如user_id 123和user_id 456会被视为同一条。这样做的意义在于你关注的不应该是某一个具体的慢查询而是一类慢查询。如果同一类 SQL 每天晚上被调用几千次每次慢 2 秒它带来的整体影响远远大于一条偶尔跑了 30 秒的 SQL。2.2 用 pt-query-digest 做深度剖析mysqldumpslow解决的是“哪类 SQL 最慢”的问题但如果你想看更详细的统计分布比如响应时间的百分位数、SQL 指纹、总耗时占比那就需要pt-query-digest。这是 Percona Toolkit 里的明星工具几乎是我定位慢查询必用的东西。安装 Percona Toolkit 之后一行命令就能生成分析报告pt-query-digest /var/log/mysql/mysql-slow.log slow_analysis.txt打开生成的报告你会看到类似下面的信息# Profile # Rank Query ID Response time Calls R/Call V/M Item # # 1 0x1234... 2356.2345 12.3% 452 5.2134 0.01 SELECT orders # 2 0x5678... 1834.1234 9.6% 128 14.3290 0.02 SELECT users它按照“总响应时间占比”排序排名靠前的就是真正消耗数据库资源的元凶。再往下翻每个 SQL 指纹的详细报告里还有ts耗时分布、Rows_sent、Rows_examined等统计能辅助你判断优化优先级。我的实际建议是维护一个固定的慢日志分析任务比如每天凌晨把前一天的慢日志跑一遍 pt-query-digest然后把 Top 10 发给相关开发。这样慢查询就不再是“出事才查”的被动状态而是形成一个可持续跟踪的指标。实践中我发现很多慢 SQL 的累积影响比单次报警要严重得多用这类工具做聚合分析才能真正看到全局。3. 分析慢 SQL 的核心动作看懂执行计划3.1 EXPLAIN 结果逐列拆解拿到一条待优化的慢 SQL 之后第一个动作一定是看它的执行计划。MySQL 里执行计划用EXPLAIN查看EXPLAIN SELECT * FROM orders WHERE user_id 123 AND created_at 2024-01-01;输出结果的几个核心列我逐个解释一下。type列是访问类型它直接告诉你 MySQL 是怎么找数据的。从好到差大致是systemconsteq_refrefrangeindexALL。看到ALL就意味着全表扫描这是最需要警惕的index表示扫描了整个索引树也不算好但如果只是覆盖索引查询有时候还能接受range是范围扫描级别中等ref和eq_ref属于精准匹配性能通常不错。key列表示实际用到的索引如果为NULL说明没走索引。key_len表示索引使用的字节数对联合索引来说它能看出实际用到了索引的哪几列。rows是 MySQL 预估需要扫描的行数这个数字越大执行代价越高。Extra列则提供额外信息比如Using filesort表示文件排序、Using temporary表示使用了临时表、Using where表示在存储引擎层拿到的数据又做了过滤。我之前遇到过一出事就急着加索引的团队结果加了索引 SQL 还是慢最后发现是EXPLAIN里type显示为ref但rows依然几十万原因是联合索引的字段顺序设计不合理。所以要记住执行计划不是只看有没有索引而是要看索引的字段顺序、扫描行数和访问方式。3.2 用 EXPLAIN ANALYZE 验证真实执行EXPLAIN展示的是优化器基于统计信息估算出来的方案rows是猜测值实际执行可能有偏差。MySQL 8.0.18 及以上版本提供了EXPLAIN ANALYZE可以直接看到真实执行的行数和耗时EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 123 AND created_at 2024-01-01;输出是一个树状结构里面包含每个算子的真实耗时、真实行数、循环次数等。比如actual time2.341..4.125 rows10 loops1中的rows10是真实扫描到的行数actual time是实际耗时单位毫秒。这里要特别提醒EXPLAIN ANALYZE会真实执行这条 SQL。它对你的 SELECT 查询是安全的但如果对大数据量查询执行它真的会跑完整个查询占用数据库资源所以生产环境使用要慎重。如果一条 SQL 本身要跑 10 分钟EXPLAIN ANALYZE也会真的让它跑 10 分钟。我在生产环境一般用两次第一次用普通EXPLAIN看执行计划是否合理确定没有明显问题后再对关键算子做一次EXPLAIN ANALYZE确认估算和实际的差异。4. 慢 SQL 背后的典型场景与优化方向4.1 索引失效隐式类型转换和函数操作看执行计划时最常遇到的情况是明明字段上有索引type却是ALL。这种情况十有八九是索引失效而索引失效最常见的原因是隐式类型转换和函数操作。举个例子SELECT * FROM users WHERE phone 13800138000;如果phone字段是VARCHAR类型而你用数字去比较MySQL 会把phone字段隐式转换成数字再比较相当于对索引列做了一次函数操作索引自然失效。解决方法是把参数改成字符串SELECT * FROM users WHERE phone 13800138000;类似的情况还有在索引列上使用函数比如SELECT * FROM orders WHERE DATE(created_at) 2024-01-01;只要在索引列上套了DATE()函数索引基本用不上。正确的写法是把它改成范围条件SELECT * FROM orders WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;这个改写保留了索引的使用能力同时避免了函数操作。这类问题的排查思路很简单看到EXPLAIN里key为NULL先检查 SQL 的 WHERE 条件里有没有对索引列做运算。还有一点容易被忽略LIKE %keyword这种前置通配符也会导致索引失效LIKE keyword%则能走范围扫描。4.2 深分页问题LIMIT 越翻越慢分页查询越到后面越慢几乎是每个业务系统都会遇到的问题。经典 SQL 长这样SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;为什么慢因为 MySQL 需要先找到第 1000020 行之前的全部数据把前 100 万行全部扫描出来再丢弃最后只返回 20 行。数据量小的时候问题不明显一旦表里有了几百万上千万行这种写法就会让Rows_examined爆炸式增长。一种常用的优化方案是延迟关联。先只查出主键或id再通过JOIN回到原表取完整数据SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20) tmp ON o.id tmp.id;内层子查询只查id如果id是主键ORDER BY created_at配合索引能够快速定位目标行外层再回表取数据整体扫描行数会大幅下降。如果业务允许更彻底的方案是游标分页也就是记住上一次查询的最后一条位置避免使用LIMIT的大 offsetSELECT * FROM orders WHERE created_at 上次返回的最晚时间 ORDER BY created_at DESC LIMIT 20;这种玩法对用户体验来说需要配合下拉加载或者“上一页/下一页”交互但对数据库最友好。实际项目中我经常把延迟关联作为立即可用的快速优化手段把游标分页作为需要产品配合的长期方案。4.3 大表 JOIN 和临时表多表 JOIN 出现性能问题的概率很高尤其是大表 JOIN 小表、大表 JOIN 大表以及带GROUP BY、ORDER BY、DISTINCT的查询。先看一个常见问题Extra列出现Using join buffer说明 MySQL 需要在内存里缓冲一张表的数据再跟另一张表匹配这通常意味着没有走索引关联。优化方向是给关联字段建索引并且尽量让驱动表外层表是小表被驱动表内层表用索引查找。我见过一个实际案例两张表各 50 万行数据JOIN 之后耗时 12 秒EXPLAIN显示type是ALL因为被驱动表orders.user_id上没有索引。后来给user_id建了普通索引同一查询降到 0.2 秒。这个例子说明JOIN 慢的时候首要排查点不是 SQL 写法而是关联字段的索引是否存在。还需要警惕一个隐形问题GROUP BY和ORDER BY如果出现在不同的索引上MySQL 可能需要先排序再分组Extra里出现Using temporary和Using filesort这时候查询会变得很慢。解决办法通常是调整索引字段顺序让排序和分组的字段都包含在同一个联合索引里如果确实无法避免那就考虑用冗余表或者把统计结果放到应用层缓存而不是每次都现场计算。Using temporary还有一个隐蔽的来源SELECT DISTINCT和UNION。如果对几千行数据做 DISTINCT临时表很快但如果是几百万行内存临时表装不下就会落到磁盘上性能断崖式下降。看到Using temporary时去检查tmp_table_size和max_heap_table_size配置只是治标把这种重型去重操作移到应用层往往更加有效。5. 别忘了锁等待和系统资源层面的干扰5.1 从 processlist 和 innodb_trx 定位锁问题有时候 SQL 本身设计没问题、索引也齐全但就是慢。这时候要怀疑是不是锁等待导致的阻塞。一条 SQL 写完了可能一直在等另一个事务释放锁Query_time高但Lock_time占了大头。慢日志里的Lock_time就是信号。先通过SHOW FULL PROCESSLIST看当前连接状态SHOW FULL PROCESSLIST;如果看到大量连接处于Waiting for table metadata lock或Waiting for next key lock之类的状态基本可以确认锁等待。在 InnoDB 引擎下更精确的手段是查询information_schema.innodb_trx表找到当前所有活跃事务SELECT trx_id, trx_state, trx_started, trx_query, trx_mysql_thread_id FROM information_schema.innodb_trx\G配合sys.innodb_lock_waits视图能直接看到谁在等锁、谁持有了锁SELECT * FROM sys.innodb_lock_waits\G这个视图会返回waiting_pid等待线程和blocking_pid阻塞线程。通过blocking_pid去SHOW FULL PROCESSLIST里找到持锁会话再结合trx_started看它跑了多久。如果它已经执行了很久还没提交那就需要和业务方确认看能不能尽快提交或回滚事务。这里有一个很常见的误区很多人以为死锁才会导致慢查询。实际上死锁只是锁问题的极端情况更多的是长事务持锁不释放导致后面所有相关 SQL 排队等待。这类慢查询用执行计划看不出来必须从锁等待的角度去排查。5.2 系统资源、统计信息与执行计划的偏差在排除了 SQL 写法、索引、锁问题之后还要把眼光放到数据库所在的机器上。慢查询有时是资源竞争引发的。top看到 CPU 使用率飙高可能是大量排序和 join 操作iostat -x看到磁盘util接近 100%可能是内存不足导致频繁磁盘读写比如临时表落盘、InnoDB buffer pool 太小导致频繁读盘。我遇到过最典型的案例是某条统计 SQL 平时执行 200 毫秒某段时间突然变成 8 秒EXPLAIN 看执行计划完全正常type是refrows也合理。后来发现是同一时间上了个大报表任务把磁盘 IO 打满了。排查该类问题不需要高深手段先top看整体负载再用iostat看磁盘压力往往几秒钟就能定位。另一个容易被忽略的因素是统计信息过期。MySQL 优化器选择索引依赖表的统计信息如果表数据量在短时间内剧烈变化统计信息没有及时更新优化器可能选错索引。这时候即使 SQL 没有变化执行计划也可能变得很差。解决方法是定期执行ANALYZE TABLE orders;如果不想手工执行可以在业务低峰期做一个定时任务。实战中大表在执行计划突变、前一天还好好的情况下排查思路里应该加上“统计信息是否过期”这一项。先用SHOW INDEX FROM orders看Cardinality数值明显偏低或者和实际行数差距很大时就执行一次ANALYZE TABLE再重新EXPLAIN看执行计划有没有变化。6. 常见问题速查表与排查经验6.1 慢查询排查速查表我把日常工作中最常遇到的慢查询现象整理成一张表方便对照排查。现象可能原因排查方式解决方案Rows_examined 很大但 Rows_sent 很小缺少索引或索引失效EXPLAIN 看 type、key、rows建设联合索引改写 SQL 避免函数和隐式转换数据量不大却频繁全表扫描统计信息过期、索引未被选用SHOW INDEX 看 CardinalityANALYZE TABLE必要时 FORCE INDEX查询卡住State 显示锁等待行锁/表锁、长事务持锁sys.innodb_lock_waits排查持锁事务尽快提交或回滚分页越翻越慢深分页 LIMIT offset 过大查看 SQL 的 LIMIT 参数延迟关联、游标分页CPU 突然飙高大量排序/hash join/重复扫描pt-query-digest 聚合分析优化索引、改写 SQL、减少重复查询磁盘 IO 频繁buffer pool 过小、临时表落盘iostat -x、SHOW ENGINE INNODB STATUS调整 innodb_buffer_pool_size避免大排序同一个 SQL 以前快现在慢统计信息过期/数据分布变化EXPLAIN 对比历史执行计划ANALYZE TABLE重新优化索引遇到慢 SQL 时我会从“日志记录 → 执行计划 → 锁等待 → 系统资源”四个层面依次排查。大多数情况下问题会在第一层和第二层之间暴露如果前两层都正常再去看锁和系统资源基本不会走弯路。6.2 实操中容易踩的几个坑先说说慢日志参数的坑。很多人把long_query_time设得特别小比如0.1导致慢日志文件疯长一天几个 GB结果真正重要的慢 SQL 反而被淹没了。我建议线上环境从1秒起步根据日志量和业务压力逐步调整。还有log_queries_not_using_indexes这个参数它的本意是记录没走索引的查询但实际效果是大量小表全表扫描的查询都会刷进来日志爆炸速度比long_query_time0.1还快除非你有明确的监控告警系统否则不要轻易打开。再说说执行计划的坑。EXPLAIN里的rows是优化器的估算值不代表真实扫描行数。如果两条同样的 SQL 因为统计信息不同rows可能差一个数量级。所以我在判断一个索引是否发挥作用时会同时关注type和rows而不会只盯着其中一个。更严谨的做法是结合EXPLAIN ANALYZE验证真实数据。还有关于索引的惯性思维发现 SQL 慢就加索引这个做法本身没错但要注意索引不是越多越好。每个索引都会增加写入开销还会占用额外的存储空间。我见过一张表被加了十几个索引写入性能严重下降最后不得不清理。优化慢查询时优先通过改写 SQL、调整索引字段顺序来解决而不是无脑加新索引。一条 SQL 慢很多时候是因为现有索引用不上并不是缺少索引。最后分享一个运维习惯生产环境变更之前先把 SQL 的EXPLAIN截图留档变更之后再做一次对比。这个习惯帮我避免了很多“改了配置反而更慢”的问题。慢查询优化不是一次性工作而是一个持续跟踪的过程。每个季度把慢日志重新分析一遍把 Top 10 拿出来过一遍你会发现数据库的性能问题大多是周期性出现的解决一批还会来一批。把这套流程跑起来慢查询就不会再是让你半夜上线的元凶。
MySQL慢查询排查与优化：从日志到执行计划的完整实践
做后台开发和数据库维护这些年我几乎每天都会和慢查询打交道。所谓的慢查询就是执行耗时超过你容忍阈值的 SQL 语句它们会被数据库单独记录在慢查询日志里等着你去处理。定位慢查询、分析 SQL 执行缓慢的原因是数据库性能优化里最基础也最实用的一环。这篇文章会从怎么把慢 SQL 捞出来、怎么看懂它的执行计划再到锁等待和系统资源层面的排查完整梳理一遍我的实操经验。适合刚接触数据库调优的后端开发、运维同学也适合已经处理过一些慢 SQL 但还缺一套系统思路的同行。1. 定位慢查询先让数据库把慢 SQL“说出来”1.1 开启慢查询日志的配置细节慢查询日志是 MySQL 默认提供的诊断工具开启之后执行时间超过阈值的 SQL 会自动落到日志文件里。很多新手遇到线上 SQL 变慢第一反应是打开数据库客户端手动执行几条 SQL 凭感觉猜这种做法既不系统也不可复现。正确思路是先让数据库自己开口把所有超时的 SQL 全部记录下来。MySQL 中慢查询日志默认是关闭的先用下面的命令确认当前状态SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE slow_query_log_file;如果slow_query_log是OFF可以用下面的方式临时开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;这里有个很容易忽略的细节long_query_time的单位是秒支持小数。线上环境我一般建议从1开始调也就是 1 秒以上的 SQL 全记录。如果你的业务本身压力很大、日志量惊人可以逐步调到2或者5但不建议一开始就设成0.1这种值否则慢日志文件会被每秒执行的小查询刷爆。还需要确认一个参数min_examined_row_limit。这个参数表示扫描行数达到多少才记录默认是0。如果你的库里有大量小表全表扫描但执行很快的查询你可能会在慢日志里看到一堆“没有价值”的记录。把它适当调大比如1000能过滤掉那些扫描行数太少的噪声记录让慢日志更有参考价值。另外补充一个运维细节。MySQL 的慢日志默认输出到文件但也支持写入mysql.slow_log表通过log_output TABLE设置。文件方式效率更高、解析更方便我长期使用文件方式不推荐把日志写到表里因为表本身也需要写入反而会影响数据库性能。1.2 真正读懂慢日志里的每一行开启慢日志之后你需要能看懂它记录的内容。一条典型的慢日志长这样# Query_time: 4.213284 Lock_time: 0.000122 Rows_sent: 10 Rows_examined: 1234567 SET timestamp1710000000; SELECT * FROM orders WHERE user_id 123 AND created_at 2024-01-01;四个关键字段Query_timeSQL 从开始执行到返回的总耗时单位秒。这是你判断是否超时的直接依据。Lock_time等待获取锁的时间。如果这个值很大说明 SQL 卡在锁等待上而不是真正在执行计算排查方向要转向锁问题。Rows_sent实际返回给客户端的行数。Rows_examined执行过程中扫描过的行数。我最看重的是Rows_examined和Rows_sent的比例。像日志里这条扫描了 123 万行只返回 10 行典型的大范围扫描撞上过滤条件索引设计有问题的可能性极高。反过来如果Rows_examined本身不大但Query_time却很高那就要考虑锁等待、网络延迟、CPU 资源竞争等外部因素。慢日志还记录了 SQL 执行时的timestamp你可以通过FROM_UNIXTIME()把它转换成可读时间用来判断慢 SQL 是否集中在业务高峰时段。2. 日志到手之后快速找出最值得优化的 SQL2.1 用自带的 mysqldumpslow 做初步聚合慢日志一旦开起来文件很快就变大里面可能躺着几千条记录。这时候如果一条条去看效率太低。MySQL 自带的mysqldumpslow工具就是用来做初步汇总的。它的基本用法是mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log参数含义-s t按查询耗时排序-s c是按出现次数排序-s l按锁定时间排序。-t 10只看前 10 条。mysqldumpslow会把结构相似、只有参数不同的 SQL 聚合到一起比如user_id 123和user_id 456会被视为同一条。这样做的意义在于你关注的不应该是某一个具体的慢查询而是一类慢查询。如果同一类 SQL 每天晚上被调用几千次每次慢 2 秒它带来的整体影响远远大于一条偶尔跑了 30 秒的 SQL。2.2 用 pt-query-digest 做深度剖析mysqldumpslow解决的是“哪类 SQL 最慢”的问题但如果你想看更详细的统计分布比如响应时间的百分位数、SQL 指纹、总耗时占比那就需要pt-query-digest。这是 Percona Toolkit 里的明星工具几乎是我定位慢查询必用的东西。安装 Percona Toolkit 之后一行命令就能生成分析报告pt-query-digest /var/log/mysql/mysql-slow.log slow_analysis.txt打开生成的报告你会看到类似下面的信息# Profile # Rank Query ID Response time Calls R/Call V/M Item # # 1 0x1234... 2356.2345 12.3% 452 5.2134 0.01 SELECT orders # 2 0x5678... 1834.1234 9.6% 128 14.3290 0.02 SELECT users它按照“总响应时间占比”排序排名靠前的就是真正消耗数据库资源的元凶。再往下翻每个 SQL 指纹的详细报告里还有ts耗时分布、Rows_sent、Rows_examined等统计能辅助你判断优化优先级。我的实际建议是维护一个固定的慢日志分析任务比如每天凌晨把前一天的慢日志跑一遍 pt-query-digest然后把 Top 10 发给相关开发。这样慢查询就不再是“出事才查”的被动状态而是形成一个可持续跟踪的指标。实践中我发现很多慢 SQL 的累积影响比单次报警要严重得多用这类工具做聚合分析才能真正看到全局。3. 分析慢 SQL 的核心动作看懂执行计划3.1 EXPLAIN 结果逐列拆解拿到一条待优化的慢 SQL 之后第一个动作一定是看它的执行计划。MySQL 里执行计划用EXPLAIN查看EXPLAIN SELECT * FROM orders WHERE user_id 123 AND created_at 2024-01-01;输出结果的几个核心列我逐个解释一下。type列是访问类型它直接告诉你 MySQL 是怎么找数据的。从好到差大致是systemconsteq_refrefrangeindexALL。看到ALL就意味着全表扫描这是最需要警惕的index表示扫描了整个索引树也不算好但如果只是覆盖索引查询有时候还能接受range是范围扫描级别中等ref和eq_ref属于精准匹配性能通常不错。key列表示实际用到的索引如果为NULL说明没走索引。key_len表示索引使用的字节数对联合索引来说它能看出实际用到了索引的哪几列。rows是 MySQL 预估需要扫描的行数这个数字越大执行代价越高。Extra列则提供额外信息比如Using filesort表示文件排序、Using temporary表示使用了临时表、Using where表示在存储引擎层拿到的数据又做了过滤。我之前遇到过一出事就急着加索引的团队结果加了索引 SQL 还是慢最后发现是EXPLAIN里type显示为ref但rows依然几十万原因是联合索引的字段顺序设计不合理。所以要记住执行计划不是只看有没有索引而是要看索引的字段顺序、扫描行数和访问方式。3.2 用 EXPLAIN ANALYZE 验证真实执行EXPLAIN展示的是优化器基于统计信息估算出来的方案rows是猜测值实际执行可能有偏差。MySQL 8.0.18 及以上版本提供了EXPLAIN ANALYZE可以直接看到真实执行的行数和耗时EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 123 AND created_at 2024-01-01;输出是一个树状结构里面包含每个算子的真实耗时、真实行数、循环次数等。比如actual time2.341..4.125 rows10 loops1中的rows10是真实扫描到的行数actual time是实际耗时单位毫秒。这里要特别提醒EXPLAIN ANALYZE会真实执行这条 SQL。它对你的 SELECT 查询是安全的但如果对大数据量查询执行它真的会跑完整个查询占用数据库资源所以生产环境使用要慎重。如果一条 SQL 本身要跑 10 分钟EXPLAIN ANALYZE也会真的让它跑 10 分钟。我在生产环境一般用两次第一次用普通EXPLAIN看执行计划是否合理确定没有明显问题后再对关键算子做一次EXPLAIN ANALYZE确认估算和实际的差异。4. 慢 SQL 背后的典型场景与优化方向4.1 索引失效隐式类型转换和函数操作看执行计划时最常遇到的情况是明明字段上有索引type却是ALL。这种情况十有八九是索引失效而索引失效最常见的原因是隐式类型转换和函数操作。举个例子SELECT * FROM users WHERE phone 13800138000;如果phone字段是VARCHAR类型而你用数字去比较MySQL 会把phone字段隐式转换成数字再比较相当于对索引列做了一次函数操作索引自然失效。解决方法是把参数改成字符串SELECT * FROM users WHERE phone 13800138000;类似的情况还有在索引列上使用函数比如SELECT * FROM orders WHERE DATE(created_at) 2024-01-01;只要在索引列上套了DATE()函数索引基本用不上。正确的写法是把它改成范围条件SELECT * FROM orders WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;这个改写保留了索引的使用能力同时避免了函数操作。这类问题的排查思路很简单看到EXPLAIN里key为NULL先检查 SQL 的 WHERE 条件里有没有对索引列做运算。还有一点容易被忽略LIKE %keyword这种前置通配符也会导致索引失效LIKE keyword%则能走范围扫描。4.2 深分页问题LIMIT 越翻越慢分页查询越到后面越慢几乎是每个业务系统都会遇到的问题。经典 SQL 长这样SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;为什么慢因为 MySQL 需要先找到第 1000020 行之前的全部数据把前 100 万行全部扫描出来再丢弃最后只返回 20 行。数据量小的时候问题不明显一旦表里有了几百万上千万行这种写法就会让Rows_examined爆炸式增长。一种常用的优化方案是延迟关联。先只查出主键或id再通过JOIN回到原表取完整数据SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20) tmp ON o.id tmp.id;内层子查询只查id如果id是主键ORDER BY created_at配合索引能够快速定位目标行外层再回表取数据整体扫描行数会大幅下降。如果业务允许更彻底的方案是游标分页也就是记住上一次查询的最后一条位置避免使用LIMIT的大 offsetSELECT * FROM orders WHERE created_at 上次返回的最晚时间 ORDER BY created_at DESC LIMIT 20;这种玩法对用户体验来说需要配合下拉加载或者“上一页/下一页”交互但对数据库最友好。实际项目中我经常把延迟关联作为立即可用的快速优化手段把游标分页作为需要产品配合的长期方案。4.3 大表 JOIN 和临时表多表 JOIN 出现性能问题的概率很高尤其是大表 JOIN 小表、大表 JOIN 大表以及带GROUP BY、ORDER BY、DISTINCT的查询。先看一个常见问题Extra列出现Using join buffer说明 MySQL 需要在内存里缓冲一张表的数据再跟另一张表匹配这通常意味着没有走索引关联。优化方向是给关联字段建索引并且尽量让驱动表外层表是小表被驱动表内层表用索引查找。我见过一个实际案例两张表各 50 万行数据JOIN 之后耗时 12 秒EXPLAIN显示type是ALL因为被驱动表orders.user_id上没有索引。后来给user_id建了普通索引同一查询降到 0.2 秒。这个例子说明JOIN 慢的时候首要排查点不是 SQL 写法而是关联字段的索引是否存在。还需要警惕一个隐形问题GROUP BY和ORDER BY如果出现在不同的索引上MySQL 可能需要先排序再分组Extra里出现Using temporary和Using filesort这时候查询会变得很慢。解决办法通常是调整索引字段顺序让排序和分组的字段都包含在同一个联合索引里如果确实无法避免那就考虑用冗余表或者把统计结果放到应用层缓存而不是每次都现场计算。Using temporary还有一个隐蔽的来源SELECT DISTINCT和UNION。如果对几千行数据做 DISTINCT临时表很快但如果是几百万行内存临时表装不下就会落到磁盘上性能断崖式下降。看到Using temporary时去检查tmp_table_size和max_heap_table_size配置只是治标把这种重型去重操作移到应用层往往更加有效。5. 别忘了锁等待和系统资源层面的干扰5.1 从 processlist 和 innodb_trx 定位锁问题有时候 SQL 本身设计没问题、索引也齐全但就是慢。这时候要怀疑是不是锁等待导致的阻塞。一条 SQL 写完了可能一直在等另一个事务释放锁Query_time高但Lock_time占了大头。慢日志里的Lock_time就是信号。先通过SHOW FULL PROCESSLIST看当前连接状态SHOW FULL PROCESSLIST;如果看到大量连接处于Waiting for table metadata lock或Waiting for next key lock之类的状态基本可以确认锁等待。在 InnoDB 引擎下更精确的手段是查询information_schema.innodb_trx表找到当前所有活跃事务SELECT trx_id, trx_state, trx_started, trx_query, trx_mysql_thread_id FROM information_schema.innodb_trx\G配合sys.innodb_lock_waits视图能直接看到谁在等锁、谁持有了锁SELECT * FROM sys.innodb_lock_waits\G这个视图会返回waiting_pid等待线程和blocking_pid阻塞线程。通过blocking_pid去SHOW FULL PROCESSLIST里找到持锁会话再结合trx_started看它跑了多久。如果它已经执行了很久还没提交那就需要和业务方确认看能不能尽快提交或回滚事务。这里有一个很常见的误区很多人以为死锁才会导致慢查询。实际上死锁只是锁问题的极端情况更多的是长事务持锁不释放导致后面所有相关 SQL 排队等待。这类慢查询用执行计划看不出来必须从锁等待的角度去排查。5.2 系统资源、统计信息与执行计划的偏差在排除了 SQL 写法、索引、锁问题之后还要把眼光放到数据库所在的机器上。慢查询有时是资源竞争引发的。top看到 CPU 使用率飙高可能是大量排序和 join 操作iostat -x看到磁盘util接近 100%可能是内存不足导致频繁磁盘读写比如临时表落盘、InnoDB buffer pool 太小导致频繁读盘。我遇到过最典型的案例是某条统计 SQL 平时执行 200 毫秒某段时间突然变成 8 秒EXPLAIN 看执行计划完全正常type是refrows也合理。后来发现是同一时间上了个大报表任务把磁盘 IO 打满了。排查该类问题不需要高深手段先top看整体负载再用iostat看磁盘压力往往几秒钟就能定位。另一个容易被忽略的因素是统计信息过期。MySQL 优化器选择索引依赖表的统计信息如果表数据量在短时间内剧烈变化统计信息没有及时更新优化器可能选错索引。这时候即使 SQL 没有变化执行计划也可能变得很差。解决方法是定期执行ANALYZE TABLE orders;如果不想手工执行可以在业务低峰期做一个定时任务。实战中大表在执行计划突变、前一天还好好的情况下排查思路里应该加上“统计信息是否过期”这一项。先用SHOW INDEX FROM orders看Cardinality数值明显偏低或者和实际行数差距很大时就执行一次ANALYZE TABLE再重新EXPLAIN看执行计划有没有变化。6. 常见问题速查表与排查经验6.1 慢查询排查速查表我把日常工作中最常遇到的慢查询现象整理成一张表方便对照排查。现象可能原因排查方式解决方案Rows_examined 很大但 Rows_sent 很小缺少索引或索引失效EXPLAIN 看 type、key、rows建设联合索引改写 SQL 避免函数和隐式转换数据量不大却频繁全表扫描统计信息过期、索引未被选用SHOW INDEX 看 CardinalityANALYZE TABLE必要时 FORCE INDEX查询卡住State 显示锁等待行锁/表锁、长事务持锁sys.innodb_lock_waits排查持锁事务尽快提交或回滚分页越翻越慢深分页 LIMIT offset 过大查看 SQL 的 LIMIT 参数延迟关联、游标分页CPU 突然飙高大量排序/hash join/重复扫描pt-query-digest 聚合分析优化索引、改写 SQL、减少重复查询磁盘 IO 频繁buffer pool 过小、临时表落盘iostat -x、SHOW ENGINE INNODB STATUS调整 innodb_buffer_pool_size避免大排序同一个 SQL 以前快现在慢统计信息过期/数据分布变化EXPLAIN 对比历史执行计划ANALYZE TABLE重新优化索引遇到慢 SQL 时我会从“日志记录 → 执行计划 → 锁等待 → 系统资源”四个层面依次排查。大多数情况下问题会在第一层和第二层之间暴露如果前两层都正常再去看锁和系统资源基本不会走弯路。6.2 实操中容易踩的几个坑先说说慢日志参数的坑。很多人把long_query_time设得特别小比如0.1导致慢日志文件疯长一天几个 GB结果真正重要的慢 SQL 反而被淹没了。我建议线上环境从1秒起步根据日志量和业务压力逐步调整。还有log_queries_not_using_indexes这个参数它的本意是记录没走索引的查询但实际效果是大量小表全表扫描的查询都会刷进来日志爆炸速度比long_query_time0.1还快除非你有明确的监控告警系统否则不要轻易打开。再说说执行计划的坑。EXPLAIN里的rows是优化器的估算值不代表真实扫描行数。如果两条同样的 SQL 因为统计信息不同rows可能差一个数量级。所以我在判断一个索引是否发挥作用时会同时关注type和rows而不会只盯着其中一个。更严谨的做法是结合EXPLAIN ANALYZE验证真实数据。还有关于索引的惯性思维发现 SQL 慢就加索引这个做法本身没错但要注意索引不是越多越好。每个索引都会增加写入开销还会占用额外的存储空间。我见过一张表被加了十几个索引写入性能严重下降最后不得不清理。优化慢查询时优先通过改写 SQL、调整索引字段顺序来解决而不是无脑加新索引。一条 SQL 慢很多时候是因为现有索引用不上并不是缺少索引。最后分享一个运维习惯生产环境变更之前先把 SQL 的EXPLAIN截图留档变更之后再做一次对比。这个习惯帮我避免了很多“改了配置反而更慢”的问题。慢查询优化不是一次性工作而是一个持续跟踪的过程。每个季度把慢日志重新分析一遍把 Top 10 拿出来过一遍你会发现数据库的性能问题大多是周期性出现的解决一批还会来一批。把这套流程跑起来慢查询就不会再是让你半夜上线的元凶。
