sql - 需要一种在 SQL 数据库中存储/查询 json 的有效方法

Question

我正在实现一项服务，其中每个用户都必须拥有自己的 json/文档数据库。除了让用户通过示例查询 json 文档外，数据库还必须支持涉及多个文档的 ACID 事务，所以我放弃了使用 Couch/Mongo 或其他 NoSQL 数据库(不能使用 RavenDB，因为它必须在 Unix 系统上运行)。

考虑到这一点，我一直在尝试寻找一种在 SQL 数据库之上实现它的方法。到目前为止，这是我的想法:

CREATE TABLE documents (
  id INTEGER PRIMARY KEY,
  doc TEXT
);

CREATE TABLE indexes (
  id INTEGER PRIMARY KEY,
  property TEXT,
  value TEXT,
  document_id INTEGER
)

每个用户都有一个包含这两个表的数据库，用户必须声明他需要查询哪些字段，以便系统可以正确填充“索引”表。因此，如果用户“A”将其帐户配置为启用按“姓名”和“年龄”进行查询，则每次该用户插入具有“姓名”或“年龄”属性的文档时，系统也会向“索引”插入一条记录表，其中“property”列将包含 name/age ，“value”将包含属性值，“document_id”将指向相应的文档。

例如，假设用户插入以下文档:

'{"name" : "Foo", "age" 43}'

这将导致对“文档”表的插入和对“索引”表的另外两个插入:

INSERT INTO documents (id,doc) VALUES (1, '{"name" : "Foo", "age" 43}');
INSERT INTO indexes (property, value, document_id) VALUES ('name', 'foo', 1);
INSERT INTO indexes (property, value, document_id) VALUES ('age', '43', 1);

假设用户“A”向服务发送了以下查询:

'{"name": "Foo", "age": 43}' //(the queries are also json documents).

此查询将被转换为以下 SQL:

SELECT doc FROM documents
WHERE id IN (SELECT document_id FROM indexes
             WHERE document_id IN (SELECT document_id FROM indexes
                                   WHERE property = 'name' AND value = 'Foo')
             AND property = 'age' AND value = '43') 

我的问题:

• 知道用户可以在他的查询中使用大量条件(比如 20-30 个 AND 条件)，这会导致子查询嵌套非常高，上面的 SELECT 查询在大多数情况下的效率如何数据库系统(postgres、mysql...)？
• 上述解决方案对于最终将包含数百万/数十亿 json 文档的数据库是否可行？
• 是否有更好的方法来满足我的要求？
• 是否有可扩展的文档数据库可以执行涉及多个文档的 ACID 事务并在 Unix 系统上运行？

Answer 1

您的 indexes 表是一个所谓的 Entity-Attribute-Value。

EAV 表非常适合存储信息并在您知道实体时调用它。 (在您的例子中，当您知道 document_id 时找到所有 indexes 行。)

但它们可怕反过来:提供属性-值组合来搜索实体。这正是您在最终查询中所拥有的。随着越来越多的实体共享相同的属性值组合(例如name=foo)，查询性能会下降。

那么，回答你的前两个问题:
1. 如所写，查询在搜索 n 属性时需要 n 子查询。随着 n 的增长，这将很难扩展。
2. 随着记录数量的增加，它会降低，尤其是数百万/数十亿条记录。

一般来说，如果您了解 EAV，人们强烈建议您避开它。

而且，更糟糕的是，在 SQL 中并没有真正好的替代方案。优化搜索的标准方法是使用索引，它可以很容易地建模为排序的数据集。但是你会需要很多索引:
- 如果您搜索所有三列，(fieldX, fieldY, fieldZ) 上的索引很棒。
- 但如果您必须在 just fieldZ 上进行搜索，那将糟透了。

如果您可以使用具有固定列数的传统表对其进行重新建模，并且有空间来应用您可能需要的每个索引组合，那将是您性能最高的模型。

如果您不能固定列数(新的属性一直出现)和/或您没有足够的空间来容纳所有不同的组合指数，你似乎被 EAV 困住了。这会起作用，但它不会在“即时”结果方面很好地扩展。

注意:如果您坚持使用 EAV，您是否测试过此查询结构？

  SELECT
    document_id
  FROM
    indexes
  WHERE
       (property = 'name' AND value = 'Foo')
    OR (property = 'age'  AND value = '43' )
  GROUP BY
    document_id
  HAVING
    COUNT(*) = 2

这假设 (document_id, property, value) 是唯一的。否则一个文档可能有两次 ('name', 'foo')，因此传递 COUNT(*) 子句。