1-MySQL - EXPLAIN

目录

about

MySQL5.7.20
初始数据：world.sql

现在，让我们回想一下SQL的执行过程：

  1. 客户端将SQL发送到mysqld。
  2. 经过连接层。
  3. 在SQL层，会经过、语法、语意检查、权限检查后经过解析器预处理后，产生计划(可能产生多个执行计划)，优化器根据解析器得出的多种执行计划，选择一个最优执行计划，然后执行器执行SQL产生执行结果。
  4. 经过存储引擎层一顿操作后通过专用线程将结果返回给客户端。

而本篇就来研究执行计划，通过执行计划可以了解MySQL选择了什么执行计划来执行SQL，并且SQL的执行过程到此结束，即并不会真正的往下交给执行器去执行；最终的目的还是优化MySQL的性能。

我们通过EXPLAIN语句来查看查看MySQL如何执行语句的信息；EXPLAIN语句可以查看SELECT、DELETE、INSERT、REPLACT和UPDATE语句。

而我们这里重点关注查询时的执行计划。

-- 基本用法
EXPLAIN SQL语句;

-- 示例
EXPLAIN SELECT * FROM city WHERE id<3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

返回中的重要参数：

字段 描述 备注
id 该SELECT标识符
select_type 该SELECT类型
table 输出结果的表
partitions 匹配的分区
type 表的连接类型
possible_keys 查询时可能的索引选择 只是有可能选择的索引，但是也能最后选择的索引不在该字段中
key 实际选择的索引 需要重点了解的
key_len 所选KEY的长度
ref 列与索引的比较
rows 表示MySQL认为执行查询必须检查的行数 innodb中是个估算值
filtered 按表条件过滤的行百分比
Extra 执行情况的附加信息 需要重点了解的

这里我们重点掌握KEY和Extra字段。其他的就参考：EXPLAIN Output Format

其次，EXPLAIN为SELECT语句中使用到的每个表都返回一行信息，并且按照MySQL在处理语句时读取它们的顺序列出了输出中的表：

DESC SELECT city.name,city.population,country.code,country.name
FROM city INNER JOIN country
ON city.countrycode = country.code;
+----+-------------+---------+------------+------+---------------------+-------------+---------+--------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys       | key         | key_len | ref                | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------+-------------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | country | NULL       | ALL  | PRIMARY             | NULL        | NULL    | NULL               |  239 |   100.00 | NULL  |
|  1 | SIMPLE      | city    | NULL       | ref  | CountryCode,inx_c_p | CountryCode | 3       | world.country.Code |   18 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------------+-------------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL通过表列表输出选定的列和回溯，直到找到一个表，其中存在更多匹配的行。从该表中读取下一行，然后继续下一个表。

参见：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_system

另外，DESCRIBE和EXPLAIN是同义词，同样可以查看语句的执行计划：

-- 下面三条语句是等价的
EXPLAIN SELECT * FROM city WHERE id<3;
DESCRIBE SELECT * FROM city WHERE id<3;
DESC SELECT * FROM city WHERE id<3;

实际上，DESCRIBE关键字通常用于获取有关表结构的信息，而EXPLAIN用于获取查询执行计划（即，有关MySQL如何执行查询的说明）。

rows

为了更好的理解EXPLAIN，rows参数也是一个很好的参考，以下来自官网对rows的解释：

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

这个字段表示MySQL认为执行查询必须检查的行数。

对于存储引擎是innodb的表，这个数值是个估计值，并不是精确值。

type

type 的官方全称是join type，意思是"连接类型"，这容易让人误会是联表的连接类型，其实不然，这里的join type事实上是数据库引擎查找表的一种方式，我们这里可以像在《高性能MySQL》中作者称呼join type为访问类型更贴切些。

该type列输出介绍如何联接表，接下来列举常见联接类型，性能从最佳到最差排序：

我们挑重点的说！为了便于理解，这里从性能最差到最佳排序一一说明。

all

