MySQL执行计划

本文基于MysqL 5.7编写，对于其它版本也适用

(一)执行计划概述
什么是执行计划呢？sql是一种傻瓜式语言，每一个条件就是一个需求，访问的顺序不同就形成了不同的执行计划。MysqL必须做出选择，一次只能有一种访问路径，一个访问路径就是一个执行计划。
通常一条sql有多个执行计划，那我们如何选择？MysqL 数据库与Oracle一样，使用的是基于开销(cost)的优化器策略，那种执行开销更低，就意味着性能更好，速度更快，MysqL就选择哪一种。

(二)执行计划的查看
MysqL 数据库的执行计划可以通过explain关键字查看，使用explain可以查看SELECT,DELETE,INSERT,REPLACE,UPDATE语句的执行计划。对于SELECT语句，还可以使用SHOW WARNINGS查看额外的执行计划信息。可以在数据库中查看explain的帮助：

MysqL> help explain
 Name: 'EXPLAIN'
 Description:
 Syntax:
 {EXPLAIN | DESCRIBE | DESC}
     tbl_name [col_name | wild]

{EXPLAIN }
     explain_type
     {explainable_stmt FOR CONNECTION connection_id}

explain_type: { EXTENDED  | PARTITIONS  | FORMAT = format_name }
 format_name: { TRADITIONAL  | JSON }

explainable_stmt: {
     SELECT statement
   DELETEINSERT| REPLACEUPDATE statement
 }

需要注意的是：
1.在早期的MysqL版本中，使用EXTENDED查看扩展信息，目前默认已经启用了扩展信息的输出，因此该参数显得多余了，在MysqL 8.0中已经移除该参数。
2.在早期版本中，分区信息是使用EXPLAIN PARTITIONS输出的，目前已经默认开启了分区信息的输出，该参数也已经不再需要，在MysqL 8.0中已经移除该参数。
3.不能在同一个EXPLAIN中同时使用EXTENDED和PARTITIONS关键字，这2个关键字都不能与FORMAT关键字一起使用。

FORMAT参数用于选择输出格式，一共有2种输出格式：
-- TRADITIONAL ：以表格显示输出，默认模式
-- JSON ：以json格式输出
此外，在MysqL 8.0中还提供了TREE方式输出，这里暂时不作了解，后面单独说明。

总结一下，EXPLAIN的语法看着较为复杂，实则非常简单。在去除过时参数后，真正可选的参数只有一个FORMAT=json，其它参数都不用选。所以最终执行计划有2种输出形式：

-- 以表格格式输出执行计划，默认方式
EXPLAIN sql_stmt

 以json格式输出执行计划
EXPLAIN FORMAT=JSON sql_stmt

(三)执行计划解释
（3.1）执行计划基础信息
这里我们以下面查询的执行计划为例，来解释执行计划中各个列的含义。
首先以TRADITIONAL格式查看执行计划：

再以JSON格式查看执行计划：

> explain format = json b.deptno;

… 结果略 …

可以看到，两种格式输出的信息基本相同，但是也存在不一样的地方，个人觉得最大的区别在于：josn格式的执行计划把cost给展示出来了，MysqL优化器是基于cost选择执行计划的，查看cost对于调优很重要。但是，在实际的使用过程中，我们往往会以表格的形式查看执行计划，因为表格形式的执行计划较为简练，便于我们查看。本文在讲解执行计划时，也只使用表格格式。

这里解释各个列的含义：

id：查询标识符。在所有组中，id值越大，优先级越高，越先执行，id如果相同，可以认为是一组，从上往下顺序执行；
例子1：id相同，执行顺序从上到下，下面例子的执行顺序为：t1 --> t2 --> t3

例子2：id不同，执行顺序为id从大到小，下面例子的顺序为：t3 --> t2 –> t1

例子3：id相同又不同。id相同，可以认为一组，从上往下执行，在所有组中，id值越大，优先级越高，越先执行。下面例子的执行顺序为：t1 --> <derived2> --> t2

