MySQL EXPLAIN

EXPLAIN 用来查看 SQL 的执行计划。它不会直接告诉你“最佳优化方案是什么”，但会告诉你 MySQL 准备怎么访问表、选哪个索引、估计扫描多少行、是否需要额外排序或临时表。

一句话理解：

EXPLAIN 是慢 SQL 排查的 X 光片，它能看到执行路径，但最终怎么治还要结合数据量、索引、业务查询一起判断。

为什么必须学 EXPLAIN

很多 SQL 在小数据量时都很快，但上线后数据量一大就慢。只看 SQL 文本很难判断数据库实际怎么执行。

flowchart TD
    A["SQL 看起来没问题"] --> B["线上数据量变大"]
    B --> C["扫描行数暴涨"]
    C --> D["排序 / 回表 / 锁等待变重"]
    D --> E["接口超时"]
    E --> F["用 EXPLAIN 定位执行路径"]

如果不会看 EXPLAIN，优化常常会变成：

1. 看见慢就随便加索引。
2. 加了索引但 SQL 还是不用。
3. 不知道慢在扫描、排序、回表还是临时表。
4. 不知道优化前后是否真的变好。

基本用法

explain
select id, title
from article
where author_id = 1001 and status = 1
order by publish_time desc
limit 20;

MySQL 8 还可以使用：

explain analyze
select id, title
from article
where author_id = 1001 and status = 1
order by publish_time desc
limit 20;

EXPLAIN 是优化器的预估计划，EXPLAIN ANALYZE 会实际执行 SQL 并给出真实耗时，所以生产环境要谨慎使用，尤其是会返回大量数据或修改数据的语句。

阅读顺序

不要从左到右机械读字段。建议按这个顺序看：

flowchart TD
    A["1. 看表访问顺序<br/>id / select_type / table"] --> B["2. 看访问类型<br/>type"]
    B --> C["3. 看索引选择<br/>possible_keys / key / key_len"]
    C --> D["4. 看扫描规模<br/>rows / filtered"]
    D --> E["5. 看额外动作<br/>Extra"]
    E --> F["6. 结合 SQL 和业务判断是否要改"]

核心判断不是“有没有用索引”这么简单，而是：

1. 扫描行数是否合理。
2. 使用的索引是否符合查询条件和排序。
3. 是否产生大量回表。
4. 是否需要额外排序或临时表。
5. 优化器估算是否可能偏差。

关键字段

字段	说明	关注点
id	查询执行层级标识	子查询、联合查询时重点看
select_type	查询类型	SIMPLE、PRIMARY、SUBQUERY、DERIVED
table	当前访问的表	多表 join 时看访问顺序
type	访问类型	判断扫描方式和索引利用程度
possible_keys	可能使用的索引	有候选索引不代表最终使用
key	实际使用的索引	为空通常表示没选中索引
key_len	使用索引的长度	可大致判断联合索引用到哪几列
ref	哪个列或常量与索引比较	看连接条件和等值条件
rows	预估扫描行数	越小通常越好，但只是估算
filtered	过滤后剩余比例估算	与 rows 一起估算结果规模
Extra	额外执行信息	排序、临时表、覆盖索引等

EXPLAIN 参数到底从哪里来

EXPLAIN 不是 MySQL 执行 SQL 后“统计出来”的结果，而是优化器在真正执行前，根据 SQL 语义、表结构、索引、统计信息和成本模型推导出来的执行计划。

flowchart TD
    A["SQL 文本"] --> B["解析器生成语法树"]
    B --> C["预处理器解析表、列、权限"]
    C --> D["优化器枚举访问路径"]
    D --> E["读取统计信息和索引信息"]
    E --> F["估算 rows、filtered、cost"]
    F --> G["选择成本较低的计划"]
    G --> H["输出 EXPLAIN 字段"]

可以把每个字段来源理解成下面这张表：

字段	主要来源	怎么产生	是否精确
id	SQL 查询块结构	解析 SQL 后按查询块、子查询、UNION 分配编号	结构性字段，基本确定
select_type	SQL 语法结构	判断是 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等	结构性字段，基本确定
table	优化后的访问对象	可能是真实表、派生表、临时结果、物化子查询	基本确定
partitions	分区表和条件	分区裁剪后决定访问哪些分区	依赖分区条件
type	优化器选择的访问路径	根据索引、条件、Join 方式选择 const/ref/range/ALL 等	计划结果，可能受统计信息影响
possible_keys	表结构和可用条件	根据 WHERE/JOIN 中能利用索引的条件列列出候选索引	候选，不代表会用
key	成本模型选择结果	优化器比较候选路径成本后选中的索引	计划结果，可能选错
key_len	索引定义和使用前缀	根据索引列类型、长度、是否可空、字符集估算使用字节数	计算值，不代表实际数据长度
ref	等值匹配右侧来源	显示索引列和常量、列、函数结果怎么比较	计划结果
rows	统计信息和选择性估算	根据表行数、索引基数、采样、直方图、范围估算得出	估算值，经常不准
filtered	剩余条件选择性估算	估算存储引擎取出的行经过 Server 层条件后剩多少比例	估算值
Extra	优化器和执行器策略	输出覆盖索引、ICP、临时表、排序、Join Buffer 等附加动作	计划提示

所以看 EXPLAIN 要有一个前提：结构类字段通常比较确定，成本和行数类字段都是估算。

统计信息从哪里来

rows、filtered、key 是否选择某个索引，核心都依赖统计信息。InnoDB 不会为了一个普通 EXPLAIN 把整张表扫一遍，它会用已保存或采样得到的统计信息来估算。

常见统计信息来源：

来源	作用	查看或维护方式
表行数估算	判断全表扫描成本	show table status like 'table_name'
索引基数 Cardinality	判断某个索引列区分度	show index from table_name
InnoDB 采样页	估算索引不同值数量和数据分布	analyze table table_name 更新
持久化统计信息	保存 InnoDB 表和索引统计	innodb_stats_persistent
直方图 Histogram	MySQL 8 用于记录非索引列或低区分字段分布	analyze table ... update histogram
表结构元数据	字段类型、长度、是否可空、索引列顺序	show create table

查看索引基数：

show index from article;

重点看：

字段	含义
Key_name	索引名
Seq_in_index	当前列在联合索引中的位置
Column_name	索引列
Cardinality	该索引前缀的不同值数量估算

例如表有 100 万行，idx_status(status) 的 Cardinality = 2，优化器会认为这个索引区分度很低，单独走它可能要读取大量记录再回表，所以可能不选。

更新统计信息：

analyze table article;

MySQL 8 可以创建直方图，帮助优化器理解数据分布：

analyze table article
update histogram on status, author_id with 100 buckets;

查看直方图：

select *
from information_schema.column_statistics
where schema_name = database()
  and table_name = 'article';

直方图适合数据分布很不均匀、优化器经常估错的字段。但不要无脑给所有字段建直方图，维护成本和统计信息复杂度也会上升。

rows 是怎么来的

rows 是优化器预估“访问当前表这一步大概要读取多少行候选记录”。它不是实际扫描行数。

全表扫描时

如果 type = ALL，rows 通常接近表行数估算值：

explain
select *
from article
where title like '%mysql%';

如果 title 没有可用全文索引或前缀索引，这类条件可能无法利用普通 B+Tree 精确定位，于是优化器估算需要扫描整张表。

主键或唯一索引等值时

explain
select *
from article
where id = 100;

如果 id 是主键：

字段	常见值
type	const
key	PRIMARY
rows	1

因为主键唯一，最多匹配一行。

普通索引等值时

假设：

create index idx_author on article(author_id);

查询：

explain
select *
from article
where author_id = 1001;

如果表估算 100 万行，idx_author 的 Cardinality 约为 1 万，优化器可能粗略认为每个作者平均：

1000000 / 10000 = 100 行

此时 rows 可能接近 100。

但真实业务可能很不均匀：头部作者有 10 万篇，普通作者只有几篇。如果没有更精确的统计信息，rows 就可能估错。

范围扫描时

