一、什么是成本
MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。
1.1、MySQL中一条查询语句的执行成本是由下面这两个方面组成的
1.1.1、I/O成本
当
表使用MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本
1.1.2、CPU成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本
1.1.3、小结
对于
InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大佬规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数。需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2
二、计算单表查询的成本
2.1、数据准备-表 single_table
三、基于成本的优化步骤
3.1、查询SQL
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';
3.2、根据搜索条件，找出所有可能使用的索引
对于
B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的范围区间（LIKE匹配字符串前缀也行），也就是说这些搜索条件都可能使用到索引，设计MySQL的大佬把一个查询中可能使用到的索引称之为possible keys
-
key1 IN ('a', 'b', 'c')，这个搜索条件可以使用二级索引idx_key1 -
key2 > 10 AND key2 < 1000，这个搜索条件可以使用二级索引idx_key2 -
key3 > key2，这个搜索条件的索引列由于没有和常数比较，所以并不能使用到索引 -
key_part1 LIKE '%hello%'，key_part1通过LIKE操作符和以通配符开头的字符串做比较，不可以适用索引 -
common_field = '123'，由于该列上压根儿没有索引，所以不会用到索引
3.3、计算全表扫描的代价
3.3.1、查看统计
SHOW TABLE STATUS LIKE 'single_table';
3.3.2、Rows
对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的single_table表是使用InnoDB存储引擎的，所以虽然实际上表中有10000条记录，但是SHOW TABLE STATUS显示的Rows值只有9693条记录
3.3.3、Data_length
本选项表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小
Data_length = 聚簇索引的页面数量 x 每个页面的大小
- 聚簇索引的页面数量
聚簇索引的页面数量 = 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
3.3.4、总成本
98.1 + 1939.6 = 2037.7
3.3.5、小结
小贴士：我们前面说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程其实有的B+树内节点是不需要访问的，但是设计MySQL的大佬们在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算I/O成本的依据，是不区分内节点和叶子节点的，有点儿简单暴力。
网友评论