MySQL 执行计划详解

MySQL 执行计划详解

转自：https://www.cnblogs.com/yinjw/p/11864477.html

我们在查询前能否预先估计查询究竟要涉及多少行，使用那些索引、运行时间等，答案是可以的，MySQL提供相应的功能和语法来实现该功能。Server会比较所有可能方法所耗费的资源. 最终SQL语句被物理性执行的方法被称做执行计划（数据库服务器在执行sql语句之前会制定几套执行计划！看那个机会消耗的系统资源少，就是用那套计划！）

我们经常使用 MySQL 的执行计划来查看 SQL 语句的执行效率，接下来分析执行计划的各个显示内容。

EXPLAIN SELECT * FROM users 
WHERE id IN (SELECT userID FROMuser_address WHERE address = "湖南长沙麓谷") ;

执行计划的 id

select 查询的序列号，标识执行的顺序

• id 相同，执行顺序由上至下
• id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

执行计划的 select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等。

• SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
• PRIMARY：查询中包含子部分，最外层查询则被标记为 primary
• SUBQUERY/MATERIALIZED：SUBQUERY 表示在 select 或 where 列表中包含了子查询，MATERIALIZED：表示 where 后面 in 条件的子查询
• UNION：表示 union 中的第二个或后面的 select 语句
• UNION RESULT：union 的结果

对于 UNION 和 UNION RESULT 可以通过下面的例子展现：

EXPLAIN
SELECT * FROM users WHERE id IN(1, 2)
UNION
SELECT * FROM users WHERE id IN(3, 4);

执行计划的 table

查询涉及到的表。

• 直接显示表名或者表的别名
• <unionM,N> 由 ID 为 M，N 查询 union 产生的结果
• <subqueryN> 由 ID 为 N 查询产生的结果

执行计划的 type 

访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：NULL>system > const > eq_ref > ref > range > index > ALL。

NULL :   这种访问方式意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表。
system：系统表，少量数据，往往不需要进行磁盘IO
const： 常量连接, 当mysql能对查询的某部分进行优化并将其转换成一个常量时，他就会使用这些访问类型，举例来说，如果你通过将某一行的主键放入where子句里的方式来选取此行的主键，mysql就能把这个查询转换为一个常量，然后就可以高效地将表从联接执行中移除。
eq_ref：主键索引（primary key）或者非空唯一索引（unique not null）等值扫描,使用这种索引查找，mysql知道最多只返回一条符合条件的记录，这种访问方法可以在mysql使用主键或者唯一性索引查找时看到，它会将它们与某个参考值做比较。mysql对于这类访问类型的优化做得非常好，因为它知道无需估计匹配行的范围或在找到匹配行后再继续查找。
ref：非主键非唯一索引等值扫描, 这是一种索引访问（有时也叫做索引查找），它返回所有匹配某个单个值的行，然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体，此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。
ref_or_null是ref之上的一个变体，它意味着mysql必须在初次查找的结果里进行第二次查找以找出null条目。
range：范围扫描就是一个有限制的索引扫描，它开始与索引里的某一点，返回匹配这个值域的行，这比全索引扫描要好一点，因为它用不着遍历全部索引，显而易见的扫描是  带有between或在where子句里带有>的查询。
当mysql使用索引去查找一系列值时，例如in()和or列表，也会显示为范围扫描，然而，这两者其实是相当不同的访问类型，在性能上有重要的差异。 此类扫描的开销                跟索引类型的相当。
index：这个跟全表扫描一样，只是mysql扫描表时按索引次序而不是行，它的主要优点是避免了排序；最大缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按    随机次序访问行，开销将非常大。
如果在extra列中看到“Using index”，说明mysql正在使用索引覆盖，它只扫描索引的数据，而不是按索引次序的每一行，它比按索引次序全表扫描的开销要少很多。
ALL：这就是所谓的全表扫描（full table scan），意味着mysql必须扫描整张表，从头到尾，去找到需要的行。（有个例外，例如在查询里使用了limit，或者在extra列中显示“Using distinct/not exists”。

 

下面通过举例说明。

system

explain select * from mysql.time_zone;

上例中，从系统库 MySQL 的系统表 time_zone 里查询数据，访问类型为 system，这些数据已经加载到内存里，不需要进行磁盘 IO，这类扫描是速度最快的。

explain select * from (select * from user where id=1) tmp;

再举一个例子，内层嵌套（const）返回了一个临时表，外层嵌套从临时表查询，其扫描类型也是 system，也不需要走磁盘 IO，速度超快。

const 

数据准备：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

explain select * from user where id=1;

const 扫描的条件为： 

1. 命中主键（primary key）或者唯一（unique）索引
2. 被连接的部分是一个常量（const）值 

如上例，id 是 主键索引，连接部分是常量1。

eq_ref

数据准备：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;

eq_ref 扫描的条件为，对于前表的每一行（row），后表只有一行被扫描。 

再细化一点：  

1. join 查询
2. 命中主键（primary key）或者非空唯一（unique not null）索引
3. 等值连接；

如上例，id 是主键，该 join 查询为 eq_ref 扫描。

ref

数据准备：

CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;

如果把上例 eq_ref 案例中的主键索引，改为普通非唯一（non unique）索引。就由 eq_ref 降级为了 ref，此时对于前表的每一行（row），后表可能有多于一行的数据被扫描。

select * from user where id=1;

当 id 改为普通非唯一索引后，常量的连接查询，也由 const 降级为了 ref，因为也可能有多于一行的数据被扫描。

ref 扫描，可能出现在 join 里，也可能出现在单表普通索引里，每一次匹配可能有多行数据返回，虽然它比 eq_ref 要慢，但它仍然是一个很快的 join 类型。

