🥇在PostgreSQL中用JSONB替换EAV

TL;DR：JSONB 可以在不牺牲查询性能的前提下，极大地简化数据库模式开发。

介绍

让我们来看一个经典的例子，这可能是关系数据库领域最古老的用例之一：我们有一个实体，需要存储该实体的某些属性。但并非所有实例都具有相同的属性集，而且将来可能还会添加更多属性。

解决这个问题的最简单方法是在数据库表中为每个属性值创建一个列，然后只需填充特定实体实例所需的属性值即可。太好了！问题解决了……直到你的表包含数百万条记录，而你需要添加一条新记录。

让我们考虑一下EAV模式（实体-属性-值这种结构很常见。一个表包含实体（记录），另一个表包含属性名称（特性），第三个表将实体与其属性关联起来，并包含当前实体的这些属性的值。这样，您可以为不同的对象设置不同的属性集，还可以动态添加属性，而无需更改数据库结构。

然而，如果 EVA 方法没有缺点，我就不会写这篇文章了。例如，检索一个或多个实体（每个实体只有一个属性）需要在查询中进行两次连接：第一次连接属性表，第二次连接值表。如果一个实体有两个属性，则需要四次连接！此外，所有属性通常都以字符串形式存储，这会导致结果和 WHERE 子句都需要进行类型强制转换。如果您编写大量查询，这将造成相当大的资源浪费。

尽管存在这些显而易见的缺陷，EAV长期以来一直被用于解决这类问题。这些缺陷是不可避免的，而且当时也没有更好的替代方案。
但随后PostgreSQL中出现了一种新的“技术”……

从 PostgreSQL 9.4 开始，新增了 JSONB 数据类型，用于存储二进制 JSON 数据。虽然以这种格式存储 JSON 通常比纯文本 JSON 占用更多空间和时间，但其操作速度却快得多。JSONB 还支持索引，进一步提升了查询速度。

JSONB 数据类型允许我们通过在实体表中添加一个 JSONB 列来取代繁琐的 EAV 模式，从而显著简化数据库设计。然而，许多人认为这会以性能为代价……这就是我撰写本文的原因。

建立测试数据库

为了进行比较，我在 PostgreSQL 9.5 的全新安装版本（售价 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
);

看起来简单多了，不是吗？然后它被添加到实体表中（实体 & entity_jsonb）10万条记录，相应地，该表填充了相同的数据，其中使用了EAV模式和JSONB列方法—— entity_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';

我想我们可以同意，第二个版本更简洁（没有 join 操作！），因此更易读。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 时，每个要查询的属性都需要两次连接。在我看来，上面的查询展示了数据库设计的显著简化。您还可以查看更多关于如何编写 JSONB 查询的示例。这邮政。
现在该谈谈性能了。

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

为了比较性能，我使用了解释分析为了计算执行时间，我在查询中创建了多个索引。每个查询至少执行了三次，因为查询规划器第一次执行的时间较长。首先，我运行了未创建任何索引的查询。这显然是 JSONB 的一个优势，因为 EAV 所需的连接无法使用索引（外键字段没有索引）。之后，我在 EAV 值表的两个外键列上创建了索引，并在另一个列上创建了索引。 GIN 用于 JSONB 列。

数据更新显示了以下时间结果（单位：毫秒）。请注意，刻度为对数刻度：

在 PostgreSQL 中用 JSONB 替换 EAV

我们发现，由于上述原因，JSONB 在不使用索引的情况下比 EAV 快得多（超过 50000 倍）。当我们为主键列建立索引时，这种差异几乎消失，但 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。大小只有原来的三分之一！这让我有点意外，因为我们在每个 JSONB 对象中都存储了属性名称。

但数据本身就说明了一切：在EAV中，每个属性值需要存储两个整数外键，这会导致额外占用8字节的数据。此外，在EAV中，所有属性值都以文本形式存储，而JSONB则尽可能在内部使用数值和逻辑值，从而占用更少的存储空间。

结果

总的来说，我认为将实体属性以 JSONB 格式存储可以显著简化数据库的设计和维护。如果您执行大量查询，将所有数据与实体存储在同一张表中无疑会更加高效。它简化了数据交互本身就是一大优势，而且最终生成的数据库大小也缩小了三倍。

此外，根据基准测试结果，我们可以得出结论：性能损失非常小。在某些情况下，JSONB 的性能甚至比 EAV 更快，表现更佳。然而，此基准测试显然并未涵盖所有方面（例如，具有大量属性的实体、现有数据中属性数量的显著增加等），因此，如果您有任何改进建议，请随时在评论区留言！

来源： habr.com