活久见,为什么SHOW TABLE STATUS总是不更新2


那我们再看看文档中关于 IFS.TABLES 的描述吧:


25.36 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.


看到这里,真相基本上呼之欲出了。

IFS.TABLES表中看到的数据是有cache的,默认cache时长是 86400秒(即1天),修改参数 information_schema_stats_expiry 即可调整时长。也就是说,除非cache过期了,或者手动执行 ANALYZE TABLE 更新统计信息,否则不会主动更新。

这个参数(功能)是MySQL 8.0后新增的,所以这个问题在8.0之前的版本不存在。

参数 information_schema_stats_expiry 还影响其 IFS.STATISTICS 表。

此外,该参数还可以在session级动态修改。

我们尝试修改session级配置:



[root@yejr.run]>set session information_schema_stats_expiry = 0;

# 修改完后就可以看到Rows数据变了
[root@yejr.run]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 795064
 Avg_row_length: 57
...

[root@yejr.run]>set session information_schema_stats_expiry = 86400;
# 把session配置改回默认值,尴尬的发现Rows值又恢复成0了
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
...

看来,如果应用程序中有需要读取 table status 概要信息的时候,最好还是先手动执行 ANALYZE TABLE 或者修改参数值,也可以用下面这样的SQL:

select /* set_var(information_schema_stats_expiry = 1) */ * from information_schema.tables where table_schema='test' and table_name = 'ttxx'\G

这是MySQL 8.0后新增的HINT语法。

另外,文档中还有一段注释:

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

意思是,当启用参数 innodb_read_only 后再执行 ANALYZE TABLE 就会失败,哪怕要更新统计信息的表是MyISAM引擎,因为所有InnoDB表都被设置为只读,更新统计信息后无法回写到对应的InnoDB字典表里了。

3. 总结

遇到诡异问题时,总是习惯性地先去查阅官方手册,通常都是可以得到答案的,耐心点,再耐心点。

上一篇:SQL点滴6—“微软不认识闰年2月29日”&字符"N"的作用


下一篇:[moka同学笔记]yii2.0查询数据库