sql运行效率,sql语句怎么运行
很多人在使用SQL的时候经常会陷入一个误区,就是过于注重结果的正确性,而忽略了不同实现方式之间可能存在的性能差异,尤其是在大型或者复杂的数据库环境下(比如在线事务处理OLTP或者DSS)。
我在工作实践中发现,糟糕的SQL往往来自于索引设计不恰当、连接条件不充分、where子句未优化。
经过适当优化后,它们的运行速度明显提高!
我从这三个方面总结一下:
为了更直观的说明问题,测试了SQL在所有实例中的运行时间,不超过1秒的表示为(1秒)。-
测试环境:主机:HP LH II-主频:330MHZ -内存:128 MB-
操作系统:Operserver5.0.4 -
数据库:Sybase11.0.3
1.不合理的索引设计
示例:表记录有620,000行。让我们看看下面的SQL如何在不同的索引下运行:
1.日期上有一个非聚集索引。
从记录日期中选择计数(*)
“1999年12月1日”和日期“1999年12月14日”以及金额2000 (25秒)
Select,sum(金额)from record group by date (55秒)
从记录日期中选择计数(*)
1999 09 01 并放入( bj , sh) (27秒)
分析:日期上有大量重复值。在非聚集索引下,数据随机存储在数据页上。搜索该范围时,必须执行表扫描来查找该范围内的所有行。
2.日期的簇索引
从记录日期中选择计数(*)
“1999年12月1日”和日期“1999年12月14日”以及金额2000 (14秒)
Select,sum(金额)from record group by date (28秒)
从记录日期中选择计数(*)
1999 09 01 并放入( bj , sh) (14秒)
分析:在簇索引下,数据按物理顺序在数据页上,重复值也排列在一起。所以在搜索范围时,可以先找到这个范围的起点和终点,只扫描这个范围内的数据页,避免大规模扫描,提高查询速度。
3.地点、日期和金额的综合指数
从记录日期中选择计数(*)
“1999年12月1日”和日期“1999年12月14日”以及金额2000 (26秒)
Select,sum(金额)from record group by date (27秒)
从记录日期中选择计数(*)
1999 09 01 并放置在( bj, sh) (1秒钟)
分析:这是一个不合理的复合索引,因为它的前导列是place,第一条和第二条SQL都没有引用place,所以没有利用upper索引。第三条SQL使用的是place,引用的列全部包含在复合索引中,形成索引覆盖,所以它的速度非常快。
4.日期、地点和金额的综合指数
从记录日期中选择计数(*)
“1999年12月1日”和“1999年12月14日”以及2000年的金额(1秒)
Select,sum(金额)from record group by date (11秒)
从记录日期中选择计数(*)
1999 09 01 并放置在( bj , sh) (1秒钟)
分析:这是一个合理的组合指数。它以date为前导列,这样每一条SQL都可以利用索引,索引覆盖在第一条和第三条SQL中形成,所以性能最好。
5.总结:
默认情况下建立的索引是非聚集索引,但有时并不是最佳索引。合理的索引设计应该基于对各种查询的分析和预测。
一般来说:
对于有大量重复值和频繁范围查询(between、=、=)以及order by和group by的列,考虑建立聚簇索引;
同时频繁访问多列,每列包含重复值。可以考虑建立组合指数;
组合索引应尽量使关键查询形成索引覆盖,其前导列必须是使用频率最高的列。
二。不适当的连接条件:
示例:表card有7896行,card_no上有一个非聚集索引,表account有191122行,account _no上有一个非聚集索引。尝试查看两个SQL在不同表联接条件下的执行情况:
从账户a中选择sum(a.amount ),
卡片,其中a .卡片编号=b .卡片编号(20秒)
从账户a中选择sum(a.amount ),
卡片b,其中a.card_no=b.card_no且
a .账号=b .账号(1秒)
分析:第一种连接条件下,最佳查询方案是用account做外表,card做内表。使用卡上的索引,可以通过以下公式估计其I/O时间:
外表账户中22541页(外表账户中191122行*外表第一行对应的内表卡中要查找的3页)=595907 I/O。
在第二种连接条件下,最好的查询方案是用card做外表,account做内表。使用索引on account,I/O次数可以用下面的公式估算:外表卡1944页(外表卡7896行*内表account每行要搜索4页)=3528次I/O。
可见,只有具备充分的连接条件,才会实施真正的最佳方案。
总结:
1.在多表操作实际执行之前,查询优化器会根据连接条件列出几组可能的连接方案,找出系统开销最低的最佳方案。连接条件要充分考虑有索引的表和多行的表;内外外观的选择可以由公式确定:外表匹配行数*内表每次搜索数,最小乘积为最优解。
2.检查实现scheme的方法——使用set showplanon,打开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秒)
分析:
在SQL运行时,对where子句中某一列的任何操作的结果都是逐列计算的,因此它必须在不使用列上方的索引的情况下搜索表;
如果可以在编译查询时获得这些结果,则可以通过SQL优化器优化它们,使用索引来避免表搜索,因此SQL重写如下:
select * from record where card _ no like 5378% (1秒)
Select * from记录,其中金额1000 * 30 (1秒)
select * from record where date= 1999/12/01 (1秒)
你会发现SQL明显更快!
2.示例:表stuff有200,000行,id _no上有一个非聚集索引。
select(*)from stuff where id _ no in( 0 , 1) (23秒)
分析:where条件中的:-in 逻辑上等价于 or ,所以解析器会将in (0 , 1 )转换为id_no=0 或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秒,在62万行下,时间只有4秒。
或者,更好的是,编写一个简单的存储过程:
create proc count _ stuff as declare @ a int declare
@ b int declare @ c int declare @ d char(10)begin select
@a=count(*)从id_no=0 选择的材料中
@ b=count(*)from stuff where id _ no= 1 end select
@ c=@ a @ b select @ d=convert(char(10),@c)print @d
直接算出结果,执行时间和上面一样快!
总结:
如您所见,优化,即where子句利用索引,而非优化会导致表扫描或额外开销。
1.对列的任何操作都会导致表扫描,包括数据库函数、计算表达式等。查询时,尽可能将操作移到等号右边。
2.in、or子句经常使用工作表使索引无效;如果没有生成大量重复值,考虑反汇编该子句;反汇编子句中应包含索引。
3.善于使用存储过程,使SQL更加灵活高效。
从上面的例子可以看出,SQL优化的本质是在结果正确的前提下,通过使用优化器能够识别的语句,充分利用索引,减少表扫描的I/O次数,尽可能避免表搜索的发生。其实SQL的性能优化是一个复杂的过程,以上只是应用层面的一个体现。深入的研究还会涉及到数据库层的资源分配、网络层的流量控制和操作系统层的整体设计。