explain
select *
from article
where publish_time >= '2026-01-01'
  and publish_time < '2026-02-01';

如果使用 idx_publish_time，优化器会估算这个索引范围覆盖多少索引记录。范围越大，rows 越大。

联合索引时

create index idx_author_status_time
on article(author_id, status, publish_time);

查询：

explain
select *
from article
where author_id = 1001
  and status = 1
  and publish_time >= '2026-01-01';

优化器会按联合索引前缀估算：

先估 author_id = 1001 命中多少
再估 author_id = 1001 and status = 1 命中多少
再估 publish_time 范围覆盖多少

如果统计信息只知道平均分布，而真实数据强烈倾斜，估算仍然可能偏差。

filtered 是怎么来的

filtered 表示：存储引擎按访问路径取出候选行后，MySQL Server 层再应用剩余条件，预计还能保留多少百分比。

可以粗略理解为：

当前表预计输出行数 ≈ rows * filtered / 100

例如：

type	key	rows	filtered
range	idx_author_status_time	10000	10.00

含义是：优化器预计先从索引范围读 10000 行候选记录，再经过剩余条件过滤后，大约保留 10%，也就是约 1000 行。

哪些条件会影响 filtered：

条件类型	说明
索引没用到的 WHERE 条件	例如联合索引范围列后面的条件
函数条件	例如 date(created_at)
非索引列过滤	存储引擎取行后再过滤
Join 条件	被驱动表关联后剩余过滤
统计信息	值分布估计会影响过滤比例

filtered 很低通常说明：虽然用了索引，但取出的候选行里大部分会被过滤掉，索引可能不够贴合查询条件。

key_len 是怎么来的

key_len 是优化器计划使用的索引前缀字节长度。它常用来判断联合索引大概用到了几列。

它不是索引文件真实读取了多少字节，也不是字段实际值长度，而是根据字段定义计算出来的最大可能使用长度。

影响 key_len 的因素：

因素	影响
字段类型	int 4 字节，bigint 8 字节，datetime 5 字节左右
字符串长度	varchar(50) 要按字符集最大字节数估算
字符集	utf8mb4 一个字符最多 4 字节
是否允许 NULL	可空列通常额外占 1 字节
变长字段	varchar 还需要长度字节
联合索引用到几列	多用一列，key_len 增加对应列长度

示例：

create table user_info (
  id bigint primary key,
  age int not null,
  city varchar(20) not null,
  status tinyint not null,
  key idx_city_status_age(city, status, age)
) engine = InnoDB default charset = utf8mb4;

查询 1：

explain
select *
from user_info
where city = '杭州';

查询 2：

explain
select *
from user_info
where city = '杭州'
  and status = 1;

如果查询 2 的 key_len 比查询 1 更长，通常说明联合索引多用到了 status 这一列。

注意：key_len 只能辅助判断，不能只靠它断定性能好坏。真正还要看 type、rows、filtered 和 Extra。

possible_keys 和 key 为什么不同

possible_keys 是“语法和索引结构上可能用的索引”，key 是优化器成本比较后最终选择的索引。

flowchart TD
    A["WHERE 和 JOIN 条件"] --> B["找出可能匹配的索引"]
    B --> C["possible_keys"]
    C --> D["估算每个索引成本"]
    D --> E["选择成本最低的访问路径"]
    E --> F["key"]

成本会考虑：

1. 预计扫描多少行。
2. 是否需要回表。
3. 是否能利用索引顺序避免排序。
4. 是否覆盖索引。
5. Join 顺序和被驱动表访问成本。
6. 统计信息估算结果。

所以 possible_keys 有值但 key = NULL 并不矛盾。优化器可能认为全表扫描更便宜。

type 是怎么决定的

type 是优化器选择访问路径后的结果，不是 SQL 语法直接决定的。

