mysql只有information_schema_validationquery not set

全栈程序员站长

发布于 2022-09-30 15:25:47

7680

发布于 2022-09-30 15:25:47

文章被收录于专栏：全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。

在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来解决。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/192798.html原文链接：https://javaforall.cn

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022年9月16日，如有侵权请联系 cloudcommunity@tencent.com 删除

缓存