为什么SHOW TABLE STATUS显示Rows少了40%

老叶茶馆

发布于 2024-03-12 08:30:03

970

发布于 2024-03-12 08:30:03

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(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `A25` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `A26` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `A27` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `A28` varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `A29` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`A0`),
  KEY `a0` (`A0`)
) ENGINE=InnoDB;

如上，表统计信息中看到的行数和实际相差了34%，这个差距太大了，且无论执行多少次 ANALYZE TABLE 更新统计信息，这个问题依然存在。

2. 排查过程

首先想到的是之前遇到过的另一个问题：为什么SHOW TABLE STATUS总是不更新，于是尝试修改 information_schema_stats_expiry = 0，发现问题依旧。


greatsql> SET information_schema_stats_expiry=0;
greatsql> SHOW TABLE STATUS LIKE 't1'\G
...
           Rows: 6553584
...

又想到了可能是因为这个表是用DTS工具从Oracle迁移过来的，之前遇到过另一个问题：MySQL批量导入数据时，为何表空间膨胀了N倍。于是做如下尝试：

greatsql> CREATE TABLE t2 LIKE t1;
greatsql> INSERT INTO t2 SELECT * FROM t1 ORDER BY A0;
greatsql> ANALYZE TABLE t2;
greatsql> SHOW TABLE STATUS LIKE 't2'\G
...
           Rows: 6553464
...

问题依旧存在，看来也不是这个原因。

换个姿势查看表统计信息：

greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='test' AND table_name='t1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | t1         | PRIMARY    | 2024-03-01 17:19:20 | n_diff_pfx01 |    6782959 |          20 | A0                                |
| test          | t1         | PRIMARY    | 2024-03-01 17:19:20 | n_leaf_pages |    3276792 |        NULL | Number of leaf pages in the index |
| test          | t1         | PRIMARY    | 2024-03-01 17:19:20 | size         |    3750272 |        NULL | Number of pages in the index      |
| test          | t1         | a0         | 2024-03-01 17:19:20 | n_diff_pfx01 |    9992226 |          20 | A0                                |
| test          | t1         | a0         | 2024-03-01 17:19:20 | n_leaf_pages |       8315 |        NULL | Number of leaf pages in the index |
| test          | t1         | a0         | 2024-03-01 17:19:20 | size         |       9514 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

注意到 sample_size=20，那么有没可能是采样page数太低，导致统计结果不准呢，之前也有过类似案例：为什么要关注索引统计误差 以及 细说ANALYZE TABLE。继续做尝试：

greatsql> ALTER TABLE t1 STATS_SAMPLE_PAGES=100 STATS_PERSISTENT=1 STATS_AUTO_RECALC=1;

如上，调大 sample_size=100，再次更新统计信息后查看，问题依旧 ~~~：

greatql> ANALYZE TABLE t1;
greatql> SHOW TABLE STATUS LIKE 't1'\G
...
           Rows: 6721792
...

不过看起来 Rows 数值还是涨了点，看来是有一定关系，但不是关键性的。之后甚至调大 sample_size=3000（约为总pages数量的0.1%），结果还是不理想，再次铩羽。

回过头来，重新认真审视表统计信息，突然像发现新大陆似的，似乎找到了一丝端倪，这个表的行平均长度（ Avg_row_length）很大，这很容易造成行存储溢出以及产生过多碎片（简单粗暴地说，就是当Avg_row_length>=8000就会发生存储overflow），参考以往案例：浅析InnoDB Record Header及page overflow。有没可能是这个原因呢？说干就干，尝试删除几个存储大对象数据的列：

-- 统计各列存储长度
greatsql> SELECT LENGTH(A19), LENGTH(A20), LENGTH(A25), LENGTH(A26), LENGTH(A27), LENGTH(A28), LENGTH(A29) FROM t1 LIMIT 1;
+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| LENGTH(A19) | LENGTH(A20) | LENGTH(A25) | LENGTH(A26) | LENGTH(A27) | LENGTH(A28) | LENGTH(A29) |
+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
|         250 |        1000 |         295 |         395 |         495 |         595 |         599 |
+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

-- 根据实际存储长度，逐一删除几个大对象列
-- 先删除A20列，看起来确实有效果
greatsql> ALTER TABLE t1 DROP A20; ANALYZE TABLE t1; SHOW TABLE STATUS LIKE 't1'\G
...
           Rows: 7343082
 Avg_row_length: 6250
...

-- 继续，删除A29列，效果更好了
greatsql> ALTER TABLE t1 DROP A29; ANALYZE TABLE t1; SHOW TABLE STATUS LIKE 't1'\G
...
           Rows: 7838236
 Avg_row_length: 4687
...

按照上面的做法，逐一删除存储长度超过255字节的字符串列：A20、A29、A28、A27、A26、A25、A19，之后查看表统计信息：

greatsql> SHOW TABLE STATUS LIKE 't1'\G
...
           Rows: 10324539
 Avg_row_length: 616
...

这时的统计信息看起来就基本准确了，并且还看到 Avg_row_length 相比原来也减小了很多（从最开始的9375降到616），问题真正的症结就在于此。

3. 原因分析

上面我们说过，因为数据表中有多个大对象列，造成行数据存储溢出（overflow），并产生了很多碎片，导致InnoDB在做统计信息采样分析时，无法获得相对准确的统计信息。从上面挨个删除各大对象列的过程中也能感受到统计信息逐步趋于准确的变化。

在所有的数据库开发规范中，都会建议不要在数据库中存储大对象数据类型，如果真的有需要，也应该把这些大对象列从主表中分离出去，再利用主键进行关联即可。在很多年前我还作为游戏业务DBA时，就优化过类似的案例，仅仅只是把数个大对象列从主表分离出去，分别存储在多个子表中，各表的存储空间消耗总和相比原来降低了75%，仅为原来的25%，可见这个影响有多大。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2024-03-01，如有侵权请联系 cloudcommunity@tencent.com 删除

status