活久見，為什麼SHOW TABLE STATUS總是不更新11. 問題描述2. 問題探究

1. 問題描述

2. 問題探究

3. 總結

4. 延伸閱讀

前幾天，QQ群裡在讨論一個關于MySQL表統計資訊遲遲不更新的問題。

這個問題我複現了，下面是詳細過程：

# 建立一個空表
[[email protected]]>create table ttxx like t1;

# 第一次執行 show table status，看到 Rows = 0，沒問題
[[email protected]] [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萬條資料
[[email protected]]>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，并且過了幾秒鐘後多執行幾次，結果依然如此
[[email protected]] [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

兩個表，卻又可以看到該表的統計資訊已經更新了：

[[email protected]] [test]>select * from mysql.innodb_table_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | ttxx       | 2020-06-04 16:18:24 | 795064 |                 2788 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+

[[email protected]] [test]>select * from mysql.innodb_index_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_diff_pfx01 |     795064 |          20 | aid                               |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_leaf_pages |       2764 |        NULL | Number of leaf pages in the index |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | size         |       2788 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+      

嘗試跑一個SQL觀察執行計劃，看起來也是正确的：

[[email protected]] [test]>desc select count(*) from ttxx;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | ttxx  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 795064 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+      

此時再執行

SHOW TABLE STATUS

依然無法看到資訊更新。

到底腫麼回事呢？

作為老司機（踩坑大戶），首先想到的就是檢查官方手冊。

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 表。