mysql的explain学习笔记

最新推荐文章于 2025-08-04 23:14:52 发布

jjrenwu003

最新推荐文章于 2025-08-04 23:14:52 发布

阅读量633

点赞数 22

CC 4.0 BY-SA版权

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/jjrenwu003/article/details/149286154

一、explain参数和详解

1.explain 查询结果

EXPLAIN SELECT * FROM `springboot_yuemo`.`user_info` WHERE `userid`=2;

filtered列的作用，rows*filtered/100,用来估算出将要和explain中前一个表进行连接的行数，在预处理的时候估算大概会输出多少数据

2.show warnings;结果

/* select#1 */ select '2' AS `userid`,'Bob Johnson' AS `username`,'bob@example.com' AS `email`,'34' AS `age`,'Male' AS `gender`,'2025-07-11 17:12:46' AS `created_at` from `springboot_yuemo`.`user_info` where true

其结果是将原来的sql优化为这样的sql，在优化器的时候使用。

3.嵌套语句查看

SET SESSION optimizer_switch='derived_merge=off';
explain SELECT *
FROM (
-- 子查询der：选择所有用户ID（可扩展为选择其他字段）
SELECT userid FROM user_info
) AS users
WHERE EXISTS (
-- 过滤条件：只保留在订单表中存在记录的用户
SELECT 1 FROM user_orders WHERE user_orders.userid = users.userid
);

4.这些参数的具体意义，到底有什么用

id参数

表示语句执行的优先级，id越大优先级越高， id相同则谁排前面谁先执行。

select_type参数

含义：查询类型，指示查询是简单查询还是复杂查询的一部分。
常见值：
SIMPLE：简单查询（不包含子查询或 UNION）。
PRIMARY：主查询（包含子查询的外层查询）。
SUBQUERY：子查询。
DERIVED：派生表（FROM 子句中的子查询）。
UNION：UNION 操作的第二个及后续查询。

table参数

含义：当前行正在处理的表名。

type参数

表示关联类型或者访问类型，即mysql查表的行，反映了查询使用索引的效率：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

possible_keys参数

可能使用的索引列表。

key参数

查询的时候用到的索引，优先选择二级索引，

若 key 显示为 idx_userid，说明查询实际使用了名为 idx_userid 的索引；
若 key 为 NULL，则说明查询未使用索引（需警惕是否存在性能问题）。

key_len参数

参数长度，越短效率越高

ref参数

含义：显示哪些列或常量被用于与索引进行比较。

rows参数
含义：MySQL 估计为了执行查询需要扫描的行数。这个值只是一个估计，可能与实际情况有很大差异。

Extra参数
含义：包含额外的查询执行信息，可能包含以下重要提示：
Using filesort：需要额外的排序操作，通常意味着性能问题。
Using temporary：使用临时表来处理查询，通常出现在 GROUP BY 或 ORDER BY 操作中。
Using index：使用了覆盖索引，这是高效的。
Using where：使用了 WHERE 子句进行过滤。
Range checked for each record：性能较差的查询，需要为每一行检查索引范围。

二、实例说明

explain SELECT * FROM `user_info`;

其type 为ALL，这是全索引扫描，从b+树叶子节点从链表中扫描，其效率最低。

explain SELECT userid,email FROM user_info;

其type 为index，通过email索引树直接获取数据，无需回表。

key_len=403,utf8mb4每个字符长度4字节，存储长度需要占位2字节，允许为空的列需要1字节存储NULL标志，结果 4*100+2+1=403

key为email ，它这里为什么key是email而不是userid呢？

这里涉及到主键索引其叶子节点缓存的是整行数据，而用email做的二级索引，其隐式包含了userid，且二级索引包含了所有需要查询的字段，所以采用二级索引查询的数据更少，效率更高。mysql优化器会优先选择更小更紧凑的覆盖索引，避免回表或者扫描更大的索引结构。

对比项	ALL（全表扫描）	INDEX（索引扫描）
数据访问方式	逐行扫描表数据	遍历索引树
是否需回表	是（直接访问数据行）	否（索引包含所有查询列）
性能	最差（数据量大时明显变慢）	较好（依赖索引大小）
适用场景	小表、无合适索引或全量数据查询	查询列被索引覆盖、需快速定位数据

索引B+树的非叶子节点是否存入缓冲区是由缓冲区大小决定的，如果大小足够，会直接将非叶子节点常驻内存，如果不够，会通过LRU变体算法来决策，所以合理配置缓冲池和优化查询模式，可以最大限度减少磁盘 IO，提升性能。

LRU将缓冲池分为两部分：

年轻区域（New Sublist）：约占缓冲池的 5/8，存放最近访问的热数据。

老区域（Old Sublist）：约占缓冲池的 3/8，存放较少访问的冷数据。

页淘汰决策如下

新页加载：
新读取的页首先进入老区域的头部，而非直接进入年轻区域。
页访问：
- 如果页在老区域被访问且停留时间超过 innodb_old_blocks_time（默认 1000ms），则移至年轻区域头部。
- 如果页在年轻区域被访问，仅移动到年轻区域头部（否则保持原位）。
页淘汰：
当缓冲池满时，优先从老区域尾部淘汰页（即最久未被频繁访问的页）。