mysql5.5和mysql5.6.36_Mysql Explain 解读(基于MySQL 5.6.36)

最新推荐文章于 2022-09-16 16:45:56 发布

weixin_39640773

最新推荐文章于 2022-09-16 16:45:56 发布

阅读量176

点赞数

文章标签： mysql5.5和mysql5.6.36

本文链接：https://blog.csdn.net/weixin_39640773/article/details/113381015

版权

Mysql Explain 解读(基于MySQL 5.6.36)

1.语法

explain < table_name >

#例子

explain select * from t3 where id=3952602;

2.explain输出解释

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

2.1 ID

mysql执行计划中的ID

1. ID如果相同，可以认为是一组，从上往下顺序执行

2. ID不同,其中ID越大，优先级越高，越早执行

3. ID列为null的就表是这是一个结果集，不需要使用它来进行查询。

#例如:

mysql> explain select * from (select * from ( select * from sbtest1 where id=9901) a) b;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

3 rows in set (0.00 sec)

很显然这条SQL是从id=3 向上执行.

2.2 select_type

就是select类型,可以有以下几种

(1) SIMPLE

表示不需要union操作或者不包含子查询的简单select查询。

mysql> explain select * from sbtest1 where id=9901;

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.12 sec)

(2). PRIMARY

一个需要union操作或者含有子查询的select，位于最外层的查询的select_type即为primary。且只有一个

mysql> explain select * from (select * from sbtest1 where id=9901) a ;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

2 rows in set (0.00 sec)

(3).UNION

union连接的两个select查询，除了第一个表外，第二个以后的表select_type都是union

mysql> explain select * from sbtest1 where id=9901 union all select * from sbtest1 where id= 9902 ;

+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+

+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+

+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+

3 rows in set (0.01 sec)

(4).DEPENDENT UNION

与union一样，出现在union 或union all语句中，但是这个查询会受到外部表查询的影响

mysql> explain select * from sbtest1 where id in (select id from sbtest1 where id=9901 union all select id from sbtest1 where id= 9902);

+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-----------------+

+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-----------------+

+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-----------------+

4 rows in set (0.02 sec)

(5).UNION RESULT

包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

mysql> explain select * from sbtest1 where id=9901 union all select * from sbtest1 where id= 9902 ;

+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+

+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+

+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+

3 rows in set (0.01 sec)

(6).SUBQUERY

除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

mysql> explain select * from sbtest1 where id = (select id from sbtest1 where id=9901 ) ;

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

2 rows in set (0.00 sec)

(7). DEPENDENT SUBQUERY

与dependent union类似，表示这个subquery的查询会受到外部表查询的影响,

也就是说外部的查询会推到内部去！

比如下面的查询,外部查询做了一次索引扫面然后带入到子查询里

mysql> explain select id from sbtest1 a where not exists (select id from sbtest1 where id =a.id);

+----+--------------------+---------+--------+---------------+---------+---------+-----------------+--------+--------------------------+

+----+--------------------+---------+--------+---------------+---------+---------+-----------------+--------+--------------------------+

| 1 | PRIMARY | a | index | NULL | k_1 | 4 | NULL | 933711 | Using where; Using index |

+----+--------------------+---------+--------+---------------+---------+---------+-----------------+--------+--------------------------+

2 rows in set (0.05 sec)

(8).DERIVED

from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

mysql> explain select * from (select * from sbtest1 where id=9901) a ;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

2 rows in set (0.01 sec)

2.3 table

显示的查询表名。

1.如果查询使用了别名,那么这里显示的是别名

2.如果不涉及对数据表的操作，那么这显示为null。

3.如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id,

表示结果来自于这个查询产生。

4.如果是尖括号括起来的，与类似,也是一个临时表，

表示这个结果来自于union查询的id为M,N的结果集。

mysql> explain select * from (select * from ( select * from sbtest1 where id=9901) a) b;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

2.4 type

这列显示了连接的类别,有无使用索引等

依次从好到差：

system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，

index_merge，index，ALL。

除了all之外，其他的type都可以使用到索引，

除了index_merge之外，其他的type只可以用到一个索引

(1).system

表中只有一行数据或者是空表，且只能用于myisam和memory表。

如果是Innodb引擎表，type列在这个情况通常都是all或者index

mysql> explain select * from (select * from sbtest1 where id=9901) a ;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

2 rows in set (0.00 sec)

(2).const

使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。

因为仅有一行，在这行的列值可被优化器认为是常数.

onst表很快，因为它们只读取一次！

其他数据库也叫做唯一索引扫描

mysql> explain select * from sbtest1 where id=9901;

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.02 sec)

(3). eq_ref

出现在要连接多个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，

且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

mysql> explain select * from sbtest1 a ,sbtest2 b where a.id=b.id;

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------+

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------+

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------+

