为什么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指数退让算法不是本文的重点,因此也不多讲了。
相关文章
- Windows服务器如何修改SQL server内存大小等设置
- 如何使用码匠连接 Microsoft SQL Server
- SQL Server中的Forwarded Record计数器影响IO性能的解决方法
- 优化Oracle SQL优化:提升性能的先进方法(oracle当前sql)
- 优化SQL Server性能:精准利用索引(sqlserver索引的使用)
- the cloud云端 SQL Server:无缝升级云计算时代(sqlserverin)
- Using SQL Server Loops to Speed Up Data Processing(sqlserver循环语句)
- 密码解锁!重置SQL Server 密码步骤指南(重置sqlserver)
- 借助科脉打造SQL Server优异的网络性能(科脉sqlserver)
- serverOracle 与 SQL Server之间的友谊连接(oracle连接sql)
- 借助力控连接SQL Server,构建高效数据库系统(力控连sqlserver)
- SQL Server驱动类:管理数据中的首选方案(sqlserver驱动类)
- SQL Server出现错误0:解决方案(sqlserver错误0)
- SQL Server表之重建:从零到一再上飞(sqlserver表重建)
- 集令人惊叹!SQL Server群集的强大优势(sqlserver群)
- 列SQL Server稀疏列:最优的存储和性能策略(sqlserver 稀疏)
- sql体验SQL Server:本周SQLServer之旅(sqlserver 本周)
- SQL Server无效列处理指南(sqlServer无效列)
- SQL Server拼接字段解密大法(sqlserver拼字段)
- 生成SQL Server报告自动生成:准确可靠的性能分析(sqlserver报告)
- 使用SQL Server分号提升数据库查询性能(sqlserver 分号)
- SQL Server 保证唯一值的完整性(sqlserver值唯一)
- 使用SQL Server代码实现数据表变更(sqlserver代码表)
- 探索Oracle的SQL跟踪工具:优化数据库性能的重要利器(oracle跟踪sql工具)
- SQL Server累加:攻取跃升数据分析新高度(sqlserver 累加)
- 优化SQL Server查询性能的策略(针对sqlserver)
- 重启 SQL Server:解决服务器性能问题(重启 sqlserver)