慢查询优化：从 EXPLAIN 开始排查

慢查询排查是个有固定套路的活：先从慢查询日志里找到嫌疑人，用 EXPLAIN 看它打算怎么执行，判断问题出在索引、写法还是表结构上，改完再验证。整条链路里，EXPLAIN 是承上启下的一步——它不能告诉你语句有多慢，但能告诉你为什么慢。这篇文章把这条链路的每一环拆开讲。

一、慢查询日志：先把嫌疑人找出来

MySQL 默认不记录慢查询，需要显式开启：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;        -- 超过 1 秒的语句记入日志
SET GLOBAL log_queries_not_using_indexes = OFF;  -- 大表上慎开，日志会爆炸

long_query_time 的取值看业务：在线交易类调到几百毫秒，后台批处理可以放宽到几秒。改这个参数只对新连接生效，已经建立的连接要用 SET SESSION 或重连。

日志内容一行一条语句，开头是执行时间、锁时间、返回行数、扫描行数，后面跟完整 SQL。mysqldumpslow 按总耗时、次数、平均耗时排序聚合，能快速找出”执行次数多、总耗时高”的语句，这类语句优化的收益最大。

扫描行数和返回行数的比值值得关注：返回 10 行却扫了 50 万行，基本可以确定索引没起作用或者过滤条件失效。日志里的 Rows_examined 是服务端实际扫描的行数，比 EXPLAIN 的估算值更可信。

开启 log_queries_not_using_indexes 要谨慎。开发环境开着能发现漏建索引的地方，生产环境在大表上开，日志量会很快失控，磁盘写满比慢查询本身更麻烦。

二、EXPLAIN 的关键字段

EXPLAIN SELECT ... 返回的是一行执行计划，关注这几列：

id / select_type：有几个查询块、是简单查询还是子查询或联合。
type：访问方式，从好到坏大致是 system/const > eq_ref > ref > range > index > ALL。出现 ALL 并且 rows 大，就是要处理的信号。
possible_keys / key：候选索引和实际选用的索引。possible_keys 有值但 key 是 NULL，说明优化器算出来用索引不如全表扫，通常是选择性太低或者统计信息不准。
key_len：用上了索引的前多少字节，可以反推联合索引里有几列参与了查找。
rows：估算扫描行数。优化器的估算值，看量级。
Extra：Using index（覆盖索引）、Using index condition（索引下推）、Using filesort（额外排序）、Using temporary（临时表）。

光看 EXPLAIN 不够时，用 EXPLAIN ANALYZE（MySQL 8.0.18+）或者 FORMAT=JSON 的输出，里面有更细的耗时信息，能看出时间花在哪一层。

三、几种典型的慢法

全表扫描是最常见的一种。原因通常很直白：条件列没建索引，或者写法破坏了索引可用性。排查顺序是先确认列上有索引，再确认写法没绕过它。

隐式转换是容易漏掉的一种。列是 VARCHAR，查询条件传了数字（WHERE phone = 13800138000），MySQL 会把列值转成数字再比较，索引失效。反过来列是数字、条件是字符串（WHERE id = '123'）多数情况下还能用索引，但还是写成类型一致更稳妥。

索引失效的原因见前一篇关于函数和写法的讨论：列上做计算、LIKE 左通配、OR 连接非索引列，都是常见触发点。

分页深翻是个结构性问题。LIMIT 100000, 20 的执行方式是先扫出前 100020 行再丢掉前 100000 行，页码越深越慢。几种改写思路：

-- 改写一：延迟关联，先拿主键再回表
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY created_at LIMIT 100000, 20) t
  ON o.id = t.id;

-- 改写二：记住上次的位置（需要稳定排序键）
SELECT * FROM orders WHERE created_at > '2026-10-01 12:00:00'
ORDER BY created_at LIMIT 20;

第一种把排序和过滤放在覆盖索引里完成，只对 20 行做回表；第二种是”游标分页”，代价是只能一页一页往后翻，不能直接跳到第 N 页。

