来自:Doubl_K-博客园.
链接:
好的语句就像这辆车,跑的又快又帅气!今天这里介绍一些技巧让你可以改装一下自己的车!
重中之重---语句执行顺序
在QQ群和人聊天的时候突然有位群友说:我才知道原来语句走索引是按照slct的字段筛选的!振振有词,非常肯定!另一个群友反问updat呢?看起来很小白的问题,但确实让我很震惊!所以我们先看看语句的执行顺序
如果我没记错这是《SQLSERVER技术内幕--查询》这本书的开篇第一章第一节。书的作者也要让读者首先了解语句是怎么样的一个执行顺序,因为不知道顺序何谈写个好语句?
查询的逻辑执行顺序:
()FROMlft_tabl
(2)ONjoin_condition
(3)join_typJOINright_tabl
(4)WHEREwhr_condition
(5)GROUPBYgroup_by_list
(6)WITH{cub
rollup}
(7)HAVINGhaving_condition
(8)SELECT(9)DISTINCT()top_spcificationslct_list
(0)ORDERBYordr_by_list
标准的SQL的解析顺序为:
().FROM子句组装来自不同数据源的数据
(2).WHERE子句基于指定的条件对记录进行筛选
(3).GROUPBY子句将数据划分为多个分组
(4).使用聚合函数进行计算
(5).使用HAVING子句筛选分组
(6).计算所有的表达式
(7).使用ORDERBY对结果集进行排序
执行顺序:
、FROM:对FROM子句中前两个表执行笛卡尔积生成虚拟表vt
2、ON:对vt表应用ON筛选器只有满足join_condition为真的行才被插入vt2
3、OUTER(join):如果指定了OUTERJOIN保留表(prsrvdtabl)中未找到的行将行作为外部行添加到vt2生成t3如果from包含两个以上表则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束
4.WHERE:对vt3应用WHERE筛选器只有使whr_condition为tru的行才被插入vt4
5、GROUPBY:按GROUPBY子句中的列列表对vt4中的行分组生成vt5
6、CUBE
ROLLUP:把超组(suprgroups)插入vt6生成vt6
7、HAVING:对vt6应用HAVING筛选器只有使having_condition为tru的组才插入vt7
8、SELECT:处理slct列表产生vt8
9、DISTINCT:将重复的行从vt8中去除产生vt9
0、ORDERBY:将vt9的行按ordrby子句中的列列表排序生成一个游标vc0
、TOP:从vc0的开始处选择指定数量或比例的行生成vt并返回调用者
我们了解了sqlsrvr执行顺序,请以前不知道的看官们,反复试验反复记忆!那么我们就接下来进一步养成日常sql好习惯,也就是在实现功能的同时又考虑性能的思想!
设计思路
具体写法的优化请不要着急,那都是小儿科!
设计思路说的有点大了,下面介绍几个最常见的设计问题!
循环改批量
循环单条操作,请改成批量操作,如果没办法修改,请尽量想办法修改!这算是最常见的吧:
、应用代码端一记for循环再恶心点的每次打开关闭连接,跑个几分钟,数量大点几小时。请把你的每次for循环出来的结果放在一个datatabl,list啥的,不要找到一条就往数据库写一条!
2、数据库中的游标也是差不多的道理,如果有可能不用游标循环一条一条处理,请尽量不要使用。如果自己认为必须用,也请问问别人是否可以有其他方式做批量!
3、如果没法避免一条一条的写入,那么在处理前显示开启一个事务bgintran在处理完成后
建立索引后,同样并不是每个查询都会使用索引,在使用索引的情况下,索引的使用效率也会有很大的差别。如上面缺失的索引我们添加上以后再查询!
索引查找(sk),一般为最优(但查找也要看查找的筛选性),尽量吧whr条件中的字段建成一个组合索引,并且包含要查询slct中的字段。这里就不继续深入了。
看懂执行计划创建
如何看懂执行计划这就是一个可以写几百页书的话题了,但是看懂执行计划是做优化的重中之重了!以后的文章中会详细讲解。
通过执行计划可以看出语句的主要消耗到底在哪里,另外配合ststatisticsioon等分析读次数,也是优化的关键,创建或优化索引页是主要从这里出发。
语句常规习惯
只返回需要的数据
返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节,如果返回不需要的数据,就会增加服务器、网络和客户端的无效劳动,其害处是显而易见的,避免这类事件需要注意:
横向来看:
、不要写SELECT*的语句,而是选择你需要的字段。
2、当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。参见:细心很重要---猜猜这个SQL执行的什么意思
纵向来看:
、whr条件要尽量的多且保证高筛选性。
2、业务中很常见要返回大批量数据到前端,但是这些数据真的都是必要的么?前端是否可以加一些默认条件呢?
减少不必要的操作
写语句之前,理清你的思路!
、杜绝不必要的表连接,多一个表链接代表多很大部分开销。
2、减少不必要的条件判断,很多时候前台传入为空值得时候后台语句被写成XX=XXORXXISNULLORXXLIKEOR...OR...OR等。这是比较经典的问题了,请加入判断在拼入最后的条件!
3、你的语句需要去重复么?distinct、union等操作
4、LEFTJOIN和innrjoin的区别,是否真的需要lftjoin,否则选用innrjoin来减少不必要的数据返回。
5、ordrby你的语句是否需要排序?排序是否可以通过索引来降低性能消耗?我见过竟然插入数据也带着ordrby的!
尽量早的筛选
、最经典的例子就是whr和having的区别,看过语句执行顺序你应该已经明白了。能写在whr中不要放在having中。
2、使用临时表降低语句复杂性,要降低临时表的数据量,也就是要把有条件的表尽量关联并做成临时表。
3、前面提到的隐式转换,索引字段使用计算或函数,也会导致数据不能尽早筛选。
常用的写法误区(以下都是网上片面结论)
所有别人提到的方法到底有无效
、or要用unionall代替(or是很常规的一种写法,情况分很多种,一个表的两个条件用a.a=Xora.a=XX,一个表两个字段用a.a=Xora.b=x,两个不同表字段用a.a=Xorb.a=X这是网上说的unionall代替的)
2、避免使用in、notin(数据量小的时候不会有问题,如果数据量大可能影响性能,数据量大处理方式先把in中的数据放入临时表)
3、事务操作过程要尽量小,能拆分的事务要拆分开来。(前文中提到的例子,有些情况循环写入下,显示开启一个大事务会有很大帮助)
4、使用with(nolock)查询语句不会阻塞(一般情况下是这样,但是如果有架构修改或快照发布等使用with(nolock)也会阻塞)
5、用xists代替in(情况也很复杂不能一概而论)
-----博客地址----
Exprt诊断优化系列北京中科医院是假的吗上海治疗白癜风医院