all便是所谓的全表扫描了，如果出现了all类型，通常意味着你的SQL处于一种最原始的状态，还有很大的优化空间！为什么这么说，因为all是一种非常原始的查找方式，低效且耗时，用all查找相当于你去一个2万人的学校找"张开"，all的方式是把这2万人全找个遍，即便你踩了狗屎找的第一个人就叫"张开"，那你也不能停，因为无法确定这两万人中是否还有"张开"存在，直到把2万人找完为止。所以除了极端情况，我们应该避免这种情况出现。

我们来看常见出现all的情况。

  1. 查询条件字段是非索引字段：
EXPLAIN SELECT * FROM city WHERE district='henan';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. 查询条件中，包含 !=、not in、like。
-- 注意，以下情况适用于辅助索引
EXPLAIN SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4188 |    82.19 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city WHERE countrycode != 'CHN';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4188 |    88.73 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


-- 而聚集索引来说，还是会走索引
EXPLAIN SELECT * FROM city WHERE id != 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2103 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


-- 而针对于like情况，% 放在首位不会走索引，放在后面会走索引
EXPLAIN SELECT * FROM city WHERE countrycode LIKE 'CH%';   -- 走索引
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city WHERE countrycode LIKE '%H%';	-- 不走索引
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

index

index是另一种形式上的all类型，只不过index是全索引扫描(但索引也是建立在全表记录上的)，index根据条件扫描索引然后回表取数据。index和all相比，它们都扫描了全表的数据，而且index要先扫索引再回表取数据，这么一说，还不如all快呢(在同样的工况下)！但是为啥官方说index比all效率高呢？原因(我经过严格的逻辑推理和分析得来的)在于索引是有序的，所以index效率高些，来看排序示例：

EXPLAIN SELECT * FROM city ORDER BY id;		-- id 是主键
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 4188 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city ORDER BY population;    -- 普通字段
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

首先要注意观察rows字段，都是全表扫描。

可以看到根据population排序走的是ALL访问类型，但Extra字段说使用Using filesort;而根据主键id排序的访问类型是index，并且Extra字段是NULL，即没有额外的排序，所以这可能就是官方说index比all性能好的原因。

还有一种情况需要我们注意，那就是Extra字段是Using index，并且type是index：

EXPLAIN SELECT id FROM city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如上这种情况我们称为"索引覆盖"，那什么是"索引覆盖"？举个例子：我们从字典(一张表)的目录(索引)中找到所有的字，而无需查每个字的实际位置，因为我们想要的数据都在索引中，这就是"索引覆盖"，稍微正式点的解释：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

range

range是基于索引的范围扫描，包含>,<,>=,<=,!=,like,in，not in,or,!=，not in的情况会走range；

出现range的条件是：查询条件列是非PRIMARY KEY和UNIUQE KEY的索引列，也就是说条件列使用了索引，但该索引列的值并不是唯一的，这样的话，即使很快的找到了第一条数据，但仍然不能停止的在指定的范围内继续找。

range的优点是不需要扫描全表，因为索引是有序的，即使有重复值，但也被限定在指定的范围内。

-- 首先为 population 字段建立一个普通索引，现在 population 和 countrycode 是普通索引，而 id 是主键
ALTER TABLE city ADD INDEX idx_p(population);

-- 示例
EXPLAIN SELECT * FROM city WHERE population < 100000;	-- 走索引的范围查找
EXPLAIN SELECT * FROM city WHERE countrycode LIKE 'CH%';  -- 因为索引是有序的，也走索引的范围查找

注意，上面前两个例子可以享受到B+树带来的查询优势(查询连续)；而下面的例子是无法享受的(查询不连续)：

EXPLAIN SELECT * FROM city WHERE countrycode IN ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

对此，我们可以做些优化：

EXPLAIN SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
|  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref

ref出现的条件是： 查找条件列使用了索引但不是PRIMARY KEY和UNIQUE KEY。其意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

EXPLAIN SELECT * FROM city WHERE countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

eq_ref

在多表连接时，连接条件(ON)使用了唯一索引(UNIQUE NOT NULL,PRIMARY KEY)时，走eq_ref。

-- 查询世界上人口小于100人的城市名字
EXPLAIN SELECT city.name,city.population,country.code,country.name
FROM city INNER JOIN country
ON city.countrycode = country.code  -- country表的code字段是 pk
WHERE city.population<100;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | city    | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4188 |    33.33 | Using where |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.city.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

