SQL SERVER调优常用方法

说起SQL SERVER的调优，我想大伙也很想知道这方面的知识。本人也正在探索的路上，大家有什么好的意见，欢迎一起探讨、研究。博取众人之长，才能扬长避短。
本文中的内容主要是摘自《程序员的SQL金典》，如若大家想拜读，可在网上下载拜读（当然最好的方式还是购买作者的书）。
关于调优的方案，有涉及硬件方面的知识，也有涉及软件方面的知识。但本人只是个软件方面的IT男，所以只是记录软件方面的内容。
其实关于SQL SERVER或者是其它数据库来讲，有些优化手段都是一致的。比如常规的方式有如下几种方式：

 

创建必要的索引

 

大学读书时就听说过数据库里面的索引，一直没去深究过，也在无知无畏中进行了四年多的开发生涯，想来惭愧的很，今天有幸了解，顿感人生之阔然开朗一般。索引，不单是数据库里面才有，像我们写代码不也碰到数组也有索引嘛。索引就是已经按照某一种固定好的方式排序好内容，然后我们再去通过索引位置来定位到它。说到SQL SERVER的索引，有必要讲讲两个概念。分别是聚簇索引和非聚簇索引。
1、聚簇索引：就是索引存储的方式跟内容物理存储的方式一致
2、非聚簇索引：就是索引存储的方式跟内容物理存储的顺序不一致
下面简单说明两者的区别。最简单的例子就是汉语词典的方式，对于一本汉语词典来说，它的物理存储顺序是已经固定好了的，是通过拼音的顺序排列好的，这也就是说，汉语词典的物理存储方式就是通过拼音的方式来存储。比如以拼音来建立的索引，就是聚簇索引。WHY？因为索引的存储跟汉语词典的物理存储是一致的，也就是上面概念所说的分类。那么非聚簇索引是什么情况，比如像汉语词典里面偏旁部首是一种索引的话，那么偏旁部首的索引就会是非聚簇索引了。WHY？因为它跟汉语词典原有的物理存储方式不一致。当创建聚簇索引时需要每一张表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个，而非聚集索引则可以创建多个。

注：由于索引需要占据一定的存储空间，而且索引也会降低数据插入、更新和删除的速度，所以应该只创建必要的索引，一般是在检索的字段创建索引。 对于删除来说，索引会造成碎片的问题。因为当我们删除一条记录的时候，对应的索引并不会删除。造成创建的索引被闲置，一旦闲置的索引碎片多了，就会影响查询的效率。系统的垃圾碎片也是一样的道理，需要定期清除。对于索引来说，发现使用索引搜索的速度慢了，就需要定期去重建索引，重建索引将会先删除之前创建的索引，然后再新建新的索引，主流数据库管理系统都提供了重建索引的功能。

 

使用预编译查询

 

很多人会使用存储过程把SQL语句预先编译起来，以此来达到优化的目的。有的项目是根据用户的输入来动态执行SQL语句，不管何种方式，都使用参数化的方式来执行，这样不仅可以避免SQL注入漏洞攻击，最重要数据库会对这些参数化SQL执行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，这样以后再执行这个SQL 的时候就直接使用预编译的结果，这样可以大大提高执行的速度。

 

调整WHERE 子句中的连接顺序（这个不是很懂？）

 

DBMS 一般采用自下而上的顺序解析WHERE 子句，根据这个原理,表连接最好写在其他WHERE条件之前，那些可以过滤掉最大数量记录。
比如下面的SQL语句性能较差：

SELECT [FName],[FCity]
,[FAge]
,[FSalary]
FROM T_Person
WHERE FSalary > 50000
AND FPosition= ‘MANAGER’
AND 25 < (SELECT COUNT(FName) FROM T_Manager
WHERE FManagerId=2);

我们将子查询的条件放到最前面，下面的SQL语句性能比较好：

SELECT [FName],[FCity]
,[FAge]
,[FSalary]
FROM T_Person
WHERE
25 < (SELECT COUNT(FName) FROM T_Manager
WHERE FManagerId=2)
AND FSalary > 50000
AND FPosition= ‘MANAGER’ ;

 

SELECT语句中避免使用'*'

 

对于SELECT * FROM TABLE这种方式，我想很多人都会这么去查询。WHY？一方面是因为系统的数据量级别还比较低，二来也图方便。但是随着项目的数据量疯长，系统的性能急速下降之后，优化的每一种方式都需要引起我们的重视。像查询这种方式来讲，如果不用检索出所有列的情况，尽量指定查询的列。这能有效减轻网络的负载和服务器资源的消耗。即使确实需要检索所有列，也不要使用SELECT *，因为这是一个非常低效的方法，DBMS在解析的过程中，会将*依次转换成所有的列名，这意味着将耗费更多的时间。在SQL Server Management Studio工具里面查询图省事，数据量不大我觉得还是可以的:)，但至少在代码里面是不建议这么处理。

 

