存储 JSON 文档
适用范围:SQL Server 2016 (13.x) 及更高版本
Azure SQL 数据库
Azure SQL 托管实例
SQL 数据库引擎提供可使用标准 SQL 语言分析 JSON 文档的本机 JSON 函数。 可以将 JSON 文档存储在 SQL Server 或 SQL 数据库中,并像在 NoSQL 数据库中一样查询 JSON 数据。 本文介绍存储 JSON 文档的相关选项。
JSON 存储格式
第一个存储设计决策是,如何在表中存储 JSON 文档。 有以下两个可用选项:
LOB 存储 - JSON 文档可按原样存储在数据类型为json 或 nvarchar 的列中。 这是快速数据加载和引入的最佳方式,因为加载速度与字符串列的加载速度是匹配的。 如果没有为 JSON 值编制索引,这种方法可能会额外带来查询/分析时间方面的性能损失,因为必须在运行查询时分析原始 JSON 文档。
关系存储 - 使用
OPENJSON
、JSON_VALUE
或JSON_QUERY
函数将 JSON 文档插入表中时,可以分析这些文档。 输入 JSON 文档中的片段可以存储在包含数据类型为 json 或 nvarchar 的 JSON 子元素的列中。 这种方法会延长加载时间,因为 JSON 分析是在加载过程中完成的;但查询与关系数据的经典查询的性能是匹配的。目前,JSON 数据类型 在 Azure SQL 数据库中可用。
目前在 SQL Server 中,JSON 不属于内置数据类型。
经典表
在 SQL Server 或 Azure SQL 数据库中存储 JSON 文档最简单的方法是创建一个只有两列的表,一列为文档 ID,一列为文档内容。 例如:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max)
);
或者,在受支持的情况下:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] json
);
此结构等效于可在经典文档数据库中找到的集合。 主键 _id
是一个自动递增的值,该值为每个文档提供唯一标识符并可用于快速查找。 此结构最适合用于经典 NoSQL 方案,即通过 ID 检索文档或通过 ID 更新存储的文档。
- 使用可用于存储 JSON 文档的本机 json 数据类型。
- nvarchar(max) 数据类型可存储最大 2GB 大小的 JSON 文档。 但是,如果确定 JSON 文档不超过 8KB,出于性能原因,建议使用 nvarchar(4000) 而不是 nvarchar(max)。
上例创建的示例表假定在 log
列中存储的 JSON 文档有效。 如需确保在 log
列中保存的 JSON 文档有效,可在该列添加 CHECK 约束。 例如:
ALTER TABLE WebSite.Logs
ADD CONSTRAINT [Log record should be formatted as JSON]
CHECK (ISJSON([log])=1)
每次有人在该表中插入或更新文档时,此约束都会验证 JSON 文档的格式是否正确。 如果没有该约束,该表的插入功能会得到优化,因为任何 JSON 文档无需进行任何处理都可直接添加到该列。
在表中存储 JSON 文档时,可使用标准 Transact-SQL 语言查询文档。 例如:
SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
FROM WebSite.Logs
WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
GROUP BY JSON_VALUE([log], '$.severity')
HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC
可使用任意 T-SQL 函数和查询子句来查询 JSON 文档。 SQL Server 和 SQL 数据库不会在可以用来分析 JSON 文档的查询中引入任意约束。 可通过 JSON_VALUE
函数从 JSON 文档中提取值,并在查询中使用该值,如同使用任何其他值一样。
能够使用大量 T-SQL 查询语法是 SQL Server 和 SQL 数据库与经典 NoSQL 数据库的主要区别 - 在 Transact-SQL 中,可能有需要处理 JSON 数据的任何函数。
索引
如果发现查询频繁按某一属性(例如 JSON 文档中的 severity
属性)搜索文档,则可在该属性上添加行存储非聚集索引来加速查询。
可创建计算列,在指定的路径(即 $.severity
路径)上公开 JSON 列中的 JSON 值,并在此计算列上创建标准索引。 例如:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max),
[severity] AS JSON_VALUE([log], '$.severity'),
index ix_severity (severity)
);
此示例中使用的计算列是非持久化列或虚拟列,不会向表添加额外空间。 索引 ix_severity
用它来提升查询性能,如下例所示:
SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'
此索引的一个重要特征是,它可用于识别排序规则。 如果原始 nvarchar 列包含 COLLATION
属性(例如,区分大小写或日语),则该索引按照与 nvarchar 列关联的语言规则或区分大小写规则进行组织。 如果正在开发适用于全球市场的应用程序且在处理 JSON 文档时需要使用自定义语言规则,那么此排序规则感知功能会非常有用。
大型表和列存储格式
如需在集合中包含大量 JSON 文档,建议在该集合上添加聚集列存储索引,如下例所示:
create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
[_id] bigint default(next value for WebSite.LogID),
[log] nvarchar(max),
INDEX cci CLUSTERED COLUMNSTORE
);
聚集列存储索引提供较高的数据压缩率(高达 25 倍),可以极大地减少存储空间需求、降低存储成本并提高工作负荷的 I/O 性能。 此外,由于聚集列存储索引针对表扫描和 JSON 文档上的分析进行了优化,因此这类索引最适合用于日志分析。
前面的示例使用序列对象向 _id
列分配值。 序列和标识均为 ID 列的有效选项。
频繁更改的文档和内存优化表
如果希望在集合中进行大量更新、插入和删除操作,可将 JSON 文档存储在内存优化表中。 内存优化 JSON 集合始终将数据保留在内存中,因此不会产生任何存储 I/O 开销。 此外,内存优化 JSON 集合是完全无锁的,这意味着文档上的操作不会锁定其他任何操作。
将经典集合转换为内存优化集合只需执行一个操作,即在表定义后指定 WITH (MEMORY_OPTIMIZED=ON)
选项,如下例所示。 完成操作后,即可获得内存优化版的 JSON 集合。
CREATE TABLE WebSite.Logs (
[_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
[log] nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)
内存优化表最适合用于频繁更改的文档。 考虑内存优化表时,还要考虑性能。 如果可能,为内存优化集合中的 JSON 文档使用 nvarchar(4000) 数据类型,而不是 nvarchar(max),因为它可以极大地提升性能。 内存优化表不支持 json 数据类型。
与经典表一样,可以使用计算列将索引添加到在内存优化表中公开的字段上。 例如:
CREATE TABLE WebSite.Logs (
[_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
[log] nvarchar(max),
[severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
INDEX ix_severity (severity)
) WITH (MEMORY_OPTIMIZED=ON)
若要最大化性能,请将 JSON 值强制转换为用于保留属性值的最小可能类型。 在前面的示例中,使用的是 tinyint。
还可将更新 JSON 文档的 SQL 查询放置在存储过程中,以获得本机编译的优势。 例如:
CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN
ATOMIC WITH (transaction isolation level = snapshot, language = N'English')
UPDATE WebSite.Logs
SET [log] = JSON_MODIFY([log], @Property, @Value)
WHERE _id = @Id;
END
此本机编译的过程生成查询并创建运行查询的 .DLL 代码。 本机编译的过程是查询和更新数据的一种更快的方法。
结束语
可使用 SQL Server 和 SQL 数据库中的本机 JSON 函数处理 JSON 文档,如在 NoSQL 数据库中一样。 每种数据库(关系型或 NoSQL)在 JSON 数据处理方面都各有优缺点。 在 SQL Server 或 SQL 数据库中存储 JSON 文档的关键优势在于,可以获得完整的 SQL 语言支持。 可以使用丰富的 Transact-SQL 语言处理数据和配置各种存储选项,从列存储索引的高压缩比和快速分析到内存优化表的无锁处理方式。 同时,还可获取成熟的安全性和国际化功能带来的优势,这些功能可在 NoSQL 方案中重复使用。 鉴于本文中所述的理由,建议考虑在 SQL Server 和 SQL 数据库中存储 JSON 文档。
详细了解 SQL Server 和 Azure SQL 数据库中的 JSON
有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视频介绍,请观看以下视频: