MySQL 的执行原理（二）

5.3. MySQL 的查询成本

5.3. MySQL 的查询成本

MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者
说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模
糊的，其实在 MySQL 中一条查询语句的执行成本是由下边这两个方面组成的：
I/O 成本
我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到
磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然
后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。
CPU 成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作
损耗的时间称之为 CPU 成本。

对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL
规定读取一个页面花费的成本默认是 1.0，读取以及检测一条记录是否符合搜索
条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我
们最常用到，当然还有其他的成本常数。
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。

5.3.2. 单表查询的成本

5.3.2.1. 基于成本的优化步骤实战

在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句
所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是
所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程
总结一下就是这样：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S',
'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND expire_time<=
'2021-03-22 18:35:09' AND insert_time> expire_time AND order_note LIKE '%7 排
1%' AND order_status = 0;
1
2
3
4

乍看上去有点儿复杂，我们一步一步分析一下。

1. 根据搜索条件，找出所有可能使用的索引
   我们前边说过，对于 B+树索引来说，只要索引列和常数使用=、<=>、IN、
   NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写
   成<>）或者 LIKE 操作符连接起来，就可以产生一个所谓的范围区间（LIKE 匹配字
   符串前缀也行），MySQL 把一个查询中可能使用到的索引称之为 possible keys。
   我们分析一下上边查询中涉及到的几个搜索条件：

• order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) ，这个搜索条件可以使用二
  级索引 idx_order_no。
• expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’，
  这个搜索条件可以使用二级索引 idx_expire_time。
• insert_time> expire_time，这个搜索条件的索引列由于没有和常数比较，所
  以并不能使用到索引。
• order_note LIKE ‘%hello%’，order_note 即使有索引，但是通过 LIKE 操作符和
  以通配符开头的字符串做比较，不可以适用索引。
• order_status = 0，由于该列上只有联合索引，而且不符合最左前缀原则，所
  以不会用到索引。
  综上所述，上边的查询语句可能用到的索引，也就是 possible keys 只有
  idx_order_no,idx_expire_time。
  
  2. 计算全表扫描的代价
  对于 InnoDB 存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依
  次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需
  要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由
  于查询成本=I/O 成本+CPU 成本，所以计算全表扫描的代价需要两个信息：
  聚簇索引占用的页面数
  该表中的记录数
  这两个信息从哪来呢？MySQL 为每个表维护了一系列的统计信息，关于这些
  统计信息是如何收集起来的我们放在后边再说，现在看看怎么查看这些统计信息。
  MySQL 给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息，如果
  要看指定的某个表的统计信息，在该语句后加对应的 LIKE 语句就好了，比方说
  我们要查看 order_exp 这个表的统计信息可以这么写：

SHOW TABLE STATUS LIKE 'order_exp'\G
1

出现了很多统计选项，但我们目前只需要两个：
Rows
本选项表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说，该值是
准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。从查询结果
我们也可以看出来，由于我们的 order_exp 表是使用 InnoDB 存储引擎的，所以
虽然实际上表中有 10567 条记录，但是 SHOW TABLE STATUS 显示的 Rows 值只有
10350 条记录。
Data_length
本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该
值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚
簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：

• Data_length = 聚簇索引的页面数量 x 每个页面的大小
  我们的 order_exp 使用默认 16KB 的页面大小，而上边查询结果显示
• Data_length 的值是 1589248，所以我们可以反向来推导出聚簇索引的页面数量：
  聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
  我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所
  以就可以计算全表扫描成本了。
  现在可以看一下全表扫描成本的计算过程：
  I/O 成本
  97 x 1.0 + 1.1 = 98.1
  97 指的是聚簇索引占用的页面数，1.0 指的是加载一个页面的成本常数，后
  边的 1.1 是一个微调值。
  TIPS：MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编
  码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们分析。
  CPU 成本
  10350x 0.2 + 1.0 = 2071
  10350 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估
  计值，0.2 指的是访问一条记录所需的成本常数，后边的 1.0 是一个微调值。
  总成本：
  98.1 + 2071 = 2169.1
  综上所述，对于 order_exp 的全表扫描所需的总成本就是 2169.1。

TIPS：我们前边说过表中的记录其实都存储在聚簇索引对应 B+树的叶子节点
中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组
成的双向链表把所有记录都查看一遍。
也就是说全表扫描这个过程其实有的 B+树非叶子节点是不需要访问的，但
是 MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O
成本的依据，是不区分非叶子节点和叶子节点的。

3. 计算使用不同索引执行查询的代价

