ID、UUID 和“deleted_at”列，如何避免性能下降？

考慮這張表，但在PostgreSQL上有數千條記錄：

| id |    uuid | color    | ... |          deleted_at |
|----|---------|----------|-----|---------------------|
| 1  | 4fc1... | red      | ... | 2020-01-01 13:00:00 |
| 2  | 4fc1... | gray     | ... | 2020-01-01 13:00:00 |
| 3  | 4fc1... | blue     | ... |                null |
| 4  | 4fc1... | red      | ... |                null |
| 5  | 4fc1... | blue     | ... | 2019-12-03 00:45:00 |

目的：

• id(autoincrement) 用作主鍵和主鉤子，以將 JOIN 與其他表一起使用。
• uuid是用於辨識記錄的面向公眾的值。
• deleted_at(timestamp|null) 用於檢查記錄何時被刪除，並進行相應的過濾。

80% 的查詢是通過以下方式完成的：

• WHERE [id] = ? AND WHERE [deleted_at] NOT NULL
• WHERE [uuid] = ? AND WHERE [deleted_at] NOT NULL

當數據庫變得更大時，我可以看到性能下降，因為這些列沒有被索引，除了id設置為主鍵（自動增量）但沿deleted_at列使用。

INSERT/UPDATE操作有點少，DELETE幾乎不存在。

我想到了複合主鍵並為 and 創建兩個索引id+deleted_at，uuid+deleted_at但我不確定，因為INSERT/UPDATE考慮到現在有兩個索引而不僅僅是一個主鍵，它可能會阻礙操作。

---

我如何加速對該表的查詢？

更新 1：我考慮使用 ID 和 UUID，因為我有連接到三個表的表，所以三個 UUID = 128×3 位，而三個 ID = 64×3 位。但我可以用 192 位來換取一致性。

無論表的大小如何，使用主鍵進行檢索的where id = ?時間幾乎都是恆定的。使用 AND 添加條件不會改變這一點（OR但使用會是另一回事）

如果您需要使用 uuid 列上的條件進行查詢具有相同的性能，請在其上創建一個（唯一）索引。

如果您總是查詢，=則無需向索引添加其他列。創建包含的附加複合索引(id, deleted_at)不會提高查詢速度。

如果有的話，過濾（部分）索引可能會有所幫助：

create unique index on (id) where deleted_at is null;
create unique index on (uuid) where deleted_at is null;

但這僅在您刪除的行多於未刪除的行時才有幫助。

---

製作id, deleted_at主鍵似乎是完全錯誤的，因為這意味著您可以擁有多個具有相同值的行id- 這幾乎不是您所期望的。

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