前言

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

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

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

  1. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  2. TABLE tbl_name [, tbl_name] ...
  3. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  4. TABLE tbl_name
  5. UPDATE HISTOGRAM ON col_name [, col_name] ...
  6. [WITH N BUCKETS]
  7. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  8. TABLE tbl_name
  9. 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/10,那么就把这张表加入到后台的recalc pool 中, 而如果变更的行数超过 16+n_rows/16(6.25%),更新非持久化统计信息 。

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

  1. SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
  2. {FROM | IN} tbl_name
  3. [{FROM | IN} db_name]
  4. [WHERE expr]

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

列名描述
database_name库名
table_name表名
last_update最后更新这张表的时间
n_rows表中的数据行数
clustered_index_size聚集索引的页面数
sum_of_other_index_sizes其他非主键索引的页面数
列名描述
database_name库名
table_name表名
index_name索引名
last_update最后更新这张表的时间
stat_name统计项名称
stat_value统计项值
sample_size采样的页面数
stat_description统计的说明

其中stat_name 包括:

  • 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的代码路径为:

  1. sql_admin.cc:ql_cmd_analyze_table::execute
  2. sql_admin.cc:mysql_admin_table
  3. handler.cc:ha_analyze
  4. ha_innodb.cc:ha_innodbase::analyze
  5. ha_innnodb.cc:ha_innodbase::info_low
  6. dict0stats.cc:dict_stats_update
  7. dict0stats.cc:dict_stats_update_persistent
  8. dict0stats.cc:dict_stats_analyze_index
  9. dict0stats.cc:dict_stats_analyze_index_level
  10. 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中淘汰出去。 以下几类情况比较特殊

  • innodb_force_recovery 大于等于4
  • innodb_read_only_mode 那么,统计信息不会持久化, 而是走内存
  • rtree索引是不采集统计信息的

线程首先获取树的高度, 然后自顶向下, 逐层分析, 如果是复合索引,那么通过逐渐增加前缀依次计算cardinality, 每一层最多扫描N_SAMPLE_PAGES(index)个页面, 如果diff值超过 N_DIFF_REQUIRED(index) = (N_SAMPLE_PAGES(index) * 10), 那么认为是found_level, 停止扫描, 然后从该层开始(很可能是非叶节点层), 根据扫描过的记录对数据进行分组,分成若干个Segment, 随机选择每个segment中的一条记录向下探测, 然后计算叶节点的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%的记录。 更科学的做法是在整棵索引树上均匀的采样。 在新版本中终于有了InnoDB引擎层的sample实现。 目前的代码只支持单线程的采样, 但是从代码架构看已经实现了parallel_reader的接口,不久后一定会实现多线程并行的采样。InnoDB的采样是交给了单独的worker线程来实现的,一般是对主键进行。整体思路就是根据采样比率相对平均的选择叶子节点页面,假设采样率是10%, 那么会选择一个叶子页面后跳过9个叶子页面, 被选中的页面中会对所有的记录进行采样。