• 从第 1 步分析我们得到，上述查询可能使用到 idx_order_no，idx_expire_time
  这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分
  析是否可能使用到索引合并。这里需要提一点的是，MySQL 查询优化器先分析使
  用唯一二级索引的成本，再分析使用普通索引的成本，我们这里两个索引都是普
  通索引，先算哪个都可以。我们也先分析 idx_expire_time 的成本，然后再看使用
  idx_order_no 的成本。

• 使用 idx_expire_time 执行查询的成本分析
  idx_expire_time 对应的搜索条件是：expire_time> ‘2021-03-22 18:28:28’ AND
  expire_time<= ‘2021-03-22 18:35:09’ ，也就是说对应的范围区间就是：
  (‘2021-03-22 18:28:28’ , ‘2021-03-22 18:35:09’ )。

• 思考题：扫描区间怎么样从我们复杂的 SQL 语句里提取出来？前面已经讲过
  了，不记得的同学回看一下章节《3.2.3.深入思考索引在查询中的使用》。
  使用 idx_expire_time 搜索会使用用二级索引 + 回表方式的查询，MySQL 计
  算这种查询的成本依赖两个方面的数据：
  1、范围区间数量
  不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索
  引的一个范围区间的 I/O 成本和读取一个页面是相同的。本例中使用
  idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引
  付出的 I/O 成本就是：1 x 1.0 = 1.0
  2、需要回表的记录数
  优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来
  说就是要计算 idx_expire_time 在(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’)
  这个范围区间中包含多少二级索引记录，计算过程是这样的：

• 步骤 1：先根据 expire_time> ‘2021-03-22 18:28:28’这个条件访问一下
  idx_expire_time 对应的 B+树索引，找到满足 expire_time> ‘2021-03-22 18:28:28’ 这个条件的第一条记录，我们把这条记录称之为区间最左记录。我们前头说过在
  B+数树中定位一条记录的过程是很快的，是常数级别的，所以这个过程的性能消
  耗是可以忽略不计的。

• 步骤 2：然后再根据 expire_time<= ‘2021-03-22 18:35:09’这个条件继续从
  idx_expire_time 对应的 B+树索引中找出最后一条满足这个条件的记录，我们把
  这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。

• 步骤 3：如果区间最左记录和区间最右记录相隔不太远（在 MySQL 5.7 这个
  版本里，只要相隔不大于 10 个页面即可），那就可以精确统计出满足 expire_time>
  ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’条件的二级索引记
  录条数。否则只沿着区间最左记录向右读 10 个页面，计算平均每个页面中包含
  多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量
  就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少
  个页面呢？解决这个问题还得回到 B+树索引的结构中来。

我们假设区间最左记录在页 b 中，区间最右记录在页 c 中，那么我们想计算
区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页 c 之间有多
少页面，而它们父节点中记录的每一条目录项记录都对应一个数据页，所以计算
页 b 和页 c 之间有多少页面就相当于计算它们父节点（也就是页 a）中对应的目
录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本
就很小了。

• 不过还有问题，如果页 b 和页 c 之间的页面实在太多，以至于页 b 和页 c 对
  应的目录项记录都不在一个父页面中怎么办？既然是树，那就继续递归，之前我
  们说过一个 B+树有 4 层高已经很了不得了，所以这个统计过程也不是很耗费性
  能。
• 知道了如何统计二级索引某个范围区间的记录数之后，就需要回到现实问题
  中来，MySQL 根据上述算法测得 idx_expire_time 在区间(‘2021-03-22 18:28:28’ ，
  ‘2021-03-22 18:35:09’)之间大约有 39 条记录。

explain SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09';
1

读取这 39 条二级索引记录需要付出的 CPU 成本就是：
39 x 0.2 + 0.01 = 7.81
其中 39 是需要读取的二级索引记录条数，0.2 是读取一条记录成本常数，0.01
是微调。
在通过二级索引获取到记录之后，还需要干两件事儿：
1、根据这些记录里的主键值到聚簇索引中做回表操作
MySQL 评估回表操作的 I/O 成本依旧很简单粗暴，他们认为每次回表操作都
相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少
次回表操作，也就是需要进行多少次页面 I/O。我们上边统计了使用
idx_expire_time 二级索引执行查询时，预计有 39 条二级索引记录需要进行回表
操作，所以回表操作带来的 I/O 成本就是：
39 x 1.0 = 39 .0
其中 39 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数。
2、回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立
回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的
用户记录，然后再检测除 expire_time> ‘2021-03-22 18:28:28’ AND expire_time<
'2021-03-22 18:35:09’这个搜索条件以外的搜索条件是否成立。

