MySQL 的底层执行原理2
内容概述
MySQL 的底层执行原理
# [1] MySQL 的成本计算
# [1.1] MySQL 的成本是什么？
MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。 不过我们之前对成本的描述是非常模糊的，其实在MySQL 中一条查询语句的执行成本是由下边这两个方面组成的：
I/O 成本
我们的表经常使用的MyISAM、InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。 这个从磁盘到内存这个加载的过程损耗的时间称之为I/O 成本。
CPU 成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU 成本。
对于InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 规定读取一个页面花费的成本默认是1.0， 读取以及检测一条记录是否符合搜索条件的成本默认是0.2。 1.0、0.2 这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。
需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。
# [1.2] 单表查询的成本
# 基于成本的优化步骤实战
在一条单表查询语句真正执行之前，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;
2
3
4
5
6
7
8
根据搜索条件，找出所有可能使用的索引
我们前边说过，对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、 !=（不等于也可以写成<>）或者LIKE 操作符连接起来，就可以产生一个所谓的范围区间（LIKE 匹配字符串前缀也行）， MySQL 把一个查询中可能使用到的索引称之为possible keys。
我们分析一下上边查询中涉及到的几个搜索条件：
（1）、order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') ，这个搜索条件可以使用二级索引idx_order_no。
（2）、expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09'，这个搜索条件可以使用二级索引idx_expire_time。
（3）、insert_time> expire_time，这个搜索条件的索引列由于没有和常数比较，所以并不能使用到索引。
（4）、order_note LIKE '%hello%'，order_note 即使有索引，但是通过LIKE 操作符和以通配符开头的字符串做比较，不可以适用索引。
（5）、order_status = 0，由于该列上只有联合索引，而且不符合最左前缀原则，所以不会用到索引。
综上所述，上边的查询语句可能用到的索引，也就是possible keys 只有idx_order_no,idx_expire_time。
计算全表扫描的代价
对于InnoDB 存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集， 所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O 成本+CPU 成本，所以计算全表扫描的代价需要两个信息：
（1）、聚簇索引占用的页面数
（2）、该表中的记录数
这两个信息从哪来呢？MySQL 为每个表维护了一系列的统计信息，关于这些统计信息是如何收集起来的我们放在后边再说，现在看看怎么查看这些统计信息。
MySQL 给我们提供了SHOW TABLE STATUS 语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的LIKE 语句就好了， 比方说我们要查看order_exp 这个表的统计信息可以这么写：
SHOW
TABLE STATUS LIKE 'order_exp'
\G
2
3
我们可以看到出现了很多统计选项，但我们目前只需要两个：
（1）、Rows
本选项表示表中的记录条数。对于使用MyISAM 存储引擎的表来说，该值是准确的，对于使用InnoDB 存储引擎的表来说，该值是一个估计值。
从查询结果我们也可以看出来，由于我们的order_exp 表是使用InnoDB 存储引擎的，所以虽然实际上表中有10567 条记录，
但是SHOW TABLE STATUS 显示的Rows 值只有10350 条记录。
（1）、Data_length
本选项表示表占用的存储空间字节数。使用MyISAM 存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB 存储引擎的表来说，
该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：**Data_length = 聚簇索引的页面数量x 每个页面的大小。
**
我们的order_exp 使用默认16KB 的页面大小，而上边查询结果显示Data_length 的值是1589248，所以我们可以反向来推导出聚簇索引的页面数量：
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。
下面，我们一起来看一下全表扫描成本的计算过程：
（1）、I/O 成本
97 x 1.0 + 1.1 = 98.1
97 指的是聚簇索引占用的页面数，1.0 指的是加载一个页面的成本常数，后边的1.1 是一个微调值。
TIPS：MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们分析。
（2）、CPU 成本
10350x 0.2 + 1.0 = 2071
10350 指的是统计数据中表的记录数，对于InnoDB 存储引擎来说是一个估计值，0.2 指的是访问一条记录所需的成本常数，后边的1.0
是一个微调值。
（3）、总成本
98.1 + 2071 = 2169.1
综上所述，对于order_exp 的全表扫描所需的总成本就是2169.1。
TIPS
我们前边说过表中的记录其实都存储在聚簇索引对应 B+ 树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点， 就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。
也就是说全表扫描这个过程其实有的 B+ 树非叶子节点是不需要访问的，但是MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算I/O 成本的依据， 是不区分非叶子节点和叶子节点的。
- 计算使用不同索引执行查询的代价
从第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 语句里提取出来？前面章节《深入思考索引在查询中的使用》中有详细讲解。
使用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+ 树索引中找出最后一条满足这个条件的记录，
我们把这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。