MySQL字段信息统计机制场景

  • 发表于：
  • 分类： 网络资源
  • 浏览：浏览(44)

技术社群推送的这篇文章《新特性解读 | MySQL 8.0 字段信息统计机制》，介绍了字段信息统计的机制，对于我们了解数据库的原理，以及在其它技术领域的设计可以参考。

1背景

同事咨询个问题：某个业务基于 INFORMATION_SCHEMA 
统计表的信息（比如最大值）向表里面插入数据。

请问 INFORMATION_SCHEMA.TABLES
 中的 AUTO_INCREMENT 
会不会及时地更新呢？

先说结论：可以！

这里涉及到 信息统计机制[1] 或者说频率问题，主要由参数 information_schema_stats_expiry
 控制。

表信息更新的基本逻辑

默认情况下，MySQL 会高效地从 系统表 mysql.index_stats
 和 mysql.table_stats
 中检索这些列的缓存值，而不是直接从存储引擎中获取统计信息。如果缓存的统计信息不可用或已过期，MySQL 将从存储引擎中检索最新的统计信息，并将其统计信息更新并缓存在 mysql.index_stats
 和 mysql.table_stats
 字典表中。后续查询将检索缓存的统计信息，直到缓存的统计数据过期。

值得注意的是：MySQL 重新启动或第一次打开 mysql.index_stats
 和 mysql.table_stats
 表不会自动更新缓存的统计信息。

核心参数

核心参数 information_schema_stats_expiry
 默认是 86400 秒。也就是说每隔一天自动收集一次相关统计信息到 information_schema
 中的如下表字段中：

STATISTICS.CARDINALITY
TABLES.AUTO_INCREMENT
TABLES.AVG_ROW_LENGTH
TABLES.CHECKSUM
TABLES.CHECK_TIME
TABLES.CREATE_TIME
TABLES.DATA_FREE
TABLES.DATA_LENGTH
TABLES.INDEX_LENGTH
TABLES.MAX_DATA_LENGTH
TABLES.TABLE_ROWS
TABLES.UPDATE_TIME

参数 information_schema_stats_expiry
 的值决定再次收集表的统计信息的时间间隔，默认 86400 秒。如果设置为 0，每次都会直接从存储引擎的相关内存对象中读取统计信息，当然势必会影响一部分性能。

在以下情况中，查询统计信息列不会在 mysql.index_stats
 和 mysql.table_stats
 字典表中存储或更新统计信息：

  1. 缓存的统计信息尚未过期时。
  2. 当 information_schema_stas_expiry
     设置为 0 时。
  3. 当 MySQL server 处于只读、超级只读、事务只读或 innodb_read_only
     模式时。
  4. 查询获取  Performance Schema
     的数据时。

information_schema_stas_experity
  支持全局和会话级别，每个会话都可以定义自己的过期值。从存储引擎中检索并由一个会话缓存的统计信息可用于其他会话。

2测试

本文以 MySQL 8.0.30 为例，进行分析。

2.1 测试准备

 CREATE TABLE `sbtest1` (
 `id` int NOT NULL AUTO_INCREMENT,
 `k` int NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

2.2  测试

查看 information_schema.tables
 中 sbtest1
 的当前信息，最大值 1200006，表结构定义中自增最大值也是 1200006。

master [localhost:22031] {msandbox} (test) > show variables like 'information_schema_stats_expiry' ;
+-----------------------------------------+---------+
| Variable_name                   | Value |
+-----------------------------------------+---------+
| information_schema_stats_expiry | 86400 |
+-----------------------------------------+---------+
1 row in set (0.02 sec)

master [localhost:22031] {msandbox} (test) > select  table_name, AUTO_INCREMENT  from information_schema.tables where  table_name='sbtest1';
+---------------+--------------------+
| TABLE_NAME | AUTO_INCREMENT |
+---------------+--------------------+
| sbtest1    |        1200006 |
+---------------+--------------------+
1 row in set (0.01 sec)

master [localhost:22031] {msandbox} (test) > show create table  sbtest1 \G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1200006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

插入新的数据，自增值加 1。

master [localhost:22031] {msandbox} (test) > insert into sbtest1(k,c,pad) values(1,'c','cc');
Query OK, 1 row affected (0.00 sec)

master [localhost:22031] {msandbox} (test) > show create table  sbtest1 \G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1200007 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

但是 information_schema.tables
 中的值并未发生变化。

master [localhost:22031] {msandbox} (test) > select  table_name, AUTO_INCREMENT  from information_schema.tables where  table_name='sbtest1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| sbtest1    |        1200006 |
+------------+----------------+
1 row in set (0.00 sec)

设置为实时更新

修改 information_schema_stats_expiry
 为 0。

master [localhost:22031] {msandbox} (test) > set  information_schema_stats_expiry=0;
Query OK, 0 rows affected (0.00 sec)

master [localhost:22031] {msandbox} (test) > show variables like 'information_schema_stats_expiry' ;
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0     |
+---------------------------------+-------+
1 row in set (0.00 sec)

master [localhost:22031] {msandbox} (test) > select  table_name, AUTO_INCREMENT  from information_schema.tables where  table_name='sbtest1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| sbtest1    |        1200007 |
+------------+----------------+
1 row in set (0.00 sec)

master [localhost:22031] {msandbox} (test) > show create table  sbtest1 \G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1200007 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

插入数据自增加 1 ，查询 information_schema.tables
 中的 自增列统计值 也是实时更新。

master [localhost:22031] {msandbox} (test) > insert into sbtest1(k,c,pad) values(1,'c','cc');
Query OK, 1 row affected (0.00 sec)

master [localhost:22031] {msandbox} (test) > select  table_name, AUTO_INCREMENT  from information_schema.tables where  table_name='sbtest1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| sbtest1    |        1200008 |
+------------+----------------+
1 row in set (0.00 sec)

master [localhost:22031] {msandbox} (test) > show create table  sbtest1 \G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1200008 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

手工修改自增列，也是可以实时更新。

master [localhost:22031] {msandbox} (test) > alter table sbtest1  AUTO_INCREMENT=1200010;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost:22031] {msandbox} (test) > select  table_name, AUTO_INCREMENT  from information_schema.tables where  table_name='sbtest1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| sbtest1    |        1200010 |
+------------+----------------+
1 row in set (0.00 sec)

3小结

MySQL 8.0 对于表字段的统计信息提供更多的技术特性来支持。统计有效性时长，字段本身的直方图，使用起来越来越便利。

回过头来看这个需求，其实如果是业务监控或者数据库监控信息比如监控表的主键最大值是否溢出，比较实际的建议还是查询具体的表的最大 id
 值，比较实际，查询频率更可控。

参考资料

[1]

信息统计机制: https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html


如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，




近期更新的文章：
《MySQL主键自增值为什么出现"空洞"的场景》
《show effective grants》
《你知道台风的名称是怎么定义的？》
《搭建知识体系的借鉴和了解》
《MySQL全新版本模型》

近期的热文：
《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》

文章分类和索引：
《公众号1200篇文章分类和索引》


免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论