在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样： 

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

1、根据搜索条件，找出所有可能使用的索引

分析得到 possible keys 只有 idx_order_no,idx_expire_time。

2、计算全表扫描的代价

对于 InnoDB 存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。
由于查询成本= I/O 成本+CPU 成本，所以计算全表扫描的代价需要两个信息：

  • 聚簇索引占用的页面数
  • 该表中的记录数

MySQL 给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息

  1. SHOW TABLE STATUS LIKE 'order_exp'

image.png
出现了很多统计选项，但我们目前只需要两个：

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 是一个微调值。

    CPU 成本

  • 10350x 0.2 + 1.0 = 2071

  • 10350 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估计值，0.2 指的是访问一条记录所需的成本常数，后边的 1.0 是一个微调值。 :::tips MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编 码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们分析。 :::

    总成本：

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

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

    我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。这里需要提一点的是，MySQL 查询优化器先分析使用唯一二级索引的成本

    一、二级索引 + 回表方式的查询

    比如搜索条件是：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’ )
    使用 idx_expire_time 搜索会使用用二级索引 + 回表方式的查询，MySQL 计算这种查询的成本依赖两个方面的数据：

    1、范围区间数量

    不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。本例中使用idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引付出的 I/O 成本就是：1 x 1.0 = 1.0

    2、需要回表的记录数

    优化器需要计算二级索引的某个范围区间到底包含多少条记录。
    步骤 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 条记录。
    image.png

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

    读取这 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 对应的搜索条件是：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’);

image.png
读取这些二级索引记录的 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

三、考虑索引合并

本例中有关 order_no 和 expire_time 的搜索条件是使用 AND 连接起来的，而对于 idx_order_no 和 idx_expire_time 都是范围查询，也就是说查找到的二级索引记录并不是按照主键值进行排序的，并不满足使用 Intersection 索引合并的条件， 所以并不会使用索引合并。

4、对比各种执行方案的代价，找出成本最低的那一个