哪种更有效的smallint或character(10)? [英] Which is more efficient smallint or character(10)?
问题描述
我要在一个表中存储5000本书，该表包含标题，作者，年份和ISBN.现在，我正在准备一张桌子，供您查看这些书.这样做会更高效或更有效，可以在图书表中为ID创建一列，然后使用该ID在评论表中存储图书的评论，或者使用存储为一个字符(10个)?
I'm storing 5000 books in a table, the table contains titles, authors, years and ISBN. Now I'm making a table for reviews of the books. Which would be more efficient or just the better way to do it, create a column for ids in the books' table and use that id to store reviews of the books in the reviews table, or use the books' ISBN number which is stored as a character(10)?
当我说有效"时，是指节省存储空间".
When I say "efficient" I mean "conserving storage space".
推荐答案
我要说的是，如果表经过精心设计，则在存储空间方面添加人为的 smallint 主键会更便宜
I'd say that adding an artificial smallint primary key would be cheaper in terms of storage space, if the table is carefully designed.
smallint 占用2个字节，而包含ASCII字符的 character(10)(即与之相反的 varlena )，将占用14个字节.
A smallint takes 2 bytes, while a character(10) (which is, counter-intuitively, a varlena) containing ASCII characters, will consume 14 bytes.
在表中，多余的2个字节将被浪费，但不要忘记您将在主键列上有一个索引.因此，索引值实际上将存储两次:一次在表中，一次在索引中.
In the table, the extra 2 bytes would be waste, but don't forget that you will have an index on the primary key column. So the indexed value will actually be stored twice: once in the table, once in the index.
为简单起见，让我们忽略元组标头和其他开销.
For simplicity's sake, let's ignore tuple headers and other overhead.
-
使用ISBN作为主键将使每个表行额外花费14个字节.
Using the ISBN as primary key will cost an extra 14 bytes per table row.
添加 smallint 主键将在表中添加两个字节，在索引中添加两个字节，总共增加了四个字节.
Adding a smallint primary key will add two bytes to the table and two to the index, making a total of four added bytes.
因此添加 smallint 主键应节省空间.
So adding a smallint primary key should save space.
您不应忽略对齐问题.所有数据类型都存储在内存地址中，该内存地址是某些2的幂的倍数.这是处理器的体系结构所必需的. smallint 通常具有对齐方式2，字符具有对齐方式1，而例如 timestamp 具有对齐方式8.
You should not ignore alignment issues. All data types are stored at memory addresses that are multiples of certain powers of two. This is required by the processors' architectures. A smallint typically has alignment 2, character has alignment 1, while for example timestamp has alignment 8.
因此，如果您的表定义为
So if your table is defined as
CREATE TABLE book (
id smallint PRIMARY KEY,
issue_time timestamp with time zone,
isbn character(10)
);
然后表数据将如下所示:
Then the table data would look like this:
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| | |X|X|X|X|X|X| | | | | | | | | ... (ISBN omitted)
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
id padding issue_time
该行在8字节边界处对齐，并且从 issue_time 的开头的六个字节将为空填充字节".
The row is aligned at an 8-byte boundary, and the six bytes from the end if id to the beginning of issue_time will be empty “padding bytes”.
因此，要充分利用它，您必须考虑按什么顺序定义列.
So to make the most of it, you'll have to consider in which order you define the columns.
为什么实际上所有这些都不是很重要:
具有5000或10000个条目的表无论如何都是很小的.
A table with 5000 or 10000 entries is tiny, no matter what.
尽管花在优化空间上的任何事情最多都是不必要的微优化.
Any though spent on optimizing space here is at best unnecessary micro-optimization.
但是在计划表上可能有一个聪明的主意，以后很容易适得其反:如果–与您的期望有所不同如果您想在表格中存储7万本图书，即使您允许使用负的 id ，您也会发现 smallint 是不够的.当您必须更改主键的数据类型，而在实时系统中引用该主键的所有外键时，所要承受的痛苦将远远超过通过巧妙的优化节省大约100 KB所获得的乐趣.
But what may be a smart idea on the planning table can easily backfire later:
If – different from what you expect – you want to store 70000 books in the table, you will find that a smallint won't be enough, even if you allow negative ids. The pain you will have to endure when you have to change the data type of a primary key and all foreign keys that reference it in a live system will by far outweigh any pleasure you get from saving some 100 KB by clever optimizations.
这篇关于哪种更有效的smallint或character(10)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
