SQL调优全链路实战：从慢查询定位到索引优化与执行计划分析

1. 先搞清楚：SQL 调优到底在调什么

工作里一谈到数据库慢，很多人第一反应就是“加索引”“ rewrite 一下 SQL”，但真正上手调优之前，我建议你先花十分钟想清楚一个问题：这条 SQL 慢，到底是慢在查询本身，还是慢在整体负载，还是慢在等你的人太多？

说个我实际遇到的例子。某次线上订单表查询突然从 50ms 飙到 2.8s，开发同学第一时间贴了 SQL 过来让帮忙优化。我看了一眼，单表查询、条件简单、索引齐全，怎么看都不该慢。后来一查才发现，同一时刻有个批量任务在跑 UPDATE，锁住了大量行，这条 SELECT 一直在等行锁释放。你就算把 SQL 优化到极致，锁不解决照样慢。所以 SQL 调优的第一步不是改 SQL，而是先确认瓶颈到底在哪个环节。

一般来说，一条 SQL 从客户端发出来到结果返回，会经过网络传输、SQL 解析、优化器生成执行计划、存储引擎执行、返回数据这几个阶段。调优就是逐个环节排查，看时间花在了哪里。实操中最常用的定位手段就是慢查询日志配合性能视图，先确认“慢”这个事实，再谈“怎么调”。

另外，SQL 调优不只是“加索引”这么简单。它是一条完整的链路：定位慢 SQL、分析执行计划、优化索引设计、改写 SQL、调整参数、最后做回归验证。每一环都有对应的工具和方法论。这篇文章我就按这条链路，把我平时在项目里实操过的经验和踩过的坑完整梳理一遍。

2. 定位慢 SQL：没有目标，调优就是瞎忙

2.1 慢查询日志的正确打开方式

MySQL 提供了慢查询日志，专门记录执行时间超过阈值的 SQL。这一步是调优的起点，但很多人在配置上就出了问题。

慢查询日志默认是关闭的，需要手动开启。我个人习惯在配置文件里这样设置：

slow_query_log = 1 slow_query_log_file = /data/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1

几个参数说明一下。long_query_time我设置成 1 秒，即超过 1 秒的记录。生产环境有专门的 DBA 团队的话，可以设成 0.5 秒甚至更低。log_queries_not_using_indexes这个参数很多人会忽略，它的作用是即使 SQL 执行很快，但只要没用索引也会记录，这个对发现隐性问题很有帮助。

这里有个坑：long_query_time的单位是秒，支持小数，但很多教程里写的是整数。比如你写long_query_time = 0.5，MySQL 5.7 及以上版本是能正确识别的，但有些老版本只认识整数。设置完记得确认一下生效值：

SHOW VARIABLES LIKE 'long_query_time';

还有一个细节：慢查询日志记录的是“执行时间超过阈值”的语句，但所谓“执行时间”是指语句从开始执行到返回结果的耗时，不包括等待锁的时间。所以前面说的那种被锁阻塞的慢查询，用慢查询日志是抓不到的，需要结合SHOW ENGINE INNODB STATUS或者 performance_schema 来排查。

2.2 用一个查询快速捞出慢 SQL

慢查询日志是文本文件，直接打开看往往很长，而且夹杂着很多无关信息。我一般用mysqldumpslow这个自带工具做汇总：

mysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log

这句命令的意思是按查询次数排序，显示前 10 条最频繁出现的慢 SQL。-s c是按 count 排序，也可以改成-s t按总耗时排序、-s at按平均耗时排序。注意mysqldumpslow会把 SQL 里的具体值抽象成N和S，方便把同类 SQL 归并到一起统计，这是它的核心价值。

如果嫌命令行工具不够直观，也可以直接在数据库里查。MySQL 5.7 开始支持 performance_schema 中的 events_statements_summary_by_digest 表，可以按 SQL 摘要维度查执行次数、平均耗时、扫描行数等：

SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1000000000 AS avg_ms, SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

这张表的字段很多，里面SUM_ROWS_EXAMINED和SUM_ROWS_SENT的比例特别值得关注。如果扫描行数是返回行数的几十倍甚至几百倍，基本可以断定这条 SQL 的访问路径有问题，该加索引或者该改写。

