sql-server - JSON 列或传统列

Question

我的团队正在开发一个发票模块，我们的用户需要能够将行项目添加到发票中并将这些行项目存储在我们的数据库中。这些订单项可以在初始创建后进行编辑。

发票的行项目看起来像这样。

LineItemName
EnteredBy
Quantity
CostPerUnit
Subtotal

我们的一位团队成员建议我们将行数据存储到 JSON 列中，而不是多个 SQL 列中。他的论点是，将所有行项目数据存储到单个 JSON 列中会更简单，而不是编写代码来检测哪些行项目可能已被删除、更新、重新排序或以其他方式从数据库的原始状态进行操作。

我以前没有使用过 JSON 列，据我所知，使用这些列时存在许多性能问题，并且在从 JSON 数据列构建查询时存在一些额外的复杂性。我们必须报告这些行项目，因此性能绝对是一个问题。我们还运行 SQL Server 2012，据我所知，它不包含对 JSON 列的 native 支持，除非我们升级到 SQL Server 2016。旁注，我们很可能会在未来 2-3 年内迁移到 MYSQL。

任何人都可以提供一些有关正确调用的指导吗？我的直觉是，我们应该利用现有方法并编写额外的代码来检测数据库更改，以避免以后出现性能问题和报告复杂性的问题。

Answer 1

简短回答:不要存储在 JSON 中，使用列，这就是它们存在的原因。

长答案

您正在使用关系数据库来存储您的数据，使用这些软件提供的功能来存储和组织您的数据。

正如评论中已经出现的那样，将值存储在单独的列中，允许您执行不同的聚合，对这些列进行过滤，而无需解析非关系数据结构的开销(最有可能使用第三方插件/clr/函数/其他)。

此外，JSON 数据没有固定的结构。如果不解析 JSON 字段并编写自定义验证，则无法验证 JSON 字段中存储的数据的一致性。

在一个字段中存储多个数据也意味着，您不能(或不容易)

• 对嵌入字段使用约束
• 您无法强制嵌入字段的结构、有效范围、每个字段的有效值
• 定义嵌入字段的数据类型
• 对数据建立索引(不包含它们)
• 在这些字段中聚合/搜索
• 扩展系统
• 查询嵌入的元数据、字段列表等

数据库服务器不能

• 跟踪每个字段的索引统计信息
• 使用 JSON 字段优化查询(因为提取数据需要进行字符串操作)
• 无法最佳地存储每个字段的数据。

上述内容很重要，但没有一个列表是完整的。

你会赢得什么？

• 一些字段名称。
• 数据库具有一定的灵 active ，但应用程序的功能要复杂得多，因为所有验证都应在应用程序中完成两次 - 当您想要写入数据时和当您读取数据时。
• 当您必须修复 JSON 字段中的某些内容时，这是一个非常令人头痛的问题。

作为替代方案，您可以使用 XML 列，它由 SQL Server 支持，并且上述某些内容不是问题，但是:它仍然没有固定的结构。 (如果有，您可以将数据存储在传统列中。在这两种情况下，您都必须手动指定结构)。

请注意:您选择的用于存储数据的格式当然是基于意见的，但根据经验，只要传统列能够满足您的需求，就使用传统列，并避免使用序列化数据。特别是如果您只想使用其中的某些部分进行任何类型的计算。

何时可以存储序列化数据:当强制一致性并不重要并且您永远不会将其用于统计查询或过滤时。 (但是，在大多数情况下，“从不”部分是不正确的:))