mysql使用explain调试sql

explain的作用：sql调试的命令，可以对sql语句进行解析和分析。

explain select * from table where id=1

执行完毕之后，它的输出有以下字段：

id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra

要想知道explain命名怎么使用，就必须把这些字段搞清楚

一、id

SELECT查询的标识符， 每个SELECT语句都会自动分配一个唯一的标识符

二、select_type

每个select查询字句的类型，具体类型以及对应作用如下：

2.1 SIMPLE

简单的SELECT语句（不包括UNION操作或子查询操作）

2.2 PRIMARY

这是为更复杂的查询而创建的首要表(也就是最外层的表)，这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）

2.3 UNION

UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句没有依赖关系）

2.4 DEPENDENT UNION

UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）。

2.5 UNION RESULT

UNION操作的结果，id值通常为NULL。

EXPLAIN SELECT p.* FROM table p WHERE p.user_code LIKE '12345678%' UNION SELECT p.* FROM base_user p WHERE p.user_id > 100000000;

2.6 SUBQUERY

子查询中首个SELECT（如果有多个子查询存在）

explain select a.* from table a where user_id not in (select user_id from base_user_role where user_id='2')

2.7 DEPENDENT SUBQUERY

子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）

explain select a.* from table a where user_id not in (select user_id from base_user_role where user_id=a.user_id)

2.8 DERIVED

被驱动的SELECT子查询（子查询位于FROM子句）

2.9 MATERIALIZED

被物化的子查询

2.10 UNCACHEABLE SUBQUERY

对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）

2.11 UNCACHEABLE UNION

UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

三、 table

table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表的标识符，如派生表、子查询或集合。

四、 partitions

匹配的分区(这个目前用处不大)

五、 type

type 列代表QEP 中指定的表使用的连接方式。type作为访问类型，其值代表着当前查询所用的类型，是体现性能的一个重要指标，从表中可以看到，从上到下，扫描表的方式越来越宽，性能也就越来越差，因此，对于一个查询，最好能保持在range级别以上。

下面是最常用的几种连接方式：

类型名	优级别	解释
system	1	表仅有一行
const	2	表最多有一个匹配行,在查询开始时即被读取
eq_ref	3	使用primary key或者unique key作为多表连接的条件,仅从该表中读取一行
ref	4	作为查询条件的索引在每个表匹配索引值的行从表中读取出来
fulltext	5	全文索引检索
ref_or_null	6	和ref一致，但增加了NULL值查询支持
index_merge	7	表示使用了索引合并优化方法
unique_subquery	8	使用了替换了in子查询
index_subquery	9	使用了替换了in子查询，但只适用于子查询中的非唯一索引
range	10	只检索给定范围的行,使用一个索引来选择行
index	11	全表扫描，但扫描表的方式是按索引的次序进行
ALL	12	全表扫描的方式找到匹配的行

六、 possible_keys

主动指出查询能用哪个索引在表中找到记录，也就是会列出在查询中的字段中有索引的字段，但不一定被查询所用，因为了能是一个无效的单列索引。

七、 key

显示再查询中实际使用的索引/键，如果没有索引，则显示NULL。 但如果想强制查询中使用或忽视possible_keys列中的索引，则可以在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

八、 key_len

表示索引中使用的字节数。

九、 ref

表示哪些列或常量被用于查找索引列上的值。

十、 rows

显示当前查询估算到的查找到匹配记录所需的记录行数。

十一、filtered

filtered 列给出了一个百分比的值，这个百分比值和rows 列的值相乘，可以估计出那些将要和QEP 中的前一个表进行连接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

十二、 Extra

显示当前查询所用的解决方式，它有以下几种情况：

类型名	解释
Using where	列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort	MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer	改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where	这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中
Using index	仅使用索引树中的信息从表中检索列信息，而不需要进行附加搜索来读取实际行(使用二级覆盖索引即可获取数据)