搜索
1.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectidfromtwherenum=02.应尽量避免在where子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。3.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=204.in和notin也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and35.尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。见如下例子：SELECT*FROMT1WHERENAMELIKE‘%L%’SELECT*FROMT1WHERESUBSTING(NAME,2,1)=’L’SELECT*FROMT1WHERENAMELIKE‘L%’即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。6.必要时强制查询优化器使用某个索引，如在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：selectidfromtwherenum=@num可以改为强制查询使用索引：selectidfromtwith(index(索引名))wherenum=@num7.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：SELECT*FROMT1WHEREF1/2=100应改为:SELECT*FROMT1WHEREF1=100*2SELECT*FROMRECORDWHERESUBSTRING(CARD_NO,1,4)=’5378’应改为:SELECT*FROMRECORDWHERECARD_NOLIKE‘5378%’SELECTmember_number,first_name,last_nameFROMmembersWHEREDATEDIFF(yy,datofbirth,GETDATE())21应改为:SELECTmember_number,first_name,last_nameFROMmembersWHEREdateofbirthDATEADD(yy,-21,GETDATE())即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：selectidfromtwheresubstring(name,1,3)='abc'--name以abc开头的idselectidfromtwheredatediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id应改为:selectidfromtwherenamelike'abc%'selectidfromtwherecreatedate='2005-11-30'andcreatedate'2005-12-1'9.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。11.很多时候用exists是一个好的选择：selectnumfromawherenumin(selectnumfromb)用下面的语句替换：selectnumfromawhereexists(select1frombwherenum=a.num)SELECTSUM(T1.C1)FROMT1WHERE((SELECTCOUNT(*)FROMT2WHERET2.C2=T1.C20)SELECTSUM(T1.C1)FROMT1WHEREEXISTS(SELECT*FROMT2WHERET2.C2=T1.C2)两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如：IF(SELECTCOUNT(*)FROMtable_nameWHEREcolumn_name='xxx')可以写成：IFEXISTS(SELECT*FROMtable_nameWHEREcolumn_name='xxx')经常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如：SELECTa.hdr_keyFROMhdr_tbla----tbla表示tbl用别名a代替WHERENOTEXISTS(SELECT*FROMdtl_tblbWHEREa.hdr_key=b.hdr_key)SELECTa.hdr_keyFROMhdr_tblaLEFTJOINdtl_tblbONa.hdr_key=b.hdr_keyWHEREb.hdr_keyISNULLSELECThdr_keyFROMhdr_tblWHEREhdr_keyNOTIN(SELECThdr_keyFROMdtl_tbl)三种写法都可以得到同样正确的结果，但是效率依次降低。12.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。13.避免频繁创建和删除临时表，以减少系统表资源的消耗。14.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。15.在新建临时表时，如果一次性插入数据量很大，那么可以使用selectinto代替createtable，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先createtable，然后insert。16.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncatetable，然后droptable，这样可以避免系统表的较长时间锁定。17.在所有的存储过程和触发器的开始处设置SETNOCOUNTON，在结束时设置SETNOCOUNTOFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。18.尽量避免大事务操作，提高系统并发能力。19.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。20.避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:SELECTnameFROMemployeeWHEREsalary60000在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。21.充分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在WHERE子句中将连接条件完整的写上，有可能大大提高查询速度。例：SELECTSUM(A.AMOUNT)FROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NOSELECTSUM(A.AMOUNT)FROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NOANDA.ACCOUNT_NO=B.ACCOUNT_NO第二句将比第一句执行快得多。22、使用视图加速查询把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：SELECTcust.name，rcvbles.balance，……othercolumnsFROMcust，rcvblesWHEREcust.customer_id=rcvlbes.customer_idANDrcvblls.balance0ANDcust.postcode“98000”ORDERBYcust.name如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个视图中，并按客户的名字进行排序：CREATEVIEWDBO.V_CUST_RCVLBESASSELECTcust.name，rcvbles.balance，……othercolumnsFROMcust，rcvblesWHEREcust.customer_id=rcvlbes.customer_idANDrcvblls.balance0ORDERBYcust.name然后以下面的方式在视图中查询：SELECT＊FROMV_CUST_RCVLBESWHEREpostcode“98000”视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。23、能用DISTINCT的就不用GROUPBYSELECTOrderIDFROMDetailsWHEREUnitPrice10GROUPBYOrderID可改为：SELECTDISTINCTOrderIDFROMDetailsWHEREUnitPrice1024.能用UNIONALL就不要用UNIONUNIONALL不执行SELECTDISTINCT函数，这样就会减少很多不必要的资源25.尽量不要用SELECTINTO语句。SELECTINOT语句会导致表锁定，阻止其他用户访问该表。