WHERE子句使用JSON_VALUE时，如何加速含大JSON字段的SQL查询？

针对你提到的Clients表（2万多行，Data字段为30k-40k字符的JSON），使用JSON_VALUE查询嵌套字段耗时过长的问题，以下是几种无需修改原表列结构的优化方法：

1. 新增持久化计算列并创建索引 #

通过添加基于目标JSON路径的持久化计算列，将JSON解析的开销提前固化，再给该列建索引，避免查询时逐行解析大JSON。

-- 添加持久化计算列，存储提取出的目标值
ALTER TABLE dbo.Clients
ADD TargetValue AS JSON_VALUE(Data,'$.Parent.Child.ChildOfChild.Value') PERSISTED;

-- 创建包含UniqueId的非聚集索引，让查询直接通过索引返回结果
CREATE NONCLUSTERED INDEX IX_Clients_TargetValue ON dbo.Clients(TargetValue) INCLUDE (UniqueId);

优化后的查询语句：

SELECT UniqueId
FROM dbo.Clients
WHERE TargetValue LIKE 'Value';

注意：如果存在JSON格式不合法的行，需要先清理或处理，否则计算列创建会失败。

2. 使用全文索引加速大文本匹配 #

针对大JSON字段的特性，全文索引比普通LIKE查询效率高很多，适合模糊匹配场景。

-- 创建全文目录（若数据库中没有）
CREATE FULLTEXT CATALOG ft_Clients_Data AS DEFAULT;

-- 给Data字段创建全文索引（需指定表的主键索引名，替换为实际主键索引）
CREATE FULLTEXT INDEX ON dbo.Clients(Data) KEY INDEX PK_Clients_UniqueId;

查询时结合JSON路径的上下文关键词缩小范围：

SELECT UniqueId
FROM dbo.Clients
WHERE CONTAINS(Data, '"Value" AND "Parent" AND "Child" AND "ChildOfChild"');

3. 优化查询语句的匹配逻辑 #

• 如果是精确匹配，把LIKE 'Value'改成=，避免LIKE带来的额外计算：

  SELECT UniqueId
  FROM dbo.Clients
  WHERE JSON_VALUE(Data,'$.Parent.Child.ChildOfChild.Value') = 'Value';
  

• 若必须使用LIKE，避免前缀通配符（如%Value），前缀无通配符的LIKE有可能利用索引（如果存在）。

4. 迁移至内存优化表（SQL Server 2016+） #

将表转换为内存优化表，内存中的JSON解析和查询速度远快于磁盘表，且无需修改原列结构：

-- 创建内存优化表（需指定主键，这里假设UniqueId是主键）
CREATE TABLE dbo.Clients_MemoryOptimized
(
    UniqueId nvarchar(200) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 32768),
    Data nvarchar(max) NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- 将原表数据导入内存优化表
INSERT INTO dbo.Clients_MemoryOptimized SELECT * FROM dbo.Clients;

之后直接查询内存优化表即可获得显著的速度提升。

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