MySQL之EXPLAIN全方位详解

2020-12-12 本文已影响0人超_onlyu

EXPLAIN的应用场景

日常工作中，我们通常会打印出耗时较长的SQL语句，然后对SQL语句进行分析和优化，这时候就可以使用EXPLAIN来查看SQL语句的执行计划，解析出SQL语句是否有效利用了索引、是否进行了全表扫描等等，来辅我们进行SQL优化。

概述

EXPLAIN关键字可以模拟查询优化器执行SQL语句，从而分析出SELECT语句的执行逻辑，帮助我们排查SQL语句的性能问题点，更有效的利用索引来优化SQL语句。

MYSQL查询优化器最主要的目标是尽可能地使用索引，并且使用最严格的索引来排除尽可能多的数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快，那么找到与条件匹配的数据行的速度也就越快。如果能够首先进行最严格的测试，查询就可以执行地更快。

EXPLAIN属性

首先我们先准备两张表

CREATE TABLE `sc_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uni_key` varchar(32) NOT NULL COMMENT '唯一编码',
  `key_1` varchar(128) NOT NULL COMMENT '普通索引1',
  `c_key_1` int(10) unsigned NOT NULL COMMENT '联合索引1',
  `key_2` varchar(32) NOT NULL DEFAULT '' COMMENT '普通索引2',
  `c_key_2` varchar(32) NOT NULL COMMENT '联合索引2',
  `col_1` int(10) NOT NULL COMMENT '普通列',
  `c_key_3` int(10) NOT NULL COMMENT '联合索引3',
  `col_2` text NOT NULL COMMENT '普通列2',
 `key_3` int(11) DEFAULT NULL COMMENT '普通索引3',
  PRIMARY KEY (`id`) COMMENT 'ID',
  UNIQUE KEY `uni_key` (`uni_key`) USING BTREE,
  KEY `idx_c_key123` (`c_key_1`,`c_key_2`,`c_key_3`) USING BTREE,
  KEY `idx_key_1` (`key_1`) USING BTREE,
  KEY `idx_key_2` (`key_2`) USING BTREE,
  KEY `idx_key_3` (`key_3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8mb4 COMMENT='表';

CREATE TABLE `sc_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uni_key` varchar(32) NOT NULL COMMENT '唯一编码',
  `key_1` varchar(128) NOT NULL COMMENT '普通索引1',
  `c_key_1` int(10) unsigned NOT NULL COMMENT '联合索引1',
  `key_2` varchar(32) NOT NULL DEFAULT '' COMMENT '普通索引2',
  `c_key_2` varchar(32) NOT NULL COMMENT '联合索引2',
  `col_1` int(10) NOT NULL COMMENT '普通列',
  `c_key_3` int(10) NOT NULL COMMENT '联合索引3',
  `col_2` text NOT NULL COMMENT '普通列2',
 `key_3` int(11) DEFAULT NULL COMMENT '普通索引3',
  PRIMARY KEY (`id`) COMMENT 'ID',
  UNIQUE KEY `uni_key` (`uni_key`) USING BTREE,
  KEY `idx_c_key123` (`c_key_1`,`c_key_2`,`c_key_3`) USING BTREE,
  KEY `idx_key_1` (`key_1`) USING BTREE,
  KEY `idx_key_2` (`key_2`) USING BTREE,
  KEY `idx_key_3` (`key_3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8mb4 COMMENT='表1';

两个表字段相同，id都是主键索引，uni_key是唯一索引，key_1、key_2和key_3是普通索引，c_key_1与c_key_2与c_key_3三个字段建立了联合索引。
看一下EXPLAIN的所有属性列

EXPLAIN SELECT * FROM sc_table;

列含义

下面先简单列出每个属性列的含义，后面展开叙述

项	说明
id	在一个大查询中每个SELECT关键字都对应一个唯一的id
select_type	当前SELECT对应的查询类型
table	当前SELECT对应的表名
partitions	匹配的分区信息（已过时）
type	对当前表的关联类型或访问类型
possible_keys	可能用到的索引
key	实际使用到的索引
key_len	实际使用到的索引⻓度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息`(列或常量)`
rows	Mysql预估的需要读取的记录行数
filtered	表经过检索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

id

EXPLAIN每一行表示一个小SELECT的执行信息。每个SELECT对应一个id。表示执行SELECT子句的优先顺序，id 值越大优先级越高，越先被执行。id 相同，执行顺序由上至下，id值为 NULL 则最后执行。

EXPLAIN SELECT (SELECT key_2 FROM sc_table WHERE key_1 = 'b000002');

可以看到查询语句的两个SELECT关键字分别对应两行id，会先执行id=2的SELECT。

三种特殊场景
一、
MySQL查询优化器（Mysql Query optimizer）可能会对含有子查询的查询语句进行优化重写，转换为连接查询。所以我们就可以通过EXPLAIN来查看查询优化器是否对子查询进行了重写：

EXPLAIN SELECT * FROM sc_table WHERE key_1 IN (SELECT key_1 FROM sc_table_1);

如上，查询语句中有一个子查询，但执行计划中两个表的id都是1，说明查询优化器将子查询转换成连接查询。
二、
带UNION子句的查询，每个SELECT关键字同样会对应一个id，但是还有一点不通的特征，如下：

EXPLAIN SELECT * FROM sc_table UNION SELECT * FROM sc_table_1;

UNION会合并多个查询结果集，并使用临时表方式对结果集记录去重，如上边的查询计划所示，UNION创建了一个名为<union 1,2>的内部临时表（图中第三行），id是NULL说明是用于合并UNION两侧的结果集而创建的临时表。
三、
连接查询

EXPLAIN SELECT * FROM sc_table_1 INNER JOIN sc_table;

上面连接查询中表sc_table_1和sc_table分别对应一个id，值都是1。说明在连接查询的查询计划中每个表会对应一条记录，记录id相同，第一条的表是驱动表，第二条的表是被驱动表，在上面的查询中sc_table_1是驱动表，sc_table是被驱动表。

select_type查询类型

MySQL为查询计划的每个小查询都定义了select_type属性，代表小查询在整个查询计划中扮演的⻆色，select_type的取值列表：

项	说明
SIMPLE	简单的SELECT（不使用UNION或子查询）
PRIMARY	最外层的SELECT
UNION	UNION中的第二个或随后的SELECT查询，不依赖于外部查询的结果集
UNION RESULT	一个UNION的结果
DEPENDENT UNION	UNION中的第二个或随后的SELECT查询，依赖于外部查询的结果集
SUBQUERY	子查询中的第一个SELECT查询，不依赖于外部查询的结果集
DEPENDENT SUBQUERY	第一个SELECT子查询，依赖于外部查询的结果集
DERIVED	临时（派生）表用于FROM子句里有子查询的情况。MySQL会递归执行这些子查询，把结果放在临时表里。
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新为外层查询的每一行进行评估。
UNCACHEABLE UNION	UNION中的第二个或更后面的SELECT查询属于不可缓存的子查询

SIMPLE
查询语句中不包含UNION、子查询的查询语句都属于SIMPLE类型，单表查询都是SIMPLE类型。

EXPLAIN SELECT * FROM sc_table;

EXPLAIN SELECT * FROM sc_table_1 INNER JOIN sc_table;

PRIMARY
一个大查询语句，若包含一个或多个UNION、UNION ALL、子查询、连接查询等，这个大查询就由多个小查询组成，其中最左侧的小查询的select_type值就是PRIMARY，如：

EXPLAIN SELECT (SELECT key_2 FROM sc_table WHERE uni_key = 'b000002');

图1

EXPLAIN SELECT * FROM sc_table UNION SELECT * FROM sc_table_1;

图2

UNION
一个大查询语句，若包含一个或多个UNION、UNION ALL、子查询，这个大查询就由多个小查询组成，除了最左侧PRIMARY小查询以外，其余的小查询select_type值就是UNION，如上方图2。

UNION RESULT
包含UNION的查询，MySQL使用临时表实现UNION两侧结果集的合并、去重，该临时表的查询的select_type值就是UNION RESULT，如上方图2。

SUBQUERY
如果包含子查询的查询语句不能转化为semi-join（半连接）形式，并且该子查询不是相关子查询，并且查询优化器决定采用将该子查询物化注2的方案来执行该子查询时，该子查询的第一个SELECT关键字小查询的select_type就是SUBQUERY。换一种方式讲就是子查询被包含在SELECT中但不在FROM子句中，并且父查询和子查询FROM条件中没有关联字段的子查询就是SUBQUERY，如上方图1。这里需要注意一点，由于select_type为SUBQUERY的子查询会被物化，所以只会执行一遍。

DEPENDENT SUBQUERY
如果包含子查询的查询语句不能转化为semi-join（半连接）形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字小查询的select_type就是DEPENDENT SUBQUERY，如下：

EXPLAIN SELECT * FROM sc_table 
WHERE key_1 IN (SELECT key_1 FROM sc_table_1 WHERE key_1 = 'a002' 
UNION SELECT key_1 FROM sc_table_1 WHERE key_1 = 'a001');

图3

这里需要注意一点，select_type 为 DEPENDENT SUBQUERY 的查询可能会被执行多次。

DEPENDENT UNION
在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询，那么UNION之后的小查询的select_type的值就是 DEPENDENT UNION，如上图3。

DERIVED
对于采用物化的方式执行的包含派生表注3的查询，该派生表对应的子查询的select_type值就是DERIVED。或者说包含在FROM子句中的子查询的select_type就是DERIVED。MySQL会将结果存放在一张虚表中，也称为派生表，从而避免使用临时表，如下：

EXPLAIN SELECT a.* FROM 
(SELECT * FROM sc_table WHERE col_2 = 'b000003') a;

备注：在MySQL5.7中没有使用派生表，而是直接优化为SIMPLE类型
id为1的记录代表外层查询，注意它的table列显示的是<derived2>，表示该查询是针对将派生表物化之后的sc_table表进行查询的。

MATERIALIZED
当查询优化器在执行包含子查询的语句，并且该子查询为非相关子查询注5时，将子查询物化注2之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED，如下：

EXPLAIN SELECT *
   FROM sc_table
  WHERE col_2 IN (SELECT col_1 FROM sc_table_1);

UNCACHEABLE SUBQUERY
不常用

UNCACHEABLE UNION
不常用

table

不管查询语句有多复杂，不管涉及到多少个表的查询，最后也会分别对每个表进行单表访问。所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问类型，该条记录的table列代表着访问的表名。

type`重点`

本列标识关联类型或访问类型，即MySQL决定如何查找行和查找数据行的大概范围。
按最优到最差的类型排序system > const > eq_ref > ref > range > index > ALL。
其中除了ALL这个访问方法外，其余的访问方法都能用到索引，除了 index_merge访问方法外，其余的访问方法都最多只能用到一个索引。一般来说，得保证查询达到range级别，最好达到ref级别。

system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，那么对该表的访问类型就是system。system是const访问类型的一个特例，如下：

EXPLAIN SELECT * FROM (SELECT * FROM sc_table WHERE id = 1) a;

子查询返回值只有1条记录，所以外层查询的访问类型是system。

const
当根据主键或者唯一二级索引列与常数进行等值匹配时，单表访问类型就是const，如下：

EXPLAIN SELECT * FROM sc_table WHERE uni_key = 'b000002';

eq_ref
连接查询如果被驱动表是通过主键或者唯一二级索引注4列等值匹配的方式进行访问的(如果该主键或者唯一二级索引注4是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问类型就是eq_ref，如：

EXPLAIN SELECT * FROM sc_table 
WHERE uni_key IN (SELECT id FROM sc_table_1 where sc_table.key_1 = sc_table_1.key_1);

MySQL将sc_table作为驱动表，sc_table_1作为被驱动表，sc_table_1的访问类型是eq_ref，说明MySQL查询优化器将查询转换为通过主键等值比配的连接查询。下面是使用唯一二级索引等值匹配的例子

EXPLAIN SELECT * FROM sc_table,sc_table_1
  WHERE sc_table.uni_key=sc_table_1.uni_key;

ref
当通过普通二级索引列与常量进行等值匹配时来查询某个表，可能会找到多个符合条件的行，那么对该表的访问类型就可能是ref

EXPLAIN SELECT * FROM sc_table WHERE key_1 = "a002";

ref_or_null
当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是 ref_or_null

EXPLAIN SELECT * FROM sc_table WHERE key_1 = "a002" OR key_1 IS NULL;

index_merge索引合并参考
一般情况下一个查询只能使用到一个索引，但单表访问在某些场景下可以使用Intersection交集、Union并集、Sort-Union交集的并集这三种索引合并的方式来执行查询，此时MySQL使用索引合并的方式来执行查询，比如：

-- 并集
EXPLAIN SELECT * FROM sc_table WHERE key_1 = '10' OR key_2 = '20';
-- 交集（大量数据触发）
EXPLAIN SELECT * from sc_table where key_1 = 'tom' AND key_2 = '17'; 
explain select * from sc_table where (c_key_1<10 and c_key_2>50) or key_1=50;
-- 交并集（大量数据触发）
EXPLAIN SELECT * FROM sc_table WHERE (c_key_1<10 AND c_key_2>50) OR key_1=50;

unique_subquery
类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器IN子查询转换为EXISTS子查询，并且子查询与外部查询使用主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery

EXPLAIN SELECT * FROM sc_table 
WHERE uni_key 
IN (SELECT id FROM sc_table_1 where sc_table.key_1 = sc_table_1.key_1) 
OR key_2 = 'a';

index_subquery
与unique_subquery类似，区别是子查询与外部查询使用的是普通索引进行等值匹配，比如：

EXPLAIN SELECT * FROM sc_table WHERE key_1 
IN (SELECT key_1 FROM sc_table_1 where sc_table.key_2 = sc_table_1.key_2)
OR key_2 = 'a';

range区间访问
使用到索引的范围查询in()、between、>、<、>=、<=等操作的访问类型就是range，如：

EXPLAIN SELECT * FROM sc_table WHERE key_1 IN ('a', 'b', 'c');
EXPLAIN SELECT * FROM sc_table WHERE key_3 > 10 AND key_3 < 6666;

注意，范围查询必须是开闭的区间，比如 ><、>= <=。

index
扫描全部的索引记录，主要优点就是避免了排序, 但是开销仍然非常大

EXPLAIN SELECT c_key_1 FROM sc_table WHERE c_key_2 = 'a';

上述查询的搜索列表只有c_key_1列，且搜索条件也只有c_key_2一列，这两列又恰好包含在联合索引索引idx_c_key123中，但是搜索条件c_key_2不能直接使用该索引进行ref或者range方式的访问，只能扫描整个idx_c_key123索引的记录，所以访问类型是index。

ALL
即全表扫描，意味着MySQL需要从头到尾去查找所需要的行，性能最低。

possible_keys

对某个表进行查询时可能用到的索引，如果为空，说明没有可用的索引，如果表中数据不多后面的key就可能是null，表示MySQL认为索引对此查询帮助不大，选择了全表查询

key

表访问实际使用的索引。如果没有使用索引，则为NULL。如果想强制MySQL使用或忽视possible_keys列中的索引，可以在查询中使用 force index和ignore index

key_len

当查询优化器使用某个索引时，该索引记录的最大⻓度，在不损失精确性的情况下，长度越短性能越好。通过这个值可以算出具体使用了索引中的哪些列。

类型长度固定的索引列的实际最大长度就是该固定长度，如CHAR(10)实际占用的最大存储空间长度就是10字节。对于指定字符集可变长度的类型的索引列比如VARCHAR(100)字符集UTF8，该列索引最大长度就是100 × 3 = 300个字节。
如果该索引列允许NULL值，则key_len比不允许NULL时多1个字节，所以尽量设置索引列不允许NULL。
可变长度的索引字段都会有2个字节的空间来存储该变⻓列的实际⻓度。

数值类型tinyint：1字节、smallint：2字节、int：4字节、bigint：8字节
时间类型date：3字节、timestamp：4字节、datetime：8字节
字符串char(n)：n字节、varchar(n)：如果是utf-8字符集，则长度 3n + 2
浮点数类型: float：4字节、double：8字节

DECIMAL 参考
DECIMAL可能的最大取值范围与DOUBLE 一样，但是其有效的取值范围由M和D的值决定decimail(M,D)。
要计算所占用的字节数，需要分为整数与小数两部分，分开计算再相加，每部分又按照9位数字去拆分，计算规则如下：

位数	占用字节数
1-2	1
3-4	2
5-6	3
7-9	4

例如：
DECIMAL(24,8)
整数部分24-8=16位,小数部分8位,小数部分的8位根据规则所占用的字节数为4个,整数部分按每9位进行拆分,16=9+7,9位的部分占4个字节,7位的部分按照规则也占4个字节,所以整数部分占8个字节,加上小数部分的4个字节,总共占12个字节。

备注：索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引ADD INDEX inx_col_2(col_2(20)) USING BTREE。
举例：

EXPLAIN SELECT * FROM sc_table WHERE id > 5;

id是INT类型，并且不允许NULL，所以使用该列的索引时key_len大小是4。

EXPLAIN SELECT * FROM sc_table WHERE key_1 = "a";

key_1列的类型是VARCHAR(128)字符集是utf8mb4，并且不允许NULL所以该列实际最多占用的存储空间是128*4+2=514字节。

EXPLAIN SELECT * FROM sc_table WHERE c_key_1 = "bb" AND c_key_2 = "aa";

key_len为134可以判断查询使用了联合索引idx_c_key123的前两个索引列，索引列c_key_1为INT类型长度是4，索引列c_key_2是VARCHAR(32)类型，字符集是utf8mb4不允许NULL，所以4+32*4+2=134。

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_ subquery其中之一时，ref列展示的就是与索引列作等值匹配所用到的列或常量，如：

EXPLAIN SELECT * FROM sc_table WHERE key_1 = 't';

如上ref列的值是const，说明在使用idx_key_1索引执行查询与key_1列做常数等值匹配，下面的更复杂一点：

EXPLAIN SELECT * FROM sc_table INNER JOIN sc_table_1 ON sc_table.id = sc_table_1.id;

被驱动表sc_table的访问类型是eq_ref，对应的ref列的值是sc_table_1.id，这说明在对被驱动表进行访问时会用到PRIMARY索引，也就是聚簇索引（参考）与一个列进行等值匹配的条件。
与索引列进行等值匹配的对象也可以是一个函数，如下：

EXPLAIN SELECT * FROM sc_table 
INNER JOIN sc_table_1 ON sc_table_1.key_1 = UPPER(sc_table.key_1);

如上对sc_table_1表采用ref访问类型执行查询，在查询计划的ref列里输出的是func，说明与sc_table_1表的key_1列进行等值匹配的对象是一个函数。

rows

如果MySQL查询优化器在执行执行查询时如果使用到了索引进行查询，rows列就代表预计扫描的索引记录行数，如果没使用到索引rows列就代表预计需要扫描的行数，比如：

EXPLAIN SELECT * FROM sc_table WHERE key_1 > 't' and key_1 < 'w';

rows列的值是1，说明查询优化器在对使用idx_key_1索引进行查询的成本进行分析之后，预估到满足条件的记录只有1条。单这个行数只是预估值，可能和实际查询到的行数不一致。

Extra

Extra额外说明，用来描述一些额外信息，我们可以通过这些额外信息来更准确的理解MySQL查询优化器将如何执行给定的查询语句，常见的有：

No tables used
当查询语句的没有FROM子句时提示的额外信息，如：

EXPLAIN SELECT CURRENT_DATE;

Impossible WHERE
查询语句的WHERE子句永远是FALSE的额外信息，如：

EXPLAIN SELECT * FROM sc_table WHERE 1 <> 1;

No matching min/max row
当查询列有MIN或者MAX聚集函数，但是并没有符合WHERE子句中检索条件的记录时的额外信息，如：

EXPLAIN SELECT MIN(key_1) FROM sc_table WHERE key_1 = '123123';

表里没有符合key_1 = '123123'的记录。

Using index
当查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下的额外信息。比如下面的查询只需要用到索引key_1而不需要回表操作：

EXPLAIN SELECT key_1 FROM sc_table WHERE key_1 = 'a';

Using index condition
有些搜索条件中虽然出现了索引列，但查询的列不完全被索引覆盖无法使用到索引，比如：

EXPLAIN SELECT * FROM sc_table WHERE key_2 > 't' AND key_2 LIKE '%fsf';

查询条件不是都能够使用到idx_key_2索引，并且查询的列不完全被索引覆盖。
在以前版本的MySQL中，是按照以下步骤来执行这个查询的：

先根据第一个条件，从二级索引idx_key_2中获取到对应的二级索引记录。
根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的记录再检测记录是否符合第二个条件，将符合条件的记录加入到最后的结果集。

但是虽然第二个条件不能组成范围区间参与range访问类型的执行，但这个条件毕竟只涉及到了key_2列，MySQL后面版本优化了一下：

先根据第一个条件，从二级索引idx_key_2中获取到对应的二级索引记录。
对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足第二个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。
对于满足第二个条件的二级索引记录执行回表操作。

回表操作其实是一个随机IO，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。MySQL把这个改进称为索引条件下推(Index Condition Pushdown)。
如果在查询语句的执行过程中要使用索引条件下推这个特性，在Extra列则会显示Using index condition。

Using where
当要查询的列不能被索引覆盖，并且改查询语句的WHERE子句中有搜索条件时，Extra列中会提示Using where额外信息，比如：

EXPLAIN SELECT * FROM sc_table WHERE key_1 > "a";

Not exists
当使用左（外）连接LEFT JOIN时，如果WHERE子句中包含被驱动表的某个列等于NULL的搜索条件，并且那个列不允许存储NULL，那么Extra列就是这个额外信息。

EXPLAIN SELECT * FROM sc_table t 
LEFT JOIN sc_table_1 t1 ON t.key_1 = t1.key_1 
WHERE t1.id IS NULL;

Using filesort
很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要使用文件排序的方式执行查询，就会在Extra列中显示该额外信息，比如：

EXPLAIN SELECT * FROM sc_table ORDER BY col_2 LIMIT 10;

如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，所以最好想办法将使用文件排序的方式改为使用索引排序。
MySQL中有两种文件排序方式：

单路排序：一次性取出满足条件的行的所有字段，然后在sort buffer中进行排序。
双路排序（回表排序模式）：首先根据相应的条件检索出满足条件行的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段。

MySQL通过比较系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式：

如果max_length_for_sort_data比查询字段的总长度大，那么使用单路排序模式
如果max_length_for_sort_data比查询字段的总长度小，那么使用双路排序模式

比如：

EXPLAIN SELECT * FROM sc_table WHERE key_1 = "aa" ORDER BY key_2;

先看单路排序的详细过程：

从索引idx_key_1找到第一个满足key_1 = ‘aa’条件的主键 id
根据主键id取出整行，取出所有字段的值，存入sort_buffer中
从索引idx_key_1t找到下一个满足key_1 = ‘aa’条件的主键id
重复直到不满足key_1 = ‘aa’
对sort_buffer中的数据按照字段key_2进行排序
返回结果给客户端

再看下双路排序的详细过程：

从索引idx_key_1找到第一个满足key_1 = ‘aa’条件的主键id
根据主键id取出整行，把排序字段key_2和主键id这两个字段放到sort buffer中
从索引idx_key_1取下一个满足 key_part1 = ‘aa’ 记录的主键id
重复直到不满足 key_part1 = ‘aa’
对sort_buffer中的字段key_2和主键id按照字段key_2进行排序
遍历排序好的id和字段key_2，按照id的值回到原表中取出所有字段的值返回给客户端

对比两个排序模式，单路排序会把所有需要查询的字段都放到sort buffer中，而双路排序只会把主键和需要排序的字段放到sort buffer中进行排序，然后再通过主键回到原表查询需要的字段。
如果MySQL排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
如果MySQL排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data的值，让优化器优先选择全字段排序（单路排序），把需要的字段放到sort_buffer中，这样排序后就会直接从内存里返回查询结果了。
所以，MySQL通过max_length_for_sort_data这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

Using temporary
MySQL有时会借助临时表完成某些功能，如去重、排序等，比如执行包含DISTINCT、GROUP BY、UNION等子句的查询中，如果不能使用索引进行查询，MySQL就可能使用建立内部临时表方式执行查询，Extra列就会显示Using temporary。

EXPLAIN SELECT DISTINCT col_1 FROM sc_table;

或者

EXPLAIN SELECT col_1, COUNT(*) AS a FROM sc_table GROUP BY col_1;

上面Extra列不仅包含Using temporary，还包含Using filesort，但是查询语句中并没有ORDER BY子句，原因是MySQL会在包含GROUP BY子句的查询中默认添加ORDER BY子句，上面的语句等价于：

EXPLAIN SELECT col_1, COUNT(*) AS a FROM sc_table GROUP BY col_1 ORDER BY col_1;

如果不想为GROUP BY子句的查询进行排序，需要显示声明ORDER BY NULL，如下：

EXPLAIN SELECT col_1, COUNT(*) AS a FROM sc_table GROUP BY col_1 ORDER BY NULL;

这样可以省去排序的成本。
另外，Extra为Using temporary需要建立临时表，性能成本很高，所以最好能使用索引来替代临时表，比如下面语句：

EXPLAIN SELECT key_1, COUNT(*) AS a FROM sc_table GROUP BY key_1;

Select tables optimized away

当查询列有MIN或者MAX聚集函数来访问索引字段时的额外信息，如：

EXPLAIN SELECT MAX(key_1) FROM sc_table;

Using join buffer (Block Nested Loop)

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

EXPLAIN SELECT * FROM sc_table t INNER JOIN sc_table_1 t1 ON t.col_1 = t1.col_1;

注2：MySQL查询优化器通过将子查询结果物化到一个临时表中，之后任何地方需要该结果集，MySQL会再次引用该临时表，来加快查询执行速度，临时表存储在内存中，如果表太大则会落到硬盘存储，参考
注3：派生表由外部查询FROM子句中的子查询创建，外部查询结束派生表也会被删除，参考
注4：主索引由系统创建，比如主键，二级索引是我们自己创建的索引
注5：非相关子查询是独立于外部查询的值的子查询，只会执行一次。相关子查询依赖于外部查询中的值，会被执行多次，参考

MySQL之EXPLAIN全方位详解

EXPLAIN的应用场景

概述

EXPLAIN属性

列含义

id

select_type查询类型

table

type`重点`

possible_keys

key

key_len

ref

rows

Extra

Select tables optimized away

Using join buffer (Block Nested Loop)

猜你喜欢

热点阅读

MySQL之EXPLAIN全方位详解

EXPLAIN的应用场景

概述

EXPLAIN属性

列含义

id

select_type查询类型

table

type重点

possible_keys

key

key_len

ref

rows

Extra

Select tables optimized away

Using join buffer (Block Nested Loop)

猜你喜欢

热点阅读

type`重点`