3 個額外的 varchar(32) 鍵並更新它們——性能太差了？

我有一張桌子，基本上看起來像這樣：

table1
--------
id      bigserial autoincrement primary key,
token1  varchar(32) unique not null,
token2  varchar(32) unique not null,
token3  varchar(32) unique not null
[........some other data ......]

我的客戶端 Web 應用程序是這樣建構的，我不能總是使用“id”來檢索記錄。也就是說，我需要能夠通過“令牌”之一來檢索記錄：

select * from table1 where id = 1;
select * from table1 where token1 = 'fdsafd';
select * from table1 where token2 = 'ghgfdhg';
select * from table1 where token3 = 'rewqrw';

此外，如果出現某種情況，我的客戶端 Web 應用程序可能還需要在不同時間更新“令牌”。不太經常。

我估計該表每天最多會增長 100.000 條記錄，在一天內均勻分佈；每天大約有 20k 更新。

問題：

1. 將使用這 3 個 32 個字元長的令牌，而不是僅使用“id”，

並且

2）將更新它們

顯著惡化性能？

3. 改進方法？所有 3 個令牌必須保留。我應該為令牌創建一個單獨table2的令牌，我只插入令牌，以避免在“table1”中更新它們嗎？

首先，正如評論的那樣，使用uuid有效 UUID 值的數據類型 not varchar(32)。更便宜、更安全、更清潔。看：

• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎

主要問題的答案取決於全貌。您提供了一些很好的資訊，更多的是相關的：

• 這些 UUID 是否自然屬於主表？它們是表中描述的實體的屬性嗎？
• 行有多寬？
• 有多少並發寫入活動？典型的讀寫模式？
• 您的儲存和 RAM 有多快/有限/多貴？
• 最終，所有活動的總和以及隨著時間的推移它可能如何發展的前景是相關的。

這就是為什麼很難給出明確的答案。對於這兩種設計都存在爭議：

1. 向主表添加 3 列。
2. 添加一個單獨的 1:1 表，每行 3 個 UUID。
3. 添加一個單獨的 1:n 表，每行有 1 個 UUID。

假設：

• 總是有3 個 UUID 值（如您的NOT NULL約束所示）。
• 主排比較寬。
• 主行和/或添加的 UUID 有很多更新（您提到每天 20k），但幾乎沒有同時涉及兩者的更新。
• 您總是查詢三個 UUID 值之一，並確切知道要查看哪一列。永遠不要同時查看所有三個值。
• Postgres 11 或更高版本（INCLUDE子句需要）。

然後我會選擇選項 2：

CREATE TABLE table1_uuids (
 id      bigint PRIMARY KEY REFERENCES table1
, token1  uuid NOT NULL
, token2  uuid NOT NULL
, token3  uuid NOT NULL
, CONSTRAINT token1_uni UNIQUE (token1) INCLUDE (id)
, CONSTRAINT token2_uni UNIQUE (token2) INCLUDE (id)
, CONSTRAINT token3_uni UNIQUE (token3) INCLUDE (id)
);

您的範例查找如下所示：

SELECT *
FROM   table1
WHERE  id = (SELECT id FROM table1_uuids token1 = 'xyz');

基本上，子查詢將給定的令牌 UUID 解析為 PK id，使用內部創建的三個索引之一來實現UNIQUE約束。THEINCLUDE子句允許僅索引掃描。

或者像這樣：

SELECT t.*
FROM   table1_uuids tu
JOIN   table1 t USING (id)
WHERE  tu.token1 = 'xyz';

基本原理

拆分增加了每行 28 字節的成本，以及 PK 索引的大小，以及必須連接兩個表或在兩個表中插入/更新而不是一個表的查詢的一些複雜性和成本。但它避免了表和索引膨脹。使更新更快。僅對 UUID 或僅對主表進行更新來減少鎖定爭用。它加快了對主表的查詢速度，不需要額外的 UUID（我希望，大多數查詢不需要。）

從 Postgres 11 開始，我們可以方便地使用子句添加id到每個UNIQUE約束。INCLUDE這允許非常快速的僅索引掃描，而無需添加另一個冗餘索引。有關的：

• 具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快？

我沒有選擇選項 3 的原因之一：UNIQUE三個 UUID 中的每一個都有一個約束。如果你把它們都放在一個列中，你就做不到UNIQUE。它必須是一個多列約束，包括“token_type”或其他東西。更貴，更不優雅。

主行的寬度以及主行和每個 UUID 列的更新頻率具有關鍵重要性。每次更新都必須在 Postgres 的 MVCC 模型中寫入一個新的行版本（可能會排除 TOASTed 值）。另外，除了 HOT 更新之外，每個附加索引中都有一個新的索引條目。看：

• 更新語句中的冗餘數據

這個成本隨著主行的寬度而增加。如果這種情況經常發生和/或行很寬，那麼您的表和索引就會膨脹和/或您需要為VACUUM. 對性能都不利。

如果 UUID 列沒有更新，選項 1 看起來會好很多，如果有更多更新，選項 3 會。

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