文章

MySQL 基于成本的优化

查询成本

我们知道对于同一个 SQL，可以按照不同的方案去执行，MySQL 会选择执行成本最低的那种方案。

一条语句的查询成本由下面两个方面组成：

  1. IO 成本：InnoDB 把表存储在磁盘上，查询的时候，需要把数据加载到内存中，这个将数据从磁盘加载到内存的过程花费的时间就是 IO 成本。

  2. CPU 成本：检查数据是否满足查询条件以及对结果集排序就是 CPU 成本。

对于 InnoDB 存储引擎来说，规定读取一个页的成本为 1，检查一条记录是否符合查询条件的成本为 0.2，这两个值也叫成本常数。

单表查询的成本

以之前用过的 employee 表为例：

CREATE TABLE `employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100),
  `employee_id` INT,
  `english_name` VARCHAR(100),
  `country` VARCHAR(100),
  `province` VARCHAR(100),
  `city` VARCHAR(100),
  `extra_info` VARCHAR(100),
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  UNIQUE KEY `idx_employee_id` (`employee_id`),
  KEY `idx_english_name` (`english_name`),
  KEY `idx_country_province_city` (`country`, `province`, `city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 同时还要创建一个跟 employee 表一样的 employee2 表

基于成本的优化

基于成本的优化过程大致如下：

  1. 根据查询条件，找出所有可能使用的索引

  2. 计算全表扫描的查询成本

  3. 计算使用不同索引的查询成本

  4. 对比全表扫描和不同索引的查询成本，选择成本最小的那一个（选择出来的查询成本最小的方案也叫执行计划）

以下面这个查询为例，我们详细分析下基于成本的优化过程：

SELECT * FROM employee 
WHERE name IN ('Tom', 'Jerry', 'Louis') 
AND employee_id > 10 AND employee_id < 1000 
AND english_name > name 
AND country LIKE '%republic%' 
AND common_field = 'abc';

1. 根据查询条件，找出所有可能使用的索引

这个 SQL 中有这些查询条件：

  1. name IN ('Tom', 'Jerry', 'Louis')，可以使用 idx_name 索引

  2. employee_id > 10 AND employee_id < 1000，可以使用 idx_employee_id 索引

  3. english_name > name，没有跟常数比较，不能使用索引

  4. country LIKE '%republic%'，LIKE使用了以通配符开头的模式，不能使用索引

  5. common_field = 'abc'，common_field 列没有索引，不能使用索引

综上，该 SQL 可能使用的索引（possible_keys）有：idx_name 和 idx_employee_id。

2. 计算全表扫描的查询成本

由于查询成本 = IO 成本 + CPU 成本，因此计算全表扫描的查询成本需要知道：

  1. 聚簇索引页数

  2. 表中的记录数

MySQL 每张表都维护了这些统计信息，通过 SHOW TABLE STATUS LIKE '<table_name>' 语句可以查看表的统计信息，如：

mysql> SHOW TABLE STATUS LIKE 'employee'\G
*************************** 1. row ***************************
Name: employee
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 9414
Ava_row_length: 168
Data_length: 1589248
Max_data_length: 0
Index_length: 1507328
Data_free: 4194304
Auto_increment: 10001
Create_time: 2023-03-01 14:59:58
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:

我们现在只用关注 Rows 和 Data_length：

  1. Rows：表中的记录数（对于InnoDB 表，这个是个估计值）

  2. Data_length：表占用的存储空间字节数（对于InnoDB 表，这个是聚簇索引占用的存储空间大小，因此 Data_length = 聚簇索引页数 * 单页大小，可以计算得：聚簇索引页数 = 1589248 / 16384 = 97）

现在我们已经知道了聚簇索引页数和表中的记录数，可以计算 employee 表全表扫描的成本了：

  1. IO 成本 = 97 * 1 = 97

  2. CPU 成本 = 9414 * 0.2 = 1882.8

那么全表扫描的成本就是97 + 1882.8 = 1979.8。

实际 MySQL 计算成本时还会进行微调，也就是会在计算出的成本上再加一个微调值，不过接下来我们的计算都不引入微调值。

MySQL 根据聚簇索引的总页数计算的全表扫描的 IO 成本，但是我们知道全表扫描其实只用遍历叶子节点就行，所以这里是有一点小误差，不过基本没什么影响。

3. 计算使用不同索引的查询成本

MySQL 查询优化器会先分析使用唯一二级索引的查询成本，再分析普通二级索引的查询成本，我们也按照这个顺序来分析。另外，除了分析单个索引的查询成本，还要分析是否能用索引合并。

使用 idx_employee_id 索引的查询成本

idx_employee_id 索引对应的查询条件是：employee_id > 10 AND employee_id < 1000。

