SQL SERVER中一些常见性能问题的总结
上一篇 / 下一篇 2007-06-06 14:34:51 / 个人分类:SQL
51Testing软件测试网}1Y!Ll&O'L d-C,ah
作者:pbsql(风云) 51Testing软件测试网(}9D y R:W&L
日期:2005-12-06 51Testing软件测试网#R f?"U l
0_X5Ic6j;Y0 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
h2[$x7_q;IXq0 51Testing软件测试网 La |&L6so%eH
2.应尽量避免使用 left join 和 null 值判断。left join 比 inner join 消耗更多的资源,因为它们包含与 null (不存在)数据匹配的数据,所以如果可以重新编写查询以使得该查询不使用任何 inner join ,则会得到相应的回报。 51Testing软件测试网zaiTs/`t
例如有两表: 51Testing软件测试网0A\0oH!ma`O([`-Re
product(product_id int not null,product_type_id int null,...),产品表, product_id 为大于0的整数, product_type_id 与表 product_type 关联,但可为空,因为有的产品没有类别 51Testing软件测试网8cH?O5O8CP
product_type(product_type_id not null,product_type_name null,...),产品类别表 51Testing软件测试网.Jf+ugw-xh
此时要关联两表后查询 product 的内容,马上会想到使用 inner join ,但下面有一种方法可避免使用 inner join : 51Testing软件测试网7bU7]prf6D
在 product_type 中增加一条记录:0,'',...,并将 product 的 product_type_id 设置为 not null ,当产品没有类别时将其 product_type_id 设为0,这样查询就可以使用 inner join 了。
Rp6Hi([5a9T0 51Testing软件测试网M _2\"t5n"{
3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎可能放弃使用索引而进行全表扫描。 51Testing软件测试网My#j#z5y6W7d.g*q
51Testing软件测试网D;l:h${m h
4.应尽量避免在 where 子句中使用 or 来连接条件,否则将可能导致引擎放弃使用索引而进行全表扫描,如有表 t , key1 、 key2 上建有索引,需要下面的存储过程:
%u2zjoR%NY0 create procedure select_proc1 @key1 int=0,@key2 int=0
W }BZD0 as
-^:oMIMu|Y8yu!r0 begin 51Testing软件测试网0@+pW.}0HW4T
select key3 from t 51Testing软件测试网&Ksk$Ju7WTC;?
where (@key1=0 or key1=@key1) 51Testing软件测试网&?Mo,B8d0|'k@
and (@key2=0 or key2=@key2) 51Testing软件测试网p m)C6n }7] i1{u
end 51Testing软件测试网:q iu'H5XH
go 51Testing软件测试网:W0@;k(Na/@,eE4tog G
这个存储过程会导致全表扫描,可作如下修改: 51Testing软件测试网u9R~4C \cz(_[
create procedure select_proc2 @key1 int=0,@key2 int=0 51Testing软件测试网-~2uA2} ];zwl
as 51Testing软件测试网M)O,I"n O~6FWB
begin 51Testing软件测试网;\a!}W t"T
if @key1 <>0 and @key2<>0
/} Z_xW0 select key3 from t
I1p _6nE%Y h0 where key1=@key1 and key2=@key2 51Testing软件测试网2Ep7V)S5P^ b'Y
else 51Testing软件测试网^L5I \ s5m"VW
if @key1<>0 51Testing软件测试网&Al,B ~H8\2~.Qi
select key3 from t where key1=@key1 51Testing软件测试网ap+h y @7t s H
else
2[m'][(DRT9q8Q;E0 select key3 from t where key2=@key2 51Testing软件测试网c&Z/w?~"yl"\+f
end 51Testing软件测试网Z;i-uc S.|NF
go 51Testing软件测试网2l2b`y6|u&{#v Jz*P?
更改后虽然代码增加了,但效率提高了。
1O/b.@8LdL#T/O(o0 51Testing软件测试网.R!Gl+g/g%Z3Tv
5.in 和 not in 也要慎用,如:
;L7a%Ph$l:y x0 select id from t where num in(1,2,3) 51Testing软件测试网n-B7BUxU5kwf
对于连续的数值,能用 between 就不要用 in 了: 51Testing软件测试网4CZ/UxXa.r
select id from t where num between 1 and 3 51Testing软件测试网 Y2FGDsx5R;H1j
51Testing软件测试网#o ^2m8Gs3x
6.下面的查询也将导致全表扫描:
'd)xfc#Xm"d:Nf0 select id from t where name like '%abc%' 51Testing软件测试网j2g e8v#c ^`t b
若要提高效率,可以考虑全文检索。 51Testing软件测试网/]0qwR^;N9e
51Testing软件测试网8k },@N\
7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: 51Testing软件测试网j#Rqf7NK:C)C
select id from t where num=@num 51Testing软件测试网9iE i)k }K
可以改为强制查询使用索引: 51Testing软件测试网hl5wfK,N9M
select id from t with(index(索引名)) where num=@num 51Testing软件测试网+J.gE [[}
uX0@@%O%w0 8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: 51Testing软件测试网+K![A1f:TL!G
select id from t where num/2=100
MN D8?WB0 应改为:
WB5tGD wQ | }0 select id from t where num=100*2
2nw&JH(NS0 51Testing软件测试网]J-w6B5R
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
[in!aj2?0 select id from t where substring(name,1,3)='abc'--name以abc开头的id 51Testing软件测试网!bdt NhAZ(z3Px
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id
T0E@"Y4t*T4\-A ^:m0 应改为:
Kz_ G @nI!C"N0 select id from t where name like 'abc%'
3Q7EJ9z5V'Dr}&I!BT9q0 select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' 51Testing软件测试网-Y0D"R;Bde.|R3J
!}/dE'l,gw,M ^0 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
g6r!V7CB~ R${6_0 51Testing软件测试网Xpx@G YH`E{
11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。 51Testing软件测试网 ld3L\%_M k
+`~"EL.?;j Jg$x0 12.不要写一些没有意义的查询,如需要生成一个空表结构: 51Testing软件测试网O:}` w5j{7k-P+t*@
select col1,col2 into #t from t where 1=0 51Testing软件测试网G0x7p(p*ZR
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: 51Testing软件测试网'{%Q!J/Ut(p w
create table #t(...) 51Testing软件测试网4K9C7ca:x']B-v
Pr;^\0l0s4P?0 13.很多时候用 exists 代替 in 是一个好的选择:
Gl5s8TopT0 select num from a where num in(select num from b) 51Testing软件测试网8A;C#I ?2g,VO^
用下面的语句替换:
作者:pbsql(风云) 51Testing软件测试网(}9D y R:W&L
日期:2005-12-06 51Testing软件测试网#R f?"U l
0_X5Ic6j;Y0 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
h2[$x7_q;IXq0 51Testing软件测试网 La |&L6so%eH
2.应尽量避免使用 left join 和 null 值判断。left join 比 inner join 消耗更多的资源,因为它们包含与 null (不存在)数据匹配的数据,所以如果可以重新编写查询以使得该查询不使用任何 inner join ,则会得到相应的回报。 51Testing软件测试网zaiTs/`t
例如有两表: 51Testing软件测试网0A\0oH!ma`O([`-Re
product(product_id int not null,product_type_id int null,...),产品表, product_id 为大于0的整数, product_type_id 与表 product_type 关联,但可为空,因为有的产品没有类别 51Testing软件测试网8cH?O5O8CP
product_type(product_type_id not null,product_type_name null,...),产品类别表 51Testing软件测试网.Jf+ugw-xh
此时要关联两表后查询 product 的内容,马上会想到使用 inner join ,但下面有一种方法可避免使用 inner join : 51Testing软件测试网7bU7]prf6D
在 product_type 中增加一条记录:0,'',...,并将 product 的 product_type_id 设置为 not null ,当产品没有类别时将其 product_type_id 设为0,这样查询就可以使用 inner join 了。
Rp6Hi([5a9T0 51Testing软件测试网M _2\"t5n"{
3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎可能放弃使用索引而进行全表扫描。 51Testing软件测试网My#j#z5y6W7d.g*q
51Testing软件测试网D;l:h${m h
4.应尽量避免在 where 子句中使用 or 来连接条件,否则将可能导致引擎放弃使用索引而进行全表扫描,如有表 t , key1 、 key2 上建有索引,需要下面的存储过程:
%u2zjoR%NY0 create procedure select_proc1 @key1 int=0,@key2 int=0
W }BZD0 as
-^:oMIMu|Y8yu!r0 begin 51Testing软件测试网0@+pW.}0HW4T
select key3 from t 51Testing软件测试网&Ksk$Ju7WTC;?
where (@key1=0 or key1=@key1) 51Testing软件测试网&?Mo,B8d0|'k@
and (@key2=0 or key2=@key2) 51Testing软件测试网p m)C6n }7] i1{u
end 51Testing软件测试网:q iu'H5XH
go 51Testing软件测试网:W0@;k(Na/@,eE4tog G
这个存储过程会导致全表扫描,可作如下修改: 51Testing软件测试网u9R~4C \cz(_[
create procedure select_proc2 @key1 int=0,@key2 int=0 51Testing软件测试网-~2uA2} ];zwl
as 51Testing软件测试网M)O,I"n O~6FWB
begin 51Testing软件测试网;\a!}W t"T
if @key1 <>0 and @key2<>0
/} Z_xW0 select key3 from t
I1p _6nE%Y h0 where key1=@key1 and key2=@key2 51Testing软件测试网2Ep7V)S5P^ b'Y
else 51Testing软件测试网^L5I \ s5m"VW
if @key1<>0 51Testing软件测试网&Al,B ~H8\2~.Qi
select key3 from t where key1=@key1 51Testing软件测试网ap+h y @7t s H
else
2[m'][(DRT9q8Q;E0 select key3 from t where key2=@key2 51Testing软件测试网c&Z/w?~"yl"\+f
end 51Testing软件测试网Z;i-uc S.|NF
go 51Testing软件测试网2l2b`y6|u&{#v Jz*P?
更改后虽然代码增加了,但效率提高了。
1O/b.@8LdL#T/O(o0 51Testing软件测试网.R!Gl+g/g%Z3Tv
5.in 和 not in 也要慎用,如:
;L7a%Ph$l:y x0 select id from t where num in(1,2,3) 51Testing软件测试网n-B7BUxU5kwf
对于连续的数值,能用 between 就不要用 in 了: 51Testing软件测试网4CZ/UxXa.r
select id from t where num between 1 and 3 51Testing软件测试网 Y2FGDsx5R;H1j
51Testing软件测试网#o ^2m8Gs3x
6.下面的查询也将导致全表扫描:
'd)xfc#Xm"d:Nf0 select id from t where name like '%abc%' 51Testing软件测试网j2g e8v#c ^`t b
若要提高效率,可以考虑全文检索。 51Testing软件测试网/]0qwR^;N9e
51Testing软件测试网8k },@N\
7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: 51Testing软件测试网j#Rqf7NK:C)C
select id from t where num=@num 51Testing软件测试网9iE i)k }K
可以改为强制查询使用索引: 51Testing软件测试网hl5wfK,N9M
select id from t with(index(索引名)) where num=@num 51Testing软件测试网+J.gE [[}
uX0@@%O%w0 8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: 51Testing软件测试网+K![A1f:TL!G
select id from t where num/2=100
MN D8?WB0 应改为:
WB5tGD wQ | }0 select id from t where num=100*2
2nw&JH(NS0 51Testing软件测试网]J-w6B5R
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
[in!aj2?0 select id from t where substring(name,1,3)='abc'--name以abc开头的id 51Testing软件测试网!bdt NhAZ(z3Px
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id
T0E@"Y4t*T4\-A ^:m0 应改为:
Kz_ G @nI!C"N0 select id from t where name like 'abc%'
3Q7EJ9z5V'Dr}&I!BT9q0 select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' 51Testing软件测试网-Y0D"R;Bde.|R3J
!}/dE'l,gw,M ^0 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
g6r!V7CB~ R${6_0 51Testing软件测试网Xpx@G YH`E{
11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。 51Testing软件测试网 ld3L\%_M k
+`~"EL.?;j Jg$x0 12.不要写一些没有意义的查询,如需要生成一个空表结构: 51Testing软件测试网O:}` w5j{7k-P+t*@
select col1,col2 into #t from t where 1=0 51Testing软件测试网G0x7p(p*ZR
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: 51Testing软件测试网'{%Q!J/Ut(p w
create table #t(...) 51Testing软件测试网4K9C7ca:x']B-v
Pr;^\0l0s4P?0 13.很多时候用 exists 代替 in 是一个好的选择:
Gl5s8TopT0 select num from a where num in(select num from b) 51Testing软件测试网8A;C#I ?2g,VO^
用下面的语句替换: