统计--VARCHAR与NVARCHAR在统计预估上的区别

时间:2021-11-12 00:21:27

最近遇到一个问题,当查询使用到模糊查询时,由于预估返回行数过高,执行计划认为索引查找+Key Lookup的成本过高,因此采用Clustered Index Scan的方式,消耗大量逻辑IO,执行计划较差。

经过测试,发现对于模糊查询,NVARCHAR和VARCHAR的预估返回行数差距很大,因此拿出来供大家一起测试。

首先生成测试数据,分别创建TB101和TB102的表,表上有相同的聚集索引和非聚集索引,表中有100w数据,创建测试数据脚本如下:

DROP TABLE TB101
GO
DROP TABLE TB102
GO
SELECT
CAST(NCHAR(19968+20902*RAND(RID))+NCHAR(19968+20902/2*RAND(RID))+NCHAR(19968+20902/3*RAND(RID)) AS varchar(40)) AS RName
,*
INTO TB101
FROM(
SELECT ROW_NUMBER()OVER(ORDER BY T1.OBJECT_ID DESC) AS RID,T1.* FROM sys.all_objects T2
CROSS JOIN sys.all_columns T1
) AS T
WHERE T.RID<1000000
GO
SELECT * INTO TB102 FROM TB101
GO
ALTER TABLE TB102
ALTER COLUMN RName NVARCHAR(40)
GO
CREATE UNIQUE CLUSTERED INDEX IDX_RID ON TB101(RID)
GO
CREATE UNIQUE CLUSTERED INDEX IDX_RID ON TB102(RID)
GO
CREATE INDEX IDX_Name ON TB101(RName)
GO
CREATE INDEX IDX_Name ON TB102(RName)
GO EXEC sp_spaceused 'TB101'
EXEC sp_spaceused 'TB102'
GO

统计--VARCHAR与NVARCHAR在统计预估上的区别

两表使用空间相同,数据相同。

测试前先更新下统计:

--更新统计
UPDATE STATISTICS TB101 WITH FULLSCAN
GO
UPDATE STATISTICS TB102 WITH FULLSCAN

开始测试1

SELECT RName FROM TB101
WHERE RName LIKE '你好%'

其执行计划为:
统计--VARCHAR与NVARCHAR在统计预估上的区别

测试2:

SELECT RName FROM TB102
WHERE RName LIKE N'你好%'

统计--VARCHAR与NVARCHAR在统计预估上的区别

感谢网友“ 害怕飞的鸟”的提醒,我们测试了以中文开头的模糊查询,需要测试以字母开头的模糊查询。

因此重新创建测试用例(生成新的测试数据目的为了避免查询值落在统计的两端,原理请参考大神高继伟的SQL Server 统计信息(Statistics)-概念,原理,应用,维护

准备测试数据:

SELECT
CAST(NCHAR(65+25*RAND(RID))+NCHAR(24*RAND(RID))+NCHAR(19968+20902/2*RAND(RID))+NCHAR(19968+20902/3*RAND(RID)) AS varchar(40)) AS RName
,*
INTO TB103
FROM(
SELECT ROW_NUMBER()OVER(ORDER BY T1.OBJECT_ID DESC) AS RID,T1.* FROM sys.all_objects T2
CROSS JOIN sys.all_columns T1
) AS T
WHERE T.RID<1000000
GO
SELECT * INTO TB104 FROM TB103
GO
ALTER TABLE TB104
ALTER COLUMN RName NVARCHAR(40)
GO
CREATE UNIQUE CLUSTERED INDEX IDX_RID ON TB103(RID)
GO
CREATE UNIQUE CLUSTERED INDEX IDX_RID ON TB104(RID)
GO
CREATE INDEX IDX_Name ON TB103(RName)
GO
CREATE INDEX IDX_Name ON TB104(RName)
GO

测试1:

SELECT RName FROM TB103
WHERE RName LIKE 'A你好%'

统计--VARCHAR与NVARCHAR在统计预估上的区别

测试2:

SELECT RName FROM TB104
WHERE RName LIKE N'A你好%'

统计--VARCHAR与NVARCHAR在统计预估上的区别

通过上面四个测试,可以得出以下结论:

1. 当字段的数据类型为NVARCHAR时,无论模糊查询以中文还是英文字母开头,预估返回行数和实际返回行数相差不多

2. 当字段的数据类型为VARCHAR且模糊查询以英文字母开头,预估返回行数和实际返回行数相差不多

3.  当字段的数据类型为VARCHAR且模糊查询以中文开头,预估返回行数和实际返回行数相差较大。

--==============================================================

当预估返回行数与实际返回行数相差较大时,就很容易生成较差的执行计划,如对于查询:

SELECT * FROM TB101
WHERE RName LIKE '你好%'

由于预估索引查找会返回50w的数据,查询优化器引擎认为如果使用索引查找+Key Lookup就会消耗上200W+的逻辑IO, 效率会远低于表扫描,因此有了下面的执行计划:
统计--VARCHAR与NVARCHAR在统计预估上的区别

而实际上,经过索引查找后,只会返回少量的数据行,这些行做Key Lookup也只会消耗少量的逻辑IO,因此索引查找+Key Lookup是最高效的。

解决办法:

对于这种问题,可以有几种办法处理:

1. 强制索引查找

SELECT * FROM TB101 WITH(FORCESEEK)
WHERE RName LIKE '你好%'

统计--VARCHAR与NVARCHAR在统计预估上的区别

2. 使用隐式转化

SELECT * FROM TB101
WHERE RName LIKE N'你好%'

统计--VARCHAR与NVARCHAR在统计预估上的区别

经过一次隐式转换后,预估返回行数出奇地下降下来,生成了正确的执行计划(看来隐式转换也是有存在价值地哦)

3. 如果不想修改程序的话,可以考虑使用参数化和执行计划指南来实现

--=========================================================================================

总结(以下结论未经过大神认证,请自行组鉴别正确性):

1. 当字段的数据类型为NVARCHAR时,无论模糊查询以中文还是英文字母开头,预估返回行数和实际返回行数相差不多

2. 当字段的数据类型为VARCHAR且模糊查询以英文字母开头,预估返回行数和实际返回行数相差不多

3. 当字段的数据类型为VARCHAR且模糊查询以中文开头,预估返回行数和实际返回行数相差较大。

4. 隐式转换未必会导致表扫描或索引扫描,也未必会导致执行计划质量不优。

--===========================================================

统计--VARCHAR与NVARCHAR在统计预估上的区别

统计--VARCHAR与NVARCHAR在统计预估上的区别