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

SEian.G

发布于 2021-12-13 10:46:58

1.7K0

发布于 2021-12-13 10:46:58

在做自动化运维开发过程中，需要从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版本

返回的table_name是小写
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版本

返回的table_name是大写
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，确实是301

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。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-12-09，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库