1. 背景介绍
测试环境中,有一个表执行 SHOW TABLE STATUS
时看到的 rows
结果总是和真实数量相差了将近40%:
-- 执行SHOW TABLE STATUS,看到Rows只有约655万行数据
greatsql> SHOW TABLE STATUS LIKE 't1'\G
*************************** 1. row ***************************
Name: t1
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-03-01 15:04:31
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_bin
Checksum: NULL
Create_options:
Comment:
-- 执行COUNT(*)看到实际有1000万行数据
greatsql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
-- 表结构如下
greatsql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`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) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`A18` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`A19` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`A20` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`A21` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`A22` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`A23` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`A24` varchar(2