当前位置：首页 > news >正文

MySQL(InnoDB统计信息)

news 来源：原创 2025/5/16 17:00:26

后面也会持续更新，学到新东西会在其中补充。
建议按顺序食用，欢迎批评或者交流！
缺什么东西欢迎评论！我都会及时修改的！
大部分截图和文章采用该书，谢谢这位大佬的文章，在这里真的很感谢让迷茫的我找到了很好的学习文章。我只是加上了自己的拙见。我只是记录学习没有任何抄袭意思
MySQL 是怎样运行的：从根儿上理解 MySQL - 小孩子4919 - 掘金小册

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

InnoDB提供了两种存储统计数据的方式：

永久性的统计数据
这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
非永久性的统计数据
这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。
在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。

mysql> show variables like '%innodb_stats_persistent%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent              | ON    |
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
2 rows in set (0.05 sec)

不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。
在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：

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

当STATS_PERSISTENT=1时，表明想把该表的统计数据永久的存储到磁盘上。
当STATS_PERSISTENT=0时，表明想把该表的统计数据临时的存储到内存中。
如果在创建表时未指定STATS_PERSISTENT属性，那默认采用系统变量innodb_stats_persistent的值作为该属性的值。

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

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

mysql> SHOW TABLES FROM mysql LIKE 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.00 sec)

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

innodb_table_stats

mysql> desc mysql.innodb_table_stats;
+--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| Field                    | Type            | Null | Key | Default           | Extra                                         |
+--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| database_name            | varchar(64)     | NO   | PRI | NULL              |                                               |
| table_name               | varchar(199)    | NO   | PRI | NULL              |                                               |
| last_update              | timestamp       | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| n_rows                   | bigint unsigned | NO   |     | NULL              |                                               |
| clustered_index_size     | bigint unsigned | NO   |     | NULL              |                                               |
| sum_of_other_index_sizes | bigint unsigned | NO   |     | NULL              |                                               |
+--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+

字段名	描述
database_name	数据库名
table_name	表名
last_update	本条记录最后更新时间
n_rows	表中记录的条数
clustered_index_size	表的聚簇索引占用的页面数量
sum_of_other_index_sizes	表的其他索引占用的页面数量

mysql> SELECT * FROM mysql.innodb_table_stats;
+---------------+--------------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name         | last_update         | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------------+---------------------+---------+----------------------+--------------------------+
| test          | single_table       | 2025-02-02 10:51:10 |    9913 |                   97 |                      150 |
+---------------+--------------------+---------------------+---------+----------------------+--------------------------+
34 rows in set (0.00 sec)

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

n_rows统计项的收集

InnoDB统计一个表中有多少行记录的套路是这样的：

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

可以看出来这个n_rows值精确与否取决于统计时采样的页面数量，innodb_stats_persistent_sample_pages的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久；该值设置的越小，统计出的n_rows值越不精确，但是统计耗时特别少。
所以在实际使用是需要我们去权衡利弊，该系统变量的默认值是20。
在这里插入图片描述
InnoDB默认是以表为单位来收集和存储统计数据的，我们也可以单独设置某个表的采样页面的数量，设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

如果我们在创建表的语句中并没有指定STATS_SAMPLE_PAGES属性的话，将默认使用系统变量innodb_stats_persistent_sample_pages的值作为该属性的值。

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的值。

一个段的数据在非常多时（超过32个页面），会以区为单位来申请空间，这里头的问题是以区为单位申请空间中有一些页可能并没有使用，但是在统计clustered_index_size和sum_of_other_index_sizes时都把它们算进去了，所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。

innodb_index_stats

mysql> desc mysql.innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

字段名	描述
database_name	数据库名
table_name	表名
index_name	索引名
last_update	本条记录最后更新时间
stat_name	统计项的名称
stat_value	对应的统计项的值
sample_size	为生成统计数据而采样的页面数量
stat_description	对应的统计项的描述

注意这个表的主键是(database_name,table_name,index_name,stat_name)，其中的stat_name是指统计项的名称，也就是说innodb_index_stats表的每条记录代表着一个索引的一个统计项。

SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'single_table';mysql> SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'single_table';
+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name   | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | single_table | PRIMARY      | 2025-02-02 10:51:10 | n_diff_pfx01 |       9913 |          20 | id                                |
| test          | single_table | PRIMARY      | 2025-02-02 10:51:10 | n_leaf_pages |         62 |        NULL | Number of leaf pages in the index |
| test          | single_table | PRIMARY      | 2025-02-02 10:51:10 | size         |         97 |        NULL | Number of pages in the index      |
| test          | single_table | idx_key1     | 2025-02-02 10:51:10 | n_diff_pfx01 |      10000 |          20 | key1                              |
| test          | single_table | idx_key1     | 2025-02-02 10:51:10 | n_diff_pfx02 |      10000 |          20 | key1,id                           |
| test          | single_table | idx_key1     | 2025-02-02 10:51:10 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| test          | single_table | idx_key1     | 2025-02-02 10:51:10 | size         |         21 |        NULL | Number of pages in the index      |
| test          | single_table | idx_key2     | 2025-02-02 10:51:10 | n_diff_pfx01 |      10000 |          10 | key2                              |
| test          | single_table | idx_key2     | 2025-02-02 10:51:10 | n_leaf_pages |         10 |        NULL | Number of leaf pages in the index |
| test          | single_table | idx_key2     | 2025-02-02 10:51:10 | size         |         11 |        NULL | Number of pages in the index      |
| test          | single_table | idx_key3     | 2025-02-02 10:51:10 | n_diff_pfx01 |      10000 |          20 | key3                              |
| test          | single_table | idx_key3     | 2025-02-02 10:51:10 | n_diff_pfx02 |      10000 |          20 | key3,id                           |
| test          | single_table | idx_key3     | 2025-02-02 10:51:10 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| test          | single_table | idx_key3     | 2025-02-02 10:51:10 | size         |         21 |        NULL | Number of pages in the index      |
| test          | single_table | idx_key_part | 2025-02-02 10:51:10 | n_diff_pfx01 |      10000 |          37 | key_part1                         |
| test          | single_table | idx_key_part | 2025-02-02 10:51:10 | n_diff_pfx02 |      10000 |          37 | key_part1,key_part2               |
| test          | single_table | idx_key_part | 2025-02-02 10:51:10 | n_diff_pfx03 |      10000 |          37 | key_part1,key_part2,key_part3     |
| test          | single_table | idx_key_part | 2025-02-02 10:51:10 | n_diff_pfx04 |      10000 |          37 | key_part1,key_part2,key_part3,id  |
| test          | single_table | idx_key_part | 2025-02-02 10:51:10 | n_leaf_pages |         37 |        NULL | Number of leaf pages in the index |
| test          | single_table | idx_key_part | 2025-02-02 10:51:10 | size         |         97 |        NULL | Number of pages in the index      |
+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
20 rows in set (0.01 sec)

先查看index_name列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看出来，PRIMARY索引（也就是主键）占了3条记录，idx_key_part索引占了6条记录。

针对index_name列相同的记录，stat_name表示针对该索引的统计项名称，stat_value展示的是该索引在该统计项上的值，stat_description指的是来描述该统计项的含义的。

n_leaf_pages：表示该索引的叶子节点占用多少页面。
size：表示该索引共占用多少页面。
n_diff_pfxNN：表示对应的索引列不重复的值有多少。其中的NN长得有点儿怪呀。
其实NN可以被替换为01、02、03... 这样的数字。比如对于idx_key_part来说：
n_diff_pfx01表示的是统计key_part1这一个列不重复的值有多少。
n_diff_pfx02表示的是统计key_part1、key_part2这两个列组合起来不重复的值有多少。
n_diff_pfx03表示的是统计key_part1、key_part2、key_part3这三个列组合起来不重复的值有多少。
n_diff_pfx04表示的是统计key_part1、key_part2、key_part3、id这四个列组合起来不重复的值有多少。

对于普通的二级索引，并不能保证它的索引列值是唯一的，比如对于idx_key1来说，key1列就可能有很多值重复的记录。
此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。
对于主键和唯一二级索引则没有这个问题，它们本身就可以保证索引列值的不重复，所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。
比如上边的idx_key1有n_diff_pfx01、n_diff_pfx02两个统计项，而idx_key2却只有n_diff_pfx01一个统计项

在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，sample_size列就表明了采样的页面数量是多少。

对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pages × 索引列的个数。当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫描来统计索引列的不重复值数量了。所以可以在查询结果中看到不同索引对应的size列的值可能是不同的。

定期更新统计数据

开启innodb_stats_auto_recalc。

mysql> show variables like '%innodb_stats_auto_recalc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.02 sec)

系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据，它的默认值是ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新innodb_table_stats和innodb_index_stats表。