这里如果使用的单表来测试eq_ref，经常会出现const，后来想想是对的！UNIQUE NOT NULL和PRIMARY KEY都具有唯一性，而匹配结果是唯一的可不就是const么！所以eq_ref多出现在联表查询中，因为联接条件通常都是具有唯一性或者主键！

除了 system和 const类型，eq_ref是最好的联接类型。

const,system

首先，system是最优的，它的意思是表只有一行(但效果我没演示出来)，是const类型的一种特例，所以就把这两个列一块了。

EXPLAIN SELECT * FROM city WHERE id=3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

const表示：该表最多具有一个匹配行，该行在查询开始时读取。因为又只有一行，所以优化器的其余部分可以将这一行中的列的值视为一个常量。

我们将const看作是最快的，因为只有一行匹配结果。

key_len

执行计划中的key_len字段计算的是使用到的索引的长度，通过ken_len可以帮助我们进一步确定索引的使用情况。
这里只列出我们最常用的int、char、varchar三种数据类型，先来复习一些基础知识：

  • char和varchar是日常使用最多的字符类型。char(N)用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。
  • varchar(N)用于保存可以变长的字符串，长度最大为65535，只存储字符串实际实际需要的长度（它会增加一个额外字节来存储字符串本身的长度），varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节。

char和varchar跟字符编码也有密切的联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。
不同字符编码占用的存储空间不同：

Latinl编码(一个字符1个字节)：

值 char(4) 存储的字节 varchar(4) 存储的字节
'' ' ' 4 '' 1
'ab' 'ab ' 4 'ab' 3
'abcd' 'abcd' 4 'abcd' 5
'abcdefgh' 'abcd' 4 'abcd' 5

GBK编码(一个字符2个字节)：

值 char(4) 存储的字节 varchar(4) 存储的字节
'' ' ' 8 '' 1
'ab' 'ab ' 8 'ab' 5
'abcd' 'abcd' 8 'abcd' 9
'abcdefgh' 'abcd' 8 'abcd' 9

UTF8编码(一个字符3个字节)：

值 char(4) 存储的字节 varchar(4) 存储的字节
'' ' ' 12 '' 1
'ab' 'ab ' 12 'ab' 7
'abcd' 'abcd' 12 'abcd' 13
'abcdefgh' 'abcd' 12 'abcd' 13

接下来，实验开始。
有表结构如下：

create table k1(
	id int not null primary key auto_increment,
	a int,
	b int not null,
	c char(10),
	d char(10) not null,
	e varchar(10),
	f varchar(10) not null
)engine=innodb charset=utf8;

我们定义的k1表，但没有添加数据，这没关系。

首先来看int的索引长度怎么算出来的：

-- 创建索引
alter table k1 add index(a);
alter table k1 add index(b);

-- key_len的长度是5，对于字段a来说，int类型的索引本身占4个字节，且a字段又允许为空，所以再加1个字节的存储标志位，是否允许为空，加一起，正好是5个字节
explain select * from k1 where a=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | a             | a    | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 对于字段b来说，int类型的索引本身占4个字节，但a字段不允许为空，也就不用加1个字节的标志位了，所以ken_len是4
explain select * from k1 where b=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | b             | b    | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 再来看在联合索引中的使用情况
alter table k1 add index mul_idx_a_b(a, b);
explain select * from k1 where a=1 and b=1;  -- 使用的是联合索引，a是5字节，b是4字节，加一起是9个字节
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | mul_idx_a_b,a,b   | mul_idx_a_b| 9       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+

在来看char的索引长度怎么算出来的：

-- 创建索引
alter table k1 add index(c);
alter table k1 add index(d);
alter table k1 add index mul_idx_c_d(c, d);

-- 字段c是char类型，1个字符用3个字节表示，char(10) * 3 = 30字节，且c字段又允许为空，所以再加1个字节的存储标志位，是否允许为空，加一起，正好是31个字节
explain select * from k1 where c='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | c,mul_idx_c_d | c    | 31      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 字段d是char类型，1个字符用3个字节表示，char(10) * 3 = 30字节，但d字段不允许为空，也就不用加1个字节的标志位了，所以ken_len是30
explain select * from k1 where d='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | d             | d    | 30      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 来看联合索引的使用情况，当然，首先要删除两个普通索引，因为MySQL对于下面的查询示例来说，会优先选择普通索引
drop index c on k1;
drop index d on k1;
explain select * from k1 where c='a' and d='a';  -- c + d = 61
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | mul_idx_c_d   | mul_idx_c_d | 61      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+

