慢查询优化思路

1. 慢查询优化的核心思路

先判断慢在扫描、排序、回表、锁等待还是系统资源，再结合 EXPLAIN 看执行计划，最后做有针对性的优化，并用真实数据回归验证。

一个完整的排查顺序通常是：

先从慢查询日志、监控、performance_schema 定位最慢、最频繁、影响最大的 SQL。
先判断是“执行慢”还是“等待慢”。如果 Lock_time 很高，问题可能在锁，而不是索引。
对目标 SQL 执行 EXPLAIN，看访问类型、命中的索引、扫描行数、排序和临时表情况。
结合业务语义优化索引、改写 SQL、缩小结果集、缩短事务，必要时调整表结构和架构。
用 EXPLAIN、EXPLAIN ANALYZE 和线上指标回归，确认收益是否真实，副作用是否可接受。

2. 先判断慢在什么地方

只看“响应慢”还不够，必须先区分慢的类型。慢查询日志里的几个字段很关键。

证据	典型现象	可以得到的结论	优先优化方向
`Query_time` 很高，`Lock_time` 很低	SQL 执行时间长，但不是在等锁	大概率是扫描、排序、回表、聚合本身很重	看 `EXPLAIN`，重点看 `type`、`rows`、`Extra`
`Lock_time` 很高	SQL 本身不一定慢，可能是在等锁	大事务、热点行更新、范围更新、DDL 与 DML 冲突	查锁等待、缩短事务、减少锁范围
`Rows_examined` 远大于 `Rows_sent`	扫了很多，真正返回很少	索引不准、走错索引、条件不可索引、深分页	补联合索引、改写 SQL、避免大 `OFFSET`
`Rows_sent` 很大	返回结果集本身就很大	不是数据库“找得慢”，而是“搬得多”	减列、分页、异步导出、缓存
CPU 高、磁盘 I/O 高	常伴随 `Using filesort`、`Using temporary`	排序、聚合、临时表、全表扫描压力大	优化 `ORDER BY` / `GROUP BY` 索引，减少扫描

需要注意两点：

Rows_examined 是慢查询日志里非常重要的信号。它大，通常说明查询代价大。
Rows_examined 是服务层统计的检查行数，不等于存储引擎内部所有操作的完整成本，所以还要结合 EXPLAIN 一起看。

3. `EXPLAIN` 的核心字段怎么解读

3.1 字段本身代表什么

EXPLAIN 的重点不是背字段定义，而是看到字段后能立刻推导问题和动作。

字段	重点看什么	常见结论	典型优化动作
`id`、`select_type`	是否有子查询、派生表、`UNION`，执行层次是否复杂	`SUBQUERY`、`DERIVED` 多，说明查询被拆成多步执行	能改成 `JOIN` 就改，必要时拆分 SQL 或预计算
`table`	当前访问的是哪张表，别名是否符合预期	多表查询时可看出驱动表和被驱动表	让驱动表更小，被驱动表关联列有索引
`partitions`	是否只访问了命中的分区	如果分区表扫描了很多分区，说明分区裁剪失败	条件里带上分区键，避免对分区键做函数和隐式转换
`type`	访问类型是 `const`、`ref`、`range`、`index` 还是 `ALL`	`ALL` 是全表扫描，`index` 是全索引扫描，`range` 也可能扫很多	优先把 `ALL`、`index` 降到 `ref`、`range` 或更优
`possible_keys`	优化器认为哪些索引“可能可用”	有候选索引不代表最终会用	检查候选索引是否真的服务当前查询模式
`key`	实际用了哪个索引	`key = NULL` 说明没用索引；用了意料外索引，说明索引设计或统计信息有问题	补联合索引、更新统计信息、改写 SQL
`key_len`	联合索引用到了多长前缀	长度比预期短，说明联合索引后半段没用上	检查最左前缀、范围条件、隐式转换、函数计算
`ref`	索引列和什么比较，是 `const`、列还是 `func`	`const` 通常较好；`func` 要警惕表达式或类型转换	尽量让条件变成列与常量直接比较
`rows`	预计要扫描多少行	这是优化最值得盯的字段之一，越大越危险	让过滤更早发生，减少扫描范围
`filtered`	扫描到的行中，能保留下来的比例	`rows * filtered / 100` 可以估算传给下一步的行数	如果比例很低，说明当前索引过滤不够精准
`Extra`	是否排序、临时表、覆盖索引、连接缓冲等	很多性能问题最终都能在这里看到信号	按 `Extra` 的具体值做针对性优化