InnoDB默认是以表为单位来收集和存储统计数据的，也可以单独为某个表设置是否自动重新计算统计数的属性，设置方式就是在创建或修改表的时候通过指定STATS_AUTO_RECALC属性来指明该表的统计数据存储方式：

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

当STATS_AUTO_RECALC=1时，表明想让该表自动重新计算统计数据，当STATS_AUTO_RECALC=0时，表明不想让该表自动重新计算统计数据。如果在创建表时未指定STATS_AUTO_RECALC属性，那默认采用系统变量innodb_stats_auto_recalc的值作为该属性的值。

手动调用ANALYZE TABLE语句来更新统计信息

如果innodb_stats_auto_recalc系统变量的值为OFF的话

mysql> ANALYZE TABLE single_table;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| test.single_table | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.08 sec)

ANALYZE TABLE语句会立即重新计算统计数据，也就是这个过程是同步的，在表中索引多或者采样页面特别多时这个过程可能会特别慢，请不要没事儿就运行一下ANALYZE TABLE语句，最好在业务不是很繁忙的时候再运行。

手动更新`innodb_table_stats`和`innodb_index_stats`表

这个还是别乱改吧！看看就行了影响执行计划的！
其实innodb_table_stats和innodb_index_stats表就相当于一个普通的表一样，我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。

步骤一：更新innodb_table_stats表。

//强制告诉InnoDB：“此表当前仅有1行数据”。
//此时磁盘上的实际数据并未改变，仅修改了统计信息。
//如果表中实际有更多数据，这会导致优化器基于错误信息生成低效的执行计划。
UPDATE mysql.innodb_table_stats SET n_rows = 1WHERE table_name = 'single_table';

步骤二：让MySQL查询优化器重新加载更改过的数据。
更新完innodb_table_stats只是单纯的修改了一个表的数据，需要让MySQL查询优化器重新加载我们更改过的数据，运行下边的命令就可以了：

//FLUSH TABLE single_table会清除表的缓存，并触发InnoDB重新加载该表的元数据
FLUSH TABLE single_table;

使用SHOW TABLE STATUS语句查看表的统计数据时就看到Rows行变为了1。
在这里插入图片描述

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

把系统变量innodb_stats_persistent的值设置为OFF时，之后创建的表的统计数据默认就都是非永久性的了，或者我们直接在创建表或修改表时设置STATS_PERSISTENT属性的值为0，那么该表的统计数据就是非永久性的了。
与永久性的统计数据不同，非永久性的统计数据采样的页面数量是由innodb_stats_transient_sample_pages控制的，这个系统变量的默认值是8。

mysql> show variables like '%innodb_stats_transient_sample_pages%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| innodb_stats_transient_sample_pages | 8     |
+-------------------------------------+-------+
1 row in set (0.00 sec)

innodb_stats_method的使用

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

单表查询中单点区间太多，比方说这样：

SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');

当IN里的参数数量过多时，采用index dive的方式直接访问B+树索引去统计每个单点区间对应的记录的数量就太耗费性能了，所以直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。

连接查询时，如果有涉及两个表的等值匹配连接条件，该连接条件对应的被驱动表中的列又拥有索引时，则可以使用ref访问方法来对被驱动表进行查询，比方说这样：

SELECT * FROM t1 JOIN t2 ON t1.column = t2.key WHERE ...;

在统计索引列不重复的值的数量时，索引列中出现NULL值怎么办，比方说某个索引列的内容是这样：

mysql>  create table test(t int);
Query OK, 0 rows affected (0.04 sec)mysql> insert into test values(1),(2),(null),(null);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> create index idx_t on test(t);mysql> select * from test;
+------+
| t    |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
+------+
4 rows in set (0.00 sec)mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.02 sec)

innodb_stats_method的系统变量，相当于在计算某个索引列不重复值的数量时如何对待NULL值。

nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。
如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
nulls_unequal：认为所有NULL值都是不相等的。
如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
nulls_ignored：直接把NULL值忽略掉。

mysql> show variables like '%innodb_stats_method%';
+---------------------+-------------+
| Variable_name       | Value       |
+---------------------+-------------+
| innodb_stats_method | nulls_equal |
+---------------------+-------------+
1 row in set (0.00 sec)

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值。

总结

所有结论都需要反复测试！如果有错误欢迎指正！一起努力！
如果喜欢的话，请点个赞吧就算鼓励我一下。