数据库查询优化(转载)

9/1/2015来源:SQL技巧人气:1042

数据库查询优化(转载)1 使用SET NOCOUNT ON 选项:

缺 省地,每次执行SQL语句时,一个消息会从服务端发给客户端以显示SQL语句影响的行数。这些信息对客户端来说很少有用。通过关闭这个缺省值,你能减少在 服务端和客户端的网络流量,帮助全面提升服务器和应用程序的性能。为了关闭存储过程级的这个特点,在每个存储过程的开头包含“SET NOCOUNT ON”语句。

2 正确使用UNION和UNION ALL:

许 多人没完全理解UNION和UNION SELECT是怎样工作的,因此,结果浪费了大量不必要的SQLServer资源。当使用UNION时,它相当于在结果集上执行SELECT DISTINCT。换句话说,UNION将联合两个相类似的记录集,然后搜索重复的记录并排除。如果这是你的目的,那么使用UNION是正确的。但如果你 使用UNION联合的两个记录集没有重复记录,那么使用UNION会浪费资源,因为它要寻找重复记录,即使你确定它们不存在。

所以如果你知道你要联合的记录集里没有重复,那么你要使用UNION ALL,而不是UNION。UNION ALL联合记录集,但不搜索重复记录,这样减少SQLServer资源的使用,从而提升性能。

3 尽量不用SELECT * :

绝 大多数情况下,不要用 * 来代替查询返回的字段列表,用 * 的好处是代码量少、就算是表结构或视图的列发生变化,编写的查询SQL语句也不用变,都返回所有的字段。但数据库服务器在解析时,如果碰到 *,则会先分析表的结构,然后把表的所有字段名再罗列出来。这就增加了分析的时间。

4 慎用SELECT DISTINCT:

DISTINCT子句仅在特定功能的时候使用,即从记录集中排除重复记录的时候。这是因为DISTINCT子句先获取结果集然后去重,这样增加SQLServer有用资源的使用。当然,如果你需要去做,那就只有去做了。

当如果你知道SELECT语句将从不返回重复记录,那么使用DISTINCT语句对SQLServer资源不必要的浪费。

5 少用游标:

任何一种游标都会降低SQLServer性能。有些情况不能避免,大多数情况可以避免。所以如果你的应用程序目前正在使用TSQL游标,看看这些代码是否能够重写以避免它们。如果你需要一行一行的执行操作,考虑下边这些选项中的一个或多个来代替游标的使用:

使用临时表

使用WHILE循环

使用派生表

使用相关子查询

使用CASE语句

使用多个查询

上面每一个都能取代游标并且执行更快。 如果你不能避免使用游标,至少试着提高它们的速度,找出加速游标的方法。

6 选择最有效率的表名顺序:

SQLSERVER的 解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表driving table)将被最先处理,在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表,当SQLSERVER处理多个表时,会运用排序及 合并的方式连接它们。首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序;然后扫描第二个表(FROM子句中最后第二个表);最后将所有 从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如: 表 TAB1有 16384 条记录,表 TAB2 有5条记录,选择TAB2作为基础表 (最好的方法):

select count(*) from TAB1 a, TAB2 b

选择TAB1作为基础表 (不佳的方法):

select count(*) from TAB2 a, TAB1 b

如果有3个以上的表连接查询,那就需要选择交叉表(intersection table)作为基础表,交叉表是指那个被其他表所引用的表。

7 使用表的别名(Alias):

当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上,这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。

8 SARG你的WHERE条件:

ARGE来 源于"Search Argument"(搜索参数)的首字母拼成的"SARG",它是指WHERE子句里,列和常量的比较。如果WHERE子句是sargable(可 SARG的),这意味着它能利用索引加速查询的完成。如果WHERE子句不是可SARG的,这意味着WHERE子句不能利用索引(或至少部分不能利用), 执行的是全表或索引扫描,这会引起查询的性能下降。

在WHERE子句里不可 SARG的搜索条件如"IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE"和"LIKE '%500'",通常(但不总是)会阻止查询优化器使用索引执行搜索。另外在列上使用包括函数的表达式、两边都使用相同列的表达式、或和一个列(不是常 量)比较的表达式,都是不可SARG的。

并不是每一个不可SARG的WHERE子句都注定要全表扫描。如果WHERE子句包括两个可SARG和一个不可SARG的子句,那么至少可SARG的子句能使用索引(如果存在的话)帮助快速访问数据。

