explain使用方法及结果分析

最新推荐文章于 2024-05-31 11:11:09 发布

饮风丶欤

最新推荐文章于 2024-05-31 11:11:09 发布

阅读量522

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/weixin_42867178/article/details/129060452

版权

explain使用方法及结果分析
1. 什么是`explain`命令
2. 如何使用`explain`命令
3. 分析`explain`命令执行结果
3.1. `id`
3.2. `select_type`
3.3. `table`
3.4. `type`
3.5. `possible_keys`
3.6. `key`
3.7. `key_len`
3.8. `ref`
3.9. `rows`
3.10. `filtered`
3.11. `Extra`

1. 什么是explain命令
explain命令是查看MySQL查询优化器如何执行查询的主要方法，可以很好地分析SQL语句的执行情况。

每当遇到执行慢的SQL，就可以使用explain命令来检查SQL的执行情况，并根据运行结果进行分析，采用相应的方法对SQL语句进行优化。

通过explain我们可以获得以下信息：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

2. 如何使用explain命令
只需在SQL语句前使用explain关键字即可；

如：

EXPLAIN SELECT * FROM student_info WHERE student_id = 'A01234567';

3. 分析explain命令执行结果
在执行了explain命令后，会得到一个含有很多列的输出结果，下面一起来看一下各个列所代表的含义吧。

3.1. id
如果多行id相同，执行顺序由上至下；
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
如果多行id有的相同有的不同，那么id相同的可以认为是一组，同一组中从上往下执行；id大的组优先执行；

3.2. select_type
select_type所显示的是SELECT的类型：

SIMPLE：简单的SELECT，没有使用UNION或者子查询；
PRIMARY：最外层SELECT；
UNION：第二层，在SELECT之后使用了UNION；
DEPENDENT UNION：UNION语句中的第二个SELECT，依赖于外部子查询；
UNION RESULT：UNION的结果；
SUBQUERY：子查询中的第一个SELECT；
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询；
DERIVED：导出表的SELECT（FROM子句的子查询）；

3.3. table
显示对应行正在访问哪个表，通常是表名或者该表的别名（如果SQL定义了别名）;

3.4. type
type所显示的是查询使用了哪种类型，所有type按照从好到坏的顺序排列如下：
system > const > eq_ref > ref > range > index > all；

system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计；
const：表示通过索引一次就找到了，const用于primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where语句中，MySQL就能将该查询转换为一个常量；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体；
range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between, <, >, in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引；
index：全表扫描，index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）；
all：全表扫描，将遍历全表以找到匹配的行；

3.5. possible_keys
这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

3.6. key
显示MySQL决定采用哪个索引来优化对该表的访问。如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

如果该索引没有出现在possible_keys列中，那么MySQL选用它是出于另外的原因如可能选择了一个覆盖索引，哪怕没有where子句。

换言之，possible_keys揭示了哪一个索引有助于高效行查找，而key显示的是优化采用的哪一个索引可以最小化查询成本。

3.7. key_len
显示了MySQL在索引里使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

3.8. ref
显示哪个字段或常数与key一起被使用。

3.9. rows
表示MySQL预估的为了找到所需的行而要读取的行数。根据表的统计信息和索引的选用情况，这个估算可能很不精确。通过把所有rows列值相乘，可以粗略的估算出整个查询会检查的行数。越小越好。

3.10. filtered
这一列是MySQL 5.1里新加的，它显示的是针对表里符合某个条件（where子句或联结条件）的记录数所做的一个悲观估算的百分比。

3.11. Extra
这一列包含的是不适合在其他列显示的额外信息。常见的最重要的值有：

Using index表示MySQL将使用覆盖索引，以避免回表查询。不要把覆盖索引和index访问类型混淆了；
Using where表示MySQL服务器将在存储引擎检索行后再进行过滤；
Using temporary表示MySQL在对查询结果排序时会使用一个临时表；
Using filesort表示MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。MySQL有两种文件排序算法，两种都可以在内存或磁盘上完成。但explain不会告诉你MySQL使用的是哪种，也不会告诉你排序是在内存还是磁盘上完成；
Range checked for each record（index map：N）表示没有好用的索引，新的索引将在联接的每一行上重新估算。N是显示在possible_keys列中索引的位图，并且是冗余的；
Using union表示MySQL运用了索引合并策略，索引合并策略有时是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕。