人
们
在使用
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 where
substring(card_no,1,4)='5378'(13
秒
)
select * from record where
amount/30< 1000
（
11
秒）
select * from record where
convert(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
的性能
优
化是一个
复杂
的
过
程，上述
这
些只是在
应
用
层
次的一
种
体
现
，深入研究
还
会
涉
及数据
库层
的
资
源配置、网
络层
的流量控制以及操作系
统层
的
总
体
设计
。
转载于:https://www.cnblogs.com/caicainiao/archive/2011/08/13/2137062.html
