• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL 8.0 information_schema.tables表和之前版本的差异

MySQL SEian.G 3年前 (2021-12-08) 3456次浏览 已收录 0个评论
文章目录[隐藏]

在做自动化运维开发过程中,需要从information_schema.tables获取MySQL表相关的元信息,发现MySQL8.0和5.7存在的差异还是比较大的;在MySQL8.0以前,通常会通过infomation_schema的表来获取一些元数据,例如从tables表中获取表的下一个auto_increment值,从indexes表获取索引的相关信息等。

但在MySQL8.0去查询这些信息的时候,出现了不准确的情况,例如auto_increment。以及查询出来的列名称从大写变成了小写,例如table_name

#MySQL 5.7版本

<strong>返回的table_name是小写</strong>
mysql>select table_name,auto_increment from information_schema.tables where table_schema='wjqtest' and table_name='test1';    
+------------+----------------+
| table_name | auto_increment |
+------------+----------------+
| test1      |            300 |
+------------+----------------+
1 row in set (0.00 sec)

#MySQL 8.0版本

<strong>返回的table_name是大写</strong>
mysql>select table_name,auto_increment from information_schema.tables where table_schema='wjqtest' and table_name='test1';    
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| test1      |              6 |
+------------+----------------+
1 row in set (0.01 sec)

这是在自动化程序运维中可能会遇到的一个问题;回归正题,我们来看一下,上面information_schema.tables查看元数据的相关问题;

测试示例:

例如:test1表的auto_increment是6

mysql>show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `addr` varchar(30) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

从information_schema.tables查出test1表的auto_increment是6,这时tables表信息是准确的

mysql>select auto_increment from information_schema.tables where table_schema='wjqtest' and table_name='test1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

将test1表的auto_increment修改为300

mysql>alter table test1 auto_increment=300;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

查询tables表,发现auto_increment仍然是6;在MySQL8.0以前,这时tables表的auto_increment应该是显示最新值300的

mysql>show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `addr` varchar(30) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>>select auto_increment from information_schema.tables where table_schema='wjqtest' and table_name='test1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

向test1表插入数据,应用最新的auto_increment

mysql>insert into test1 values('wjq',28,'beijin',null);
Query OK, 1 row affected (0.02 sec)

检查test1表的下一个AUTO_INCREMENT,确实是30·

mysql>show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `addr` varchar(30) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

test1表插入操作以后,再次查询tables表,auto_increment值仍然是6

mysql>select auto_increment from information_schema.tables where table_schema='wjqtest' and table_name='test1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

从tables表看到test1表上一次更新时间是2021-08-19 10:39:26

mysql>select auto_increment,update_time from information_schema.tables where table_schema='wjqtest' and table_name='test1';
+----------------+---------------------+
| AUTO_INCREMENT | UPDATE_TIME         |
+----------------+---------------------+
|              6 | 2021-08-19 10:39:26 |
+----------------+---------------------+
1 row in set (0.00 sec)

mysql>select now();
+---------------------+
| now()               |
+---------------------+
| 2021-12-08 19:53:57 |
+---------------------+
1 row in set (0.00 sec)

对test1表插入数据,这时test1表的update_time应该是当前时间

mysql>insert into test1 values('wjq11',28,'beijin',null);
Query OK, 1 row affected (0.01 sec)

但从tables表查询到update_time仍然没更新

mysql>select auto_increment,update_time from information_schema.tables where table_schema='wjqtest' and table_name='test1';
+----------------+---------------------+
| AUTO_INCREMENT | UPDATE_TIME         |
+----------------+---------------------+
|              6 | 2021-08-19 10:39:26 |
+----------------+---------------------+
1 row in set (0.00 sec)

从以上例子可以看出,MySQL8.0的tables表变得不可靠了。前面文章有说到,MySQL8.0里,tables不再是某个引擎表,而是改造成了视图。再仔细看一下tables视图的定义

