Mysql EXPLAIN
1. EXPLAIN关键词的作用
EXPLAIN
关键字可以查看表的结构、查看SELECT语句的执行过程
EXPLAIN TABLE
同DESCRIBE TABLE
或SHOW COLUMNS FROM TABLE
查看表的字段定义信息。EXPLAIN SELECT select_query
可查看SELECT语句的执行过程。
2. EXPLAIN输出参数说明
2.1 参数列表
参数名 | 参数含义 |
---|---|
id | SELECT 的标识 |
select_type | select的类型 |
table | 查询的数据表 |
partitions | 匹配的分区 |
type | join类型 |
possible_keys | 可能的索引 |
key | SELECT中使用到的索引 |
key_len | 使用的索引的长度 |
ref | 字段和索引的对应比较 |
rows | 语句检索的记录行数 |
filtered | 被条件过滤的数据百分比 |
Extra | 附加信息 |
2.2 参数详细说明
id
SELECT在查询中的标识符。如果查询的数据是UNION的结果,这个值可能是NULL。在这种情况下,参数table显示的值类似<unionM,N>
, M/N代表的是涉及的SELECT标识符。select_type
select_type值 含义 SIMPLE simple SELECT(没有使用UNION或者子查询) PRIMARY 最外层的查询 UNION 在UNION语句中第二个或者后边的SELECT语句 DEPENDENT UNION 在UNION语句中第二个或者后边的SELECT语句,并且依赖了外层的查询 UNION RESULT UNION的结果集 SUBQUERY 子查询中的第一个SELECT语句 DEPENDENT SUBQUERY 子查询中的第一个SELECT语句,并且依赖了外层的查询 DERIVED 有SELECT语句派生的表(在FROM子句中使用的SELECT语句) UNCACHEABLE SUBQUERY 不可缓存的查询 UNCACHEABLE UNION 不可缓存的UNION table
数据行输入设计的表的名称,还可能会出现一下两种情况:<unionM,N>
union语句输出的数据,M和N是涉及的select语句的id<derivedN>
数据是由派生的表输出的,N是select语句id。
partitions
查询中命中的数据分片(分区), 以后补充吧,没有使用partitions的经验type
连接类型(原文:The join type)。以下是详细说明,排列顺序从好到差:system
表只有一行数据,是const的一种特殊情况,一般不常见。const
表最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快记住一定是用到primary key 或者unique,并且只检索出两条数据的 情况下才会是const。比较常见。
原文:The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.
这种类型的语句会被用做const表:
SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
eq_ref
对于从上一个表读取出来的每行数据,在关联表中有一行数据可以进行关联。除了system
和const
外eq_ref
是最好的join类型。索引的所有部分部分是PRIMARY KEY
或UNIQUE NOT NULL
就会使用这种连接类型。
翻译的不好还是看原文吧:One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
示例语句:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
图中的示例中两个表的关联字段都是primarykey
上图的示例是使用UNIQUE索引。需要注意的是UNIQUE字段必须是NOT NULLref
对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。比较常见。
以上是抄袭,还是继续看原文吧 T^T:All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.
sql示例(官网示例):
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
fulltext
The join is performed using a FULLTEXT index.ref_or_null
类似ref
,特殊处理了含有NULL值的数据行。经常用于分析子查询。
官方示例:SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。unique_subquery
在IN
子查询的情况下替换ref
优化器,索引字段为SUNIQUE
类型如下情况:value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery
类似unique_subquery
,区别在于IN
子查询可以是非唯一索引字段。range
使用过一个索引进行范围选定时使用。
当使用=,<>,>,>=,<,<=,IS NULL, <=>, BETWEEN,IN
操作符与常熟比较时会使用range
类型。
index
该联接类型与ALL相同,除了只有索引树被扫描。ALL
全表扫描。通常如果在连接中第一个表优化器(type)标记为const
时,还不太坏。在其他情况下比较糟糕。
通常情况下ALL
可以通过添加索引来优化。
Extra
这里总结一下平时常见或者比较简单的,以后碰到其他情况再补充。const row not found
类似select * rom table
table是空的。Distinct
MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。Full scan on NULL key
This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.Impossible HAVING
HAVING
条件一直为假,并且不能筛选数据。
请检查SQL语句条件限定和字段选择是否正确,或者HAVING
条件是否必须。Impossible WHERE
同Impossible HAVING
,针对的是where
子句。Using index
语句检索了idex tree没有读取真正的数据行。查询的数据是索引列会使用此策略。
同时出现Using where
表示使用该索引进行了数据的筛选。没有出现Using where
表示可能只使用索引读取数据,并没有进行筛选。Using temporary
查询中需要创建一个临时表保存结果。通常发生在含有GROUP BY
和ORDER BY
的查询语句中。Using where
除需要进行全表检索的情况下,如果Extra
中没有出现Using where
并且type
是ALL
或index
时表示你的可能编写错误了。