MySQL慢查询排查与优化：执行计划、索引设计与SQL改写实战指南

搞数据库的人谁还没被慢查询坑过几回月初大促前压测接口突然从 20ms 飙到 2s半夜告警群里连着刷屏一查全是同一条 SQL新来的同事上线了一个功能结果把线上库的 CPU 直接打满。这些场景背后几乎都有一个共同的名字——MySQL 慢查询。我这些年排查过的慢 SQL 没有一百条也有八十条了从最开始只会看执行计划到后来慢慢形成一套完整的排查和优化流程踩过的坑确实不少。这篇文章就把我日常处理慢查询的思路、工具、实操命令和优化原则整理出来希望能帮你少走点弯路。1. 定位慢查询先找到元凶再谈优化很多人一听说 SQL 慢第一反应是加索引这其实把顺序搞反了。优化慢 SQL 的第一步是定位搞清楚是哪条 SQL、在哪个环节慢、扫描了多少行、返回了多少行。没有这些数据支撑优化就是瞎猜。1.1 慢查询日志配置与日常开启建议MySQL 自带的慢查询日志是排查问题最基础的手段很多生产环境默认是关闭的需要手动开启。相关配置项不多但每个都值得仔细确认。-- 查看当前慢查询相关配置 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE slow_query_log_file; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE log_queries_not_using_indexes;slow_query_log是否开启慢查询日志ON 开启OFF 关闭。slow_query_log_file慢查询日志文件的保存路径。long_query_time阈值时间单位秒执行时间超过这个值的 SQL 会被记录。默认是 10 秒我个人建议线上环境设成 1 秒如果业务压力不大甚至可以设成 0.5 秒。log_queries_not_using_indexes是否记录没有走索引的 SQL。建议开启因为很多时候慢不慢不完全看执行时间全表扫描在数据量小的时候不慢等表涨起来就完了提前记录有备无患。动态开启的命令如下不需要重启 MySQLSET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;这里有个细节需要注意long_query_time的修改只对之后新建的连接生效已经存在的连接不会立即生效。所以你在命令行改了之后最好重新连接一次再测试否则会怀疑自己改了个寂寞。还有一点慢查询日志文件会一直增长时间长了会占用不少磁盘空间。建议配合 logrotate 或者写个定时任务定期切割同时按天或者按周归档避免把磁盘撑爆。我见过不止一次因为慢查询日志太大把磁盘写满的案例那不是优化是事故。1.2 慢查询日志分析从海量日志里捞关键 SQL日志开启之后接下来就是分析。如果慢查询不多直接 vim 打开日志文件人肉看就行。但如果线上环境慢查询比较多日志文件可能是几百 MB 甚至几个 GB这时候就得用工具。MySQL 自带的mysqldumpslow是官方提供的日志汇总工具用法很简单# 按执行次数排序显示前 10 条 mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log # 按平均执行时间排序显示前 20 条 mysqldumpslow -s at -t 20 /var/lib/mysql/slow.log # 按总执行时间排序显示前 20 条 mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log-s指定排序方式常用的是c执行次数、t总耗时、at平均耗时-t指定返回前 N 条。mysqldumpslow会把日志中具体的参数值抽象成N和S方便把结构相同的 SQL 聚合成一条这个设计很贴心。如果觉得mysqldumpslow不够直观可以用 Percona Toolkit 里的pt-query-digest它能生成一份详细的报告按总耗时、执行次数、平均行数等维度展示所有慢查询还会把典型的 SQL 样例列出来一键定位最耗时的语句。分析结果里重点看几个指标Query_time 分布、Rows_examined、Rows_sent。如果Rows_examined远大于Rows_sent说明查询扫描了大量数据但只返回了少量结果这种 SQL 优化空间往往很大。2. 执行计划EXPLAIN解读读懂 MySQL 的内心戏定位到具体的慢 SQL 之后下一步就是用EXPLAIN看执行计划。执行计划是优化器根据表结构、索引、统计信息生成的一份查询路线图能不能看懂它直接决定了你能不能找到性能瓶颈。注意EXPLAIN只是分析 SQL 的执行计划并不会真正执行 SQL所以在线上的大表上分析也不用担心拖垮数据库可以放心用。2.1 EXPLAIN 核心列到底怎么看EXPLAIN SELECT ...的输出结果有好几列每列都有用但实际排查时优先级不同。列名作用重点关注程度type访问类型从好到差依次是 system const eq_ref ref range index ALL极重要key实际使用的索引名如果为 NULL 说明没用索引极重要rows预估扫描的行数只是一个估算值但参考价值很高重要extra额外的执行信息包含了非常多关键线索极重要filtered表过滤条件过滤的比例百分比越低越好一般type列是核心中的核心。看到ALL就要警惕这是全表扫描数据量一大基本就会出问题index代表遍历了整棵索引树比ALL好一点但本质也是扫描range表示索引范围扫描比如WHERE id 100这种情况还算健康ref是非唯一索引等值匹配比较常用eq_ref是多表连接时被驱动表通过主键或唯一索引访问效率很高const和system是极值主键或唯一索引等值查询时会出现性能最优。2.2 Extra 列里藏的优化线索Extra列很多初学者不重视其实里面信息量很大。常见的关键字有这么几个Using filesort文件排序。说明 MySQL 没法利用索引完成排序只能把数据加载到内存或者磁盘排序。这个对性能影响很大尤其当参与排序的数据量很大时。出现这个关键字优先考虑能不能在ORDER BY字段上建索引或者调整联合索引的字段顺序让排序走索引。Using temporary使用了临时表。通常在GROUP BY、DISTINCT、UNION这类操作中出现如果临时表还被写到了磁盘上那性能会进一步恶化。出现这个关键字优先考虑改写 SQL 或者调整索引。Using index覆盖索引扫描。这是比较理想的情况查询的字段都在索引里不需要回表性能很好看到它不需要太担心。Using where在存储引擎返回记录后Server 层还要进一步过滤。如果 type 是 ALL 并且出现了 Using where基本就是全表扫描加过滤的节奏需要特别留意。举个例子我前阵子排查过一条 SQLEXPLAIN SELECT order_id, user_id, amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 20;执行计划里typeALL、ExtraUsing filesort两个大坑全踩了。订单表几百万行status字段区分度低加上排序没走索引这条语句每次执行都是全表扫描加文件排序不慢才怪。后来把(status, create_time)建成联合索引同时把查询字段都包含进去做成覆盖索引执行计划变成typeref、ExtraUsing index查询直接从 1.2 秒降到了 30ms。3. SQL 优化实操从能用到好用理解了执行计划找到问题所在接下来就是动手优化 SQL。优化 SQL 的原则我一直强调四个字减少扫描。所有优化手段本质上都是在减少 MySQL 需要扫描的数据量。3.1 索引失效的典型场景排查清单索引明明建了但 SQL 没走这是最让人头疼的情况。我整理了实际工作中最常见的七种索引失效场景基本覆盖了日常开发 90% 的踩坑点函数包裹索引列。WHERE DATE(create_time) 2024-01-01这种写法MySQL 无法使用create_time上的索引。正确写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02把函数去掉或者改成范围查询。隐式类型转换。表里phone字段是 varchar查询写成WHERE phone 13800138000数字会被转成字符串再比较导致索引失效。解决办法是 SQL 里写成字符串WHERE phone 13800138000。LIKE前置通配符。WHERE name LIKE %张%这种写法没法走索引因为索引是从左往右匹配的%开头意味着头都不确定。一定要做模糊匹配可以分词的场景考虑用全文索引或者 ES而不是死磕 MySQL 的LIKE。OR连接非索引列。WHERE id 1 OR status 1如果status没有索引即使id走了主键索引整个查询依然可能退化成全表扫描。能改成UNION ALL就改或者给两侧的字段都建上索引。NOT IN (SELECT ...)。子查询返回大量数据时MySQL 优化器可能放弃索引选择全表扫描。这种写法通常可以改写成LEFT JOIN ... WHERE ... IS NULL性能会有明显提升。对索引列进行运算符操作。WHERE salary * 2 10000这类写法索引照样失效。把表达式移到等式右边WHERE salary 5000。联合索引字段乱序使用。联合索引(a, b, c)遵循最左前缀原则你直接写WHERE b 1 AND c 2索引用不上。必须要有a字段的条件在前面。注意索引失效的场景在 MySQL 5.7 和 8.0 上略有不同。5.7 的隐式类型转换几乎必然导致索引失效8.0 在某些情况下优化器会自己转换但不要指望这个写 SQL 时保持类型一致才是正解。3.2 常用 SQL 改写技巧与实战案例光知道哪些写法会让索引失效还不够得知道怎么改写才能优化。我在实际工作中用到的改写技巧主要有以下几种。分页深翻页优化最常见的慢查询之一是大分页比如LIMIT 100000, 20。MySQL 需要先扫描 100020 行然后丢弃前 100000 行工作量大得惊人。解决思路有两种第一种是延迟关联先通过覆盖索引找到主键 ID再回表查数据-- 优化前 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 优化后 SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id t.id;第二种是记住上次查询的最大 ID通过WHERE id ?的方式翻页SELECT * FROM orders WHERE id 100000 ORDER BY id LIMIT 20;这种方式适合数据是顺序追加、ID 连续的业务场景不适合频繁删除数据的表因为删除会造成 ID 空洞页码数据会对不上。NOT IN改LEFT JOIN-- 优化前 SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist); -- 优化后 SELECT u.* FROM users u LEFT JOIN blacklist b ON u.id b.user_id WHERE b.user_id IS NULL;改写之后MySQL 会用users表驱动blacklist表配合索引效率会好很多。不过要注意如果两个表的数据量差异很大LEFT JOIN也可能产生临时表和文件排序还是需要配合执行计划来判断。大表COUNT(*)优化SELECT COUNT(*) FROM table_name在 MyISAM 表里是秒回的因为引擎直接存了总行数。但在 InnoDB 里由于 MVCC 机制COUNT(*)需要逐行计数大表上执行简直噩梦。我常用的替代方案是如果业务只是需要大概的数量级可以用SHOW TABLE STATUS来估算行数不精确但是快如果需要精确数量又频繁查询维护一张计数表在业务事务里同步更新虽然增加了一点写开销但读速度是质的飞跃。利用覆盖索引减少回表一条查询如果所有需要的字段都在同一个索引里MySQL 可以直接从索引返回结果完全不需要回表读取数据行。这个技巧在报表查询里特别实用。比如经常要查用户的状态和更新时间-- 建索引 ALTER TABLE users ADD INDEX idx_status_update (status, update_time); -- 查询两个字段都在索引中走覆盖索引 SELECT status, update_time FROM users WHERE status 1;需要注意的是覆盖索引虽然好用但也不建议为了覆盖而把过多的字段塞进索引索引字段越多占用的空间越大写入的成本也越高。一般覆盖查询最频繁的那几个字段就足够了。4. 索引设计优化一劳永逸的基础工程很多时候SQL 本身写得没问题但执行计划还是不走索引那问题就出在索引设计上。索引不是建得越多越好也不是在查询字段上随便加一个就行设计索引需要结合业务特征和数据分布。4.1 索引选择性与前缀索引索引的区分度是建索引时第一个要考虑的因素。区分度太低比如性别字段只有男女两个值在这个字段上建索引几乎起不到过滤作用优化器甚至会认为走索引比全表扫描还慢干脆放弃索引。区分度可以用选择性Selectivity来衡量计算公式是选择性 COUNT(DISTINCT column) / COUNT(*)选择性越接近 1说明这个字段越值得建索引。比如订单表的order_no字段每个订单一个号选择性是 1而order_status字段可能只有十几个值选择性很低单独建索引意义不大只能配合其他条件使用。对于字段本身比较长的列比如url、content这种动辄几百字符的字段直接建索引会浪费大量空间还会拖慢 B 树的查询效率。这时候可以考虑前缀索引只对字段的前 N 个字符建索引ALTER TABLE articles ADD INDEX idx_url_prefix (url(64));前缀长度的选择需要找到一个平衡点既要有足够的区分度又不能太长。一般可以这样测SELECT COUNT(DISTINCT LEFT(url, 32)) / COUNT(*) AS selectivity32, COUNT(DISTINCT LEFT(url, 48)) / COUNT(*) AS selectivity48, COUNT(DISTINCT LEFT(url, 64)) / COUNT(*) AS selectivity64 FROM articles;对比不同前缀长度下的选择性选择第一个接近 1 的长度作为索引前缀通常 32 到 64 之间就能达到不错的效果。4.2 联合索引设计原则与最左前缀联合索引是实际业务中用得最多的索引类型但也是最容易出问题的地方。很多人以为在 WHERE 条件里涉及的字段都建上索引就行但 MySQL 的联合索引遵循最左前缀原则索引顺序是(a, b, c)那么WHERE a ?、WHERE a ? AND b ?、WHERE a ? AND b ? AND c ?都能走索引但WHERE b ?就走不了。关于联合索引字段顺序的排布我有几条经验选择性高的字段放前面。同样一个联合索引(status, create_time)如果大部分数据 status 都是 1那status在前其实过滤不了多少数据反过来如果create_time的区分度远高于status把create_time放在前面效果可能更好。这里不是绝对的还要结合业务查询条件来看。等值条件优先排序条件在后。如果查询里既有等值条件又有排序把等值判断的字段放前面排序字段放后面。比如WHERE status 1 ORDER BY create_time DESC联合索引(status, create_time DESC)能同时服务过滤和排序避免Using filesort。考虑执行计划再做最终决定。同样的 SQL 在 5.7 和 8.0 上优化器的选择可能不同。设计好联合索引后一定用EXPLAIN验证不要停留在纸面推演。举个例子一个订单查询页面主要查询条件是商户 ID 订单状态 创建时间范围排序是创建时间倒序SELECT order_id, amount, create_time FROM orders WHERE merchant_id 10086 AND status 1 AND create_time 2024-06-01 ORDER BY create_time DESC LIMIT 20;这里最合理的联合索引是(merchant_id, status, create_time)。前两个字段负责精确定位到某个商户的某种状态的订单第三个字段同时承担了范围过滤和排序。如果同时再把order_id和amount也包含进去形成覆盖索引查询性能会更加理想。4.3 冗余索引与无效索引清理索引也不是越多越好每一条索引都会拖慢写入性能。InnoDB 在写入数据时需要维护所有索引索引多了插入和更新速度自然就下来了。所以定期清理冗余索引是必要的维护工作。常见的情况包括以下几种重复索引idx_status和idx_status_create_time中前者就是冗余的因为(status, create_time)联合索引已经覆盖了单纯status的查询场景。未被使用的索引通过performance_schema.table_io_waits_summary_by_index_usage或者sys.schema_unused_indexes视图可以查到没有被使用过的索引确认后可以删除。部分前缀冗余(a, b)和(a)两棵索引树前者也能覆盖后者的查询后者就可以清理掉。清理索引的命令很简单-- 查看未使用的索引(需要开启 performance_schema) SELECT * FROM sys.schema_unused_indexes; -- 删除冗余索引 ALTER TABLE orders DROP INDEX idx_status;注意删除索引前必须确认生产环境的慢查询日志和监控中确实没有依赖这个索引的 SQL否则上线后的突发慢查询会让你非常被动。5. 真实案例复盘与问题速查前面讲了不少原理和方案可能有点抽象。这一节我复盘两个实际处理过的案例再给一个速查表方便你以后直接对照排查。5.1 典型慢查询案例复盘案例一一条报表 SQL 拖垮整个读库背景是业务方每天凌晨跑一批报表原本半小时就能跑完某天开始突然要跑三个小时还频繁报锁等待超时。排查过程如下先查慢查询日志发现耗时最长的是一条多表 JOIN 的汇总查询执行时间超过 2000 秒。EXPLAIN分析后发现最外面的大表走了全表扫描rows预估扫描 800 万行Extra里还有Using temporary; Using filesort。再往里面看JOIN 用的关联字段在另一张表上没有索引导致被驱动表每次都要全表扫描去匹配。方案分两步第一步给关联字段建立普通索引让 JOIN 走ref类型第二步把ORDER BY字段重新整理进联合索引消除文件排序。优化后同样一条 SQL执行时间从 2000 秒降到了 11 秒报表任务稳定在 40 分钟内跑完。这个案例给我最大的教训是多表关联查询一定要在关联字段上确认索引尤其是被驱动表的关联字段没有索引就是灾难。案例二深分页翻页导致页面卡死背景是后台管理系统的订单列表用户一翻到几百页就卡住接口响应时间超过 30 秒。排查时直接看 SQL发现是经典的深分页写法SELECT * FROM orders WHERE merchant_id 10086 ORDER BY create_time DESC LIMIT 300000, 20;这条 SQL 的EXPLAIN显示走了merchant_id的索引rows 也只有几千行看起来没问题但实际执行却异常慢。原因在于MySQL 根据merchant_id找到几千行之后需要按照create_time排序然后丢弃掉前面 30 万行再返回第 300001 到 300020 行。回表和排序的开销被放大了几个数量级。优化时我改成了延迟关联先在子查询里用覆盖索引拿到排序后的主键 ID再关联回原表取完整行SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE merchant_id 10086 ORDER BY create_time DESC LIMIT 300000, 20 ) t ON o.id t.id;子查询只查id能走(merchant_id, create_time, id)的覆盖索引避免了大范围回表外层再通过主键关联取完整数据。优化后接口响应时间从 30 秒降到了 0.8 秒。不过说实话这种优化治标不治本数据量再大一些LIMIT 300000依然要扫描很多索引。更彻底的做法是像 3.2 节提到的那样改成WHERE create_time ?的键集分页方式每次按上次返回的最后一条数据的排序值作为下一页的起点翻页越深优势越明显。5.2 慢查询排查速查表症状可能原因排查入口优化手段typeALL全表扫描EXPLAIN 的 type 列确认 WHERE、JOIN 字段是否缺索引ExtraUsing filesort排序未走索引EXPLAIN 的 Extra 列建联合索引时把排序字段加入索引末尾Rows_examined远大于返回行数过滤条件弱或类型不匹配慢日志中的行数优化 WHERE 条件、索引设计锁等待超时长事务或行锁竞争SHOW ENGINE INNODB STATUS拆分事务、缩短事务执行时间CPU 使用率突增某条 SQL 扫描量暴涨慢查询日志锁定 SQL 后按上述流程优化日志文件巨大慢查询过多或阈值过低磁盘空间针对慢 SQL 逐个优化后调高阈值这个表是我平时排查问题的快捷入口建议收藏或者贴到自己团队的 wiki 里。排查慢查询最忌讳的就是凭感觉一定要拿着EXPLAIN一条一条验证用数据说话。6. 一些额外的配置调优心得SQL 层面的优化做到位之后如果还有性能问题可以再看看 MySQL 的配置参数。配置调优不是一上来就调 buffer pool 大小而是基于问题的针对性调整这里分享几个日常比较有用的点。innodb_buffer_pool_size是 InnoDB 缓存表和索引数据的内存区域这个参数对读性能影响极大。如果太小热点数据频繁被淘汰每次都走磁盘性能自然上不去。通常建议设置为物理内存的 50% 到 70%但具体还要看服务器上是否还跑着其他进程。查看当前命中率可以用SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; -- 命中率 Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads)tmp_table_size和max_heap_table_size会影响内存临时表的大小当GROUP BY、DISTINCT产生临时表超过这个大小就会落到磁盘性能急剧下降。注意这两个参数取的是最小值建议一起调整比如都设成 64M能让大多数临时表留在内存里。还有一个容易被忽略的是long_query_time和慢查询日志的配合我见过很多团队把阈值设得很低比如 0.1 秒结果慢查询日志每天几十个 G反而无法有效定位问题。合理做法是先设置 1 到 2 秒等主要慢查询都优化完再逐步下调阈值。配置调优有个铁律一次只改一个参数改完观察一段时间通过对比压测结果决定保留还是回滚。不要一堆参数一起调出了问题根本不知道是哪个参数引起的。7. 写在最后的经验做 MySQL 慢查询优化这几年我最深的体会是慢 SQL 排查没有银弹但有一把万能钥匙——执行计划。无论你面对的是多复杂的 SQL只要肯静下心来用EXPLAIN把每一步的执行路径看清楚问题基本都能浮出水面。优化的顺序我始终建议是先看慢查询日志找到元凶再用 EXPLAIN 分析执行计划然后针对性改写 SQL、调整索引最后才轮到配置参数。千万不要一上来就调 buffer poolSQL 本身有问题的前提下再大的内存也扛不住全表扫描。另外一个容易被忽略的点是优化不是一次性工作业务数据量在增长历史 SQL 的执行计划可能随时变化。有条件的话把慢查询监控和告警接入到自动化运维体系每周花半小时扫一遍新增的慢 SQL比等线上出问题再被动救火要舒服得多。最后分享一个小技巧每次优化完记得记录优化前后的执行时间、扫描行数、执行计划变化形成一张前后对比表。这不仅是给自己积累经验将来做代码评审或者向上汇报时都是很有说服力的数据。慢查询优化这事儿做得多了你会发现与其说是调数据库不如说是在磨自己的排查思维——每一次定位到根因的瞬间都还挺有成就感的。