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列，分别是id、select_tpye、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

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

引用资料