文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

一文带你了解MySQL之InnoDB统计数据是如何收集的

2023-05-25 10:58

关注

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

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

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

不过InnoDB默认是以表为单位来收集和存储统计数据的,也就是说我们可以把某些表的统计数据(以及该表的索引统计数据)存储在磁盘上,把另一些表的统计数据存储在内存中。怎么做到的呢?我们可以在创建和修改表的时候通过指定stats_persistent属性来指明该表的统计数据存储方式:

create table 表名 (...) engine=innodb, stats_persistent = (1|0);
alter table 表名 engine=innodb, stats_persistent = (1|0);

需要注意的是如果我们在创建表时未指定stats_persistent属性,那默认采用系统变量innodb_stats_persistent的值作为该属性的值

mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.01 sec)
mysql> set persist innodb_stats_persistent =1;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> show tables from mysql like 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.03 sec)

可以看到,这两个表都位于mysql系统数据库下边,其中:

我们下边的就是看⼀下这两个表里边都有什么以及表里的数据是如何生成的

2.1 innodb_table_stats

这里直接看一下innodb_table_stats表中的各个列都是干嘛的:

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

注意这个表的主键是(database_name,table_name),也就是innodb_table_stats表的每条记录代表着一个表的统计信息。我们直接看一下这个表里的内容:

mysql> select * from mysql.innodb_table_stats where database_name= 'testdb' limit 2;
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| testdb        | demo1         | 2023-05-06 18:20:56 |      1 |                    1 |                        0 |
| testdb        | demo8         | 2023-05-16 16:38:16 |  18758 |                   97 |                      170 |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.01 sec)

可以看到我们熟悉的demo8表的统计信息就对应着mysql.innodb_table_stats的第二条记录。几个重要统计信息项的值如下:

2.1.1 n_rows 统计项的收集

为啥一直强调n_rows这个统计项的值是估计值呢?现在就来揭晓答案。InnoDB统计一个表中有多少条记录是这样的:

小提示真实的计算过程比这个稍微复杂一些,不过大致上就是这样的意思

可以看出来这个n_rows值精确与否取决于统计时采样的页面数量,MySQL为我们准备了一个名为innodb_stats_persistent_sample_pages的系统变量来控制使用永久性的统计数据时,计算统计数据时采样的页面数量。

所以在实际使用是需要我们去权衡利弊,该系统变量的默认值是20。

mysql> show variables like 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
1 row in set (0.01 sec)
mysql> set persist innodb_stats_persistent_sample_pages =20;
Query OK, 0 rows affected (0.02 sec)

我们前边说过,不过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的值作为该属性的值。

2.1.2 clustered_index_size 和 sum_of_other_index_sizes统计项的收集

统计这两个数据需要大量用到我们之前学习的InnoDB表空间的知识,如果大家压根就没有看那一章,那下边的计算过程大家还是不要看了(看也看不懂);如果看过了,那大家就会发现InnoDB表空间的知识真是有用。

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

步骤一: 从数据字典中找到表的各个索引对应的根页面位置(系统表sys_indexes里存储了各个索引对应的根页面信息)

步骤二: 从根页面的Page Header里找到叶子节点段和非叶子节点段对应的Segment Header。在每个索引的根⻚⾯的Page Header部分都有两个字段:

步骤三: 从叶子节点段和非叶子节点段的Segment Header中找到这两个段对应的INODE Entry结构。

Segment Header结构如下:

一文带你了解MySQL之InnoDB统计数据是如何收集的

从对应的INODE Entry结构中可以找到该段对应所有零散的页面地址以及freenot_fullfull链表的基节点。

INODE Entry结构如下:

一文带你了解MySQL之InnoDB统计数据是如何收集的

直接统计零散的页面有多少个,然后从那三个链表的List Length字段中读出该段占用的区的大小,每个区占用64个页,所以就可以统计出整个段占用的页面。

这个是链表基节点的示意图:

一文带你了解MySQL之InnoDB统计数据是如何收集的

步骤四: 分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数,它们的和就是clustered_index_size的值,按照同样的套路把其余索引占用的页面数都算出来,加起来之后就是sum_of_other_index_sizes的值

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

2.2 innodb_index_stats

直接看一下这个innodb_index_stats表中的各个列都是干嘛的:

字段名描述
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表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么,别着急,我们直接看一下关于demo8表的索引统计数据都有些什么:

mysql> select * from mysql.innodb_index_stats where table_name='demo8';
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name   | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb        | demo8      | PRIMARY      | 2023-05-16 16:38:16 | n_diff_pfx01 |      18750 |          20 | id                                |
| testdb        | demo8      | PRIMARY      | 2023-05-16 16:38:16 | n_leaf_pages |         75 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | PRIMARY      | 2023-05-16 16:38:16 | size         |         97 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | n_diff_pfx01 |        256 |          22 | key1                              |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | n_diff_pfx02 |      18567 |          22 | key1,id                           |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | n_leaf_pages |         22 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | size         |         23 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key2     | 2023-05-16 16:38:16 | n_diff_pfx01 |      18565 |          18 | key2                              |
| testdb        | demo8      | idx_key2     | 2023-05-16 16:38:16 | n_leaf_pages |         18 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key2     | 2023-05-16 16:38:16 | size         |         19 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | n_diff_pfx01 |       4053 |          30 | key3                              |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | n_diff_pfx02 |      18568 |          30 | key3,id                           |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | n_leaf_pages |         30 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | size         |         31 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx01 |      16122 |          50 | key_part1                         |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx02 |      18570 |          50 | key_part1,key_part2               |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx03 |      18570 |          50 | key_part1,key_part2,key_part3     |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx04 |      18570 |          50 | key_part1,key_part2,key_part3,id  |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_leaf_pages |         50 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | size         |         97 |        NULL | Number of pages in the index      |
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
20 rows in set (0.03 sec)

