MySQL
EXPLAIN命令是查询质量优化不可缺点和失误的一部分,该文重要解说explain命令的行使及有关参数表明。

原文:

Mysql Explain 这里做多个素材的通横盘治。

EXPLAIN Output Columns

列名 说明
id 执行编号,标识select所属的行。如果在语句中没子查询或关联查询,只有唯一的select,每行都将显示1。否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置
select_type 显示本行是简单或复杂select。如果查询有任何复杂的子查询,则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUlT)
table 访问引用哪个表(引用某个查询,如“derived3”)
type 数据访问/读取操作类型(ALL、index、range、ref、eq_ref、const/system、NULL)
possible_keys 揭示哪一些索引可能有利于高效的查找
key 显示mysql决定采用哪个索引来优化查询
key_len 显示mysql在索引里使用的字节数
ref 显示了之前的表在key列记录的索引中查找值所用的列或常量
rows 为了找到所需的行而需要读取的行数,估算值,不精确。通过把所有rows列值相乘,可粗略估算整个查询会检查的行数
Extra 额外信息,如using index、filesort等

          

一.语法

id

id是用来所有人家标记整个查询中SELELCT
语句的,在嵌套查询中id越大的口舌越先施行。该值或许为NULL,要是这一行用来证实的是其他行的联合结果。

         

explain < table_name >

select_type

表示查询的类别

类型 说明
simple 简单子查询,不包含子查询和union
primary 包含union或者子查询,最外层的部分标记为primary
subquery 一般子查询中的子查询被标记为subquery,也就是位于select列表中的查询
derived 派生表——该临时表是从子查询派生出来的,位于form中的子查询
union 位于union中第二个及其以后的子查询被标记为union,第一个就被标记为primary如果是union位于from中则标记为derived
union result 用来从匿名临时表里检索结果的select被标记为union result
dependent union 顾名思义,首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询
subquery 子查询中第一个SELECT语句
dependent subquery 和DEPENDENT UNION相对UNION一样

在平日事业中,我们会临时会开慢查询去记录一些推行时间十分久的SQL语句,搜索这一个SQL语句并不代表完事了,些时大家平日用到explain那么些命令来查阅几个那个SQL语句的奉行布置,查看该SQL语句有未有应用上了目录,有未有做全表扫描,那都足以通过explain命令来查看。所以大家深深了然MySQL的依靠开销的优化器,仍是能够赢得大多也许被优化器考虑到的拜见计谋的内情,以致当运维SQL语句时哪类政策预计会被优化器接纳。(QEP:sql生成二个执行安插query
Execution plan)

例如: explain select * from t3 where id=3952602;

table

对应行正在访问哪八个表,表名恐怕别称

  • 涉及优化器会为查询选拔事关顺序,左边深度优先
  • 当from中有子查询的时候,表名是derivedN的款式,N指向子查询,相当于explain结果中的下一列
  • 当有union result的时候,表名是union
    1,2等的花样,1,2代表涉足union的query id

留意:MySQL对待那几个表和普通表一样,但是那些“有的时候表”是平素不其他索引的。

威澳门尼斯人36366com 1

二.explain输出解释

type

type突显的是探访类型,是较为关键的三个目标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null
> index_merge > unique_subquery > index_subquery > range
> index > ALL
,平日的话,得保险查询起码到达range品级,最CANON达成ref。

类型 说明
All 最坏的情况,全表扫描
index 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index,说明正在使用覆盖索引,只扫描索引的数据,它比按索引次序全表扫描的开销要小很多
range 范围扫描,一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref 一种索引访问,它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是,它用在关联操作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效)
const 当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。当主键放入where子句时,mysql把这个查询转为一个常量(高效)
system 这是const连接类型的一种特例,表仅有一行满足条件。
Null 意味说mysql能在优化阶段分解查询语句,在执行阶段甚至用不到访问表或索引(高效)
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+

possible_keys

突显查询利用了哪些索引,表示该索引能够展开高效地搜寻,可是列出来的目录对于后续优化进度大概是绝非用的

威澳门尼斯人36366com 2

1.id

key

key列展现MySQL实际调整运用的键(索引)。如果未有采取索引,键是NULL。要想强制MySQL使用或忽略possible_keys列中的索引,在查询中使用FORCE
INDEX、USE INDEX可能IGNORE INDEX。

expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下边临这一个字段出现的只怕进行批注:

  作者的明亮是SQL试行的顺遂的标记,SQL从大到小的实行.

key_len

