database - smallint 或 character(10) 哪个更有效？

Question

我在一个表中存储了 5000 本书，该表包含标题、作者、年份和 ISBN。现在我正在制作一张书评表。哪个更有效或者更好的方法是在书籍表中为 id 创建一个列并使用该 id 将书籍评论存储在评论表中，或者使用书籍的 ISBN 号存储为一个字符(10)？

当我说“高效”时，我的意思是“节省存储空间”。

Answer 1

我想说的是，如果表经过精心设计，添加一个人工 smallint 主键在存储空间方面会更便宜。

smallint 占用 2 个字节，而 character(10)(与直觉相反，varlena)包含 ASCII 字符, 将消耗 14 个字节。

在表中，多出的 2 个字节会被浪费掉，但不要忘记，您将在主键列上建立索引。所以索引值实际上会存储两次:一次在表中，一次在索引中。

为简单起见，让我们忽略元组 header 和其他开销。

• 使用 ISBN 作为主键会导致每个表行额外占用 14 个字节。

• 添加 smallint 主键会向表中添加两个字节，向索引中添加两个字节，总共添加四个字节。

所以添加一个smallint主键应该可以节省空间。

你不应该忽视对齐问题。所有数据类型都存储在内存地址中，这些地址是某些 2 的幂的倍数。这是处理器架构所要求的。 smallint 通常有 2 位对齐，character 有 1 位对齐，而例如 timestamp 有 8 位对齐。

所以如果你的表定义为

CREATE TABLE book (
   id smallint PRIMARY KEY,
   issue_time timestamp with time zone,
   isbn character(10)
);

然后表格数据看起来像这样:

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| | |X|X|X|X|X|X| | | | | | | | | ... (ISBN omitted)
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
 id    padding     issue_time

该行以 8 字节边界对齐，从 id 末尾到 issue_time 开头的六个字节将是空的“填充字节”。

因此，要充分利用它，您必须考虑定义列的顺序。

为什么这一切在现实中不是很相关:

无论如何，包含 5000 或 10000 个条目的表都很小。

尽管在这里优化空间，但充其量只是不必要的微优化。

但是在计划表上可能是一个聪明的想法很容易在以后适得其反:如果——与你的预期不同——你想在表中存储 70000 本书，你会发现一个 smallint 是不够的，即使你允许负的 id秒。当您必须更改主键的数据类型以及在实时系统中引用它的所有外键时，您将不得不忍受的痛苦将远远超过您通过巧妙的优化节省大约 100 KB 所获得的任何乐趣。