MySQL EXPLAIN 与慢查询优化|bestows-Blog

30 秒回答

EXPLAIN 是用来判断 SQL 执行计划的工具，面试重点看 5 个字段：type、key、rows、Extra、possible_keys。优化慢查询的顺序一般是：先确认慢 SQL 和执行频率，再看执行计划是否走对索引，然后检查扫描行数、回表、排序、临时表、锁等待和数据量增长。不要一上来就加索引，先判断慢是因为索引问题、数据量问题、锁问题还是架构问题。

一、慢查询排查流程

1. 定位慢 SQL

常见来源：

慢查询日志：slow_query_log、long_query_time
业务监控：接口 P95/P99、数据库耗时埋点
APM / Trace：接口链路里哪个 SQL 慢
MySQL 状态：SHOW PROCESSLIST 看正在卡住的 SQL

面试回答：

我会先确认慢查询是偶发还是持续，是单条 SQL 慢还是整体数据库慢。单条 SQL 慢看 EXPLAIN 和数据分布；整体慢还要看连接数、CPU、IO、锁等待、Buffer Pool 命中率。

2. 查看执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY created_at DESC LIMIT 20;

重点不是背字段，而是会判断风险：

字段	关注点
`type`	访问类型，至少应尽量达到 `range`，最好 `ref/const`
`possible_keys`	优化器认为可能使用的索引
`key`	实际使用的索引
`rows`	预估扫描行数，越大越危险
`filtered`	过滤比例，低说明扫描了很多无效行
`Extra`	是否出现临时表、文件排序、回表优化等信息

二、type 字段怎么判断

从好到差：

type	含义	面试判断
`system/const`	表只有一行或主键/唯一索引等值查询	很好
`eq_ref`	join 时被驱动表通过主键/唯一索引匹配一行	很好
`ref`	普通索引等值查询	常见且可接受
`range`	范围扫描，如 `>`, `<`, `between`, `in`	可接受，注意扫描行数
`index`	扫整个索引树	比全表好一点，但仍可能很慢
`ALL`	全表扫描	高风险

面试追问：`index` 一定比 `ALL` 好吗？

不一定。index 是扫描整棵二级索引，索引比整行小，所以可能少读一些数据；但如果扫描量很大，仍然慢。优化不能只看 type，还要看 rows、是否回表、是否排序和实际耗时。

三、Extra 高频信息

Extra	含义	优化方向
`Using index`	覆盖索引，不需要回表	好现象
`Using where`	存储引擎返回后还要过滤	看过滤比例
`Using index condition`	索引下推 ICP	通常是好事
`Using filesort`	需要额外排序	建联合索引匹配排序，或减少排序数据量
`Using temporary`	使用临时表	常见于 group/order/distinct，风险较高
`Using join buffer`	Join 没用好索引	给被驱动表连接字段加索引

`Using filesort` 是磁盘排序吗？

不一定。它表示不能直接利用索引有序性，需要额外排序。可能在内存里排，也可能数据量大时落盘。真正要看排序数据量、sort_buffer_size、是否需要回表。

四、为什么索引失效

高频场景：

对索引列使用函数：where date(created_at) = '2026-06-29'
隐式类型转换：字符串列用数字比较
联合索引不满足最左前缀
范围条件后的列无法继续用于有序定位
like '%abc' 前缀不固定
or 两侧有一侧没有索引
低选择性字段单独建索引收益小，如性别、状态
优化器认为全表扫描更便宜

示例：

-- 差：对列做函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-29';

-- 好：改成范围
SELECT * FROM orders
WHERE created_at >= '2026-06-29 00:00:00'
  AND created_at <  '2026-06-30 00:00:00';

五、联合索引怎么设计

设计口诀：

等值字段在前，范围字段靠后，排序字段尽量接上，选择性和业务频率一起看。

例如订单列表：

SELECT order_id, status, amount, created_at
FROM orders
WHERE user_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

可考虑：