在来看varchar的索引长度怎么算出来的：

-- 创建索引
alter table k1 add index(e);
alter table k1 add index(f);
alter table k1 add index mul_idx_e_f(e, f);

-- 字段e是varchar类型，varchar类型需要额外的1~2个字节存储字符本身的长度，这里取2。是否允许为空又占1个字节，所以是 10 * 3 + 2 + 1 = 33
explain select * from k1 where e='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | e,mul_idx_e_f | e    | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 字段f是varchar类型，但不允许为空，所以，10 * 3 + 2 = 32
explain select * from k1 where f='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | f             | f    | 32      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 来看联合索引的使用情况，当然，首先要删除两个普通索引，因为MySQL对于下面的查询示例来说，会优先选择普通索引
drop index e on k1;
drop index f on k1;
explain select * from k1 where e='a' and f='a';  -- 字段e的33 + 字段f的32 = 65
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | mul_idx_e_f   | mul_idx_e_f | 65      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+

参考：MySQL key_len 大小的计算

extra

EXPLAIN输出的extra列包含有关MySQL如何解析查询的其他信息，帮助我们进一步了解执行计划的细节信息。

这里我们也是挑些常用的说。

Using where

查询语句中使用WHERE条件过滤数据。出现WHERE情况有点复杂，我们来举几个例子。

  1. 当WHERE条件是索引列时：
EXPLAIN SELECT id FROM city WHERE id<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city WHERE id<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

两个查询条件一致，就是返回的结果字段不一样，第一个返回id字段，这个id字段是个主键，所以在Using where的基础上又Using index，这表示，id字段的值在索引上就查到了，没有进行回表查询；而第二个返回的是所有字段，其它字段不包含索引列，所以要回表查询，也就没有了Using index，所以，第一个性能高些。

  1. 当WHERE条件是非索引列时：
EXPLAIN SELECT * FROM city WHERE district='henan';
EXPLAIN SELECT id FROM city WHERE district='henan';
EXPLAIN SELECT countrycode FROM city WHERE district='henan';
EXPLAIN SELECT district FROM city WHERE district='henan';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上面几个查询语句中的查询条件不变，并且district字段时非索引字段，所以，无论是是返回所有字段、还是只返回主键、普通索引字段、普通非索引字段，都是Using where，并且观察type栏是ALL，所以，可以对district字段做优化，也就是添加个索引，性能会好些：

ALTER TABLE city ADD INDEX idx_d(district);
EXPLAIN SELECT district FROM city WHERE district='henan';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_d         | idx_d | 20      | const |   18 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

性能立马就上来了！

通过几个示例，我们也能看到，当Extra栏出现Using where时，可以参考type栏做适当优化：

  1. 当type栏是ALL时，说明SQL语句有很大的优化空间。
  2. 当Extra栏是Using index时，参考type栏的情况，可以看看是不是建个索引。
  3. 如果出现回表查询情况，尽量避免使用select *，而是选择返回指定字段，然后为指定字段尝试是否可以建个联合索引。

Using index

当Extra栏出现Using index表示使用覆盖索引进行数据返回，没有回表查询。

EXPLAIN SELECT countrycode FROM city WHERE countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city WHERE countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

上面第二个示例，就出现了回表查询的情况，因为有的字段不在索引中。

Using index condition

当Extra栏出现Using index condition时，表示先使用索引条件过滤数据，然后根据其他子句进行回表查询操作：

ALTER TABLE city ADD INDEX inx_p(Population);	-- 首先为population字段建立一个索引
EXPLAIN SELECT name,population FROM city WHERE population<100;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | inx_p         | inx_p | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

上例的population字段是索引字段，先走索引把符合条件的结果过滤出来，然后select语句还需要查询name字段，就又回表查询了~

Using temporary

当Extra栏出现Using temporary时，表示MySQL需要创建临时表来保存临时结果；如果查询包含不同列的GROUP BY和ORDER BY子句时，通常会需要临时表：

