MySQL EXPLAIN安详严整

MySQL
EXPLAIN命令是询问质量优化不可缺点和失误的一片段,该文首要批注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,借使那一行用来注明的是其它行的一块儿结果。

         

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)

table

对应行正在访问哪二个表,表名大概别称

  • 关系优化器会为查询接收事关顺序,侧边深度优先
  • 当from中有子查询的时候,表名是derivedN的情势,N指向子查询,也正是explain结果中的下一列
  • 当有union result的时候,表名是union
    1,2等的花样,1,2象征涉足union的query id

注意:MySQL对待那个表和普通表同样,可是那么些“不常表”是不曾别的索引的。

图片 1

type

type展现的是访谈类型,是十分重大的一个目的,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null
> index_merge > unique_subquery > index_subquery > range
> index > ALL
,常常的话,得保险查询起码达到range等级,最棒能达成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)

possible_keys

显示查询利用了怎么索引,表示该索引可以张开高效地搜寻,然则列出来的目录对于后续优化进程可能是从未有过用的

图片 2

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,下边临那一个字段现身的或是张开分解:

key_len

key_len列呈现MySQL决定运用的键长度。借使键是NULL,则长度为NULL。使用的目录的长短。在不损失准确性的意况下,长度越短越好

一、 id

ref

ref列彰显应用哪个列或常数与key一齐从表中接受行。

     作者的接头是SQL实施的逐风度翩翩的标志,SQL从大到小的实行

rows

rows列呈现MySQL认为它实行查询时必须检查的行数。注意那是二个预价值评估。

  1. id相似不时候,试行顺序由上至下

  2. 借使是子查询,id的序号会依次增加,id值越大优先级越高,越先被推行

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操作,在找到第一匹配的元组后即停止找同样值的动作

作者:高广超
链接:
來源:简书
版权表达:内容来自互连网,版权归原创者全部。除非不可能确认,大家都会标注笔者及出处,如有侵犯权益烦请告知,大家会立马删除并表示歉意。多谢。

 

我积累多年的干货文书档案免费赠与,包涵前端后端和测量检验,系统结构,高并发管理,优化等

图片 3

 

3.id假设相仿,能够感觉是大器晚成组,从上往下生机勃勃生龙活虎实行;在装有组中,id值越大,优先级越高,越西施行

 

 

二、select_type

      示查询中各种select子句的类型**

