搞数据库的人，谁还没被慢查询坑过几回？月初大促前压测，接口突然从 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。建议开启，因为很多时候慢不慢不完全看执行时间，全表扫描在数据量小的时候不慢，等表涨起来就完了，提前记录有备无患。
动态开启的命令如下，不需要重启 MySQL：
SET 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，基本就是全表扫描加过滤的节奏，需要特别留意。
举个例子，我前阵子排查过一条 SQL：
EXPLAIN SELECT order_id, user_id, amount FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 20;执行计划里type=ALL、Extra=Using filesort，两个大坑全踩了。订单表几百万行，status字段区分度低，加上排序没走索引，这条语句每次执行都是全表扫描加文件排序，不慢才怪。后来把(status, create_time)建成联合索引，同时把查询字段都包含进去做成覆盖索引，执行计划变成type=ref、Extra=Using 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 慢查询排查速查表
| 症状 | 可能原因 | 排查入口 | 优化手段 |
|---|---|---|---|
type=ALL | 全表扫描 | EXPLAIN 的 type 列 | 确认 WHERE、JOIN 字段是否缺索引 |
Extra=Using 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 pool，SQL 本身有问题的前提下，再大的内存也扛不住全表扫描。
另外一个容易被忽略的点是：优化不是一次性工作，业务数据量在增长，历史 SQL 的执行计划可能随时变化。有条件的话，把慢查询监控和告警接入到自动化运维体系，每周花半小时扫一遍新增的慢 SQL，比等线上出问题再被动救火要舒服得多。
最后分享一个小技巧：每次优化完记得记录优化前后的执行时间、扫描行数、执行计划变化，形成一张前后对比表。这不仅是给自己积累经验，将来做代码评审或者向上汇报时，都是很有说服力的数据。慢查询优化这事儿，做得多了你会发现，与其说是调数据库，不如说是在磨自己的排查思维——每一次定位到根因的瞬间，都还挺有成就感的。
