mysql - MYSQL 5.7 中的原生 JSON 支持 : what are the pros and cons of JSON data type in MYSQL?

Question

在 MySQL 5.7 中，用于存储 JSON data in MySQL 的新数据类型表已添加。这显然将是 MySQL 的一个巨大变化。他们列出了一些好处

Document Validation - Only valid JSON documents can be stored in aJSON column, so you get automatic validation of your data.

Efficient Access - More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is storedin an optimized binary format that allows for quicker access to objectmembers and array elements.

Performance - Improve your queryperformance by creating indexes on values within the JSON columns.This can be achieved with “functional indexes” on virtual columns.

Convenience - The additional inline syntax for JSON columns makes itvery natural to integrate Document queries within your SQL. Forexample (features.feature is a JSON column): SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

哇!它们包括一些很棒的功能。现在更容易操作数据。现在可以在列中存储更复杂的数据。所以 MySQL 现在加入了 NoSQL。

现在我可以想象对 JSON 数据的查询类似于

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN 
( 
SELECT JSON_EXTRACT(data,"$.inverted") 
FROM t1 | {"series": 3, "inverted": 8} 
WHERE JSON_EXTRACT(data,"$.inverted")<4 );

那么我可以在几个 json 列中存储巨大的小关系吗？好吗？它是否破坏了规范化。 如果这是可能的，那么我猜它会像 MySQL 列中的 NoSQL 一样。我真的很想了解更多有关此功能的信息。 MySQL JSON 数据类型的优缺点。

Answer 1

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

在这样的表达式或函数中使用列会破坏使用索引帮助优化查询的查询的任何机会。上面显示的查询被强制执行表扫描。

关于“高效访问”的说法具有误导性。这意味着在查询检查包含 JSON 文档的行后，它可以提取字段而无需解析 JSON 语法的文本。但是仍然需要进行表扫描来搜索行。换句话说，查询必须检查每一行。

以此类推，如果我在电话簿中搜索名字为“Bill”的人，我仍然必须阅读电话簿中的每一页，即使已突出显示名字以便更快地发现他们。

MySQL 5.7 允许您在表中定义一个虚拟列，然后在该虚拟列上创建索引。

ALTER TABLE t1
  ADD COLUMN series AS (JSON_EXTRACT(data, '$.series')),
  ADD INDEX (series);

那么如果你查询虚拟列，它可以使用索引，避免表扫描。

SELECT * FROM t1
WHERE series IN ...

这很好，但有点忽略了使用 JSON 的意义。使用 JSON 的吸引人之处在于它允许您添加新属性而无需执行 ALTER TABLE。但事实证明，如果您想借助索引搜索 JSON 字段，则无论如何都必须定义一个额外的(虚拟)列。

但您不必为 JSON 文档中的每个 字段定义虚拟列和索引，只需搜索或排序的那些。 JSON 中可能还有其他属性，您只需在选择列表中提取这些属性，如下所示:

SELECT JSON_EXTRACT(data, '$.series') AS series FROM t1
WHERE <other conditions>

我通常会说这是在 MySQL 中使用 JSON 的最佳方式。仅在选择列表中。

当您在其他子句(JOIN、WHERE、GROUP BY、HAVING、ORDER BY)中引用列时，使用常规列而不是 JSON 文档中的字段更有效。

我做了一个名为 How to Use JSON in MySQL Wrong 的演讲在 2018 年 4 月的 Percona Live 大会上。我将在秋季的 Oracle Code One 上更新并重复演讲。

JSON 还有其他问题。例如，在我的测试中，JSON 文档需要的存储空间是存储相同数据的传统列的 2-3 倍。

MySQL 正在积极推广其新的 JSON 功能，主要是为了劝阻人们不要迁移到 MongoDB。但是像 MongoDB 这样的面向文档的数据存储从根本上说是一种非关系的数据组织方式。它与关系不同。我并不是说一个比另一个更好，它只是一种不同的技术，适用于不同类型的查询。

当 JSON 使您的查询更有效率时，您应该选择使用 JSON。

不要仅仅因为它是新的或为了时尚而选择一种技术。

---

编辑:如果您的 WHERE 子句使用与虚拟列定义完全相同的表达式，则 MySQL 中的虚拟列实现应该使用索引。也就是说，以下应该使用虚拟列上的索引，因为虚拟列是定义的AS (JSON_EXTRACT(data,"$.series"))

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

除非我通过测试此功能发现，如果表达式是 JSON 提取函数，则由于某种原因它不起作用。它适用于其他类型的表达式，但不适用于 JSON 函数。更新:据报道，这最终在 MySQL 5.7.33 中有效。