type	产生条件	来源判断
system	表只有一行或系统表特殊情况	表统计和优化规则
const	主键或唯一索引等值匹配常量	唯一性约束 + 等值条件
eq_ref	Join 时用唯一索引或主键匹配，每次最多一行	Join 条件 + 唯一索引
ref	普通非唯一索引等值匹配	普通索引 + 等值条件
range	使用索引做范围扫描	范围条件、between、>、in 等
index	扫描整棵索引	覆盖索引或按索引顺序全扫
ALL	全表扫描	无合适索引或全表更便宜

type 的好坏要结合数据量。range 扫 500 行很好，扫 500 万行也会很慢；index 覆盖索引扫少量页还行，扫完整大索引也可能很慢。

ref 是怎么来的

ref 显示当前索引和谁进行比较。

常见值：

ref	含义
const	和常量比较，例如 where user_id = 1001
db.table.column	Join 中和另一张表的列比较
func	和函数或表达式结果比较
NULL	没有等值引用，常见于范围扫描或全表扫描

示例：

explain
select *
from orders o
join user_info u on o.user_id = u.id
where o.status = 1;

如果 u.id 是主键，被驱动表 u 的 ref 可能显示 o.user_id，表示每条订单用 user_id 去用户表主键索引里查。

Extra 是怎么来的

Extra 是优化器和执行器对附加动作的描述。它不是单一来源，而是执行计划中的各种策略提示。

Extra	来源	说明
Using index	覆盖索引判断	查询字段都能从索引拿到，不需要回表
Using where	剩余条件过滤	存储引擎返回后还要在 Server 层过滤
Using index condition	ICP 优化	在存储引擎层用索引条件先过滤一部分
Using filesort	排序策略	不能完全按索引顺序返回，需要额外排序
Using temporary	分组或去重策略	需要临时表处理中间结果
Using join buffer	Join 策略	被驱动表没有很好利用索引，使用 Join Buffer

Extra 要结合 rows 看。小数据量 Using filesort 可能没问题；大数据量 Using temporary; Using filesort 就要重点关注。

EXPLAIN 和真实执行为什么不一样

普通 EXPLAIN 是预估，不执行 SQL。真实执行可能因为缓存、锁等待、IO、数据分布、参数值不同而表现不同。

工具	是否执行 SQL	看到什么
EXPLAIN	不真正执行普通 SELECT	优化器预估计划
EXPLAIN ANALYZE	会实际执行	实际耗时、实际行数、循环次数
慢日志	已执行 SQL 的记录	执行时间、锁时间、扫描行数等
optimizer_trace	不一定执行完整查询	优化器为什么这么选

MySQL 8 可以用：

explain analyze
select id, title
from article
where author_id = 1001 and status = 1
order by publish_time desc
limit 20;

生产环境谨慎使用，因为它会真的执行 SQL。

如果想看优化器为什么选某个索引，可以打开 optimizer_trace：

set optimizer_trace = 'enabled=on';

select id, title
from article
where author_id = 1001 and status = 1
order by publish_time desc
limit 20;

select trace
from information_schema.optimizer_trace;

optimizer_trace 会比较详细，适合排查“为什么有索引但不用”“为什么选错索引”。

怎么验证 rows 估算是否靠谱

面试和生产排查里经常会出现这种情况：

EXPLAIN rows 看起来不大，但接口还是慢；或者 rows 很大，但实际查询很快。

原因是 rows 只是优化器执行前的估算。要判断它靠不靠谱，需要把“预估计划”和“真实执行证据”对上。

推荐按下面的顺序验证：

flowchart TD
    A["发现 SQL 慢"] --> B["先看普通 EXPLAIN"]
    B --> C["看 rows / filtered / key / Extra"]
    C --> D{"估算是否符合直觉"}
    D -- "不符合" --> E["查统计信息和 optimizer_trace"]
    D -- "符合但仍慢" --> F["看慢日志和实际耗时"]
    F --> G["低风险环境执行 EXPLAIN ANALYZE"]
    G --> H["对比 estimated rows 和 actual rows"]
    H --> I["结合 Handler_read 判断访问模式"]
    I --> J["调整 SQL、索引或统计信息"]

1. 用 EXPLAIN ANALYZE 看真实行数和耗时

