mysql - 查询成本 - 优化器

查询成本

我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：

• I/O成本

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

• CPU成本

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

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大叔规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0

我们还是选创建一个表，并插入一万条数据

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

单表查询成本

基于成本的优化步骤

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

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

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

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，可以使用到的索引称为possible keys

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

• 聚簇索引占用的页面数（I/O成本）
• 该表中的记录数（CPU成本）

我们可以通过 SHOW TABLE STATUS语句来查询single_table表的统计信息，TABLE STATUS中记录了表的行数（ROWS）和表占用的存储空间字节数（Data_length）

页面数为 Data_length/每个页面占用的字节数

 --  Rows: 9693（不是精确值；计算方式：按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。）， Data_length: 1589248
 -- 页面数，一个页面占用16kb（这个其实不精确，全表扫描只会用到内节点的最左节点，然后遍历全部叶节点）
 1589248 ÷ 16 ÷ 1024 = 97
 -- i/o成本，1.1为偏差常数
 97 x 1.0 + 1.1 = 98.1
 -- cpu成本，1.0为偏差常数
 9693 x 0.2 + 1.0 = 1939.6
 -- 总成本
 98.1 + 1939.6 = 2037.7

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

idx_key2执行查询的成本分析

-- 查询二级索引I/O成本，一个范围查询默认一个页面读取
1 x 1.0 = 1.0
-- 范围查询中有95条数据，该数据是通过范围边界计算得到的（或通过父节点的目录项记录计算），0.01是偏差常数
95 x 0.2 + 0.01 = 19.01
-- 回表的I/O成本
95 x 1.0 = 95.0
-- 回表后要将筛选
95 x 0.2 = 19.0
-- 总成本
96.0 + 38.01 = 134.01

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

idx_key1对应的搜索条件是：key1 IN ('a', 'b', 'c')，也就是说相当于3个单点区间：

• ['a', 'a']
• ['b', 'b']
• ['c', 'c']

-- 一个范围查询等于一个I/O操作
3 x 1.0 = 3.0
-- 范围查询中的数据数
35 + 44 + 39 = 118
-- 读取符合的数据，cup成本
118 x 0.2 + 0.01 = 23.61
-- 回表
118 x 1.0 = 118.0
-- 回表后判断
118 x 0.2 = 23.6
-- 总和
3.0 + 118 x 1.0 + 118 x 0.2 + 0.01 + 118 x 0.2 = 168.21

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

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：

• 全表扫描的成本：2037.7
• 使用idx_key2的成本：134.01
• 使用idx_key1的成本：168.21

很显然，使用idx_key2的成本最低，所以当然选择idx_key2来执行查询喽。

单点范围查询中的成本计算

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

sql
复制代码SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

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

小贴士：

dive直译为中文的意思是跳水、俯冲的意思，原谅我的英文水平捉急，我实在不知道怎么翻译 index dive，索引跳水？索引俯冲？好像都不太合适，所以压根儿就不翻译了。不过大家要意会index dive就是直接利用索引对应的B+树来计算某个范围区间对应的记录条数。

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

索引统计数据就是通过索引的行数/索引基数（索引基数为索引中的不重复值，若基数为1这索引中的值都是一样的），这样可以获得一个单点范围查询的大概数量。

若索引的行数为10000，索引基数1000，则相同索引有10条重复的，单点范围就是10

多表查询成本

准备工作

连接查询至少是要有两个表的，只有一个single_table表是不够的，所以为了故事的顺利发展，我们直接构造一个和single_table表一模一样的single_table2表。为了简便起见，我们把single_table表称为s1表，把single_table2表称为s2表。

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

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

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）

计算公式

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

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

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

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

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

很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。

比如对于下边这个查询来说：

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作为驱动表，s2作为被驱动表

• 分析对于驱动表的成本最低的执行方案

  首先看一下涉及s1表单表的搜索条件有哪些：

  • s1.key2 > 10 AND s1.key2 < 1000

  所以这个查询可能使用到idx_key2索引，从全表扫描和使用idx_key2这两个方案中选出成本最低的那个，这个过程我们上边都唠叨过了，很显然使用idx_key2执行查询的成本更低些。

• 然后分析对于被驱动表的成本最低的执行方案

  此时涉及被驱动表s2的搜索条件就是：

  • s2.common_field = 常数（这是因为对驱动表s1结果集中的每一条记录，都需要进行一次被驱动表s2的访问，此时那些涉及两表的条件现在相当于只涉及被驱动表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作为驱动表，s1作为被驱动表

• 分析对于驱动表的成本最低的执行方案

  首先看一下涉及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这几个方案里选出一个成本最低的方案。一般情况下，ref的访问方式要比range成本更低，这里假设使用idx_key1进行对s1的访问。

所以此时使用s2作为驱动表时的总成本就是：

使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本

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

• 尽量减少驱动表的扇出
  成本更低，这里假设使用idx_key1进行对s1的访问。

所以此时使用s2作为驱动表时的总成本就是：

使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本

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

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