主页 / dba / 问题 / 274563
Accepted
Majid
Asked: 2020-08-29 08:43:04 +0800 CST

使用 BIGINT 和两倍的存储空间或 INTEGER 但有额外的操作？

  • 772

假设我有一个agency用一些列命名的表：

  internal_id(integer, unique)
, external_id(bigint, unique)
, name, location, created_at, ...

internal_id并且external_id每个都是唯一的并且可以作为主键列。

还有一些其他表（说A, B, C, D, E）引用了这个表。假设这些表中的每一个都可能包含数百万或数十亿行。

通常我有external_id需要过滤表A, B, C, D, E数据的时候。

考虑到性能和存储空间，以下哪种方案是最好的选择：

  1. 在中用internal_id作主键agency，在其他表中用作外键。因为这个字段占用了 4 个字节的存储空间，我们可以节省数十亿字节。但是，正如我通常拥有的那样external_id，我必须为每个查询做一个额外JOIN的惩罚：
    SELECT A.* FROM A
         INNER JOIN agency ON A.internal_id=agency.internal_id
    WHERE agency.external_id=5;
  1. 在中用internal_id作主键agency，在其他表中用作外键。但是为了摆脱额外的JOIN，在我的应用程序中，我可以首先使用一个简单的查询 ( ) 映射external_id到，然后将获取的用于另一个简单的查询：internal_idSELECT internal_id FROM agency WHERE external_id=5internal_id
    SELECT * FROM A
    WHERE internal_id=59; -- 59 is the fetched internal_id from the other query

它是否比JOIN考虑应用程序和数据库之间的额外往返具有更好的性能？

  1. 忘记internal_id并将其用external_id作主键和外键，每个表中的每条记录都会增加 4 个额外字节A, B, C, D, E（
    SELECT * FROM A
    WHERE external_id=5

更新：

  • agency表可能包含成千上万或最多几百万行。
  • internal_id并且external_id不会随着时间的推移而改变，但其他非身份列可能很少改变。
  • 大约有 5 到 7 个相关表A, B, C, D, E, ...（
postgresql performance

2 个回答

  1. 您概述的原因的第三个选项：您不必agency每次都查询。对于返回少量行的查询来说，连接/查找并不是特别昂贵，而是：

    • 您的数据库更易于使用
    • 您的代码更简洁，更易于调试
    • 更容易优化返回多于几行的查询
    • internal_id避免了agency桌上无意义的开销。

    这当然值 4 个字节/行。我们不再将数据存储在磁带上，它不像以前那样需要考虑。

    你是对的，但是当相关表包含数万亿行时呢？所以对于 1 万亿行，它需要更多的 4TB 数据。它不会影响数据库的整体性能吗？

    如果您正在阅读整个表格，是的。但大多数时候，我们最多只查找几十/几百行。为什么要扩展到“数万亿”行？如果您正在处理该卷，则所需的硬件不会因为堆中额外的 4TB 而损坏。

    • 2
  2. Best Answer

    假设

    • agency行数少于您为其他表提到的“数百万和数十亿”。远低于范围integer：-2147483648 到 +2147483647。否则我们需要bigint开始internal_id。

    • 但是agency还是很大的。否则，不要理会下面的索引优化。

    • 两者都internal_id几乎external_id没有改变。

    • ID 值大致均匀分布。不少非常常见的机构和许多非常罕见的机构。（这可能有利于没有键转换的查询优化。）

    我会考虑使用这种查询样式的方案 1 和 2的组合：

    SELECT *
FROM   A
WHERE  internal_id = (SELECT internal_id FROM agency WHERE external_id=5);

    子查询封装了键翻译，可以用作提供文字的替代品internal_id。当涉及许多连接时，也使查询计划器的工作更简单一些。

    除非您internal_id为许多后续查询重复使用，否则单独的查找会不必要地增加单独往返服务器的成本。

    您可以将关键翻译封装在一个简单的 SQL 函数中：

    CREATE FUNCTION public.f_ext2int(_external_id bigint)
 RETURNS int
 LANGUAGE sql STABLE PARALLEL SAFE AS
'(SELECT internal_id FROM public.agency WHERE external_id = _external_id)';

    那么上面的查询就变成了：

    SELECT * FROM A WHERE internal_id = f_ext2int(5);

    该函数可以由查询计划器“内联”。看：

    我建议这个表定义：

    CREATE TABLE agency (
  internal_id integer          -- PK is NOT NULL implicitly
, external_id bigint NOT NULL  -- NOT NULL, right?
-- , name, location, created_at, ...
, PRIMARY KEY (internal_id) INCLUDE (external_id)
, UNIQUE      (external_id) INCLUDE (internal_id)
);

    这提供了关键索引(internal_id, external_id)并强制执行您提到的约束，而没有冗余索引(external_id, internal_id) 。

    第二个 ( UNIQUE (external_id) INCLUDE (internal_id)) 用于反向查找。似乎你也需要那个。否则，您可以跳过INCLUDE那里的子句。为什么我们需要两个索引？看：

    它大量使用覆盖索引（Postgres 11 或更高版本）。看：

    除其他外，覆盖索引否定了附加列的压载，以agency实现键转换。

    有了这些索引，键转换就可以快速进行仅索引扫描以进行键转换。在查询大型表的上下文中，成本几乎可以忽略不计。

    这为每个额外的表和索引节省了“数百万和数十亿”乘以 4 个字节（这可能更重要）。诚然，存储一直在变得更便宜，但 RAM（和快速缓存！）通常仍然有限。更大的表和索引意味着更少的数据可以保留在缓存中。这对性能至关重要。

    更宽的行总是或多或少地对数据库的整体性能产生负面影响，即使使用便宜的存储也是如此。相关讨论：

    integer在许多表（和日志文件，以及调试......）中使用较小的数字进行操作通常在人眼上更容易。甚至可能是最重要的实际好处。

    • 2

相关问题