子查询改写是另一类常见优化。相关子查询（外层每行都执行一次内层）在 MySQL 5.6 之前基本是灾难，新版本已经做了不少优化，但改写成 JOIN 或临时表往往还是更可控：

-- 相关子查询
SELECT * FROM orders o
WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE user_id = o.user_id);

-- 改写成 JOIN
SELECT o.* FROM orders o
JOIN (SELECT user_id, AVG(amount) AS avg_amt FROM orders GROUP BY user_id) t
  ON t.user_id = o.user_id AND o.amount > t.avg_amt;

四、SQL 重写的两个实用技巧

延迟关联（Deferred Join）刚才在分页里见过，思路可以推广：先用索引在小结果集上做筛选和排序，再回表取需要的列。核心是把”扫描大表”变成”扫索引”。

覆盖索引是让查询所需的所有列都出现在索引里，避免回表。实现方式通常是建联合索引，把 SELECT 的列包进去。代价是索引变宽、写入变慢，适合读多写少、查询模式固定的场景。

重写的时候多用 UNION ALL 替代 OR，用 EXISTS 替代大 IN 子查询（尤其是 IN 后面是大结果集时），用范围条件替代函数计算。这些都是等价改写，行为可预期。

五、统计信息与索引维护

优化器的决策依赖统计信息。统计信息不准时，优化器可能选错索引，表现为”明明有索引却不走”。

-- 手动刷新统计信息
ANALYZE TABLE orders;
-- 查看索引基数
SHOW INDEX FROM orders;

大批量导入、删除之后统计信息会滞后，执行一次 ANALYZE TABLE 成本不高，可以作为数据变更后的常规步骤。注意它会读取数据页做估算，超大表上频繁执行也不是免费的，按需。

索引本身也需要维护。长期运行的表会有碎片，OPTIMIZE TABLE 会重建表并整理碎片，代价是锁表和大量 IO，要在低峰期做。更平滑的做法是用 pt-online-schema-change 或原生的 ALTER TABLE ... ALGORITHM=INPLACE 做在线重建。

没被用到的索引要及时清掉。每个索引都拖慢写入，还占存储。sys.schema_unused_indexes（MySQL 5.7+）能列出一段时间内没被用过的索引，配合业务确认后再删。

六、什么时候该改表结构

SQL 优化是有天花板的。有些问题是写法层面的，改 SQL 就能解决；有些是模型层面的，改 SQL 只是延缓。判断标准：

字段选择性太低（比如 status 只有两三个值）却频繁作为唯一过滤条件：要么组合其他条件，要么接受全表扫描然后在应用层过滤，再么就是模型有问题。
一个字段要存多个值（比如逗号分隔的标签）却要按单个值查：FIND_IN_SET 走不了索引，这类需求本质上该拆成关联表或者用专门的检索方案。
统计类查询拖慢在线库：报表和 OLTP 混在一套实例里，再怎么优化 SQL 都是拆东墙补西墙。分离到只读副本或者离线库，是架构层面的解。
单行数据过大（几百 KB 的 TEXT/BLOB 字段夹在频繁查询的表里）：拆表或者把大字段挪到独立表，主表保持瘦。
热点行争用（比如库存扣减集中在一行）：这是设计问题，SQL 再快也解决不了锁等待，要改成分段扣减或者异步化。

改表结构的代价高，要上线、要回归、可能要停机窗口。所以顺序应该是：先用 EXPLAIN 确认 SQL 层面确实没有优化空间，再评估表结构和架构。反过来做，容易在模型上过度设计，然后发现 SQL 里还有没用上的索引。

排查慢查询这件事，经验的价值体现在”知道该怀疑什么”。全表扫描先查索引，有索引不走先查写法和统计信息，写法没问题再看模型。走完这三步，大部分问题都能收敛到一个具体的、可执行的改动上。