mysql中EXPLAIN的执行计划与优化器的决策过程

来源：这里教程网时间：2026-02-28 20:38:48 作者：

EXPLAIN 输出的 key、key_len、rows 字段到底在反映什么

EXPLAIN

不是执行器日志，而是优化器生成的「预估执行计划」。它不运行 SQL，只模拟优化器如何选择索引、估算扫描行数、决定连接顺序。其中：

key
显示最终被选用的索引名——但注意，它可能是
NULL
（没走索引），也可能是
PRIMARY
（走了主键），还可能是
idx_name
（走了二级索引）。如果
key
和
WHERE
条件字段不匹配，大概率是索引失效或统计信息过期。
key_len
表示实际用到的索引字节数，不是字段长度。比如
VARCHAR(255)
定义为 utf8mb4，单字符最多 4 字节，但若条件是
WHERE name = 'abc'
，且
name
是联合索引第一列，则
key_len
可能是
12
（3 字符 × 4 字节），而不是 1020（255×4）；若条件含前缀模糊匹配（
LIKE 'abc%'
），
key_len
仍会计算，但
LIKE '%abc'
就不会走索引，
key_len
为
NULL
。
rows
是优化器基于表统计信息（
INFORMATION_SCHEMA.STATISTICS
或
ANALYZE TABLE
结果）估算的扫描行数，不是精确值。当
rows
远大于实际结果集（比如
SELECT COUNT(*)
只返回 10 行，但
rows=98765
），说明统计信息陈旧，需手动执行
ANALYZE TABLE t_name
更新。

type=ALL 和 type=range 的区别不只是“全表扫 vs 索引扫”

type

字段反映访问类型，但它的取值逻辑依赖于「是否用上索引」+「是否能做范围裁剪」+「是否覆盖查询所需字段」：

type=ALL
：表示全表扫描，哪怕加了
WHERE
条件，只要没命中可用索引（如函数包裹字段：
WHERE YEAR(create_time) = 2023
），就会回退到
ALL
。
type=range
：表示用了索引做范围扫描，但只适用于 B+Tree 索引的有序特性。例如
WHERE id BETWEEN 100 AND 200
、
WHERE status IN (1,2)
、
WHERE name > 'zhang'
。注意：如果联合索引是
(a,b,c)
，而条件只有
WHERE b = 2
，则无法利用该索引的有序性，
type
仍是
index
或
ALL
，不是
range
。容易忽略的一点：
type=index
表示遍历整个索引树（按索引顺序全扫），比
ALL
快（因索引更小、顺序 I/O 更好），但如果
SELECT *
且索引非覆盖，仍要回表，性能未必优于
ALL
（尤其当数据页缓存差时）。

Extra 字段里 “Using filesort” 和 “Using temporary” 意味着什么

这两个提示直接暴露了排序和分组操作未走索引，而是由 server 层临时处理：

Using filesort
：不是真写磁盘文件，而是 MySQL 在内存中用
qsort
排序，超限后才落磁盘。触发常见场景包括：
ORDER BY
字段不在索引最左前缀中、
ORDER BY
与
WHERE
条件用不同索引、
ORDER BY a DESC, b ASC
（混合方向，MySQL 8.0 前不支持索引满足）。
Using temporary
：表示创建了内部临时表，通常出现在
GROUP BY
、
DISTINCT
、
UNION
或某些
ORDER BY
场景。如果
GROUP BY
字段没索引，或索引不能覆盖
SELECT
列（导致无法用松散索引扫描），就必现此提示。MySQL 5.7+ 默认用
TempTable
引擎（内存哈希表），但一旦超出
tmp_table_size
或含大字段（
TEXT/BLOB
），就会转成磁盘
MyISAM
表，性能断崖下跌。二者常同时出现：
GROUP BY x ORDER BY y
若
x
和
y
不在同一个索引里，既建临时表又额外排序。

为什么 ANALYZE TABLE 后 EXPLAIN 结果变了，但查询没变快

更新统计信息能让优化器选更合理的执行路径，但不保证性能提升——因为优化器只看成本模型，不看真实 I/O 延迟或并发争用：

统计信息准确了，优化器可能从走
idx_a
改为走
idx_b
，但如果
idx_b
的叶子节点更稀疏（比如高基数字段 + 高碎片率），实际随机 I/O 次数反而上升。优化器默认假设所有数据页都在 buffer pool 中，但生产环境常有冷数据。此时
rows=1000
的
range
扫描，若对应 1000 个分散的磁盘页，比
rows=5000
的
ALL
扫描（顺序读）更慢。真正要验证效果，得用
SELECT ... INTO DUMPFILE
或
sys.schema_table_statistics
查看实际
innodb_rows_read
和
handler_read_next
，而不是只盯
EXPLAIN
的
rows
。

EXPLAIN FORMAT=TRADITIONAL
SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1 AND o.created_at > '2024-01-01'
ORDER BY o.amount DESC;

这类 JOIN 查询的执行计划，优化器决策链极长：先评估单表访问方式（