[已解决] 为什么SHOW TABLE STATUS 不更新

205 2 2024-2-28 18:20

本帖最后由 vatebur 于 2024-2-28 18:26 编辑

表数据1千万条，以下方法都试过了，information_schema.tables里的行数还是不对（7百万）
1.optimize table
2.ANALYZE TABLE
3.innodb_stats_on_metadata + SELECT count(*)
4. information_schema_stats_expiry

error_log里没看到相关的日志

mysql> use ds4;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW VARIABLES LIKE 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> set session information_schema_stats_expiry = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show table status like 'ds_test_table'\G
*************************** 1. row ***************************
Name: ds_test_table
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 6553584
Avg_row_length: 9375
Data_length: 61444456448
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2024-02-28 04:28:13
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> SELECT count(*) FROM ds4.ds_test_table;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.11 sec)
mysql>
mysql> ANALYZE TABLE ds4.ds_test_table;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| ds4.ds_test_table | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> show table status like 'ds_test_table'\G
*************************** 1. row ***************************
Name: ds_test_table
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 6553584
Avg_row_length: 9375
Data_length: 61444456448
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2024-02-28 04:28:13
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select /* set_var(information_schema_stats_expiry = 1) */ * from information_schema.tables where table_schema='ds4' and table_name = 'ds_test_table'
-> ;
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def | ds4 | ds_test_table | BASE TABLE | InnoDB | 10 | Dynamic | 6553584 | 9375 | 61444456448 | 0 | 0 | 4194304 | NULL | 2024-02-28 04:28:13 | NULL | NULL | utf8mb4_bin | NULL | | |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0 |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql>