我们做软件开发，大部分人都离不开数据库，尤其是erp开发，和数据库打交道就更频繁了，存储过程动辄几千行，如果数据量大，人员流动大，能否保证系统在接下来的一段时间内顺利运行？我们还能保证下一个人能看懂我的存储过程吗？然后跟大家分享一下公司平时的培训和个人的工作经验，希望对大家有所帮助。要知道sql语句，我想我们需要知道sqlserver查询分析器是如何执行我们的sql语句的。我们很多人会看执行计划，或者使用profile来监控和调优查询语句或存储过程慢的原因，但是如果我们知道查询分析器的执行逻辑顺序，并且我们在开始的时候就很有信心，开始安全吗？1.查询的逻辑执行顺序(1)FROM(2)ON(3)JOIN(4)WHERE(5)GROUPBY(6）与{立方体|rollup}(7)HAVING(8)SELECT(9)DISTINCT(10)ORDERBY(11)标准SQL的解析顺序是：(1).FROM子句汇集来自不同数据源的数据(2)。WHERE子句根据指定条件过滤记录(3)。GROUPBY子句将数据分成多个组(4)。使用聚合函数进行计算(5)。使用HAVING子句过滤组(6)。计算所有表达式(7)。使用ORDERBY对结果集进行排序。2、执行顺序1、FROM：在FROM子句中对前两个表执行fluteCarlproduct生成虚拟表vt12.ON：对vt1表应用ON过滤器，只有当为真时，该行才会被插入到vt23中.OUTER(join)：如果指定了OUTERJOIN，则在保留的表中找不到该行将行作为外部行添加到vt2中生成t3。如果from包含两个以上的表，对上一个join生成的结果表和下一个表重复上述步骤，直接结束。4.WHERE：对vt3应用WHERE过滤器只使用为true插入到vt45.GROUPBY：按GROUPBY子句中的列列表到vt4通过行分组生成vt56.CUBE|ROLLUP：Insertsupergroups（超组）到vt6中生成vt67.HAVING：对vt6应用HAVING过滤器，只将为true的组插入到vt78中。SELECT：处理选择列表到生成vt89。DISTINCT：去除vt8中重复的行，生成vt910。ORDERBY：按照orderby子句中的列列表对vt9的行进行排序，生成游标vc1011.TOP：选择从vc10开始到指定行数或比例的行生成vt11返回给调用者看到这里，是不是和linqtosql的语法有些相似？如果我们了解了sqlserver的执行顺序，那么我们就会进一步养成日常sql的一个好习惯，即在实现功能的同时要考虑性能。数据库是一个可以进行集合操作的工具，我们应该尽量使用这个工具，所谓集合操作，其实就是批操作，就是尽量减少客户端大数据量的循环操作，用SQL语句或者存储过程代替。、网络传输数据、客户端接收数据、客户端处理数据等，如果返回不必要的数据，会增加服务端、网络和客户端的无效劳动，危害是显而易见的。为避免此类事件，需要注意：A.横向查看（1）不要写SELECT*语句，而是选择自己需要的字段。(2)在SQL语句中连接多个表时，请使用表的别名，并在每个Column前加上别名。这样可以减少解析时间，减少Column歧义引起的语法错误。--如果有表table1(ID,col1)和table2(ID,col2)SelectA.ID,A.col1,B.col2--SelectA.ID,col1,col2--这个不要写，不是有利于以后程序扩展fromtable1Ainnerjointable2BonA.ID=B.IDWhere...B.从纵向看(1)合理写WHERE子句，不要写没有WHERE的SQL语句。(2)SELECTTOPN*--如果没有WHERE条件，就用这个代替4.尽量少重复工作A.控制同一条语句的多次执行，尤其是很多程序员很少执行的一些基础数据的多次执行注意的。B.减少数据转换次数。可能需要数据转换是设计的问题，但是程序员可以减少次数。C.消除不必要的子查询和连接表。子查询在执行计划中一般被解释为外连接，冗余的连接表会带来额外的开销。D、在同一张表上合并多个相同条件的UPDATE，比如UPDATEEMPLOYEESETFNAME='HAIWER'WHEREEMP_ID='VPA30890F'UPDATEEMPLOYEESETLNAME='YANG'WHEREEMP_ID='VPA30890F'这两条语句要合并进入下面的A语句UPDATEEMPLOYEESETFNAME='HAIWER',LNAME='YANG'WHEREEMP_ID='VPA30890F'E,UPDATE操作不要拆分成DELETE操作+INSERT操作的形式，虽然功能是一样的，但性能差异很大。5、注意临时表和表变量的使用在复杂的系统中，临时表和表变量是很难避免的。关于临时表和表变量的使用，需要注意：A.如果语句很复杂，连接太多，可以考虑使用临时表和表变量，分步完成。B、如果需要多次使用大表中的同一部分数据，可以考虑使用临时表和表变量来临时存储这部分数据。C、如果需要将多个表的数据整合起来形成一个结果，可以考虑使用临时表和表变量来逐步汇总这多个表的数据。D.在其他情况下，应控制临时表和表变量的使用。E、关于临时表和表变量的选择，有很多说法是表变量在内存中，速度快，所以应该优先选择表变量，但在实际使用中，（1）主要考虑需要的数据量放在临时表中。在量大的情况下，临时表的速度更快。(2)执行时间段和预计执行时间（多长时间）F.关于选择SELECTINTO和CREATETABLE+INSERTINTO生成临时表，一般情况下SELECTINTO会比CREATETABLE+INSERTINTO快很多，但是，SELECTINTO将锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES和SYSCOLUMNS。在多用户并发环境下，很容易阻塞其他进程。因此，我的建议是，在并发系统中，尽量使用CREATETABLE+INSERTINTO，而在大数据量的单条语句使用中，使用SELECTINTO。六、子查询的用法子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他子查询中。可以在任何允许表达式的地方使用子查询。子查询可以使我们的编程变得灵活多样，可以用来实现一些特殊的功能。但是在性能方面，往往不恰当的子查询使用会形成性能瓶颈。如果子查询的条件使用其外表的字段，则该子查询称为相关子查询。相关子查询可以用IN、NOTIN、EXISTS、NOTEXISTS引入。关于关联子查询，需要注意的是：(1)A、NOTIN、NOTEXISTS的关联子查询可以写成LEFTJOIN。例如：SELECTPUB_NAMEFROMPUBLISHERSWHEREPUB_IDNOTIN(SELECTPUB_IDFROMTITLESWHERETYPE='BUSINESS')--可以重写为：SELECTA.PUB_NAMEFROMPUBLISHERSALEFTJOINTITLESBONB.TYPE='BUSINESS'ANDA.PUB_ID=B.PUB_IDWHEREB.PUB_IDISNULL(2)SELECTTITLEFROMTITLESWHERENOTEXISTS(SELECTTITLE_IDFROMSALESWHERETITLE_ID=TITLES.TITLE_ID)可以重写为：SELECTTITLEFROMTITLESLEFTJOINSALESONSALES.TITLE_ID=TITLES.HEULITBLES.HEULTITLE_ID保证子查询不重复，IN和EXISTS的关联子查询可以替换为INNERJOIN。例如：SELECTPUB_NAMEFROMPUBLISHERSWHEREPUB_IDIN(SELECTPUB_IDFROMTITLESWHERETYPE='BUSINESS')可以重写为：SELECTA.PUB_NAME--SELECTDISTINCTA.PUB_NAMEFROMPUBLISHERSAINNERJOINTITLESBONB.TYPE='BUSINESSB'ID=B.PUB_ID(3)C,IN将相关子查询替换为EXISTS，如SELECTPUB_NAMEFROMPUBLISHERSWHEREPUB_IDIN(SELECTPUB_IDFROMTITLESWHERETYPE='BUSINESS')--可以用如下语句代替：SELECTPUB_NAMEFROMPUBLISHERSWHEREEXISTS(SELECT1FROMTITLESWHERETYPE='BUSINESS'ANDPUB_ID=PUBLISHERS.PUB_ID)D.不要使用COUNT(*)子查询来判断是否有记录。最好使用LEFTJOIN或EXISTS。比如有人写了这样一条语句：SELECTJOB_DESCFROMJOBSWHERE(SELECTCOUNT(*)FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)=0——应该改为：SELECTJOBS.JOB_DESCFROMJOBSLEFTJOINEMPLOYEEONEMPLOYEE.JOB_ID=JOBS.JOB_IDWHEREEMPLOYEE.EMP_IDISNULL_DELECTFJREOHE(SELECTCOUNT(*)FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)<>0——应该改为：SELECTJOB_DESCFROMJOBSWHEREEXISTS(SELECT1FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)7.尽量使用索引每次查询都会使用索引，在使用索引的情况下，索引的效率也会相差很大。只要我们不强制在查询语句中指定索引，索引的选择和使用是SQLServer优化器的自动选择，它的选择是根据查询语句的条件和相关表的统计信息，这就需要我们在写SQL语句的时候让优化器尽可能的使用索引。为了让优化器高效的使用索引，在写语句的时候要注意：(1)A.不要对索引字段进行操作，而是想办法做转换，比如SELECTIDFROMTWHERENUM/2=100应该改为：SELECTIDFROMTWHERENUM=100*2SELECTIDFROMTWHERENUM/2=NUM1如果NUM有索引，应该改为：SELECTIDFROMTWHERENUM=NUM1*2如果NUM1有索引，则不应更改。(2)发现这样一句话：SELECTyear,month,amountFROMbalancetableWHERE100*year+month=2010*100+10应该改成：SELECTyear,month,amountFROMbalancetableWHEREyear=2010ANDmonth=10B。不要转换索引字段的格式日期字段示例：WHERECONVERT(VARCHAR(10),datefield,120)='2010-07-15'应该改为WHEREdatefield>='2010-07-15'AND日期字段示例<'2010-07-16'ISNULL转换：WHEREISNULL(field,'')<>''应更改为：WHEREfield<>''WHEREISNULL(field,'')=''notShouldbemodifiedWHEREISNULL(field,'F')='T'shouldbechangedto:WHEREfield='T'WHEREISNULL(field,'F')<>'T'不应修改(3)C、不索引字段使用函数WHERELEFT(NAME,3)='ABC'或WHERESUBSTRING(NAME,1,3)='ABC'应改为：WHERENAMELIKE'ABC%'日期查询示例:WHEREDATEDIFF(DAY,date,'2010-06-30')=0应该改为：WHEREdate>='2010-06-30'ANDdate<'2010-07-01'WHEREDATEDIFF(DAY,date,'2010-06-30')>0应更改为：WHEREdate<'2010-06-30'WHEREDATEDIFF(DAY,date,'2010-06-30')>=0应更改为：WHEREdate<'2010-07-01'WHEREDATEDIFF(DAY,date,'2010-06-30')<0应改为：WHEREdate>='2010-07-01'WHEREDATEDIFF(DAY,date,'2010-06-30')<=0应该改为：WHEREdate>='2010-06-30'D、不执行索引字段上的多字段连接例如：WHEREFAME+'.'+LNAME='HAIWEI.YANG'应该改成：WHEREFNAME='HAIWEI'ANDLNAME='YANG'8、多表连接的连接条件对索引的选择意义重大，所以在写连接条件的时候需要特别注意。A.连接多张表时，连接条件一定要写完整，不要重复，不要省略。B、连接条件尽量使用聚簇索引。C、注意ON、WHERE和HAVING的区别。可以减少中间操作要处理的数据。按理说应该是最快的，WHERE应该比HAVING快，因为它在进行SUM之前先过滤数据，只有在两表join的时候才用ON，所以在只有一张表的时候，只有WHERE和剩下的。考虑join优先级：(1)INNERJOIN(2)LEFTJOIN(注意：RIGHTJOIN换成LEFTJOIN)(3)CROSSJOIN其他需要注意和理解的点是：A，在IN之后的值列表中，放出现次数最多的值放在最前面，出现次数最少的值放在最后，减少判断次数B.注意UNION和UNIONALL的区别。--允许重复数据使用UNIONALL更好C，注意使用DISTINCT，不需要的时候不要使用D，TRUNCATETABLE和DELETEE的区别，减少访问数据库的次数和我们写存储过程，如果比较长，最后用标记标记为open，因为可读性很强，即使语句写得不好，但是语句很工整，C#有regionsql，我比较喜欢用--startofsql语句查询数ofemployees--endof我们一般在正式机上使用，不能随便debug程序，但是很多时候程序在我们本地机上是没问题的，但是进入正式系统就出问题了，就是不能随便在官方机上操作，怎么办？我们可以使用回滚来调试我们的存储过程或者sql语句来排错。BEGINTRANUPDATEaSETfield=''ROLLBACK我一般会在job的存储过程中加入如下一段，这样检查错误就可以放在存储过程中了。如果执行了错误的回滚操作，但是如果程序中已经存在事务回滚，那么存储过程中不要写事务，这样会造成事务回滚嵌套，降低执行效率，但是我们往往可以把check放在存储过程，有助于我们解读存储过程和排查问题。BEGINTRANSACTION--事务回滚开始--检查错误IF(@@ERROR>0)BEGIN--回滚操作ROLLBACKTRANSACTIONRAISERROR('删除作业报告错误',16,3)RETURNEND--结束事务COMMITTRANSACTION
