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

MySQL
EXPLAIN命令是查询性能优化不可缺少的一部分,该文主要讲解explain命令的使用及相关参数说明。

(4).DEPENDENT UNION

table

对应行正在访问哪一个表,表名或者别名

  • 关联优化器会为查询选择关联顺序,左侧深度优先
  • 当from中有子查询的时候,表名是derivedN的形式,N指向子查询,也就是explain结果中的下一列
  • 当有union result的时候,表名是union
    1,2等的形式,1,2表示参与union的query id

注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。

3.id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

1.id

possible_keys

显示查询使用了哪些索引,表示该索引可以进行高效地查找,但是列出来的索引对于后续优化过程可能是没有用的

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

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE
INDEX、USE INDEX或者IGNORE INDEX。

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
官方手册,手册是英文的,本人英语水平不好。。。翻译有出入的地方大家多多提意见。

eq_ref可以用于使用=
操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY
KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。
ref可以用于使用=或<=>操作符的带索引的列。

key_len

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

八、ref

只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。

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能在优化阶段分解查询语句,在执行阶段甚至用不到访问表或索引(高效)

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)

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

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

作者:高广超
链接:
來源:简书
版权申明:内容来源网络,版权归原创者所有。除非无法确认,我们都会标明作者及出处,如有侵权烦请告知,我们会立即删除并表示歉意。谢谢。

 

小编积累多年的干货文档免费赠送,包含前端后端和测试,系统架构,高并发处理,优化等

欧博国际平台 1

 

key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE
INDEX、USE INDEX或者IGNORE INDEX。

(4).ref

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等

key列显示MySQL实际决定使用的键(索引)

ref列显示使用哪个列或常数与key一起从表中选择行。

id

id是用来顺序标识整个查询中SELELCT
语句的,在嵌套查询中id越大的语句越先执行。该值可能为NULL,如果这一行用来说明的是其他行的联合结果。

相关文章:

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

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

rows

rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

const、system:
当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system

看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER
BY上,而不是GROUP BY上

MySQL
EXPLAIN命令是查询性能优化不可缺少的一部分,该文主要讲解explain命令的使用及相关参数说明。

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)

(10).index

(5) UNION RESULT(UNION的结果)

(1).system

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

+—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+
| id | select_type | table | type   | possible_keys     | key       |
key_len | ref                  | rows | Extra |
+—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+
|  1 | SIMPLE      | t4    | ALL    | NULL              | NULL      |
NULL    | NULL                 | 1000 |       |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,idx_t3_id | idx_t3_id
| 4       | dbatest.t4.accountid |    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 (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 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。

 

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

Using
where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤

(3).UNION

  1. id相同时,执行顺序由上至下

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

  1. select_type

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

在下面的例子中,MySQL可以使用ref_or_null联接来处理ref_tables:

 

从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

 

(6). index_merge

(9) UNCACHEABLE
SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

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

 

  1. ref

欧博国际平台 2

5.possible_keys

四、type

我的理解是最外层的select.例如:

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

子查询中的第一个SELECT,取决于外面的查询

          

2 rows in set (0.00 sec)

filtered

在MySQL 5.7.3之前,如果使用EXPLAIN EXTENDED 将显示此列。 从MySQL
5.7.3开始,默认情况下启用扩展输出,并且不需要EXTENDED关键字。

一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

ref

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

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. }

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

(6) SUBQUERY(子查询中的第一个SELECT)

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

 

就是select类型,可以有以下

 

这是const联接类型的一个特例。表仅有一行满足条件.如下(t3表上的id是
primary key)

                

SELECT * from tbl_name WHERE
primary_key=1;
SELECT * from tbl_name WHERE primary_key_part1=1和
primary_key_part2=2;

六、Key

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

欧博国际平台 3

(2).Not exists

Select tables optimized
away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

简单SELECT(不使用UNION或子查询等) 例如:

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

例如

欧博国际平台 4

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

 

value IN (SELECT primary_key FROM
single_table WHERE some_expr)

table

对应行正在访问哪一个表,表名或者别名

  • 关联优化器会为查询选择关联顺序,左侧深度优先
  • 当from中有子查询的时候,表名是derivedN的形式,N指向子查询,也就是explain结果中的下一列
  • 当有union result的时候,表名是union
    1,2等的形式,1,2表示参与union的query id

注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。

(4).UNION RESULT

 

看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

该列包含MySQL解决查询的详细信息,有以下几种情况:

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

 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column=other_table.column;


SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

五、possible_keys

在下面的例子中,MySQL可以使用ref联接来处理ref_tables:

Author

发表评论

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