🥇PostgreSQL 中用 JSONB 取代 EAV

TL; DR：JSONB 可以在不犧牲查詢效能的情況下大幅簡化資料庫模式開發。

介紹

讓我們舉一個可能是關係資料庫（資料庫）世界中最古老的用例之一的經典範例：我們有一個實體，我們需要保存該實體的某些屬性（屬性）。但並非所有實例都可能具有相同的屬性集，並且將來可能會添加更多屬性。

解決此問題的最簡單方法是在資料庫表中為每個屬性值建立一個列，然後簡單地填寫特定實體實例所需的列。偉大的！問題解決了......直到您的表包含數百萬筆記錄並且您需要新增記錄。

考慮 EAV 模式（實體-屬性-值），這種情況經常發生。一個表包含實體（記錄），另一個表包含屬性名稱（屬性），第三個表將實體與其屬性相關聯，並包含目前實體的這些屬性的值。這使您能夠為不同的物件擁有不同的屬性集，並且還可以在不更改資料庫結構的情況下動態新增屬性。

然而，如果 EVA 方法沒有一些缺點，我就不會寫這篇文章。因此，例如，要取得一個或多個每個具有 1 個屬性的實體，查詢中需要 2 個聯接：第一個是與屬性表的聯接，第二個是與值表的聯接。如果一個實體有 2 個屬性，則需要 4 個連線！此外，所有屬性通常都會儲存為字串，這會導致結果和 WHERE 子句都進行類型轉換。如果您編寫大量查詢，那麼這在資源使用方面是相當浪費的。

儘管有這些明顯的缺點，EAV 長期以來一直被用來解決這類問題。這些都是不可避免的缺點，而且根本沒有更好的選擇。
但隨後 PostgreSQL 中出現了一種新的「技術」...

從 PostgreSQL 9.4 開始，新增了 JSONB 資料類型來儲存 JSON 二進位資料。雖然以這種格式儲存 JSON 通常比純文字 JSON 需要更多的空間和時間，但對其執行操作要快得多。 JSONB 還支援索引，這使得查詢速度更快。

JSONB 資料類型可讓我們透過在實體表中新增 JSONB 欄位來取代繁瑣的 EAV 模式，從而大幅簡化資料庫設計。但許多人認為這應該伴隨著生產力的下降......這就是我寫這篇文章的原因。

設定測試資料庫

為了進行比較，我在 9.5 美元版本上全新安裝的 PostgreSQL 80 上建立了資料庫 DigitalOcean 烏班圖14.04。在 postgresql.conf 設定一些參數後我運行這使用 psql 的腳本。建立下表是為了以 EAV 形式呈現資料：

CREATE TABLE entity ( 
  id           SERIAL PRIMARY KEY, 
  name         TEXT, 
  description  TEXT
);
CREATE TABLE entity_attribute (
  id          SERIAL PRIMARY KEY, 
  name        TEXT
);
CREATE TABLE entity_attribute_value (
  id                  SERIAL PRIMARY KEY, 
  entity_id           INT    REFERENCES entity(id), 
  entity_attribute_id INT    REFERENCES entity_attribute(id), 
  value               TEXT
);

下面是一個表，其中將儲存相同的數據，但屬性位於 JSONB 類型列中 – 性能.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

看起來簡單很多，不是嗎？然後將其新增至實體表中（實體 & 實體_jsonb）10 萬筆記錄，相應地，使用 EAV 模式和 JSONB 列的方法填充相同的資料 - 實體_jsonb.屬性。因此，我們在整個屬性集中收到了幾種不同的資料類型。範例資料：

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

所以現在我們對這兩個選項都有相同的數據。讓我們開始比較工作中的實作吧！

簡化您的設計

前面說過，資料庫設計大大簡化了：一張表，使用 JSONB 欄位作為屬性，而不是用三張表作為 EAV。但這如何反映在請求上呢？更新一個實體屬性如下圖所示：

-- EAV
UPDATE entity_attribute_value 
SET value = 'blue' 
WHERE entity_attribute_id = 1 
  AND entity_id = 120;

-- JSONB
UPDATE entity_jsonb 
SET properties = jsonb_set(properties, '{"color"}', '"blue"') 
WHERE id = 120;

正如您所看到的，最後一個請求看起來並不簡單。要更新 JSONB 物件中的屬性值，我們必須使用該函數 jsonb_set()，並且應該將我們的新值作為 JSONB 物件傳遞。但是，我們不需要事先知道任何識別符。查看 EAV 範例，我們需要知道entity_id 和entity_attribute_id 才能執行更新。如果您想根據物件名稱更新 JSONB 列中的屬性，則只需一行簡單的操作即可完成。