key_len列显示MySQL决定选取的键长度。如果键是NULL,则长度为NULL。使用的目录的长度。在不损失精确性的景况下,长度越短越好

一、 id

例如:

ref

ref列展现应用哪个列或常数与key一齐从表中采纳行。

     笔者的接头是SQL推行的依次的标记,SQL从大到小的实施

mysql> explain select * from (select * from ( select * from t3
where id=3952602) a) b;
+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

rows

rows列展现MySQL认为它实行查询时必得检查的行数。注意这是贰个预评估价值。

  1. id同样不常候,实行各种由上至下

  2. 比如是子查询,id的序号会递增,id值越大优先级越高,越先被实行

很令人瞩目那条SQL是从里向外的施行,正是从id=3 向上实施.

Extra

Extra是EXPLAIN输出中别的叁个很要紧的列,该列展现MySQL在查询进度中的一些详细音信,MySQL查询优化器实行查询的历程中对查询安顿的要害补充新闻。

类型 说明
Using filesort MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了后者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快,所以一般这时也要考虑优化查询了。使用文件完成排序操作,这是可能是ordery by,group by语句的结果,这可能是一个CPU密集型的过程,可以通过选择合适的索引来改进性能,用索引来为查询结果排序。
Using temporary 用临时表保存中间结果,常用于GROUP BY 和 ORDER BY操作中,一般看到它说明查询需要优化了,就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。
Using index 说明查询是覆盖了索引的,不需要读取数据文件,从索引树(索引文件)中即可获得信息。如果同时出现using where,表明索引被用来执行索引键值的查找,没有using where,表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的,但无需再回表查询记录。
Using index condition 这是MySQL 5.6出来的新特性,叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的,但是现在可以了,这样减少了不必要的IO操作,但是只能用在二级索引上。
Using where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意:Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
Using join buffer 使用了连接缓存:Block Nested Loop,连接算法是块嵌套循环连接;Batched Key Access,连接算法是批量索引连接
impossible where where子句的值总是false,不能用来获取任何元组
select tables optimized away 在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作,或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

作者:高广超
链接:
來源:简书
版权表明:内容出自网络,版权归原创者全体。除非不能够明确,大家都会标记小编及出处,如有侵害版权烦请告知,我们会及时删除并表示歉意。感谢。

 

作者储存多年的干货文书档案免费赠予,包涵前端后端和测量检验,系统架构,高并发管理,优化等

威澳门尼斯人36366com 3

 

3.id一旦一致,能够以为是一组,从上往下依次试行;在有着组中,id值越大,优先级越高,越先实行

  1. select_type

 

哪怕select类型,能够有以下

 

(1) SIMPLE

二、select_type

简单来说SELECT(不使用UNION或子查询等) 举个例子:

      示查询中各种select子句的品种**

mysql> explain select * from t3 where
id=3952602;

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
|  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4 
     | const |    1 |       |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+

(2) P汉兰达IMA传祺Y(查询中若满含别的复杂的子部分,最外层的select被标志为P安德拉IMATiggoY)

(2). PRIMARY

(3) UNION(UNION中的第二个或前面包车型客车SELECT语句)

自己的知晓是最外层的select.比方:

(4) DEPENDENT UNION(UNION中的第一个或前边的SELECT语句,决计于外面包车型大巴询问)

mysql> explain select * from (select *
from t3 where id=3952602) a ;

(5) UNION RESULT(UNION的结果)

+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

(6) SUBQUEEvoqueY(子查询中的第叁个SELECT)

(3).UNION

威澳门尼斯人36366com,(7) DEPENDENT SUBQUEMuranoY(子查询中的第三个SELECT,决意于外面包车型地铁询问)

UNION中的第二个或前面的SELECT语句.举个例子

(8) DECRUISERIVED(派生表的SELECT, FROM子句的子查询)

mysql> explain select * from t3 where
id=3952602 union all select * from t3 ;

(9) UNCACHEABLE
SUBQUE奔驰G级Y(一个子查询的结果不能被缓存,必需重新评估外链接的率先行)

+—-+————–+————+——-+——————-+———+———+——-+——+——-+
| id | select_type  | table      | type  | possible_keys     | key 
   | key_len | ref   | rows | Extra |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+
|  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY
| 4       | const |    1 |       |
|  2 | UNION        | t3         | ALL   | NULL              | NULL    |
NULL    | NULL  | 1000 |       |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL              |
NULL    | NULL    | NULL  | NULL |       |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+

 

(4).DEPENDENT UNION

三、table

UNION中的第四个或前面包车型地铁SELECT语句,决定于外面包车型大巴询问

