执行计划

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 语句，并分析查询语句的性能瓶颈。

一、环境准备¶

1. 创建表¶

# 创建角色表
CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建用户表
CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建多对多关联表
CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 插入数据¶

INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('1','李逍遥','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','李逍遥');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('2','重楼','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','重楼');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('3','徐长卿','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','徐长卿');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('4','紫萱','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','紫萱');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('5','龙葵','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','龙葵');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('6','许茂山','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','许茂山');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');

INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10');

二、Explain 结果列¶

1. `id`¶

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id情况有三种

1.1 `id`相同¶

执行顺序从上到下

mysql [test]>EXPLAIN SELECT * FROM t_role r, t_user u, user_role ur WHERE r.id = ur.role_id AND u.id = ur.user_id;
+----+-------------+-------+------------+--------+-----------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys               | key     | key_len | ref             | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+-----------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | r     | NULL       | ALL    | PRIMARY                     | NULL    | NULL    | NULL            |    5 |   100.00 | NULL                                               |
|  1 | SIMPLE      | ur    | NULL       | ALL    | fk_ur_user_id,fk_ur_role_id | NULL    | NULL    | NULL            |    6 |    20.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY                     | PRIMARY | 98      | test.ur.user_id |    1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+-----------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

1.2 `id`不同¶

id值越大，优先级越高，越先被执行。

mysql [test]>EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = '龙葵'));
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t_role    | NULL       | const | PRIMARY              | PRIMARY              | 98      | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user_role | NULL       | ref   | fk_ur_user_id        | fk_ur_user_id        | 99      | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | t_user    | NULL       | const | unique_user_username | unique_user_username | 137     | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

1.3 `id`有相同也有不同¶

id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

# 注意：在Mysql5.7中， 会对衍生表进行合并优化。 需要临时关闭该功能（默认是打开的），
mysql [test]>set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql [test]>EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id;
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key           | key_len | ref       | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL          | NULL    | NULL      |    2 |   100.00 | Using where |
|  1 | PRIMARY     | r          | NULL       | eq_ref | PRIMARY       | PRIMARY       | 98      | a.role_id |    1 |   100.00 | NULL        |
|  2 | DERIVED     | ur         | NULL       | ref    | fk_ur_user_id | fk_ur_user_id | 99      | const     |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

2. `select_type`¶

表示SELECT的类型，常见的取值，如下表所示：

select_type	含义
`SIMPLE`	简单的`SELECT`查询，查询中不包含子查询或者`UNION`
`PRIMARY`	查询中若包含任何复杂的子查询，最外层查询标记为`PRIMARY`
`SUBQUERY`	在`SELECT`或 `WHERE`列表中包含了子查询
`DERIVED`	在`FROM`列表中包含的子查询，被标记为 DERIVED（衍生）。MYSQL会递归执行这些子查询，把结果放在临时表中
`UNION`	若第二个`SELECT`出现在`UNION`之后，则标记为`UNION`；若`UNION`包含在`FROM`子句的子查询中，外层`SELECT`将被标记为： `DERIVED`
`UNION RESULT`	从`UNION`的匿名临时表获取结果的`SELECT`

3. `table`¶

对应行查询的表。

当 from子句中有子查询时，table 列为是 <derivenN> 的格式，表示这一行的执行的是 id = N 行的查询。
当有 union 时，table 的数据为 <union M,N> 的格式，M 和 N 表示参与 union 的 select 行id。

4. `partitions`¶

查询将与记录相匹配的分区。对于非分区表，该值为 NULL。

5. `type`¶

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
`NULL`	MySQL不访问任何表、索引，直接返回结果
`system`	表只有一行记录(等于系统表)，这是`const`类型的特例，一般不会出现
`const`	表示通过索引一次就找到了，`const`用于比较`primary key`或者 `unique` 索引。因为只匹配一行数据，所以很快。如将主键置于`where`列表中，MySQL 就能将该查询转换为一个常量。
`eq_ref`	当主键或唯一键索引的相关列并联接使用时（联表查询），最多匹配一条符合条件的记录。这是除了 `const`之外的最好的联接类型，简单的 `select` 查询不会出现 `req_ref`，更多出现在联表查询。
`ref`	本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做`ref`是因为索引要跟某个参考值相比较，这个参考值是一个常数或者是来自多表查询前一个表里的结果值。
`range`	范围扫描，通常出现在 `between`、`>`、`<`等操作中，使用一个索引来检索给定范围的行。
`index`	`index` 与 `ALL`的区别为: `index`类型只是遍历了索引树，通常比`ALL`快， `ALL`是遍历数据文件。
`ALL`	全表扫描，扫描主键（聚簇、聚集）索引树的所有叶子节点，通常这种情况下要根据业务场景来增加其他索引进行优化。（例外：查询中使用了LIMIT、或者在Extra列中显示“Using distinct/not exists”）。

结果值从最好到最坏依次是：

system > const > eq_ref > ref > range > index > ALL

