数据库 explain详解


文章目录

  • 数据库 explain详解
  • 1、什么是explain
  • 2、explain有什么用
  • 3、explain个字段详解
  • 3.1 id详解
  • 3.2 select_type 详解
  • 3.3 table 详解
  • 3.4 type 详解
  • 3.5 possible_keys
  • 3.6 key
  • 3.7 key_len
  • 3.8 ref
  • 3.9 rows
  • 3.10 extra
  • 4、关于MySQL执行计划的局限性


1、什么是explain

explain即查看执行计划，使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

2、explain有什么用

（1）表的读取顺序
（2）数据读取操作的操作类型
（3）哪些索引可以使用
（4）哪些索引被实际使用
（5）表之间的引用
（6）每张表有多少行被优化器查询

3、explain个字段详解

explain的信息组成如下：

extmem数据库 explain数据库_MySQL

3.1 id详解

id列的编号就是select 的序列号，也可以理解为SQL执行顺序的标识，有几个select 就有几个id。

（1）id值不同：如果是只查询，id的序号会递增，id值越大优先级越高，越先被执行；

（2）id值相同：从上往下依次执行；

（3）id列为null：表示这是一个结果集，不需要使用它来进行查询。

3.2 select_type 详解

这个字段主要是查询的类型，用于区分普通查询、联合查询、子查询等复杂的查询；

（1）simple：最简单的查询，表示查询中不包括union操作或者子查询。

例如：
explain select * from user where id = 1;
      simple

（2）primary：需要union操作或者含有子查询的select，位于最外层的查询的select_type即为primary，且只有一个。

例如：
explain select * from （select * from user where id = 1）newuser;
    primary                 derived

（3）derived：from列表中出现的子查询，也叫做衍生表；mysql或者递归执行这些子查询，把结果放在临时表里。

例如：
explain select * from （select * from user where id = 1）newuser;
    primary                 derived

（4）subquery：除了from子句中包含的子查询外（derived），其他的方出现的子查询都可能是subquery。

例如：
expalin select * from user where id = (select id from orders where ordreid = 1)
        primary                              subquery

（5）union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived。

例如：
EXPLAIN SELECT username FROM USER WHERE uid = 1 UNION  
        SELECT money FROM orders WHERE state = 1
   primary                     union        union result

（6）union result：从union表获取结果的select ，因为它不需要参与查询，所以id字段为null。

例如：
EXPLAIN SELECT username FROM USER WHERE uid = 1 UNION  
        SELECT money FROM orders WHERE state = 1
   primary                     union        union result

3.3 table 详解

表示explain 的一行正在访问哪个表。

（1）如果查询使用了别名，那么这里显示的是别名;

（2）如果不涉及对数据表的操作，那么这显示为null;

（3）如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生;

（4）如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

3.4 type 详解

type显示查询使用了何种查询范围，主要有以下几种，从最好到最坏依次是：
system > const > eq_ref >ref > range > index > ALL

（1）system：表中只有一行数据（等于系统表），这是const 类型的特例，平时不会出现，可以忽略不计。

（2）const：使用唯一索引或者主键，表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。

例如：
EXPLAIN SELECT * FROM USER WHERE uid = 1

结果：

extmem数据库 explain数据库_子查询_02


（3）eq_ref：唯一性索引扫描，对于每个索引键，表中只有一行数据与之匹配。常见于主键或唯一索引扫描。

例如：
创建了一张学生表students
CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(22) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

创建了一张班委表
CREATE TABLE `montinors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` varchar(22) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

EXPLAIN SELECT * FROM montinors , students WHERE montinors.id = students.`id`
那么班委也属于学生表中的一部分，即班委表中的每一个id,在学生表中只有一行与之对应。

结果：

extmem数据库 explain数据库_extmem数据库_03

（4）ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质也是一种索引。

（5）range：索引范围扫描，常见于使用>,<,between ,in ,like等运算符的查询中。

（6）index：索引全表扫描，把索引树从头到尾扫一遍。index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Indx都是读全表，但index是从索引中读取的，而all是从硬盘中读的)

（7）all：遍历全表以找到匹配的行（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

（8）NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。

3.5 possible_keys

显示查询可能使用到的索引。

3.6 key

显示查询实际使用哪个索引来优化对该表的访问；

select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

3.7 key_len

用于处理查询的索引长度，表示索引中使用的字节数。通过这个值，可以得出一个多列索引里实际使用了哪一部分。

注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

3.8 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

例如：

extmem数据库 explain数据库_extmem数据库_04

3.9 rows

表示MySQL根据表统计信息及索引选用情况，大致估算的找到所需的目标记录所需要读取的行数，不是精确值。

3.10 extra

不适合在其他列中显示但十分重要的额外信息。

类型

说明

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

4、关于MySQL执行计划的局限性

（1）EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；

（2）EXPLAIN不考虑各种Cache；

（3）EXPLAIN不能显示MySQL在执行查询时所作的优化工作；

（4）部分统计信息是估算的，并非精确值；

（5）EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看。