因为我们通过范围区间获取到二级索引记录共 39 条，也就对应着聚簇索引
中 39 条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索
条件的 CPU 成本如下：

39 x 0.2 =7.8
其中 39 是待检测记录的条数，0.2 是检测一条记录是否符合给定的搜索条
件的成本常数。
所以本例中使用 idx_expire_time 执行查询的成本就如下所示：
I/O 成本：
1.0 + 39 x 1.0 = 40 .0 (范围区间的数量 + 预估的二级索引记录条数)
CPU 成本：
39 x 0.2 + 0.01 + 39 x 0.2 = 15.61 （读取二级索引记录的成本 + 读取并检测
回表后聚簇索引记录的成本）
综上所述，使用 idx_expire_time 执行查询的总成本就是：
40 .0 + 15.61 = 55.61

使用 idx_order_no 执行查询的成本分析
idx_order_no 对应的搜索条件是：order_no IN (‘DD00_6S’, ‘DD00_9S’,
‘DD00_10S’)，也就是说相当于 3 个单点区间。
与使用 idx_expire_time 的情况类似，我们也需要计算使用 idx_order_no 时需
要访问的范围区间数量以及需要回表的记录数，计算过程与上面类似，我们不详
列所有计算步骤和说明了。
范围区间数量
使用 idx_order_no 执行查询时很显然有 3 个单点区间，所以访问这 3 个范围
区间的二级索引付出的 I/O 成本就是：
3 x 1.0 = 3.0
需要回表的记录数
由于使用 idx_expire_time 时有 3 个单点区间，所以每个单点区间都需要查找
一遍对应的二级索引记录数，三个单点区间总共需要回表的记录数是 58。

explain SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S',
'DD00_10S');
1
2

读取这些二级索引记录的 CPU 成本就是：58 x 0.2 + 0.01 = 11.61
得到总共需要回表的记录数之后，就要考虑：
根据这些记录里的主键值到聚簇索引中做回表操作，所需的 I/O 成本就是：
58 x 1.0 = 58.0
回表操作后得到的完整用户记录，然后再比较其他搜索条件是否成立
此步骤对应的 CPU 成本就是：
58 x 0.2 = 11.6
所以本例中使用 idx_order_no 执行查询的成本就如下所示：

• I/O 成本：
  3.0 + 58 x 1.0 = 61.0 (范围区间的数量 + 预估的二级索引记录条数)
• CPU 成本：
  58 x 0.2 + 58 x 0.2 + 0.01 = 23.21 （读取二级索引记录的成本 + 读取并检测
  回表后聚簇索引记录的成本）
• 综上所述，使用 idx_order_no 执行查询的总成本就是：
  61.0 + 23.21 = 84.21
  是否有可能使用索引合并（Index Merge）
  本例中有关 order_no 和 expire_time 的搜索条件是使用 AND 连接起来的，而
  对于 idx_order_no 和 idx_expire_time 都是范围查询，也就是说查找到的二级索引
  记录并不是按照主键值进行排序的，并不满足使用 Intersection 索引合并的条件，
  所以并不会使用索引合并。而且 MySQL 查询优化器计算索引合并成本的算法也
  比较麻烦，所以我们也不会细说。
  4. 对比各种方案，找出成本最低的那一个
  下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：
  全表扫描的成本：2148.7
  使用 idx_expire_time 的成本：55.61
  使用 idx_order_no 的成本：84.21
  很显然，使用 idx_expire_time 的成本最低，所以当然选择 idx_expire_time
  来执行查询。

请注意：1、MySQL 的源码中对成本的计算实际要更复杂，但是基本思想和
算法是没错的。
2、在 MySQL 的实际计算中，在和全文扫描比较成本时，使用索引的成本会
去除读取并检测回表后聚簇索引记录的成本，也就是说，我们通过 MySQL 看到
的成本将会是：idx_expire_time 为 47.81(55.61-7.8)，idx_order_no 为
72.61(84.21-11.6)。但是 MySQL 比较完成本后，会再计算一次使用索引的成本，
此时就会加上去除读取并检测回表后聚簇索引记录的成本，也就是我们计算出来
的值。

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

index dive
有时候使用索引执行查询时会有许多单点区间，比如使用 IN 语句就很容易
产生非常多的单点区间，比如下边这个查询（下边查询语句中的…表示还有很多
参数）：

SELECT * FROM order_exp WHERE order_no IN ('aa1', 'aa2', 'aa3', ... , 'zzz');
1

很显然，这个查询可能使用到的索引就是 idx_order_no，由于这个索引并不
是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多
少，需要我们去计算。就是先获取索引对应的 B+树的区间最左记录和区间最右
记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确
计算，多的时候只能估算）。MySQL 把这种通过直接访问索引对应的 B+树来计
算某个范围区间对应的索引记录条数的方式称之为 index dive。