3.2 `type` 字段可以直接推导什么

type 是访问方式，不是“是否用了索引”的简单二元判断。很多人看到用了索引就放心，这是不对的。

`type`	含义	通常说明什么	优化建议
`const`、`eq_ref`	单行或唯一索引精确匹配	这是比较理想的访问方式	一般不是瓶颈，继续看是否还有排序、回表、锁等待
`ref`	非唯一索引的等值匹配	通常不错，但如果索引区分度低，仍可能扫描很多行	看 `rows` 是否仍然过大，必要时改成更精准的联合索引
`range`	范围扫描	常见于 `> < BETWEEN IN`	范围条件不一定慢，关键是看扫描区间是否过大
`index`	全索引扫描	本质上仍然是全量扫描，只是扫的是索引树	不要误判为“已经优化好了”，通常要继续减少扫描
`ALL`	全表扫描	典型危险信号	补索引、改写条件、减少返回范围

结论上要注意：

type = ref 不等于查询一定快，低区分度索引照样可能扫几十万行。
type = index 不等于“命中了索引所以没问题”，它仍可能是全量扫描。
type 必须和 rows、filtered、Extra 一起看，单独看不完整。

3.3 `Extra` 字段最值得关注的信号

`Extra`	可以得到的结论	常见问题	优化方向
`Using index`	使用了覆盖索引，不需要回表	通常是好事	保持必要字段在索引中，但不要把索引做得过宽
`Using index condition`	使用了 Index Condition Pushdown，先在索引层过滤一部分，再决定是否回表	比普通回表好，但通常还不是覆盖索引	如果是高频查询，可继续设计覆盖索引
`Using where`	存储引擎取到行后，MySQL 还要继续做条件过滤	不是坏事，但如果搭配 `ALL` / `index`，往往说明过滤发生得太晚	让过滤列进入联合索引前缀
`Using filesort`	`ORDER BY` 不能直接利用索引顺序，需要额外排序	常见于排序列没进索引，或过滤和排序没共用一个索引	让等值过滤列在前，排序列紧随其后
`Using temporary`	需要临时表保存中间结果	常见于 `GROUP BY`、`DISTINCT`、复杂排序	优化分组排序索引，必要时先过滤再聚合
`Using join buffer`	连接阶段用到了连接缓冲	常见于被驱动表关联列缺索引，或连接代价较高	给被驱动表关联列建索引，缩小驱动表数据集

这里有一个容易被误解的点：

Using filesort 并不一定表示“落磁盘排序”，它本质上表示“无法直接按索引顺序得到结果，需要额外排序步骤”。数据量小时可能在内存完成，数据量大时才会变成明显瓶颈。

4. 根据 `EXPLAIN` 字段，如何直接推导优化动作

4.1 `key = NULL`，`type = ALL`

这通常说明当前 SQL 没有走到有效索引，或者优化器认为全表扫描更便宜。

你可以优先检查：

WHERE 条件列是否根本没有索引。
是否对索引列做了函数、表达式、计算、隐式类型转换。
返回结果集是否太大，以至于优化器认为扫全表更划算。
统计信息是否过旧，导致优化器误判。

对应动作通常是：补索引、把条件改写成可索引形式、必要时执行 ANALYZE TABLE 更新统计信息。

4.2 `possible_keys` 有值，但 `key` 不是预期索引

这说明“有索引可用”，但“当前索引设计不够贴合查询模式”。

典型原因有：

单列索引很多，但缺少真正匹配 WHERE + ORDER BY + LIMIT 的联合索引。
当前索引区分度太低，比如单独给 status 这种低区分度列建索引。
优化器统计信息不准，误选了代价更高的索引。

优化方向通常不是强行 FORCE INDEX，而是先补正确的联合索引，再回归验证。FORCE INDEX 更适合临时止血，不适合当根治方案。

4.3 `key_len` 明显短于预期

这通常说明联合索引没有被完整使用。最常见的原因是：

不满足最左前缀。
中间列断了。
前面的列已经进入范围查询，后续列无法继续用于精确过滤。
条件发生了隐式类型转换或函数计算。

比如联合索引是 (a, b, c)，但 EXPLAIN 显示只用了 a，那优化就不是“再多建一个索引”，而是先改查询模式，让 a、b、c 能按顺序参与过滤或排序。