EXPLAIN SELECT Population FROM city GROUP BY Population;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

Using filesort

刚才出现了Using filesort，这个是啥意思呢？先来看个示例：

EXPLAIN SELECT * FROM city ORDER BY population;  -- population 为普通索引字段
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

EXPLAIN SELECT * FROM city WHERE countrycode='CHN' ORDER BY population; -- countrycode 和 population 都为索引列
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

由上例的查询语句可以看到，Extra栏是Using index condition; Using filesort，走索引我们可以理解，毕竟countrycode字段是索引字段，而Using filesort则要好好来聊聊了。

这个filesort并不是说通过磁盘文件进行排序，而是告诉我们在查询时进行了一个排序操作，即在查询优化器(MySQL Query Optimizer)所给出的执行计划中被称为文件排序(filesort)。

文件排序是通过相应的排序算法，将取得的数据在内存中进行排序。MySQL需要将数据在内存中进行排序，所使用的内存区域(通过系统变量sort_buffer_size设置的排序区)，这个排序区是每个Thread独享的，所以说可能在同一时刻，MySQL中可能会存在多个sort buffer内存区域。

我们来看下filesort的排序过程，加深理解：

  1. 根据表的索引或者全表扫描，读取所有满足条件的记录。
  2. 对于每一行，存储一对值(排序列，行行为记录指针)到缓冲区；排序的索引列的值，即ORDER BY用到的列值和指向该行数据的行指针；缓冲区的大小为sort_buffer_size大小。
  3. 当缓冲区满后，运行一个快速排序(quick sort)来将缓冲区中的数据排序，并将排序完的数据存储到一个临时文件(存储块)中，并保存一个存储块的指针；当然，如果缓冲区不满，也就不用搞什么临时文件了。
  4. 重复以上步骤，直到将所有行读完，并建立相应的有序的临时文件。
  5. 使用归并排序(貌似是，如果错误可以指正)对存储块进行排序，只通过两个临时文件的指针来不断的交换数据，最终达到两个文件都是有序的。
  6. 重复步骤5过程，直到所有的数据都排序完毕。
  7. 采用顺序读取的方式，将每行数据读入内存，并取出数据传到客户端，注意，这里的读数据并不是一行一行的读，读的缓存大小由read_rnd_buffer_size来决定。

以上就是filesort的过程，采取的方法为快排加归并；但我们应该注意到，一行数据会被读两次，第一次是WHERE条件过滤时；第二个是排序完毕后还要用行指针去读一次(发给客户端)。所以，这里也有一个优化方案，那就是直接读入数据，在sort_buffer中排序完事后，直接发送到客户端了，大致过程如下：

  1. 读取满足条件的记录。
  2. 对于每一行，记录排序的KEY和数据行指针，并且把要查询的列也读出来。
  3. 根据索引KEY进行排序。
  4. 读取排序完成的数据，并且直接根据数据位置读取数据返回客户端。

但问题也来了，在查询的在字段很多，数据量很大时，排序起来就很占用空间，因此max_length_for_sort_data变量就决定了是否能使用这个排序算法。

我们可以总结一下上面的两种方案：

  1. 双路排序：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer中进行排序，排序后在把查询字段依照行指针取出，共执行两次磁盘I/O。
    • 双路排序由于有大量的随机I/O，效率不高，但是节约内存；排序使用快排(quick sort)算法，但是如果内存不够则会使用块排序(block sort)，将排序结果写入磁盘，然后再将结果合并。
  2. 单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序，执行一次磁盘你I/O；当然，遇到内存不足时仍然会使用临时文件。

MySQL主要通过比较我们所设定的系统参数max_length_for_sort_data的大小和Query语句所取出的字段类型大小总和来判断需要使用哪种排序算法；如果max_length_for_sort_data更大，则使用优化后的单路排序，反之使用双路排序。

另外，在连表查询中，如果ORDER BY的子句只引用了第一个表，那么MySQL会先对第一个表进行排序，然后再连表，此时执行计划中的Extra就是Using filesort，否则MySQL会先把结果保存到临时表中，然后再对临时表进行排序，此时执行计划中的Extra就是Using filesort