MySQL 8 的 EXPLAIN ANALYZE 会真的执行 SQL，并输出实际耗时、实际行数、循环次数。它能回答普通 EXPLAIN 回答不了的问题：

普通 EXPLAIN	EXPLAIN ANALYZE
预计读多少行	实际读了多少行
预计执行路径	实际按该路径执行后的耗时
看不到每步循环次数	能看到 loops
风险较低	会执行 SQL，生产要谨慎

示例：

explain analyze
select id, title
from article
where author_id = 1001
  and status = 1
order by publish_time desc
limit 20;

重点看三类信息：

信息	怎么理解
estimated rows	优化器预计行数
actual rows	实际产生的行数
actual time	每一步真实耗时

如果 estimated rows 和 actual rows 差距很大，通常说明统计信息、数据分布或条件选择性估算有问题。此时不要急着改 SQL，先确认统计信息是否过期，是否需要直方图，是否存在极端热点值。

2. 用慢日志看真实扫描量

慢日志记录的是已经执行过的 SQL，适合线上观察真实表现。

建议打开慢日志并关注：

show variables like 'slow_query_log';
show variables like 'long_query_time';
show variables like 'log_queries_not_using_indexes';

慢日志里常见字段：

字段	含义
Query_time	SQL 总耗时
Lock_time	等锁耗时
Rows_sent	返回给客户端的行数
Rows_examined	扫描或检查的行数

Rows_examined 和 Rows_sent 差距很大时，说明 MySQL 为了返回少量结果检查了大量数据。此时即使 Extra 出现 Using index，也不能说明一定快。

例子：

Rows_examined = 1000000
Rows_sent = 20

这通常意味着索引不够贴合、深分页、排序过滤顺序不合理，或者查询范围本身太大。

3. 用 Handler_read 指标观察访问模式

MySQL 会维护一些 Handler 计数器，可以粗略观察当前会话或全局的存储引擎访问方式。

查看当前会话计数：

show session status like 'Handler_read%';

常见指标：

指标	含义	排查含义
Handler_read_key	通过索引定位读取	通常表示用了索引点查或范围起点
Handler_read_next	按索引顺序读下一条	范围扫描、索引扫描增多
Handler_read_rnd_next	顺序读下一行	全表扫描或临时表扫描增多
Handler_read_first	读索引第一条	索引全扫可能出现

可以在测试环境这样观察一个 SQL 前后的变化：

show session status like 'Handler_read%';

select id, title
from article
where author_id = 1001
  and status = 1
order by publish_time desc
limit 20;

show session status like 'Handler_read%';

如果执行后 Handler_read_rnd_next 增长很大，可能存在全表扫描或临时表扫描；如果 Handler_read_next 增长很大，可能正在沿索引范围扫大量记录。

注意：Handler 指标不是万能真相，它是辅助证据。真正判断还要结合 EXPLAIN、慢日志、实际数据分布、SQL 条件和业务返回量。

4. 用 optimizer_trace 看优化器为什么这么选

当你看到“明明有索引，MySQL 却不用”时，普通 EXPLAIN 只能告诉你结果，optimizer_trace 可以告诉你优化器比较过哪些路径、为什么放弃某个索引。

典型关注点：

trace 内容	说明
candidate indexes	候选索引有哪些
rows estimation	每条路径估算扫描多少行
cost	访问路径成本
chosen	最终选择哪个路径
cause	某些路径为什么被放弃

如果 trace 显示走某个索引成本比全表扫描更高，常见原因是：

1. 条件选择性差，返回比例太高。
2. 二级索引回表成本太高。
3. 统计信息认为该索引区分度低。
4. 排序、分页、Join 顺序导致另一个索引更合适。

5. 常见判断场景

现象	可能原因	处理方向
rows 估算远小于实际	统计信息过期、数据倾斜、直方图缺失	analyze table，必要时建直方图
rows 不大但耗时高	锁等待、IO 慢、回表随机读、排序重	查慢日志、锁、Buffer Pool、磁盘
Using index 仍然慢	覆盖索引全扫、深分页、大范围扫描	缩小范围、游标分页、归档
possible_keys 有值但 key 为空	优化器认为全表扫描更便宜	看返回比例、选择性、回表成本
filtered 很低	取出很多候选后才过滤	调整联合索引，把高选择性条件前置
EXPLAIN 和线上表现不一致	参数值不同、缓存状态不同、并发锁不同	用真实参数、慢日志、压测验证