大多数情况下,如果表上有包 括查询里所有SELECT、JOIN、WHERE子句用到的列的覆盖索引,那么覆盖索引能够代替全表扫描去返回查询的数据,即使它有不可SARG的 WHERE子句。但记住覆盖索引尤其自身的缺陷,如此经常产生宽索引会增加读磁盘I/O。某些情况下,可以把不可SARG的WHERE子句重写成可 SARG的子句。例如:

WHERE SUBSTRING(firstname,1,1) = 'm'

可以写成:

WHERE firstname like 'm%'

这两个WHERE子句有相同的结果,但第一个是不可SARG的(因为使用了函数)将运行得慢些,而第二个是可SARG的,将运行得快些。

如果你不知道特定的WHERE子句是不是可SARG的,在查询分析器里检查查询执行计划。这样做,你能很快的知道查询是使用了索引还是全表扫描来返回的数据。仔细分析,许多不可SARG的查询能写成可SARG的查询。下面分几点讲解WHERE条件的SARG。

8.1 WHERE子句中的连接顺序

SQLSERVER采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例如:

(低效)

SELECT * FROM EMP E

WHERE SAL > 50000

AND JOB = ‘MANAGER’

AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)

(高效)

SELECT * FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)

AND SAL > 50000

AND JOB = ‘MANAGER’

8.2 避免困难的正规表达式:

MATCHES和LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时间。例如:

SELECT * FROM customer WHERE zipcode LIKE "98_ _ _"

即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM customer WHERE zipcode >="98000",在执行查询时就会利用索引来查询,显然会大大提高速度。

另外,还要避免非开始的子串。例如语句:

SELECT * FROM customer WHERE zipcode[2,3] >"80"

在where子句中采用了非开始子串,因而这个语句也不会使用索引。

8.3 避免对大型表行数据的顺序存取:

在嵌套查询中,对表的顺序存 取对查询效率可能产生致命的影响。比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询10亿行数据。避免这种情 况的主要方法就是对连接的列进行索引。例如,两个表:学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个表要做连接,就要在“学 号”这个连接字段上建立索引。

还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作:

SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008

虽然在customer_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:

SELECT * FROM orders WHERE customer_num=104 AND order_num>1001

UNION ALL

SELECT * FROM orders WHERE order_num=1008

这样就能利用索引路径处理查询。

8.4 EXISTS和IN的使用:

在 许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。   在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的,因为它对子查询中的表执行了一个全表遍历。为了避免使用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

8.5 避免在索引列上使用IS NULL和IS NOT NULL:

避免在索引中使用任何可以为空的列,SQLSERVER将无法使用该索引。对于单列索引,如果列包含空值,索引中将不存在此记录;对于复合索引,如果每个列都为空,索引中同样不存在此记录。如果至少有一个列不为空,则记录存在于索引中。  

  如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null),SQLSERVER将不接受下一条具有相同A,B值(123,null)的记录插入。  

  如果所有的索引列都为空,SQLSERVER将认为整个键值为空,而空不可能等 于空,因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使 SQLSERVER停用该索引。下面的代码将会很低效(索引失效):

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL

8.6 避免在索引列上使用计算:

WHERE子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。   例如下面的语句低效 :

SELECT …FROM DEPT WHERE SAL * 12 > 25000

而下面的语句将是高效的:

SELECT …FROM DEPT WHERE SAL > 25000/12

请务必注意,查询中不要对索引列进行处理,如:TRIM,substring,convert等等操作。

8.7 用WHERE子句替换HAVING子句:

避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序、统计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

9 避免或简化排序:

应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。以下是一些影响因素:

l 索引中不包括一个或几个待排序的列;

l group by或order by子句中列的次序与索引的次序不一样;

l 排序的列来自不同的表。

为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。

10 临时表的使用:

临 时表有很多特殊的用途,象用来替代游标,不过它们仍能引起性能问题,如果这个问题能消除,SQLServer将执行得更快。在永久表和临时表的数据行相同 的条件下,使用临时表没有永久表快。但有时还必须得使用临时表,如先从存储大量数据的永久表中提取符全条件的存放到临时表,然后在临时表上执行操作。如果 是直接在存储大量数据的永久表上执行操作(如:统计、循环等),其性能将大打折扣。所以,使不使用临时表,何时使用临时表,需要具体情况决定。

11 是否使用视图:

视图最大的用途是处理安全相 关的问题,而不是一些懒惰的开发人员用来存储经常使用的查询的方法。例如,如果你需要允许用户访问特定SQLServer的数据,那么你也许可以考虑为用 户(或组)创建一个视图,然后给用