MySQL优化器特性(九)行数评估

俊达2年前技术文章1294

查询的行数在成本计算中起了很重要的作用:

1、row_evaluate_cost和行数直接相关

2、需要访问多少索引页面,和行数直接相关。根据页面大小和平均索引条目长度计算每个索引页面的记录数,根据记录数计算索引页面数。

3、需要回表访问多少表的页面,和行数直接相关。mysql认为range访问时,每一行记录都需要访问一个数据页面。

4、主键范围扫描页面数,和行数直接相关。


那么如何精确的评估查询需要访问的记录数呢?

InnoDB中有一个index dive的特性,可以根据range区间范围,直接到索引中统计满足条件的记录数。


index dive

index dive统计一个索引区间范围内记录数的逻辑大致如下(btr_estimate_n_rows_in_range):

1、找到范围的下界

2、扫描数据,直到范围的上界,或者扫描的页面数超出限制(10个页面?)

3、如果范围内的页面数不多,则可以精确统计出需要访问的记录数。

4、如果范围内的页面数超过扫描限制,则根据已经扫描的页面,计算平均每页记录数,乘以页面数量,得到一个预估的纪录数。







页面的数量可以通过统计上一层branch页面中的索引条目数得到。根据索引结构的特征,索引页面中的一行记录指向下一个层级中的一个page,根据索引页面中的记录数,就可以得到下一个层级中相应的页面数:


如果一个SQL中涉及到的range的数量特别多(比如in列表中传入了大量数据),则统计每一range的记录数就会消耗大量成本,mysql有一个参数eq_range_index_dive_limit,用于限制优化器进行index dive的开销。如果一个SQL中的range数超过了eq_range_index_dive_limit,优化器就不会通过index dive来评估记录数,转而使用表和索引的统计信息,或者使用经验公式来评估记录数。

mysql 8.0中,eq_range_index_dive_limit默认值为200。



相关文章

MySQL运维实战(5.4) MySQL元数据乱码

表结构Comment乱码如果DDL实际编码和character_set_client设置不一致,也会引起乱码。$ cat test_comment_utf8.sql create...

MySQL运维实战(6)用户认证插件caching_sha2_password

MySQL用户认证可以使用几种不同的方式,创建用户时可以制定认证方式:create user 'username'@'%' identif...

MySQL优化器特性(五)单表访问路径

数据库的访问路径(access path)是指从表中获取数据的方式,一般可以通过扫描表或通过索引获取数据。想熟练掌握SQL优化技能,首先需要熟悉单表访问路径。本文先简单介绍MySQL支持的各种单表访问...

MySQL优化器特性(六)表扫描成本计算

全表扫描成本使用optimizer_trace,或者使用explain format=tree, 或者explain format=json,可以查看查询的costmysql> exp...

MySQL运维实战(7.1) 开启GTID复制

MySQL从5.6版本开始支持GTID复制。开启GTID之后,主库上执行的每一个事务都有一个全局唯一的ID。GTID由两部分组成:server_uuid和事务序列号。初始化数据库时,会生成一个全局唯一...

MySQL运维实战之备份和恢复(8.8)恢复单表

xtrabackup支持单表恢复。如果一个表使用了独立表空间(innodb_file_per_table=1),就可以单独恢复这个表。1、Prepareprepare时带上参数--export,xtr...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。