SQL Server中JSON数组对象的索引创建与查询优化问询

问题描述 #

我有一张名为db_elements的表，包含两列：

• Id：类型为binary(255)的主键
• event：类型为nvarchar(max)的JSON内容

event列的JSON示例格式如下：

{
    "id": 123,
    "name": "test",
    "elements": [{
        "element_id": 45,
        "element_type": "type_01"
    }, {
        "element_id": 65,
        "element_type": "type_01"
    }, {
        "element_id": 87,
        "element_type": "type_02"
    }]
}

每行的JSON数组elements的元素数量各不相同，但对象格式一致。

我需要执行的查询语句如下：

SELECT id, event FROM db_elements 
CROSS APPLY OPENJSON(event, '$.elements') WITH (
    element_id int '$.element_id', 
    element_type nvarchar(max) '$.element_type'
) WHERE element_id = 87 AND element_type = 'type_02';

由于表数据量较大且持续增长，希望创建索引加速查询，目前仅找到针对简单字段的索引示例，因此提出以下问题：

1. 是否可创建针对该JSON数组的索引以加速上述查询？
2. 若可以，具体如何操作？
3. 若不可行，有哪些替代方案？

解答 #

1. 是否可以创建索引加速查询？ #

可以。针对这种嵌套JSON数组的查询场景，可通过索引视图或持久化计算列配合索引的方式实现加速（以SQL Server为例，语法匹配该数据库）。

2. 具体操作方案 #

方案一：创建索引视图（推荐）

索引视图会预先将JSON数组展开为关系型行并持久化，查询时直接读取索引视图的索引，避免每次解析JSON的开销：

1. 创建绑定架构的视图，展开JSON数组：

CREATE VIEW vw_db_elements_elements
WITH SCHEMABINDING
AS
SELECT
    d.Id,
    CAST(JSON_VALUE(e.value, '$.element_id') AS int) AS element_id,
    JSON_VALUE(e.value, '$.element_type') AS element_type,
    d.event
FROM dbo.db_elements d
CROSS APPLY OPENJSON(d.event, '$.elements') e;

2. 对视图创建唯一聚集索引（索引视图必须配置唯一聚集索引）：

CREATE UNIQUE CLUSTERED INDEX IX_vw_db_elements_elements 
ON vw_db_elements_elements (Id, element_id, element_type);

后续查询可直接针对视图执行，或由SQL Server自动在原查询中调用该索引视图。

方案二：持久化计算列+非聚集索引

如果仅需快速定位包含目标元素的行，无需展开所有数组内容，可创建标记型计算列并建索引：

1. 添加持久化计算列：

ALTER TABLE db_elements 
ADD HasTargetElement AS IIF(
    JSON_PATH_EXISTS(event, '$.elements[*]?(@.element_id == 87 && @.element_type == "type_02")'),
    1,
    0
) PERSISTED;

2. 创建包含目标返回列的非聚集索引：

CREATE NONCLUSTERED INDEX IX_db_elements_HasTargetElement 
ON db_elements (HasTargetElement)
INCLUDE (Id, event);

修改查询语句利用该计算列过滤：

SELECT Id, event FROM db_elements
WHERE HasTargetElement = 1
CROSS APPLY OPENJSON(event, '$.elements') WITH (
    element_id int '$.element_id', 
    element_type nvarchar(max) '$.element_type'
) WHERE element_id = 87 AND element_type = 'type_02';

3. 替代方案 #

如果无法创建索引（如权限限制、数据库版本不支持），可考虑以下方案：

• 拆分JSON到独立表：创建db_elements_elements表，字段包含Id（外键关联db_elements.Id）、element_id、element_type，通过触发器、应用层逻辑或ETL任务同步event列的JSON数组数据到该表。查询时直接关联此表，性能远高于实时解析JSON。
• 优化查询语句：改用EXISTS子查询形式，减少不必要的行展开，帮助生成更高效的执行计划：

SELECT Id, event FROM db_elements
WHERE EXISTS (
    SELECT 1 FROM OPENJSON(event, '$.elements')
    WITH (element_id int, element_type nvarchar(max))
    WHERE element_id = 87 AND element_type = 'type_02'
);

• 使用内存优化表：若SQL Server版本支持，将db_elements改为内存优化表，这类表对JSON数据的解析和查询性能有显著提升，且支持针对JSON属性的索引。

内容的提问来源于stack exchange，提问作者Satheno