显示这一行的多寡是有关哪张表的,一时不是实在的表名字,看见的是derivedx(x是个数字,小编的领会是第几步施行的结果)

mysql> explain select * from t3 where
id in (select id from t3 where id=3952602 union all select id from
t3)  ;

威澳门尼斯人36366com 4

+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
| id | select_type        | table      | type   | possible_keys     |
key     | key_len | ref   | rows | Extra                    |
+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
|  1 | PRIMARY            | t3         | ALL    | NULL              |
NULL    | NULL    | NULL  | 1000 | Using where              |
|  2 | DEPENDENT SUBQUERY | t3         | const  | PRIMARY,idx_t3_id |
PRIMARY | 4       | const |    1 | Using index              |
|  3 | DEPENDENT UNION    | t3         | eq_ref | PRIMARY,idx_t3_id |
PRIMARY | 4       | func  |    1 | Using where; Using index |
|NULL | UNION RESULT       | <union2,3> | ALL    | NULL          
   | NULL    | NULL    | NULL  | NULL |                          |
+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(4).UNION RESULT

威澳门尼斯人36366com 5

UNION的结果。

 

mysql> explain select * from t3 where
id=3952602 union all select * from t3 ;

四、type

+—-+————–+————+——-+——————-+———+———+——-+——+——-+
| id | select_type  | table      | type  | possible_keys     | key 
   | key_len | ref   | rows | Extra |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+
|  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY
| 4       | const |    1 |       |
|  2 | UNION        | t3         | ALL   | NULL              | NULL    |
NULL    | NULL  | 1000 |       |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL              |
NULL    | NULL    | NULL  | NULL |       |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+

意味着MySQL在表中找到所需行的办法,又称“访谈类型”。

(5).SUBQUERY

常用的品类有: ALL, index,  range, ref, eq_ref, const, system,
NULL(从左到右,品质从差到好)

子查询中的第三个SELECT.

ALL:Full Table Scan, MySQL将遍历全表以找到特出的行

mysql> explain select * from t3 where
id = (select id from t3 where id=3952602 )  ;

index: Full Index Scan,index与ALL分化为index类型只遍历索引树

+—-+————-+——-+——-+——————-+———+———+——-+——+————-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra       |
+—-+————-+——-+——-+——————-+———+———+——-+——+————-+
|  1 | PRIMARY     | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4 
     | const |    1 |             |
|  2 | SUBQUERY    | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4 
     |       |    1 | Using index |
+—-+————-+——-+——-+——————-+———+———+——-+——+————-+

range:只检索给定范围的行,使用四个索引来抉择行

(6).  DEPENDENT SUBQUERY

ref: 表示上述表的连天相配原则,即怎样列或常量被用于查找索引列上的值

子查询中的第三个SELECT,决议于外面包车型大巴询问

eq_ref:
类似ref,差异就在行使的目录是独一无二索引,对于各类索引键值,表中独有一条记下相称,简来讲之,就是多表连接中使用primary
key只怕 unique key作为关系条件

mysql> explain select id from t3 where
id in (select id from t3 where id=3952602 )  ;

const、system:
当MySQL对查询某部分举办优化,并转变为贰个常量时,使用那么些品种访问。如将主键置于where列表中,MySQL就能将该查询转变为贰个常量,system是const类型的特例,当查问的表唯有一行的动静下,使用system

+—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+
| id | select_type        | table | type  | possible_keys     | key 
   | key_len | ref   | rows | Extra                    |
+—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+
|  1 | PRIMARY            | t3    | index | NULL              | PRIMARY
| 4       | NULL  | 1000 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t3    | const | PRIMARY,idx_t3_id |
PRIMARY | 4       | const |    1 | Using index              |
+—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+

NULL:
MySQL在优化进度中表达语句,推行时还是毫无访问表或索引,举个例子从三个索引列里接纳最小值可以通过单独索引查找达成。

7).DERIVED

 

派生表的SELECT(FROM子句的子查询)

五、possible_keys

mysql> explain select * from (select *
from t3 where id=3952602) a ;

建议MySQL能利用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不料定被询问利用

+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

该列完全部独用立于EXPLAIN输出所示的表的顺序。那意味着在possible_keys中的有些键实际上无法按生成的表次序使用。
一旦该列是NULL,则从未有关的目录。在这里种气象下,能够透过检查WHERE子句看是不是它引用有个别列或适合索引的列来升高你的查询质量。如若是这么,创制贰个稳当的目录并且再一次用EXPLAIN检查查询

3.table

 

来得这一行的数额是有关哪张表的.

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注