一般来说，我们需要保证查询至少达到range级别，最好达到ref 。

其他可能出现字段：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

6.`possible_keys`¶

possible_keys 主要显示查询可能用到哪些索引来查找，只是可能会使用，并不代表一定会使用。

常见值说明：

NULL: 没有相关索引，如果是 NULL 的话，可以考虑在 where 子句中创建一个适当的索引来提高查询性能，然后继续用 explain 查看其效果；也有可能出现 possible_keys 为 NULL，但是 key 有值，实际走了索引。有列值：如果显示表中的某列，则表示可能会走这一列对应列值的索引；如果 possible_keys 有值，但是 key 显示 NULL，这种情况一般存在于表中数据量不大的情况，因为 MySQL 语句优化器认为索引对此查询的帮助不大，从而选择了全表查询。

7. `key`¶

key 表示 MySQL 实际采用哪个索引来优化对该表的查询。

如果没有使用索引，则该列为 NULL。

查询中若使用了覆盖做引，则该索引仅出现在key列表中。

8. `key_len`¶

显示了 MySQL 索引所使用的字节数，通过这个数值可以计算具体使用了索引中的哪些列（主要用于联合索引的优化）。

如果字段允许设置为 NULL，则需要 1 字节来记录是否为 NULL； Not NULL 的列则不需要。

9. `ref`¶

显示了之前的表在key列记录的索引中查找值所用的列或常量

10. `row`¶

显示预计查询的结果数，并不是真正的结果集中的记录（行）数，仅供参考。

11`filtered`¶

查询的结果行数（记录）占总读取行数（记录）的百分比，最大为 100 （也就是 100%），意味着没有对行进行筛选。

12.`extra`¶

其他的额外的执行计划信息，在该列展示。

Using index：使用覆盖索引，在 type 相同的情况下， Extra 的值为 Using index 要比为 NULL 性能高。表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using where：使用 where 关键字来查询，并且对应的列没有设置索引，对应的 key 为 NULL。

Using index condition：非覆盖索引查询并进行了回表，并且辅助索引使用了条件查询语句（where 或其他）。

Using temporary：MySQL 需要创建创建一个临时表来处理查询，出现这种情况一般要添加索引进行优化处理。常见于排序order by和分组查询group by。

Using filesort：使用外部排序而不是索引排序，当数据较小的时候采用的是内存排序，当数据量较大的时候会频繁的访问磁盘，并将排序后的数据写入磁盘。

Select tables optimized away：使用聚合函数（例如 max、min等）来访问存在索引的字段时，只访问索引树中已排好序的叶子，节点性能很高。

八、执行计划¶

1. 获取执行计划¶

mysql> DESC SELECT name,population FROM city WHERE name='ChongQing';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2. 关键字段¶

2.1 table查询的表¶

2.2 type 查询的类型:全表，索引¶

type字段可能出现的值

2.2.1 ALL 全表扫描，不走索引¶

例1：查询条件列没有索引

SELECT name,population FROM city WHERE name='ChongQing';

例2：查询条件出现以下语句（辅助索引）

DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
# 对于聚集索引，使用以上语句依然会走索引
DESC 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 | 2032 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.2.2 INDEX 全索引¶

# 1. 查询需要获取整个索引树中的值时
mysql> DESC SELECT countrycode FROM city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 12      | NULL | 4046 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 2.联合索引中，任何一个非最左列作为查询条件时
SELECT * FROM t1 WHERE b

2.2.3 RANGE 索引范围扫描¶

辅助索引：>、 >=、 <、 <= 、LIKE、 IN、 OR
主键：<>、 NOT IN

例：

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

注意：

2.2.4 ref 非唯一性索引，等值查询¶

mysql> ALTER TABLE city ADD INDEX  idx_c_p(countrycode, population);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC 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,idx_c_p | CountryCode | 12      | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.2.5 eq_ref 在多表连接时，连接条件使用了唯一索引¶

mysql> DESC SELECT city.name, country.name, country.SurfaceArea
    -> FROM city JOIN country
    -> ON city.countrycode=country.code
    -> 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,idx_c_p | NULL    | NULL    | NULL                   | 4046 |    33.33 | Using where |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY             | PRIMARY | 12      | world.city.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------------+---------+---------+------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

2.2.6 system,const 唯一索引的等值查询¶

mysql> DESC SELECT * FROM city WHERE id=5;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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.01 sec)

2.3 possible_keys 可能会用到的索引¶

2.4 key 使用到的索引，判断索引是否合理¶

2.5 extra 额外信息¶

2.5.1 filesort 文件排序¶

说明在SQL语句中存在排序

mysql> 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 | 12      | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE city ADD INDEX  idx_c_p(countrycode, population);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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,idx_c_p | idx_c_p | 12      | const |  363 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

当看到执行计划extra位置出现filesort，说明有文件排序出现

观察需要排序（ORDER BY，GROUP BY，DISTINCT）的条件有没有索引

根据子句的指向顺序去创建联合索引