旅法师营地 | No.23

我所理解的MySQL系列·第3篇·SQL调优必备知识点

2020/10/18 MySQL 共 13794 字，约 40 分钟

MySQL 系列的第三篇博客，主要内容是 MySQL 中关于 Explain 执行计划的分析，假如你已经知道如何分析执行计划，那么对于 SQL 调优也就信手拈来了。

纵观众多一二线大厂招聘时的岗位要求，但凡设计数据库的必定会要求有 SQL 调优的经验，这几乎已经成为与 Spring 不相上下的“八股文”类面试题。

要想进行 SQL 调优，首先需要知道 SQL 的执行情况，最直观的感觉当然是 SQL 语句执行的时间，然而除此之外，我们还可以通过执行计划来分析 SQL 语句的执行情况，从而进行调优。

mysql-3-封面

1. Explain 简述

Explain 语句可以查看 MySQL 是如何执行这条 SQL 语句的，包括使用索引情况、扫描行数等，这些信息对于 SQL 调优来说十分重要，所以首先得看懂执行计划。

mysql> explain select * from user where name='one';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | a             | a    | 13      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

以上是一条简单查询语句的执行计划，这张表一共有12个字段，分别代表不同的含义，下面一一叙述。

  • id: 表示 SQL 执行的顺序，值越大，优先级越高。若值相同，执行顺序由优化器决定。
  • select_type: 表示 select 查询语句的类型
  • table: SQL 语句查询的表名（或该表的别名），也可能是临时表等不存在的表
  • partitions: 查询语句涉及的分区信息
  • type: 关联类型（访问类型），决定了 MySQL 是如何查找表中行的。性能从最差到最优依次是 ALL, index, range, index_merge, ref, eq_ref, const, system, NULL
  • possible_keys: 展示了查询语句可以使用的所有索引
  • key: 展示了优化器决定采用的索引名称
  • key_len: 展示了 MySQL 使用索引长度的字节数
  • ref: 在 key 列记录的索引中查找值所使用的列或常量
  • rows: 扫描行数的估值
  • filtered: 最终满足查询语句行数占存储引擎返回总行数的百分比
  • Extra: 其他执行信息

以上只是对执行计划表各个字段的名词解释，接下来我会通过实际的例子来帮助大家（我自己）更好地理解其中 select_type, type, key_len, rows, Extra 这些重要的字段。

2. Explain 详述

2.1 示例表结构

首先介绍本文中将用到的示例表表结构以及数据行：

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(36) DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `email` varchar(36) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_age_name`(`age`, `name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;

通过函数向表中插入1000000条测试数据。

CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()
begin 
  declare i int; 
  set i=1; 
  while(i<=1000000)do 
    insert into user(id,name,age,email) values(i, CONCAT('name',i), i % 50, concat(i,'name@email.cn'));
    set i=i+1; 
  end while;
end

2.2 select_type in Explain

执行计划中 select_type 字段表示 select 查询语句的类型，常见类型有：

  • SIMPLE: 简单的查询语句，不包括子查询和关联，如：
mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.2.1 PRIMARY

若查询语句中包含任何复杂的子部分，那么最外层部分会被标记为 PRIMARY，如：

