SQL优化（三）

1、要合理使用索引 
索引是数据库一个重要的构成部分，很多人都会忽略它，其实索引的根本目的就是 
为了提高查询效率。 
使用原则如下： 
在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则 
由优化器自动生成索引。 

在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。 

在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要 
建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就 
无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度 
。 
如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。 

在写sql语句时就必须注意有些写法是会使得数据库无法使用索引的，比如IS NULL 
IS NOT NULL，IN ，NOT IN 等。。。 

2．避免或简化排序 
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生 
输出时，优化器就避免了排序的步骤。以下是一些影响因素： 
●索引中不包括一个或几个待排序的列； 
●group by或order by子句中列的次序与索引的次序不一样； 
●排序的列来自不同的表。 
为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可 
能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应 
当试图简化它，如缩小排序的列的范围等。 

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 
SELECT ＊ FROM orders WHERE order_num=1008 
这样就能利用索引路径处理查询。 

4．避免相关子查询 
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中 
的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此 
应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的 
行。 

5．避免困难的正规表达式 
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子句中采用了非开始子串，因而这个语句也不会 
使用索引。 

6．使用临时表加速查询,SQL2000中还可以使用表变量来代替临时表 
把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序 
操作，而且在其他方面还能简化优化器的工作。例如： 
SELECT cust.name，rcvbles.balance，……other columns 
FROM cust，rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
AND cust.postcode>“98000” 
ORDER BY cust.name 
如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个 
临时文件中，并按客户的名字进行排序： 
SELECT cust.name，rcvbles.balance，……other columns 
FROM cust，rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
ORDER BY cust.name 
INTO TEMP cust_with_balance 
然后以下面的方式在临时表中查询： 
SELECT ＊ FROM cust_with_balance 
WHERE postcode>“98000” 
临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘 
I/O，所以查询工作量可以得到大幅减少。 
注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意 
不要丢失数据。 

7．用排序来取代非顺序存取 
非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这 
一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。 
有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。 

如果你不太明白SQL语句的执行过的话. 

记住这样的原则. 
1. 
对字段的计算会引起全表扫描. 
所以,能用: 
select * from 表 where 字段=1 
就不要用: 
select * from 表 where 字段-1=0 

2. 
必要的索引对提高数据处理速度很重要.因此,对于经常要排序,进行条件比较的字段,要

建立索引.(注意区分复合索引和单独索引) 
3. 
善于利用存储过程/视图,化繁为简 
4. 
对于不能确定效率的查询分析语句,将它复制到查询分析器中.按Ctrl+L进行分析. 
看看它的执行要经过那些步骤. 
每个步骤的执行时间大慨需要占用的时间百分比. 
在进行表扫描的步骤中,是否利用上了你创建的索引. 
如果还有其他查询方法,再分析其他查询方法,通过比较确定最优的查询方法. 

查询优化建议 
有些查询天生就消耗大量资源。这与基本的数据库和索引问题有关。这些查询的效率并

不低，因为查询优化器会以最有效的可能方式实现这些查询。然而，它们确实消耗大量

资源，而且 Transact-SQL 面向集合的性质使这些查询看起来效率很低。查询优化器的

智能水平无法消除这些构造的固有资源成本。与不复杂的查询相比，这些查询的固有成

本十分昂贵。虽然 Microsoft? SQL Server? 2000 使用最佳的访问计划，但受到基础构

造可能性的限制。例如，下列类型的查询可能占用大量资源： 
返回大结果集的查询 

高度不唯一的 WHERE 子句 
不过有一些针对优化查询和提高查询性能的建议，其中包括： 
添加更多的内存（尤其是如果服务器运行许多复杂查询而且其中几个查询执行很慢） 

在有多个处理器的计算机上运行 SQL Server。多个处理器使 SQL Server 得以利用并行

查询。有关更多信息，请参见并行查询处理。 
考虑重新编写查询。 
如果查询使用游标，则确定如果使用效率更高的游标类型（如快速只进游标）或单纯查

询能否更有效地编写游标查询。单纯查询的性能一般优于游标操作。一组游标语句通常

是一个外循环操作，在此操作中，一旦使用内部语句便开始处理外循环内的每行，因此

