使用 BIGINT 和兩倍的儲存空間或 INTEGER 但有額外的操作？

假設我有一個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;

2. 在中用internal_id作主鍵agency，在其他表中用作外鍵。但是為了擺脫額外的JOIN，在我的應用程序中，我可以首先使用一個簡單的查詢 ( ) 映射external_id到，然後將獲取的用於另一個簡單的查詢：internal_id``SELECT internal_id FROM agency WHERE external_id=5``internal_id

   SELECT * FROM A
   WHERE internal_id=59; -- 59 is the fetched internal_id from the other query

它是否比JOIN考慮應用程序和數據庫之間的額外往返具有更好的性能？

3. 忘記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, ...（

假設

• 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);

該函式可以由查詢計劃器“內聯”。看：

• PostgreSQL UDF（使用者定義函式）成本
• 如何確定 SQL 函式是否可以內聯？

我建議這個表定義：

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 或更高版本）。看：

• PostgreSQL 中的覆蓋索引是否有助於 JOIN 列？

除其他外，覆蓋索引否定了附加列的壓載，以agency實現鍵轉換。

有了這些索引，鍵轉換就可以快速進行僅索引掃描以進行鍵轉換。在查詢大型表的上下文中，成本幾乎可以忽略不計。

這為每個額外的表和索引節省了“數百萬和數十億”乘以 4 個字節（這可能更重要）。誠然，儲存一直在變得更便宜，但 RAM（和快速記憶體！）通常仍然有限。更大的表和索引意味著更少的數據可以保留在記憶體中。這對性能至關重要。

更寬的行總是或多或少地對數據庫的整體性能產生負面影響，即使使用便宜的儲存也是如此。相關討論：

• 查詢期間從磁碟檢索什麼？

integer在許多表（和日誌文件，以及調試……）中使用較小的數字進行操作通常在人眼上更容易。甚至可能是最重要的實際好處。

引用自：https://dba.stackexchange.com/questions/274563