这个结果有点多,正确查看这个结果的方式是这样的:

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

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

2.3 定期更新统计数据

随着我们不断的对表进行增删改操作,表中的数据也一直在变化,innodb_table_stats和innodb_index_stats表立的统计数据是不是也应该跟着变一变了?当然要变了,不变的话MySQL查询优化器计算的成本可就差老远了。MySQL的提供了如下两种更新统计数据的方式:

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

mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> set persist innodb_stats_auto_recalc =1;
Query OK, 0 rows affected (0.00 sec)

再一次强调,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_persistent=0时,表明不想让该表自动重新计算统计数据

如果我们在创建表时未指定stats_auto_recalc属性,那默认采用系统变量innodb_stats_auto_recalc的值作为该属性的值。

mysql> analyze table demo8;

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

2.4 手动动更新innodb_table_stats 和 innodb_index_stats表

其实innodb_table_stats和innodb_index_stats表就相当于一个普通的表一样,我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。比如说我们想把demo8表关于行数的统计数据更改一下可以这么做:

步骤一: 更新demo8表

update innodb_table_stats set n_rows = 1 where table_name = 'demo8';

步骤二: 让MySQL查询优化器重新加载我们更改过的数据

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

flush table demo8;

之后我们使用show table status语句查看表的统计数据时就看到Rows行变为了1。

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

当我们把系统变量innodb_stats_persistent的值设置为OFF时,之后创建的表的统计数据默认就都是非永久性的了,或者我们直接在创建表或修改表时设置stats_persistent属性的值为0,那么该表的统计数据就是非永久性的了。

与永久性的统计数据不同,非永久性的统计数据采样的页面数量是由innodb_stats_transient_sample_pages控制的,这个系统变量的默认值是8。另外,由于非永久性的统计数据经常更新,所以导致MySQL查询优化器计算查询成本的时候依赖的是经常变化的统计数据,也就会生成经常变化的执行计划,这个可能让大家有些懵逼。不过最近的MySQL版本都不咋用这种基于内存的非永久性统计数据了,所以我们也就不深入了解了。

mysql> show variables like 'innodb_stats_transient_sample_pages';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| innodb_stats_transient_sample_pages | 8     |
+-------------------------------------+-------+
1 row in set (0.01 sec)
mysql> set persist innodb_stats_transient_sample_pages =1;
Query OK, 0 rows affected (0.00 sec)

四、innodb_stats_method 的使用

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

select * from tbl_name where key in ('xx1', 'xx2', ..., 'xxn');

select * from t1 join t2 on t1.column = t2.key where ...;

在真正执行对t2表的查询前,t1.comumn的值是不确定的,所以我们也不能通过index dive的方式直接访问B+树索引去统计每个单点区间对应的记录的数量,所以也只能依赖统计数据中的平均一个值重复多少次来计算单点区间对应的记录数量。

在统计索引列不重复的值的数量时,有一个比较烦的问题就是索引列中出现NULL值怎么办,比如说某个索引列的内容是这样:

+------+
| col  |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
+------+

此时计算这个col列中不重复的值的数量就有下边的分歧:

mysql> select 1 = null;
+----------+
| 1 = null |
+----------+
|     null |
+----------+
1 row inset (0.00 sec)
mysql> select 1 != null;
+-----------+
| 1 != null |
+-----------+
|      null |
+-----------+
1 row inset (0.00 sec)
mysql> select null = null;
+-------------+
| null = null |
+-------------+
|        null |
+-------------+
1 row inset (0.00 sec)
mysql> select null != null;
+--------------+
| null != null |
+--------------+
|         null |
+--------------+
1 row inset (0.00 sec)

所以每一个NULL值都是独一无二的,也就是说统计索引列不重复的值的数量时,应该把NULL值当作一个独立的值,所以col列的不重复的值的数量就是:4(分别是1、2、NULL、NULL这四个值)。

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

反正这个锅是甩给用户了,当你选定了innodb_stats_method值之后,优化器即使选择了不是最优的执行计划,那也跟MySQL没关系了哈~ 当然对于用户的我们来说,最好不在索引列中存放NULL值才是正解。

总结

InnoDB以表为单位来收集统计数据,这些统计数据可以是基于磁盘的永久性统计数据,也可以是基于内存的非永久性统计数据

以上就是一文带你了解MySQL之InnoDB统计数据是如何收集的的详细内容,更多关于MySQL InnoDB统计数据收集的资料请关注我们其它相关文章!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