4.4 `rows` 很大，`filtered` 很低

这种组合非常典型，说明“先扫了很多，再丢掉大部分”。也就是说，过滤发生得太晚。

优化方向通常是：

把过滤性更强的列前置到联合索引前缀。
避免先扫低区分度索引再做二次过滤。
尽量让排序、分组也复用同一棵索引，减少中间结果集。

如果是多表 JOIN，还要结合 rows * filtered / 100 判断当前表到底向下一步传了多少行。

4.5 `Using filesort` 或 `Using temporary`

这类问题很多都不是“数据库不会排序”，而是“数据库只能先拿很多数据，再额外排序或聚合”。

优化时优先考虑：

ORDER BY、GROUP BY 的列是否能接在等值过滤列之后组成联合索引。
是否可以先缩小结果集，再做排序和聚合。
是否误用了 SELECT *，导致无法使用覆盖索引。
是否存在深分页，导致排序前必须扫描大量无效行。

4.6 `EXPLAIN` 看起来还行，但 SQL 仍然很慢

这时就要跳出“只看执行计划”的误区。常见原因有：

实际慢在锁等待，而不是执行计划。
估算行数不准，计划在当前数据分布下已经失真。
大量回表、网络传输、客户端处理、连接池排队把整体时间拉长了。

如果版本支持，优先用 EXPLAIN ANALYZE 看实际执行时间、实际返回行数、循环次数。MySQL 8.0.18 起支持 EXPLAIN ANALYZE，它比普通 EXPLAIN 更能发现“估算和真实执行不一致”的问题。

5. 慢查询的常见优化方向

5.1 索引优化

索引优化的核心不是“多建索引”，而是按查询模式建索引。

高频原则有三条：

高频 WHERE 条件优先进入联合索引。
等值匹配列通常放前面，排序列、范围列紧随其后。
高频读接口可以适当做覆盖索引，但要评估写放大和索引体积。

5.2 SQL 改写

很多慢查询不是因为 MySQL 差，而是 SQL 写法让优化器没法走最优路径。

典型改写包括：

避免 SELECT *，只查必要列。
避免在索引列上做函数、表达式和隐式类型转换。
避免大 OFFSET 深分页，改成基于主键或游标的分页。
复杂 OR 条件可视情况拆成 UNION ALL。

5.3 排序、分组与分页

ORDER BY、GROUP BY、DISTINCT 是慢查询高发区，因为它们很容易引入额外排序和临时表。

优化时要优先思考：

能不能让排序列直接走索引顺序。
能不能先过滤再排序，而不是先扫描大结果集再排序。
能不能先查主键集合，再回表拿详情。

5.4 事务与锁

不是所有慢 SQL 都是“查询计划差”。如果更新语句很慢，尤其要警惕锁等待。

典型问题包括：

大事务持锁时间过长。
范围更新没有命中索引，导致锁范围扩大。
热点行被高并发反复更新。

这类问题的优化方向往往是缩短事务、分批更新、命中索引、减少热点竞争，而不是继续给查询加索引。

5.5 表结构与架构

如果单表已经非常大，且业务访问模式天然分散，单机单表优化会越来越接近上限。

这时可以考虑：

冷热分离、归档历史数据。
反范式冗余，减少复杂 JOIN。
分区、分库分表、读写分离、缓存前置。

但这类方案属于最后一层手段。在大多数面试和真实线上场景里，先把索引和 SQL 优化到位，收益通常最大。

6. 完整案例：一条订单列表慢查询如何排查和优化

6.1 场景描述

订单表有几千万数据，商家后台有一个“最近订单列表”接口，只展示某个租户下状态为已支付的最近 20 条订单。接口 RT 很高，慢查询日志频繁出现。

表结构可以简化为：

CREATE TABLE orders (
  id BIGINT NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  create_time DATETIME NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  KEY idx_tenant_id (tenant_id),
  KEY idx_status (status),
  KEY idx_create_time (create_time)
);

6.2 慢查询日志给出的第一手证据

-- slow log 摘要
-- Query_time: 3.842157
-- Lock_time: 0.006321
-- Rows_sent: 20
-- Rows_examined: 482316
SELECT id, tenant_id, status, create_time
FROM orders
WHERE tenant_id = 1001
  AND status = 1
ORDER BY create_time DESC
LIMIT 20;

