【MySQL】 InnoDB 的统计数据收集

Metadata

title: 【MySQL】 InnoDB 的统计数据收集
date: 2023-06-23 15:44
tags:
  - 行动阶段/完成
  - 主题场景/数据存储
  - 笔记空间/KnowladgeSpace/ProgramSpace/BasicsSpace
  - 细化主题/数据存储
categories:
  - 数据存储
keywords:
  - 数据存储
description: 【MySQL】 InnoDB 的统计数据收集

概述

InnoDB 以表为单位来收集统计数据，这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据。
innodb_stats_persistent 控制着是否使用永久性统计数据；
innodb_stats_persistent_sample_pages 控制着永久性统计数据的采样页面数量；
innodb_stats_transient_sample_pages 控制着非永久性统计数据的采样页面数量；
innodb_stats_auto_recalc 控制着是否自动重新计算统计数据。
我们可以针对某个具体的表，在创建和修改表时通过指定STATS_PERSISTENT 、STATS_AUTO_RECALC 、STATS_SAMPLE_PAGES 的值来控制相关统计数据属性。
innodb_stats_method 决定着在统计某个索引列不重复值的数量时如何对待NULL 值。

前言

通过SHOW TABLE STATUS 可以看到关于表的统计数据
通过SHOW INDEX 可以看到关于索引的统计数据

两种不同的统计数据存储方式

永久性的统计数据
这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
非永久性的统计数据
这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了

提供了系统变量innodb_stats_persistent 来控制到底采用哪种方式去存储统计数据

InnoDB 默认是以表为单位来收集和存储统计数据的

也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。

在创建和修改表的时候通过指定STATS_PERSISTENT 属性来指明该表的统计数据存储方式

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

基于磁盘的永久性统计数据

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里

这两个表都位于mysql 系统数据库下边

innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

innodb_table_stats

注意这个表的主键是(database_name,table_name)

innodb_table_stats表的每条记录代表着一个表的统计信息

几个重要统计信息项的值如下：

n_rows 的值是9693 ，表明single_table 表中大约有9693 条记录，注意这个数据是估计值。
clustered_index_size 的值是97 ，表明single_table 表的聚簇索引占用97个页面，这个值是也是一个估计值。
sum_of_other_index_sizes 的值是175 ，表明single_table 表的其他索引一共占用175个页面，这个值是也是一个估计值。

n_rows统计项的收集

n_rows 这个统计项的值是估计值

InnoDB 统计一个表中有多少行记录的套路:

按照一定算法（并不是纯粹随机的）选取几个叶子节点页面
计算每个页面中主键值记录数量
然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows 值。

n_rows 值精确与否取决于统计时采样的页面数量

准备了一个名为innodb_stats_persistent_sample_pages 的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。
该系统变量的默认值是20

clustered_index_size和sum_of_other_index_sizes统计项的收集

这两个统计项的收集过程如下：

从数据字典里找到表的各个索引对应的根页面位置。
系统表SYS_INDEXES 里存储了各个索引对应的根页面信息。
从根页面的Page Header 里找到叶子节点段和非叶子节点段对应的Segment Header 。
在每个索引的根页面的Page Header 部分都有两个字段：
- PAGE_BTR_SEG_LEAF ：表示B+树叶子段的Segment Header 信息。
- PAGE_BTR_SEG_TOP ：表示B+树非叶子段的Segment Header 信息。
从叶子节点段和非叶子节点段的Segment Header 中找到这两个段对应的INODE Entry 结构。
从对应的INODE Entry 结构中可以找到该段对应所有零散的页面地址以及FREE 、NOT_FULL 、FULL 链表的基节点。
直接统计零散的页面有多少个，然后从那三个链表的List Length 字段中读出该段占用的区的大小，每个区占用64 个页，所以就可以统计出整个段占用的页面。
分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数,
- 它们的和就是clustered_index_size 的值
- 按照同样的套路把其余索引占用的页面数都算出来，加起来之后就是sum_of_other_index_sizes 的值。

以区为单位申请空间中有一些页可能并没有使用，但是在统计clustered_index_size 和 sum_of_other_index_sizes 时都把它们算进去了，所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。

innodb_index_stats

注意这个表的主键是(database_name,table_name,index_name,stat_name)

其中的stat_name 是指统计项的名称，也就是说innodb_index_stats表的每条记录代表着一个索引的一个统计项。

正确查看这个结果的方式是这样的：

先查看index_name 列，这个列说明该记录是哪个索引的统计信息
针对index_name 列相同的记录， stat_name 表示针对该索引的统计项名称， stat_value 展示的是该索引在该统计项上的值， stat_description 指的是来描述该统计项的含义的。
- n_leaf_pages 该索引的叶子节点占用多少页面
- size 表示该索引共占用多少页面。
- n_diff_pfxNN 表示对应的索引列不重复的值有多少
在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样， size 列就表明了采样的页面数量是多少。

定期更新统计数据

提供了如下两种更新统计数据的方式：

开启innodb_stats_auto_recalc 。
系统变量innodb_stats_auto_recalc 决定着服务器是否自动重新计算统计数据，它的默认值是ON ，也就是该功能默认是开启的。
过自动重新计算统计数据的过程是异步发生的
手动调用ANALYZE TABLE 语句来更新统计信息
ANALYZE TABLE语句会立即重新计算统计数据，也就是这个过程是同步的

手动更新innodb_table_stats 和innodb_index_stats 表

手动更新某个表或者索引的统计数据

步骤一：更新innodb_table_stats 表。
步骤二：让MySQL 查询优化器重新加载我们更改过的数据。

基于内存的非永久性统计数据

系统变量innodb_stats_persistent 的值设置为OFF
直接在创建表或修改表时设置STATS_PERSISTENT 属性的值为0

由于非永久性的统计数据经常更新，所以导致MySQL 查询优化器计算查询成本的时候依赖的是经常变化的统计数据，也就会生成经常变化的执行计划

innodb_stats_method的使用

索引列不重复的值的数量这个统计数据对于MySQL 查询优化器十分重要，因为通过它可以计算出在索引列中平均一个值重复多少行

它的应用场景主要有两个

单表查询中单点区间太多
直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量
连接查询时，如果有涉及两个表的等值匹配连接条件，该连接条件对应的被驱动表中的列又拥有索引时，则可以使用ref 访问方法来对被驱动表进行查询

他们提供了一个名为innodb_stats_method 的系统变量，相当于在计算某个索引列不重复值的数量时如何对待NULL 值这个锅甩给了用户，这个系统变量有三个候选值：

nulls_equal ：认为所有NULL 值都是相等的。
nulls_unequal ：认为所有NULL 值都是不相等的。
nulls_ignored ：直接把NULL 值忽略掉。

最好不在索引列中存放NULL值才是正解