（优化篇）MySQL 之执行计划分析

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 3.9k | 阅读时长 ≈ 6 分钟

  在 MySQL 中，通过 explain或desc命令可以获取 MySQL 执行 SELECT 语句时的相关信息。

定位低效 SQL

  在使用explain命令前，一般会先通过show processlist命令查询低效率的 SQL 语句，下面为该命令的输出结果:

1
2
3
4
5
6
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |   23 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+

  各个列含义见下表：

列 含义
Id 用户登录 MySQL 时，系统分配的connection_id，可以通过函数connection_id()查看
User 显示当前用户。若不是 root 用户，则只显示当前用户权限范围的 SQL 语句
Host 显示该语句来自哪个 IP 的哪个端口，可以用来跟踪出现问题语句的用户
db 显示当前进程所连接的数据库
Command 显示当前连接执行的命令，一般取值为休眠（Sleep），查询（Query），连接（Connect）等
Time 显示这个状态持续的时间，单位为秒
State 显示使用当前连接的 SQL 语句的状态，其描述的是语句执行中的某一个状态。一个 SQL 语句，以查询为例，可能需要经过 copying to tmp table、sorting result、sending data等状态才可以完成
Info 显示这个 SQL 语句，是判断问题语句的一个重要依据

  说明一下，输出结果中的event_scheduler为事件调度器，MySQL 8 以上是默认开启的，可以通过如下命令查看到其状态：

1
SHOW VARIABLES LIKE 'event_scheduler';

1
2
3
4
5
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

  不过这并不是我们关注的重点，我们关注的重点是通过该命令可以定位查询时间比较长的 SQL 语句。

注意哦：查询时间短的 SQL 语句该命令是不显示的。

  举个栗子：当我们查询一个 具有 30 万条数据的表时，是需要耗费一定时间的，这就属于慢查询了。
  示例如下：

1
2
3
4
5
6
7
mysql> select count(*) from tb_user;
+----------+
| COUNT(*) |
+----------+
|   300000 |
+----------+
1 row in set (3.07 sec)

  现在通过show processlist命令就可以查询到该条慢查询 SQL 了：

1
2
3
4
5
6
+----+-----------------+-----------+------+---------+------+------------------------+-------------------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info                          |
+----+-----------------+-----------+------+---------+------+------------------------+-------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |   23 | Waiting on empty queue | NULL                          |
|  8 | root            | localhost | demo | Query   |    2 | Sending data           | select count(*) from tb_user; |
+----+-----------------+-----------+------+---------+------+------------------------+-------------------------------+

  这样就可以决定是否去优化类似的 SQL 语句了。

分析执行计划

  通过show processlist命令查询到定效率的 SQL 语句后，可以通过 explain或desc命令来获取 MySQL 执行 SELECT 语句时的相关信息。

EXPLAIN

  下面为一个例子：

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

  通过EXPLAIN输出的各个列含义见下表（后续会详细分析）：

列 说明
id 表示表的执行顺序，id 越大，越先执行，id 相同，由上至下执行。
select_type SELECT 的类型，取值包括 SIMPLE、PRIMARY、UNION、SUBQUERY 等
table 输出结果集所引用的表
partitions 匹配的分区信息
type 连接的类型，取值不同性能也不同
possible_keys 查询时可能使用的索引
key 实际使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 执行情况的说明和描述

前期准备

  首先我们先准备 3 张数据表（用户表、权限表、中间表）用作测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_role
-- ----------------------------
DROP TABLE IF EXISTS `tb_role`;
CREATE TABLE `tb_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(30) NOT NULL,
  `role_desc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_role
-- ----------------------------
BEGIN;
INSERT INTO `tb_role` VALUES (1, 'bussinessman', '商家');
INSERT INTO `tb_role` VALUES (2, '普通用户', '普通用户');
INSERT INTO `tb_role` VALUES (3, 'vip用户', 'vip用户');
COMMIT;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `salt` varchar(50) NOT NULL,
  `phone` varchar(12) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
BEGIN;
INSERT INTO `tb_user` VALUES (1, 'boss', '8b8a3c8c36d3bbac7aaaca51eab1f9e7', 'fa0a1a7a-07ce-4120-8b69-400a2592efc2', '110', '1@163.com');
INSERT INTO `tb_user` VALUES (2, 'zhangsan', 'e791e9e740d84a6c6bdeb3fad56fbc8e', '2b99bb5e-33ef-4042-a12b-f4f13169526f', '124402314320', '22@126.com');
INSERT INTO `tb_user` VALUES (3, 'Jack', '6d35ce2799d15803850cf15af75a8e47', 'be88a049-8429-49d9-8b34-d12d78749e10', NULL, '123@qq.com');
INSERT INTO `tb_user` VALUES (4, 'zhaoliu', '699bf9534eec40254e007296c949e3d5', '0038a93b-8b2f-4a91-8137-24bc0e5e8399', '312312', '1231231');
COMMIT;

-- ----------------------------
-- Table structure for tb_user_role
-- ----------------------------
DROP TABLE IF EXISTS `tb_user_role`;
CREATE TABLE `tb_user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_tb_ur_user_id` (`user_id`),
  KEY `FK_tb_ur_role_id` (`role_id`),
  CONSTRAINT `FK_tb_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`),
  CONSTRAINT `FK_tb_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `tb_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_user_role
-- ----------------------------
BEGIN;
INSERT INTO `tb_user_role` VALUES (1, 1, 1);
INSERT INTO `tb_user_role` VALUES (2, 2, 1);
INSERT INTO `tb_user_role` VALUES (3, 2, 3);
INSERT INTO `tb_user_role` VALUES (4, 3, 2);
INSERT INTO `tb_user_role` VALUES (5, 4, 3);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