多条SQL语句压缩到一句SQL中去执行

 

对于SQL语句的执行，每次都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。

 

使用表的别名

 

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

 

用EXISTS替代IN   （这个是否就没有使用IN的必要了？）

 

在查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用EXISTS而不是IN 通常将提高查询的效率，因为IN 子句将执行一个子查询内部的排序和合并。下面的语句2 就比语句1 效率更加高。

语句 1：

SELECT  [FName],[FSalary],[FNumber] FROM T_Employee
WHERE FNumber> 0
AND FDEPTNO IN (SELECT FNumber
FROM T_Department
WHERE FMangerName = 'Tome')

语句 2：

SELECT [FName],[FSalary],[FNumber] FROM T_Employee
WHERE FNumber > 0
AND EXISTS (SELECT 1
FROM T_Department
WHERE T_Department. FDEPTNO = T_Employee.FNumber
AND FMangerName = ‘MELB’)

 

用表连接替换EXISTS  （这个不是很懂，不知道执行方式是怎么样的？感觉都一样）

 

通常来说，表连接的方式比EXISTS 更有效率，因此如果可能的话尽量使用表连接替换EXISTS。下面的语句2 就比语句1 效率更加高。
语句 1：

SELECT FName FROM T_Employee
WHERE EXISTS
(
SELECT 1 FROM T_Department
WHERE T_Employee.FDepartNo= FNumber
AND FKind='A'
);

语句 2：

SELECT FName FROM T_Department, T_Employee
WHERE T_Employee. FDepartNo = T_Departmen. FNumber
AND FKind = ‘A’ ;

 

避免在索引列上使用计算

 

在 WHERE 子句中，如果索引列是计算或者函数的一部分，DBMS 的优化器将不会使用索引而使用全表扫描。
例如下面的SQL语句用于检索月薪的12倍大于两万五千元的员工：

SELECT [TName],[FSalary] FROM T_Employee
WHERE FSalary * 12 >25000;

由于在大于号左边的是FSalary与12 的成绩表达式，这样DBMS 的优化器将不会使用字段FSalary的索引，因为DBMS必须对T_Employee表进行全表扫描，从而计算FSalary * 12 的值，然后与25000 进行比较。将上面的SQL语句修改为下面的等价写法后DBMS将会使用索引查找，从而大大提高了效率：

SELECT [FName],[FSalary],[FNumber] FROM T_Employee
WHERE FSalary >25000/12;

同样的，不能在索引列上使用函数，因为函数也是一种计算，会造成全表扫描。下面的语句2就比语句1 效率更加高。
语句 1：

SELECT [FAmount],[FName] FROM T_Example
WHERE ABS(FAmount)=300

语句2：

SELECT [FAmount],[FName] FROM T_Example
WHERE FAmount=300 OR FAmount=-300

 

用UNION ALL 替换UNION

 

当 SQL 语句需要UNION 两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用UNION这两个结果集同样会尝试进行合并，然后在输出最终结果前进行排序。因此，如果检索结果中不会有重复的记录的话，应该用UNION ALL替代UNION，这样效率就会因此得到提高。下面的语句2 就比语句1效率更加高。
语句 1：

SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE = '20010101'
UNION
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE ='20010102'

语句2：

SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE ='20010101'
UNION ALL
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE = '20010102'

 

避免隐式类型转换造成的全表扫描

 

T_Person 表的字符串类型字段FLevel 为人员的级别，在FAge 字段上建有索引。我们执行下面的SQL语句用于检索所有级别等于10的员工：

SELECT FId,FAge,FName
FROM T_Person
WHERE FAge=10

在这个SQL 语句中，将字符串类型字段FLevel 与数值10 进行比较，由于在大部分数据库中隐式转换类型中数值类型的优先级高于字符串类型，因此DBMS会对FAge字段进行隐式类型转换，相当于执行了下面的SQL语句：

SELECT FId,FAge,FName
FROM T_Person
WHERE TO_INT(FAge)=10

由于在索引字段上进行了计算，所以造成了索引失效而使用全表扫描。因此应将SQL语句做如下修改：

SELECT FId,FAge,FName
FROM T_Person
WHERE FAge='10'

 

防止检索范围过宽

 

如果DBMS 优化器认为检索范围过宽，那么它将放弃索引查找而使用全表扫描。下面是几种可能造成检索范围过宽的情况：
使用IS NOT NULL或者不等于判断，可能造成优化器假设匹配的记录数太多。
使用LIKE 运算符的时候，"a%"将会使用索引，而"a%c"和"%c"则会使用全表扫描，因为"a%c"和"%c"不能被有效的评估匹配的数量。