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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

单表查询成本

基于成本的优化步骤

在一条单表查询语句真正执行之前，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
2
3
4
5
6

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
1
2
3
4
5
6
7
8
9

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
1
2
3
4
5
6
7
8
9
10

使用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
1
2
3
4
5
6
7
8
9
10
11
12

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');
1
2

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

小贴士：

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

有零星几个单点区间的话，使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题，可是你架不住有的孩子憋足了劲往IN语句里塞东西呀，我就见过有的同学写的IN语句里有20000个参数的