- I/O 成本
- CPU 成本
规定:
- 读一个页面成本为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;
基于成本的优化步骤
- 根据搜索条件，找出所有可能使用的索引
- 计算全表扫描的代价
- 计算使用不同索引执行查询的代价
- 对比各种执行方案的代价，找出成本最低的那一个
实例分析
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. 根据搜索条件，找出所有可能使用的索引
对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的范围区间（LIKE匹配字符串前缀也行），也就是说这些搜索条件都可能使用到索引，设计MySQL的大叔把一个查询中可能使用到的索引称之为possible keys。
上边的查询语句可能用到的索引，也就是possible keys只有idx_key1和idx_key2。
2. 计算全表扫描的代价
查询成本=I/O成本+CPU成本
全表扫描的代价需要两个信息：
- 聚簇索引占用的页面数
- 该表中的记录数
查看某个表的统计信息:
- Rows: 表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。
- Data_length: 表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小
Data_length = 聚簇索引的页面数量 x 每个页面的大小聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
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 # Rows
Avg_row_length: 163
Data_length: 1589248 # Data_length
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)
成本计算:
- I/O 成本
# 页面数 成本常数 微调值
97 x 1.0 + 1.1 = 98.1
- CPU 成本
#记录数 成本常数 微调值
9693 x 0.2 + 1.0 = 1939.6
- 总成本
98.1 + 1939.6 = 2037.7
3. 计算使用不同索引执行查询的代价
MySQL 分析索引的顺序:
- 唯一二级索引
- 普通索引
使用idx_key2执行查询的成本分析
idx_key2对应的搜索条件是：key2 > 10 AND key2 < 1000，也就是说对应的范围区间就是：(10, 1000)，使用idx_key2搜索的示意图就是这样子：
对于使用二级索引 + 回表方式的查询，设计MySQL的大叔计算这种查询的成本依赖两个方面的数据：
- 范围区间数量
不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用idx_key2的范围区间只有一个：(10, 1000)，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：
1 x 1.0 = 1.0
- 需要回表的记录数
需要知道:
- 区间最左记录和区间最右记录之间的页面数
- 平均页面内记录条数
- 在MySQL 5.7.21这个版本里，只要相隔不大于10个页面即可，那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数
- 否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以页面数量
如何获取页面数量?
- 通过上级目录项个数
- 如果目录项页面数量过多可以继续向上递归
CPU 成本:
# 记录数 成本常数 微调
95 x 0.2 + 0.01 = 19.01
95咋来的没说.
- 回表成本估算
设计MySQL的大叔评估回表操作的I/O成本依旧很豪放，他们认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。
# 记录数 成本常数
95 x 1.0 = 95.0
- 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立
全表扫描好像忽略了搜索条件, 所有搜索条件都是扫描一行. 其他搜索条件相当于一个搜索条件.
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 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）
- 总成本
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
需要回表的记录数
- [‘a’, ‘a’]: 35
- [‘b’, ‘b’]: 44
- [‘c’, ‘c’]: 39
CPU 成本:
35 + 44 + 39 = 118
# 记录数 成本常数 微调
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 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）
- 总成本
121.0 + 47.21 = 168.21
是否有可能使用索引合并（Index Merge）
本例中有关key1和key2的搜索条件是使用AND连接起来的，而对于idx_key1和idx_key2都是范围查询，也就是说查找到的二级索引记录并不是按照主键值进行排序的，并不满足使用Intersection索引合并的条件，所以并不会使用索引合并。
4. 对比各种执行方案的代价，找出成本最低的那一个
- 全表扫描的成本：2037.7
- 使用idx_key2的成本：134.01
- 使用idx_key1的成本：168.21
若有收获，就点个赞吧
0 人点赞