(1卡塔尔国 SIMPLE(轻便SELECT,不使用UNION或子查询等卡塔尔(英语:State of Qatar)

(2卡塔尔 PTiguanIMA奥迪Q7Y(查询中若富含别的复杂的子部分,最外层的select被标志为PCRUISERIMAPAJEROY卡塔尔国

(3卡塔尔(英语:State of Qatar) UNION(UNION中的第4个或前边的SELECT语句)

(4卡塔尔(قطر‎ DEPENDENT UNION(UNION中的第一个或前边的SELECT语句,决议于外面包车型的士询问卡塔尔(قطر‎

(5) UNION RESULT(UNION的结果)

(6卡塔尔(قطر‎ SUBQUEEvoqueY(子查询中的第多少个SELECT卡塔尔(قطر‎

(7卡塔尔 DEPENDENT SUBQUE福特ExplorerY(子查询中的第三个SELECT,决计于外面包车型大巴查询卡塔尔

(8卡塔尔(英语:State of Qatar) DECR-VIVED(派生表的SELECT, FROM子句的子查询卡塔尔

(9卡塔尔国 UNCACHEABLE
SUBQUE昂CoraY(贰个子查询的结果无法被缓存,必需另行业评比估外链接的第大器晚成行卡塔尔(قطر‎

 

三、table

显示那风度翩翩行的多少是关于哪张表的,有时不是实在的表名字,见到的是derivedx(x是个数字,作者的明亮是第几步实践的结果卡塔尔(قطر‎

图片 4

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 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

图片 5

 

四、type

表示MySQL在表中找到所需行的法子,又称“访问类型”。

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

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

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

range:只检索给定范围的行,使用二个索引来选取行

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

eq_ref:
雷同ref,差别就在利用的目录是必经之路索引,对于每一个索引键值,表中独有一条记下相配,轻易的话,便是多表连接中行使primary
key也许 unique key作为关系条件

const、system:
当MySQL对查询某有些开展优化,并转移为二个常量时,使用这几个种类访谈。如将主键置于where列表中,MySQL就能够将该查询调换为叁个常量,system是const类型的特例,当查问的表唯有大器晚成行的处境下,使用system

NULL:
MySQL在优化进度中解释语句,奉行时居然毫无采访表或索引,比方从四个索引列里筛选最小值能够透过单独索引查找完结。

 

五、possible_keys

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

该列完全部独用立于EXPLAIN输出所示的表的前后相继。那代表在possible_keys中的某个键实际上不能按生成的表次序使用。
举例该列是NULL,则从未有关的目录。在这里种景况下,能够透过检查WHERE子句看是还是不是它引用某个列或契合索引的列来进步你的询问质量。假如是那样,创设三个适用的目录何况再一次用EXPLAIN检查查询

 

六、Key

key列展现MySQL实际调节运用的键(索引)

借使未有选拔索引,键是NULL。要想强制MySQL使用或忽略possible_keys列中的索引,在查询中使用FORCE
INDEX、USE INDEX可能IGNORE INDEX。

 

七、key_len

代表索引中运用的字节数,可通过该列总括查询中央银行使的目录的长度(key_len展现的值为索引字段的最大大概长度,并不是实际应用长度,即key_len是依靠表定义总计而得,不是通过表内检索出的)

不损失精确性的情形下,长度越短越好 

 

八、ref

代表上述表的三番五遍相配原则,即怎样列或常量被用来查找索引列上的值

 

九、rows

 表示MySQL依照表总括音信及索引接收景况,推断的找到所需的记录所要求读取的行数

 

十、Extra

该列包含MySQL消除查询的详细音讯,有以下二种情景:

Using
where:列数据是从仅仅使用了目录中的音讯而并未有读取实际的行动的表再次来到的,这发生在对表的总体的央求列都以同二个索引的局地的时候,表示mysql服务器将要积攒引擎检索行后再开展过滤

Using
temporary:表示MySQL须要运用有时表来储存结果集,习以为常于排序和分组查询

Using filesort:MySQL中无法利用索引完毕的排序操作称为“文件排序”

Using join
buffer:改值强调了在获得连接条件时未有采纳索引,並且需求一而再一连缓冲区来存款和储蓄中间结果。固然现身了这些值,那应该注意,依据查询的具体情状或者须求加多索引来改过能。

Impossible where:这些值重申了where语句会促成未有适合条件的行。

Select tables optimized
away:那个值意味着仅通过动用索引,优化器大概仅从聚合函数结实中回到风流罗曼蒂克行

 

 

总结:

EXPLAIN不会报告您至于触发器、存款和储蓄进程的新闻或客商自定义函数对查询的熏陶事态
• EXPLAIN不思考各样Cache
• EXPLAIN不可能突显MySQL在实行查询时所作的优化办事
• 部分计算新闻是估量的,实际不是准确值
• EXPALIN只可以表明SELECT操作,其余操作要重写为SELECT后翻看施行布置。

 

参考资料:

                

                

 

相关文章:

MySQL高性能表设计规范:http://www.jianshu.com/p/f797bbe11d76
MySQL EXPLAIN详解:http://www.jianshu.com/p/ea3fc71fdc45
MySQL 锁机制 常用知识点:http://www.jianshu.com/p/0d5b7cd592f9

MySQL
EXPLAIN命令是查询质量优化不可缺点和失误的风姿浪漫部分,该文首要讲明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,借使那风姿罗曼蒂克行用来证实的是其它行的协同结果。

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一样

table

对应行正在访谈哪一个表,表名恐怕别称

  • 关系优化器会为查询选取涉及顺序,侧边深度优先
  • 当from中有子查询的时候,表名是derivedN的情势,N指向子查询,也等于explain结果中的下一列
  • 当有union result的时候,表名是union
    1,2等的方式,1,2表示涉足union的query id

专心:MySQL对待这个表和普通表一样,然则这个“不常表”是没有别的索引的。

type

type显示的是访谈类型,是非常主要的三个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null
> index_merge > unique_subquery > index_subquery > range
> index > ALL
,常常的话,得保障查询起码到达range等第,最棒能达到规定的规范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能在优化阶段分解查询语句,在执行阶段甚至用不到访问表或索引(高效)

possible_keys

来得查询利用了什么样索引,表示该索引能够举办急迅地找出,可是列出来的目录对于三番两回优化进度恐怕是未曾用的

key

key列展现MySQL实际调控利用的键(索引)。若无接受索引,键是NULL。要想免强MySQL使用或不经意possible_keys列中的索引,在查询中利用FORCE
INDEX、USE INDEX或然IGNORE INDEX。

key_len

key_len列展现MySQL决定动用的键长度。假如键是NULL,则长度为NULL。使用的目录的长度。在不损失准确性的情事下,长度越短越好

ref

ref列显示应用哪个列或常数与key一齐从表中选拔行。

rows

rows列突显MySQL以为它试行查询时必须检查的行数。注意那是三个预估价。

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操作,在找到第一匹配的元组后即停止找同样值的动作

因为直接对MySQL中explain的归来结果管中窥豹,所以明日花时间研商下,写成博客分享给大家;内容根本参照他事他说加以考查MySQL的官方手册,还会有英特网的部分文章。

因为Mysq不一致版本对查询优化差异,所以区别版本explain的结果只怕不一样等,本文基于MySQL
5.7 版本,参照他事他说加以考察MySQL 5.7
官方手册,手册是英文的,自身阿尔巴尼亚语水平糟糕。。。翻译有出入的地点我们多多提意见。

EXPLAIN 语法

  1. {EXPLAIN | DESCRIBE | DESC}
  2. tbl_name [col_name | wild]
  3. {EXPLAIN | DESCRIBE | DESC}
  4. [explain_type]
  5. {explainable_stmt | FOR CONNECTION connection_id}
  6. explain_type: {
  7. EXTENDED
  8. | PARTITIONS
  9. | FORMAT = format_name
  10. }
  11. format_name: {
  12. TRADITIONAL
  13. | JSON
  14. }
  15. explainable_stmt: {
  16. SELECT statement
  17. | DELETE statement
  18. | INSERT statement
  19. | REPLACE statement
  20. | UPDATE statement
  21. }

此次试验应用到的表布局

  1. Welcome to the MySQL monitor. Commands end with ; or g.
  2. Your MySQL connection id is 12
  3. Server version: 5.7.10 Homebrew
  4. mysql> show tables;
  5. +----------------------+
  6. | Tables_in_index_test |
  7. +----------------------+
  8. | index_one |
  9. | index_relation |
  10. | index_test |
  11. +----------------------+
  12. 3 rows in set (0.00 sec)
  • index_one表
    1. mysql> show create table index_one;
    2. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
    3. | Table | Create Table |
    4. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
    5. | index_one | CREATE TABLE `index_one` (
    6. `id` int(11) NOT NULL AUTO_INCREMENT,
    7. PRIMARY KEY (`id`)
    8. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
    9. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
    10. 1 row in set (0.00 sec)
  • index_relation 关系表
    1. mysql> show create table index_relation;
    2. +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    3. | Table | Create Table |
    4. +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    5. | index_relation | CREATE TABLE `index_relation` (
    6. `id` int(11) NOT NULL AUTO_INCREMENT,
    7. `aid` int(11) DEFAULT NULL,
    8. PRIMARY KEY (`id`),
    9. KEY `index_aid` (`aid`)
    10. ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 |
    11. +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    12. 1 row in set (0.00 sec)
  • index_test 表
    1. mysql> show create table index_test;
    2. +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    3. | Table | Create Table |
    4. +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    5. | index_test | CREATE TABLE `index_test` (
    6. `id` int(11) NOT NULL AUTO_INCREMENT,
    7. `a` varchar(64) DEFAULT NULL,
    8. `b` varchar(64) DEFAULT NULL,
    9. `c` varchar(65) DEFAULT NULL,
    10. `e` varchar(64) DEFAULT NULL,
    11. PRIMARY KEY (`id`),
    12. KEY `Index_a_b_c` (`a`,`b`,`c`),
    13. KEY `index_b` (`b`)
    14. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
    15. +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    16. 1 row in set (0.00 sec)

EXPLAIN 介绍

EXPLAIN Output

  1. mysql> explain select * from index_test ,index_relation where index_test.id=index_relation.aid;
  2. +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
  5. | 1 | SIMPLE | index_test | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL |
  6. | 1 | SIMPLE | index_relation | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
  7. +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
  8. 2 rows in set, 1 warning (0.00 sec)

EXPLAIN Output Columns

Column 含义 Meaning
id query中select的序列号 The SELECT identifier
select_type select 类型 The SELECT type
table 输出的行所引用的表 The table for the output row
partitions   The matching partitions
type 联接类型 The join type
possible_keys 可以使用的索引 The possible indexes to choose
key 决定使用到的索引 The index actually chosen
key_len 使用到的的索引长度 The length of the chosen key
ref   The columns compared to the index
rows 扫描的行数 Estimate of rows to be examined
filtered   Percentage of rows filtered by table condition
Extra 查询的额外信息 Additional information

id

query中select查询的连串号,假诺为union查询的话,最终意气风发行Union结果的id为null。别的还被用于
table 字段仰慕味着归总行的开首和得了行的id,比如table =
<unionM,N>,表示归并id从M到N行的结果。

  1. mysql> explain select * from index_test where a='asf' union select * from index_test where a='dfe';
  2. +----+--------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+--------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------+
  5. | 1 | PRIMARY | index_test | NULL | ref | Index_a_b_c | Index_a_b_c | 195 | const | 1 | 100.00 | NULL |
  6. | 2 | UNION | index_test | NULL | ref | Index_a_b_c | Index_a_b_c | 195 | const | 1 | 100.00 | NULL |
  7. | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
  8. +----+--------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------+
  9. 3 rows in set, 1 warning (0.01 sec)

select_type

能够知晓为select查询类型,对于索引优化未有重大的意义,简介一下,有以下结果:

Value 含义 Meaning
SIMPLE 简单的查询(没有使用Union 或 子查询) Simple SELECT (not using UNION or subqueries)
PRIMARY 最外层的查询 Outermost SELECT
UNION Union查询中的地二个,或第三、第四… Second or later SELECT statement in a UNION
DEPENDENT UNION Union的结果被外层查询依赖 Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT 合并结果 Result of a UNION.
SUBQUERY 子查询 First SELECT in subquery
DEPENDENT SUBQUERY 子查询的结果被外层查询依赖 First SELECT in subquery, dependent on outer query
DERIVED   Derived table SELECT (subquery in FROM clause)
MATERIALIZED   Materialized subquery
UNCACHEABLE SUBQUERY   A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION   The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
  • SIMPLE 轻便的查询,未有利用Union或子查询,举例:
    1. mysql> explain select * from index_test where id=1;
    2. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    5. | 1 | SIMPLE | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    6. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  • PRIMARY Union查询中的最外层的询问,举个例子:

    1. mysql> explain select * from index_test where id=1 union select * from index_test where id=2;
    2. +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    5. | 1 | PRIMARY | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    6. | 2 | UNION | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    7. | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
    8. +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
  • UNION Union询问中的第3个或未来的查询,比如:

    1. mysql> explain select * from index_test where id=1 union select * from index_test where id=2 union select * from index_test where id=2;
    2. +----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    5. | 1 | PRIMARY | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    6. | 2 | UNION | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    7. | 3 | UNION | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    8. | NULL | UNION RESULT | <union1,2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
    9. +----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
  • DEPENDENT UNION 相符于UNION,只是它的结果被外层查询注重,举例:

    1. mysql> explain select * from index_test where id in (select aid from index_relation where id=1 union select aid from index_relation where id=2);
    2. +----+--------------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+--------------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    5. | 1 | PRIMARY | index_test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
    6. | 2 | DEPENDENT SUBQUERY | index_relation | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    7. | 3 | DEPENDENT UNION | index_relation | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    8. | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
    9. +----+--------------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
  • UNION RESULT 合并多行的结果,举例 UNION 中的例子。
  • SUBQUERY 子查询,例如:

    1. mysql> explain select * from index_test where id = (select aid from index_relation where id=(select 1));
    2. +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    5. | 1 | PRIMARY | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    6. | 2 | SUBQUERY | index_relation | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    7. +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  • DEPENDENT
    SUBQUERY
     分歧于SUBQUE翼虎Y的是,查询结果被外层重视。比如DEPENDENT
    UNION中的例子。

  • DERIVED 子查询的结果作为外层查询的根源,比方:
    1. mysql> explain select * from (select 1) as a;
    2. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
    5. | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
    6. | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
    7. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+

table

查询利用到的表名,有二种非常情状:

  • <unionM,N> The row refers to the union of the rows with id
    values of M and N.
  • <derivedN> The row refers to the derived table result for the
    row with an id value of N. A derived table may result, for example,
    from a subquery in the FROM clause.
  • <subqueryN> The row refers to the result of a materialized
    subquery for the row with an id value of N

partitions

结果记录中跨了多少个分区,如若数量表用到了分区,能够使用EXPLAIN PARTITIONS
SELECT * FROM table查看结果集的分区情状。

type

链接类型,从最棒到最差的连接类型为system、const、eq_ref、ref、range、indexhe和ALL

  • system 那是const联接类型的叁个特例,超少现身,举例 DE安德拉IVED
    中的例子。
  • const 表最多有叁个相配行,用于主键或然独一索引字段的询问,速度超级快,比方:

    1. mysql> explain select * from index_test where id = 1;
    2. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    5. | 1 | SIMPLE | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    6. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  • eq_ref 笔者精晓是多少个表联接,联接的字段在多个表都是唯风流倜傥的,即主键或独一索引,比如:

    1. mysql> explain select index_test.a from index_test,index_one where index_one.id=index_test.id;
    2. +----+-------------+------------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
    5. | 1 | SIMPLE | index_one | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
    6. | 1 | SIMPLE | index_test | NULL | eq_ref | PRIMARY | PRIMARY | 4 | index_test.index_one.id | 1 | 100.00 | NULL |
    7. +----+-------------+------------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
  • ref 和eq_ref分歧的是比很多行,使用最多的品种,比方:

    1. mysql> explain select * from index_test,index_relation where index_relation.aid=index_test.id;
    2. +----+-------------+----------------+------------+------+---------------+-----------+---------+--------------------------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+----------------+------------+------+---------------+-----------+---------+--------------------------+------+----------+-------------+
    5. | 1 | SIMPLE | index_test | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL |
    6. | 1 | SIMPLE | index_relation | NULL | ref | index_aid | index_aid | 5 | index_test.index_test.id | 5 | 100.00 | Using index |
    7. +----+-------------+----------------+------------+------+---------------+-----------+---------+--------------------------+------+----------+-------------+
  • fulltext 全文索引

  • ref_or_null 和ref差异的是,sql中包括 O途乐 key_column IS NULL。

    1. mysql> explain select index_test.a from index_test where a='abc' or a is null;
    2. +----+-------------+------------+------------+-------------+---------------+-------------+---------+-------+------+----------+--------------------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+-------------+---------------+-------------+---------+-------+------+----------+--------------------------+
    5. | 1 | SIMPLE | index_test | NULL | ref_or_null | Index_a_b_c | Index_a_b_c | 195 | const | 2 | 100.00 | Using where; Using index |
    6. +----+-------------+------------+------------+-------------+---------------+-------------+---------+-------+------+----------+--------------------------+
  • index_merge 表示使用了目录归并优化措施,key列包蕴了采纳的目录的清单,比如:

    1. mysql> explain select * from index_test where a='abc' or id=1;
    2. +----+-------------+------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
    5. | 1 | SIMPLE | index_test | NULL | index_merge | PRIMARY,Index_a_b_c | Index_a_b_c,PRIMARY | 195,4 | NULL | 2 | 100.00 | Using sort_union(Index_a_b_c,PRIMARY); Using where |
    6. +----+-------------+------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
  • unique_subquery
  • index_subquery
  • range 只检索给定范围的行,key列呈现接受了哪些索引,当使用=、<>、>、>=、<、<=、IS
    NULL、<=>、BETWEEN只怕IN操作符,用常量比较根本字列时,能够行使range

    1. mysql> explain select * from index_test where id > 5;
    2. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    5. | 1 | SIMPLE | index_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
    6. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  • index 遇ALL区别的是,它只扫描了索引树。因为索引文件通常比数据文件小。所以比ALL快。比方:
    1. mysql> explain select a from index_test where c = 'abc';
    2. +----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
    5. | 1 | SIMPLE | index_test | NULL | index | NULL | Index_a_b_c | 588 | NULL | 6 | 16.67 | Using where; Using index |
    6. +----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
  • ALL 全表扫描
    1. mysql> explain select e from index_test where c = 'abc';
    2. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    5. | 1 | SIMPLE | index_test | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
    6. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

possible_keys

列出能够使用什么索引来查询,最后会选拔一个最优的。假如该列为NULL,则表示从未相关的目录。

key

列出决定采纳的目录,倘使为NULL表示从没使用索引,能够使用FORCE INDEX、USE
INDEX大概IGNORE INDEX
免强MySQL使用或忽略possible_keys列出的目录,在查。

key_len

列出利用索引的尺寸,通过选拔的目录长度能够猜度出联合索引中哪多少个字段使用到了目录,比如:

  1. mysql> explain select * from index_test where a='abc' and b='abc' and c>'abc';
  2. +----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
  5. | 1 | SIMPLE | index_test | NULL | range | Index_a_b_c,index_b | Index_a_b_c | 588 | NULL | 1 | 100.00 | Using index condition |
  6. +----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
  7. 1 row in set, 1 warning (0.00 sec)
  8. mysql> explain select * from index_test where a='abc' and b>'abc' and c='abc';
  9. +----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
  10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  11. +----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
  12. | 1 | SIMPLE | index_test | NULL | range | Index_a_b_c,index_b | Index_a_b_c | 390 | NULL | 1 | 16.67 | Using index condition |
  13. +----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+

ref

rows

MySQL感到必需检查的用来回到央浼数据的行数,约小越快;InnoDB
存款和储蓄引擎那一个值是八个大概值。

filtered

在MySQL 5.7.3事情发生早前,就算使用EXPLAIN EXTENDED 将展现此列。 从MySQL
5.7.3开始,默许情状下启用扩展输出,並且无需EXTENDED关键字。

Extra

列出了MySQL查询的详细音信,有非常多种音信,如下

  • const row not found
  • Deleting all rows
  • Distinct
  • FirstMatch(tbl_name)
  • Full scan on NULL key
  • Impossible HAVING
  • Impossible WHERE
  • Impossible WHERE noticed after reading const tables
  • Using filesort
  • Using index
  • Using index condition
  • Using index for group-by
  • Using temporary
  • Using where
  • Zero limit

发表评论

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