MYSQL 5.7中的本机JSON支持：MYSQL中JSON数据类型的优缺点是什么？

一尘不染

MYSQL 5.7中的本机JSON支持：MYSQL中JSON数据类型的优缺点是什么？

mysql

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

文档验证 -JSON列中只能存储有效的JSON文档，因此您可以自动验证数据。

有效访问
-更重要的是，当您将JSON文档存储在JSON列中时，它不会存储为纯文本值。相反，它以优化的二进制格式存储，从而可以更快地访问对象成员和数组元素。

性能 -通过在JSON列中的值上创建索引来提高查询性能。这可以通过虚拟列上的“功能索引”来实现。

便利性 -JSON列的附加内联语法使在SQL中集成文档查询变得非常自然。例如（features.feature是一个JSON列）：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数据类型的优缺点。

阅读 1864

2020-05-17

共1个答案

一尘不染

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

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

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

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

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文档中的字段会更有效。

我在2018年4月的Percona Live会议上发表了名为“
如何在MySQL错误中使用JSON的JSON”的演讲。我将在秋季在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函数。

2020-05-17