最终要形成一个习惯：EXPLAIN 给你优化器的想法，慢日志和 EXPLAIN ANALYZE 给你真实执行证据，optimizer_trace 给你选择原因，Handler_read 给你访问模式侧面证据。

type 访问类型

type 反映 MySQL 访问表的方式。常见从好到差大致如下：

type	含义	示例	评价
const	主键或唯一索引等值，最多一行	where id = 1	很好
eq_ref	join 中通过唯一索引匹配一行	订单 join 用户主键	很好
ref	普通索引等值匹配	where user_id = ?	常见可接受
range	索引范围扫描	where created_at >= ?	可接受，注意扫描范围
index	扫描整个索引	覆盖索引全扫	比全表小，但仍可能重
ALL	全表扫描	没有可用索引	大表要重点关注

注意：type = ALL 不一定永远错误。小表、返回大部分数据、统计类查询可能全表扫描更划算。优化要看业务和数据量。

Extra 常见提示

Extra	含义	是否一定要优化
Using index	覆盖索引，不需要回表	通常是好现象
Using where	存储引擎返回后还要过滤	常见，不一定有问题
Using index condition	索引下推 ICP	通常是优化
Using filesort	额外排序	大数据量要关注
Using temporary	使用临时表	分组排序场景要关注
Using join buffer	join 没很好利用索引	需要检查 join 条件

Using filesort 不是一定写磁盘，它表示不能直接利用索引顺序完成排序，需要额外排序过程。数据量小时可以接受，数据量大时要重点优化。

Demo：建索引前后对比

准备表：

create table article (
  id bigint primary key auto_increment,
  author_id bigint not null,
  status tinyint not null,
  publish_time datetime not null,
  title varchar(100) not null
) engine = InnoDB default charset = utf8mb4;

查询：

explain
select id, title
from article
where author_id = 1001 and status = 1
order by publish_time desc
limit 20;

没有合适索引时，可能看到：

字段	可能表现
type	ALL
key	NULL
rows	很大
Extra	Using where; Using filesort

添加联合索引：

alter table article
add index idx_author_status_time(author_id, status, publish_time);

再次查看：

explain
select id, title
from article
where author_id = 1001 and status = 1
order by publish_time desc
limit 20;

重点观察：

字段	期望变化
key	使用 idx_author_status_time
type	从 ALL 变成 ref 或 range
rows	明显下降
Extra	排序压力可能降低

为什么有索引但不用

flowchart TD
    A["SQL 有可用索引"] --> B{"优化器估算成本"}
    B -- "走索引更便宜" --> C["使用索引"]
    B -- "全表扫描更便宜" --> D["不使用索引"]
    D --> E["可能原因：返回比例高 / 统计信息偏差 / 索引选择性差"]

常见原因：

原因	示例	解释
返回比例太高	where status = 1，90% 都满足	走索引后大量回表，可能不如全表扫描
索引选择性差	性别、布尔字段	区分度太低
对索引列做函数	date(created_at) = ?	B+Tree 不能直接按函数结果定位
隐式类型转换	字符串字段用数字比较	可能导致索引不能正常使用
统计信息不准	数据分布变化大	优化器估算 rows 偏差

更新统计信息：

analyze table article;

不要一看到没用索引就强制 force index。强制索引可能短期有效，但数据分布变化后反而变慢。优先从 SQL、索引设计、统计信息判断。

Demo：函数导致索引效果变差

准备索引：

alter table article
add index idx_publish_time(publish_time);

不推荐：

explain
select *
from article
where date(publish_time) = '2026-01-01';

推荐：

explain
select *
from article
where publish_time >= '2026-01-01 00:00:00'
  and publish_time < '2026-01-02 00:00:00';

