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

不念不忘少年蓝@ 2023-02-16 04:17 56阅读 0赞

> 踩坑了吗？

1. 问题描述2. 问题探究3. 总结4. 延伸阅读

# 1. 问题描述 #

前几天，QQ群里在讨论一个关于MySQL表统计信息迟迟不更新的问题。  
这个问题我复现了，下面是详细过程：

# 创建一个空表
    [root@yejr.run]>create table ttxx like t1;
    
    # 第一次执行 show table status，看到 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
     Avg_row_length: 0
        Data_length: 16384
    Max_data_length: 0
       Index_length: 16384
          Data_free: 0
     Auto_increment: 1
        Create_time: 2020-06-04 16:17:54
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.00 sec)
    
    # 写入将近80万条数据
    [root@yejr.run]>insert into ttxx select id,name,c1 from t1;
    Query OK, 799994 rows affected (8.25 sec)
    Records: 799994  Duplicates: 0  Warnings: 0
    
    # 再次执行 show table status，发现 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
     Avg_row_length: 0
        Data_length: 16384
    Max_data_length: 0
       Index_length: 16384
          Data_free: 0
     Auto_increment: 1
        Create_time: 2020-06-04 16:17:54
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options:
            Comment:

简言之，就是执行 `SHOW TABLE STATUS` 时**无法及时查看到该表的最新统计信息**。

但与此同时，直接查看 `mysql.innodb_table_stats` 和 `mysql.innodb_index_stats` 两个表，却又可以看到该表的统计信息已经更新了：

尝试跑一个SQL观察执行计划，看起来也是正确的：

此时再执行 `SHOW TABLE STATUS` 依然无法看到信息更新。

到底肿么回事呢？

# 2. 问题探究 #

作为老司机（踩坑大户），首先想到的就是检查官方手册。  
MySQL官方手册的描述中，有这么一段内容：

• Rows
    
    The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
    
    The Rows value is NULL for INFORMATION_SCHEMA tables.
    
    For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

简言之，就是说MyISAM表的Rows是精确值，但InnoDB表则只是大概值，甚至有可能只是真实值的40% ~ 50% 之间。

另外，这个信息是从 `INFORMATION_SCHEMA.TABLES` (下面简称IFS.TABLES)里获取的：

Table information is also available from the INFORMATION_SCHEMA TABLES table. See Section 25.36, “The INFORMATION_SCHEMA TABLES Table”.

那我们再看看文档中关于 `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. 总结 #

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

# 4. 延伸阅读 #

*  The INFORMATION\_SCHEMA TABLES Table，https://dev.mysql.com/doc/refman/8.0/en/tables-table.html
 *  SHOW TABLE STATUS Statement，https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html
 *  sys var information\_schema\_stats\_expiry，https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html\#sysvar\_information\_schema\_stats\_expiry

enjoy MySQL 8.0 :)

全文完。

--------------------

*  由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
    
    ![format_png][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码直接享受组团价
    
    ![format_png 1][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeEx5ajlRdWJEY0hQeUlwemgwaWNXSVpnTm96aFo3TmxDbW53MkU1ekpqUzZTTjE0TENaR0JvUW04WGliVHkzZDFtaWFqYWlhQVhTcTFTVVEvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUek5xdmliY3IwZmJDQkU3MXBoMzF5aWJ5TzNFOVJONnlLd0NYd1R6cVZmc1ZxM2RxUUFpYWRoUDlGSkVXOUluNlk0Mnl4ZTVUUWVuMm9BUS82NDA?x-oss-process=image/format,png