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

时间:2023-11-09 20:42:38

    最近一个朋友和我探讨关于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:

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

    图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指数退让算法不是本文的重点,因此也不多讲了。