select_type：select_type可选的参数较多，如下：
select值                               含义
----------------------             -------------------------------------
SIMPLE                               简单的select查询，未使用UNION和子查询
PRIMARY                             查询中包含任何复杂的子部分，则被标记为PRIMARY，PRIMARY为最外层查询，最后执行
UNION                                第2个SELECT在UNION之后，则被标记为UNION
DEPENDENT UNION             含有UNION查询的第二个或最后一个表，依赖外部的查询
UNION RESULT                    UNION结果
SUBQUERY                          在SELECT或WHERE中包含的子查询
DEPENDENT SELECT            子查询中的第一个SELECT，依赖外部的查询
DERIVED                             衍生表，衍生表是FROM子句中子查询的内部名称
MATERIALIZED                    物化子查询
UNCACHEABLE SUBQUERY   子查询，其结果无法缓存，必须针对外部查询的每一行进行评估
UNCACHEABLE UNION          在UNION里的第二个或最后一个表属于不可缓存的子查询

table：输出所使用的表名称，也可以是：
   -- <unionM,N>：该行是id为M和N的行的并集
   -- <derivedN>：该行是id为N的行的派生表
   -- <subqueryN>：该行是物化子查询的结果
以这个执行计划为例，访问顺序为：t1 --> derivered2 --> t2。其中”derived2”中的2是t1的id值。

partitions：该参数用于记录使用的分区信息，NULL表示该表不是分区表

create table t_part_table ( id int primary key,col2 varchar(20) ) PARTITION by range(id) ( partition p100 values less than(100),partition p200 200300400500600700800900values less than MAXVALUE ); CREATE DEFINER=`root`@`%` PROCEDURE `p_insert_part_table`() BEGIN #Routine body goes here... DECLARE str1 30); DECLARE i int; set i = 1while i <= 2000 do set str1 = substring(md5(rand()),1); font-weight: bold">1,1); font-weight: bold">15insert into t_part_table(id,col2) (i,str1); = i + ; end whileEND

使用分区的样例如下：

type：连接类型，见后面"执行计划连接类型type"
possible_keys：在该查询中，MysqL可能使用的索引，如果此列是NULL，则没有相关的索引，在这种情况下，需要检查WHERE字句，以确定是否适合创建索引
key：MysqL实际使用的索引。在大多数情况下，key中的值都在possible_key里面，但也会出现possible_key不存在该值，但key里面存在的情
key_len：该列指MysqL决定使用的索引长度。该值体现了在使用复合索引的时候，使用了复合索引的前面哪几个列(需要根据字段长度计算)，如果key列为NULL，则该列也为NULL。由于key存储的格式原因，可以为NULL的列的key长度比NOT NULL的列长度大1。
ref：ref列显示哪些列或者常量与key中的索引进行比较，以从表中选择行
例子1：这里显示常量与主键进行比较，选择相应的行

例子2：这里在查询t2、t3表的时候，使用主键进行查询，并且使用t1.id列与主键进行比较过滤，选择合适的列

rows：MySQL查询需要遍历的行数，对于innodb表，可能并不总是准确的。这里需要特别注意，Oracle数据库的执行计划里面也有rows列，不过代表结果的行数，含义不一样
filtered:被条件过滤的行数百分比。最大值为100，表示没有行过滤，值从100减小表示过滤增加。rows表示检查的行数，rows * filtered/100表示过滤后的行数，也就是与下表进行连接的行
Extra：执行计划的额外信息，见后面"执行计划额外信息Extra"

（3.2）执行计划连接类型type
explain的type列表示表的连接类型，从最佳到最差类型如下(其中黑体部分是常见的重点类型)：
System --> const --> eq_ref --> ref --> fulltext --> ref_or_unll --> index_merge --> unique_subquery --> index_subquery --> range --> index --> ALL

这里解释各个类型的含义：

system : 该表只有一行，这是const连接的特殊情况，平时不会出现，可以不用重点注意
const：该表最多只有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中的值做为常量，因为它值读取一次。const在基于主键或者唯一性索引比较时使用。

eq_ref：与驱动表的连接查询，后表(被驱动表)仅读取一行数据，当被驱动表存在主键索引或者unique+not null时使用，eq_ref用于使用"="运算的索引列（参考：https://oomake.com/question/1081106）
ref：与驱动表的连接查询，后表(被驱动表)读取一行或多行数据。ref用于仅使用key的最左前缀，或者说key不是PAIMARY KAY或UNIQUE索引。换句话说，如果连接无法根据KEY选择单个行，则使用ref，否则使用eq_ref。ref可以用于使用"="或者"<=>"运算符进行的比较

