我本来打算在 SO 上问这个,但想得更好,所以我在这里尝试一下。
我已经对此进行了搜索,但找不到任何确定的数据。我有一个 MySQL 表,如下所示:
| id | type | value |
该value
字段包含一个 JSON 字符串,列类型为 JSON。我的 JSON 包含我希望能够在 IE 上调用的标识信息
WHERE json_extract(value, '$.contractor_id')='12345'
这样做而不是仅仅创建一个单独的contractor_id
列有什么性能影响?这个特定的表有大约 500,000 行。也不是一个可contractor_id
键或可索引的字段......那么它真的是 6 单向半打吗?还是出于性能考虑,我需要创建一个单独的列是否有特定原因?
取决于您的 MySQL 版本。
在 MySQL 5.7 中,可以基于 json_extract() 表达式创建一个虚拟列,并在该虚拟列上创建一个索引。但是您必须使用该虚拟列进行搜索才能使用索引。
在 MySQL 8.0 中,您可以在表达式上创建虚拟索引,而无需先创建虚拟列。但是对 json 表达式的索引是有限制的。
所以我必须将它转换为一个整数:
优化器似乎能够解决这个问题,我什至可以根据部分表达式来使用索引。
但是,RDBMS 中关于 JSON 的所有这些特性都是一个麻烦。您最终会被迫采用复杂且需要深入了解高级功能的解决方案。
为什么不直接创建
contractor_id
或任何其他要作为普通列索引的属性?那要简单得多。我看到人们在 MySQL 中使用 JSON 的次数越多,我就越觉得它是添加到产品中的最糟糕和最不必要的特性之一。
在某些情况下,当您必须存储具有可变字段的数据时,您可能确实需要一个“半结构化”列。JSON 对此很有用,或者 XML、YAML 或 protobufs 等。但是让它们像普通列一样支持 SQL 操作并不是一个好策略。
对要搜索或排序的属性使用普通列。如果必须,请仅使用 JSON 作为“有效负载”列来存储可变数据。
您可能还喜欢我的演讲How to Use JSON in MySQL Wrong。我在 MySQL 8.0 之前开发了该演示文稿,因此它不包括表达式索引,但其他点仍然正确,例如 JSON 需要更多空间来存储相同的数据。