浅谈SQL Server中统计关于查询的影响解析[MSSQL防范]

图1.统计信息

下面我们通过一个简单的例子来看统计信息是若何影响查询解析器.我成立一个测试表,有两个INT值的列,此中id为自增,ref上成立非堆积索引,插入100条数据,从1到100,再插入9900条等于100的数据.图1中的统计信息就是示例数据的统计信息.

此时,我where后利用ref值作为查询条件,但是给定差别的值,我们可以看出按照统计信息,查询解析器做出了差别的挑选,如图2所示.

图2.按照差别的谓词,查询优化器做了差别的挑选

其实,关于查询解析器来说,柱状图关于直接可以肯定的谓词非常管用,这些谓词比方:

where date = getdate()
where id= 12345
where monthly_sales < 10000 / 12
where name like "Careyson" + "%"

但是关于比方

where price = @vari
where total_sales > (select sum(qty) from sales)
where a.id =b.ref_id

where col1 =1 and col2=2

这类在运行时才能知道值的查询,采样步长就明显不是那么好用了.别的,上面第四行假如谓词是两个查询条件,利用采样步长也并不好用.因为无论索引有多少列,采样步长仅仅存储索引的第一列.当柱状图不再好用时,SQL Server利用密度来肯定最佳的查询线路.

密度的公式是:1/表中唯一值的 个数.当密度越小时,索引越简单被选中.比方图1中的第二个表,我们可以通过以下公式来计算一下密度:

图3.某一列的密度

按照公式可以推断,当表中的数据量渐渐增大时,密度会越来越小.

关于那些不能按照采样步长做出挑选的查询,查询解析器利用密度来预计行数,这个公式为:预计的行数=表中的行数*密度

那么,按照这个公式,假如我做查询时,预计的行数就会为如图4所示的数字.

图4.预计的行数

我们来考证一下这个结论,如图5所示.

图5.预计的行数

因此,可以看出,预计的行数是和实际的行数有出入的,当数据分布均匀时,大概数据量大时,这个偏差将会变的非常小.

由上面的例子可以看到,查询解析器由于依靠于统计信息举行查询,那么过期的统计信息则大概招致低效率的查询.统计信息既可以由SQL Server来举行管理,也可以手动举行更新,也可以由SQL Server管理更新时手动更新.

当开启了自动更新后,SQL Server监控表中的数据更改,当到达临界值时则会自动更新数据.这个尺度是:

上述条件的满意均会招致统计被更新.

当然,我们也可以利用以下语句手动更新统计信息.

UPDATE STATISTICS 表名[索引名]

SQL Server还可以针对不属于任何索引的列成立统计信息来帮忙查询解析器获得"预计的行数".当我们开启数据库级别的选项"自动成立统计信息"如图6所示.

图6.自动成立统计信息

当这个选项设置为True时,当我们where谓词指定了不在任何索引上的列时,列的统计信息会被成立,但是会有以下两种情形例外:

我们可以通过系统视图sys.stats来查看这些统计信息,如图7所示.

图7.通过系统视图查看统计信息

当然,也可以通过以下语句手动成立统计信息:

CREATE STATISTICS 统计名称 ON 表名 (列名 [,...n])

本文简单谈了统计信息关于查询途径挑选的影响.过期的统计信息很简单造成查询性能的降低.因此,按期更新统计信息是DBA重要的工作之一.