为什么SQL语句Where 1=1 and在SQL Server中不影响性能

    最近一个朋友和我探讨关于Where 1=1 and这种形式的语句会不会影响性能。最后结论是不影响。

    虽然结论正确，但对问题的认识却远远没有解决问题的根本。实际上在T-SQL语句的书写过程中经常犯得错误就是得出一个很窄的结论，然后教条式的奉若圣经，对于T-SQL领域来说，在网上经常可以看到所谓的优化守则，随便在网上搜了一些摘录如下：

• 不要有超过5个以上的表连接（JOIN）
• 考虑使用临时表或表变量存放中间结果
• 少用子查询
• 视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
• 对出现在where子句中的字段加索引
• 避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描
• 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录
• 如果使用了IN或者OR等时发现查询没有走索引，使用显式申明指定索引
• EXISTS要远比IN的效率高。

       ……….

 

问题出在哪了?

    虽然上述指导意见看上去没什么问题，也不能说完全不正确，但实际上有两个重大问题：

    脱离上下文：很多道理只能在一个上下文范围内生效，脱离了上下文范围就毫无意义。举个例子，平常有人对你说你有点肾虚，我想你的第一反应肯定是想办法捍卫男人的尊严了，但如果你去医院检查医生这么说，那你可能就会一脸虔诚的求教如何补了：-），那举上述摘录的语句例子：1）少用子查询，如果在SQL Server操作XML的XPATH按节点属性筛选的时候，那转换成子查询一定会更快 2）如果使用了IN或者OR等时发现查询没有走索引，使用显式申明指定索引，这种情况查询分析器不走索引一定会有其原因，

 

    不解释本质原因：佛语有云“凡所有相,皆是虚妄,若见诸相非相,即见如来”。请看下面故事:

说有一次两个府吏一起来看病，一个叫倪寻，一个叫李延，两人的症状也一样，都是头痛，身上发热，也许都是感冒吧。而华佗却说：“倪寻应当用下法来治，李延应当用汗法来治（寻当下之，延当发汗）。”旁人认为很奇怪，大家也一定认为很奇怪吧，为什么同样的一个病，同样的症状，会有不同的治疗法子呢？华佗解释了，他说：“倪寻是外实，而立延是内实，所以用了不同的法子。”果然，第二天，他们两的病都好了。

    其实可以看出，完全同样的症状，可以是完全不同的原因，反之，同样的原因，也可以形成完全不同的“相”。如果仅仅是看到“相”而采取应激处理措施，往往结果会不尽人意。

 

Think Like Query Optimizer

    在每一个领域都有其领域内的规则，最简单来说，如果你不符合C#规范去编程，比如错误的使用关键字，那么编译就会报错。当然，每一个领域内还会有一些隐藏的规则，也有人会说是所谓的“潜规则”，这类规则往往不在明面上，比如说你不符合最佳实践编写一段程序，编译不会报错，但因此而引起的性能或是安全性问题就是你需要遵循最佳实践这个“潜规则”才能避免。

    而在SQL Server领域，T-SQL语句到查询结果返回需要经历一个完整的周期，如图1：

   

    图1.T-SQL生命周期

 

    因此，在关系数据库领域，SQL语句的写法只是一个抽象的逻辑，而不是像编程语言那样直接的实现。比如说访问一行数据，如果是编程语言实现，就需要指定连接数据的方式，打开数据，按某个方式取出数据，最后还要关闭连接，而在SQL Server中，T-SQL仅仅是定义如何去获取所需的数据，而无需考虑实现细节。

    图1中从T-SQL到具体返回数据经历了多个步骤，每一个步骤又存在大量的规则。因此在本文提到Where 1=1 and引起的性能问题就需要按照查询分析器的规则去考虑为什么，这也是Think like query optimizer。

 

    在SQL Server中，T-SQL需要编译为执行计划才能去执行，在编译过程中，Query Optimizer需要考虑很多元数据，比如说表上的索引、数据分布、估计行数、一些参数配置、硬件环境等，在这其中，最重要的就是估计行数，SQL Server需要估计行数来估计成本。

 

Where 1=1 and写法为什么不会变慢?

    因为查询分析器在代数树优化阶段就把1=1 直接给过滤掉了。这个功能就是查询优化器中所谓的“Constant Folding”。

    我们这里假设查询分析器在代数树优化阶段没有把where 1=1这种情况直接过滤掉。

    比如语句select * from table where a=1 and b=2 这个语句，SQL Server估计的行数会是：

    a列的选择率*b列的选择率*表中采样的总行数

    因此，当Where 1=1 and a=1时，结果就变为

     1*a列的选择率 *表中采样的总行数=a列的选择率 *表中采样的总行数

 

    因此无论是否有1=1 and，查询分析器都会估计相同的行数，从而拥有同样的执行计划，因此不影响性能。

 

    当我们明白了查询分析器对A and B这种写法是如何估计行数之后，那么我们就可以推算出什么情况A and B可能引起执行计划不准确。从公式来看，SQL Server认为A列和B列是无关联的，如果A和B关联很大，那么估计的行数一定会非常不准。

    这里我们举例，假如表中有100万行数据，where a=1的数据有1万条，where b=1的数据有1万条，则A和B的选择性都是1/100=0.01，在Where中A And B联合的估计行数则变为0.01*0.01=0.0001*100万=100行，假设where a=1 和b=1所筛选的数据为同样的1万行数据，则估计行数为100而实际行数为1万，则可能引起执行计划的不准确，从而引起性能问题。当然，这种情况的确是少数，但发生后往往对性能有一定影响，因此SQL Server 2014新的行数估计采用了指数退让算法，在这种情况下就会估计为1000行，从而引起性能问题的可能性会变小，2014指数退让算法不是本文的重点，因此也不多讲了。

转载于:https://www.cnblogs.com/CareySon/p/4138575.html