新特性解读 | MySQL 8.0 字段信息统计机制

2023-08-02 846

作者通过一个案例详细说明了 MySQL 8.0 字段信息统计机制的相关参数和使用方式。

作者：杨奇龙

网名“北在南方”，资深 DBA，主要负责数据库架构设计和运维平台开发工作，擅长数据库性能调优、故障诊断。

本文来源：原创投稿

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

背景

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

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

先说结论：可以！

这里涉及到信息统计机制或者说频率问题，主要由参数 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 字典表中存储或更新统计信息：

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

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

测试

本文以 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)

小结

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

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

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。

SQLE 获取

类型	地址
版本库	https://github.com/actiontech/sqle
文档	https://actiontech.github.io/sqle-docs/
发布信息	https://github.com/actiontech/sqle/releases
数据审核插件开发文档	https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/10092464

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

NSX多租户之旅

从多租户数据面到完整的多租户框架我们很高兴地宣布NSX中的Projects这一项新功能，可以对NSX部署的多个租户进行细粒度的资源管理。 Projects提供灵活的资源分配和管理，将NSX的多租户支持提升到新的水平。企业管理员可以将平台划分为不同Projects，将不同的空间分配给不同的租户，同时保持完全的可见性和控制。NSX消费模式的这一扩展支持NSX用户使用他们自己的对象，看到与他们自己的配置相关的警报，并通过Traceflow测试他们工作负载之间的连接性。这篇文章概述了NSX的新多租户功能，解释了它们是如何从传统的多租户数据面（仍然支持）发展到基于Projects的新多租户框架（管理员可以选择利用）。多租户数据面 - 路由在讨论Projects引入的新的多租户功能之前，我们先来看看传统上在数据平面怎么实现多租户的。 NSX支持多层路由模型，在NSX基础设施内的不同网关之间进行逻辑分离，对服务和政策给予完全的控制和灵活性。这种模式使数据中心的互连简单而稳定，也使复杂的、可能被隔离的应用环境实现自动化。 Tier-0网关在逻辑和物理网络之间提供网关服务。传统上，它被设置为动态...

2023-08-02

766

7月28日，华为云开源团队受邀在北京理工大学举办第二期线下meetup，此次活动从“了解开源和前沿开源技术分享”出发，帮助高校学生了解开源，参与开源，同时通过技术分享与现场实操帮助高校学生加强对前沿开源技术的了解。在本次活动中，我们邀请了开放原子开源基金会专家郭皓为参会学生们做了“探索开源之道”的分享，阐述了开源软件运动的内涵、意义和启示向在校大学生们传递开源的价值与意义，以及如何参与开源等。开放原子开源基金会资深研究院郭皓郭皓在分享中提到：“在开源的道路上，每个人都可以成为改变世界的一部分。在数字新世界中，开源之道在是一种重要的探索方式，它鼓励合作、共享和创新，推动技术的发展和进步。通过开源，人们可以共同创造一个更加开放和包容的数字新世界。” 校园是全球开源软件运动的重要开展地，大学生是尤其重要的中坚力量。开源已经被纳入到国家顶层战略发展规划，大学生应该多参与开源项目，提升技能和知识，丰富经验和开拓视野，提升就业竞争力。在《数据结构和算法在数据库中的应用》议题中，openGemini社区发起人向宇分享了openGemini时序数据库中使用的数据结构和算法，分别从意义、结构、...

2023-08-02

344

资源下载

更多资源

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。