MySQL explain字段分析

MySQL EXPLAIN

在编写SQL语句的时候,并不是只要能够返回数据就解决问题了,还要针对SQL执行时间对语句进行优化。如果执行速度过慢,此时就需要EXPLAIN命令来查看一个SQL的执行计划,该语句有没有用上索引,涉及行数,有没有全表查询,还可以获得很多可能被优化器考虑到的访问策略的细节。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> EXPLAIN SELECT major.id, major.name as majorName, stu_class

-> FROM major, user
-> WHERE user.major_id=major.id
-> AND user.stu_num=302181091
-> ;

+----+-------------+-------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | stu_num,major_id_index | stu_num | 9 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | major | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.04 sec)

EXPLAIN命令共有12列,分别是idselect_tpyetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra

id

执行编号,标识select所属的行。如果在语句中没子查询或关联查询,只有唯一的select,每行都将显示1。如果有子查询,id的序号会递增,id值越大优先级越高,越先被执行;如果id相同,如开头explain语句中两行id是相同的,可以认为是同一组,从上往下执行;在所有组id越大优先级也越高。

select_type

select_type显示的是每个SELECT子句的类型。

select_type 类型
SIMPLE 简单SELECT,不使用UNION或子查询等
PRIMARY 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION UNION中的第二个或后面的SELECT语句
DEPENDENT UNION UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT UNION的结果
SUBQUERY 子查询中的第一个SELECT
DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
DERIVED 派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外链接的第一行

table

显示这一行的数据是关于哪张表的,有时不是真实的表名字,看到的是derivedx(x是个数字),指的是派生表,即由id为n的查询产生的结果;还有的是unionx,y(x和y都是数字),指的是id为x和y查询union产生的结果。

partitions

  • 对于分区表,显示查询的分区ID
  • 对于非分区表,显示的为 NULL

type

type列表示MySQL在表中找到所需行的方式,又称“访问类型”。

性能从上到下由高到低。

type
system 这是const联接类型的一个特例,当查询的表只有一行时使用
const 表中有且只有一个匹配的行时使用,如对主键或是唯一索引的查询,这是效率最高的联接方式
eq_ref 唯一索引或主键引查找,对于每个索引键,表中只有一条记录与之匹配。
ref 非唯一索引查找,返回匹配某个单独值的所有行。
ref_or_null 类似于 ref类型的查询,但是附加了对 null 值列的查询
index_merge 该联接类型表示使用了索引合并优化的方法
range 索引范围的扫描常见于 between, > ,<这样的查询条件
index full index scan 全索引扫描,同all的区别是,遍历的是索引树
all full tabel scan 全表扫描,这是效率最差的联接方式

possible_keys

  • 指出mysql能使用那些索引来优化查询

  • 查询列所涉及的列上的索引都会被列出来,但不一定会被使用

key

  • 查询优化器优化查询实际所使用的索引

  • 如果没有可用的索引,则显示 null

key_len

  • 表示索引字段的最大的可能长度

  • key_len的长度由字段定义计算而来,并非数据的实际长度

ref

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

rows

  • 表示msql通过索引的统计信息,估算的所需读取的行数

  • rows值的大小时个统计抽样的结果,并不十分的准确

filtered

  • 表示返回的结果的行数占需要读取行数的百分比

  • Filtered列的值越大越好

  • Filtered列的值依赖的说统计信息

Extra

Extra
Distinct 优化distinct操作,在找到第一匹配的元祖后即停止找同样的动作
Not exists 使用 not exists 来优化查询
Using filesort 使用额外操作进行排序,通常会出现在order by 或 group by 查询
Using index 使用了覆盖索引进行查询
Using temporary mysql需要使用临时表来处理查询,常见于排序,子查询,和分组查询
Using where 需要在mysql服务器层使用 where条件来过滤数据
slect tables /optimized away 直接通过索引来获得数据,不用访问表

TODO

引用资料