MySQL 8.0 InnoDB 的统计信息机制优化

2024-01-04 13:13:40 浏览数 (1)

图片 from 通义万相

前言

ＭySQL 的InnoDB引擎会维护着用户表每个索引的统计信息，来帮助查询优化器选择最优的执行计划，详细的来说，key的分布情况能决定多表join的顺序，也能够决定查询使用哪一个索引。这些统计信息可以由专门的后台线程刷新，也可以由用户也可以显示的调用Analyze table的命令来刷新统计信息，本文基于最新的 MySQL 8.0 来具体分析一下刷新统计信息的具体实现。

统计信息收集触发以及查看

MySQL有多种方法会触发统计信息的收集，显示的最典型就是Analyze Table 语法，并且由于在MySQL 8.0 中支持了直方图统计信息， 因此analyze table 还扩充了Histogram语法

代码语言：javascript复制
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ...

执行Analyze table 的用户需要拥有表的 SELECT 和 INSERT 权限，由于Analyze table会更新数据字典里的统计信息表（8.0）因此在 innodb_read_only 开关被打开时有可能会导致执行失败。

在analyze table的过程中会持有InnoDB 表的 read only 锁， 因此会存在短暂的阻塞用户写入更新删除的操作。除此之外analyze table 要把table 从 table definition cache 刷出来， 因此还会需要一个flush lock， 此时如果有长事务使用了这张表， 那么必须等待长事务结束。

其次还有自动触发的场景， InnoDB的表在做rebuild index， add column， truncate等涉及数据修改的DDL时会需要收集正确的统计信息。

除此之外在后台有专门的线程，叫做dict_stats_thread来处理统计信息，InnoDB会长期追踪每一张表的行数:

  1. 判断条件是发现更新的记录超过表记录总数的1/10，那么就把这张表加入到后台的 recalc pool 中
  2. 如果变更的行数超过 16 n_rows/16（6.25%），更新非持久化统计信息 。

具体的统计信息可以通过以下语句观察到：

代码语言：javascript复制
SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

如果开启了InnoDB统计信息持久化，也可以通过查询 innodb_table_stats 和 innodb_index_stats 看到

其中stat_name 包括：

代码语言：javascript复制
n_diff_pfxNN （不同前缀列的cardinality）
n_leaf_page (索引叶子节点数目)
size (索引页面数目)

执行计划的相关变量

innodb_stats_persistent 控制统计信息是否持久化。统计信息在早期的MySQL中是不持久化的，在新版本的 MySQL 中该选项默认是持久化。当变量打开时，统计信息就会被持久化到物理表中，统计信息会更加的稳定和精确。否则表的统计信息会在诸如每次重启前周期性的计算。持久化的统计信息也可以手动修改，修改完成后，使用FLUSH TABLE 命令可以刷新统计信息（不推荐线上如此操作，可能会引发一系列的SQL执行计划问题）

innodb_stats_auto_recalc 控制表多少比例的行被修改后自动更新统计信息，默认是10%， 也可以在create 或者alter table 时通过 STATS_AUTO_RECALC 语法来指定比率。

innodb_stats_include_delete_marked 控制是否在分析索引时包含打上删除标记的记录,在默认的情况下，InnoDB 计算统计信息会读未提交的数据，如果遇到有事务在删除表中的记录，会影响到统计信息的准确度。

innodb_stats_method 统计信息遇到NULL值如何处理，可以认为相等，也可以认为不想等，或者忽略它们。

innodb_stats_on_metadata 在关闭持久化统计信息时，是否在show table status/查看information_schema的TABLES，STATISTICS表时更新统计信息。

innodb_stats_persistent_sample_pages 开启索引信息持久化后索引统计时采样的页面数， 默认20个页面

innodb_stats_transient_sample_pages 关闭索引信息持久化后索引统计时采样的页面书， 默认8个页面

不带直方图的analyze

Analyze table 是可以探测 key 的分布情况，并且将其记录到系统表，在每次analyze的时候也会检测数据表是否发生过变化。

统计信息会获取非常多的信息， 包括索引的修改时间、大小，等等在诸多的统计信息中其中Cardinality是一个很特殊的维度， 对于Cardinality的评估是通过采样评估的方式对表的每一个索引进行统计， 所以得到的是一个估算值而不是精确值。很多的查询选择到了错误的执行计划也是如此原因。

具体Analyze的代码路径为:

