MYSQL 5.7中的原生 JSON 支持: MYSQL 中的 JSON 数据类型的优缺点是什么？

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

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

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

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

方便 -JSON 列的附加内联语法使之成为 在 SQL 中集成 Document 查询非常自然 Example (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 colum？好吃吗？这会打破常态吗。如果这是可能的，那么我猜测它将像一个 MySQL 列中的 NoSQL 一样.我真的很想知道更多关于这个功能的信息。MySQLJSON 数据类型的优缺点。

70333 次浏览
小开

以下来自 MySQL 5.7通过 JSON 带来了性感的内容听起来不错:

在 MySQL 中使用 JSON 数据类型有两个优点 在文本字段中存储 JSON 字符串:

数据验证。 JSON 文档将被自动验证，并且 无效的文档将产生错误。改进内部存储 将 JSON 数据转换为允许快速读取的格式 访问结构化格式的数据。服务器能够 通过键或索引查找子对象或嵌套值，允许添加 灵活性和表现。

...

NoSQL 商店的特色风味 (文档数据库、键值存储和图形数据库)可能更好 选项为他们的特定用例，但添加这个 Datatype 可能允许您降低技术的复杂性 价格是与 MySQL (或兼容的)数据库耦合 对于许多用户来说，这不是问题。

请注意关于 文件验证的语言，因为它是一个重要因素。我想需要执行一系列测试来比较这两种方法。那两个人是:

  1. 带有 JSON 数据类型的 Mysql
  2. 没有

从我所看到的来看，目前网络上关于 mysql/json/性能的幻灯片分享很少。

也许你的职位可以成为它的枢纽。或者也许性能是一个事后想法，不确定，您只是兴奋地不创建一堆表。

小开

我最近遇到了这个问题，我总结了以下经验:

没有一种方法可以解决所有的问题。 您应该正确地使用 JSON。

一个例子是:

我有一个名为: CustomField的表，它必须有两列: name、 fields。 name是一个本地化的字符串，它的内容应该是:

{
"en":"this is English name",
"zh":"this is Chinese name"
...(other languages)
}

fields应该是这样的:

[
{
"filed1":"value",
"filed2":"value"
...
},
{
"filed1":"value",
"filed2":"value"
...
}
...
]

正如您所看到的，name和 fields都可以保存为 JSON，而且它可以工作！

但是，如果我经常使用 name来搜索这个表，我应该怎么做？使用 JSON_CONTAINS，JSON_EXTRACT... ？显然，将它保存为 JSON 不再是一个好主意，我们应该将它保存为一个独立的表: CustomFieldName。

从上面的情况来看，我认为你应该记住这些想法:

  1. 为什么 MYSQL 支持 JSON？
  2. 您为什么想要使用 JSON? 您的业务逻辑只是需要这个吗? 或者还有其他东西？
  3. 永远不要偷懒

谢谢

小开

根据我的经验，至少在 MySql 5.7中实现 JSON 并不是很有用，因为它的性能很差。 嗯，对于读取数据和验证来说，这并不是那么糟糕。然而，使用 MySql 修改 JSON 比使用 Python 或 PHP 要慢10-20倍。 让我们想象一个非常简单的 JSON:

{ "name": "value" }

让我们假设我们必须把它转换成这样的东西:

{ "name": "value", "newName": "value" }

您可以使用 Python 或 PHP 创建简单的脚本，这些脚本将选择所有行并逐一更新它们。您不必为它执行一个巨大的事务，因此其他应用程序可以并行使用该表。当然，如果愿意，也可以进行一个大型事务，因此可以保证 MySql 将执行“全部或全部”操作，但是其他应用程序很可能无法在事务执行期间使用数据库。

我有4000万行表，Python 脚本在3-4小时内更新它。

现在我们有了 MySql JSON，所以我们不再需要 Python 或 PHP 了，我们可以这样做:

UPDATE `JsonTable` SET `JsonColumn` = JSON_SET(`JsonColumn`, "newName", JSON_EXTRACT(`JsonColumn`, "name"))

看起来很简单，也很棒。但是，它的速度比 Python 版本慢10-20倍，而且它是单一事务，因此其他应用程序不能并行修改表数据。

因此，如果我们只想在4000万行的表中复制 JSON 密钥，那么在30-40小时内我们根本不需要使用表。毫无意义。

关于读取数据，根据我的经验，通过 WHERE中的 JSON_EXTRACT直接访问 JSON 字段也非常缓慢(比 TEXT在非索引列上使用 LIKE要慢得多)。虚拟生成的列执行速度要快得多，但是，如果我们事先知道数据结构，就不需要 JSON，可以使用传统的列。当我们在真正有用的地方使用 JSON 时，例如，当数据结构未知或经常更改(例如，自定义插件设置)时，定期为任何可能的新列创建虚拟列看起来都不是一个好主意。

Python 和 PHP 使 JSON 验证成为一种魅力，因此我们是否需要在 MySql 端进行 JSON 验证是值得怀疑的。为什么不验证 XML、 MicrosoftOffice 文档或检查拼写呢？;)

小开
最佳答案 
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 的吸引人之处在于，它允许您添加新属性，而不必执行 ALTERTABLE。但是，如果您希望借助索引搜索 JSON 字段，那么无论如何都必须定义一个额外的(虚拟)列。

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

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 还有其他问题。例如，在我的测试中，与存储相同数据的传统列相比，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中起作用。

小开

斯特朗不同意其他答案中提到的一些事情(公平地说，那是几年前的事了)。

我们已经非常谨慎地开始采用带有健康怀疑态度的 JSON 字段。

这通常描述了我们目前的处境:

  • 就像99% 的应用程序一样，我们并没有大规模地开发。我们使用许多不同的应用程序和数据库，其中大多数都能够在适当的硬件上运行。
  • 如果性能 是的出现问题，我们有适当的流程和诀窍来进行更改。
  • 我们对哪些表将会很大有一个大致的概念，并且仔细考虑如何为它们优化查询。
  • 我们也知道在哪些情况下这是 没有真正需要的。
  • 我们在应用程序层非常擅长数据验证和静态类型化。

最后,

当我们使用 JSON 存储复杂数据时，其他表从不直接引用该数据。我们也倾向于从不需要在热路径中的 where 子句中使用它们。

因此，记住所有这些，使用一个小的 JSON 字段而不是一个或多个表极大地降低了查询和数据模型的复杂性。消除这种复杂性使得编写某些查询更加容易，使得我们的代码更加简单，并且通常节省了时间。

复杂性和性能需要仔细平衡。不应该盲目地应用 JSON 字段，但是对于这样工作的情况来说，这是非常好的。

“ JSON 字段不能很好地执行”是不使用 JSON 字段的有效理由，如果您处于性能差异很重要的地方。

一个具体的例子是，我们有一个表，其中我们存储视频转码设置。设置表每行有1个“配置文件”，设置本身的最大嵌套级别为4(数组和对象)。

尽管这是一个庞大的数据库，但是数据库中只有几百条这样的记录。建议将其分成5个表将不会产生任何益处，而且会带来很多痛苦。

这是一个极端的例子，但是我们还有很多其他的例子(更多的行) ，其中决定使用 JSON 字段已经是几年前的事了，而且还没有引起问题。

最后一点: 现在可以直接对 JSON 字段进行索引。


提交答案