SQL从大到小的执行.,以及当运行SQL语句时哪种策略预计会被优化器采用

发布时间:2019-10-14  栏目:数据  评论:0 Comments

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

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 id from t3 where
id in (select id from t3 where id=3952602 )  ;

UNION RESULT:union的结果 explain select name from car_info where
id =100 union select name from web_car_brands where id =5;
+——+————–+—————-+——-+—————+———+———+——-+——+—–
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+——+————–+—————-+——-+—————+———+———+——-+——+–
| 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 |
| | 2 | UNION | web_car_brands | const | PRIMARY,id | PRIMARY | 4 |
const | 1 | | | NULL | UNION RESULT | <union1,2> | ALL | NULL |
NULL | NULL | NULL | NULL | |
+——+————–+—————-+——-+—————+———+———+——-+——+—-
SUBQUERY:子查询中的第一个SELECT语句 explain select name from
car_info where id = (select id from web_car_series where id = 5);
+—-+————-+—————-+——-+—————+———+———+——-+——+—–
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+—-+————-+—————-+——-+—————+———+———+——-+——+——
| 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 |
| | 2 | SUBQUERY | web_car_series | const | PRIMARY | PRIMARY | 4 | |
1 | Using index |
+—-+————-+—————-+——-+—————+———+———+——-+——+—–

key_len

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

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

+—-+————-+——-+——+——————-+———–+———+———————-+——+——-+
| 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    | ref  | PRIMARY,idx_t3_id | idx_t3_id |
4       | dbatest.t4.accountid |    1 |       |
+—-+————-+——-+——+——————-+———–+———+———————-+——+——-+

DERIVED:在from列表中包含子查询,mysql会递归的执行该子查询,并把结果放在临时表中
explain select * from (select name from car_info where id = 100) a;
+—-+————-+————+——–+—————+———+———+——+——+-
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+——+——+-
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL |
1 | | | 2 | DERIVED | car_info | const | PRIMARY | PRIMARY | 8 | | 1 |
|
+—-+————-+————+——–+—————+———+———+——+——+-
type列: MySQL
在表里找到所需行的方式包括如下几张(由左至右,由最差到最好):
All–>index–>range–>ref –>eq_ref–>const,system
–>null
ALL:进行全数据表扫描
index:按照索引的次序扫描表,先读索引,然后读取具体的数据行,其实还是全表扫描,好处在于不用排序,按照索引的顺序
range:按照某个范围读取数据行 ref:非唯一性索引访问
eq_ref:使用唯一性索引访问(主键或者唯一性索引)
const:最多只有一个匹配行,const常用于数值比较如 primary key
null:在优化过程中已经得到结果,不需要访问表或者索引 如:explain select
min(id) from car_info;
possible_keys列:
possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

table

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

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

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

 

(5).SUBQUERY

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
explain select name from car_info where id in (select id from
web_car_series where id = 5);
+—-+——————–+—————-+——-+—————+———+———+——-+——+-
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+—-+——————–+—————-+——-+—————+———+———+——-+——+
| 1 | PRIMARY | car_info | index | NULL | name | 768 | NULL | 145 |
Using where; Using index | | 2 | DEPENDENT SUBQUERY | web_car_series |
const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+—-+——————–+—————-+——-+—————+———+———+——-+—–

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

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

+—-+————-+———-+——-+—————+——+———+——+——+—————–
| id | select_type | table | type |…

id

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

三、table

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

 使用EXPLAIN语句检查优化器操作