对于这种`二级索引 + 回表`形式的查询，成本涉及这几个方面：

(1) 读取二级索引页的成本（IO 成本）

由于难以估算会读取多少索引页，因此 MySQL 粗略认为读取索引的一个范围区间相当于读取一个页的成本。这里只有一个 (10, 1000) 的范围区间，因此这项成本为 1。

(2) 检查二级索引记录的成本（CPU 成本）

在这个例子中，需要计算区间 (10, 1000) 中的索引记录数：

  1. 首先找到满足 employee_id > 10 和 employee_id < 1000 的第一条记录，分别记为区间最左记录和区间最右记录，这个过程是常数级别的，成本忽略不计。

  2. 如果区间最左记录和区间最右记录相隔不超过 10 个页，则精确计算其中的记录；否则只沿着区间最左记录往右读 10 个页，计算平均每个页中符合条件的记录数，再将这个平均值乘以区间最左记录和区间最右记录之间的页数，就是大致的记录数，这里假设我们算出来是 98 条记录。（计算区间最左记录和区间最右记录之间的页数，就是计算它们父节点中对应目录项记录间隔的目录项记录数）

因此，这项成本为：98 * 0.2 = 19.6

(3) 回表时读取聚簇索引页的成本（IO 成本）

MySQL 认为每条记录回表就相当于访问一个页面，因此这项成本为：98 * 1 = 98。

(4) 回表时检查聚簇索引记录的成本（CPU 成本）

这项成本为：98 * 0.2 = 19.6

综上，使用idx_employee_id 索引的查询成本为：1 + 19.6 + 98 + 19.6 = 138.2

使用 idx_name 索引的查询成本

idx_name 索引对应的查询条件是：name IN ('Tom', 'Jerry', 'Louis')。

(1) 读取二级索引页的成本

这个例子相当于 3 个单点区间：['Tom', 'Tom']、['Jerry', 'Jerry'] 和 ['Louis', 'Louis']，则这项成本为：3 * 1 = 3。

(2) 检查二级索引记录的成本

分别计算这 3 个单点区间中的记录数，方法同上，假设这 3 个区间中的记录数分别为：67、8 和 48，一共 123 条记录，则这项成本为：123 * 0.2 = 24.6。

(3) 回表时读取聚簇索引页的成本

这项成本为：123 * 1 = 123。

(4) 回表时检查聚簇索引记录的成本

这项成本为：123 * 0.2 = 24.6。

综上，使用 idx_name 索引的查询成本为：3 + 24.6 + 123 + 24.6 = 175.2。 

是否能用索引合并

这里 idx_name 和 idx_employee_id 的查询条件使用 AND 连接，可能使用 Intersection 索引合并，但是 idx_employee_id 索引使用的是范围查询，不符合使用 Intersection 索引合并的条件，所以我们这个例子不能使用索引合并。

4. 对比全表扫描和不同索引的查询成本，选择成本最小的那一个

列出上面计算出的成本：

  1. 全表扫描的成本：1979.8

  2. 使用 idx_employee_id 的成本：138.2

  3. 使用 idx_name 的成本：175.2

比较可知，使用 idx_employee_id 索引的查询成本最低，因此会使用索引 idx_employee_id 来执行查询。

基于索引统计数据的成本计算

MySQL 把上面那种直接计算区间最左记录和区间最右记录中的记录数的方式称为 index dive。但是有时候执行的查询会有很多单点区间，这时候直接计算区间最左记录和区间最右记录中的记录数成本会非常高，甚至比全表扫描成本还高，因此 MySQL 在单点区间数大于 eq_range_index_dive_limit（默认为 200） 时会使用索引统计数据来计算成本。这些索引统计数据可以使用 SHOW INDEX FROM <table_name> 来查看，如：

mysql> SHOW INDEX FROM employee;
+----------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee |          0 | PRIMARY                   |            1 | id           | A         |        9388 |     NULL | NULL   |      | BTREE      |         |               |
| employee |          0 | idx_employee_id           |            1 | employee_id  | A         |        9414 |     NULL | NULL   | YES  | BTREE      |         |               |
| employee |          1 | idx_name                  |            1 | name         | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| employee |          1 | idx_english_name          |            1 | english_name | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| employee |          1 | idx_country_province_city |            1 | country      | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| employee |          1 | idx_country_province_city |            2 | province     | A         |          56 |     NULL | NULL   | YES  | BTREE      |         |               |
| employee |          1 | idx_country_province_city |            3 | city         | A         |         129 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我们了解下这些属性：

名称

说明

Table

索引所属的表

Non_unique

索引值是否唯一，聚簇索引和唯一二级索引该列值为 0 ，普通二级索引该列值为 1