現在讓我們根據新顏色選擇剛剛更新的實體：

-- EAV
SELECT e.name 
FROM entity e 
  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';

-- JSONB
SELECT name 
FROM entity_jsonb 
WHERE properties ->> 'color' = 'blue';

我認為我們可以同意第二個更短（沒有連接！），因此更具可讀性。 JSONB 在這裡獲勝！我們使用 JSON ->> 運算子從 JSONB 物件取得顏色作為文字值。還有第二種方法可以使用 @> 運算子在 JSONB 模型中實現相同的結果：

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

這有點複雜：我們檢查其屬性列中的 JSON 物件是否包含 @> 運算子右側的物件。可讀性更低，生產力更高（見下文）。

當您需要一次選擇多個屬性時，讓我們更輕鬆地使用 JSONB。這就是 JSONB 方法真正發揮作用的地方：我們只需選擇屬性作為結果集中的附加列，而不需要聯結：

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

使用 EAV，您要查詢的每個屬性都需要 2 個聯結。在我看來，上述查詢顯示了資料庫設計的極大簡化。有關如何編寫 JSONB 查詢的更多範例，請參見此郵政。
現在是時候談談性能了。

Производительность

為了比較我使用的性能解釋分析在查詢中，計算執行時間。每個查詢至少執行三次，因為查詢規劃器第一次需要更長的時間。首先，我在沒有任何索引的情況下執行查詢。顯然，這是 JSONB 的優勢，因為 EAV 所需的聯結無法使用索引（外鍵欄位未建立索引）。之後，我在 EAV 值表的 2 個外鍵列上建立了一個索引，以及一個索引 GIN 對於 JSONB 欄位。

資料更新在時間（以毫秒為單位）方面顯示了以下結果。請注意，刻度是對數的：

我們發現，如果您不使用索引，JSONB 比 EAV 快得多（> 50000-x），原因如上所述。當我們用主鍵索引列時，差異幾乎消失，但 JSONB 仍然比 EAV 快 1,3 倍。請注意，JSONB 列上的索引在這裡不起作用，因為我們沒有在評估標準中使用屬性列。

對於基於屬性值選擇數據，我們得到以下結果（正常範圍）：

您可以注意到，JSONB 再次比不含索引的 EAV 運行得更快，但是當 EAV 帶有索引時，它仍然比 JSONB 運行得更快。但後來我看到 JSONB 查詢的時間是相同的，這提示我 GIN 索引不起作用。顯然，當您在具有填充屬性的列上使用 GIN 索引時，它僅在使用包含運算子 @> 時才生效。我在新的測試中使用了它，它對時間產生了巨大的影響：只有 0,153ms！這比 EAV 快 15000 倍，比 ->> 運算子快 25000 倍。

我覺得已經夠快了！

資料庫表大小

讓我們比較一下兩種方法的表格大小。在 psql 中，我們可以使用指令顯示所有資料表和索引的大小 dti+

對於 EAV 方法，表格大小約為 3068 MB，索引最大為 3427 MB，總共 6,43 GB。 JSONB 方法使用 1817 MB 用於表，318 MB 用於索引，即 2,08 GB。結果少了3倍！這個事實讓我有點驚訝，因為我們在每個 JSONB 物件中儲存屬性名稱。

但數字仍然說明一切：在 EAV 中，我們為每個屬性值儲存 2 個整數外鍵，從而產生 8 個位元組的附加資料。此外，EAV 將所有屬性值儲存為文本，而 JSONB 將盡可能在內部使用數字和布林值，從而減少佔用空間。

結果

總的來說，我認為以 JSONB 格式保存實體屬性可以使資料庫的設計和維護變得更加容易。如果您正在執行大量查詢，那麼將所有內容與實體保留在同一個表中實際上會更有效地工作。事實上，這簡化了資料之間的交互作用已經是一個優點，但產生的資料庫體積縮小了 3 倍。

此外，根據所執行的測試，我們可以得出結論，性能損失非常微不足道。在某些情況下，JSONB 甚至比 EAV 更快，使其更好。然而，這個基準測試當然並沒有涵蓋所有方面（例如具有大量屬性的實體、現有數據的屬性數量顯著增加...），因此如果您對如何改進它們有任何建議，歡迎在評論中留言！

來源： www.habr.com

在 PostgreSQL 中以 JSONB 取代 EAV

介紹

設定測試資料庫

簡化您的設計

Производительность

資料庫表大小

結果

添加評論取消回复

在 PostgreSQL 中以 JSONB 取代 EAV

介紹

設定測試資料庫

簡化您的設計

Производительность

資料庫表大小

結果

添加評論 取消回复

添加評論取消回复