12-基于成本的优化

• 读取一个页面花费的成本默认是 1.0
• 读取以及检测一条记录是否符合搜索条件的成本默认是 0.2

12.2.2 基于成本的优化步骤

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';

一个查询中可能使用到的索引称之为possible keys

2、计算全表扫描的代价

全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。

查询成本 = I/O 成本 + CPU 成本

所以计算全表扫描的代价需要两个信息：

• 聚簇索引占用的页面数

• 该表中的记录数

可以从每个表的统计信息里找到这两个信息

mysql> SHOW TABLE STATUS LIKE 'single_table'\G
*************************** 1. row ***************************           
Rows: 9693 
Data_length: 1589248

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

• Data_length：本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小

Data_length = 聚簇索引的页面数量 x 每个页面的大小

这样就反向来推导出聚簇索引的页面数量=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

在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算I/O成本的依据，不区分内节点和叶子节点

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

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

idx_key2对应的搜索条件是：key2 > 10 AND key2 < 1000，也就是说对应的范围区间就是：(10, 1000)

对于使用二级索引+回表方式的查询，计算这种查询的成本依赖两个方面的数据

• 范围区间数量

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

• 需要回表的记录数

优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算idx_key2在(10, 1000)这个范围区间中包含多少二级索引记录

步骤1：先找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录
步骤2：然后再根据key2 < 1000这个条件找出第一条满足这个条件的记录，我们把这条记录称之为区间最右记录
步骤3：如果区间最左记录和区间最右记录相隔不大于10个页面，那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了

怎么知道两个页面之间相隔过少个页面呢？
假设区间最左记录在页b中，区间最右记录在页c中，那么我们想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页c之间有多少页面，而每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算它们父节点中对应的目录项记录之间隔着几条记录

根据上述算法测得idx_key2在区间(10, 1000)之间大约有 95 条记录。读取这 95 条二级索引记录需要付出的 CPU 成本就是：95 x 0.2 + 0.01 = 19.01
95 是需要读取的二级索引记录条数，0.2 是读取一条记录成本常数，0.01 是微调

认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面IO

上边统计了使用idx_key2二级索引执行查询时，预计有 95 条二级索引记录需要进行回表操作，所以回表操作带来的 I/O 成本就是：95 x 1.0 = 95.0
95 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数

回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除key2 > 10 AND key2 < 1000这个搜索条件以外的搜索条件是否成立。因为我们通过范围区间获取到二级索引记录共 95 条，也就对应着聚簇索引中 95 条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的 CPU 成本为：95 x 0.2 = 19.0，其中95是待检测记录的条数，0.2是检测一条记录是否符合给定的搜索条件的成本常数

所以本例中使用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

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

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

• 范围区间数量

使用idx_key1执行查询时很显然有3个单点区间，所以访问这3个范围区间的二级索引付出的I/O成本就是：3 x 1.0 = 3.0

• 需要回表的记录数
  • 查找单点区间 [‘a’, ‘a’] 对应的二级索引记录数：35
  • 查找单点区间 [‘b’, ‘b’] 对应的二级索引记录数：44
  • 查找单点区间 [‘b’, ‘b’] 对应的二级索引记录数：39

读取这些二级索引记录的 CPU 成本就是：118 x 0.2 + 0.01 = 23.61

根据这些记录里的主键值到聚簇索引中做回表操作所需的 I/O 成本就是：118 x 1.0 = 118.0
回表操作后得到的完整用户记录，然后再比较其他搜索条件是否成立，此步骤对应的 CPU 成本就是：118 x 0.2 = 23.6

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

• I/O 成本 = 3.0 + 118 x 1.0 = 121.0 (范围区间的数量 + 预估的二级索引记录条数)
• CPU 成本 = 118 x 0.2 + 0.01 + 118 x 0.2 = 47.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇 索引记录的成本）
• 综上所述，使用idx_key1执行查询的总成本 = 121.0 + 47.21 = 168.21

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

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

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

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

通过直接访问索引对应的 B+ 树来计算某个范围区间对应的索引记录条数的方式称之为index dive

如果单点扫描区间很多，index dive次数很多，也会有性能损耗

可以查看系统变量eq_range_index_dive_limit

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了

MySQL 也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法

• Cardinality表示索引列中不重复值的个数
• 对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值

当IN语句中的参数个数大于或等于系统变量eq_range_index_dive_limit的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据，指的是这两个值

• 使用SHOW TABLE STATUS展示出的Rows值，也就是一个表中有多少条记录

• 使用SHOW INDEX语句展示出的Cardinality属性

可以针对索引列，计算出平均一个值重复多少次

一个值的重复次数 ≈ Rows ÷ Cardinality

比如Rows值是 9693 ，它对应索引列key1的Cardinality值是 968 ，所以我们可以计算key1列平均单个值的重复次数就是：9693 ÷ 968 ≈ 10（条）

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

假设IN语句中有 20000 个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应 10 条记录，所以总共需要回表的记录数就是：20000 x 10 = 200000

12.3 连接查询的成本

12.3.2 条件过滤（Condition Filtering）

对于两表连接查询来说，它的查询成本由下边两个部分构成：

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

对驱动表进行查询后得到的记录条数称之为驱动表的 扇出（fanout）

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

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

12.3.3 两表连接的成本分析

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

优化重点其实是下边这两个部分：

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

• 尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低访问被驱动表的成本了
• 如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了

12.3.4 多表连接的成本分析

在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就不对该连接顺序继续往下分析了