MySQL的Explain：Filtered，key_len，Extra信息解读

数据运维 2024-01-30 贤蛋大眼萌 手机阅读

在MySQL里，Explain都不会陌生，因为可以通过Explain查看SQL语句的预执行计划，这是目前分析SQL语句性能瓶颈常用的方式。Explain用法到MySQL8.0版本从简单方式延伸到EXPLAIN FORMAT=TREE，EXPLAIN FORMAT=JSON，EXPLAIN ANALYZE，EXPLAIN FOR CONNECTION多样化用法，并且输出内容更丰富。

MySQL的Innodb引擎是索引组织表，在Explain输出中SQL语句是否质量高，首先查看是否使用索引，还需要关注Filtered + Rows，key_len，Extra指标，最终体现在计算的COST成本中。
MySQL的Explain：Filtered，key_len，Extra信息解读-每日运维网
下面具体了解索引以外的，这些重点指标。

filtered

filtered是已筛选列表示按表条件筛选的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。从100开始递减的值表示过滤量的增加。rows显示检查的估计行数，rows×filtered显示与下表连接的行数。例如，如果行为1000，过滤后的行为50.00（50%），则与下表连接的行数为1000×50%=500。
MySQL的Explain：Filtered，key_len，Extra信息解读-每日运维网
用来估算与其他表连接时扫描的行数，row x filtered = 101762 x 0.10% = 100行。很多场景下，超过28%左右比例，MySQL自身会选择全表扫描。

key_len

索引使用长度的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时，判断该索引有多少部分被使用到非常重要。除此之外，一些长度长的索引，影响性能比较大。key_len只指示了where中用于条件过滤时被选中的索引列，是不包含Order by/Group by这一部分被选中的索引列。
每种类型所占的字节数如下：

类型 占用空间
char(n) n个字节
varchar(n) 2个字节存储变长字符串，如果是utf-8，则长度 3n + 2
tinyint 1个字节
smallint 2个字节
int 4个字节
bigint 8个字节
date 3个字节
timestamp 4个字节
datetime 8个字节
DECIMAL(M,D) M字节(D+2 , 如果M CREATE TABLE `test1` (
`id` bigint NOT NULL AUTO_INCREMENT,
`a` int NOT NULL,
`b` int NOT NULL,
`c` int NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_mult` (`a`,`b`,`c`)
) ENGINE=InnoDB;
mysql> EXPLAIN select * from test1 where a=1;
mysql> EXPLAIN select * from test1 where a=1 and b=2;
mysql> EXPLAIN select * from test1 where a=1 and b=2 and c=3;

MySQL的Explain：Filtered，key_len，Extra信息解读-每日运维网
EXPLAIN的结果“几乎“相同，都用到索引idx_mult,唯一不同的是key_len。根据索引长度key_len，可以推断多维索引使用了前缀索引机制。
idx_mult是二维索引KEY (a,b,c)，因此INT类型长度应该是4+4+4=12
a=1 key_len是4，a=1 and b=1 key_len是8 ,a=1 and b=2 and c=3 key_len是12，前面即“不完全”用到索引才能得到结果。

Extra

Extra列包含有关如何解析查询的附加信息，却又十分重要。按照目前提供的官方说明，起码有36个信息可参考。
几个常输出的附加信息。

附件信息 说明
Using where 表示使用了where条件搜索，但没有使用索引。
Using index 表示用到了覆盖索引，即在索引上就查到了所需数据，无需二次回表查询，性能较好。
Using filesort 表示使用了外部排序，即排序字段没有用到索引。）
Using temporary 表示用到了临时表，下面的示例中就是用到临时表来存储查询结果。
Using join buffer 表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。

还有其他日常很少见的附加信息
1.Zero limit
查询包含LIMIT 0子句，无法选择任何行。

mysql> EXPLAIN select * from employees01 where emp_no < 100 Limit 0;
+----+-------------+-------+------+。。。+----------+------------+
| id | select_type | table | rows |。。。|filtered | Extra |
+----+-------------+-------+------+。。。+----------+------------+
| 1 | SIMPLE | NULL | NULL |。。。| NULL | Zero limit |
+----+-------------+-------+------+。。。+----------+------------+
1 row in set, 1 warning (0.00 sec)

2.No tables used
查询没有FROM子句。

root@localhost: 12:05: [employees]> explain select 1;
+----+-------------+-------+------+。。。+----------+----------------+
| id | select_type | table | rows |。。。|filtered | Extra |
+----+-------------+-------+------+。。。+----------+----------------+
| 1 | SIMPLE | NULL | NULL |。。。| NULL | No tables used |
+----+-------------+-------+------+。。。+----------+----------------+
1 row in set, 1 warning (0.00 sec)

3.Distinct
MySQL正在寻找不同的值，因此在找到第一个匹配的行后，它将停止为当前行组合搜索更多的行。

4.unique row not found
对于SELECT这样的查询。。。FROM tbl_name，没有行满足表上UNIQUE索引或PRIMARY KEY的条件。

5.Scanned N databases
这表示在处理INFORMATION_SCHEMA表查询时执行的目录扫描次数。

6.Plan isn’t ready yet
EXPLAIN FOR CONNECTION会出现此值，当优化器尚未完成为在命名连接中执行的语句创建执行计划时。

7.Deleting all rows
对于DELETE，一些存储引擎（如MyISAM）支持以简单快捷的方式删除所有表行的处理程序方法。

总结

了解和解读Explain输出信息，对SQL语句优化非常有必要。

相关推荐
数据运维 2023-09-03 贤蛋大眼萌
数据运维 2023-04-15 贤蛋大眼萌
数据运维 2023-04-29 贤蛋大眼萌
数据运维 2023-04-26 贤蛋大眼萌
数据运维 2023-08-11 贤蛋大眼萌

站点声明：本站部分内容转载自网络，作品版权归原作者及来源网站所有，任何内容转载、商业用途等均须联系原作者并注明来源。

相关侵权、举报、投诉及建议等，请发邮件至E-mail：service@mryunwei.com

Copyright 每日运维网  浙ICP备2022017665号-3 基于WordPress | 由七牛云提供 CDN 加速
回到顶部