fulltext：使用FULLTEXT进行连接
ref_or_null：这种方式类似于ref，但是MysqL会额外搜索包含NULL值的行

index_merge：索引合并优化，把多个索引合并为一个使用
unique_subquery：该类型替换eq_ref形式下的IN子查询，子查询中最多返回一个值，提高查询效率：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery：该类型类似于unique_subquery，它用来替代子查询，子查询中返回值的个数不确

SELECT key_column range：使用索引去检索一个范围的行数据，key列是使用到的索引，ref列为NULL。range可以在=，<>，>,>=,<,<=,IS NULL,<=>,BETWEEN,LIKE或IN()

index：index与ALL相似，只是索引树被扫描，对应2种情况：
a.索引本省存储相关的列数据，如果索引中的数据可满足查询需求，则仅扫描索引树，在这种情况下，Extra列显示为Using index。
b.使用对索引的读取执行全表扫描，以按顺序查找数据行，在这种情况下，Extra没有出现Using index。

ALL：全表扫描，如果驱动表不是以const方式获取数据的，则可以会导致非常糟糕的查询性能。通常可以添加索引来避免权标扫描

（3.3）执行计划额外信息Extra
Extra列用于输出EXPLAIN的额外信息，这里说明了可以在此列中显示的值，因为这只值较多，这里先列出重要的信息和我能理解的信息，有的参数翻译过来实在不理解什么意思，就忽略了，见谅。

const row not found：诸如这样的查询SELECT ... FROM table_name，表为NUL
Deleting all rows：对于DELETE操作，某些存储引擎(如MYISAM)支持一种方法，能够快速删除所有数据，如果删除数据时使用到这种方法，Extra列就显示该值
Dintinct：MysqL正在寻找不同的值，因此在找到第一个匹配的行后，将停止当前行搜索更多
FirstMatch(table_name)：半连接快捷方式用于该表
Full scan on NULL key:当优化器无法使用索引访问时，子查询优化作为一个备用策略（不明觉厉）
Impossible HAVING:该HAVING子句始终为false，无法选择任何行
Impossible WHERE：该WHERE子句始终为false，无法选择任何行

No matching min/max row:没有行满足查询的条件，例如SELECT MIN(...) FROM ... WHERE condition
No matching row in const table：对于具有连接的查询，存在一个空表或没有满足唯一索引条件的行的表
No matching rows after partition pruning：在分区修剪后，优化器未发现任何要删除或更新的内容
No table used：查询没有FROM子句，或者有FROM DUAL子句
Not exists：MysqL能够对LEFT JOIN进行优化，并且在找到符合LEFT JOIN条件的一行后，不检查该表中的值相等的行，例如:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL

假设t2.id定义为NOT NULL，在这种情况下，MysqL扫描t1表，并且使用t1.id查找t2中id相等的数据，如果在t2中找到与t1中id相等的行，MysqL知道t2.id从来不会为NULL，因此不会再继续扫描t2表中id相等的值。换句话说，对于t1的每一行数据， MysqL仅仅需要找到t2中与之id相同的一条数据即可，不论t2中有多少条与t1中id相同的数据

Scanned N databases：这表明在处理information_schema表查询时服务器执行了多少目录扫描。N值可能是0、1、all
unique row not found：使用唯一性索引、主键索引查询数据，为匹配到行数据
Using filesort：MysqL必须额外找出如何按顺序检索行。通过根据连接类型遍历所有行并存储与该where子句匹配的所有行的排序键和指向该行的指针来完成排序。然后对键进行排序，并按排序顺序检索行

Using index：仅使用索引树中的信息从表中检索列信息，不需要回表访问数据
Using index for group-by：表示MysqL找到了一个索引，该索引可用于检索GROUP BY或DISTINCT查询的所有列，而无需对实际表进行额外的磁盘访问
Using join buffer(Block Nested Loop)，Using join buffer(Batched key Access)：表示使用块嵌套循环算法，并使用批处理秘钥访问算法
Using Temporary：为了查询，MysqL需要创建一个临时表来保存结果。这种情况通常发生在GROUP BY、ORDER BY查询中

Using where：使用where去过滤返回客户端的数据行数
Zero limit：查询使用了LIMIT 0，没有返回任何行

【完】

上一篇：在一台Linux服务器上安装多个MySQL 下一篇：MySQL审计audit

MySQL执行计划

猜你在找的MySQL相关文章