Key_name

索引名称

Seq_in_index

索引列在索引中的位置，从 1 开始

Column_name

索引列的名称

Collation

索引列中值的排序方式，A表示升序，NULL表示降序

Cardinality

索引列值的基数，即索引列中不重复值的数量（对于 InnoDB 来说，这个是估计值）

Sub_part

前缀索引的n值，NULL表示使用完整索引

Packed

索引列如何被压缩，NULL表示未被压缩

Null

该索引列是否允许存储NULL值

Index_type

索引类型，如BTREE

Comment

未在索引列中描述的有关索引的信息

Index_comment

索引注释信息

假如我们要计算下面这条 SQL 的成本：

SELECT * FROM employee WHERE name IN ('Tom', 'Jerry', ..., 'Louis');  -- IN 后面的参数有 500 个

由于 IN 后面的参数有 500 个，意味范围区间大于 200 个，不使用 index dive 而是使用统计数据计算成本。过程如下：

  1. 通过 SHOW INDEX FROM employee 拿到 idx_name 的 Cardinality 值为 827

  2. 通过 SHOW TABLE STATUS LIKE 'employee' 拿到 Rows 值为 9414

  3. 计算一个索引值的平均记录数 = Rows / Cardinality ≈ 12

  4. 计算回表记录数 ≈ 500 * 12 = 6000

  5. 计算成本 ...

连接查询的成本

我们已经知道连接查询使用的是嵌套循环连接算法，它的查询成本如下：

  1. 单次访问驱动表的成本

  2. 多次访问被驱动表的成本（取决于驱动表查询结果集中的记录数）

MySQL 把驱动表查询结果集中的记录数称为驱动表的扇出（fanout）。

连接查询的成本计算公式是这样的：连接查询的总成本 = 单次访问驱动表的成本 + 驱动表的扇出 * 单次访问被驱动表的成本，可知驱动表的扇出越小，被驱动表的查询次数越少，连接查询的总成本越低。

连接查询成本占大头的是：驱动表的扇出 * 单次访问被驱动表的成本，因此连接查询成本的优化重点其实是下边这两个部分: 

  1. 尽量减小驱动表的扇出

  2. 对被驱动表的访问成本要尽量低

根据连接查询的成本优化重点，我们写 SQL 的时候要尽量在被驱动表的连接列上建立索引。如果可以，驱动表的连接列最好是主键或者唯一二级索引。

我们下面看下如何计算驱动表的扇出，，有的查询很容易计算驱动表的扇出，如：

SELECT * FROM employee AS s1 INNER JOIN employee2 AS s2 WHERE s1.employee_id > 10 AND s1.employee_id < 1000;

对于上面的查询，通过 index dive 方式可以计算出区间 (10, 1000) 之间的索引记录数，这个值对应的就是驱动表的扇出。有的查询计算驱动表的扇出只能靠猜，如：

SELECT * FROM employee AS s1 INNER JOIN employee2 AS s2 WHERE s1.employee_id >10 AND s1.employee_id < 1000 AND s1.extra_info > 'abc';

由于查询优化器不会去执行 s1.extra_info > 'abc'，因此优化器只能猜用这个条件过滤后还剩多少记录数，这个猜的值就是上面这个查询的驱动表的扇出。MySQL 把这种用猜计算扇出的方式称之为 condition filtering。

在计算机领域，fanout 表示一个数据流从一个源传输到多个目的地。

多表连接的成本分析

想要得到最低成本的查询方案，需要分情况讨论：

  1. 对于左连接和右连接来说，驱动表是固定的，因此只需要考虑为驱动表和被驱动表选择成本最低的访问方法即可。

  2. 对于内连接来说，驱动表可以由优化器来选择的，因此需要考虑两个方面：

    1. 不同的表作为驱动表最终的查询成本是不同的，因此要考虑表的连接顺序

    2. 为驱动表和被驱动表选择成本最低的访问方法

确定有多少种连接顺序：

  1. 两表连接，有 AB 和 BA 这 2 种连接顺序

  2. 三表连接，有 ABC、ACB、BAC、BCA、CAB 和 CBA 这 6 种连接顺序

  3. 四表连接，有 4 3 2 * 1 = 24 种连接顺序

  4. n 表连接，有 n (n - 1) (n - 2) ... 1 种连接顺序，即 n!

