Mysql内核查询成本计算_i/o成本

作者：Cpp五条 | 2024-04-24 04:33:11

踩

i/o成本

sql语句查询成本的组成

I/O成本：将数据从磁盘读到内存的成本。

CPU成本 ：数据读到内存后过滤、排序得到满足条件数据的成本。

总成本 =I/O成本+CPU成本

注意：对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。 1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。

查看mysql执行所有可能途径使用的成本

1. 开启trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭，具体方法参考：Mysql索引优化实战一_季风泯灭的季节的博客-CSDN博客

2. 使用explain查看，在explain后面加上 format=json 即可。

explain format=json SELECT  * from test5

单表查询的成本

计算成本的过程

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

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

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

全表扫描的代价

I/O成本=总页数*1.0+1.1

CPU成本 =总记录数*0.2+1.0

数据库表都维护了总记录数， InnoDB是一个估算的值，MYISAM是一个准确的值。通过SHOW TABLE STATUS 命令可以查到。总页数则用表的总大小➗16➗1024得到。

二级索引的代价

1. 使用二级索引的成本

如果是范围查询则算一次IO，因为它属于顺序IO。如果是精准查询则有几个就是几次IO，因为它是随机IO.顺序IO的效率大概是随机IO的40-400倍。如 in(1,3)就是两次IO。二级索引的成本如下：

I/O成本=待查找总页数（IO次数）*1.0

CPU成本 =待回表总记录数*0.2+0.01

2. 回表的代价

mysql需先计算出要回表的记录数，如果总记录数小于10页，则精准计算，如果大于10页，则以前10页的平均记录数X页数（页数从父节点的关键字数统计得出）进行估算。

I/O成本=待回表总记录数*1.0

~~CPU成本 =总记录数*0.2~~

使用二级索引时不用考虑回表的CPU成本。总成本等于上述三者之和。

通过上述比较我们可以联想到，当走索引需要回表的记录数接近或大于原表的总页数时，走索引回表造成的成本可能就比不走索引的成本要高，那么mysql优化器可能会选择全表扫描。

表关联查询的成本

MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

1. 单次查询驱动表的成本

2. 多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条

记录）

对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值。

如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条（不借助索引）。

如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条（借助索引先过滤）。

MySQL把这个猜的过程称之为 condition filtering 。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜。

两表连接的成本分析

连接查询的成本计算公式是这样的：

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

可以知道，要想优化表关联查询有两个方向：

1. 尽量减少驱动表的扇出，即以小表做驱动表。

2. 减少被驱动表的单次访问成本（因为扇出数存在，使得单次访问被驱动表），即尽可能的避免磁盘IO，去走索引，且让回表的记录数尽可能的少。

多表连接的成本

多表连接的成本与两表连接类似，只是后一个表以前面的sql查询结果为扇出去查询，其查询性能消耗为 x+ny+nz....,x、y、z为各表查询成本，n为扇出数。同时，mysql在sql分析阶段付出的成本是n！。因此，它的表越多性能越差。

where 和on 的区别

1. 执行时间不同，on先执行从各个表中过滤数据后进行关联，再使用where对结果进行进一步过滤。

2. on只对被驱动表有效。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/477619