-- 查询世界上人口小于100人的城市名字
DESC SELECT city.name,city.population,country.code,country.name
FROM city INNER JOIN country
ON city.countrycode = country.code
WHERE city.population<100  -- 先把条件过滤出来，再连表操作
ORDER BY city.population;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra                       |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | city    | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4188 |    33.33 | Using where; Using filesort |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.city.CountryCode |    1 |   100.00 | NULL                        |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)

-- 查询世界上人口小于100人的城市名字
DESC SELECT city.name,city.population,country.code,country.name
FROM city INNER JOIN country
ON city.countrycode = country.code
-- where city.population<100	-- 没有where条件，直接连表然后生成临时表，完事再排序
ORDER BY city.population;
+----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref                | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | country | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  239 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | city    | NULL       | ref  | CountryCode   | CountryCode | 3       | world.country.Code |   18 |   100.00 | NULL                            |
+----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

一般的，在如下的几种情况中，也会使用文件排序：

  • WHERE语句于ORDER BY语句使用了不同的索引。
  • 检查的行数据过多，且没有使用覆盖索引。
  • 对索引列使用了ASC和 DESC。
  • WHERE语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式。
  • WHERE语句与ORDER BY语句组合满足最左前缀，但WHERE语句中查找的是范围。

所以，想要加快ORDER BY的排序速度，可以：

  • 增加sort_buffer_size的大小，如果大量的查询较小的话，这个很好，在缓冲区就直接搞定了。
  • 增加read_rnd_buffer_size的大小，可以一次性的将更多的数据读入到内存中去。
  • 列长度尽量小些。

再来个示例：

-- 先看下当前表的索引情况，心里有底
SHOW INDEX FROM city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

现在做个查询操作：

DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY Population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到了使用了索引也使用了文件排序，遇到这种情况，我们可以通过以下思路来解决：

  1. 观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件，有没有索引。
  2. 如果没有，可以根据子句的执行顺序，去创建联合索引。

按照上述思路，我们可以先为ORDER BY语句的population字段和countrycode添加一个联合索引：

-- 添加一个联合索引
ALTER TABLE city ADD INDEX inx_c_p(CountryCode,Population);
-- 可以看到走了联合索引，然后没有了文件排序
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY Population;
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys       | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode,inx_c_p | inx_c_p | 3       | const |  363 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

小结

执行计划算是MySQL优化部分的内容了，想要弄懂首先要对SQL语句非常熟练，并且也要非常熟练索引相关的知识，还需要熟悉存储引擎，因为有些情况是基于指定存储引擎下的结果；除此之外，想要弄懂Extra栏，需要同时结合表的索引情况、查询语句、优化器(MySQL会优化我们的SQL)、以及EXPLAIN的type栏和rows栏等综合分析出现的各种情况。

这里单独对Extra的几种情况做下总结：

  • Using index：表示使用索引，如果只有Using index，表示使用覆盖索引返回数据而没有回表查询的操作。
  • Using index;Using where：说明在使用索引的基础上还需要回表查询记录，可以考虑只返回指定的字段和建立联合索引来尝试避免回表查询情况。
  • Using index condition：说明会先根据走索引过滤结果，然后再根据其他子句的情况做回表查询操作。
  • Using where：表示在查询中很可能出现了回表查询的情况，可以观察是否加个索引来优化。
  • Using temporary：表示MySQL需要创建临时表来保存临时结果；通常出现在包含不同列的GROUP BY和ORDER BY子句时，另外也时常跟Using filesort一起出现。
  • Using filesort：表示在使用索引之外，还需要额外的排序操作，也可以根据具体情况添加索引来解决。

咳咳，有没有发现一个问题，如果SQL性能不行，第一想法就是加个索引试试........这里只有一句话，索引虽好，但需要具体情况具体分析。

that's all,see also:

mysql之filesort原理 | MySQL执行计划extra解析 | mysql中的文件排序(filesort) | MySQL filesort的理解 | mysql中explain的type的解释 | mysql覆盖索引与回表 | mysql explain extra理解详解

posted @ 2020-09-10 10:26  听雨危楼  阅读(404)  评论(0)  编辑  收藏  举报