+—-+————-+———-+——-+—————+——+———+——+——+—————–
| id | select_type | table | type | possible_keys | key | key_len|
ref | rows | Extra
+—-+————-+———-+——-+—————+——+———+——+——+—————–
| 1 |SIMPLE | car_info | range | name | name | 768 | NULL | 9 | Using
where; Using index |
+—-+————-+———-+——-+—————+——+———+——+——+—————-
EXPLAIN输出解释
select_type 有如下几种类型:
SIMPLE:未使用连接查询或者子查询的简单select语句 explain select *
from car_info;
PRIMARY:最外层的select语句 explain select * from (select name from
car_info where name like ‘凯迪拉克%’) as a;
+—-+————-+————+——-+—————+——+———+——+——+————-
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+—-+————-+————+——-+—————+——+———+——+——+—————
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 |
|
| 2 | DERIVED | car_info | range | name | name | 768 | NULL | 9 | Using
where; Using index |
+—-+————-+————+——-+—————+——+———+——+——+—————
UNION:union中的第二个,或后面的select语句 explain select name from
car_info where id =100 union select name from web_car_brands where id
=5;
+——+————–+—————-+——-+—————+———+———+——-+——+–
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+——+————–+—————-+——-+—————+———+———+——-+——+—
| 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 |
| | 2 | UNION | web_car_brands | const | PRIMARY,id | PRIMARY | 4 |
const | 1 | | | NULL | UNION RESULT | <union1,2> | ALL | NULL |
NULL | NULL | NULL | NULL | |
+——+————–+—————-+——-+—————+———+———+——-+——+—

rows

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

rows

MySQL认为必须检查的用来返回请求数据的行数,约小越快;InnoDB
存储引擎这个值是一个大约值。

const用于用常数值比较PRIMARY
KEY或UNIQUE索引的所有部分时。在下面的查询中,tbl_name可以用于const表:

如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询
key列
key列显示MySQL实际决定使用的键(索引)。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE
INDEX、USE INDEX或者IGNORE INDEX。
key_len列
key_len列显示MySQL决定使用的键长度。使用的索引的长度,在不损失精确性的情况下,长度越短越好
rows列 rows列显示MySQL认为它执行查询时必须检查的行数
 mysql相关优化技巧
尽量使用数据类型相同的数据列进行比较
使带索引的数据列在比较表达式中单独出现
不要在like模式的开始位置使用通配符,此时索引无效
尽量使用数值操作,少使用字符串操作
数据类型合理选用,尽量”小”,选择适用于存储引擎的数据格式
尽量将数据列声明为NOT NULL
,因为MYSQL不需要在查询处理期间检查数据列值是否为NULL
考虑使用ENUM数据列,ENUM在MYSQL内部被表示为一系列数值,处理速度快
利用Procedure analyse()语句
该语句可以将数据列中可以采用ENUM方式字段列出,procedure
analyse(16,256)语句表示数据列中不同取值超过16个的或者长度超过256个字节的,不提出ENUM类型的建议
对容易产生碎片化的数据表进行整理,对于可变长度的数据列,随着数据的大量修改或者删除极易产生碎片,因此需要定期optimize
table
尽量避免对BLOB或TEXT值进行索引

key

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

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

(7).Using where

DEPENDENT
UNION:union中的第二个或后面的色了传统语句,取决于外面的查询

mysql> explain select * from t3 where id in (select id from t3 where
id=3952602 union all select id from t3) ;
+—-+——————–+————+——–+——————-+———+———+——-+——+——
| 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 | |
+—-+——————–+————+——–+——————-+———+———+——-+——+-

possible_keys

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

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

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

ref

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

ref

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

例如

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

 

 

Mysql Explain 这里做一个资料的全面整理。

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

 

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

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等

 

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;

图片 2

对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

图片 3

  1. ref

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

(10).index

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

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

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

1 row in set (0.02 sec)

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

  1. Extra

Using join
buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

(2). PRIMARY

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

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

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

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

(4).DEPENDENT UNION

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)

如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

显示这一行的数据是关于哪张表的,有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

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

expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释:

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候

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查询中的第二个或之后的查询,例如:

    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
     不同于SUBQUERY的是,查询结果被外层依赖。例如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. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+

UNION的结果。

八、ref

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

 

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

九、rows

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

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

(7). unique_subquery

type

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

  • system 这是const联接类型的一个特例,很少出现,例如 DERIVED
    中的例子。
  • 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中包含 OR 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. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

该类型替换了下面形式的IN子查询的ref:

                

7.key_len

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

(4).Using filesort

十、Extra

子查询中的第一个SELECT.

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)

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

filtered

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

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

相关文章

留下评论

网站地图xml地图