2.3 顺手排查性能基线

除了慢查询日志，生产环境排查慢 SQL 时我还会顺手看一下当时的系统状态。很多时候 SQL 本身没有问题，是资源占用导致整体性能下降。重点关注三个指标：CPU 使用率、磁盘 IO 的iowait、以及 InnoDB 的Buffer Pool命中率。

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

用这两个值计算命中率：(read_requests - reads) / read_requests。正常情况下命中率应该在 99% 以上，如果低于 95%，就要考虑是不是 Buffer Pool 配得偏小，或者有大量全表扫描在刷数据页。

这一步做完，定位阶段才算完整。事实清楚之后，才进入真正核心的环节：分析执行计划。

3. EXPLAIN 要这样看：执行计划里的性能密码

3.1 一条 SQL 的执行计划长什么样

拿到一条慢 SQL，我做的第一件事永远是执行EXPLAIN。最基础也最常用的语法：

EXPLAIN SELECT o.order_id, o.amount, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'PAID' AND o.create_time > '2024-01-01';

执行后返回的结果集里每一行代表执行计划中的一步，关键字段有id、select_type、table、type、possible_keys、key、rows、Extra。很多人只看key有没有索引，其实远远不够。真正的信息量在type、rows和Extra这三个字段上。

type表示访问类型，从上到下性能从好到差排列为：

正常情况下，能优化到ref或range是及格线，能到eq_ref或const就是满分。如果看到ALL，基本可以确定这条 SQL 有问题，需要加索引或者改写。

3.2 rows 不是精确值，但可以参考

rows字段估算的是 MySQL 优化器认为需要扫描的行数。注意这是估算值，不是实际执行值。它来自统计信息，统计信息又不一定准，所以有时候rows和实际相差很大。不过作为参考完全够用。

看rows的目的，是判断是否发生了“扫描行数远大于返回行数”的情况。比如一条 SQL 查询结果是 1 行，但rows是 10 万，这就说明访问路径选得不好。优化方向很明确：把扫描行数降下来。

这里补充一个实操技巧：EXPLAIN只能看估算值，想看实际执行时真实的扫描行数，需要用到EXPLAIN ANALYZE。这是 MySQL 8.0.18 引入的功能，它会真正执行 SQL，并返回每个步骤的实际耗时和行数：

EXPLAIN ANALYZE SELECT o.order_id, o.amount, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'PAID';

输出结果里会显示actual time和rows，这才是真实执行数据。MySQL 8.0 以下没有这个功能，可以用SET profiling = 1加SHOW PROFILES来看执行耗时，但拿不到精确到每一步的行数。

3.3 Extra 字段里藏着的关键信息

Extra字段经常会被忽略，而且它常伴随一些“看起来很不妙”的内容。我挑几个高频出现的说下。

Using filesort：不是磁盘文件排序，而是表示无法利用索引顺序，需要额外排序。这个一般伴随 ORDER BY 出现，优化方式是让排序字段跟查询条件构成联合索引，利用索引天然有序的特性消除排序。

Using temporary：使用了临时表。通常出现 GROUP BY 或 DISTINCT 的场景，同样的优化思路，尽量让分组字段走索引。

Using index：这是个好消息，表示覆盖索引生效，查询只需要扫描索引，不用回表。

Using where：表示在存储引擎返回记录后，又加了一层过滤条件。这个本身不算大问题，但要结合type一起看。如果type是 ALL 且Using where，那就是全表扫描后过滤，必须优化。

Using index condition：这是 Index Condition Pushdown（ICP）特性，在 MySQL 5.6 之后默认开启。表示存储引擎层先根据索引进行条件过滤，再返回表数据。能有效减少回表次数，在联合索引的场景下经常出现。

我自己看执行计划有一个习惯：先把type扫一眼，凡是 ALL 的记录标出来；再核对rows和返回行数的比例；最后看Extra里有没有filesort和temporary。三个维度过一遍，SQL 的问题基本就暴露了。

4. 索引优化：调优的胜负手

4.1 索引为什么这么快：B+ 树的逻辑

索引之所以能加速查询，是因为它底层用 B+ 树这种数据结构组织数据。B+ 树的特点是：

