如何让你的SQL运行得更快

人们在使用SQL时，往往会陷入一个误区，即过于关注得到的结果是否正确，而忽略了不同实现方法之间可能存在的性能差异，尤其是在大型或复杂的数据库环境中(如在线事务处理OLTP或决策支持系统DSS)。我在工作实践中发现，糟糕的SQL往往来自于索引设计不恰当、连接条件不充分、where子句未优化。经过适当优化后，它们的运行速度明显提高！我从这三个方面来总结:
为了更直观的说明问题，SQL在所有实例中的运行时间都经过测试，不超过1秒的都表示为(< 1秒)。
测试环境-
主机:HP LH II
时钟速度:330MHZ
内存:128 MB
操作系统:Operserver5.0.4
数据库:Sybase11.0.3

1.索引设计不合理
例:表记录有620000行。在不同的索引下尝试一下。以下SQL的操作:
1。日期上有非聚集索引
Select count(*)from record where date > ' 1991 201 ' and date 2000(25秒)Select date，sum(amount)from record group by date(55秒)Select count(*)from record where date > ' 1999 09 01 ' and place in(' bj '，' sh') (27秒)

分析:
日期存在大量重复值。在非聚集索引下，数据随机存储在数据页上。在范围搜索期间，必须执行表扫描来查找该范围内的所有行。

2.日期上的群集索引
select count(*)from record where date > ' 1991 201 ' and date 2000(14秒)select date，Sum(amount)from record group by date(28秒)select count(*)from record where date > ' 1999 09 01 ' and place in(' bj '，' sh') (14秒)

分析:
在簇索引下，数据按物理顺序在数据页上，重复值也排列在一起。所以在搜索一个范围的时候，可以先找到这个范围的起点和终点，只扫描这个范围内的数据页，避免大规模扫描，提高查询速度。

3.位置、日期、金额的组合索引
select count(*)from record where date > ' 1991 201 ' and date 2000(26秒)select date，Sum(amount)from record group by date(27秒)select count(*)from record where date > ' 1999 09 01 ' and place in(' bj，' sh') (< 1秒)

分析:

这是一个不合理的复合索引，因为它的前导列是place，第一个和第二个SQL没有引用place，所以它没有利用upper索引。第三条SQL使用的是place，引用的列全部包含在复合索引中，形成索引覆盖，所以它的速度非常快。

4.日期、地点和金额的综合指数

select(*)from record where date > ' 1991 201 ' and date 2000(< 1秒)select date，Sum(amount)from record group by date(11秒)select count(*)from record where date > ' 1999 09 01 ' 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

将SQL更改为:
select sum(a . amount)from account a，card b其中a.card _ no = b.card _ no和a.account _ no = b.account _ no (< 1秒)

分析:
第一种连接条件下，查询方案是以account为外表，以card为内表。通过使用卡上的索引，I/O时间可通过以下公式估算:

外表账户22541页+(外表账户191122行*内表卡片外表第一行要搜索的3页)=595907 I/O次

第二种连接条件下，查询方案是以卡片为外层表，以账户为内层表。使用账户指数，I/O时间可通过以下公式估算:

外表卡中1944页+(外表卡中7896行*外表中每行对应的内表账户中要查找的4页)= 33528 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) =' 12

分析:

在SQL运行时，对where子句中某一列的任何操作的结果都是逐列计算的，因此它必须在不使用列上方的索引的情况下搜索表；如果可以在编译查询时获得这些结果，则可以通过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有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 =′

得到两个结果并再次相加是值得的。因为每句话被索引，执行时间只有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(*)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) print @d

直接算出结果，执行时间和上面一样快！

摘要:

可见，所谓优化，就是在where子句中使用索引。如果不进行优化，就会出现表扫描或额外的开销。

1。对列的任何操作都会导致表扫描，包括数据库函数、计算表达式等。查询时，尽量将操作移到等号右边。

2.in、or子句经常使用工作表使索引无效；如果没有生成大量重复值，考虑反汇编该子句；反汇编子句中应包含索引。

3。善于使用存储过程，使SQL更加灵活高效。

从上面的例子可以看出，SQL优化的本质就是在结果正确的前提下，通过使用优化器能够识别的语句，充分利用索引，从而减少表扫描的I/O次数，尽可能避免表搜索。其实SQL的性能优化是一个复杂的过程，以上只是应用层面的一个体现。深入的研究还会涉及到数据库层的资源分配、网络层的流量控制以及操作系统层的整体设计。