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

时间：2022-06-06 03:50:43

我们知道统计信息会直接决定关系引擎产生何种执行计划，这篇文章通过演示2个例子像大家展示

1、统计信息对连接方式的影响

2、统计信息对单表数据获取方式的影响

以下内容是我曾经做过的一次培训内容，测试环境是SQLServer 2008 R2。如果你还不明白什么是统计信息，请点这里

先来看下统计信息对连接方式的影响，

首先创建一个测试DB,并将“自动创建统计信息”和“自动更新统计信息”设置成OFF，然后运行一下代码创建相关表和写入记录

CREATE TABLE CHECK2_T1
(
    ID INT,
    C1 CHAR(800)
)

CREATE TABLE CHECK2_T2
(
    ID INT,
    C1 CHAR(8000)
)

--向两个表中插入数据
BEGIN TRAN
DECLARE @I INT
SET @I=1
WHILE @I<=100000
 BEGIN
    INSERT INTO CHECK2_T1 SELECT @I,'C1'
    
    SET @I=@I+1
 END
COMMIT TRAN

BEGIN TRAN
DECLARE @I INT
SET @I=1
WHILE @I<=500000
 BEGIN
    INSERT INTO CHECK2_T2 SELECT @I,'C2'
    
    SET @I=@I+1
 END
COMMIT TRAN

然后执行以下两句sql，②中的sql用了表提示HASH,目的是模拟有统计信息的情况，稍后会演示有统计信息的情况

下面两句sql根据查询条件可知结果集为0行，我们看下他们的执行计划。

①SELECT A.* FROM CHECK2_T1 A INNER JOIN CHECK2_T2 B ON A.ID=B.ID WHERE A.C1='C3'

②SELECT A.* FROM CHECK2_T1 A INNER HASH JOIN CHECK2_T2 B ON A.ID=B.ID WHERE A.C1='C3'

执行计划如下：

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

对比以上两个执行计划，发现①中的两表连接用的是“合并连接”，CHECK2_T2这张被全表扫描，现在你应该明白我们将CHECK2_T2表模拟成一条记录一个8K页的意义，实际上单独这张表已经占用了50W个page，大约4G磁盘空间，对它进行表扫描自然会慢。

看了①的执行计划，我们知道优化的话就是要去掉这个“合并连接”，改成“嵌套循环”或者“哈希连接”都能达到我们的效果，于是就有了②sql中我们通过连接提示强制计划走“哈希匹配”，我们看到②的sql是瞬时出来的，达到了我们的目的，当然我们也可以将"HASH"提示改成“LOOP”提示，让查询走“嵌套循环”的方式，其效果也是瞬时出来的，查询计划如下

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

现在我们看到通过添加连接提示达到了想要的执行计划，但这并不是我们建议的，我们看到执行计划中表扫描的图标中有个黄色的三角叹号，鼠标移上去它提示我们该表上缺少XX统计信息，比如CHECK2_T1上提示我们C1列上缺少统计信息，这个直接导致了sqlserver产生了一个不好的执行计划，我们手动在CHECK2_T1表的C1列上创建统计信息WX_C1，

创建完成后再次执行①的sql，发现默认的执行计划开始走“哈希匹配”，也是瞬时完成查询，执行计划如下

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

目前为止向大家展示了统计信息对执行计划中表连接的影响，下一篇文章会演示统计信息对单表查询计划的影响。

需要说明的是，实际上我也无法说清楚的解析统计信息是如何导致不同的的查询计划，这是sqlserver引擎内部的东西，资料也相对较少，但是大家应该明白统计信息的重要性，就像微软官方文档所说的，过期的统计信息可能严重影响系统性能。最好保持默认的数据库“自动创建统计和自动更新统计信息”打开状态，除非你有足够的理由不采用自动创建和自动更新统计信息的功能。

标签：

相关文章