mysql>show create table information_schema.tables\G
*************************** 1. row ***************************
                View: TABLES
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TABLES` AS select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,if((`tbl`.`type` = 'VIEW'),NULL,internal_table_rows(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `TABLE_ROWS`,if((`tbl`.`type` = 'VIEW'),NULL,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `AVG_ROW_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `MAX_DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_index_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `INDEX_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_free(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_FREE`,if((`tbl`.`type` = 'VIEW'),NULL,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`)) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_update_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `UPDATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_check_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,if((`tbl`.`type` = 'VIEW'),NULL,internal_checksum(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECKSUM`,if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1),if((`sch`.`default_encryption` = 'YES'),1,0))) AS `CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where ((0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

可以看到,auto_increment和update_time列均引用自mysql.table_stats表。那么tables视图的信息不准确,根本原因就是table_stats表的统计信息并没有实时更新。

解决统计信息过旧的问题,从以往的经验来看,当表数据更新占比达到一定数值,就会触发统计信息收集。所以尝试了不断插入和更新test表,但tables视图的信息仍然是不准确的,也就说明table_stats的统计信息根本没有更新。

当然是使用analyze table命令去人为的触发表信息收集,tables视图的信息会更新至当前准确的状态。

但如果总是要analyze table命令去人为更新才能得到真实的数据,那么tables表存在的意义何在?

在MySQL8.0,数据字典方面做了不少的改动。本文就不详细介绍所有的知识点,关于MySQL 8.0数据字典相关内容详细参考文章《MySQL 8.0新特性: 数据字典》。针对tables视图等不准确的情况,其实是跟数据字典表和其数据缓存有关系。

数据字典有很多相关的表,但这些表是不可见的。既不能通过select来获取表数据,也不能通过show tables看到它的踪影,同样也不会出现在information_schema.tables的table_name范畴里。但是,大部分数据字典表会有相关的视图来获取它的数据,例如tables表相关的视图是information_schema.tables,当然,从information_schema.tables的定义看,也不是一对一的关系,其中还包含其他表的数据。

数据字典表用来做什么呢,还记得.frm,db.opt这些文件吗?在MySQL8.0里,你会发现这些文件都没有了。原本记录在这些文件中的元数据,现在记录就记录在数据字典表里,而数据字典表集中存在一个单独的innodb表空间中,系统文件名为mysql.ibd,也就是说,元数据不再是直接在.frm等文件上读写,而是存在存储引擎上。

为了最小化磁盘IO,MySQL8.0增加了一个字典对象缓存(dictionary object cache)。同时为了提高information_schema的查询效率,statistics和tables字典表的数据缓存在字典对象缓存中,并且有一定的保留时间,如果没超过保留时间,即使是实例重启,缓存中的信息也不会更新,只有超过了保留时间,才会到存储引擎里抓取最新的数据。同时,字典对象缓存采用LRU的方式来管理缓存空间。

那么到这里,information_schema.tables视图不准确的疑问就解开了,原因即是字典对象缓存中统计信息并没有更新,那么怎么解决呢?

可以通过设置information_schema_stats_expiry为0来使字典对象缓存实时更新,该参数默认值为86400,即24小时。如果没有缓存的统计信息或统计信息已过期,则在查询表统计信息列时将从存储引擎检索统计信息。

问题解决了,那么来捋一捋,都有哪些情况下,字典缓存中索引和表的统计信息不会自动更新呢?

1.缓存中统计信息还没过期;

2.information_schema_stats_expiry没设成0;

3.当实例在read_only相关模式下运行;

4.当查询同时获取performance schema的数据。

针对第一二点,可以通过设置set global information_schema_stats_expiry=0来解决,也可以仅在会话级设置;针对以上问题,除了第三点,都可以通过analyze table来解决。

注意

如果innodb_read_only启用了系统变量,则ANALYZE TABLE可能会失败,因为它无法更新使用的数据字典中的统计表 InnoDB。对于ANALYZE TABLE更新密钥分配的操作,即使该操作更新了表本身(例如,如果它是MyISAM表),也可能会发生故障。要获取更新的分发统计信息,请设置 information_schema_stats_expiry=0。

MySQL 8.0 information_schema.tables表和之前版本的差异


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL 8.0 information_schema.tables表和之前版本的差异
喜欢 (2)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址