MySQL系列之Explain（五）

mysql执行计划，使用explain关键字可以模拟优化器执行sql语句，分析查询语句或者结构的性能瓶颈

在select语句之前增加explain关键字，mysql会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行sql语句

explain select * from dz where tp_dm = '26' and tp_bh = '00002';

注意：如果from中包含子查询，则是将执行子查询的结果放入临时表中

id列

id列的编号是select的序号列，有几个select就有几个唯一的id，id的顺序是按select出现的顺序增长的。id列值越大执行优先级越高越先执行，id相同则从上往下执行，id为null最后执行。

根据id可以判断优化器是否重写了sql(比如子查询重写为连接查询)。

select_type列

查询级别，表示对应行是简单还是复杂的查询，查询中每个select子句的类型

simple：简单查询，表示查询不包含子查询和union查询

primary：复杂查询中最外层的select (包含子查询)

subquery：子查询中的第一个select（不在from子句中）

derived：包含在from子句中的子查询，mysql会将结果存放在一个临时表中，也称为派生表。

union：表示此查询是union的第二或随后的查询

dependent union：union中的第二个或后面的查询语句,取决于外面的查询

union result：union的结果

materialized：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时

dependent subquery：子查询中的第一个 select，取决于外面的查询(子查询依赖于外层查询的结果)

table列

表名，该语句查询的表

partitions列

如果查询时基于分区表的话，会显示查询将访问的分区。

type列

查询访问方法，优化sql的重要字段，也是判断sql性能和优化程度的重要指标。他的取值类型范围：

ref_on_unll，index_merge，unique_subquery，index_subquery

const：通过索引一次命中，匹配一行数据

system：表中只有一行记录，相当于系统表

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配

ref：非唯一性索引扫描，返回匹配某个值的所有

range：只检索给定范围的行，使用一个索引来选择行，一般用于between、<>

index：只遍历索引树

all：表示全表扫描，这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多，执行效率越慢。

执行效率：all<index<range<ref<eq_ref<const<system。最好是避免all和index

possible_keys列

显示查询可能使用哪些索引来查找，仅仅是可能，实际不一定会用到。

explain时可能出现possible_keys列有值，而key显示null的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是null，则没有使用索引，在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查询效果。

key列

显示mysql在当前查询时所真正使用到的索引，是possible_keys的子集

如果没有使用索引，则该列是null，如果想强制mysql使用或忽略possible_keys列中的索引，在查询中使用force index、ignore index。

key_len列

显示mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列，评估组合索引是否完全被使用，这也是优化sql，评估索引的重要指标。

例如：sys_user的联合索引user由user_id和card_id两个int列组成，并且每个int是4个字节，通过结果中的key_len=4，可以推断出查询使用了第一个列，user_id列来执行索引查找。

key_len计算规则如下：

char(n)和varchar(n)在5.0.3版本后，n代表字符数，若是utf-8，一个数字或字母占1个字节，1个汉字占3个字节

char(n)：存汉字长度就是3n字节

varchar(n)：存汉字则长度是3n+2字节，2字节用来存储字符串长度（变长字符串）

tinyint：1字节

int：4字节

bigint：8字节

date：3字节

timestamp：4字节

datetime：8字节

rows列（重要）

若是全表扫描，预计需要扫描的行数；

若是索引查询，预计扫描的索引记录行数。

mysql查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，若扫描读取的行数越多，说明索引设置不对，或者字段传入的类型问题，说明需要优化

filtered列

表示返回结果的行数占需读取行数的百分比，值越大越好，说明百分比高，查询到的数据准确，值小的话，百分比小，说明查询的数据量大，结果集少。Filtered列的值依赖于统计信息。

ref列

显示key列记录的索引中，表查找值时使用到的列或常量。例如：const、字段名

extra列

no tables used，impossible where，no matching min/max row，using index，using index condition，using where，using join buffer(block nested loop)，using filesort，using temporary，start temporary/end temporary，firstMatch(表名)

using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，说明性能不错。

using where：sql使用了where过滤，效率较高。

using filesort：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。查询 cpu 资源消耗大，延时大，建议优化。

using temporary：查询使用了临时表，一般出现于排序、分组和多表 join 情况下，查询效率不高，建议优化。