MySQL优化器查询成本计算

m0_47704296

已于 2022-12-20 16:35:02 修改

阅读量282

点赞数

分类专栏： mysql 文章标签： mysql 数据库

于 2022-12-20 16:30:43 首次发布

本文链接：https://blog.csdn.net/m0_47704296/article/details/128386724

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文深入探讨了数据库查询优化中的成本计算，包括I/O成本和CPU成本。介绍了InnoDB存储引擎中页的成本，全表扫描的代价计算以及如何评估不同索引的查询代价。通过示例详细解析了单表查询、使用不同索引执行查询的代价，并讨论了连接查询的成本分析，强调了优化驱动表扇出和被驱动表访问成本的重要性。

摘要由CSDN通过智能技术生成

什么是成本

I/O成本

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数。

单表查询的成本

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个

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

计算全表扫描的代价

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

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

MySQL为每个表维护了一系列的统计信息。SHOW TABLE STATUS语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的LIKE语句，例如：

mysql> SHOW TABLE STATUS LIKE 'single_table'\G
*************************** 1. row ***************************
           Name: single_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9693
 Avg_row_length: 163
    Data_length: 1589248
Max_data_length: 0
   Index_length: 2752512
      Data_free: 4194304
 Auto_increment: 10001
    Create_time: 2018-12-10 13:37:23
    Update_time: 2018-12-10 13:38:03
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

虽然出现了很多统计选项，但我们目前只关心两个：

Rows

表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的single_table表是使用InnoDB存储引擎的，所以虽然实际上表中有10000条记录，但是SHOW TABLE STATUS显示的Rows值只有9693条记录。
Data_length

本选项表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
```
Data_length = 聚簇索引的页面数量 x 每个页面的大小
```
我们的single_table使用默认16KB的页面大小，而上边查询结果显示Data_length的值是1589248，所以我们可以反向来推导出聚簇索引的页面数量：
```
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
```

现在可以看一下全表扫描成本的计算过程：

I/O成本
```
97 x 1.0 + 1.1 = 98.1
```
97指的是聚簇索引占用的页面数，1.0指的是加载一个页面的成本常数，后边的1.1是一个微调值，我们不用在意。
CPU成本：
```
9693 x 0.2 + 1.0 = 1939.6
```
9693指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.2指的是访问一条记录所需的成本常数，后边的1.0是一个微调值，我们不用在意。
总成本：
```
98.1 + 1939.6 = 2037.7
```

综上所述，对于single_table的全表扫描所需的总成本就是2037.7。

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

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

SELECT * FROM single_table WHERE 
    key1 IN ('a', 'b', 'c') AND 
    key2 > 10 AND key2 < 1000 AND 
    key3 > key2 AND 
    key_part1 LIKE '%hello%' AND
    common_field = '123';

从第1步分析我们得到，上述查询可能使用到idx_key1和idx_key2这两个索引

使用idx_key2执行查询的成本分析

使用idx_key2搜索的示意图就是这样子：

在这里插入图片描述

对于使用二级索引 + 回表方式的查询：

范围区间数量

不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的（因为可以准确定位到最小值的叶子节点，利用双向链表的特性向后遍历即可，所以只有一次随机IO）。本例中使用idx_key2的范围区间只有一个：(10, 1000)，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：
```
1 x 1.0 = 1.0
```
需要回表的记录数

优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算idx_key2在(10, 1000)这个范围区间中包含多少二级索引记录，计算过程是这样的：
- 步骤1：先根据key2 > 10这个条件访问一下idx_key2对应的B+树索引，找到满足key2 > 10这个条件的第一条记录，这个搜索过程是常数级别的，性能消耗是可以忽略不计的。
- 步骤2：然后再根据key2 < 1000这个条件继续从idx_key2对应的B+树索引中找出最后一条满足这个条件的记录，我们把这条记录称之为区间最右记录，同步骤1。
- 步骤3：如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.21这个版本里，只要相隔不大于10个页面即可），那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到B+树索引的结构中来：

在这里插入图片描述

    > 如图，我们通过`区间最左记录`和`区间最右记录`向上递归查找父节点**两个目录项记录相隔的页数**

根据上述算法测得idx_key2在区间(10, 1000)之间大约有95条记录。读取这95条二级索引记录需要付出的CPU成本（需要判断二级索引是否符合搜索条件——10<x<1000）就是：
```
95 x 0.2 + 0.01 = 19.01
```
其中95是需要读取的二级索引记录条数，0.2是读取一条记录成本常数，0.01是微调。
根据这些记录里的主键值到聚簇索引中做回表操作，预计有95条二级索引记录需要进行回表操作，所以回表操作带来的I/O成本就是：
```
95 x 1.0 = 95.0
```
其中95是预计的二级索引记录数，1.0是一个页面的I/O成本常数。
回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立，