为什么推荐范围写法：索引里保存的是完整时间值，范围条件可以沿 B+Tree 找到起点并顺序扫描；date(publish_time) 需要对列计算后比较，破坏了直接定位能力。

多表 join 怎么看

多表查询时，重点看：

1. 谁先被访问。
2. 连接字段是否有索引。
3. 被驱动表是否通过索引查找。
4. 是否出现 Using join buffer。

示例：

explain
select o.id, o.amount, u.username
from orders o
join user_profile u on o.user_id = u.id
where o.created_at >= '2026-01-01'
  and o.created_at < '2026-02-01';

建议索引：

alter table orders add index idx_created_user(created_at, user_id);
-- user_profile.id 是主键，天然有索引

如果 orders 按时间范围先过滤出少量订单，再通过 user_id 关联用户主键，通常比先扫用户再查订单更合理。

商业案例：资产列表慢 SQL 怎么从 EXPLAIN 推到优化方案

下面用一个商业系统常见场景把 EXPLAIN 串起来：医疗资产平台的资产列表页，需要按租户、医院、状态、创建时间分页查询。

1. 表结构和原始 SQL

create table asset_info (
  id bigint primary key auto_increment,
  tenant_id bigint not null,
  hospital_code varchar(32) not null,
  asset_name varchar(100) not null,
  asset_type varchar(32) not null,
  status tinyint not null,
  created_at datetime not null,
  updated_at datetime not null,
  key idx_tenant_status (tenant_id, status),
  key idx_created_at (created_at)
) engine = InnoDB default charset = utf8mb4;

业务 SQL：

select id, asset_name, asset_type, status, created_at
from asset_info
where tenant_id = 10001
  and hospital_code = 'H001'
  and status = 1
order by created_at desc
limit 20;

可能看到的执行计划：

字段	可能值	说明
type	ref	用了普通索引等值匹配
possible_keys	idx_tenant_status,idx_created_at	两个索引从语义上都可能相关
key	idx_tenant_status	优化器选择了租户 + 状态索引
rows	120000	预计该租户该状态有 12 万候选行
filtered	10.00	预计再经过 hospital_code 过滤剩 10%
Extra	Using where; Using filesort	还要过滤医院，并额外排序

这条 SQL 的问题不是“完全没用索引”，而是“索引只解决了一部分条件”。它能按 tenant_id,status 找到候选，但还要在候选行里过滤 hospital_code，并对大量候选按 created_at 排序。

2. 为什么 idx_created_at 不一定更好

初学者容易想：既然 order by created_at desc，那走 idx_created_at 不就不用排序了吗？

不一定。因为 idx_created_at 是全表时间索引，它能按时间顺序读，但无法直接限定租户、医院和状态。它可能要从最新资产开始一路扫描，边扫边判断：

tenant_id 是否等于 10001
hospital_code 是否等于 H001
status 是否等于 1

如果系统里很多租户、很多医院，当前租户医院的数据只占很小比例，那么沿 idx_created_at 扫描可能要跳过大量不相关数据。

flowchart TD
    A["选择 idx_created_at"] --> B["按全表时间倒序扫描"]
    B --> C["逐行判断 tenant / hospital / status"]
    C --> D{"是否凑够 20 行?"}
    D -->|"否"| B
    D -->|"是"| E["返回结果"]

优化器会在“按过滤条件缩小范围再排序”和“按排序索引扫描再过滤”之间估算成本。统计信息不准或数据分布倾斜时，它可能选错。

3. 正确索引应该贴合过滤和排序

这个列表页的稳定访问模式是：

tenant_id 等值
hospital_code 等值
status 等值
created_at 排序
limit 20

更合适的联合索引：

alter table asset_info
add index idx_tenant_hospital_status_created
(tenant_id, hospital_code, status, created_at);

为什么这个顺序合理？

索引列	对应 SQL	作用
tenant_id	等值	先缩小到当前租户
hospital_code	等值	再缩小到当前医院
status	等值	再缩小到当前状态
created_at	排序	在小范围内按时间顺序读取

优化后可能看到：