CREATE INDEX idx_user_status_created
ON orders(user_id, status, created_at);

好处：

user_id/status 用于快速过滤
created_at 可利用索引顺序减少排序
如果查询字段都在索引里，可变成覆盖索引

联合索引常见误区

不是选择性最高的字段永远放第一，而是要结合查询条件是否等值、是否排序、是否高频。
where a = ? and b > ? order by c 中，b 是范围，c 通常很难继续用于排序。
建太多索引会拖慢写入，因为每次插入/更新都要维护索引树。

六、深分页怎么优化

问题 SQL：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

为什么慢：

MySQL 需要扫描前 1000000 + 20 条
如果查询 *，可能还要大量回表

优化方式：

1. 基于游标翻页

SELECT * FROM orders
WHERE id > ?
ORDER BY id
LIMIT 20;

适合时间线、订单列表、消息列表。

2. 延迟关联

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

先用覆盖索引找主键，再回表取完整数据。

3. 业务限制

搜索页不允许无限翻页，超过一定页数改为条件筛选。

七、count 怎么优化

count(*) 在 InnoDB 中通常需要扫描索引或表，不能像 MyISAM 一样直接返回精确行数。

常见方案：

方案	适用场景	风险
`count(*)` 扫描较小二级索引	数据量不大或低频统计	大表慢
单独计数表	订单数、点赞数、库存数	需要处理一致性
缓存计数	展示型统计	允许短暂不一致
估算值	后台报表、分页总数	不精确

面试表达：

如果是强一致计数，用数据库事务维护计数表；如果只是展示，用 Redis 缓存或异步聚合；如果是后台报表，走离线数仓或定时统计，不要在主库高频 count 大表。

八、慢查询不一定是索引问题

还可能是：

锁等待：事务长时间不提交
连接池耗尽：应用侧拿不到连接
Buffer Pool 命中率下降：热数据装不下
磁盘 IO 高：刷脏页、redo/binlog 写入压力
主从延迟：读请求落到延迟从库
大事务：undo 膨胀、锁持有时间长
SQL 返回数据太多：网络传输和对象反序列化慢

排查命令：

SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS;
EXPLAIN ANALYZE SELECT ...;

MySQL 8.0 可用 EXPLAIN ANALYZE 看实际执行耗时，不只是预估。

面试官可能继续追问

为什么加了索引还是慢？
可能索引选择性低、扫描行数仍大、需要回表、需要排序、统计信息不准、优化器没选该索引，或者慢在锁等待/IO/网络。
覆盖索引为什么快？
二级索引叶子节点已经包含查询所需字段，不需要再通过主键回表读聚簇索引，减少随机 IO 和 Buffer Pool 压力。
如何判断是否需要建联合索引？
看高频查询模式，不为低频临时 SQL 建索引。联合索引应服务过滤、排序、覆盖中的至少一个核心目标。
线上能不能直接加索引？
大表加索引可能耗时、占 IO、影响写入。要看版本是否支持 online DDL，低峰执行，必要时用 gh-ost/pt-online-schema-change。
索引越多越好吗？
不是。索引占空间，降低写入速度，增加优化器选择成本。高频读路径才值得建。

项目结合

电商订单查询

订单表常见查询：

用户查订单列表：user_id + created_at
商家查待发货订单：seller_id + status + created_at
根据订单号查详情：唯一索引 order_no
支付回调幂等：唯一索引 payment_no 或 biz_id

回答时可以说：

我不会只按字段建索引，而是按接口查询模式建索引。比如用户订单列表按 user_id,status,created_at 建联合索引，既能过滤用户和状态，又能按创建时间排序。支付回调这类幂等场景则用唯一索引兜底。

易错点

只看 key 不看 rows。
看到 Using filesort 就以为一定落盘。
低选择性字段单独建索引。
为所有查询都建索引，导致写入变慢。
深分页仍然用 limit offset 硬扛。
没区分 SQL 慢和锁等待慢。