即:
任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。
如:
selectidfromtwheresubstring（name,1,3='abc'--name以abc开头的idselectidfromtwhere
datediff（day,createdate,'2005-11-30'=0--‘2005-11-30’生成的id
应改为:
selectidfromtwherenamelike'abc%'
selectidfromtwherecreatedate>='2005-11-30'and
createdate<'2005-12-1'
9.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
11.很多时候用exists是一个好的选择:
electnumfromawherenumin（selectnumfromb
用下面的语句替换:
selectnumfromawhereexists（select1frombwherenum=a.numSELECTSUM（T1.C1FROMT1WHERE（
（SELECTCOUNT（*FROMT2WHERET2.C2=T1.C2>0
SELECTSUM（T1.C1FROMT1WHEREEXISTS（
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_keySELECTa.hdr_keyFROMhdr_tbla
LEFTJOINdtl_tblbONa.hdr_key=b.hdr_keyWHEREb.hdr_keyISNULL
SELECThdr_keyFROMhdr_tbl
WHEREhdr_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是不兼容的。
数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。
例如:
SELECTnameFROMemployeeWHEREsalary>60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。
我们应当在编程时将整型转化成为钱币型,而不要等到运行
时转化。
21.充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在WHERE子句中将连接条件完整的写上,有可能大大提高查询速度。
例:
SELECTSUM（A.AMOUNTFROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NOSELECTSUM（A.AMOUNTFROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NOANDA.ACCOUNT_NO=B.ACCOUNT_NO
第二句将比第一句执行快得多。
22、使用视图加速查询
把表的一个子集进行排序并创建视图,有时能加速查询。
它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。
例如:
SELECTcust.name,rcvbles.balance,……othercolumns
FROMcust,rcvbles
WHEREcust.customer_id=rcvlbes.customer_id
ANDrcvblls.balance>0
ANDcust.postcode>“98000”
ORDERBYcust.name
如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个视图中,并按客户的名字进行排序:
CREATEVIEWDBO.V_CUST_RCVLBES
AS
SELECTcust.name,rcvbles.balance,……othercolumns
FROMcust,rcvbles
WHEREcust.customer_id=rcvlbes.customer_id
ANDrcvblls.balance>0
ORDERBYcust.name
然后以下面的方式在视图中查询:
SELECT*FROMV_CUST_RCVLBES
WHEREpostcode>“98000”
视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。
23、能用DISTINCT的就不用GROUPBY
SELECTOrderIDFROMDetailsWHEREUnitPrice>10GROUPBYOrderID可改为:
SELECTDISTINCTOrderIDFROMDetailsWHEREUnitPrice>10
24.能用UNIONALL就不要用UNION
UNIONALL不执行SELECTDISTINCT函数,这样就会减少很多不必要的资源
35.尽量不要用SELECTINTO语句。
SELECTINOT语句会导致表锁定,阻止其他用户访问该表。
上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。
最好的方法当然是测试,看实现相同功能的SQL语句哪个执行时间最少,但是数据库中如果数据量很少,是比较不出来的,这时可以用查看执行计划,即:
把实现相同功能的多条SQL语句考到查询分析器,按CTRL+L看查所利用的索引,表扫描次数（这两个对性能影响最大,总体上看询成本百分比即可。
今天在itput上看了一篇文章,是讨论一个语句的优化:
原贴地址:
一,发现问题优化的语句:
请问以下语句如何优化:
CREATETABLEaa_001
（ipVARCHAR2（28,
nameVARCHAR2（10,
passwordVARCHAR2（30
select*fromaa_001whereipin（1,2,3orderbynamedesc;
--目前表中记录有一千多万条左右,而且in中的值个数是不确定的。
以上就是优化的需要优化的语句和情况。
不少人在后面跟帖:
有的说没办法优化,有的说将IN该为EXISTS,有的说在ip上建立索引复合索引（ip,name等等。
二,提出问题那这样的情况,能优化吗,如何优化?
今天就来讨论这个问题。
三,分析问题1,数据量1千万多条。
2,in中的值个数是不确定
3.1分析数据分布这里作者没有提到ip列的数据的分布情况,目前ip列的数据分布可能有以下几种:
1,ip列（数据唯一,或者数据重复的概率很小
2,ip列（数据不均匀,可能有些数据重复多,有些重复少
3,ip列（数据分布比较均匀,数据大量重复,主要就是一些同样的数据（可能只有上万级别不同的ip数据等
解决问题:
1,对于第一种数据分布情况,只要在ip列建立一个索引即可。
这时不管表有多少行,in个数是不确定的情况下,都很快。
2,对应第二中数据分布情况,在ip列建立索引,效果不好。
因为数据分布不均匀,可能有些快,有些慢
3,对应第三种数据分布情况,在ip列建立索引,速度肯定慢。
注意:
这里的orderbynamedesc是在取出数据后再排序的。
而不是取数据前排序
对于2,3两个情况,因为都是可能需要取出大量的数据,优化器就采用表扫描（tablescan,而不是索引查找（indexseek,速度很慢,因为这时表扫描效率要优于索引查找,特别是高并发情况下,效率很低。
那对应2,3中情况,如何处理。
是将in改成exists。
其实在sqlserver2005和oracle里的优化器在in后面数据少时,效率是一样的。
这时采用一般的索引效率很低。
这时如果在ip列上建立聚集索引,效率会比较高。
我们在SQLserver2005中做个测试。
表:
[dbo].[[]]]中有约200万条数据。
包含列Userid,id,Ruleid等列。
按照上面的情况查询一下类似语句:
select*from[dbo].[[]]]where
useridin
（'402881410ca47925010cb329c7670ffb','402881ba0d5dc94e010d5dced05a0008'
'4028814111a735e90111a77fa8e30384'orderbyRuleiddesc
我们先看userid的数据分布情况,执行下面语句:
selectuserid,count（*from[dbo].[[]]]groupbyuseridorderby2
这时我们看看数据分布:
总共有379条数据,数据两从1到15万都有,数据分
布倾斜严重。
下图是其中一部分。
这时如果在ip上建立非聚集索引，效率很低，而且就是强行索引扫描强行索引扫描，效率也强行索引扫描很低，会发现IO次数比表扫描还高。
这时只能在ip上建立聚集索引。
这时看看结果。
这时发现，搜索采用了（clusteredindexseek）聚集搜索扫描。
在看看查询返回的结果：
（156603行受影响表'[]'。
扫描计数8，逻辑读取5877次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。
表'Worktable'。
扫描计数0，逻辑读取0次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。
返回15万行，才不到6千次IO。
效率较高，因为这15万行要排序，查询成本里排序占了51%。
当然可以建立（userid，Ruleid）复合聚集索引，提高性能，但这样做DML维护成本较高。
建议不采用。
从上面的测试例子可以看出，优化的解决办法：
数据分布为1：
建立ip索引即可数据分布为2，3：
在ip列建立聚集索引。
升级会员 