MYSQL基于成本的优化

什么是成本

• I/O成本
  我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
• CPU成本
  读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

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

单表查询的成本

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;

基于成本的优化步骤

//eg
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. 根据搜索条件，找出所有可能使用的索引

上边的查询语句可能用到的索引，也就是possible keys只有idx_key1和idx_key2。

2. 计算全表扫描的代价

由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：

• 聚簇索引占用的页面数

• 该表中的记录数

mysql> USE xiaohaizi;
Database changed

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:

• Rows
  对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。
• Data_length
  Data_length = 聚簇索引的页面数量 x 每个页面的大小(16*1024)

聚簇索引的页面数量 = 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是一个微调值，我们不用在意。

• CPU成本：

98.1 + 1939.6 = 2037.7
//9693指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.2指的是访问一条记录所需的成本常数，后边的1.0是一个微调值，我们不用在意。

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

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

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

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

• 需要回表的记录数

  • 步骤1：找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录
  • 步骤2：找到满足key2 < 1000这个条件的最后一条记录，我们把这条记录称之为区间最右记录
  • 步骤3：找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录
    

  idx_key2在区间(10, 1000)之间大约有95条记录。读取这95条二级索引记录需要付出的CPU成本就是：
  95 x 0.2 + 0.01 = 19.01
  回表操作带来的I/O成本就是：
  95 x 1.0 = 95.0
  回表操作带来的CPU成本就是：
  95 x 0.2 = 19.0

综上所述，使用idx_key2执行查询的总成本就是：
96.0 + 38.01 = 134.01

使用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来执行查询喽。

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

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

获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录。通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。
对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的。

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

成本=一个值的重复次数*（IN语句中数量）

连接查询的成本

Condition filtering介绍

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

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

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

//eg
SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
    WHERE s1.common_field > 'xyz';

查询优化器又不会真正的去执行查询，所以它只能猜这9693记录里有多少条记录满足common_field > 'xyz’条件。

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

两表连接的成本分析

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

多表连接的成本分析

调节成本常数

我们前边已经介绍了两个成本常数：

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

mysql.server_cost表

成本常数名称	默认值	描述
disk_temptable_create_cost	40.0	创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
disk_temptable_row_cost	1.0	向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
key_compare_cost	0.1	两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升filesort的成本，让优化器可能更倾向于使用索引完成排序而不是filesort
memory_temptable_create_cost	2.0	创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
memory_temptable_row_cost	0.2	向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
row_evaluate_cost	0.2	这个就是我们之前一直使用的检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。

mysql.engine_cost表

成本常数名称	默认值	描述
io_block_read_cost	1.0	从磁盘上读取一个块对应的成本。请注意我使用的是块，而不是页这个词儿。对于InnoDB存储引擎来说，一个页就是一个块，不过对于MyISAM存储引擎来说，默认是以4096字节作为一个块的。增大这个值会加重I/O成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。
memory_block_read_cost	1.0	与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。