前言
把程序对象以json格式序列化后存储在数据库中，是越来越常用的对象持久化方法。通常此类存储并不需要对存储后的json内容进行检索，比如根据对象属性筛选出特定对象并进行操作。但也有些应用场景会遇到这类需求。
最近就遇到一个这方面的问题：涉及到JSON查询、索引视图和全文检索。
具体问题
有一个数据表，结构和索引情况如下：
CREATE TABLE [dbo].[MyTable]
(
[Row_Id] [bigint] NOT NULL,
[Year_Code] [varchar](100) NOT NULL,
[Row_Values] [text] NULL,
[Date_Refreshed] [datetime] NOT NULL,
CONSTRAINT [PK_MyTable_Row_Id] PRIMARY KEY CLUSTERED
(
[Row_Id] ASC
)
)
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_Date_Refreshed] DEFAULT (getdate()) FOR [Date_Refreshed]
GO
CREATE NONCLUSTERED INDEX [IX_MyTable_Code] ON [dbo].[MyTable]
(
Year_Code ASC
)
GO另有一个视图，引用这个表，视图结构如下：
CREATE VIEW [dbo].[vw_MyTable] AS
SELECT
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."id"') AS id,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."year"') AS year_code,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emp_id"') AS emp_id,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emplocalid"') AS emplocalid,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."firstname"') AS firstname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."lastname"') AS lastname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."middlename"') AS middlename,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."dateofbirth"') AS dateofbirth,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."race"') AS race,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."gender"') AS gender
FROM [dbo].[MyTable]
GO常用的使用场景如下：
SELECT
[id]
,[year_code]
,[emp_id]
,[emplocalid]
,[firstname]
,[lastname]
,[middlename]
,[dateofbirth]
,[race]
,[gender]
FROM
[dbo].[vw_MyTable]
WHERE
[year_code] ='yr2023'问题是：基础表中已存在非聚集索引的情况下，在视图上建立索引能否提供更好的查询性能？如果在基础表的`[Row_Values]`字段上建立全文索引呢？
问题分析
首先，可以肯定的说，在这个使用场景下，基础表中的索引是不会起作用的，视图中的列`[year_code]`来自JSON的内容解析，它并未引用基表中的同名字段。这可以通过查询计划来证明，这个测试也能为后面的测试建立基线。
生成测试数据
测试之前先填充一些数据，60万条吧。执行下面的脚本：
DECLARE @i int
DECLARE @j int
DECLARE @n int
DECLARE @r int
DECLARE @gender varchar(10)
DECLARE @y varchar(4)
DECLARE @m varchar(2)
DECLARE @d varchar(2)
DECLARE @btd varchar(10)
SET @i=1
WHILE(@i<=600000)
BEGIN
SET @j=1
WHILE(@j<30)
BEGIN
SET @r=CAST(RAND()*100 AS INT)
IF @r % 2 = 0 SET @gender = 'male' ELSE SET @gender='female'
SET @y = CAST(1960 + @r / 2 AS varchar(4))
SET @r = CAST(RAND()*12+1 AS INT)
SET @m = FORMAT(@r, '00')
SET @r = CAST(RAND()*28+1 AS INT)
SET @d = FORMAT(@r, '00')
SET @btd = @y+'-'+@m+'-'+@d
INSERT INTO [MyTable] (Row_id, Year_Code, Row_Values)
VALUES
(
1000000+@i
,'yr'+CONVERT(VARCHAR(4), 1994+@j)
,JSON_OBJECT('id':@i, 'year':'yr'+CONVERT(VARCHAR(4), 1994+@j), 'emp_id': CAST(RAND()*10000 AS INT), 'emplocalid':'aaaasdaa', 'firstname':'Will', 'lastname':'Smith','middlename':'M', 'dateofbirth':@btd,'race':'who knows', 'gender':@gender)
)
SET @j+=1
SET @i+=1
END
END测试一、无索引直接查询视图
现在可以执行前面常用场景的查询，生成的查询计划如下：
可见并没有使用索引`[IX_MyTable_Code]`，而是执行了一次聚集索引扫描，把全表翻了一遍。
SQL SERVER部署在一台旧笔记本上，重复了几次，查询平均CPU耗时3,050ms。
测试二、使用索引视图进行查询
接下来可以看看使用索引视图会有什么变化。在准备索引视图的时候发现，SQL SERVER的索引视图不支持`TEXT`和`NTEXT`类型的列，文档建议把`TEXT`和`NTEXT`类型转换为`VARCHAR(MAX)`或`NVARCHAR(MAX)`。所以要先对基础表进行处理。
ALTER TABLE [dbo].[MyTable] ALTER COLUMN [Row_Values] VARCHAR(MAX)再建立索引视图，索引视图还有一些具体要求，涉及到的有：
1. 需要使用`WITH SCHEMABINDING`选项建立视图；
2. 需要首先为视图建立唯一聚集索引；
3. 视图中列出的所有字段都必须是确定性的。
另外，原视图中的`JSON_VALUE`函数都使用了`varchar(8000)`作为输出的数据类型，考虑到索引的效率和存储，应当尽可能使用更紧凑和明确的数据类型描述列。
综合以上要求，重新建立视图，并增加视图的索引：
IF OBJECT_ID('[dbo].[vw_MyTable]', 'view') IS NOT NULL
DROP VIEW [dbo].[vw_MyTable];
GO
CREATE VIEW [dbo].[vw_MyTable] WITH SCHEMABINDING AS
SELECT
CAST(JSON_VALUE([Row_Values], '$."id"')AS INT) AS id,
CAST(JSON_VALUE([Row_Values], '$."year"')AS VARCHAR(6)) AS year_code,
CAST(JSON_VALUE([Row_Values], '$."emp_id"')AS INT) AS emp_id,
CAST(JSON_VALUE([Row_Values], '$."emplocalid"') AS VARCHAR(20)) AS emplocalid,
CAST(JSON_VALUE([Row_Values], '$."firstname"')AS VARCHAR(100)) AS firstname,
CAST(JSON_VALUE([Row_Values], '$."lastname"') AS VARCHAR(100)) AS lastname,
CAST(JSON_VALUE([Row_Values], '$."middlename"') AS VARCHAR(100)) AS middlename,
CONVERT(DATE, JSON_VALUE([Row_Values], '$."dateofbirth"'), 102) AS dateofbirth, --只有使用确定性函数测可以
CAST(JSON_VALUE([Row_Values], '$."race"') AS VARCHAR(255)) AS race,
CAST(JSON_VALUE([Row_Values], '$."gender"') AS VARCHAR(10)) AS gender
FROM
[dbo].[MyTable]
GO
--必须先建立唯一的聚集索引
CREATE UNIQUE CLUSTERED INDEX [CLSTIDX_vwMyTable_id] ON [dbo].[vw_MyTable]
(
[id] ASC
)
GO
--再建立需要优化查询的非聚集索引
CREATE NONCLUSTERED INDEX [IDX_vwMyTable_year_code] ON [dbo].[vw_MyTable]
(
[year_code] ASC
)
GO注意视图中列`"dateofbirth"`的类型转换写法与其他列不同，这是因为函数`CAST`在将字符类型转换为`DATETIME`及相关类型时，结果通常是非确定性的。而使用`CONVERT`函数可以通过第三个参数控制输出的格式，使之有确定性的结果。参见文档1和文档2
完成这些工作后，再执行前面的查询，观察查询计划
这次搜索了视图中的非聚集索引，同时引用视图的聚集索引以定位结果行，平均CPU耗时6.4ms，比无索引时快了几百倍。
测试三、使用全文索引查询视图
最后再测试一下使用全文索引。SQL SERVER的全文索引以前接触的很少，还是按步骤一步一步来。
先创建一个全文目录，承载全文索引
CREATE FULLTEXT CATALOG [testjson] WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
GO再针对这个表中的JSON字段建立全文索引
CREATE FULLTEXT INDEX ON MyTable(Row_Values LANGUAGE 1033)
KEY INDEX [PK_MyTable_Row_Id]
WITH STOPLIST = SYSTEM考虑到样例中的JSON里没有中文，所以就用英语字典建立全文索引。
由于查询全文索引需要使用特殊语法`CONTAINS(字段名, 关键词)`，这就只能对基础表进行查询，视图在这里就没有用武之地啦。需要改写原来的查询SQL，大部分直接参考视图的SQL：
SELECT
CAST(JSON_VALUE([Row_Values], '$."id"')AS INT) AS id,
CAST(JSON_VALUE([Row_Values], '$."year"')AS VARCHAR(6)) AS year_code,
CAST(JSON_VALUE([Row_Values], '$."emp_id"')AS INT) AS emp_id,
CAST(JSON_VALUE([Row_Values], '$."emplocalid"') AS VARCHAR(20)) AS emplocalid,
CAST(JSON_VALUE([Row_Values], '$."firstname"')AS VARCHAR(100)) AS firstname,
CAST(JSON_VALUE([Row_Values], '$."lastname"') AS VARCHAR(100)) AS lastname,
CAST(JSON_VALUE([Row_Values], '$."middlename"') AS VARCHAR(100)) AS middlename,
CONVERT(DATE, JSON_VALUE([Row_Values], '$."dateofbirth"'), 102) AS dateofbirth,
CAST(JSON_VALUE([Row_Values], '$."race"') AS VARCHAR(255)) AS race,
CAST(JSON_VALUE([Row_Values], '$."gender"') AS VARCHAR(10)) AS gender
FROM
[dbo].[MyTable]
WHERE
CONTAINS([Row_Values], 'year:yr2023')条件部分因为是直接针对原始的JSON串进行查询，需要匹配的是JSON中的属性名称值对表达式。先不考虑这种方式的在json多层结构中的适用性，假设这个属性只在json结构中出现在单一节点，单纯测试性能表现。生成的查询计划如下：
这次查询使用了全文索引进行查找（图三.FulltextMatch），同时，服务器读取了整张表（图三.ClusteredIndexScan），解析了所有行的字段值预备输出（图三.计算标量），最后对两个结果集进行JOIN操作（图三.JOIN）。
平均CPU耗时416ms，比使用索引视图差距不小，耗时明显集中在表扫描上，虽然只是读取，但在大量数据面前，IO成本非常高。至于查询优化器为什么不先`JOIN`再读取和解析内容，还需要再对SQL SERVER的优化策略作进一步研究。
比较前三次测试的结果，使用索引视图的查询效率远远领先其他方法。使用全文索引因为读取数据的策略的缘故，IO性能并未改善，仅仅在匹配结果的过程得到了优化，但也获得了接近10倍的提升。在更大数据量和更大json结构下的表现还不确定，如果使用全文索引的查询计划因为数据量的缘故调整为先匹配再读取，性能应该会接近使用索引视图。
测试四、基于表的计算字段索引
测试进行到这里，忽然想到其实还有另一个解决方案：更改基础表的结构，把`year_code`字段改为计算字段，值取自`row_values`的解析，再把视图中的`year_code`字段取自基表此字段而非JSON解析，估计这样的效率会更高。不过不确定题主所述的两处`year_code`是否同义，但并不妨碍我们做个测试。
先删掉全文索引避免影响：
DROP FULLTEXT INDEX ON [dbo].[MyTable]
GO视图因为启用了`SCHEMABINDING`，无法直接更改基础表，先删除，本来也要更改视图，后面再重建：
DROP VIEW [dbo].[vw_MyTable]
GO再更改基础表：
因为没办法将表中的字段改为计算字段，所以只能为基础表增加一个计算字段，并为它建立索引，原来的字段和索引删掉。为了索引更有效，这个字段标记为要进行存储`PERSISTED`：
DROP INDEX [IX_MyTable_Code] ON [dbo].[MyTable]
GO
ALTER TABLE [dbo].[MyTable]
DROP COLUMN [Year_Code]
GO
ALTER TABLE [dbo].[MyTable]
ADD [Year_Code2] AS CAST(JSON_VALUE([Row_Values], '$."year"')AS VARCHAR(6)) PERSISTED
GO
CREATE NONCLUSTERED INDEX [IX_MyTable_Code2] ON [dbo].[MyTable]
(
Year_Code2 ASC
)
GO最后重新建立视图，替换掉其中的`year_code`来源：
CREATE VIEW [dbo].[vw_MyTable] AS
SELECT
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."id"') AS id,
year_code2 AS year_code,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emp_id"') AS emp_id,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emplocalid"') AS emplocalid,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."firstname"') AS firstname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."lastname"') AS lastname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."middlename"') AS middlename,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."dateofbirth"') AS dateofbirth,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."race"') AS race,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."gender"') AS gender
FROM [dbo].[MyTable]
GO现在可以执行最初的查询，看看效果：
SELECT
[id]
,[year_code]
,[emp_id]
,[emplocalid]
,[firstname]
,[lastname]
,[middlename]
,[dateofbirth]
,[race]
,[gender]
FROM
[dbo].[vw_MyTable]
WHERE
[year_code] ='yr2023'查询计划如下：
CPU耗时312ms，比不用索引要好很多，但是比索引视图还有很大差距。同时查询优化器提示，建立索引时可以把`[Row_Values]`字段作为`INCLUDE`字段存储进索引空间，以获取更好的性能。观察查询计划，当前计划确实没有使用新建的索引，而是在扫描聚焦索引的时候使用了`[year_code2]`字段的值进行部分扫描，只返回了符合条件的键值（图四.1），然后再从主表获取JSON串并解析。因为大大减少了不必要的JSON串的读取和解析操作，速度得到很大提高。
按优化器的提示，把JSON串附加在索引中，这样可以在找到符合要求的索引值时，就能直接读出JSON串，减少再次定位表空间的操作，可以进一步减少IO操作，提高效率。不过如此一来`[Row_Values]`字段就被复制了一份，典型的空间换时间方案。目前的逻辑读次数是33440，作为后面的对比。执行下面的脚本更改索引设置：
DROP INDEX [IX_MyTable_Code2] ON [dbo].[MyTable]
GO
CREATE NONCLUSTERED INDEX [IX_MyTable_Code2] ON [dbo].[MyTable]
(
Year_Code2 ASC
)
INCLUDE ([Row_Values])
GO再执行查询，这次的查询计划如下：
这次跟预想中一样，使用了非聚集索引查找，索引查完直接计算结果，没有再访问聚焦索引（表空间），计划非常简单。CPU耗时156ms，比上次节省了一半。逻辑读550次，确实大幅减少。但是CPU耗时仍然比索引视图要高出很多。对比查询计划，差别在于其他输出列的解析上。这很好理解，对表的查询条件增加了存储字段和索引，其他字段在输出时仍然需要读取JSON串再解析输出；而对于索引视图，这些输出列在建立视图的聚焦索引时就已经解析出来存储在索引空间里，相当于把所有需要输出的字段预先固化成了表，这样在输出时只需要从索引空间直接提取数据，自然节省大把时间。看来在这个场景下，索引视图才是最佳方案。等效的方案就是把所有需要输出的字段全部计算并存储在表中，就不做测试了。
其他测试数据
总结
- 对于需要频繁筛选其内容的JSON字段，最好能够为待筛选和输出的JSON属性在表中建立物理存储的字段，并设法保持与JSON内容的同步，比如存储计算字段，再恰当设立索引。或者如此例中，建立索引视图，以提高查询效率。这样既能够以数据冗余的方式提高查询效率，又不需要增加复杂的程序逻辑保持JSON内容和冗余字段的同步。但有得必有失，这样做也要付出相应的代价，一是需要更大的存储空间，计算字段持久化和相应的索引都需要占用数据库空间；二是会带来写入性能的损耗，计算字段需要解析文本，字段持久化和索引填充都有IO消耗，这些都会影响写入性能。因此冗余哪些数据、为谁建立索引都需要仔细斟酌，找到恰当的平衡点。
- 为了减少数据查找的IO访问，建立索引时可以把必要的字段INCLUDE进索引空间，同样是以占用更大的索引空间为代价。
- 所有可能的方案都需要进行测试评估。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