2 rows in set (0.01 sec)

(4).ref

不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现。

常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现。

总之，返回数据不唯一的等值查找就可能出现。

ref可以用于使用=或<=>操作符的带索引的列。

mysql> show create table customer \G;

*************************** 1. row ***************************

Table: customer

Create Table: CREATE TABLE `customer` (

`id` int(11) NOT NULL,

`customer_id` int(11) NOT NULL,

`datanode` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> show create table customer_addr \G;

*************************** 1. row ***************************

Table: customer_addr

Create Table: CREATE TABLE `customer_addr` (

`id` int(11) NOT NULL,

`customer_id` int(11) NOT NULL,

`customer_addr` varchar(200) DEFAULT NULL,

`datanode` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_customer_addr_customer_id` (`customer_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> explain select * from customer a ,customer_addr b where a.id =b.customer_id;

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+-------+

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+-------+

| 1 | SIMPLE | b | ref | idx_customer_addr_customer_id | idx_customer_addr_customer_id | 4 | db1.a.id | 1 | NULL |

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+-------+

(5).fulltext

全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价，优先选择使用全文索引

(6).ref_or_null

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。

在解决子查询中经常使用该联接类型的优化。实际用的不多。5.7版本貌似已经废弃了,

(7). unique_subquery

该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

子查询返回不重复值唯一值.

unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

mysql> explain select * from customer_addr where customer_id not in(select id from customer);

+----+--------------------+---------------+-----------------+---------------+---------+---------+------+------+-------------+

+----+--------------------+---------------+-----------------+---------------+---------+---------+------+------+-------------+

+----+--------------------+---------------+-----------------+---------------+---------+---------+------+------+-------------+

2 rows in set (0.23 sec)

(8).index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

mysql> explain select * from customer where id not in(select customer_id from customer_addr);

+----+--------------------+---------------+----------------+-------------------------------+-------------------------------+---------+------+------+-------------+

+----+--------------------+---------------+----------------+-------------------------------+-------------------------------+---------+------+------+-------------+

+----+--------------------+---------------+----------------+-------------------------------+-------------------------------+---------+------+------+-------------+

2 rows in set (0.00 sec)

(9).range

索引范围扫描，常见于使用>,

mysql> explain select * from sbtest1 where id between 1 and 10000;

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.09 sec)

(10). index_merge

该联接类型表示使用了索引合并优化方法。

表示查询使用了两个以上的索引,最后取交集或者并集,常见and,or的条件使用了不同的索引。

官方排序这个在ref_or_null之后，但是实际上由于要读取所有索引，性能可能大部分时间都不如range

mysql> explain select * from sbtest1 where id =9901 or k=99902;

+----+-------------+---------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

+----+-------------+---------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

+----+-------------+---------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

1 row in set (0.00 sec

(11).index

索引全扫描,该类型与ALL相同，只扫描索引,不用去读数据文件。通常比ALL快,因为索引文件通常比数据文件小。

可以使用索引排序或者分组的查询以及覆盖索引查询。

mysql> explain select count(*) from sbtest1 ;

+----+-------------+---------+-------+---------------+------------------+---------+------+--------+-------------+

+----+-------------+---------+-------+---------------+------------------+---------+------+--------+-------------+

+----+-------------+---------+-------+---------------+------------------+---------+------+--------+-------------+

1 row in set (0.00 sec)

mysql> explain select id from sbtest1;

+----+-------------+---------+-------+---------------+------------------+---------+------+--------+-------------+

+----+-------------+---------+-------+---------------+------------------+---------+------+--------+-------------+

+----+-------------+---------+-------+---------------+------------------+---------+------+--------+-------------+

1 row in set (0.00 sec)

(12). ALL

全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

需要避免的类型,效率最低。可以根据查询条件新增或者调整索引。

mysql> explain select * from sbtest1;

+----+-------------+---------+------+---------------+------+---------+------+--------+-------+

+----+-------------+---------+------+---------------+------+---------+------+--------+-------+

+----+-------------+---------+------+---------------+------+---------+------+--------+-------+

1 row in set (0.00 sec)

2.5 possible_keys

查询可能使用到的索引都会在这里列出来。

如果该列是NULL，则没有相关的索引。

在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。

2.6 key

MySQL实际使用的键(索引)。

select_type为index_merge时，这里可能出现两个以上的索引。

如果没有选择索引，键是NULL。

要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

2.7 key_len

MySQL实际使用的键(索引)长度。如果键是NULL，则长度为NULL。

使用的索引的长度。在不损失精确性的情况下，长度越短越好

如果是单列索引,那就是整个索引的长度

如果是多列索引,具体使用到了多少个列的索引,就计算多少长度进去

只计算where条件用到的索引长度，而排序和分组就算用到了索引,也不会计算到key_len中

2.8 ref

如果是使用的常数等值查询,这里会显示const

如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段

如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

2.9. rows

显示MySQL认为它执行查询时必须检查的行数。这里是估算值。

2.10 Extra

该列包含MySQL解决查询的详细信息,常用的有:

(1).Distinct

select部分使用了distinc关键字

一旦MYSQL找到了与行数据相匹配的行,就不再搜索

(2).Not exists

MYSQL优化LEFT JOIN，一旦它找到了匹配LEFT JOIN的行,就不再搜索

mysql> explain select a.* from customer a left join customer_addr b on a.id = b.customer_id where b.customer_id is null;

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+--------------------------------------+

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+--------------------------------------+

| 1 | SIMPLE | b | ref | idx_customer_addr_customer_id | idx_customer_addr_customer_id | 4 | db1.a.id | 1 | Using where; Not exists; Using index |

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+--------------------------------------+

2 rows in set (0.07 sec)

(3).Range checked for each

Record(index map:#)

没有找到理想的索引，因此对于驱动表每一行,MYSQL检查使用哪个索引，并用它来从表中返回行。

这是使用索引的最慢的方式, 但是比权标扫描快很多。

mysql> explain select a.* from customer a , customer_addr b where a.id >=b.customer_id ;

+----+-------------+-------+------+-------------------------------+------+---------+------+------+------------------------------------------------+

+----+-------------+-------+------+-------------------------------+------+---------+------+------+------------------------------------------------+

+----+-------------+-------+------+-------------------------------+------+---------+------+------+------------------------------------------------+

(4).Using filesort

排序时无法使用到索引，常见于order by和group by语句中,

此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，

然后排序关键字并按顺序检索行。

出现这个,查询就需要优化了,MYSQL需要进行额外的步骤来对返回的行排序。

它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行.

这并不代表着真的使用了文件排序！

mysql> explain select * from sbtest1 order by pad;

+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+

+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+

+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+

1 row in set (0.00 sec)

(5).Using index

查询无需回表，直接通过索引就可以获取查询的数据,即覆盖索引！

(6).Using temporary

表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，

执行计划中看不出来，需要查看status变量，

used_tmp_table，used_tmp_disk_table才能看出来。

发生这种情况一般都是需要进行优化的

mysql> explain select * from customer a left join customer_addr b on a.id = b.customer_id group by b.customer_id;

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+---------------------------------+

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+---------------------------------+

| 1 | SIMPLE | b | ref | idx_customer_addr_customer_id | idx_customer_addr_customer_id | 4 | db1.a.id | 1 | NULL |

+----+-------------+-------+------+-------------------------------+-------------------------------+---------+----------+------+---------------------------------+

2 rows in set (0.01 sec)

(7).Using where

表示查询返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。

查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，

然后server层根据检查条件进行过滤再返回真正符合查询的数据。

5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据，直接不读取,

这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition

(8).Using sort_union(...)/Using union(...)/Using intersect(...)

using intersect：表示使用and多个索引查询时,从处理结果获取交集

using union：表示使用or多个索引查询时,从处理结果获取并集

using sort_union和using sort_intersection：与前面两个对应的类似，

只是他们是出现在用and和or查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。

#使用sort_union函数来进行索引的合并

mysql> explain select * from sbtest1 where id = 9901 or k=9901;

+----+-------------+---------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

+----+-------------+---------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

+----+-------------+---------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

1 row in set (0.00 sec)

(9).Using index for group-by

表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。

注意,这里是非主键！

mysql> explain select k from sbtest1 group by k;

+----+-------------+---------+-------+---------------+---------------+---------+------+--------+--------------------------+

+----+-------------+---------+-------+---------------+---------------+---------+------+--------+--------------------------+

+----+-------------+---------+-------+---------------+---------------+---------+------+--------+--------------------------+

1 row in set (0.01 sec)

(10).using join buffer(block nested loop)，using join buffer(batched key accss)

5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

(11).firstmatch(tb_name)

5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询。

如果内表的数据量比较大，就可能出现这个

(12).loosescan(m..n)

5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中，

子查询返回的可能有重复记录时，就可能出现这个

2.11 filtered

使用explain extended时会出现这个列。

5.7之后的版本默认就有这个字段。不需要使用explain extended了。

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例,

注意是百分比，不是具体记录数。

weixin_39640773

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql5.5和mysql5.6.36_Mysql Explain 解读(基于MySQL 5.6.36)

Mysql Explain 解读(基于MySQL 5.6.36)1.语法explain < table_name >#例子explain select * from t3 where id=3952602;2.explain输出解释+----+-------------+-------+-------+-------------------+---------+---------+--...
复制链接

扫一扫