代码语言：javascript复制
sql_admin.cc:ql_cmd_analyze_table::execute
  sql_admin.cc:mysql_admin_table
    handler.cc:ha_analyze
      ha_innodb.cc:ha_innodbase::analyze
        ha_innnodb.cc:ha_innodbase::info_low
          dict0stats.cc:dict_stats_update
            dict0stats.cc:dict_stats_update_persistent
              dict0stats.cc:dict_stats_analyze_index
                dict0stats.cc:dict_stats_analyze_index_level
            dict0stats.cc:dict_stats_update_transient

在这条路径中我们发现了一个非常有意思的BUG，涉及到最新的5.6/5.7/8.0，在InnoDB的rebuild table 类型的Online DDL 过程中，如果恰好此时有用户做了analyze table 或者InnoDB的后台刷新统计信息的线程刷新到这张表的主键，此时会导致 dict0stats.cc:dict_stats_analyze_index 在调用 btr_get_size 时返回一个空的统计值，这样的后果是让查询优化器会选择全表扫描， 从而导致大量的慢SQL， 直到做完online DDL再此刷新统计信息以后才能恢复正常。

具体的BUG描述可见 https://bugs.mysql.com/bug.php?id=98132

整个统计信息刷新的过程， 如果是主动发起的Analyze table， 会加上 server 层的MDL_SHARED_READ锁并且将表从Table Definition Cache中淘汰出去。以下几类情况比较特殊

代码语言：javascript复制
innodb_force_recovery 大于等于4
innodb_read_only_mode 那么，统计信息不会持久化， 而是走内存
rtree索引是不采集统计信息的
  1. 线程首先获取树的高度，
  2. 然后自顶向下， 逐层分析， 如果是复合索引，那么通过逐渐增加前缀依次计算 cardinality， 每一层最多扫描N_SAMPLE_PAGES(index）个页面， 如果diff值超过 N_DIFF_REQUIRED(index) = (N_SAMPLE_PAGES(index) * 10)， 那么认为是 found_level, 停止扫描，
  3. 然后从该层开始（很可能是非叶节点层）， 根据扫描过的记录对数据进行分组，分成若干个 Segment， 随机选择每个segment中的一条记录向下探测，
  4. 然后计算叶节点的diff值以及external pages。

8.0 中InnoDB的统计做了进一步的细化，会统计索引页面在缓存Buffer中的比率，Buffer中一个根据Index ID作为Key的哈希结构存储着页面数目，缓存中的数据和外存中的冷数据不同，访问的代价差别也是巨大的，因此这个数据有助于进一步细化

直方图的最新变化

直方图是MySQL 8.0 中新增的统计信息方式， Analyze table 加上直方图语句就可以操作直方图的信息, 直方图并不是存储引擎层实现的，而是在Server层利用InnoDB存储引擎实现的系统表mysql.column_stats，MySQL利用JSON类型的字段来保存直方图的信息，其实现的核心代码在sql/histogram 目录下

具体的操作包括：更新直方图以及drop 直方图，其中更新直方图还可以重新指定bucket的数目， 需要注意的是直方图不支持加密表， 不支持GIS列以及JSON列，以及不支持单列唯一索引的列。

通过 histogram_generation_max_mem_size参数可以调整用于生成直方图的采样记录内存大小，通过查看information_schema的 column_statistic表可以查看 sampling-rate

具体的MySQL 8.0的直方图分析的文章可参考往期的月报文章

http://mysql.taobao.org/monthly/2016/10/09/

最新的MySQL-8.0.19 中， InnoDB实现了自己的采样算法，来避免全表扫描。

在MySQL计算直方图填充时会调用Handler层的ha_sample_init, ha_sample_next 以及 ha_sample_end 接口。

在8.0.19前 InnoDB并没有实现 sample的接口，而是用的Handler层的默认实现rnd_next，也就是全表扫描，直到独到采样比率的数据为止。这里有一个问题，如果采样率设置为10%， 那采样只是读前10%的记录。更科学的做法是在整棵索引树上均匀的采样。

在8.0.19之后的版本中 InnoDB引擎层实现 sample。

参考 https://dev.mysql.com/worklog/task/?id=8777

目前的代码只支持单线程的采样， 但是从代码架构看已经实现了 parallel_reader的接口，不久后一定会实现多线程并行的采样。

InnoDB的采样是交给了单独的worker线程来实现的，一般是对主键进行。整体思路就是根据采样比率相对平均的选择叶子节点页面，假设采样率是10%， 那么会选择一个叶子页面后跳过9个叶子页面， 被选中的页面中会对所有的记录进行采样。

from http://mysql.taobao.org/monthly/2020/03/08/ ，有删改。

#MySQL 8.0 #优化

innodb mysql 索引 统计 优化

0 人点赞