• 数据都存放在叶子节点，且叶子节点之间通过双向指针串联
• 非叶子节点只存索引键值和子节点指针，不存实际数据
• 树的高度一般维持在 3 到 4 层

这意味着什么？一次索引查找只需要经过 3 到 4 次磁盘 IO，就能从 2 千万行的表里定位到目标数据。对比全表扫描，如果数据量太大，可能要做上万次 IO。这就是索引能带来数量级性能提升的原因。

另外一个常被忽略的优势：B+ 树的叶子节点有序。所以索引天然支持范围查询和排序，ORDER BY 字段如果恰好是索引列，就能省掉 filesort。这也是前面说的“用联合索引消除排序”的底层原理。

4.2 最左前缀法则：联合索引的灵魂

联合索引是 SQL 调优里使用频率最高的优化手段，但它的使用有铁律：最左前缀法则。简单说，查询条件里必须包含联合索引最左边的列，索引才会被用到。

假设表里有联合索引(a, b, c)：

• WHERE a = 1命中索引
• WHERE a = 1 AND b = 2命中索引
• WHERE a = 1 AND b = 2 AND c = 3完全命中
• WHERE b = 2无法使用该索引
• WHERE c = 3无法使用该索引

这个规则是 B+ 树的特性决定的：联合索引的排序规则是先按第一列排，同第一列再按第二列排，以此类推。所以没有第一列作为前缀，就无法在索引树上定位。

实际项目里我见过不少这种情况：建了联合索引，但查询条件里没用上第一列。比如订单表建了(create_time, status)索引，业务方的核心查询却是WHERE status = 'PAID'。这个查询用不上索引，要么在 status 列单独建索引，要么调整查询逻辑让 create_time 参与过滤。判断一条查询能否用到联合索引，最靠谱的方法就是执行 EXPLAIN 看key字段，而不是靠猜。

4.3 覆盖索引与回表的性价比权衡

InnoDB 的主键索引（聚簇索引）的叶子节点保存的是整行数据，普通索引（二级索引）的叶子节点保存的是索引列和主键值。这就导致一个现象：通过普通索引查数据时，先找到主键值，再拿主键去聚簇索引里查整行，这个过程叫回表。

如果 SQL 要查询的列恰好全都包含在某一个索引中，那么连回表都不需要，直接在索引上查出结果，这就是覆盖索引。覆盖索引能大幅减少磁盘 IO。

举个实际例子，查询用户表的手机号和昵称：

SELECT phone, nickname FROM users WHERE phone = '13800138000';

如果只在 phone 列建了单列索引，执行时找到主键后还要回表查 nickname。如果改成联合索引(phone, nickname)，那么 phone 和 nickname 都在索引里，直接返回，不需要回表。虽然多了一个索引，但查询速度快很多。

不过覆盖索引不是越多越好。索引本身占用空间，写入时也要维护，加索引会增加插入和更新的开销。我自己判断时主要看两个点：一是高频查询的返回列是否稳定，二是表写入频率是否很高。如果一秒钟要写几万行，那加索引就要谨慎，写入性能下降的代价可能超过查询收益。

4.4 索引失效的常见场景：写在前面怕你踩坑

这部分全是经验和教训，我直接把最常见的几个坑列一下：

1. 对索引列使用函数：WHERE DATE(create_time) = '2024-01-01'会失效，应改成WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。
2. 隐式类型转换：列类型是 varchar，查询时用了数字，WHERE phone = 13800138000索引失效。字符串列上必须带引号。
3. LIKE 模糊匹配左通配：WHERE name LIKE '%张'用不上索引，WHERE name LIKE '张%'可以。
4. OR 连接非索引列：WHERE id = 1 OR status = 1，如果 status 没有索引，整个查询可能退化。建议改成 UNION ALL。
5. NOT IN、!=、<> 通常不走索引：能改成范围查询的就改。
6. 联合索引违反最左前缀：前面讲过，不复述了。

这里单独说下第 2 条。隐式类型转换不只是字符串列查数字的问题，还有字符集不一致导致索引失效。表 join 时，两边关联字段如果一个是 utf8mb4 一个是 utf8，MySQL 会做隐式转换，索引大概率失效。建表时期定义关联字段统一字符集，是最省事的方案。

