mysql - 平均行长比可能的长

Question

这不是 Why is InnoDB table size much larger than expected? 的副本该问题的答案是，如果我不指定主键，则会向该行添加 6 个字节。我确实指定了一个主键，这里有6个多字节需要说明。

---

我有一个需要数百万条记录的表，所以我密切关注每一列的存储大小。 每行应占用 15 个字节(smallint = 2 个字节，date = 3 个字节，datetime = 8 个字节)

CREATE TABLE archive (
  customer_id smallint(5) unsigned NOT NULL,
  calendar_date date NOT NULL,
  inserted datetime NOT NULL,
  value smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`customer_id`,`calendar_date`,`inserted`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

该表现在有 50 万条记录，占用的存储空间超出预期。我运行此查询以从系统中获取更多详细信息:

SELECT *
  FROM information_schema.TABLES
 WHERE table_name = 'archive';


information_schema.index_length = 0
information_schema.avg_row_length = 37
information_schema.engine = InnoDB
information_schema.table_type = BASE TABLE

如何!？

我原以为每行 15 个字节，但实际占用了 37 个字节。谁能告诉我下一步该去哪里寻找解释？我阅读了很多有关泰语的文章，并且看到了一些关于将额外的 6 或 10 个字节添加到行大小的解释，但这并没有解释额外的 22 个字节。

一种解释是索引也会占用存储空间。此表上没有索引。

一种解释是 information_schema.tables 查询返回了一个不可靠的行数，这会影响 avg_row_length。我已经检查了它针对 count(*) 查询使用的行计数，它只偏离了一点点(1% 的 1/20)，所以这不是全部。

另一种解释是碎片化。值得注意的是，这个表是从 sql 转储重建的，因此没有任何更新、插入和删除的锤击。

Answer 1

• 因为 avg_row_length 是 data_length/rows。

data_length 基本上是磁盘上 表的总大小。 InnoDB 表不仅仅是一个行列表。所以会有额外的开销。

• 因为 InnoDB 行不仅仅是数据。

与上面类似，每一行都有一些开销。所以这将增加一行的大小。 InnoDB 表也不只是塞在一起的数据列表。它需要一点额外的空白空间才能高效工作。

• 因为东西以 block 的形式存储在磁盘上，而这些 block 并不总是满的。

磁盘通常以 4K、8K 或 16K 存储内容 blocks .有时这些 block 中的东西并不完全适合，所以你可以得到 some empty space .

正如我们将在下面看到的，MySQL 将以 block 的形式分配表。并且它将分配比它需要的更多的东西，以避免必须扩大表(这可能很慢并导致 disk fragmentation，这使得事情变得更慢)。

---

为了说明这一点，让我们从一个空表开始。

mysql> create table foo ( id smallint(5) unsigned NOT NULL );
mysql> select data_length, table_rows, avg_row_length from information_schema.tables where table_name = 'foo';
+-------------+------------+----------------+
| data_length | table_rows | avg_row_length |
+-------------+------------+----------------+
|       16384 |          0 |              0 |
+-------------+------------+----------------+

它使用 16K 或四个 4K block 来存储任何内容。空表不需要这个空间，但是MySQL是在假设你要往里面放一堆数据的前提下分配的。这避免了必须对每个插入进行昂贵的重新分配。

现在让我们添加一行。

mysql> insert into foo (id) VALUES (1);
mysql> select data_length, table_rows, avg_row_length from information_schema.tables where table_name = 'foo';
+-------------+------------+----------------+
| data_length | table_rows | avg_row_length |
+-------------+------------+----------------+
|       16384 |          1 |          16384 |
+-------------+------------+----------------+

table 没有变得更大，它拥有的那 4 个 block 中有所有未使用的空间。其中一行表示 avg_row_length 为 16K。显然荒谬。让我们再添加一行。

mysql> insert into foo (id) VALUES (1);
mysql> select data_length, table_rows, avg_row_length from information_schema.tables where table_name = 'foo';
+-------------+------------+----------------+
| data_length | table_rows | avg_row_length |
+-------------+------------+----------------+
|       16384 |          2 |           8192 |
+-------------+------------+----------------+

同样的事情。为表分配了 16K，2 行使用该空间。每行 8K 的荒谬结果。

随着我插入越来越多的行，表的大小保持不变，它使用了越来越多的分配空间，并且 avg_row_length 越来越接近实际情况。

mysql> select data_length, table_rows, avg_row_length from information_schema.tables where table_name = 'foo';                                                                     
+-------------+------------+----------------+
| data_length | table_rows | avg_row_length |
+-------------+------------+----------------+
|       16384 |       2047 |              8 |
+-------------+------------+----------------+

在这里我们也开始看到 table_rows 变得不准确。我肯定插入了 2048 行。

现在当我再插入一些...

mysql> select data_length, table_rows, avg_row_length from information_schema.tables where table_name = 'foo';
+-------------+------------+----------------+
| data_length | table_rows | avg_row_length |
+-------------+------------+----------------+
|       98304 |       2560 |             38 |
+-------------+------------+----------------+

(我插入了 512 行，table_rows 不知为何突然变回了现实)

MySQL 决定表需要更多空间，因此它调整了大小并占用了更多磁盘空间。 avg_row_length 又跳了起来。

它占用的空间比这 512 行所需的空间多得多，现在它是 96K 或 24 个 4K block ，假设它以后会需要它。这最大限度地减少了它需要执行的潜在缓慢重新分配的数量，并最大限度地减少了磁盘碎片。

这并不意味着所有空间都已填满。这只是意味着 MySQL 认为它已满，需要更多空间才能有效运行。如果您想知道为什么会这样，请查看 hash table 是如何实现的。运作。我不知道 InnoDB 是否使用哈希表，但原则适用:某些数据结构在有一些空白空间时运行最佳。

---

一张表使用的磁盘与表中的行数和列的类型直接相关，但是具体的公式很难计算，并且会随着MySQL版本的不同而变化。最好的办法是进行一些实证测试，然后让自己认命，因为您永远不会得到确切的数字。