回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除key2 > 10 AND key2 < 1000这个搜索条件以外的搜索条件是否成立。

这个比较过程花费的CPU成本就是：
```
95 x 0.2 = 19.0
```

所以本例中使用idx_key2执行查询的成本就如下所示：

I/O成本：

1.0 + 95 x 1.0 = 96.0 (范围区间的数量 + 预估的二级索引记录条数)

CPU成本：

95 x 0.2 + 0.01 + 95 x 0.2 = 38.01 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用idx_key2执行查询的总成本就是：

96.0 + 38.01 = 134.01

使用idx_key1执行查询的成本分析，同上

使用idx_key1的成本：168.21

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

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

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

SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

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

有零星几个单点区间的话，使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题，可是你架不住有的孩子憋足了劲往IN语句里塞东西呀，我就见过有的同学写的IN语句里有20000个参数的🤣🤣，这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index dive操作，这性能损耗可就大了，搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大了。

所以mysql提供了一个系统变量eq_range_index_dive_limit，我们看一下在MySQL 5.7.21中这个系统变量的默认值：

在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200。

mysql> SHOW VARIABLES LIKE '%dive%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.08 sec)

也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive

连接查询的成本

Condition filtering介绍

MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值。

在这两种情况下计算驱动表扇出值时需要靠猜：

如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

这个猜的过程称之为condition filtering。

两表连接的成本分析

连接查询的成本计算公式是这样的：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要：

分别为驱动表和被驱动表选择成本最低的访问方法。

可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
然后分别为驱动表和被驱动表选择成本最低的访问方法。

比如对于下边这个查询来说：带key都是索引列

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
    ON s1.key1 = s2.common_field 
    WHERE s1.key2 > 10 AND s1.key2 < 1000 AND 
          s2.key2 > 1000 AND s2.key2 < 2000;

可以选择的连接顺序有两种：

s1连接s2，也就是s1作为驱动表，s2作为被驱动表。
s2连接s1，也就是s2作为驱动表，s1作为被驱动表。

查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划：

使用s1作为驱动表的情况
- 分析对于驱动表的成本最低的执行方案
  
  首先看一下涉及s1表单表的搜索条件有哪些：
  - s1.key2 > 10 AND s1.key2 < 1000
  所以这个查询可能使用到idx_key2索引，从全表扫描和使用idx_key2这两个方案中选出成本最低的那个，很显然使用idx_key2执行查询的成本更低些。
- 然后分析对于被驱动表的成本最低的执行方案
  
  此时涉及被驱动表s2的搜索条件就是：
  - s2.common_field = 常数（这是因为对驱动表s1结果集中的每一条记录，都需要进行一次被驱动表s2的访问）
  - s2.key2 > 1000 AND s2.key2 < 2000
  很显然，第一个条件由于common_field没有用到索引，所以并没有什么卵用，此时访问s2表时可用的方案也是全表扫描和使用idx_key2两种，假设使用idx_key2的成本更小。
所以此时使用s1作为驱动表时的总成本就是（暂时不考虑使用join buffer（对驱动表批量的取出查询结果到内存中，一次性的和被连接表数据做匹配筛选，详情见：连接查询原理）对成本的影响）：
```
使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本
```
使用s2作为驱动表的情况
- 分析对于驱动表的成本最低的执行方案
  
  首先看一下涉及s2表单表的搜索条件有哪些：
  - s2.key2 > 1000 AND s2.key2 < 2000
  所以这个查询可能使用到idx_key2索引，从全表扫描和使用idx_key2这两个方案中选出成本最低的那个，假设使用idx_key2执行查询的成本更低些。
- 然后分析对于被驱动表的成本最低的执行方案
  
  此时涉及被驱动表s1的搜索条件就是：
  - s1.key1 = 常数
  - s1.key2 > 10 AND s1.key2 < 2000
  这时就很有趣了，使用idx_key1可以进行ref方式的访问，使用idx_key2可以使用range方式的访问。这是优化器需要从全表扫描、使用idx_key1、使用idx_key2这几个方案里选出一个成本最低的方案。这里有个问题啊，因为idx_key2的范围区间是确定的：(10, 1000)，怎么计算使用idx_key2的成本我们上边已经说过了，可是在没有真正执行查询前，s1.key1 = 常数中的常数值我们是不知道的，怎么衡量使用idx_key1执行查询的成本呢？其实很简单，直接使用索引统计数据就好了（就是索引列平均一个值重复多少次）。一般情况下，ref的访问方式要比range成本更低，这里假设使用idx_key1进行对s1的访问。
所以此时使用s2作为驱动表时的总成本就是：
```
使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本
```

最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。从上边的计算过程也可以看出来，连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：

尽量减少驱动表的扇出
对被驱动表的访问成本尽量低

这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。