sqlserver索引:有关SQL SERVER建立索引需要注意的问题

="t18">---- 人们在使用SQL时往往会陷入个误区即太关注于所得结果是否正确而忽略了区别实现思路方法的间可能存在
性能差异这种性能差异在大型或是复杂数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明
显笔者在工作实战中发现不良SQL往往来自于不恰当索引设计、不充份连接条件和不可优化where子句在对
它们进行适当优化后其运行速度有了明显地提高！下面我将从这 3个方面分别进行整理总结:

---- 为了更直观地介绍说明问题所有例子中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.在placedateamount上组合索引

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第和第 2条SQL没有引用place因此也没有利用上索引；第 3个SQL使用了place且引用所有列都包含在组合索引中形成了索引覆盖所以它速度是非常快

---- 4.在dateplaceamount上组合索引
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都可以利用索引并且在第和第 3个SQL中形成了索引覆盖因而性能达到了最优

---- 5.整理总结:

---- 缺省情况下建立索引是非群集索引但有时它并不是最佳；合理索引设计要建立在对各种查询分析和预测
上般来说:

---- ①.有大量重复值、且经常有范围查询

(between, >,< >=,< =)和order by
、group by发生列可考虑建立群集索引；

---- ②.经常同时存取多列且每列都含有重复值可考虑建立组合索引；

---- ③.组合索引要尽量使关键查询形成索引覆盖其前导列定是使用最频繁列

2、不充份连接条件:
---- 例:表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秒)

---- 将SQL改为:
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页+(外层表account191122行*内层表card上对应外层表第行所要查找3页)=595907次I/O

---- 在第 2个连接条件下最佳查询方案是将card作外层表account作内层表利用account上索引其I/O次数可由以下公式估算为:

---- 外层表card上1944页+(外层表card7896行*内层表account上对应外层表每行所要查找4页)= 33528次I/O

---- 可见只有充份连接条件真正最佳方案才会被执行

---- 整理总结:

---- 1.多表操作在被实际执行前查询优化器会根据连接条件列出几组可能连接方案并从中找出系统开销最小最佳方案连接条件要充份考虑带有索引表、行数多表；内外表选择可由公式:外层表中匹配行数*内层表中每次查找次数确定乘积最小为最佳方案

---- 2.查看执行方案思路方法-- 用 showplanon打开showplan选项就可以看到连接顺序、使用何种索引信息；想
看更详细信息需用sa角色执行dbcc(3604,310,302)

3、不可优化where子句
---- 1.例:下列SQL条件语句中列都建有恰当索引但执行速度却非常慢:

select * from record where sub(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 as
declare @a
declare @b
declare @c
declare @d char(10)
begin
select @a=count(*) from stuff where id_no='0'
select @b=count(*) from stuff where id_no='1'
end
select @c=@a+@b
select @d=convert(char(10),@c)
pr @d

---- 直接算出结果执行时间同上面样快！
---- 整理总结:

---- 可见所谓优化即where子句利用了索引不可优化即发生了表扫描或额外开销

---- 1.任何对列操作都将导致表扫描它包括数据库、计算表达式等等查询时要尽可能将操作移至等号右边

---- 2.in、or子句常会使用工作表使索引失效；如果不产生大量重复值可以考虑把子句拆开；拆开子句中应该包含索引

---- 3.要善于使用存储过程它使SQL变得更加灵活和高效

---- 从以上这些例子可以看出SQL优化实质就是在结果正确前提下用优化器可以识别语句充份利用索引减少表扫描I/O次数尽量避免表搜索发生其实SQL性能优化是个复杂过程上述这些只是在应用层次种体现深入研究还会涉及数据库层资源配置、网络层流量控制以及操作系统层总体设计

延伸阅读

• 2010-12-18-- sql索引,软件开发人员真的了解SQL索引吗(聚集索引)
• 2010-12-9-- 索引整理,找出所有非xml索引并重新整理的sql
• 2010-12-14-- sql索引,利用SQL索引提高查询速度
• 2009-9-6-- sql索引:找出所有非xml索引并重新整理的sql
• 2009-9-6-- sqlserver索引:SQL Server 索引结构及其使用(一)--深入浅出理解索引结构
• 2009-9-12-- sqlserver:sqlserver取得网站WebSite路径的几种思路方法以及比较
• 2009-2-12-- sqlserver复制:SQL Server提高合并复制性能
• 2009-9-7-- sqlserver数据库:SQLServer 数据库备份过程中经常遇到的 9种情况
• 2008-12-17-- sqlserverdts设置:SQL SERVER定时作业的设置思路方法
• 2009-2-12-- sqlserver内存:SQL Server应用程序查询正在浪费内存吗