可考虑使用 GROUP BY 或 CASE 语句或改为使用子查询。 
如果应用程序使用循环，可考虑在查询内放入循环。应用程序常包含带参数化查询的循

环，该循环执行许多次并要求运行应用程序的计算机与 SQL Server 之间有网络往返。

可改为使用临时表创建一个更复杂的查询。只需提供一个网络往返，查询优化器即会更

好地优化这个查询。 
不要对同一查询内的单个表使用多个别名以模拟索引交叉。模拟索引交叉已没有必要，

因为 SQL Server 会自动考虑索引交叉并且可以在同一查询内的相同表上使用多个索引

。 

只在必要时才使用查询提示。若查询使用在 SQL Server 早期版本上执行的提示，则应

在不指定提示的情况下对该查询进行测试。提示会防碍查询优化器选择更好的执行计划

。有关更多信息，请参见 SELECT。 
利用 query governor 配置选项和设置。可以使用 query governor 配置选项阻止执行

长时间运行的查询，从而防止消耗系统资源。默认情况下，query governor 配置选项允

许执行所有查询，而不考虑查询所需的时间。然而，可以将查询调控器设置到最大秒数

，以允许执行所有连接的所有查询或只允许执行特定连接的查询。查询调控器基于估计

的查询成本而不是实际的已用时间，因此没有任何运行时开销。它还在长时间运行的查

询开始前便将其停止，而不是先运行这些查询直到达到某些预定义的限制为止。 

SQL语句优化的原则： 
================== 
1、使用索引来更快地遍历表。 
  缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引 
下，数据在物理上随机存放在数据页上。合理的索引设计要建立在 
对各种查询的分析和预测上。一般来说：①.有大量重复值、且经常有范围查询 
（between, > , <  ，> =, <  =）和order by、group by发生的列，可考 
虑建立群集索引；②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引 
；③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定 
是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索 
引会导致系统低效。用户在表中每加进一个索引，维护索引集 
合就要做相应的更新工作。 
2、IS NULL 与 IS NOT NULL 
  不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有 
多列这样的情况下，只要这些列中有一列含有null，该列就会从 
索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何 
在where子句中使用is null或is not null的语句优化器是不允 
许使用索引的。 
3、IN和EXISTS 
  EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所 
有的IN操作符子查询改写为使用EXISTS的子查询。 
4、在海量查询时尽量少用格式转换。 
5、当在SQL SERVER 2000中，如果存储过程只有一个参数，并且是OUTPUT类型的，必 
须在调用这个存储过程的时候给这个参数一个初始的值，否则 
会出现调用错误。 
6、ORDER BY和GROPU BY 
  使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意 
