今天主要分享sqlserver数据库在做SQL优化时的25个注意事项，仅供参考。SQL优化项1、尽量避免在where子句中判断字段的空值，否则引擎会放弃使用索引而进行全表扫描，如：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_nameHROM(yy,datofbirth,GETDATE())>21应该改为：SELECTmember_number,first_name,last_nameFROMmembersWHEREdateofbirth名称以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、使用索引字段作为条件时，如果索引是复合索引，那么必须使用索引中的第一个字段作为条件，保证系统使用索引，否则索引不会被使用，应该be使字段顺序尽可能与索引顺序一致。11.在很多情况下，使用exists是一个不错的选择。例如：SELECTNUMFROMAWHERENUMIN(SELECTNUMFROMB)替换为以下语句：SELECTNUMFROMAWHEREEXISTS(SELECT1FROMBWHERENUM=A.NUM)SELECTSUM(T1.C1)FROMT1WHERE(SELECTCOUNT(*)FROMT2WHERET2.C2=T1.C2>0)替换为以下语句:SELECTSUM(T1.C1)FROMT1WHEREEXISTS(SELECT*FROMT2WHEREEXISTS(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，避免创建大量日志，提高速度；如果数据量不大，为了缓解系统表的资源，应该先建表，再插入。注意：SELECTINTO语句会导致表被锁定，防止其他用户访问该表。16、如果使用临时表，必须在存储过程结束时显式删除所有临时表，先truncatetable，再droptable，以免系统表Longerlocking。17.在所有存储过程和触发器的开头设置SETNOCOUNTON，在结束时设置SETNOCOUNTOFF。存储过程和触发器的每条语句执行完后，不需要向客户端发送DONE_IN_PROC消息。18、尽量避免大事务操作，提高系统并发度。19、尽量避免向客户端返回大量数据。如果数据量太大，就要考虑对应的需求是否合理。20.避免使用不兼容的数据类型。例如，float和int、char和varchar、binary和varbinary是不兼容的。数据类型不兼容可能会阻止优化器执行某些原本可以执行的优化操作。例如：SELECTnameFROMemployeeWHEREsalary>60000这条语句中，如果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.balance>0ANDcust.postcode>98000ORDERBYcust.name如果这个查询要执行多次而不是一次，你可以把所有unpaidcustomers在视图中找到它并按客户名称排序：CREATEVIEWDBO.V_CUST_RCVLBESASSELECTcust.name,rcvbles.balance,...othercolumnsFROMcust,rcvblesWHEREcustomer_id=rcvlbes.customer_idANDrcvblls.balance>0ORDERBYcust.name然后在视图中如下way中查询：SELECT*FROMV_CUST_RCVLBESWHEREpostcode>98000视图中的行比主表中的少，物理顺序是要求的顺序，减少了磁盘I/O，因此可以大大减少查询工作量。23、如果可以使用DISTINCT，就不需要GROUPBYSELECTOrderIDFROMDetailsWHEREUnitPrice>10GROUPBYOrderID可以改为：SELECTDISTINCTOrderIDFROMDetailsWHEREUnitPrice>1024。如果可以使用UNIONALL，请不要使用UNIONUNIONALL。不要执行SELECTDISTINCT函数，这样会减少很多不必要的资源。25、尽量不要使用SELECTINTO语句和SELECTINOT语句导致表被锁定，阻止其他用户访问该表。上面我们说的是一些提高查询速度的基本注意事项，但在更多的情况下，往往需要反复试验，比较不同的语句，才能得到最好的解决方案。最好的办法当然是测试一下，看哪条实现相同功能的SQL语句执行时间最少，但是如果数据库的数据量小，就没法比较了。在查询分析器中测试多条SQL语句，按CTRL+L查看使用的索引、表扫描次数（这两个对性能影响最大）、整体查询成本百分比。
