MD5 欄位的最佳數據類型是什麼？

我們正在設計一個眾所周知的讀取量大的系統（大約每分鐘數万次讀取）。

• 有一個表names用作一種中央系統資料庫。每行都有一個text欄位representation和一個唯一key的 MD5 散列representation。1該表目前有數千萬條記錄，預計在應用程序的生命週期內將增長到數十億條。
• 還有許多其他表（具有高度變化的模式和記錄計數）引用了該names表。這些表之一中的任何給定記錄都保證具有name_key，它在功能上是表的外鍵names。

1：順便說一下，正如您所料，此表中的記錄一旦寫入就不可更改。

對於除表之外的任何給定表names，最常見的查詢將遵循以下模式：

SELECT list, of, fields 
FROM table 
WHERE name_key IN (md5a, md5b, md5c...);

我想優化讀取性能。我懷疑我的第一站應該是最小化索引的大小（儘管我不介意在那裡被證明是錯誤的）。

**問題：**和列

的最佳數據類型是什麼？ 有理由使用over嗎？還是？key``name_key
hex(32)``bit(128)``BTREE``GIN

數據類型uuid非常適合該任務。它只佔用 16 個字節，而不是 RAM 中的 37 個字節用於varcharortext表示。（或磁碟上的 33 個字節，但在許多情況下奇數需要填充以使其有效地變為40個字節。）並且該uuid類型具有更多優勢。

例子：

SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;

看：

• 將文本表示中的十六進制轉換為十進制數
• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎

如果您不需要 md5 的加密組件，您可能會考慮其他（便宜一點）散列函式，但我會為您的案例選擇 md5。md5 非常成熟，速度非常快，無論如何您的值大多是只讀的。

一句警告：對於您的情況（immutable once written），功能依賴（偽自然）的 PK很好。但是，在可以更新的情況下，同樣會很痛苦text。考慮糾正一個錯字：PK 和所有依賴索引、“其他幾十個表”中的 FK 列和其他引用也必須更改。表和索引膨脹、鎖定問題、更新緩慢、引用失去……

如果text能在正常執行中改變，代理PK會是更好的選擇。我建議bigserial使用一個範圍為 -9223372036854775808 到 +9223372036854775807 的列。那是“十億行”的九五分之二百二十三萬億三百七十二萬億三十六億）不同的值。在任何情況下都可能是個好主意：數十個 FK 列和索引使用 8 個字節而不是 16 個字節！）。或用於更大基數或分佈式系統的隨機 UUID 。您總是可以另外儲存所說的 md5 (as )，以便快速從原始文本中找到主表中的行。 有關的：uuid

• Postgres 中 UUID 列的預設值

對於您的查詢，請參閱：

• 使用大 IN 優化 Postgres 查詢

連字元呢？

如果您更喜歡沒有連字元的表示，請刪除連字元以進行顯示：

SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')

但我不會打擾。預設表示就好了。問題真的不是這裡的代表。

如果其他方應該採用不同的方法並將不帶連字元的字元串放入組合中，那也沒有問題。Postgres 接受幾種合理的文本表示形式作為uuid. 手冊：

PostgreSQL 也接受以下替代形式的輸入：使用大寫數字，用大括號括起來的標準格式，省略部分或全部連字元，在任何四位數字組後添加連字元。例子是：

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

為什麼不bytea呢？

md5()函式text返回。您將使用decode()轉換為bytea，其預設表示為：

SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')

\220\267R^\204\366HP\302\357\264\007\372\343\362q

您將不得不encode()再次獲得原始文本表示：

SELECT encode(my_md5_as_bytea, 'hex');

最重要的是，由於內部成本，儲存為 as 的值bytea將佔用 RAM 中的 20 個字節（磁碟上的 17 個字節，填充的 24個字節） ，這對簡單索引的大小和性能特別不利。varlena

“無效”的 UUID 呢？

沒有“無效”的 UUID。

八位字節 13 和 17 對某些 UUID 類型的“版本”和“變體”進行編碼。但是 Postgres 的uuid數據類型接受所有 128 位的數量，而不考慮“版本”或“變體”。這是根據RFC 4122：

驗證機制：

除了確定 UUID 的時間戳部分是否在未來並因此尚未分配之外，沒有確定 UUID 是否“有效”的機制。

“版本”和“變體”沒有意義/不適用於此案例。為了驗證我執行了一個快速測試：

db<>在這裡擺弄

一切都對uuid這裡有利。

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