有零星几个单点区间的话，使用 index dive 的方式去计算这些单点区间对应
的记录数也不是什么问题，如果 IN 语句里 20000 个参数怎么办？

这就意味着 MySQL 的查询优化器为了计算这些单点区间对应的索引记录条
数，要进行 20000 次 index dive 操作，这性能损耗就很大，搞不好计算这些单点
区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL 考虑到了
这种情况，所以提供了一个系统变量 eq_range_index_dive_limit，我们看一下在
MySQL 5.7.21 中这个系统变量的默认值：

show variables like '%dive%';
1

也就是说如果我们的 IN 语句中的参数个数小于 200 个的话，将使用 index
dive 的方式计算各个单点区间对应的记录条数，如果大于或等于 200 个的话，可
就不能使用 index dive 了，要使用所谓的索引统计数据来进行估算。怎么个估算
法？
像会为每个表维护一份统计数据一样，MySQL 也会为表中的每一个索引维护
一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名
的语法，比如我们查看一下 order_exp 的各个索引的统计数据可以这么写：

show index from order_exp;
1

属性名 描述

• Table 索引所属表的名称。
• Non_unique 索引列的值是否是唯一的，聚簇索引和唯一二级索引的该列
  值为 0，普通二级索引该列值为 1。
• Key_name 索引的名称。
• Seq_in_index 索引列在索引中的位置，从 1 开始计数。比如对于联合索引
  u_idx_day_status，来说，insert_time, order_status, expire_time对应的位置分
  别是 1、2、3。
• Column_name 索引列的名称。
  Collation 索引列中的值是按照何种排序方式存放的，值为 A 时代表升序存
  放，为 NULL 时代表降序存放。
• Cardinality 索引列中不重复值的数量。后边我们会重点看这个属性的。
• Sub_part 对于存储字符串或者字节串的列来说，有时候我们只想对这些串
  的前 n 个字符或字节建立索引，这个属性表示的就是那个 n 值。如果对完整的列
  建立索引的话，该属性的值就是 NULL。
• Packed 索引列如何被压缩，NULL 值表示未被压缩。这个属性我们暂时不了
  解，可以先忽略掉。
• Null 该索引列是否允许存储 NULL 值。
  Index_type 使用索引的类型，我们最常见的就是 BTREE，其实也就是 B+树索
  引。
• Comment 索引列注释信息。
• Index_comment索引注释信息。
• Cardinality 属性，Cardinality 直译过来就是基数的意思，表示索引列中不重
  复值的个数。比如对于一个一万行记录的表来说，某个索引列的 Cardinality 属性
  是 10000，那意味着该列中没有重复的值，如果 Cardinality 属性是 1 的话，就意
  味着该列的值全部是重复的。不过需要注意的是，对于 InnoDB 存储引擎来说，
  使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值，
  并不是精确的。
  前边说道，当 IN 语句中的参数个数大于或等于系统变量
  eq_range_index_dive_limit 的值的话，就不会使用 index dive 的方式计算各个单点
  区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指
  的是这两个值：

使用 SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。
使用 SHOW INDEX 语句展示出的 Cardinality 属性。
结合上一个 Rows 统计数据，我们可以针对索引列，计算出平均一个值重复
多少次。
一个值的重复次数 ≈ Rows ÷ Cardinality
以 order_exp 表的 idx_order_no 索引为例，它的 Rows 值是 10350，它对应
的 Cardinality 值是 10220，所以我们可以计算 order_no 列平均单个值的重复次数
就是：10350÷ 10220≈ 1.012（条）
此时再看上边那条查询语句：

SELECT * FROM order_exp WHERE order_no IN ('aa1', 'aa2', 'aa3', ... , 'zzz');
1

假设 IN 语句中有 20000 个参数的话，就直接使用统计数据来估算这些参数
需要单点区间对应的记录条数了，每个参数大约对应 1.012 条记录，所以总共需
要回表的记录数就是：
20000 x 1.012= 21,730
使用统计数据来计算单点区间对应的索引记录条数比 index dive 的方式简单，
但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需
的成本可能相差非常大。
大家需要注意一下，在 MySQL 5.7.3 以及之前的版本中，
eq_range_index_dive_limit 的默认值为 10，之后的版本默认值为 200。所以如果
大家采用的是 5.7.3 以及之前的版本的话，很容易采用索引统计数据而不是 index
dive 的方式来计算查询成本。当你的查询中使用到了 IN 查询，但是却实际没有
用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的