database json数组查询 - MYSQL 5.7中的原生JSON支持:MYSQL中JSON数据类型的优缺点是什么?




json函数 json解析 (5)

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

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

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

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

方便性 - JSON列的附加内联语法使得在SQL中集成Document查询非常自然。 例如(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一样 。 我真的想知道更多有关此功能的信息。 MySQL JSON数据类型的优缺点。


Answers

来自MySQL 5.7的以下内容带来性感的回归,JSON听起来对我很好:

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

数据验证。 JSON文档将自动验证,无效文档将产生错误。 改进了内部存储格式。 JSON数据转换为允许以结构化格式快速读取数据的格式。 服务器能够按键或索引查找子对象或嵌套值,从而增加灵活性和性能。

...

NoSQL存储(文档DB,键值存储和图形数据库)的特殊风格可能是其特定用例的更好选择,但添加此数据类型可能会降低技术堆栈的复杂性。 价格是与MySQL(或兼容)数据库的耦合。 但对许多用户来说,这不是问题。

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

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

从我所看到的,截至目前,关于mysql / json / performance的主题,网络有一些浅薄的幻灯片。

也许你的帖子可以成为它的中心。 或许表现是一种思想,不确定,你只是兴奋不创造一堆表。


根据我的经验,至少在MySql 5.7中的JSON实现由于性能不佳而不是很有用。 嗯,阅读数据和验证并不是那么糟糕。 但是,使用Python或PHP的MySql,JSON修改速度要慢10-20倍。 让我们想象一下非常简单的JSON:

{ "name": "value" }

让我们假设我们必须将其转换为类似的东西:

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

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

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

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

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

它看起来简单而优秀。 但是,它的速度比Python版慢10-20倍,而且它是单个事务,因此其他应用程序无法并行修改表数据。

因此,如果我们只想在40万行表中复制JSON密钥,我们需要在30-40小时内完全不使用表。 它没有任何意义。

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

Python和PHP使JSON验证成为一个魅力,所以我们需要在MySql端进行JSON验证是值得怀疑的。 为什么不验证XML,Microsoft Office文档或检查拼写? ;)


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-list中提取,如下所示:

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

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

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

我在2018年4月的Percona Live会议上发表了一篇名为“ 如何在MySQL错误使用JSON”的演讲。我将在秋季更新并重复Oracle Code One上的演讲。

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

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

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

不要仅仅因为新技术或时尚而选择技术。


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

1,没有办法解决所有问题。 2,你应该正确使用JSON。

一例:

我有一个名为: CustomField的表,它必须有两列: namefieldsname是本地化字符串,它的内容应该是:

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

fields应该是这样的:

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

如您所见, namefields都可以保存为JSON,并且可以正常工作!

但是,如果我使用该name频繁搜索此表,我该怎么办? 使用JSON_CONTAINSJSON_EXTRACT ......? 显然,将它保存为JSON不是一个好主意,我们应该将它保存到一个独立的表: CustomFieldName

从上述情况来看,我认为你应该记住这些想法:

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

谢谢


每个查询中都有大量的读取,更少的正常写入

这两个数据库在热数据集适合内存的读取中表现良好。 两者都强调无连接的数据模型(并鼓励反规范化),并且都为documentsrows提供索引,尽管MongoDB的索引当前更加灵活。

Cassandra的存储引擎可以提供恒定时间写入,无论数据集增长多大。 写入在MongoDB中更成问题,部分原因在于基于B树的存储引擎,但更多的原因是每个数据库写入锁定

对于分析,MongoDB提供了一个自定义的映射/减少实现; Cassandra提供本地Hadoop支持,包括Hive (基于Hadoop map / reduce构建的SQL数据仓库)和Pig (许多人认为更适合用于map / reduce工作负载的Hadoop特定分析语言)。

不担心“大规模”的可扩展性

如果您正在查看单个服务器,MongoDB可能更适合。 对于那些更关心扩展的人来说,Cassandra的非单点故障架构将更容易设置,并且更可靠。 (MongoDB的全局写锁定也会变得更加痛苦。)Cassandra还可以更好地控制复制的工作方式,包括支持多个数据中心。

更关心简单的设置,维护和代码

这两者都是微不足道的设置,对于单个服务器具有合理的现成默认值。 Cassandra更容易在多服务器配置中设置,因为不需要担心特殊角色节点; 这里是一个截屏视频,演示在两分钟内设置一个4节点Cassandra集群

如果您目前正在使用JSON blob,那么考虑到它使用BSON来存储数据,MongoDB对于您的用例来说非常适合。 您可以拥有比您现在的数据库更丰富,更可查询的数据。 这将是Mongo最重要的胜利。





mysql json database database-normalization nosql