正常应该把所有这些连接顺序的成本都分析一遍，但是效率太低了，因此 MySQL 做了一些优化：

  1. 不满足某些规则的连接顺序直接不予考虑：MySQL 称这些规则为启发式规则（通过系统变量 optimizer_prune_level 控制是否使用启发式规则，默认开启）

  2. 限制穷举分析的连接表数量：MySQL 定义了一个系统变量 optimizer_search_depth 来限制穷举分析的连接表数量，默认值为 62，即连接表数量小于 62 才穷举分析每一种连接顺序的成本。

  3. 提前结束成本分析：MySQL 维护了一个表示当前最小连接查询成本的变量，如果分析某个连接顺序的成本时发现成本已经高于最小成本，就不会继续分析这个连接顺序了。

调节成本常数

我们上面介绍了两个成本常数：

  1. InnoDB 读取一个页的成本为 1

  2. InnoDB 检查一条记录是否符合查询条件的成本为 0.2

除了这两个成本常数，MySQL 还有很多成本常数，它们保存在名为 mysql 的系统数据库的两个 cost 表中：

mysql> SHOW TABLES FROM mysql LIKE '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+

我们知道一条 SQL 的执行分为两层：

  1. Server 层，这一层的成本常数存储在 server_cost 表中

  2. 存储引擎层，这一层的成本常数存储在 engine_cost 表中

mysql.server_cost 表

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2023-08-22 17:30:27 | NULL    |
| disk_temptable_row_cost      |       NULL | 2023-08-22 17:30:27 | NULL    |
| key_compare_cost             |       NULL | 2023-08-22 17:30:27 | NULL    |
| memory_temptable_create_cost |       NULL | 2023-08-22 17:30:27 | NULL    |
| memory_temptable_row_cost    |       NULL | 2023-08-22 17:30:27 | NULL    |
| row_evaluate_cost            |       NULL | 2023-08-22 17:30:27 | NULL    |
+------------------------------+------------+---------------------+---------+

我们先看一下 server_cost 表各个列的含义：

  1. cost_name: 成本常数的名称

  2. cost_value: 成本常数对应的值

  3. last_update: 记录最后更新时间

  4. comment: 注释

然后看一下 server_cost 表中各个成本常数的含义：

成本常数

说明

disk_temptable_create_cost

基于磁盘的临时表的创建成本（增大该值，优化器会尽量少创建基于磁盘的临时表）

disk_temptable_row_cost

向基于磁盘的临时表插入或读取记录的成本（增大该值，优化器会尽量少创建基于磁盘的临时表）

key_compare_cost

比较两条记录的成本，一般用在排序中（增大该值，会增加 filesort 成本，此时优化器会尽量使用索引排序）

memory_temptable_create_cost

基于内存的临时表的创建成本（增大该值，优化器会尽量少创建基于内存的临时表）

memory_temptable_row_cost

向基于内存的临时表插入或读取记录的成本（增大该值，优化器会尽量少创建基于内存的临时表）

row_evaluate_cost

检查一条记录是否符合查询条件的成本

这些成本常数默认都是 NULL，表示使用默认值，如果想要修改，只需要两步即可：

  1. 更新成本常数记录值：`UPDATE mysql.server_cost SET row_evaluate_cost = 0.3;`

  2. 清除优化器的成本估算缓存：`FLUSH OPTIMIZER_COSTS;`

MySQL 在执行 DISTINCT、分组、Union 以及某些排序时可能会用到临时表，如：对于 DISTINCT 查询，可以创建一个含唯一索引的临时表，然后把需要求 DISTINCT 的记录插入这个临时表中，插入完成后表中的记录就是结果集。

在数据量不大的时候，可能会使用基于内存的临时表；在数据量不大的时候，可能会使用基于磁盘的临时表

mysql.engine_cost 表

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost     |       NULL | 2023-08-22 17:30:27 | NULL    |
| default     |           0 | memory_block_read_cost |       NULL | 2023-08-22 17:30:27 | NULL    |
+-------------+-------------+------------------------+------------+---------------------+---------+

engine_cost 比 server_cost 多了两列: 

  1. engine_name: 成本常数适用的存储引擎名称，default 表示适用于所有存储引擎

  2. device_type: 存储引擎的设备类型，区分机械硬盘和固态硬盘

然后看一下 server_cost 表中各个成本常数的含义：

成本常数

默认值

说明

io_block_read_cost

1.0

从磁盘读取一个块的成本（对于 InnoDB，一个页就是一个块。增大这个值会增加 IO 成本，使得优化器更倾向于使用索引查询而不是全表扫描）

memory_block_read_cost

1.0

从内存读取一个块的成本

可以发现 io_block_read_cost 和 memory_block_read_cost 的默认值都是 1.0，这是因为 MySQL 现阶段无法判断一个块是否已经加载到内存中，因此暂时都将默认值定为了 1。

更新 engine_cost 表中成本常数和更新 server_cost 表中成本常数的方法一致，不再说明。

MySQL
License:  CC BY 4.0