0%

explain关键字

风雨送春归,飞雪迎春到。已是悬崖百丈冰,犹有花枝俏。俏也不争春,只把春来报。待到山花烂漫时,她在丛中笑。 —— 毛泽东 《卜算子·咏梅》

一、引入

  1. EXPLAIN显示了MySQL如何使用索引来处理SELECT语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。
  2. EXPLAIN命令用法十分简单, 在SQL语句前加上Explain就可以了,如:EXPLAIN SELECT * FROM 数据库.表;

二、详解

EXPLAIN SELECT * FROM users;\G结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
           id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: NULL
字段 说明
id 查询序列号(值相同时默认由上而下执行,不同时值越大代表优先级越高,则越先被执行)
select_type 查询类型,详情见下表
table 查询的表
partitions 匹配的分区
type 联接类型
possible_keys 可能选用的索引,如果该列是NULL,则没有相关的索引,这种情况下可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高查询性能
key 实际使用的索引
key_len MySQL决定使用的键长度,如果键是NULL,则长度为NULL
ref 哪个字段或常数与key一起被使用
rows 扫描了多少行
filtered 通过条件过滤出的行数的百分比估计值
Extra 额外的信息
  • id

    • 值为NULL时说明这一行数据是由另外两个SQL语句进行 UNION操作后产生的结果集
    • 值相同时说明SQL执行顺序是按照显示的从上至下执行的
    • 值不同时,值越大代表优先级越高,越先被执行
  • select_type

查询类型 说明
SIMPLE 简单SELECT(不使用UNION或子查询)
PRIMARY 最外面的SELECT,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION UNION中的第二个或后面的SELECT语句
DEPENDENT UNION UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT UNION 的结果
SUBQUERY 子查询中的第一个SELECT
DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
DERIVED 派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外链接的第一行
  • type:从上到下性能最佳到最坏
类型 说明
system 表中只有一条数据,这个类型是特殊的const类型
const 针对主键或唯一索引的等值查询扫描,最多只返回一行数据
eq_ref 通常出现在多表的join查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果,并且查询的比较操作通常是=,查询效率较高
ref 通常出现在多表的join查询,针对于非唯一或非主键索引,或者是使用了最左前缀规则索引的查询
ref_or_null 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行
index_merge 该联接类型表示使用了索引合并优化方法
unique_subquery 该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高
index_subquery 该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range 表示使用索引范围查询,通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN() 操作中。当type是range时,那么EXPLAIN输出的ref字段为NULL,并且key_len字段是此次查询使用到的索引的最长的那个
index 表示全索引扫描(full index scan),和ALL类型类似,只不过ALL类型是全表扫描,而index类型则仅仅扫描所有的索引而不扫描数据。index类型通常出现在覆盖索引的情况。
ALL 表示全表扫描,这个类型的查询是性能最差的查询之一
  • extra: 查询的额外信息
类型 说明
Distinct MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #) MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用
Using filesort MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index 覆盖索引
Using where 服务层使用WHERE条件来过滤数据
Using index condition 5.6+引入的新特性索引条件下推(index condition pushdown)
Using temporary 为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using sort_union(…), Using union(…), Using intersect(…) 这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by 类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

using index condition会先过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用WHERE子句中的其他条件去过滤这些数据行

三、用不到索引

  1. 数据类型出现隐式转换
  2. 复合索引的情况下,查询条件不满足索引最左的原则
  3. Mysql估计使用索引比全表扫描慢
  4. 用or分割开的条件,or前条件有索引,or后的列没有索引
  5. 负向查询(not,not in,not like,<>,!=,!>,!<)不会使用索引
  6. 独立的列索引不能是表达式的一部分

四、参考

  1. ICP