如果索引列里面有NULL值，Optimizer将无法优化。 
7、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时 
要尽可能将操作移至等号右边。 
8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把 
子句拆开。拆开的子句中应该包含索引。 
9、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。 
DBCC(DataBase Consistency Checker）是一组用于验证 SQL Server 数据 
库完整性的程序。 
10、慎用游标 
  在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临 
时表定义游标进行操作，这样可使性能得到明显提高。 

SQL Server中有几个可以让你检测、调整和优化SQL Server性能的工具。在本文中，我

将说明如何用SQL Server的工具来优化数据库索引的使用，本文还涉及到有关索引的一

般性知识。 

关于索引的常识 
  
影响到数据库性能的最大因素就是索引。由于该问题的复杂性，我只可能简单的谈谈这

个问题，不过关于这方面的问题，目前有好几本不错的书籍可供你参阅。我在这里只讨

论两种SQL Server索引，即clustered索引和nonclustered索引。当考察建立什么类型的

索引时，你应当考虑数据类型和保存这些数据的column。同样，你也必须考虑数据库可

能用到的查询类型以及使用的最为频繁的查询类型。 

索引的类型 

如果column保存了高度相关的数据，并且常常被顺序访问时，最好使用clustered索引，

这是因为如果使用clustered索引，SQL Server会在物理上按升序（默认）或者降序重排

数据列，这样就可以迅速的找到被查询的数据。同样，在搜寻控制在一定范围内的情况

下，对这些column也最好使用clustered索引。这是因为由于物理上重排数据，每个表格

上只有一个clustered索引。 

与上面情况相反，如果columns包含的数据相关性较差，你可以使用nonculstered索引。

你可以在一个表格中使用高达249个nonclustered索引——尽管我想象不出实际应用场合

会用的上这么多索引。 

当表格使用主关键字（primary keys），默认情况下SQL Server会自动对包含该关键字

的column(s)建立一个独有的cluster索引。很显然，对这些column(s)建立独有索引意味

着主关键字的唯一性。当建立外关键字（foreign key）关系时，如果你打算频繁使用它

，那么在外关键字cloumn上建立nonclustered索引不失为一个好的方法。如果表格有

clustered索引，那么它用一个链表来维护数据页之间的关系。相反，如果表格没有

clustered索引，SQL Server将在一个堆栈中保存数据页。 

数据页 

当索引建立起来的时候，SQLServer就建立数据页（datapage），数据页是用以加速搜索

的指针。当索引建立起来的时候，其对应的填充因子也即被设置。设置填充因子的目的

是为了指示该索引中数据页的百分比。随着时间的推移，数据库的更新会消耗掉已有的

空闲空间，这就会导致页被拆分。页拆分的后果是降低了索引的性能，因而使用该索引

的查询会导致数据存储的支离破碎。当建立一个索引时，该索引的填充因子即被设置好

了，因此填充因子不能动态维护。 

为了更新数据页中的填充因子，我们可以停止旧有索引并重建索引，并重新设置填充因

子（注意：这将影响到当前数据库的运行，在重要场合请谨慎使用）。DBCC 

INDEXDEFRAG和DBCC DBREINDEX是清除clustered和nonculstered索引碎片的两个命令。

INDEXDEFRAG是一种在线操作（也就是说，它不会阻塞其它表格动作，如查询），而

DBREINDEX则在物理上重建索引。在绝大多数情况下，重建索引可以更好的消除碎片，但

是这个优点是以阻塞当前发生在该索引所在表格上其它动作为代价换取来得。当出现较

大的碎片索引时，INDEXDEFRAG会花上一段比较长的时间，这是因为该命令的运行是基于

小的交互块（transactional block）。 
填充因子 
当你执行上述措施中的任何一个，数据库引擎可以更有效的返回编入索引的数据。关于

填充因子（fillfactor）话题已经超出了本文的范畴，不过我还是提醒你需要注意那些

打算使用填充因子建立索引的表格。 
在执行查询时，SQL Server动态选择使用哪个索引。为此，SQL Server根据每个索引上

分布在该关键字上的统计量来决定使用哪个索引。值得注意的是，经过日常的数据库活

动（如插入、删除和更新表格），SQL Server用到的这些统计量可能已经“过期”了，

需要更新。你可以通过执行DBCC SHOWCONTIG来查看统计量的状态。当你认为统计量已经

“过期”时，你可以执行该表格的UPDATE STATISTICS命令，这样SQL Server就刷新了关

于该索引的信息了。 

建立数据库维护计划 

SQL Server提供了一种简化并自动维护数据库的工具。这个称之为数据库维护计划向导

（Database Maintenance Plan Wizard ，DMPW）的工具也包括了对索引的优化。如果你

运行这个向导，你会看到关于数据库中关于索引的统计量，这些统计量作为日志工作并

定时更新，这样就减轻了手工重建索引所带来的工作量。如果你不想自动定期刷新索引

统计量，你还可以在DMPW中选择重新组织数据和数据页，这将停止旧有索引并按特定的

填充因子重建索引。 

速度，影响它的因数太多了，且数据量越大越明显。 
1、存储 
2、tempdb 
3、日志文件 
4、分区视图 
5、簇索引 
  你的表一定有个簇索引，在使用簇索引查询的时候，区块查询是最快的，如用between

，应为他是物理连续的，你应该尽量减少对它的updaet,应为这可以使它物理不连续。 

6、非簇索引 
  非簇索引与物理顺序无关，设计它时必须有高度的可选择性，可以提高查询速度，但

对表update的时候这些非簇索引会影响速度，且占用空间大，如果你愿意用空间和修改

时间换取速度可以考虑。 

7、索引视图 
  如果在视图上建立索引,那视图的结果