range

数据准备：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

explain select * from user where id between 1 and 4;
explain select * from user where id in(1,2,3);
explain select * from user where id > 3;

range 扫描就比较好理解了，它是索引上的范围查询，它会在索引上扫码特定范围内的值。

像上例中的 between，in，> 都是典型的范围（range）查询。

index

explain count (*) from user;

如上例，id 是主键，该 count 查询需要通过扫描索引上的全部数据来计数，它仅比全表扫描快一点。

ALL 

数据准备：

CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

explain select * from user,user_ex where user.id=user_ex.id;

如果 id 上不建索引，对于前表的每一行（row），后表都要被全表扫描。

文章中，这个相同的 join 语句出现了三次：

1. 扫描类型为 eq_ref，此时 id 为主键
2. 扫描类型为 ref，此时 id 为非唯一普通索引
3. 扫描类型为 ALL，全表扫描，此时id上无索引

有此可见，建立正确的索引，对数据库性能的提升是多么重要。

总结 

1. explain 结果中的 type 字段，表示（广义）连接类型，它描述了找到所需数据使用的扫描方式；
2. 常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL，其扫描速度由快到慢；
3. 各类扫描类型的要点是：

1. system 最快：不进行磁盘 IO
2. const：PK 或者 unique 上的等值查询
3. eq_ref：PK 或者 unique 上的 join 查询，等值匹配，对于前表的每一行，后表只有一行命中
4. ref：非唯一索引，等值匹配，可能有多行命中
5. range：索引上的范围扫描，例如：between、in、>
6. index：索引上的全集扫描，例如：InnoDB 的 count
7. ALL 最慢：全表扫描

4. 建立正确的索引，非常重要；
5. 使用 explain 了解并优化执行计划，非常重要；

执行计划 possible_keys

查询过程中有可能用到的索引。这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。

执行计划 key

实际使用的索引，如果为 NULL ，则没有使用索引。这一列显示了mysql决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys列中，那么mysql选用它是处于另外的原因——例如，它可能选择了一个覆盖索引，哪怕没有where子句。

换句话说，possible_keys揭示了哪一个索引能有助于高效地进行查找，而key显示的是优化采用哪一个索引可以最小化查询成本。下面是一个例子：

执行计划key_len

该列显示了mysql在索引里使用的字节数，如果mysql正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列，要记住，mysql 5.5及之前的版本只能使用索引的最左前缀，举例来说，film_actor的主键是两个smallint列，并且每个smallint列是两字节，那么索引中的每项是4字节，以下就是一个查询的示例

执行计划 rows

根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。这一列是mysql估计为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的 循环数目，也就是说它不是mysql认为它最终要从表里读取出来的行数，而是mysql为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数。（这个标准包括sql里给定的条件，以及来自联接次序上前一个表的当前列。）

根据表的统计信息和索引的选用情况，这个估算可能很不精确，在mysql5.0及更早的版本里，它也反映不出limit子句，举例来说，下面这个查询不会真的检查1057行。

执行计划 filtered 

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

执行计划 Extra 

十分重要的额外信息。

• Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。
• Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。
• Using index：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。不要把覆盖索引和index访问类型弄混了。
• Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。
• Using where：表示 SQL 操作使用了 where 过滤条件。
• Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。
• Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。

下面通过举例说明。

数据准备：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into user values(1, 'shenjian','no');
insert into user values(2, 'zhangsan','no');
insert into user values(3, 'lisi', 'yes');
insert into user values(4, 'lisi', 'no');

数据说明：

用户表：id 主键索引，name 普通索引（非唯一），sex 无索引。

四行记录：其中 name 普通索引存在重复记录 lisi。

Using filesort

explain select * from user order by sex;

 

Extra 为 Using filesort 说明，得到所需结果集，需要对所有记录进行文件排序。

这类 SQL 语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了 order by，就会触发 filesort，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序。

Using temporary

explain select * from user group by name order by sex;

Extra 为 Using temporary 说明，需要建立临时表（temporary table）来暂存中间结果。

这类 SQL 语句性能较低，往往也需要进行优化。

典型的 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

临时表存在两种引擎，一种是 Memory 引擎，一种是 MyISAM 引擎，如果返回的数据在 16M 以内（默认），且没有大字段的情况下，使用 Memory 引擎，否则使用 MyISAM 引擎。 

Using index

EXPLAIN SELECT id FROM USER;

Extra 为 Using index 说明，SQL 所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

这类 SQL 语句往往性能较好。

Using index condition

explain select id, name, sex from user where name='shenjian';

Extra 为 Using index condition 说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

这类 SQL 语句性能也较高，但不如 Using index。

Using where

explain select * from user where sex='no';

Extra 为 Using where 说明，查询的结果集使用了 where 过滤条件，比如上面的 SQL 使用了 sex = 'no' 的过滤条件

Select tables optimized away

EXPLAIN SELECT MAX(id) FROM USER;

 

比如上面的语句查询 id 的最大值，因为 id 是主键索引，根据 B+Tree 的结构，天然就是有序存放的，所以不需要等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

Using join buffer (Block Nested Loop)

explain select * from user where id in (select id from user where sex='no');

Extra 为 Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算。内层和外层的 type 均为 ALL，rows 均为4，需要循环进行4*4次计算。

这类 SQL 语句性能往往也较低，需要进行优化。

典型的两个关联表 join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

参考 

《同一个SQL语句，为啥性能差异咋就这么大呢？（1分钟系列）》

《如何利用工具，迅猛定位低效SQL？ | 1分钟系列》