MySQL8.0的information_schema.tables信息不准确怎么办

蔚落 2022-04-13 11:15 539阅读 0赞

在MySQL8.0以前，通常会通过infomation\_schema的表来获取一些元数据，例如从tables表中获取表的下一个auto\_increment值，从indexes表获取索引的相关信息等。

但在MySQL8.0去查询这些信息的时候，出现了不准确的情况。例如auto\_increment，

--此时test表的auto_increment是204
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)
    
    --从information_schema.tables查出test表的auto_increment是204，这时tables表信息是准确的
    mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';                                   
    +----------------+
    | AUTO_INCREMENT |
    +----------------+
    |            204 |
    +----------------+
    1 row in set (0.01 sec)
    --将test表的auto_increment修改为300
    mysql> alter table test auto_increment=300;
    Query OK, 0 rows affected (0.10 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    --查询tables表，发现auto_increment仍然是204；在MySQL8.0以前，这时tables表的auto_increment应该是显示最新值300的
    mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';
    +----------------+
    | AUTO_INCREMENT |
    +----------------+
    |            204 |
    +----------------+
    1 row in set (0.00 sec)
    
    --向test表插入数据，应用最新的auto_increment
    mysql> insert into test values();
    Query OK, 1 row affected (0.02 sec)
    
    --检查test表的最大值，确实是300
    mysql> select max(id) from test;
    +---------+
    | max(id) |
    +---------+
    |     300 |
    +---------+
    1 row in set (0.00 sec)
    
    --test表插入操作以后，再次查询tables表，auto_increment值仍然是204
    mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';
    +----------------+
    | AUTO_INCREMENT |
    +----------------+
    |            204 |
    +----------------+
    1 row in set (0.00 sec)

又例如一个表的更新时间

--从tables表看到test表上一次更新时间是2018-11-29 09:12:48
    mysql> select update_time from information_schema.tables where table_schema='test' and table_name='test';              
    +---------------------+
    | UPDATE_TIME         |
    +---------------------+
    | 2018-11-29 09:12:48 |
    +---------------------+
    1 row in set (0.00 sec)
    
    
    mysql> select sysdate();
    +---------------------+
    | sysdate()           |
    +---------------------+
    | 2018-11-29 09:21:49 |
    +---------------------+
    1 row in set (0.00 sec)
    
    --对test表插入数据，这时test表的update_time应该是当前时间
    mysql> insert into test values();
    Query OK, 1 row affected (0.09 sec)
    
    mysql> select sysdate();
    +---------------------+
    | sysdate()           |
    +---------------------+
    | 2018-11-29 09:22:02 |
    +---------------------+
    1 row in set (0.00 sec)
    
    --但从tables表查询到update_time仍然没更新
    mysql> select update_time from information_schema.tables where table_schema='test' and table_name='test';
    +---------------------+
    | UPDATE_TIME         |
    +---------------------+
    | 2018-11-29 09:12:48 |
    +---------------------+
    1 row in set (0.00 sec)

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

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`,
    internal_table_rows(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_max_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_index_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_data_free(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_auto_increment(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_update_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,
    internal_check_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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`,internal_checksum(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`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))) 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 (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))

可以看到，auto\_increment和update\_time列均引用自`mysql`.`table_stats`表。那么tables视图的信息不准确，根本原因就是table\_stats表的统计信息并没有实时更新。

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

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

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

对此，做一番研究。

原来在MySQL8.0，数据字典方面做了不少的改动。本文就不详细介绍所有的知识点，后续文章中再讲述。针对tables视图等不准确的情况，其实是跟数据字典表和其数据缓存有关系。

数据字典有很多相关的表，但这些表是不可见的。既不能通过select来获取表数据，也不能通过show tables看到它的踪影，同样也不会出现在information\_schema.tables的table\_name范畴里。例如跟库表有关的数据字典tables（注意，此tables跟information\_schema.tables并不是同一个东西），在归属于mysql库下，但就算有火眼金睛也不能让它显形：

mysql> use mysql;
    Database changed
    mysql> show tables like 'table';
    Empty set (0.01 sec)

但是，大部分数据字典表会有相关的视图来获取它的数据，例如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来解决。