人
们
在使用
sql
时
往往会陷入一个
误
区，即太
关
注于所得的
结
果是否正确，而忽略了不同的
实现
方法之
间
可能存在的性能差异，
这种
性能差异在大型的或是
复杂
的数据
库环
境中（如
联
机事
务处
理
oltp
或决策支持系
统
dss
）中表
现
得尤
为
明
显
。
笔者在工作
实
践中
发现
，不良的
sql
往往来自于不恰当的索引
设计
、不充份的
连
接条件和不可
优
化的
where
子句。
在
对
它
们进
行适当的
优
化后，其运行速度有了明
显
地提高！
下面将从这三个方面分别进行总结：
为
了更直
观
地
说
明
问题
，所有
实
例中的
sql
运行
时间
均
经过测试
，不超
过
１秒的均表示
为
（
< 1
秒）。
—-
测试环
境
:
主机：
hp lh ii—-
主
频
：
330mhz—-
内存：
128
兆
—-
操作系
统
：
operserver5.0.4—-
数据
库
：
sybase11.0.3
一、不合理的索引设计—-
例：表
record
有
620000
行，
试
看在不同的索引下，下面几个
sql
的运行情况：
—- 1.
在
date
上建有一非个群集索引
select count(*) from record where date >’19991201′ and date < ‘19991214’and amount >2000 (25秒)
select date ,sum(amount) from record group by date(55秒)
select count(*) from record where date >’19990901′ and place in (‘bj’,’sh’) (27秒)
—-
分析：
—-
date
上有大量的重
复值
，在非群集索引下，数据在物理上随机存放在数据
页
上，在范
围查
找
时
，必
须执
行一次表
扫
描才能找到
这
一范
围
内的全部行。
—- 2.
在
date
上的一个群集索引
select count(*) from record where date >’19991201′ and date < ‘19991214’ and amount >2000 （14秒）
select date,sum(amount) from record group by date（28秒）
select count(*) from record where date >’19990901′ and place in (‘bj’,’sh’)（14秒）
—-
分析：
—-
在群集索引下，数据在物理上按
顺
序在数据
页
上，重
复值
也排列在一起，因而在范
围查
找
时
，可以先找到
这
个范
围
的起末点，且只在
这
个范
围
内
扫
描数据
页
，避免了大范
围扫
描，提高了
查询
速度。
—- 3.
在
place
，
date
，
amount
上的
组
合索引
select count(*) from record where date >’19991201′ and date < ‘19991214’ and amount >2000 （26秒）
select date,sum(amount) from record group by date（27秒）
select count(*) from record where date >’19990901′ and place in (‘bj, ‘sh’)（< 1秒）
—-
分析：
—-
这
是一个不很合理的
组
合索引，因
为
它的前
导
列是
place
，第一和第二条
sql
没有引用
place
，因此也没有利用上索引；第三个
sql
使用了
place
，且引用的所有列都包含在
组
合索引中，形成了索引覆盖，所以它的速度是非常快的。
—- 4.
在
date
，
place
，
amount
上的
组
合索引
select count(*) from record where date >’19991201′ and date < ‘19991214’ and amount >2000(< 1秒)
select date,sum(amount) from record group by date（11秒）
select count(*) from record where date >’19990901′ and place in (‘bj’,’sh’)（< 1秒）
—-
分析：
—-
这
是一个合理的
组
合索引。它将
date
作
为
前
导
列，使
每
个
sql
都可以利用索引，并且在第一和第三个
sql
中形成了索引覆盖，因而性能达到了最
优
。
—- 5.总结：—-
缺省情况下建立的索引是非群集索引，但有
时
它并不是最佳的；合理的索引
设计
要建立在
对
各
种查询
的分析和
预测
上。
一般来
说
：
①
.
有大量重
复值
、且
经
常有范
围查询
（
between, >,<
，
>=,< =
）和
order by
、
group by
发
生的列，可考
虑
建立群集索引；
②
.
经
常同
时
存取多列，且
每
列都含有重
复值
可考
虑
建立
组
合索引；
③
.
组
合索引要尽量使
关键查询
形成索引覆盖，其前
导
列一定是使用最
频
繁的列。
二、不充份的连接条件：
例：表
card
有
7896
行，在
card_no
上有一个非聚集索引，表
account
有
191122
行，在
account_no
上有一个非聚集索引，
试
看在不同的表
连
接条件下，两个
sql
的
执
行情况：
select sum(a.amount) from account a,card b where a.card_no = b.card_no（20秒）
select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒）
—-
分析：
—-
在第一个
连
接条件下，最佳
查询
方案是将
account
作外
层
表，
card
作内
层
表，利用
card
上的索引，其
i/o
次数可由以下公式估算
为
：
外
层
表
account
上的
22541
页
+
（外
层
表
account
的
191122
行
*
内
层
表
card
上
对应
外
层
表第一行所要
查
找的
3
页
）
=595907
次
i/o
在第二个
连
接条件下，最佳
查询
方案是将
card
作外
层
表，
account
作内
层
表，利用
account
上的索引，其
i/o
次数可由以下公式估算
为
：外
层
表
card
上的
1944
页
+
（外
层
表
card
的
7896
行
*
内
层
表
account
上
对应
外
层
表
每
一行所要
查
找的
4
页
）
= 33528
次
i/o
可
见
，只有充份的
连
接条件，真正的最佳方案才会被
执
行。
总结：
1.
多表操作在被
实际执
行前，
查询优
化器会根据
连
接条件，列出几
组
可能的
连
接方案并从中找出系
统开销
最小的最佳方案。
连
接条件要充份考
虑带
有索引的表、行数多的表；内外表的
选择
可由公式：外
层
表中的匹配行数
*
内
层
表中
每
一次
查
找的次数确定，乘
积
最小
为
最佳方案。
2.
查
看
执
行方案的方法
—
用
set showplan
on
，打
开
showplan
选项
，就可以看到
连
接
顺
序、使用何
种
索引的信息；想看更
详细
的信息，需用
sa
角色
执
行
dbcc(3604,310,302)
。
三、不可优化的where子句
1.
例：下列
sql
条件
语
句中的列都建有恰当的索引，但
执
行速度却非常慢：
select * from record wheresubstring(card_no,1,4)=’5378′(13秒)
select * from record whereamount/30< 1000（11秒）
select * from record whereconvert(char(10),date,112)=’19991201’（10秒）
分析：
where
子句中
对
列的任何操作
结
果都是在
sql
运行
时
逐列
计
算得到的，因此它不得不
进
行表搜索，而没有使用
该
列上面的索引；
如果
这
些
结
果在
查询编译时
就能得到，那
么
就可以被
sql
优
化器
优
化，使用索引，避免表搜索，因此将
sql
重写成下面
这样
：
select * from record where card_no like’5378%’（< 1秒）
select * from record where amount< 1000*30（< 1秒）
select * from record where date= ‘1999/12/01’（< 1秒）
你会
发现
sql
明
显
快起来！
2.
例：表
stuff
有
200000
行，
id_no
上有非群集索引，
请
看下面
这
个
sql
：
select count(*) from stuff where id_no in(‘0′,’1’)（23秒）
分析：
—- where
条件中的
‘in’
在
逻辑
上相当于
‘or’
，所以
语
法分析器会将
in (‘0′,’1’)
转
化
为
id_no =’0′ or id_no=’1′
来
执
行。
我
们
期望它会根据
每
个
or
子句分
别查
找，再将
结
果相加，
这样
可以利用
id_no
上的索引；
但
实际
上（根据
showplan
）
,
它却采用了
“or
策略
“
，即先取出
满
足
每
个
or
子句的行，存入
临时
数据
库
的工作表中，再建立唯一索引以去掉重
复
行，最后从
这
个
临时
表中
计
算
结
果。因此，
实际过
程没有利用
id_no
上索引，并且完成
时间还
要受
tempdb
数据
库
性能的影响。
实
践
证
明，表的行数越多，工作表的性能就越差，当
stuff
有
620000
行
时
，
执
行
时间
竟达到
220
秒！
还
不如将
or
子句分
开
：
select count(*) from stuff where id_no=’0’select count(*) from stuff where id_no=’1′
得到两个
结
果，再作一次加法合算。因
为每
句都使用了索引，
执
行
时间
只有
3
秒，在
620000
行下，
时间
也只有
4
秒。
或者，用更好的方法，写一个
简单
的存
储过
程：
create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no=’0’select @b=count(*) from stuff where id_no=’1’endselect @c=@a+@bselect @d=convert(char(10),@c)print @d
直接算出
结
果，
执
行
时间
同上面一
样
快！
—- 总结：
—-
可
见
，所
谓优
化即
where
子句利用了索引，不可
优
化即
发
生了表
扫
描或
额
外
开销
。
1.
任何
对
列的操作都将
导
致表
扫
描，它包括数据
库
函数、
计
算表达式等等，
查询时
要尽可能将操作移至等号右
边
。
2.in
、
or
子句常会使用工作表，使索引失效；如果不
产
生大量重
复值
，可以考
虑
把子句拆
开
；拆
开
的子句中
应该
包含索引。
3.
要善于使用存
储过
程，它使
sql
变
得更加灵活和高效。
从以上
这
些例子可以看出，
sql
优
化的
实质
就是在
结
果正确的前提下，用
优
化器可以
识别
的
语
句，充份利用索引，减少表
扫
描的
i/o
次数，尽量避免表搜索的
发
生。其
实
sql
的性能
优
化是一个
复杂
的
过
程，上述
这
些只是在
应
用
层
次的一
种
体
现
，深入研究
还
会
涉
及数据
库层
的
资
源配置、网
络层
的流量控制以及操作系
统层
的
总
体
设计
。
1、开发人员如果用到其他库的table或view，务必在当前库中建立view来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该sp所使用的跨库table或view，不方便校验。
2、开发人员在提交sp前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。
3、高程序运行效率，优化应用程序，在sp编写过程中应该注意以下几点：
a) sql的使用规范：
i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
iv. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。
v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
vii. 尽量使用“>=”，不要使用“>”。
viii. 注意一些or子句和union子句之间的替换
ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。
x. 注意存储过程中参数和数据类型的关系。
xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。
b) 索引的使用规范：
i. 索引的创建要与应用结合考虑，建议大的oltp表不要超过6个索引。
ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引
iii. 避免对大表查询时进行table scan，必要时考虑新建索引。
iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。
v. 要注意索引的维护，周期性重建索引，重新编译存储过程。
c) tempdb的使用规范：
i. 尽量避免使用distinct、order by、group by、having、join、***pute，因为这些语句会加重tempdb的负担。
ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。
iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
d) 合理的算法使用：
根据上面已提到的sql优化技术和ase tuning手册中的sql优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ase调优命令：set statistics io on, set statistics time on , set showplan on 等