前提
本文仅讨论 SQL Server 查询时,
对于非复合统计信息,也即每个字段的统计信息只包含当前列的数据分布的情况下,
在用多个字段进行组合查询的时候,如何根据统计信息去预估行数的。
利用不同字段的统计信息做数据行数预估的算法原理,以及 SQL Server 2012 和 SQL Server 2014 该算法的差异情况,
这里暂时不涉及复合统计信息,暂不涉及统计信息的更新策略及优化相关话题,以及其他 SQL Server 版本计算方式。
统计信息是什么
简单说就是对某些字段的数据分布的一种描述,让 SQL Server 在根据条件做查询的时候,大概知道预期的数据大小,
从而指导生成合理执行计划的一种数据库对象
统计信息的分类
索引上会自动创建统计信息,SQL Server 也会根据具体的查询,在某些非索引自动创建索引,
先来直观地了解一下统计信息长什么样,参考截图,就是这么个样子,
_WA_Sys_**** 开头的是系统根据需要创建的统计信息,
与索引同名的是索引上创建的统计信息,
手动创建统计信息也可以在满足 SQL Server 命名要求的情况下自行命名。
下面一个是索引的统计信息。
统计信息的作用
查询引擎根据统计信息提供的数据做出合理的执行计划。
那么,查询引擎究竟是怎么利用统计信息做预估的呢,
以及下面将要提到的 SQL Server 2014 中较之前的版本有哪些变化?
本文将对此两点做一个简单的分析来说明 SQL Server 是怎么根据统计信息做估算的,下面开始正文。
测试环境搭建
习惯性地做一个演示的环境,创建一个表,写入 100W 的数据后面测试用。
- create table TestStatistics(Id int identity(1, 1), Status1 int, Status2 int, Status3 int)
- insert into TestStatistics values(RAND() * 1000, RAND() * 250, RAND() * 50) go 1000000
表中有四个字段,第一个是自增列,主要看 Status1,Status2,Status3 这三个字段,
三个字段的取值都是用随机数乘以一个常量系数的出来的,
因此这三个字段的数据分布范围分别是
Status1:0-999(1000 种数据分布)
Status2:0-249(250 种数据分布)
Status3:0-49(50 种数据分布)
这个后面有用。
首先在 SQL Server 2012 中做测试
先做这么一个查询:select * from TestStatistics where Status1=885 and Status2=88 and Status3=8
这个查询完成之后,表上自动创建一个三个统计信息,
这三个统计信息分别是 Status1,Status2,Status3 这个三个字段的数据分布描述
首先来看一下其中这个_WA_Sys_00000002_0EA330E9,也即 Status1 这个列的统计信息的详细信息,
注意 All density 字段值,选择性是反应一个表中该字段的重复数据有多少或者说唯一性有多少,
计算方法是:1 / 表中该字段非重复个数。
上面说了,这个 Status1 这个列的取值范围是 0-999,一共有 1000 中取值可能行,
那么这个选择行就是 1/1000=0.001,所以也是吻合这里的 All density=0.001 的
照这么计算,其余两个字段的选择度分别是 1/250=0.004 和 1/50=0.02,分别如下截图的 All density。
执行计划对数据行的预估
说完统计信息的基础问题之后,我们就可以来观察执行计划对目标数据的预估规律了。
我们来看这么一个查询,如下,注意这个是查询的条件是参数变量,而不是直接的值,后面我会解释为什么这么做。
来观察执行计划对数据行的预估:可以看出来,预估为 4 行。
那么这个 4 行是怎么计算出来的呢?
这就要利用到我们上面的选择性了,
Status1 字段的选择性是 0.001,Status2 的选择性是 0.04,
在 SQL Server 2012 中,对数据行的预估计算方式是各个字段的选择性的乘积,
假如 Pn 代表不同字段的选择性,那么预估行数的计算方法就是: 预估行数 = p0*p1*p2*p3……*RowCount
因此,执行计划显示的:预估行数 = 0.001*0.004 * 总行数(也即 1000000)= 4
说到这里解释两个可能存在的几个疑问:
第一,上述示例是用两个字段查询的,为什么不拿三个字段做演示说明?
首选,不管是多少个字段查询,预估行数符合上述计算方式是没有问题的,
但是如果通过上述公式计算出来的结果非常小,在少于 1 的情况下,SQL Server 显示预估为 1 行。
按照上述计算方法,用三个字段做查询,
预估行数 = 0.001*0.004*0.02 * 总行数(也即 1000000)= 0.08<1,所以预估为 1 行。
第二,为什么不直接用值查询,而是用变量做查询?
熟悉 SQL Server 的同学应该都知道,直接用变量查询的时候,SQL Server 编译的时候不知道具体的参数值,
在不知道具体参数值的情况下,它是使用字段的选择性的时候是用到一般性(或者说是平均)的值,
也就是统计信息中整体计算出来字段的选择性,也即 All density=0.001
这里暂定认为数据分布是均匀的,也即每个值分布差别不大。
但事实上每个值的分布的差别还有存在的,
尤其是分布不均匀的时候,当然这个是另外一个非常大的话题了,这里暂不讨论。
如果直接用明确的值做查询。
比如 select * from TestStatistic where Status1=885 and Status2=88
SQL Server 会根据统计信息中每个字段 :Status1=885 的行数和 Status2=88 行数的具体的值,
利用上述公式做预估
那么就继续用具体的值做演示说明,
可以直接用 where Status1=885 and Status2=88 这个条件查询来观察预估结果。
首先我们看统计信息中 Status1=885 的分布行数,1079 行
然后再看统计信息中 Status2=88 的分布行数,3996 行
利用上述公式,预估行数为 4.31168 行
那么直接利用值做查询是不是这个预估的行数呢?直接上图,完美地吻合了上述的计算方法得到的结果。
第三,没有索引的情况下是符合预估的计算方法,如果创建了索引呢?
查询条件中的各个列的统计信息是非相关的,
如果分别在各个列上创建单个列的索引信息,在查询的时候也属于非相关统计信息。
如截图,也就是说,虽然创建了索引,执行计划发生了变化,
从一开始的表扫描变成了通过两个索引查找后做 hash join,然后 Loop join 查询数据,咱不管它就是变成什么执行计划了
但是统对数据的预估还是跟上面全表扫描一样的,都是预估为 4.31168,没有因为创建了索引以及执行计划发生了变化而改变(预估行数)。
因为即便是创建了单列上的索引,执行计划变了,但是统计信息还是非相关的,也就是一个统计信息只描述一列字段的分布情况。
然后在 SQL Server 2014 中做测试
上述同样的数据,我这里通过 link server 将上述 SQL Server 2012 实例下的测试表的结果导入到 SQL Server 2014 的实例下的表中。
现在表结构和数据完全一致。
首选,做一个同样的测试,利用两个变量查询的查询条件做查询,看看 SQL Server 2014 预估的算法有什么变化。
还记得上面在 SQL Server 2012 中同样的写法,同样的数据的预估的情况吧,刚才预估的是 4 行,现在怎么变成 63.2456 行了?
预估行数的计算公式变了吗,当然变了,这正是本文要说的重点。
那么 SQL Server 2014 中是怎么预估的呢?公式是这么来的:预估行数 = P0*P11/2 * P21/4 * P31/8……* RowCount
那么来根据此计算方式来计算预估行数的问题:预估行数 = 0.001*0.0041/2*1000000 = ?
这里我就不做开方运算了,拿来主义,直接用 SQL Server 来算拉倒了,SQL Server 给我们提供了一个开方函数(SQRT),真 JB 好用。
计算一下结果吧,
没错,是 63.24555,保留四位有效数字的话就是 63.2456 了,预估行数跟上面计算出来的结果也是完全吻合的。
补充测试 1:
同样地,用三个条件做查询,预估算法也同样复合上述公式的结果。
按照公式来计算预估行数,选择性按照整体计算出来的选择性来,同样也是吻合的。
补充测试 2:
如果把查询条件换做具体的值,跟在 SQL Server 2012 中一样,SQL Server2014 也同样会根据具体的值得数据做计算
进行这么个查询:select * from TestStatistics2014 where Status1=858 and Status2=88
解释一下为什么这次 Status1 换成 858 了:
因为即便表结构,数据完全一致吧,受限于统计信息的步长(Steps)只有 200,两个库的统计信息也不完全一致,统计信息不能精确到任何一个值,
我们这里为了演示这个算法,找一个具体的 RANGE_HI_KEY 值,比较容易说明问题。
首先看 Status1=858 的数据分布情况
再看 Status2=88 的数据分布情况
利用上述计算方法计算出来的预估:63.27713
执行计划的预估:63.27713,也是完全吻合的。
补充测试 3,在查询列上创建创建单独的索引
跟 SQL Server 2012 中一样,执行计划发生了变化 ,但是对于数据行的预估,同样并没有因为执行计划的变化而(预估行数)变化。
虽然执行计划变了,但是对数据的预估并没有变化,预估的算法还是符合:预估行数 = P0*P11/2 * P21/4 * P31/8……* RowCount
在此可以看出,执行计划对于(未超过统计信息范围的情况下)数据行的预估,是有一定规律的,
这个规律就是:
SQL Server 2012 中,预估行数 = p0*p1*p2*p3……*RowCount(Pn 为查询字段的选择性),
SQL Server 2014 中,预估行数 = P0*P11/2 * P21/4 * P31/8……* RowCount(Pn 为查询字段的选择性)。
当然如果说统计信息过期或者取样密度不够,那就另当别论了,这个就关系到统计信息的更新策略问题了,也是一个非常大而且非常现实的问题,暂不深入展开讨论。
所以一开始我说暂不考虑统计信息自身是否理想,这里是在统计信息非常完整的情况下做测试的。
微软为什么在 SQL Server 2014 中,对非相关且未超出统计信息范围的预估行数算法做这么一个变化,
因为 PN 的值是小于 1 的
预估行数的计算方法从 p0*p1*p2*p3……*RowCount 变化为 P0*P11/2 * P21/4 * P31/8……* RowCount,显然是增加了预估行数的大小,
同时本文未提及的另外一种情况:对于超出统计信息范围的情况下,新的预估方法也增加预估行数的大小,
从整体上看,算法是倾向于 "估多不估少" 的,有这么一个改变
至于为什么要做出这个改变?
如果经常做 SQL 优化的就会发现,不少问题都是少估了预期的数据行数(因为种种原因吧,这里暂时不讨论为什么少估),
造成执行 SQL 时分配的资源不够,从而拖慢了 SQL 的执行效率
一个非常典型的问题就是,预估的数据比实际的数据行数小,造成比如内存授予的不够大,以及实际运算过程中采用不合理的执行计划
个人认为,(控制在一定范围之内的)估多的情况下可以通过获取更多的系统资源来提升 SQL 的执行效率,
正常情况下也不会说是跟实际值差的太离谱造成资源的浪费。
当然也有特殊情况,那就另当别论
要注意的是我这里有个前提,非相关的统计信息,不管是没有任何索引,还是是创建和单列上的索引,对应的统计信息,都属于非相关统计信息,
如果创建复合索引(有人习惯叫组合索引),那么执行计划对于数据行的预估并不符合上述算法,具体算法我也不清楚。
此种情况下,在 SQL Server 2012 和 SQL Server 2014 中预估算法也不一样,这个有机会再研究吧。
对于测试结果的补充说明:
测试过程中一定要保证统计信息的完整性,以及取样的百分比问题,理性情况下都是按照 100% 取样的,
中间我略去了一些细节问题,比如没此测试之前都会 update statistics TestStatistic with fullscan,保证 100% 取样。
既然要精确到小数点后几位,当然要求条件是理想情况下的,目的就是一定要排除其他条件对测试结果的影响。
总结:
本文通过一个简单的示例,来了解了 SQL Server 通过统计信息对数据预估的计算方式和原理,以及 SQL Server 2012 和 SQL Server2014 之间的差异。
统计信息对于 SQL 执行计划的选择起着中枢神经般的作用,不光是在 SQL Server 数据库中,包括其他关系数据库,统计信息都是一个非常重要的数据库对象。
可以说,SQL 优化,统计信息以及与之息息相关的执行计划是一个非常重要的因素,了解统计信息方面的知识对性能调优有着非常重要的作用。
在涉及到组合索引上的统计信息情况下,执行计划对数据行的预估,SQL Server2012 和 SQL Server 2014 中也不一样,问题将会更加有趣,待有时间再写吧。
参考: 大神的
以及
来源: http://www.cnblogs.com/wy123/p/5790855.html