统计信息对执行计划的影响(二)

上一篇文章 统计信息对执行计划的影响(一) 演示了统计信息对连接方式的影响，这一篇将给大家演示统计信息对单表数据获取方式的影响

在上次的测试DB上执行以下代码创建测试表及插入数据

--创建测试表
CREATE TABLE CHECK2_T3
(
    ID INT,
    COL1  CHAR(800),
    COL2  INT,
    COL3  CHAR(5000)
)


---向表中插入数据
BEGIN TRAN
DECLARE @I INT,@COL1 CHAR(1)=''
SET @I=1
WHILE @I<=10000
 BEGIN
    IF @I%4=0 
    BEGIN SET @COL1='B' END
    ELSE IF @I%250=0
    BEGIN SET @COL1='C' END
    ELSE
    BEGIN SET @COL1='A' END
    
    INSERT INTO CHECK2_T3 SELECT @I,@COL1,cast(rand()*1000 AS INT),'X'
    SET @I=@I+1
 END
COMMIT TRAN

然后执行下面代码在COL1上创建非聚集索引，我们知道创建索引的时候自动创建与之相关的统计信息，WITH(STATISTICS_NORECOMPUTE = ON)是禁用其统计信息自动更新

--在COL1列上创建非聚集索引，并禁用自动更新统计信息
CREATE NONCLUSTERED INDEX NCIX_COL1 ON CHECK2_T3(COL1) WITH(STATISTICS_NORECOMPUTE = ON)

--查看刚建立的索引的统计信息
DBCC SHOW_STATISTICS(CHECK2_T3,NCIX_COL1)

然后再执行以下sql，看到执行计划是用的索引查找

--COL1为'C'的只有20行，执行以下语句查询采用索引查找
SELECT AVG(COL2) FROM CHECK2_T3 
WHERE COL1='C'

再往表内插入30W条C记录，因为已经禁用自动更新统计信息，所以统计信息没有变化

BEGIN TRAN
DECLARE @I INT
SET @I=1
WHILE @I<=300000
 BEGIN    
    INSERT INTO CHECK2_T3 SELECT 100001,'C',cast(rand()*1000 AS INT),'XD'
    SET @I=@I+1
 END
COMMIT TRAN

这时C的记录数变成30W+,接着执行以下两条sql，②的sql使用了表提示 ，目的是让查询走全表扫描，①的执行计划和之前的一样，这时RID运算符的开销就相当高，以下两句对比，表扫描体现性能优势

以下两句在我本机测试的速度②比①快了一倍，多次执行注意清空缓存

① SELECT AVG(COL2) FROM CHECK2_T3 WHERE COL1='C'

② SELECT AVG(COL2) FROM CHECK2_T3 WITH(INDEX(0)) WHERE COL1='C'

 

①的执行计划之所以没有走表扫描，是因为我们没有更新统计信息,我们更新统计信息后再次执行①，发现执行计划变成了表扫描

到这里演示完成。

总结，相比统计信息对连接方式的影响，对单表查询方式的影响效果不那么显著，因为表的大小变化对统计信息更新前后的运算符都起作用。如上，虽然表扫描提升了效率，但依然不能被我们接受，我们希望更快，最好避开对原表数据页的查询，那就可以在COL1索引里包含COL2字段，这样直接扫描COL1字段上的索引便可以完成这个查询，正如执行计划上绿色提示行“缺少索引”。创建这个包含列索引后，sq效率大大提升，可以打开STATISTICS IO观察逻辑读大量减少。

另外需要说的是，通常情况下sqlserver提示的缺失索引都能带来查询效率的提升，但你也应该明白每创建一个索引都会降低系统写的效率，关于这些不在本篇文章讨论。我会在以后的专门系列里分享一些优化手法及注意事项。