SQL Server 执行计划利用统计信息对数据行的预估原理以及SQL Server 2014中预估策略的改变

yw6866

　　前提
　　本文仅讨论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中也不一样，问题将会更加有趣，待有时间再写吧。
　　参考：Fanr_Zh 大神的 http://www.cnblogs.com/Amaranthus/p/3678647.html
　　以及 http://msdn.microsoft.com/en-us/library/dn673537.aspx