mysql> explain select * from user where id=(select id from user where id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

在这条 SQL 语句的执行计划中，第一条执行的 SQL，即 select * from yser where id = (...) 就被标记为 PRIMARY

2.2.2 SUBQUERY

包含在 select 或 where 内容中的子查询会被标记为 SUBQUERY，如上一条示例 SQL 的执行计划中第二条语句，即 select id from user where id=1 的 select_type 就被标记为了SUBQUERY。

2.2.3 DERIVED

包含在 FROM 关键字后的子查询（即将子查询的结果视为「表」），被视为「表」的子查询会被标记为 DERIVED，其结果将被存放在临时表中，如：

mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | user       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

从执行计划中可以看到，第二条执行的 SQL，即 select id,name,count(*) from user where id=1 的查询类型是 DERIVED。

select_type 一共有12中查询类型，具体释义可以看官方文档-explain_select_type

2.3 type in Explain

type 字段是执行计划中衡量 SQL 非常重要的依据，它展示了 SQL 语句的关联类型（访问类型），决定了 MySQL 是如何查找表中行的。

type 字段的值性能从最差到最优依次是 ALL, index, range, index_merge, ref, eq_ref, const, system。

为了能更好地理解各个类型的含义，我对上述每一种类型都举出了相应的示例。

并未全部列出，完整的解释可以看官方文档-EXPLAIN Join Types

2.3.1 ALL

ALL 表示全表扫描，意味着存储引擎查找记录时未走索引，所以它是性能最差的一种访问类型，如

mysql> explain select * from user where age+2=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002301 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到 rows 行的值为1002301，即扫描了全表的所有数据（扫描行数的值实际为估算），如果在生产环境有这样的 SQL，绝对是要优化的。

我们知道在 where 查询条件中，不应该对查询字段使用函数或表达式（应该写在等号不等号右侧），不了解此内容的可以看看我的上一篇博客 —— 我所理解的MySQL(二)索引。

这条查询语句在优化后应该是: select * from user where age=18，去掉等号左侧的表达式，优化后的执行计划如下:

mysql> explain select * from user where age=18;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39360 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.3.2 index

index 表示全索引树扫描，由于扫描的是索引树，所以比 ALL 形式的全表扫描性能要好。

同时，由于索引树本身就是有序的，可以避免排序。

mysql> explain select id,age from user where name='name1';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_age_name | 116     | NULL | 1002301 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

示例查询语句如上所述，当查询条件存在于联合索引 idx_age_name 中，但又无法直接使用该索引（由于最左前缀原则），同时查询列 id,age 也存在于联合索引中，无须通过回表来获取时，执行计划中的访问类型 type 列就会是 index。

2.3.3 range

range 表示范围扫描，准确的说是基于索引树的范围扫描，扫描的是部分索引树，所以性能比 index 稍好。

需要注意的是，若使用 in 或者 or 时，也可以使用范围扫描。

mysql> explain select * from user where age>18 and age<20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 36690 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user where age=18 or age=20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 78720 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

2.3.4 index_merge

index_merge 即索引合并，它表示在查询时 MySQL 会使用多个索引。

MySQL 在 where 语句中存在多个查询条件，并且其中存在多个字段可以分别使用到多个不同的索引，在这种情况下 MySQL 可以对多个索引树同时进行扫描，最后将它们的结果进行合并，如：

mysql> explain select * from user where id=1 or age=18;
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                                               |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4     | NULL | 39361 |   100.00 | Using sort_union(idx_age_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

上面这条查询语句中的 id=1 和 age=18 分别使用到了 PRIMARY 主键索引和 idx_age_name 联合索引，最后再将满足这两个条件的记录进行合并。

2.3.5 ref

ref 表示索引访问（索引查找），这种访问类型会出现在查询条件中以非聚簇索引列的常量值进行查询的情况。

比如在介绍全表扫描中优化后 SQL 的访问类型就是 ref。

2.3.6 eq_ref

eq_ref 这种访问类型会出现在连接查询时，通过聚簇索引进行连接的情况，此类型最多只返回一条符合条件的记录。若表的聚簇索引为联合索引，所有的索引列必须是等值查询，如：

mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows    | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+
|  1 | SIMPLE      | user1 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                | 1002301 |   100.00 | NULL  |
|  1 | SIMPLE      | user2 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | all_in_one.user1.id |       1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

2.3.7 const

const 这种访问类型会出现在通过聚簇索引进行常量等值查询的情况，如：

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.4 key_len in Explain

在上一篇博客 —— 我所理解的MySQL(二)索引 中 5.2 部分字段匹配 中已经提到过关于索引长度的计算方式，这里再来总结一下。

2.4.1 字符类型

字符类型的字段若作为索引列，它的索引长度 = 字段定义长度 * 字符长度 + 是否默认NULL + 是否是变长字段，其中：

  • 字段定义长度 就是定义表结构时跟在字段类型后括号中的数字
  • 字符长度 是常数，utf8=3, gbk=2, latin1=1
  • 是否默认NULL 也是常数，若字段默认值为 NULL，该值为1，因为 NULL 需要额外的一个字节来表示；否则该值为0
  • 是否是变长字段 也是常数，若该字段为变长字段，该值为2；否则该值为0

所谓的变长字段就是 varchar，它所占用的就是字段实际内容的长度而非定义字段时的长度。而定长字段，也就是 char 类型，它所占用的空间就是自定字段时的长度，若超过会被截取。

举个例子，为上述实例表中添加一个字符类型字段的索引。

alter table user add index idx_name(`name`);

然后通过 name 字段去做查询，查看执行计划。

mysql> explain select * from user where name='name1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 111     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

可以看到，执行计划中 key_len 一列的值为 111。

根据上述索引长度的计算公式，name 列字段定义长度为36，字符集类型为默认的 utf8，该字段默认允许 NULL，同时该字段是可变长字段 varchar。

所以 idx_name 索引的索引长度=36*3+1+2=111，恰如执行计划中显示的值。

2.4.2 其他定长类型

对于定长类型的字段，其索引长度与它的数据类型长度是一致的。

数据类型长度
int4
bigint8
date3
datetime8
timestamp4
float4
double8

需要注意的是，若该字段允许默认值为 NULL，与字符类型一样，其索引长度也需要加上1。

mysql> explain select * from user where age=1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39366 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

如上面这个示例（本示例中索引只用到了 age 字段），age 字段为 int 类型，其索引长度本应为 4，但由于 age 字段默认允许为 NULL，所以它的索引长度就变成了5。

2.5 rows in Explain

扫描行数在执行计划中其实是一个估值，MySQL 会选择 N 个不同的索引数据页，计算平均值得到单页索引基数，然后再乘以索引页面数，就得到了扫描行数的估值。

扫描行数就是优化器考量索引执行效率的因素之一，一般而言扫描行数越少，执行效率越高。

2.6 Extra in Explain

执行计划中 Extra 字段的常见类型有：

  • Using index: 使用了覆盖索引，以避免回表
  • Using index condition: 使用了索引下推，具体可以看我的上一篇博客 —— 我所理解的MySQL(二)索引
  • Using where: 表示MySQL 会通过 where 条件过滤记录
    • 全表扫描：where 中有该表字段作为搜索条件
    • 扫描索引树：where 中包含索引字段之外的其他字段作为搜索条件
  • Using temporary: MySQL 在对查询结果排序时会使用临时表
  • Using filesort: 对结果进行外部索引排序（文件排序），排序不走索引
    • 数据较少时在内存中排序，数据较多时在磁盘中排序
    • 尽量避免该信息出现在执行计划中

3. 参考资料

最后，本文收录于个人语雀知识库: 我所理解的后端技术，欢迎来访。

文档信息

Search

    Table of Contents