5. SQL 改写实战：同样结果，不同写法

5.1 深分页优化：为什么越翻越慢

分页查询是业务系统最常见的慢 SQL 来源，尤其是深分页。看这段：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;

这条 SQL 看着简单，但执行时 MySQL 需要先扫描出前 100020 行，然后丢弃前 10 万行，只返回最后 20 行。前面扫描的行全部浪费了。数据量越大，翻页越深，代价越高。

经典优化方案是延迟关联。先只查主键，再关联回原表取完整数据：

SELECT o.* FROM orders o JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id = t.id;

子查询里只扫描主键和排序字段。如果(create_time, id)上建了联合索引，子查询本身就是覆盖索引查询，不需要回表，扫描代价小得多。外层再按主键关联取完整行，最多只查 20 行完整数据。

另一个方案是基于游标的分页，也就是记住上一次查询最后一条数据的位置：

SELECT * FROM orders WHERE create_time < '2024-01-15 10:00:00' ORDER BY create_time DESC LIMIT 20;

详情页、信息流这类应用，这个方案更优雅。但有一个限制：要求排序字段严格去重且可以比较。如果 create_time 有重复值，会出现漏数据的问题，需要额外带上 id 条件才能保证稳定。

5.2 子查询与 JOIN 的取舍

MySQL 5.6 之前对子查询的优化有限，很多人养成了“能用 JOIN 就不用子查询”的习惯。5.6 之后引入了子查询优化器，部分场景下会自动改写为 JOIN，情况改善不少。但实测下来，半连接类型的子查询（EXISTS、IN）在不同 MySQL 版本下执行计划差异很大。

比如：

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);

在 MySQL 5.7 中，优化器通常会把 IN 子查询改写为 semi-join，实际执行的其实是类似 JOIN 的操作。一旦改写失败，就可能退化成“对每条 orders 都执行子查询”，效率惨不忍睹。遇到这种情况，手工改成 JOIN 更可控。

换成 NOT IN 要特别小心。NOT IN在子查询包含 NULL 值时，结果与预期不一致，这个属于语义问题。更麻烦的是 NOT IN 在部分 MySQL 版本中不走索引，导致全表扫描。建议写成NOT EXISTS配合关联条件，或者用 LEFT JOIN 加 IS NULL 的方式。

5.3 聚合查询：让临时表消失

GROUP BY 慢的根源，通常在于 MySQL 需要先建立临时表来分组聚合。最理想的做法是让分组字段走索引，这样 B+ 树天然有序，优化器可以直接顺序扫描完成分组，完全不需要临时表。

SELECT user_id, COUNT(*) FROM orders WHERE create_time > '2024-01-01' GROUP BY user_id;

最优方式是建一个(user_id, create_time)联合索引。这样 WHERE 条件过滤 create_time，GROUP BY user_id 又利用索引顺序，一次扫描完成。

如果 SQL 里既有 GROUP BY 又有 ORDER BY，还要保证排序字段与分组字段一致。不一致时额外加临时表排序，性能直接恶化。

5.4 利用 LIMIT 提前终止

有些带 EXISTS 的关联查询，业务上只需要判断“有没有”，不需要知道具体数量。这类查询加上LIMIT 1能显著减少扫描量。

SELECT 1 FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000 LIMIT 1 );

MySQL 在找到满足条件的第一行后会提前终止扫描，LIMIT 1 让这个终止时机足够早。注意这里子查询里的 SELECT 1 是性能优化习惯，生产环境不要用SELECT *，没有任何理由需要查出整行数据。

6. 优化器与统计信息：别让引擎帮你做错误决定

6.1 ANALYZE TABLE 的操作价值

MySQL 的优化器是基于统计信息选择执行计划的，统计信息来自mysql.innodb_table_stats和mysql.innodb_index_stats这两张系统表。统计信息有精度损失，更新频率也有限制。数据变更剧烈后，统计信息很可能和实际严重偏离，优化器选了错误的索引。

遇到“明明有合适的索引，EXPLAIN 里却不是最优计划”的情况，第一步不是改 SQL，而是刷新统计信息：

