mysql——查询优化案例计算

mysql——查询优化案例计算

前言

上一篇博客说到查询优化器，但是并没有说查询成本是如何计算出来的，这一节我们就说如何计算成本

准备

有表takeout_goods,id是自增主键，shop_id是普通b+树索引，其他字段本案例用不着

语句如下：
语句1：explain select * from takeout_goods where shop_id = ‘7691005’
对于这条语句，查询优化器有两种走法，一种是去主键索引走全表扫描，另一种是走shop_id这个辅助索引，找到主键id集合后再走主键索引查出记录

语句2：explain select shop_id from takeout_goods where shop_id = ‘7691005’
语句2是为了让他利用覆盖索引，不用回表

成本分析

row_evaluate_cost：判读该行符合条件的代价，cpu代价
io_block_read_cost：磁盘读取一页的代价，io代价
查询成本 = 行数 * row_evaluate_cost+页数 * io_block_read_cost

row_evaluate_cost和io_block_read_cost这两个值很多人说是0.2和1，其实不然，各个版本可能不同，具体需要我们查mysql这个库的server_cost表和engine_cost表得知，如下图


这上面一切都是我自己的见解，我没有看见任何书有说这个，也是因为我用1和0.2去计算和查询优化器计算的相差甚远，所以在不经意间看到mysql库点进去看又不经意间看到了cost表，所以才去研究的，经过计算发现我的理论应该是正确的。我的mysql版本是8.0
row_evaluate_cost=0.1
io_block_read_cost=1

所以公式改为如下
查询成本 = 行数*0.1 + 页数 * 1

成本计算

对于这条语句 explain select * from takeout_goods where shop_id = ‘7691005’

全表扫描的成本

SHOW TABLE STATUS LIKE ‘takeout_goods’; 显示rows=560215行，数据大小为148570112b
show variables like ‘%innodb_page_size%’ 显示一页是16kb
行数已得知我们先计算页数，页数=148570112/1024/16 = 9068页

手动计算全表扫描成本 = 560215* 0.1 + 9068 * 1 = 65089.5
查询优化器计算出来的是61346（不会查看查询优化器的，请参考上篇博客查询优化器）
有一点不一样，但也正常。

走shop_id索引的成本

select count(*) from takeout_goods where shop_id = ‘7691005’ 查一下一共多少行，860行

560215行对应9068页，那么860对应的就是860/560215*9068 约等 14页，这样计算不太准，因为每一行的数据大小不一样，但没关系，不需要太精确

手动计算的走shop_id索引的成本 = 860*0.1 + 14 * 1 = 100

下面看看查询优化器的成本，一看吓一跳怎么是591.34，这误差就大了，是怎么回事呢？

原因在于shop_id是辅助索引，100只是走了辅助索引的代价，还需要通过辅助索引查出来的主键来回表查出*，这部分的代价没办法计算，但是可见这部分代价大致是591.34-100=491.34.

不信的话我们可以执行
explain select shop_id from takeout_goods where shop_id = ‘7691005’ 让他走覆盖索引，不回表，看看此时的代价，我查看查询优化器后发现是100.9 ,可以说是我们计算的已经非常精确了