先根据日志做第一轮判断：

Lock_time 很低，说明主要不是锁等待。
Rows_sent 只有 20，但 Rows_examined 高达 48 万多，说明数据库为了拿 20 行，扫描了大量无效数据。
问题大概率出在索引选择、排序路径或结果集过滤过晚。

6.3 先执行 `EXPLAIN`

对原始 SQL 执行 EXPLAIN，得到如下示意结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	ref	idx_tenant_id,idx_status,idx_create_time	idx_status	1	const	420000	1.00	Using where; Using filesort

6.4 结合字段逐项推导问题

从这份 EXPLAIN 可以直接得到以下结论：

key = idx_status，说明优化器最终走的是 status 单列索引。
type = ref 看起来不差，但 rows = 420000 说明这个索引区分度非常差。也就是说，用了索引，不代表扫得少。
filtered = 1.00 说明扫到的 42 万行里，最后大约只有 1% 真正满足 tenant_id = 1001，过滤发生得很晚。
Extra = Using filesort 说明当前访问路径无法直接满足 ORDER BY create_time DESC，所以还要额外排序。

把这几个字段连起来看，本质问题就清楚了：

当前 SQL 先通过低区分度的 status 索引拿到大量候选行，再回表或继续过滤 tenant_id，最后还要对结果做额外排序。慢不在“没走索引”，而在走了错误粒度的索引。

6.5 优化方案

这个查询的真实访问模式是：

先按 tenant_id、status 做等值过滤。
再按 create_time 排序。
只取前 20 行。

因此更合理的索引应该服务这个完整模式，而不是只服务其中一个字段。

ALTER TABLE orders
ADD INDEX idx_tenant_status_ctime_id (tenant_id, status, create_time, id);

这个索引的好处有三点：

tenant_id、status 都是等值条件，能快速缩小扫描范围。
create_time 紧跟其后，可以直接利用索引顺序完成排序，避免 filesort。
查询只返回 id、tenant_id、status、create_time，这些列都在索引里，可以形成覆盖索引。

如果接口必须返回很多不在索引中的列，也不一定要把所有列都塞进索引。更常见的方案是先用这个索引拿到 20 个主键，再按主键回表取详情，这样回表成本仍然可控。

6.6 优化后再次执行 `EXPLAIN`

加完索引后，再看执行计划，示意结果如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	ref	idx_tenant_status_ctime_id	idx_tenant_status_ctime_id	9	const,const	20	100.00	Using index

这时可以得到新的结论：

key 命中了新的联合索引，说明访问路径已经贴合查询模式。
rows 从 42 万级降到 20，说明扫描量被压到了 LIMIT 数量级。
Extra 从 Using where; Using filesort 变成 Using index，说明排序和查询都在索引层完成，不需要额外排序，也不需要回表。

从结果上看，这条 SQL 的慢查询根因已经被定位并解决。

6.7 这类问题的一般化排查方案

以后遇到类似慢 SQL，可以按下面的模板排查：

先看慢查询日志，判断是扫描慢还是锁等待慢。
再看 EXPLAIN，重点看 key、type、rows、filtered、Extra。
如果“用了索引但仍然很慢”，优先怀疑索引区分度不够，或者联合索引不匹配真实查询模式。
如果看到 Using filesort，优先检查排序列能否接在等值过滤列之后进入联合索引。
如果看到 Rows_examined 很大但只返回少量行，优先减少扫描范围，而不是先调参数。
优化后必须重新 EXPLAIN，并用真实流量或真实数据量验证收益。

7. 面试时可以怎么回答

慢查询优化我一般按闭环来答。第一步先看慢查询日志和监控，区分是执行慢还是锁等待慢；如果 Rows_examined 很大、Lock_time 很低，说明主要是扫描和排序问题。第二步用 EXPLAIN 看执行计划，重点看 type、key、key_len、rows、filtered、Extra。比如 type = ALL 往往是全表扫描，key = NULL 说明没走索引，rows 很大说明扫描范围大，Using filesort 和 Using temporary 说明排序聚合路径不优。第三步再根据执行计划做优化，核心是让索引贴合真实查询模式，尤其是联合索引要同时服务 WHERE、ORDER BY、LIMIT，必要时改写 SQL、避免深分页、减少回表、缩短事务。最后重新看 EXPLAIN，有条件的话再用 EXPLAIN ANALYZE 和线上指标确认优化真实生效。