ANALYZE TABLE orders;

执行后重新 EXPLAIN，很多情况下问题自动消失。我曾经处理过一个线上案例，某张表的统计信息显示一个索引的区分度很低，优化器全表扫描。ANALYZE TABLE 之后，优化器立刻换成了正确索引，查询从 3 秒降到 20ms。这个动作的成本很低，值得作为调优的第一步试试。

6.2 优化器正确的代价估算逻辑

理解优化器为什么“乱选”索引，先说下它的估算逻辑。优化器估算执行代价时主要看两个因素：扫描行数和回表成本。对于普通索引，每扫描一行，都要按主键回表查询一次；如果回表的成本很高，优化器可能放弃索引而选择全表扫描。

这就是为什么提升索引选择性的“区分度”这么重要。如果索引列大量重复，比如 status 列只有两个值，区分度极低。优化器算了算，用索引需要大量回表，还不如全表顺序扫描来得快。结论是：区分度过低的列不适合单独建索引，但适合放在联合索引的后面列。

6.3 经典查询场景的优化器参数补充

MySQL 8.0 之前，通过optimizer_switch可以控制优化器部分特性，常见的有：

SET optimizer_switch = 'index_condition_pushdown=on'; SET optimizer_switch = 'block_nested_loop=on'; SET optimizer_switch = 'semijoin=on';

8.0 之后默认配置普遍够用，直接改开关的场景少了很多。但有一个参数在高并发场景值得关注：optimizer_trace。它能把优化器做决策的完整过程记录下来：

SET optimizer_trace='enabled=on'; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace='enabled=off';

这个功能展示的信息量非常大，包含优化器考虑过的每一条执行路径和成本计算过程。当 EXPLAIN 看不透“为什么没用这个索引”时，翻一下 OPTIMIZER_TRACE，答案往往就在里面，不用瞎猜。

7. 常见问题与排查技巧实录

把平时项目里高频出现的问题整理成一张速查表，方便参照：

除了表格里的内容，还有三个排查技巧是文档里看不到的，属于实战里积累出来的经验。

第一个是小结果集直接落内存的验证方法。优化完 SQL 之后，用相同条件的查询在低峰期实测，观察响应时间和SHOW PROFILE的输出。SHOW PROFILE 能看到每条 SQL 的完整执行耗时分布，包括 Sending data、Sorting result、Statistics 这些阶段。每个阶段的耗时占比能告诉你下一步优化该朝哪个方向走。

第二个是逐步缩小条件范围定位问题列。当一条多表 JOIN 的 SQL 很慢，但 EXPLAIN 看不出来明显问题，可以用“分段还原”的方法：先只查 A 表，再只查 B 表，再查 A JOIN B，逐步定位慢在哪一层。这个方法效率很高，特别是当优化器做了不理想的子查询展开时，单看整条 SQL 的执行计划很容易被误导。

第三个是利用通用日志做回归对比。优化前和优化后，把同一时段内的慢 SQL 数量做对比。MySQL 的log_queries_not_using_indexes参数开启后，能持续记录“没用索引的查询”。这个数据能用来评估优化效果，也能防止新上的功能悄悄引入新的慢查询。

8. 写在最后的体会

SQL 调优这件事，表面上是在调 SQL，实际上是在调“理解”。理解你的数据分布，理解优化器的行为逻辑，理解 InnoDB 的存储结构，理解业务到底需要什么。很多人拿一条 SQL 来问我，我反问他业务场景和数据量时，他答不上来。不掌握这些信息，调优就只能靠猜，靠试，效率一塌糊涂。

我个人的工作习惯是：接到一条慢 SQL，先看统计信息是否准确，再看执行计划，再分析索引设计，最后才动手改 SQL。绝大多数慢查询在这一步就能解决。真正需要重写 SQL 的，反而是少数。顺序反了的话，可能你辛苦改完一个写法，结果发现换个索引就能解决，白费功夫。

最后分享一个日常习惯：每次调优完，把慢 SQL、执行计划、修改内容和优化前后的耗时记录到文档里。时间长了，你会发现很多表、很多查询模式是有共性的。这份积累，比任何调优工具都值钱。