mysql之explain

explain是提供一些信息关于增删查改(mysql 5.6之前只支持查)是如何执行的命令。
执行结果列下面说明下。
下面例子测试的表

CREATE TABLE `test_people` (
  `people_id` bigint(20) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `fist_name` varchar(50) NOT NULL,
  `db` date NOT NULL,
  `gender` enum('f','m') NOT NULL,
  `base_info` text NOT NULL,
  PRIMARY KEY (`people_id`),
  KEY `peopleBIKey` (`last_name`,`fist_name`,`db`) USING BTREE,
  FULLTEXT KEY `peopleBKey` (`base_info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `people` (
  `people_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) NOT NULL,
  `fist_name` varchar(50) NOT NULL,
  `db` date NOT NULL,
  `gender` enum('f','m') NOT NULL,
  `base_info` text NOT NULL,
  PRIMARY KEY (`people_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

id

• 查询标识符，这是查询中SELECT的连续编号
  
• 如果行引用其他行的联合结果，则该值可以为NULL
  
  这也说明联合查询是单独查询然后联合后的结果，结果放在临时表中。

select_type

select_type value	mean
SIMPLE	简单的查询没有使用union和子句
PRIMARY	最外面的查询
UNION	在union查询语句中的第二及之后select
DEPENDENT UNION	在union查询语句中的第二及之后select，依赖外面的子查询
UNION RESULT	在有union的查询中联合单独的查询结果为一个整体结果
SUBQUERY	在子查询中的第一个查询
DEPENDENT SUBQUERY	在子查询中的第一个查询，依赖外面的子查询
DERIVED	select的衍生表（From子句的子查询）
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	无法缓存结果的子查询，并且必须为外部查询的每一行重新计算。
UNCACHEABLE UNION	union里面的第二个及之后查询属于不可缓存子句

这个个人觉得看一遍都可以顾名思义没必要死记住，经常使用explain对照一下就行。

table

表名称，可以是虚拟的

type

连接类型：解释表是如何连接的

type value	mean
system	该表只有一行（= system table）。这是const连接类型的一个特例。
const	该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以这个行中的列值可以被其他优化器视为常量。const表非常快，因为它们只读过一次。当您将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时，会使用const
eq_ref	从这张表中读取一行，用于前面表格的每行组合。当一个索引的所有部分都被连接使用时，它被使用，索引是一个主键或唯一的非空索引。eq_ref可用于与使用=操作符进行比较的索引列。比较值可以是一个常量，也可以是一个表达式，它使用在表之前读取的表中的列。
ref	从该表中读取具有匹配索引值的所有行，用于来自先前表的各行的组合。ref被使用该索引不是唯一索引，不能根据键值选择单个行。
fulltext	连接使用FULLTEXT索引执行。
ref_or_null	这个连接类型有点像ref，但是需要mysql会额外搜索包含null值的行。这种连接类型优化常用于解析子查询。
index_merge	此连接类型表示使用索引合并优化。在这种情况下，输出行中的key列包含使用的索引列表，而key_len包含所用索引的最长键部分列表
unique_subquery	在一些子句中代替eq_ref。unique_subquery只是一个索引查找函数，它完全替代了子查询以提高效率。
index_subquery	类似于unique_subquery,只不过不是唯一索引。
range	只有在给定范围内的行被检索，使用索引来选择行。
index	对索引进行全部扫描，有点类似All，两种情况。1：如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则只扫描索引树。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。2：使用索引中的读取来执行全表扫描，以按索引顺序查找数据行。
ALL	对上一个表中的每一行的组合都进行了完整的表扫描。通常情况下，如果表是第一个未标记为const的表，并且在所有其他情况下通常都非常糟糕，那么这通常是不好的。通常情况下，您可以通过添加索引来避免所有的问题，这些索引可以根据前面的表中的常量值或列值来从表中进行行检索。

system

system可以理解为这张表读取的数据都是来自于内存中的并且只有一行数据，我测试的时候mysql 5.1能显示出来，5.7则显示

本应该这样，还有为啥没有用说明版本越高越聪明了，还有为啥没有用using where呢因为更高版本认为where字段在索引中那么using index已经包含了。

const

主键和唯一索引和常量比较时。

eq_ref

在搞这个连接的时候有点痛苦，一直搞不出来。根据理解写出如下：

type却是all，百度了下别人写的：

create table a(id int primary key);
create table a_info(id int primary key, title char(1));
insert into a value(1),(2);
insert into a_info value(1, 'a'),(2, 'b');
mysql> explain select * from a join a_info using(id);

我就搞不懂为啥两个都是主键连接为啥type不同，后来没办法一个一个比较发现mysql默认的是MyISAM引擎，然后换回innodb如下：

发现变成All了，但是第一行还是用了主键索引，但是我的people为啥没用上呢？因为我查询的全部信息，而且表里面就三条记录没必要用聚簇索引还不如直接去磁盘啦全部扫描，改成如下就和百度的一样了

现在大概能知道什么才是eq_ref：eq_ref的表字段自己是主键或者是唯一索引，它读取一行用来和其他表比较每行。
所以表示很无奈，首先写这些技术文档的人都是程序员语言表达能力一般都不怎么高，然后中英文语言又有隔阂。

这个等同于上面的，说明sql的执行不是你想象的那样（先查出test_people的所有people_id）；

ref

其实发现ref和eq_ref很像哦，只是索引类型不同，索引唯不唯一，不唯一可能匹配多行，所以理论上ref的性能比eq_ref差点。

fulltext

这个连接类型innodb也支持需要mysql版本是5.6.24及以上。

ref_or_null

看到这个我也是很迷茫，难道又搞不出来了，心灰意冷，搞那个eq_ref的时候费尽力气，但是还是冷静分析，为啥是All呢，该满足的都满足了，难道mysql优化器有毛病，其实不然看看具体数据。

看到这里了解了吗，我这里的last_name全部是w，哈哈因为博主姓王，优化器肯定是先看了看索引就一个结点，还不如自己去扫描表呢，不怪它怪我数据弄的不好，那就换

再看看

咋还是ref呢？后来发现因为我last_name是非空的优化器不会看你的语句有没有is null哦，所以把字段变为可空，结果呢

又变成all了，难不难受？想下哦，因为存在null列数据，null需要特殊处理需要找到null的列加上要查询所有列所以采用了全表扫描，改下看看

range

使用索引选择数据行。

index

下面的为啥不是主键呢

因为idx_order_status是tinyint主键是bigint更轻量加上主键索引是聚簇索引io压力会大。

ALL

总结

type那张表从上到下，连接类型从最好到最差，很明显system最好结果已经查出来了在内存中，const次于system唯一索引和常量比较一次查出结果，eq_ref次于const它用到的也是唯一索引但是要查询多次用于和其他表的某列所有行比较，ref次于eg_ref首先它不是唯一索引，它需要把满足条件的所有行读取出来再与其他表的某列所有行比较，fulltext次于ref它索引存储的内容比较多，ref_or_null次于fulltext类似于ref要多处理null字段mysql处理null字段比较麻烦，index_merge次于ref_or_null因为它没有使用组合索引而是把单个索引的查询结果在内存中进一步处理得到查询结果，unique_subquery次于index_merge涉及子语句需要缓存子语句的结果在进行处理但是用的是唯一索引所以比index_subquery好点，range次于index_subquery是范围查找很可能要扫描整个索引但是比一定要扫描全部索引的index好点，index又比all好点因为通常情况数据记录比索引记录大。

以上都是一般情况，具体据具体情况而定。可以发现一般情况唯一索引比一般索引好，索引越小越好，组合索引比单个索引联合处理好，没子语句比有子语句好，但是要根据具体业务来使用不要刻意。

possible_key

表示mysql可以选择用于查询的索引，可能会有多个。

key

mysql决定实际去使用的索引。

key_len

mysql决定实际去使用索引的长度，应该是字节长度。

ref

显示哪些列或常量与key列中指定的索引进行比较以从表中选择行。

rows

mysql认为需要比较多少行去执行这个查询，这个值是估值并不精确。

Extra

Extra列包含有关MySQL如何解析查询的附加信息。

Extravalue	mean
Child of ‘table’ pushed join@1	该表被引用为可以下推到NDB内核的联接中的表的子表。 仅在启用了下推连接时才适用于NDB群集。
const row not found	对于查询，例如SELECT…from tbl_name中，表为空。
Deleting all rows	对于DELETE，某些存储引擎（如MyISAM）支持一种处理器方法，该方法以简单快捷的方式删除所有表格行。 如果引擎使用此优化，则会显示此额外值。
Distinct	MySQL正在寻找不同的值，所以当它找到第一个匹配的行后，它会停止为当前行组合搜索更多的行。
FirstMatch(tbl_name)	tbl_name使用半连接FirstMatch加入快捷方式策略。
Full scan on NULL key	当优化器不能使用索引查找访问方法时，将子查询优化作为回退策略。
Impossible HAVING	HAVING子句总是错误的，不能查询任何行。
Impossible WHERE	WHERE子句总是错误的，不能选择任何行。
LooseScan(m…n)	使用半连接LooseScan策略。 m和n是key的部分数字。
No matching min/max row	没有一行满是查询条件例如 SELECT MIN(…) FROM … WHERE condition.
no matching row in const table	对于具有联接的查询，存在空表或没有行满足唯一索引条件的表
No matching rows after partition pruning	对于删除或更新，优化器在分区修剪之后没有发现任何删除或更新的内容。
No tables used	查询没有FROM子句，或者有FROM DUAL子句。
Not exists	MySQL能够对查询执行LEFT JOIN优化，在找到与LEFT JOIN条件匹配的一行之后，再找不到的更多行。
Plan isn’t ready yet	当优化程序尚未完成为在命名连接中执行的语句创建执行计划时，此值将与EXPLAIN FOR CONNECTION一起出现。如果执行计划输出包含多行，则根据优化器在确定完整执行计划中的进度，它们中的任何一个或全部可以具有此Extra值。
Range checked for each record	MySQL发现没有好的索引来使用，但发现一些索引可能在前面表格的列值已知之后使用。 对于上表中的每一行组合，MySQL检查是否可以使用range或index_merge访问方法来检索行。 这不是非常快，但比完成没有索引的连接要快。
Scanned N databases	这表示在处理INFORMATION_SCHEMA表的查询时，服务器执行多少个目录扫描
Select tables optimized away	优化器确定1）至多应该返回一行，2）为了产生该行，必须读取确定性的一组行。当在优化阶段读取的行可以被读取（例如，通过读取索引行）时，在查询执行期间不需要读取任何表
Skip_open_table, Open_frm_only, Open_full_table	这些值表示适用于对INFORMATION_SCHEMA表的查询的文件打开优化。Skip_open_table：表文件不需要打开。通过扫描数据库目录，可以在查询中获得信息。Open_frm_only：只需要打开.frm文件。Open_full_table: 实现信息查找。必须打开.frm、. myd和. myi文件。
Start temporary, End temporary	这表示半连接去除重复策略的临时表使用情况。
unique row not found	没有行满足表中唯一索引或主键的条件
Using filesort	一个排序无法使用索引来排序
Using index	只使用索引树中的信息从表中检索列信息，而不必执行额外的查找来读取实际行。 当查询仅使用属于单个索引一部分的列时，可以使用此策略。对于具有用户定义的聚簇索引的InnoDB表，即使Extra列中缺少使用索引，也可以使用该索引。 如果type是index或者key是PRIMARY，则是这种情况。
Using index condition	ICP优化相关，将数据过滤条件从server层面下推到存储引擎层，在存储引擎层根据索引元祖过滤数据，避免不符合条件的数据传给server。（非翻译，整理自网络。）
Using index for group-by	与using index类似，using index for group-by表明mysql找到一个索引，该索引可以返回所有group-by或distinct查询所需的列，不需要回表。
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)	做连接时，当前表之前的表被分多次读入join buffer中，然后join buffer中的表再跟当前表做join。EXPLAIN输出前一行表中的键将被缓冲,匹配的行将从使用加入缓冲区出现的行表示的表中批量提取。
Using MRR	使用多范围读取优化策略读取表格。
Using sort_union(…), Using union(…), Using intersect(…)	这些表明特定的算法显示了索引扫描如何合并index_merge表连接类型。
Using temporary	为了解决这个查询，MySQL需要创建一个临时表来保存结果。 如果查询包含GROUP BY和ORDER BY子句，它以不同的方式列出了各个字段,则通常会发生这种情况。
Using where	WHERE子句用于限制哪些行要匹配下一个表或发送给客户端。除非您专门打算从表中读取或检查所有行,如果Extra的值不是 using where 并且表的连接类型是 ALl或者是indexn你的查询语句可能有问题。
Using where with pushed condition	这个项目仅应用于NDB表。 这意味着NDB集群正在使用条件下推优化来提高非索引列和常量之间的直接比较效率。在这种情况下，条件被“推下”到集群的数据节点，并在所有数据节点上同时进行评估。这消除了通过网络发送不匹配的行的需要，可以将这些查询的速度提高5到10倍。
Zero limit	该查询有一个LIMIT 0子句，不能选择任何行。

Using where; FirstMatch(tbl_name); Using join buffer

FirstMatch(tbl_name)半连接快捷方式策略，就是有一点数据就做一次连接，Using join buffer分多次读入join buffer中，被用于连接之后的就可以清除很好的利用join buffer