为什么SHOW TABLE STATUS 不更新
本帖最后由 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>
CREATE TABLE `ds_test_table` (
`A0` bigint NOT NULL,
`A1` double DEFAULT NULL,
`A2` decimal(40,0) DEFAULT NULL,
`A3` double DEFAULT NULL,
`A4` decimal(5,2) DEFAULT NULL,
`A5` smallint DEFAULT NULL,
`A6` int DEFAULT NULL,
`A7` bigint DEFAULT NULL,
`A8` decimal(19,0) DEFAULT NULL,
`A9` decimal(38,0) DEFAULT NULL,
`A10` decimal(40,0) DEFAULT NULL,
`A11` datetime DEFAULT NULL,
`A12` datetime(6) DEFAULT NULL,
`A13` datetime DEFAULT NULL,
`A14` datetime(3) DEFAULT NULL,
`A15` datetime(6) DEFAULT NULL,
`A17` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
`A18` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
`A19` char(255) COLLATE utf8mb4_bin DEFAULT NULL,
`A20` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
`A21` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
`A22` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`A23` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
`A24` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
`A25` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
`A26` varchar(400) COLLATE utf8mb4_bin DEFAULT NULL,
`A27` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
`A28` varchar(600) COLLATE utf8mb4_bin DEFAULT NULL,
`A29` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`A0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
INFORMATION_SCHEMA.TABLES表中的TABLE_ROWS本来就是不准确的，与实际值相差可达 40 到 50% 问题分析过程已整理成文章：为什么SHOW TABLE STATUS显示Rows少了40% https://mp.weixin.qq.com/s/QMZ7O0gfs81zXo69F1bHdQ
页:
[1]