文章

InnoDB 如何计算统计数据

统计数据的存储

InnoDB 统计数据可以存储在磁盘上，也可以只存储在内存中。MySQL 提供了一个系统变量 innodb_stats_persistent 来控制使用哪种方式来存储统计数据，MySQL 5.7 默认为 ON，表示统计数据默认存储在磁盘上。

由于 InnoDB 以表为单位来收集统计数据，因此可以单独在表定义中用 STATS_PERSISTENT 指定该表的统计数据存储方式，形如：

CREATE TABLE <table_name> ( ... ) Engine=InnoDB STATS_PERSISTENT=1;  -- STATS_PERSISTENT 取值有 0 和 1，1 表示使用磁盘存储

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

当选择把统计数据存储到磁盘上时，它们其实是存储到了 mysql 系统数据库的 innodb_table_stats 和 innodb_index_stats 这两张表中：

mysql> SHOW TABLES FROM mysql LIKE 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+

innodb_table_stats 表

innodb_table_stats 表存储表的统计信息：

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'employee';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | employee   | 2023-09-01 15:06:37 |   9414 |                   97 |                       92 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+

我们看一下每一列的含义：

列名

说明

database_name

数据库名

table_name

表名

last_update

该记录的最后更新时间

n_rows

表中的记录数

clustered_index_size

表的聚簇索引占用的页数

sum_of_other_index_sizes

表的其他索引总共占用的页数

看一下我们熟悉的 employee 表：

  1. n_rows 为 9414，表示 employee 表中大概有 9414 条记录（估计值）

  2. clustered_index_size 为 97，表示 employee 表的聚簇索引大概占用 97 个页（估计值）

  3. sum_of_other_index_sizes 为 92，表示 employee 表的其他索引总共占用约 92 个页（估计值）

innodb_table_stats 表的主键是 (database_name, table_name)，也就是说表中的每一条记录对应一张表的统计数据。

n_rows 统计项

统计一个表中大概有多少条记录的过程是这样的：按照一定的算法选取几个叶子节点页，然后计算平均一个页中的记录数，再乘以叶子节点的数量就是 n_rows。

可以看出 n_rows 的精确程度取决于采样的叶子节点页数，因此 MySQL 提供了一个系统变量 innodb_stats_persistent_sample_pages，允许我们自定义计算永久性统计数据的采样页数，默认值为 20。

也可以在表定义中通过 STATS_SAMPLE_PAGES 属性单独控制某张表的采样页数。

clustered_index_size 和 sum_of_other_index_sizes 统计项

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

  1. 从系统表空间的数据字典获取表中各个索引的根页面（存储在 SYS_INDEXES 基本系统表中）

  2. 从索引根页面的 Page Header 中的 PAGE_BTR_SEG_LEAF 和 PAGE_BTR_SEG_TOP 获取叶子节点段和非叶子节点段的 INODE Entry

  3. 从对应的 INODE Entry 中获取碎片页数量和 FREE、NOT_FULL、FULL 链表的 List Base Node 中的 List Length，将碎片页数量与三个 LIst Length * 64 相加就是这个段占用的页数

  4. 分别计算聚簇索引的叶子结点段和非叶子节点段占用的页数，加起来就是 clustered_index_size 的值，按照同样的方法把其他索引占用的页数都计算出来，加起来就是 sum_of_other_index_sizes 的值。

当使用了 32 个碎片页之后，会以区为单位申请存储空间，由于分配的区中可能存在部分页未使用，因此 clustered_index_size 和 sum_of_other_index_sizes 的统计值可能会偏大一些。

innodb_index_stats 表

innodb_index_stats 表存储索引的统计信息：

mysql> SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'employee';
+---------------+------------+---------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name                | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+---------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | employee   | PRIMARY                   | 2023-09-01 15:06:37 | n_diff_pfx01 |       9388 |          20 | id                                |
| test          | employee   | PRIMARY                   | 2023-09-01 15:06:37 | n_leaf_pages |         53 |        NULL | Number of leaf pages in the index |
| test          | employee   | PRIMARY                   | 2023-09-01 15:06:37 | size         |         97 |        NULL | Number of pages in the index      |
| test          | employee   | idx_country_province_city | 2023-09-01 15:06:37 | n_diff_pfx01 |         10 |          31 | country                           |
| test          | employee   | idx_country_province_city | 2023-09-01 15:06:37 | n_diff_pfx02 |         56 |          31 | country,province                  |
| test          | employee   | idx_country_province_city | 2023-09-01 15:06:37 | n_diff_pfx03 |        129 |          31 | country,province,city             |
| test          | employee   | idx_country_province_city | 2023-09-01 15:06:37 | n_diff_pfx04 |       9600 |          31 | country,province,city,id          |
| test          | employee   | idx_country_province_city | 2023-09-01 15:06:37 | n_leaf_pages |         31 |        NULL | Number of leaf pages in the index |
| test          | employee   | idx_country_province_city | 2023-09-01 15:06:37 | size         |         32 |        NULL | Number of pages in the index      |
| test          | employee   | idx_employee_id           | 2023-09-01 15:06:37 | n_diff_pfx01 |       9600 |          10 | employee_id                       |
| test          | employee   | idx_employee_id           | 2023-09-01 15:06:37 | n_leaf_pages |         10 |        NULL | Number of leaf pages in the index |
| test          | employee   | idx_employee_id           | 2023-09-01 15:06:37 | size         |         11 |        NULL | Number of pages in the index      |
| test          | employee   | idx_english_name          | 2023-09-01 15:06:37 | n_diff_pfx01 |         10 |          26 | english_name                      |
| test          | employee   | idx_english_name          | 2023-09-01 15:06:37 | n_diff_pfx02 |       9600 |          26 | english_name,id                   |
| test          | employee   | idx_english_name          | 2023-09-01 15:06:37 | n_leaf_pages |         26 |        NULL | Number of leaf pages in the index |
| test          | employee   | idx_english_name          | 2023-09-01 15:06:37 | size         |         27 |        NULL | Number of pages in the index      |
| test          | employee   | idx_name                  | 2023-09-01 15:06:37 | n_diff_pfx01 |         10 |          21 | name                              |
| test          | employee   | idx_name                  | 2023-09-01 15:06:37 | n_diff_pfx02 |       9600 |          21 | name,id                           |
| test          | employee   | idx_name                  | 2023-09-01 15:06:37 | n_leaf_pages |         21 |        NULL | Number of leaf pages in the index |
| test          | employee   | idx_name                  | 2023-09-01 15:06:37 | size         |         22 |        NULL | Number of pages in the index      |
+---------------+------------+---------------------------+---------------------+--------------+------------+-------------+-----------------------------------+

我们看一下每一列的含义：

列名

说明

database_name

数据库名

table_name

表名

index_name

索引名

last_update

该记录的最后更新时间

stat_name

统计项的名称

stat_value

对应统计项的值

sample_size

计算统计数据的采样页数

stat_description

对应统计项的描述

我们看下表中有哪些统计项（stat_name）：

  1. n_diff_pfxNN：对应索引列的不重复值数量，这里 NN 取值为 01、02、03、...，也就是说一个索引可能有多条 n_diff_pfxNN 统计数据记录，对于 idx_country_province_city 来说：

    1. n_diff_pfx01：表示 country 这一列的不重复值数量

    2. n_diff_pfx02：表示 country,province 这两列组合起来的不重复值数量

    3. n_diff_pfx03：表示 country,province,city 这三列组合起来的不重复值数量

    4. n_diff_pfx04：表示 country,province,city,id 这四列组合起来的不重复值数量。由于普通二级索引不能保证索引列值的唯一性，因此索引列后面会加上了主键列，相应地也要统计引列后面加上主键列后的不重复值数量，而主键索引和唯一二级索引则不需要。

  2. n_leaf_pages：对应索引的叶子节点占用的页数

  3. size：对应索引占用的总页数

innodb_index_stats 表的主键是 (database_name, table_name, index_name)，也就是说表中的每一条记录对应一个索引的一个统计项的统计数据。

基于内存的临时性统计数据

当我们把系统变量 innodb_stats_persistent 的值设置为 OFF 时，或者在表定义中设置 STATS_PERSISTENT 属性为 0 ，那么该表的统计数据就会存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了。 

与永久性的统计数据不同，临时性的统计数据采样的页数是由 innodb_stats_transient_sample_pages 控制的，默认值为 8。

统计数据的更新

随着我们不断地对表做增删改操作，统计表中的数据也需要跟着更新。MySQL 支持定期和手动两种方式更新统计数据。

定期更新统计数据

定期更新的开启由 innodb_stats_auto_recalc 系统变量控制，默认是 ON 开启的。MySQL 给每个表都维护了一个变量，用来记录增删改的记录数，当改动的表记录数超过了 10%，并且定期更新统计数据是开启状态，就会异步计算该表的最新统计数据。

也可以在表定义中通过 STATS_AUTO_RECALC 属性单独给某张表设置是否定期更新统计数据。

手动更新统计数据

手动更新统计数据分为两种方式：

(1) 手动触发更新

ANALYSE TABLE <table_name>`;  -- 同步计算

(2) 手动修改统计数据

-- step 1
UPDATE [innodb_table_stats | innodb_index_stats] SET <column> = <value> WHERE table_name = <table_name>;
-- step 2: 清空表的缓存
FLUSH TABLE <table_name>;

统计不重复值的数量时如何对待 NULL 值 

索引列不重复值的数量这个统计项对MySQL 查询优化器非常重要，通过它可以计算索引列平均一个值有多少条记录，主要使用场景有：

(1) 查询条件中的列有索引并且存在大量单点区间的查询，如：

SELECT * FROM employee WHERE name IN ('n1', ''n2', ''n3', ..., 'n1000');  -- 这个查询有 1000 个单点区间

大量单点区间使用 index dive 效率太低，因此使用`索引列不重复值的数量`来计算 name 的单点区间的平均记录数，再估算总记录数。

(2) 使用等值匹配的连接条件并且连接条件中被驱动表的列有索引的连接查询，如：

SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.name = e2.english_name;

因为没真正执行查询前，e1.name 值是不确定的，因此只能使用索引列不重复值的数量来计算 e2.english_name 的单点区间的平均记录数，再估算总记录数。

在统计索引列不重复值的数量时，需要考虑如何对待 NULL 值，MySQL 提供了一个系统变量 innodb_stats_method 让用户自己选择对待 NULL 值的方式：

  1. nulls_equal：所有 NULL 值都相等，意味着所有 NULL 值只会给不重复数加1（innodb_stats_method 的默认值）

  2. nulls_unequal：所有 NULL 值都不相等，意味着每个 NULL 值都会给不重复数加1

  3. nulls_ignored：忽略 NULL 值，意味着 NULL 值不计入不重复数

MySQL
License:  CC BY 4.0