id

   id 列代表执行 SELECT 语句时加载数据表的顺序，查询语句中每出现一个SELECT关键字，就对应表分配一个唯一的id 值，id 及顺序关系存在 3 种情况：

  • id 相同时按从上到下的顺序加载数据表
  • id 不同时值越大则对应数据表越先被执行（即越大优先级越高）
  • id 同时存在相同值和不同值：id 相同的可以认为是一组，从上到下顺序执行；在不同组中，id 值越大，越先执行

id 相同

  下面通过EXPLAIN分析一下在查询所有用户的信息和其对应的权限信息时这 3 张表的执行顺序：

1
2
3
4
5
6
7
8
9
10
11
mysql> EXPLAIN 
    -> SELECT *
    -> FROM tb_user u,tb_role r,tb_user_role ur
    -> WHERE u.id = ur.user_id AND r.id = ur.role_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                   |    2 |   100.00 | NULL  |
|  1 | SIMPLE      | ur    | NULL       | ref    | FK_tb_ur_user_id,FK_tb_ur_role_id | FK_tb_ur_role_id | 4       | mysql_study.r.id       |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY                           | PRIMARY          | 4       | mysql_study.ur.user_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+-----------------------------------+------------------+---------+------------------------+------+----------+-------+

 从输出的结果可以看到： id 都相同为 1，此时按从上到下的顺序加载表。
 即最先加载 r（tb_role） 表，其次是 ur（tb_user_role） 表，最后是 u（tb_user） 表。

id 不同

  下面通过EXPLAIN分析一下在查询用户名为 boss 所对应的角色所拥有的权限时这 3 张表的执行顺序：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> EXPLAIN
    -> SELECT *
    -> FROM tb_role
    -> WHERE id = (SELECT role_id
    ->             FROM tb_user_role
    ->             WHERE user_id = (SELECT id
    ->                              FROM tb_user
    ->                              WHERE username = 'boss'));
+----+-------------+--------------+------------+-------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | tb_role      | NULL       | const | PRIMARY          | PRIMARY          | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | tb_user_role | NULL       | ref   | FK_tb_ur_user_id | FK_tb_ur_user_id | 4       | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | tb_user      | NULL       | const | unique_username  | unique_username  | 202     | const |    1 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+------------------+------------------+---------+-------+------+----------+-------------+

  根据 id 不同时值越大则对应数据表越先被执行的原则，上述 3 张表的执行顺序为：

  • tb_user
  • tb_user_role
  • tb_role

  这确实符合常理，子查询的数据表肯定是比外表先查询的，毕竟查询是由内而外所执行的过程。

id 相同亦不同

  下面通过EXPLAIN分析一下在从中间表查询 user_id 为 2 所拥有的所有角色及这些角色所拥有的权限时这 2 张表的执行顺序：

select_type

   select_type 列代表了 SELECT 的类型，取值及含义见下表：

取值 含义
SIMPLE 简单的单表查询或无子查询的 SQL 语句则为该标识
PRIMARY 若在查询语句中包含子查询，则最外层查询为该标识
SUBQUERY 若在 SELECT 或 WHERE 列中包含了子查询，则为该标识
DERIVED 若在 FROM 列中包含子查询，则为该标识
UNION 若第二个 SELECT 出现在 UNION 之后，则为标识
UNION RESULT 从 UNION 表获取结果的 SELECT

table

   table 列代表当前行记录的数据属于哪一张表。

type

   type 列代表访问类型，其取值及含义见下表：

取值 含义
ALL 全表扫描聚簇索引遍历来找到匹配的行
index 只遍历了二级索引树不进行回表操作，通常比 ALL 快，ALL 是遍历数据文件
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc'
range 利用索引进行范围匹配的，常见于 WHERE 后的 BETWEEN、<、>、IN 等操作
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79)
Index_merge 在一个查询中使用了多个二级索引
ref_or_null 非唯一性索引扫描，返回所有匹配某个列指定值或该列值IS NULL的所有行
SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;
ref 非唯一性索引扫描，返回所有匹配某个单独值的所有行
SELECT * FROM single_table WHERE key1 = 'abc'
eq_ref 类似 ref，区别在于eq_ref用于联表查询的情况，按联表的主键或唯一非空索引联合查询
const 在单表中使用主键值或者唯一二级索引列的值进行等值查找
system 表只有一行记录（等于系统表），const 的特例，一般不会出现
NULL MySQL 不访问任何表，直接返回结果

  性能由好到差的顺序如下：
  NULL –> system –> const –> eq_ref –> ref –> ref_or_null –> index_merge –> index_subquery –> range –> index –> ALL

key

   key 在输出结果中有 3 个：

列 含义
possible_keys 显示可能应用在该表的索引（一个或多个）
key 实际使用的索引（为 NULL 则未使用索引）
key_len 索引中使用的字节数，该值为索引字段最大可能长度

rows

   rows 列代表扫描行的数量。

filtered

  filtered 扇区，主要是针对其他条件过滤后满足的记录百分比，一般用于多表关联的情况，单表的无实际意义。

extra

   extra 列的取值及含义见下表：

取值 含义 效率
Using file sort 文件排序：MySQL 对数据使用一个外部的索引排序，而不是按照表内的索引顺序排序。 低
Using temporary 使用临时表保存中间结果，常见于 ORDER BY 和 GROUP BY 低
Using index 走索引 高
0%