Explain查询sql执行计划

只想搞技术的菜鸡
234 阅读9分钟

在我们优化一条sql语句的时候经常会使用到Explain语句来查看这条语句的执行计划。

如上图，我们可以看到查询结果中包含12个列，他们分别是

那么这12个列的具体值是什么呢？这些值有对应着什么情况呢？

首先我们先来创建两个测试表

// 测试表一
CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
// 测试表2
CREATE TABLE `person2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

属性一 id

首先，先来看三条sql

第一条

EXPLAIN SELECT * FROM person

它产生的结果是

第二条

EXPLAIN SELECT * FROM person p LEFT JOIN person2 p2 ON p.id=p2.id

它产生的结果是

第三条

EXPLAIN SELECT * FROM person WHERE id = (SELECT id FROM person2 WHERE id=1)

它产生的结果是

第四条

EXPLAIN SELECT *FROM person UNION SELECT *FROM person2

他产生的结果是

由以上几条sql我们可以看出当我们使用explain去查看一条select的执行计划时，每一张表就对应一条记录，但是在左连接中这两条记录的id是相同的，其实这不仅仅在左连接中，只要是由一个select关键字引起的SQL那么id就是一样的（如果是insert内带有子查询id也是一样的例如：INSERT INTO person SELECT * FROM person2）。但是为了区分驱动表和被驱动表，MySQL的开发大佬们将驱动表的记录放在了第一位，而后面的都是被驱动表。

如果我们的查询中嵌套子查询如第三条SQL，那么针对每个select关键字（其实不仅仅是select，对于update，insert，delete都可以）都会产生一个新的递增的id

如果我们使用union或者union all来连接两条语句的时候那么除了正常的两条记录以外我们还可以看到一条id为null的记录，如第四条sql。为什么会有第三条记录呢？我们看到Extra（额外信息）中有提示 using temporary（使用临时表）。所以这就是MySQL产生了一个临时表来合并记录，因为是临时的所以没有id。

属性二select_type

select_type字段记录着查询这个表的select类型，这个select类型不是简单指select语句，它也包含update，insert，delete。一般包含一下几种

SIMPLE简单SELECT(不使用UNION或子查询)
PRIMARY最外面的SELECT（比如使用union或子查询的第一个查询）
UNIONUNION中的第二个或后面的SELECT语句
DEPENDENT UNIONUNION中的第二个或后面的SELECT语句（在union外面还有个大查询）
UNION RESULTUNION的结果（使用union查询时使用的临时表）。
SUBQUERY子查询中的第一个SELECT（子查询是不相关子查询）
DEPENDENT SUBQUERY子查询中的第一个SELECT，取决于外面的查询（子查询是相关子查询）
DERIVED子查询使用物化的方式执行的

除此之外，如结果你用Explain去查看update，insert，delete的时候那么select_type显示的是对应的操作。

属性三 分区

分区是一个很复杂的事情，而且一般我们也用不到这块内容。如果感兴趣可以到中文官网去了解一下

www.mysqlzh.com/doc/169.htm…

属性四type

type的意思是连接类型，连接类型有以下几种，按照最佳到最坏进行排序

  1. system

    当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的（Innodb的统计数据是不精确的），比如MyISAM、Memory，那么对该表的访问方法就是system

  2. const

    当我们单表访问且根据主键或者唯一键进行等值查询的时候，我们最多有一个匹配行，此时的类型就是const。

    const和system类似都是最多匹配一行，唯一不同的是后者要求存储殷琼的统计数据是精确的。所以同一条sql在innodb中执行它的type就是const，比如：

    EXPLAIN SELECT *FROM person WHERE id =1

  3. eq_ref

    在连接查询时，如果被驱动表是通过主键或者唯一索引列等值匹配的方式进行访问的（如果该主键或者唯一索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref

  4. ref

    使用普通的二级索引（不是唯一索引或者主键索引）则使用ref

  5. ref_or_null

    该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。

    例如下面的sql

    SELECT * FROM person
WHERE id=1 OR name IS NULL;

  6. index_merge

    该联接类型表示使用了索引合并优化方法。例如我们使用or进行条件拼接

    EXPLAIN SELECT *FROM person WHERE name ='1' OR sex = '1'

  7. unique_subquery

    如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery

  8. index_subquery

    该联接类型类似于unique_subquery。可以替换IN子查询，但是子查询必须是非唯一索引

  9. range

    当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range

  10. index

    当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

  11. ALL

    全表扫描

属性possible_keys和key

possible_keys列表示在一个语句中可能遇到的索引有那些，key列表示实际用到的索引有哪些

另外需要注意的一点是，possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

属性key_len

key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，如果记录是NULL，则长度为NULL。

属性ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的是一个常数、某个列或者是某个函数。

比如查询语句

EXPLAIN SELECT *FROM person WHERE name ='1'

和name匹配的是一个常数，所以ref显示的就是const

如果是下面查询语句

EXPLAIN SELECT *FROM person ,person2 WHERE person.id = person2.id

那么person表的ref就是一个列

如果是如下查询语句

EXPLAIN SELECT *FROM person ,person2 WHERE person.id = upper(person2.id)

那么person表的ref就是一个函数

属性rows

rows列显示MySQL认为它执行查询时必须检查的行数。如上面当我们等值匹配name的时候，rows显示为1，意思就是预计扫描的行数为1行

属性filtered

其实这个属性对于单表查询来说并没有什么意义，比如我们在单表中使用between取某个列的区间，这个时候就是单纯的计算我们剩余数据的百分比。但是我们在实际开发中经常会遇到非常复杂的连接查询的sql语句，这个时候我们就可以通过filtered属性来计算被驱动表的被访问的次数。由此我们可以来决定是一次获取所有数据还是通过业务逐步获取。

属性Extra

  1. Distinct

    MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

  2. No tables used

    当查询语句的没有FROM子句时将会提示该额外信息

  3. Impossible WHERE

    查询语句的WHERE子句永远为FALSE时将会提示该额外信息

  4. No matching min/max row

    当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息

  5. Using index

    索引覆盖的情况下提示该额外信息

  6. Using index condition

    有些搜索条件中虽然出现了索引列，但却不能使用到索引

  7. Using where

    当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件

  8. Using join buffer (Block Nested Loop)

    在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法

  9. Not exists

    当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的

  10. Using intersect(...)、Using union(...)和Using sort_union(...)

    如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询；出现了Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询

  11. Zero limit

    当我们的LIMIT子句的参数为0

  12. Using filesort

    某个查询需要使用文件排序的方式执行查询

  13. Using temporary

    使用临时表

  14. Using index for group-by

    类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目

参考文档：MySQL 是怎样运行的：从根儿上理解 MySQL MySQL 5.1中文文档

avatar
文章
阅读
粉丝