🥇在 PostgreSQL 中用 JSONB 取代 EAV

TL； 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 Ubuntu 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.properties。因此，我們在整個屬性集中獲得了幾種不同的資料類型。範例資料：

{
  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 值表的兩個外鍵列上建立了一個索引，並且還建立了一個索引 GIN 用於 JSONB 欄位。

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

在 PostgreSQL 中以 JSONB 取代 EAV

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

對於根據屬性值選擇數據，我們得到以下結果（正常比例）：

在 PostgreSQL 中以 JSONB 取代 EAV

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

我認為那已經相當快了！

資料庫表的大小

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

在 PostgreSQL 中以 JSONB 取代 EAV

對於 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