字段	期望表现	说明
type	ref 或 range	能按联合索引定位
key	idx_tenant_hospital_status_created	使用贴合业务的索引
key_len	比原来更长	说明联合索引用到了更多前缀列
rows	明显下降	候选范围更小
Extra	不再出现大范围 Using filesort	能利用索引顺序返回

注意：如果 created_at 前面都是等值条件，MySQL 可以利用联合索引中 created_at 的顺序。如果在 created_at 前出现范围条件，例如 status > 0，后续排序利用就会受到影响。

4. 为什么不要把所有字段都放进索引

有人会继续问：既然查询字段有 asset_name、asset_type，要不要都放进索引做覆盖索引？

可以评估，但不要无脑加。覆盖索引能减少回表，但索引越宽，写入成本、页分裂成本、缓存占用都会增加。

收益 = 少回表带来的查询提升
成本 = 更大的索引空间 + 更慢的写入 + 更低的 Buffer Pool 命中率

适合考虑覆盖索引的条件：

条件	说明
查询频率很高	高频列表页、核心接口
返回列很少	只返回 id、状态、时间、短字段
写入频率可接受	不会因为索引变宽拖垮写入
数据量大且回表多	回表成本明显

不适合把大字段放进覆盖索引，例如描述、JSON、大文本、长名称字段。资产列表如果 asset_name 很长、写入频繁，可以先只建过滤排序索引，再用压测和慢日志判断是否需要覆盖。

5. 优化不是只看 EXPLAIN 一次

完整优化闭环应该是：

flowchart TD
    A["慢日志发现资产列表慢"] --> B["用真实参数 EXPLAIN"]
    B --> C["判断 key、rows、filtered、Extra"]
    C --> D["设计贴合过滤和排序的联合索引"]
    D --> E["测试环境加索引并 ANALYZE TABLE"]
    E --> F["再次 EXPLAIN 对比计划"]
    F --> G["压测或 EXPLAIN ANALYZE 验证真实耗时"]
    G --> H["观察上线后慢日志和 Rows_examined"]

上线后还要观察：

指标	目的
Rows_examined 是否下降	验证扫描量真的变小
P95/P99 是否下降	验证用户体验
写入耗时是否上升	验证新增索引副作用
Buffer Pool 命中率	验证索引变宽是否挤占缓存
磁盘空间	验证索引空间成本

6. 面试怎么答这类慢 SQL

标准回答可以这样组织：

我不会只看 SQL 有没有用索引，而是先看业务访问模式。比如资产列表按 tenant_id、hospital_code、status 过滤，并按 created_at 排序分页。EXPLAIN 如果显示只用了 tenant_id,status 索引，rows 很大且 Extra 有 Using filesort，说明索引没有同时覆盖过滤和排序。优化方向是设计联合索引 tenant_id,hospital_code,status,created_at，让等值条件缩小范围，再利用 created_at 顺序返回。优化后还要用真实参数重新 EXPLAIN，看 key、key_len、rows、filtered、Extra 是否改善，再结合慢日志 Rows_examined、P95/P99 和写入成本验证，而不是盲目加索引。

排查模板

以后看到慢 SQL，可以按这个模板分析：

1. SQL 的业务目标是什么？
2. where 条件有哪些？等值、范围、模糊、函数分别是什么？
3. order by / group by 字段是什么？
4. EXPLAIN 中 key 用了哪个索引？
5. type 是什么？rows 估算多少？
6. Extra 有没有 filesort、temporary、join buffer？
7. 查询字段是否导致大量回表？
8. 返回行数是否本身就很大？
9. 是否需要调整联合索引顺序或改写 SQL？
10. 优化后重新 EXPLAIN，并对比 rows、type、Extra。

小结

EXPLAIN 的重点不是背字段，而是建立判断链路：

1. 先看访问方式 type。
2. 再看索引选择 key。
3. 再看扫描规模 rows。
4. 再看额外动作 Extra。
5. 最后结合业务数据决定优化方案。

不要为了让执行计划“看起来漂亮”盲目加索引。真正目标是让线上 SQL 在真实数据量、真实访问频率下稳定、可解释、可维护。