SQL Server性能优化详解

约定不等于承诺〃 2022-04-01 13:15 515阅读 0赞

**数据库性能优化详解**

** **

性能调节的目的是通过将网络流通、磁盘  I/O  和  CPU  时间减到最小，使每个查询的响应时间最短并最大限度地提高整个数据库服务器的吞吐量。为达到此目的，需要了解应用程序的需求和数据的逻辑和物理结构，并在相互冲突的数据库使用之间（如联机事务处理  (OLTP)  与决策支持）权衡。

对性能问题的考虑应贯穿于开发阶段的全过程，不应只在最后实现系统时才考虑性能问题。许多使性能得到显著提高的性能事宜可通过开始时仔细设计得以实现。为最有效地优化  Microsoft® SQL Server™ 2000  的性能，必须在极为多样化的情形中识别出会使性能提升最多的区域，并对这些区域集中分析。

虽然其它系统级性能问题（如内存、硬件等）也是研究对象，但经验表明从这些方面获得的性能收益通常会增长。通常情况下， SQL Server  自动管理可用的硬件资源，从而减少对大量的系统级手动调节任务的需求（以及从中所得的收益）。

**目录：**

[设计联合数据库服务器：描述如何通过将处理负荷分摊在多个服务器间而达到高性能级别（如大型 Web 站点等所需的性能级别）。 1][Web _1]

[数据库设计：描述数据库设计如何成为提高总体性能的最有效途径。数据库设计包括逻辑数据库架构（如表和约束）和物理特性（如磁盘系统、对象位置和索引）。... 2][... 2]

[查询优化：描述正确设计的查询（用于应用程序）如何显著提高性能。... 3][... 3]

[应用程序设计：描述正确设计的用户应用程序如何显著提高性能。应用程序设计包括事务边界、锁定和批处理的使用。    4][4]

[优化实用工具和工具性能：述及 Microsoft SQL Server 2000 提供的实用工具和工具的一些可用选项，描述这些选项如何突出说明提高这些工具的性能的方法，以及同时运行这些工具和应用程序的效果。... 5][Microsoft SQL Server 2000 _... 5]

[优化服务器性能：描述如何更改操作系统（Microsoft Windows NT®、Microsoft Windows® 95、Microsoft Windows 98 或 Microsoft Windows 2000）和 SQL Server 的设置以提高总体性能。... 6][Microsoft Windows NT_Microsoft Windows_ 95_Microsoft Windows 98 _ Microsoft Windows 2000_ SQL Server _... 6]

**[操作系统相关优化][Link 1]:描述操作系统和数据库之间可改善的方面…………………………………………………7**

** **

**设计联合数据库服务器**

为达到大型  Web  站点所需的高性能级别，多层系统一般在多个服务器之间平衡每一层的处理负荷。 Microsoft® SQL Server™ 2000  通过对  SQL Server  数据进行水平分区，在一组服务器之间分摊数据库处理负荷。这些服务器相互独立，但也可以相互协作以处理来自应用程序的数据库请求；这样的一组协作服务器称为联合体。

只有当应用程序将每个  SQL  语句发送到拥有该语句所需的大部分数据的成员服务器时，联合数据库层才可以达到非常高的性能级别。这称为使用语句所需的数据配置  SQL  语句。使用所需的数据配置  SQL  语句不是联合服务器所独有的要求；在群集系统中同样有此要求。

虽然服务器联合体与单个数据库服务器呈现给应用程序的图像相同，但在实现数据库服务层的方式上存在内部差异。

<table style="width:103.38%"> 
 <tbody> 
  <tr style="height:29pt"> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:47.5%; padding-right:1.5pt; background:white; height:29pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <strong><span style="color:black; font-size:9pt">单个服务器层</span></strong> 
    </div> </td> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:51.46%; padding-right:1.5pt; background:white; height:29pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <strong><span style="color:black; font-size:9pt">联合服务器层</span></strong> 
    </div> </td> 
  </tr> 
  <tr style="height:29.55pt"> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:47.5%; padding-right:1.5pt; background:white; height:29.55pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">生产服务器上有一个</span> 
     <span style="color:black; font-size:9pt"> SQL Server </span> 
     <span style="color:black; font-size:9pt">实例。</span> 
    </div> </td> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:51.46%; padding-right:1.5pt; background:white; height:29.55pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">每个成员服务器上都有一个</span> 
     <span style="color:black; font-size:9pt"> SQL Server </span> 
     <span style="color:black; font-size:9pt">实例。</span> 
    </div> </td> 
  </tr> 
  <tr style="height:44.6pt"> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:47.5%; padding-right:1.5pt; background:white; height:44.6pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">生产数据存储在一个数据库中。</span> 
    </div> </td> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:51.46%; padding-right:1.5pt; background:white; height:44.6pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">每个成员服务器都有一个成员数据库。数据分布在成员数据库之间。</span> 
    </div> </td> 
  </tr> 
  <tr style="height:75.8pt"> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:47.5%; padding-right:1.5pt; background:white; height:75.8pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">一般每个表都是单个实体。</span> 
    </div> </td> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:51.46%; padding-right:1.5pt; background:white; height:75.8pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">原始数据库中的表被水平分区为成员表。一个成员数据库有一个成员表，而且使用分布式分区视图使每个成员服务器上看起来似乎都有原始表的完整复本。</span> 
    </div> </td> 
  </tr> 
  <tr style="height:44.6pt"> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:47.5%; padding-right:1.5pt; background:white; height:44.6pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">与单个服务器的所有连接和所有</span> 
     <span style="color:black; font-size:9pt"> SQL </span> 
     <span style="color:black; font-size:9pt">语句都由</span> 
     <span style="color:black; font-size:9pt"> SQL Server </span> 
     <span style="color:black; font-size:9pt">的同一个实例处理。</span> 
    </div> </td> 
   <td style="padding-bottom:1.5pt; padding-left:1.5pt; width:51.46%; padding-right:1.5pt; background:white; height:44.6pt; padding-top:1.5pt"> 
    <div style="line-height:10pt; margin:12pt 0cm 6pt"> 
     <span style="color:black; font-size:9pt">应用程序层必须能够在包含语句所引用的大部分数据的成员服务器上配置</span> 
     <span style="color:black; font-size:9pt"> SQL </span> 
     <span style="color:black; font-size:9pt">语句。</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

虽然目的是设计数据库服务器联合体来处理全部的工作负荷，但是可通过设计一组在不同的服务器之间分布数据的分布式分区视图来达到此目的。

** **

**数据库设计**

数据库的设计包括两个组成部分：逻辑设计和物理设计。逻辑数据库设计包括使用数据库组件（如表和约束）为业务需求和数据建模，而无须考虑如何或在哪里物理存储这些数据。物理数据库设计包括将逻辑设计映射到物理媒体上、利用可用的硬件和软件功能使得尽可能快地对数据进行物理访问和维护，还包括生成索引。要在设计后更改这些组件很困难，因此在数据库应用程序开发的早期阶段正确设计数据库、使其为业务需求建模并利用硬件和软件功能很重要。

实现 SQL Server 数据库的优化，首先要有一个好的数据库设计方案。在实际工作中，许多 SQL Server 方案往往是由于数据库设计得不好导致性能很差。实现良好的数据库设计必须考虑这些问题 :

**1.1　逻辑库规范化问题**

一般来说，逻辑数据库设计会满足规范化的前 3 级标准 :

1. 第 1 规范 : 没有重复的组或多值的列。

2. 第 2 规范 : 每个非关键字段必须依赖于主关键字，不能依赖于 1 个组合式主关键字的某些组成部分。

3. 第 3 规范 :1 个非关键字段不能依赖于另 1 个非关键字段。

遵守这些规则的设计会产生较少的列和更多的表，因而也就减少了数据冗余，也减少了用于存储数据的页。但表关系也许需要通过复杂的合并来处理，这样会降低系统的性能。某种程度上的非规范化可以改善系统的性能，非规范化过程可以根据性能方面不同的考虑用多种不同的方法进行，但以下方法经实践验证往往能提高性能。

1. 如果规范化设计产生了许多 4 路或更多路合并关系，就可以考虑在数据库实体 ( 表 ) 中加入重复属性 ( 列 )

2. 常用的计算字段 ( 如总计、最大值等 ) 可以考虑存储到数据库实体中。

比如某一个项目的计划管理系统中有计划表，其字段为 : 项目编号、年初计划、二次计划、调整计划、补列计划 … ，而计划总数 ( 年初计划 \+ 二次计划 \+ 调整计划 \+ 补列计划 ) 是用户经常需要在查询和报表中用到的，在表的记录量很大时，有必要把计划总数作为 1 个独立的字段加入到表中。这里可以采用触发器以在客户端保持数据的一致性。

3. 重新定义实体以减少外部属性数据或行数据的开支。相应的非规范化类型是 :

(1) 把 1 个实体 ( 表 ) 分割成 2 个表 ( 把所有的属性分成 2 组 ) 。这样就把频繁被访问的数据同较少被访问的数据分开了。这种方法要求在每个表中复制首要关键字。这样产生的设计有利于并行处理，并将产生列数较少的表。

(2) 把 1 个实体 ( 表 ) 分割成 2 个表 ( 把所有的行分成 2 组 ) 。这种方法适用于那些将包含大量数据的实体 ( 表 ) 。在应用中常要保留历史记录，但是历史记录很少用到。因此可以把频繁被访问的数据同较少被访问的历史数据分开。而且如果数据行是作为子集被逻辑工作组 ( 部门、销售分区、地理区域等 ) 访问的，那么这种方法也是很有好处的。

**1.2　生成物理数据库**

要想正确选择基本物理实现策略，必须懂得数据库访问格式和硬件资源的操作特点，主要是内存和磁盘子系统 I/O 。这是一个范围广泛的话题，但以下的准则可能会有所帮助。

1. 与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引的列更是如此。比如能使用 smallint 类型就不要用 integer 类型，这样索引字段可以被更快地读取，而且可以在 1 个数据页上放置更多的数据行，因而也就减少了 I/O 操作。

2. 把 1 个表放在某个物理设备上，再通过 SQL Server 段把它的不分簇索引放在 1 个不同的物理设备上，这样能提高性能。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做的好处更加明显。

3. 用 SQL Server 段把一个频繁使用的大表分割开，并放在 2 个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性能。

4. 用 SQL Server 段把文本或图像列的数据存放在 1 个单独的物理设备上可以提高性能。 1 个专用的智能型的控制器能进一步提高性能。

** **

**查询优化**

** **

**查询速度慢的原因很多，常见如下几种：　　**

1 、没有索引或者没有用到索引 ( 这是查询慢最常见的问题，是程序设计的缺陷 )

2 、 I/O 吞吐量小，形成了瓶颈效应。

3 、没有创建计算列导致查询不优化。

4 、内存不足

5 、网络速度慢

6 、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）

7 、锁或者死锁 ( 这也是查询慢最常见的问题，是程序设计的缺陷 )

8 、 sp\_lock,sp\_who, 活动的用户查看 , 原因是读写竞争资源。

9 、返回了不必要的行和列

10 、查询语句不好，没有优化

** **

**可以通过如下方法来优化查询 :　**

1 、把数据、日志、索引放到不同的 I/O 设备上，增加读取速度，以前可以将 Tempdb 应放在 RAID0 上， SQL2000 不在支持。数据量（尺寸）越大，提高 I/O 越重要 .

2 、纵向、横向分割表，减少表的尺寸 (sp\_spaceuse)

3 、升级硬件

4 、根据查询条件 , 建立索引 , 优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值 0 ）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建） , 不要对有限的几个值的字段建单一索引如性别字段

5 、提高网速 ;

6 、扩大服务器的内存 ,Windows 2000 和 SQL server 2000 能支持 4-8G 的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行  Microsoft SQL Server? 2000  时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的  1.5  倍。如果另外安装了全文检索功能，并打算运行  Microsoft  搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的  3  倍。将  SQL Server max server memory  服务器配置选项配置为物理内存的  1.5  倍（虚拟内存大小设置的一半）。

7 、增加服务器  CPU 个数 ; 但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是 MsSQL 自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和 GROUP BY 字句同时执行， SQL SERVER 根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的 CPU 的查询最适合并行处理。但是更新操作 Update,Insert ，  Delete 还不能并行处理。

8 、如果是使用 like 进行查询的话，简单的使用 index 是不行的，但是全文索引，耗空间。  like 'a%'  使用索引  like '%a'  不使用索引用  like '%a%'  查询时，查询耗时和字段值总长度成正比 , 所以不能用 CHAR 类型，而是 VARCHAR 。对于字段的值很长的建全文索引。

9 、 DB Server  和 APPLication Server  分离； OLTP 和 OLAP 分离

10 、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层  Web  站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照 SQL 帮助文件 ' 分区视图 ' ）

a 、在实现分区视图之前，必须先水平分区表

b 、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。

11 、重建索引  DBCC REINDEX ,DBCC INDEXDEFRAG, 收缩数据和日志  DBCC SHRINKDB,DBCC SHRINKFILE.  设置自动收缩日志 . 对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在 T-sql 的写法上有很大的讲究，下面列出常见的要点：首先， DBMS 处理查询计划的过程是这样的：

1 、 查询语句的词法、语法检查

2 、 将语句提交给 DBMS 的查询优化器

3 、 优化器做代数优化和存取路径的优化

4 、 由预编译模块生成查询规划

5 、 然后在合适的时间提交给系统处理执行

6 、 最后将执行结果返回给用户其次，看一下 SQL SERVER 的数据存放的结构：一个页面的大小为 8K(8060) 字节， 8 个页面为一个盘区，按照 B 树存放。

12 、 Commit 和 rollback 的区别  Rollback: 回滚所有的事物。  Commit: 提交当前的事物 .  没有必要在动态 SQL 里写事物，如果要写请写在外面如：  begin tran exec(@s) commit trans  或者将动态 SQL  写成函数或者存储过程。

13 、在查询 Select 语句中用 Where 字句限制返回的行数 , 避免表扫描 , 如果返回不必要的数据，浪费了服务器的 I/O 资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表 , 后果严重。

14 、 SQL 的注释申明对执行没有任何影响

15 、尽可能不使用光标，它占用大量的资源。如果需要 row-by-row 地执行，尽量采用非光标技术 , 如：在客户端循环，用临时表， Table 变量，用子查询，用 Case 语句等等。游标可以按照它所支持的提取选项进行分类： 只进 必须按照从第一行到最后一行的顺序提取行。 FETCH NEXT  是唯一允许的提取操作 , 也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在 SQL2000 下变得功能很强大，他的目的是支持循环。有四个并发选项  READ\_ONLY ：不允许通过游标定位更新 (Update) ，且在组成结果集的行中没有锁。  OPTIMISTIC WITH valueS: 乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项 OPTIMISTIC WITH ROW VERSIONING: 此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在  SQL Server  中，这个性能由  timestamp  数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值： @@DBTS 。每次以任何方式更改带有  timestamp  列的行时， SQL Server  先在时间戳列中存储当前的  @@DBTS  值，然后增加  @@DBTS  的值。如果某 个表具有  timestamp  列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较  timestamp  列即可。如果应用程序对没有  timestamp  列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。  SCROLL LOCKS  这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚；当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的  Select  语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的选项为关，则  COMMIT  语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标  Select  语句中的锁提示。锁提示 只读 乐观数值 乐观行版本控制 锁定无提示 未锁定 未锁定 未锁定 更新  NOLOCK  未锁定 未锁定未锁定 未锁定  HOLDLOCK  共享 共享 共享 更新  UPDLOCK  错误 更新 更新 更新  TABLOCKX  错误 未锁定 未锁定更新其它 未锁定 未锁定 未锁定 更新  \* 指定  NOLOCK  提示将使指定了该提示的表在游标内是只读的。

16 、用 Profiler 来跟踪查询，得到查询所需的时间，找出 SQL 的问题所在 ; 用索引优化器优化索引

17 、注意 UNion 和 UNion all  的区别。 UNION all 好

18 、注意使用 DISTINCT ，在没有必要时不要用，它同 UNION 一样会使查询变慢。重复的记录在查询里是没有问题的

19 、查询时不要返回不需要的行、列

20 、用 sp\_configure 'query governor cost limit' 或者 SET QUERY\_GOVERNOR\_COST\_LIMIT 来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询 , 在查询之前就扼杀掉。  SET LOCKTIME 设置锁的时间

21 、用 select top 100 / 10 Percent  来限制用户返回的行数或者 SET ROWCOUNT 来限制操作的行

22 、在 SQL2000 以前，一般不要用如下的字句 : "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" ，因为他们不走索引全是表扫描。也不要在 Where 字句中的列名加函数，如 Convert ， substring 等 , 如果必须用函数的时候，创建计算列再创建索引来替代 . 还可以变通写法： Where SUBSTRING(firstname,1,1) = 'm' 改为 Where firstname like 'm%' （索引扫描），一定要将函数和列名分开。并且索引不能建得太多和太大。 NOT IN 会多次扫描表，使用 EXISTS 、 NOT EXISTS  ， IN , LEFT OUTER JOIN  来替代，特别是左连接 , 而 Exists 比 IN 更快，最慢的是 NOT 操作 . 如果列的值含有空，以前它的索引不起作用，现在 2000 的优化器能够处理了。相同的是 IS NULL ， "NOT", "NOT EXISTS", "NOT IN" 能优化她，而 "<>" 等还是不能优化，用不到索引。

23 、使用 Query Analyzer ，查看 SQL 语句的查询计划和评估分析是否是优化的 SQL 。一般的 20% 的代码占据了 80% 的资源，我们优化的重点是这些慢的地方。

24 、如果使用了 IN 或者 OR 等时发现查询没有走索引，使用显示申明指定索引：  Select \* FROM PersonMember (INDEX = IX\_Title) Where processid IN (' 男 ' ， ' 女 ')

25 、将需要查询的结果预先计算好放在表中，查询的时候再 Select 。这在 SQL7.0 以前是最重要的手段。例如医院的住院费计算。

26 、 MIN()  和  MAX() 能使用到合适的索引。

27 、数据库有一个原则是代码离数据越近越好，所以优先选择 Default, 依次为 Rules,Triggers, Constraint （约束如外健主健 CheckUNIQUE……, 数据类型的最大长度等等都是约束） ,Procedure. 这样不仅维护工作小，编写程序质量高，并且执行的速度快。

28 、如果要插入大的二进制值到 Image 列，使用存储过程，千万不要用内嵌 Insert 来插入 ( 不知 JAVA 是否 ) 。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值 . 存储过程就没有这些动作 :  方法： Create procedure p\_insert as insert into table(Fimage) values (@image),  在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

29 、 Between 在某些时候比 IN  速度更快 ,Between 能够更快地根据索引找到范围。用查询优化器可见到差别。  select \* from chineseresume where title in (' 男 ',' 女 ') Select \* from chineseresume where between ' 男 ' and ' 女 '  是一样的。由于 in 会在比较多次，所以有时会慢些。

30 、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。

31 、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。

32 、用 OR 的字句可以分解成多个查询，并且通过 UNION  连接多个查询。他们的速度只同是否使用索引有关 , 如果查询需要用到联合索引，用 UNION all 执行的效率更高 . 多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。

33 、尽量少用视图，它的效率低。对视图操作比直接对表操作慢 , 可以用 stored procedure 来代替她。特别的是不要用视图嵌套 , 嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的 SQL 。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销 , 查询受到干扰 . 为了加快视图的查询， MsSQL 增加了视图索引的功能。

34 、没有必要时不要用 DISTINCT 和 ORDER BY ，这些动作可以改在客户端执行。它们增加了额外的开销。这同 UNION  和 UNION ALL 一样的道理。

35 、在 IN 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

36 、当用 Select INTO 时，它会锁住系统表 (sysobjects ， sysindexes 等等 ) ，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是  select INTO. drop table t\_lxh begin tran select \* into t\_lxh from chineseresume where name = 'XYZ' --commit  在另一个连接中 Select \* from sysobjects 可以看到  Select INTO  会锁住系统表， Create table  也会锁系统表 ( 不管是临时表还是系统表 ) 。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。

37 、一般在 GROUP BY  个 HAVING 字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优： select  的 Where 字句选择所有合适的行， Group By 用来分组个统计行， Having 字句用来剔除多余的分组。这样 Group By  个 Having 的开销小，查询快 . 对于大的数据行进行分组和 Having 十分消耗资源。如果 Group BY 的目的不包括计算，只是分组，那么用 Distinct 更快

38 、一次更新多条记录比分多次更新每次一条快 , 就是说批处理好

39 、少用临时表，尽量用结果集和 Table 类性的变量来代替它 ,Table  类型的变量比临时表好

40 、在 SQL2000 下，计算字段是可以索引的，需要满足的条件如下：

a 、计算字段的表达是确定的

b 、不能用在 TEXT,Ntext ， Image 数据类型

c 、必须配制如下选项  ANSI\_NULLS = ON, ANSI\_PADDINGS = ON, …….

41 、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL, 可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。以前由于 SQL SERVER 对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。 SQL2000 支持 UDFs, 现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程

42 、不要在一句话里再三的使用相同的函数，浪费资源 , 将结果放在变量里再调用更快

43 、 Select COUNT(\*) 的效率教低，尽量变通他的写法，而 EXISTS 快 . 同时请注意区别：  select count(Field of null) from Table  和  select count(Field of NOT null) from Table  的返回值是不同的！！！

44 、当服务器的内存够多时，配制线程数量  =  最大连接数 \+5 ，这样能发挥最大的效率；否则使用 配制线程数量 < 最大连接数启用 SQL SERVER 的线程池来解决 , 如果还是数量  =  最大连接数 \+5 ，严重的损害服务器的性能。

45 、按照一定的次序来访问你的表。如果你先锁住表 A ，再锁住表 B ，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表 B ，再锁定表 A ，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现

46 、通过 SQL Server Performance Monitor 监视相应硬件的负载  Memory: Page Faults / sec 计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。

Process:

1 、 % DPC Time  指在范例间隔期间处理器用在缓延程序调用 (DPC) 接收和提供服务的百分比。 (DPC  正在运行的为比标准间隔优先权低的间隔 ) 。 由于  DPC  是以特权模式执行的， DPC  时间的百分比为特权时间百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部 分。这个总数显示了作为实例时间百分比的平均忙时。

2 、 %Processor Time 计数器　如果该参数值持续超过 95% ，表明瓶颈是 CPU 。可以考虑增加一个处理器或换一个更快的处理器。

3 、 % Privileged Time  指非闲置处理器时间用于特权模式的百分比。 ( 特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务 ) 。特权时间的  %  包括为间断和  DPC  提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。

4 、 % User Time 表示耗费 CPU 的数据库操作，如排序，执行 aggregate functions 等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。  Physical Disk: Curretn Disk Queue Length 计数器该值应不超过磁盘数的 1.5~2 倍。要提高性能，可增加磁盘。  SQLServer:Cache Hit Ratio 计数器该值越高越好。如果持续低于 80% ，应考虑增加内存。 注意该参数值是从 SQL Server 启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

47 、分析 select emp\_name form employee where salary > 3000  在此语句中若 salary 是 Float 类型的，则优化器对其进行优化为 Convert(float,3000) ，因为 3000 是个整数，我们应在编程时使用 3000.0 而不要等运行时让 DBMS 进行转化。同样字符和整型数据的转换。

48 、查询的关联同写的顺序

select a.personMemberID, \* from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681'  （ A = B ,B = ' 号码 ' ）

select a.personMemberID, \* from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681'  （ A = B ,B = ' 号码 ' ，  A = ' 号码 ' ）

select a.personMemberID, \* from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681'  （ B = ' 号码 ' ，  A = ' 号码 ' ）

49 、

(1)IF  没有输入负责人代码  THEN code1=0 code2=9999 ELSE code1=code2= 负责人代码  END IF  执行 SQL 语句为 : Select  负责人名  FROM P2000 Where  负责人代码 >=:code1 AND 负责人代码  <=:code2

(2)IF  没有输入负责人代码  THEN  　 Select  负责人名  FROM P2000 ELSE code=  负责人代码  Select  负责人代码  FROM P2000 Where  负责人代码 =:code END IF  第一种方法只用了一条 SQL 语句 , 第二种方法用了两条 SQL 语句。在没有输入负责人代码时 , 第二种方法显然比第一种方法执行效率高 , 因为它没有限制条件 ;  在输入了负责人代码时 , 第二种方法仍然比第一种方法效率高 , 不仅是少了一个限制条件 , 还因相等运算是最快的查询运算。我们写程序不要怕麻烦

50 、关于 JOBCN 现在查询分页的新方法（如下），用性能优化器分析性能的瓶颈，如果在 I/O 或者网络的速度上，如下的方法优化切实有效，如果在 CPU 或者内存上，用现在的方法更好。请区分如下的方法，说明索引越小越好。

begin

DECLARE @local\_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local\_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by ReferenceID

select \* from @local\_variable where Fid > 40 and fid <= 60

end  和

begin

DECLARE @local\_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local\_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select \* from @local\_variable where Fid > 40 and fid <= 60

end  的不同

begin

create table \#temp (FID int identity(1,1),ReferenceID varchar(20))

insert into \#temp (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select \* from \#temp where Fid > 40 and fid <= 60 drop table \#temp

end

** **

完全通过系统级服务器性能优化（如内存大小、文件系统类型、处理器的数目及类型等）解决性能问题可能很诱人。但经验表明大多数性能问题不能用这种方法解决。必须通过这些方法解决性能问题：分析应用程序以及应用程序提交给数据库的查询和更新，并分析这些查询和更新如何与数据库架构交互。

**持续时间意外地长的查询和更新可能由下列原因引起：**

·                      网络通讯速度慢。

·                      服务器计算机的内存不足或  Microsoft® SQL Server™ 2000  可用的内存不足。

·                      缺少有用的统计数据。

·                      统计数据过期。

·                      缺少有用的索引

·                      缺少有用的数据条带化。

**当查询或更新花费的时间比预期的长时，使用下面的检查清单提高性能** ：

说明    建议在与技术支持提供商联系之前先参考该检查清单。

1.               性能问题与查询以外的组件是否有关？例如，问题是否为网络性能慢？是否有任何其它可能引起或间接导致性能下降的组件？可以使用  Windows NT  性能监视器监视与  SQL Server  相关和与  SQL Server  不相关的组件性能。有关更多信息，请参见使用系统监视器进行监视。

2.               如果性能问题与查询相关，涉及哪个查询或哪组查询？使用  SQL  事件探查器帮助识别慢速查询。有关更多信息，请参见使用  SQL  事件探查器进行监视。

通过使用  SET  语句启用  SHOWPLAN 、 STATISTICS IO 、 STATISTICS TIME  和  STATISTICS PROFILE  选项，可以确定数据库查询性能。

·                              SHOWPLAN  描述  SQL Server  查询优化器选择的数据检索方法。有关更多信息，请参见  SET SHOWPLAN\_ALL 。

·                              STATISTICS IO  报告与语句内引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）有关的信息。有关更多信息，请参见 SET STATISTICS IO 。

·                              STATISTICS TIME  显示分析、编译和执行查询所需的时间（以毫秒为单位）。有关更多信息，请参见  SET STATISTICS TIME 。

·                              STATISTICS PROFILE  显示每个查询执行后的结果集，代表查询执行的配置文件。有关更多信息，请参见  SET STATISTICS PROFILE 。

在  SQL  查询分析器中，还可以打开 **graphical execution plan** 选项查看关于  SQL Server  如何检索数据的图形表示。

由这些工具收集的信息使您得以确定  SQL Server  查询优化器正在如何执行查询以及正在使用哪些索引。利用这些信息，可以确定通过重写查询、更改表上的索引或修改数据库设计等方法能否提高性能。有关更多信息，请参见分析查询。

3.               是否已经用有用的统计数据优化查询？

SQL Server  自动在索引列上创建对列内的值分布情况的统计。也可以使用  SQL  查询分析器或  CREATE STATISTICS  语句在非索引列上手动创建统计；或者如果将 **auto create statistics** 数据库选项设置为 **true** ，则自动在非索引列上创建统计。查询处理器可以利用这些统计确定最佳的查询评估策略。在联接操作所涉及的非索引列上维护附加的统计信息可以提高查询性能。有关更多信息，请参见统计信息。

使用  SQL  事件探查器或  SQL  查询分析器内的图形执行计划来监视查询，以确定查询是否有足够的统计信息。有关更多信息，请参见错误和警告事件分类。

4.               查询统计信息是否为最新？统计信息是否自动更新？

SQL Server  自动在索引列上创建并更新查询统计（只要没有禁用自动查询统计更新特性）。另外，可以使用  SQL  查询分析器或  UPDATE STATISTICS  语句在非索引列上手工更新统计；或者如果 **auto update statistics** 数据库选项设置为 **true** ，则自动在非索引列上更新统计。最新的统计不取决于日期或时间数据。如果尚未进行  UPDATE  操作，则查询统计信息仍是最新的。

如果没有将统计设置为自动更新，则应设置为自动更新。有关更多信息，请参见 统计信息 。

5.               是否有合适的索引？添加一个或多个索引是否会提高查询性能？有关更多信息，请参见 [索引优化建议][Link 2] 。

6.               是否有任何数据热点或索引热点？如果有，考虑使用磁盘条带化。有关更多信息，请参见 [使用文件组放置数据][Link 3] 和 [ RAID][RAID] 。

7.              是否为查询优化器提供了优化复杂查询的最有利条件？有关更多信息，请参见 [查询优化建议。][Link 4]

**存储过程的优化：**

一、前言：在经过一段时间的存储过程开发之后，写下了一些开发时候的小结和经验与大家共享，希望对大家有益，主要是针对 Sybase 和 SQL Server 数据库，但其它数据库应该有一些共性。

二、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对 SP （存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。

三、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用 SP 来封装数据库操作。如果项目的 SP 较多，书写又没有一定的规范，将会影响以后的系统维护困难和大 SP 逻辑的难以理解，另外如果数据库的数据量大或者项目对 SP 的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的 SP 要比一个性能差的 SP 的效率甚至高几百倍。

四、内容：

1 、开发人员如果用到其他库的 Table 或 View ，务必在当前库中建立 View 来实现跨库操作，最好不要直接使用“ databse.dbo.table\_name ”，因为 sp\_depends 不能显示出该 SP 所使用的跨库 table 或 view ，不方便校验。

2 、开发人员在提交 SP 前，必须已经使用 set showplan on 分析过查询计划，做过自身的查询优化检查。

3 、高程序运行效率，优化应用程序，在 SP 编写过程中应该注意以下几点：

a) SQL 的使用规范：

i.  尽量避免大事务操作，慎用 holdlock 子句，提高系统并发能力。

ii.  尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。

iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

iv.  注意 where 字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

v.  不要在 where 子句中的“ = ”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

vi.  尽量使用 exists 代替 select count(1) 来判断是否存在记录， count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(\*) 更有效率。

vii. 尽量使用“ >= ”，不要使用“ > ”。

viii. 注意一些 or 子句和 union 子句之间的替换

ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。

x.  注意存储过程中参数和数据类型的关系。

xi. 注意 insert 、 update 操作的数据量，防止与其他应用冲突。如果数据量超过 200 个数据页面（ 400k ），那么系统将会进行锁升级，页级锁会升级成表级锁。

b)  索引的使用规范：

i.  索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引。

ii.  尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index\_name 来强制指定索引

iii. 避免对大表查询时进行 table scan ，必要时考虑新建索引。

iv.  在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

v.  要注意索引的维护，周期性重建索引，重新编译存储过程。

c)tempdb 的使用规范：

i.  尽量避免使用 distinct 、 order by 、 group by 、 having 、 join 、 cumpute ，因为这些语句会加重 tempdb 的负担。

ii.  避免频繁创建和删除临时表，减少系统表资源的消耗。

iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table ，避免 log ，提高速度；如果数据量不大，为了缓和系统表的资源，建议先 create table ，然后 insert 。

iv.  如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。

v.  如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

vi.  慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用 tempdb 的系统表。

d) 合理的算法使用：

根据上面已提到的 SQL 优化技术和 ASE Tuning 手册中的 SQL 优化内容 , 结合实际应用 , 采用多种算法进行比较 , 以获得消耗资源最少、效率最高的方法。具体可用 ASE 调优命令： set statistics io on, set statistics time on , set showplan on  等。

**以下是一些常用的优化需要注意的方面：**

操作符优化   
IN  操作符   
 用 IN 写出来的 SQL 的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。   
 但是用 IN 的 SQL 性能总是比较低的，从 ORACLE 执行的步骤来分析用 IN 的 SQL 与不用 IN 的 SQL 有以下区别：   
ORACLE 试图将其转换成多个表的连接，如果转换不成功则先执行 IN 里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用 IN 的 SQL 至少多了一个转换的过程。一般的 SQL 都可以转换成功，但对于含有分组统计等方面的 SQL 就不能转换了。   
 推荐方案：在业务密集的 SQL 当中尽量不采用 IN 操作符。   
NOT IN 操作符   
 此操作是强列推荐不使用的，因为它不能应用表的索引。   
 推荐方案：用 NOT EXISTS  或（外连接 \+ 判断为空）方案代替   
<>  操作符（不等于）   
 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。   
 推荐方案：用其它相同功能的操作运算代替，如   
a<>0  改为  a>0 or a<0  
a<>’’  改为  a>’’  
IS NULL  或 IS NOT NULL 操作（判断字段是否为空）   
 判断字段是否为空一般是不会应用索引的，因为 B 树索引是不索引空值的。   
 推荐方案：   
 用其它相同功能的操作运算代替，如   
a is not null  改为  a>0  或 a>’’ 等。   
 不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。   
 建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）   
>  及  <  操作符（大于或小于操作符）   
 大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有 100 万记录，一个数值型字段 A ， 30 万记录的 A=0 ， 30 万记录的 A=1 ， 39 万记录的 A=2 ， 1 万记录的 A=3 。那么执行 A>2 与 A>=3 的效果就有很大的区别了，因为 A>2 时 ORACLE 会先找出为 2 的记录索引再进行比较，而 A>=3 时 ORACLE 则直接找到 =3 的记录索引。   
LIKE 操作符   
LIKE 操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如 LIKE ‘%5400%’  这种查询不会引用索引，而 LIKE ‘X5400%’ 则会引用范围索引。一个实际例子：用 YW\_YHJBQK 表中营业编号后面的户标识号可来查询营业编号  YY\_BH LIKE ‘%5400%’  这个条件会产生全表扫描，如果改成 YY\_BH LIKE ’X5400%’ OR YY\_BH LIKE ’B5400%’  则会利用 YY\_BH 的索引进行两个范围的查询，性能肯定大大提高。   
UNION 操作符   
UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表 UNION 。如：   
select \* from gc\_dfys  
union  
select \* from ls\_jg\_dfys  
 这个 SQL 在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。   
 推荐方案：采用 UNION ALL 操作符替代 UNION ，因为 UNION ALL 操作只是简单的将两个结果合并后就返回。   
select \* from gc\_dfys  
union all  
select \* from ls\_jg\_dfys  
SQL 书写的影响   
 同一功能同一性能不同写法 SQL 的影响   
 如一个 SQL 在 A 程序员写的为   
Select \* from zl\_yhjbqk  
B 程序员写的为   
Select \* from dlyx.zl\_yhjbqk （带表所有者的前缀）   
C 程序员写的为   
Select \* from DLYX.ZLYHJBQK （大写表名）   
D 程序员写的为   
Select \* from DLYX.ZLYHJBQK （中间多了空格）   
 以上四个 SQL 在 ORACLE 分析整理之后产生的结果及执行的时间是一样的，但是从 ORACLE 共享内存 SGA 的原理，可以得出 ORACLE 对每个 SQL  都会对其进行一次分析，并且占用共享内存，如果将 SQL 的字符串及格式写得完全相同则 ORACLE 只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析 SQL 的时间，而且可以减少共享内存重复的信息， ORACLE 也可以准确统计 SQL 的执行频率。   
WHERE 后面的条件顺序影响   
WHERE 子句后面的条件顺序对大数据量表的查询会产生直接的影响，如   
Select \* from zl\_yhjbqk where dy\_dj = '1KV 以下 ' and xh\_bz=1  
Select \* from zl\_yhjbqk where xh\_bz=1 and dy\_dj = '1KV 以下 '  
 以上两个 SQL 中 dy\_dj （电压等级）及 xh\_bz （销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条 SQL 的 dy\_dj = '1KV 以下 ' 条件在记录集内比率为 99% ，而 xh\_bz=1 的比率只为 0.5% ，在进行第一条 SQL 的时候 99% 条记录都进行 dy\_dj 及 xh\_bz 的比较，而在进行第二条 SQL 的时候 0.5% 条记录都进行 dy\_dj 及 xh\_bz 的比较，以此可以得出第二条 SQL 的 CPU 占用率明显比第一条低。   
 查询表顺序的影响   
 在 FROM 后面的表中的列表顺序会对 SQL 执行性能影响，在没有索引及 ORACLE 没有对表进行统计分析的情况下 ORACLE 会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析， ORACLE 会自动先进小表的链接，再进行大表的链接）   
SQL 语句索引的利用   
 对操作符的优化（见上节）   
 对条件字段的一些优化   
 采用函数处理的字段不能利用索引，如：   
substr(hbs\_bh,1,4)=’5400’ ，优化处理： hbs\_bh like ‘5400%’  
trunc(sk\_rq)=trunc(sysdate) ， 优化处理：   
sk\_rq>=trunc(sysdate) and sk\_rq<trunc(sysdate+1)  
 进行了显式或隐式的运算的字段不能进行索引，如：   
ss\_df+20>50 ，优化处理： ss\_df>30  
‘X’||hbs\_bh>’X5400021452’ ，优化处理： hbs\_bh>’5400021542’  
sk\_rq+5=sysdate ，优化处理： sk\_rq=sysdate-5  
hbs\_bh=5401002554 ，优化处理： hbs\_bh=’ 5401002554’ ，注：此条件对 hbs\_bh  进行隐式的 to\_number 转换，因为 hbs\_bh 字段是字符型。   
 条件内包括了多个本表的字段运算时不能进行索引，如：   
ys\_df>cx\_df ，无法进行优化   
qc\_bh||kh\_bh=’5400250000’ ，优化处理： qc\_bh=’5400’ and kh\_bh=’250000’  
 应用 ORACLE 的 HINT （提示）处理   
 提示处理是在 ORACLE 产生的 SQL 分析执行路径不满意的情况下要用到的。它可以对 SQL 进行以下方面的提示   
 目标方面的提示：   
COST （按成本优化）   
RULE （按规则优化）   
CHOOSE （缺省）（ ORACLE 自动选择成本或规则进行优化）   
ALL\_ROWS （所有的行尽快返回）   
FIRST\_ROWS （第一行数据尽快返回）   
 执行方法的提示：   
USE\_NL （使用 NESTED LOOPS 方式联合）   
USE\_MERGE （使用 MERGE JOIN 方式联合）   
USE\_HASH （使用 HASH JOIN 方式联合）   
 索引提示：   
INDEX （ TABLE INDEX ）（使用提示的表索引进行查询）   
 其它高级提示（如并行处理等等）   
ORACLE 的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给 ORACLE 执行的一个建议，有时如果出于成本方面的考虑 ORACLE 也可能不会按提示进行。根据实践应用，一般不建议开发人员应用 ORACLE 提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了， ORACLE 在 SQL 执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。

与没有优化数据库的网站相比，数据库的存取会降低你的系统性能。但是大多数情况下，网站和数据库有密不可分的关系，正是数据库给站点提供了大容量、多样性、个性化等特色，并实现了很多特殊的功能。   
**1**  不要忘记给数据库做索引。   
 合理的索引能立即显著地提高数据库整个系统的性能。可以参考有关 SQL 性能调试书籍，学会根据所需查询方式合理制作索引和根据索引方式改进查询语句。   
**2**  在适当的情况下，尽可能的用存储过程而不是 SQL 查询。   
 因为前者已经过了预编译，运行速度更快。同时让数据库仅仅返回你所需要的那些数据，而不是返回大量数据再让 ASP 程序过滤。总之要充分和有效地发挥数据库的强大功能，让它按照我们的要求反馈给我们最合适和最精练的信息。   
**3**  在可能情况下我们应该使用 SQL Server 而不是 Access 。因为 Access 仅仅是基于文件的数据库，多用户性能很差。数据库连接尽量使用 OLEDB 和非 DSN 方式，因为这种连接方式有更好的并发性能。   
  
**4**  避免使用 DAO （ Data Access Objects ）和 RDO （ Remote Data Objects ）数据源。因为他们主要应用在单用户的处理系统里， ADO （ ActiveX Data Objects ）才是为 Web 应用设计的。   
**5**  建立记录集 Rescordset 的时候要清晰合理地设置数据游标 (cursort) 和锁定方式 (locktype) 。   
 因为在不同的方式下 ASP 会以不同的方式操纵数据库，其执行速度也有很大区别，尤其在大数据量的时候。如果你只想遍历数据，那么默认游标（前进、只读）会带来最好的性能。   
**6**  当你引用 ADO 变量的时候，会消耗较多的 CPU 周期。因此，如果在一个 ASP 页面中多次引用数据库的字段变量，一个较好的方式是将字段值先放入本地变量，然后可以直接调用本地变量来计算和显示数据。   
**7**  缓存 ADO Connection 对象也许不是一个好主意。   
 如果一个连接（ Connection ）对象被存储在 Application 对象中而被所有 ASP 页面使用，那么所有页面就会争着使用这个连接。但是如果连接对象被存储在 Session 对象中，就要为每个用户创建一个数据库连接，这就减小了连接池的作用，并且增大了 Web 服务器和数据库服务器的压力。可以用在每个使用 ADO 的 ASP 页创建和释放 ADO 对象来替代缓存数据库连接。因为 IIS 内建了数据库连接池，所以这种方法非常有效，缺点是每个 ASP 页面都需要进行一些创建和释放操作。   
**8** ASP 最强大和主要的用途之一就是对数据库进行操作，在数据库操作中我们要注意：不要任意使用 “SELECT  ＊  ......”  形式的 SQL 查询语句。应该尽量检索你所需要的那些字段。比如一个表中有 10 个字段，但是你只会用到其中的一个字段（ name ），就该使用 “select name from mytable” ，而不是用 “select  ＊  from mytable” 。在字段数比较少的时候，两者的区别可能并不明显，但是当一个表中拥有几十个字段的时候，数据库会多检索很多你并不需要的数据。在这种情况下你最好不要为了节省打字时间或者害怕查找对应字段名称的麻烦，而要老老实实地使用 “select id,name,age... from mytable” 。   
**9**  及时关闭打开的记录集对象以及连接 (Connection) 对象。   
 记录集对象和连接对象耗费系统资源相当大，因此它们的可用数量是有限的。如果你打开了太多的记录集对象以及连接对象而最后却没有关闭它们，可能会出现 ASP 程序刚开始的时候运行速度很快，而多运行几遍就越来越慢的现象，甚至导致服务器死机。请使用如下方法进行关闭：   
MyRecordSet.closeSet MyRecordSet=Nothing  
Set MyConnection=Nothing   
**10**  连接数据库   
 仍然使用 ODBC 系统或者文件 DSN 来连接数据库，或者使用很快的 OLEDB 技术来连接。使用后者，当移动 Web 文件时，不再需要修改配置。   
OLEDB 位于应用程序与 ODBC 层之间。在 ASP 页面中， ADO 就是位于 OLEDB 之上的程序。调用 ADO 时，首先发送给 OLEDB ，然后再发送给 ODBC 层。可以直接连接到 OLEDB 层，这么做后，将提高服务器端的性能。怎么直接连接到 OLEDB 呢？   
 如果使用 SQLServer 7 ，使用下面的代码做为连接字符串：   
strConnString = "DSN='';DRIVER=\{SQL SERVER\};" & \_   
"UID=myuid;PWD=mypwd;" & \_   
"DATABASE=MyDb;SERVER=MyServer;"   
 最重要的参数就是 “DRIVER=” 部分。如果你想绕过 ODBC 而使用 OLEDB 来访问 SQL Server ，使用下面的语法：   
strConnString ="Provider=SQLOLEDB.1;Password=mypassword;" & \_   
"Persist Security Info=True;User ID=myuid;" & \_   
"Initial Catalog=mydbname;" & \_   
"Data Source=myserver;Connect Timeout=15"   
 为什么这很重要   
 现在你可能奇怪为什么学习这种新的连接方法很关键？为什么不使用标准的 DSN 或者系统 DSN 方法？好，根据 Wrox 在他们的 ADO 2.0 程序员参考书籍中所做的测试，如果使用 OLEDB 连接，要比使用 DSN 或者 DSN － less 连接，有以下的性能提高表现：   
 性能比较：   
\----------------------------------------------------------------------  
SQL Access   
 连接时间 : 18 82   
 重复 1 ， 000 个记录的时间： 2900 5400   
OLEDB DSN OLEDB DSN   
 连接时间： 62 99  
 重复 1 ， 000 个记录的时间： 100 950   
\----------------------------------------------------------------------  
 这个结论在 Wrox 的 ADO 2.0 程序员参考发表。时间是以毫秒为单位，重复 1 ， 000 个记录的时间是以服务器油标的方式计算的。

**有一个例子：**

select a. \*, m.amount  
from tableA a,  
(  
select b.fieldD, sum(c.total\_amount) amount  
from tableA b, tableB c  
where b.fieldC = 100 and  
b.fieldA in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF') and  
b.fieldId = c.fieldId  
group by b.fieldD  
) m  
where a.fieldC = 100 and a.fieldD = m.fieldD and  
a.fieldA = 'GG'  
  
  
 这句 sql 当中对同一个表扫描了两次 , 所以效率太低 , 有什么办法可以避免这种写法 ?  
tableA,tableB  是主从表关系。   
 请不要用 sql server  中太特殊的语法，因为要用到 oracle 中。   
 在 oracle 中无人回答。   
  
\------------------------------------------  
  
SQL 语句的写法是根据你的业务要求，改写起来效果不能很明显。   
  
 先分析一下你的 SQL 的执行路径：   
  
1 、   
 首先会分别对 tableA 和 tableB 应用 filter 动作（使用 m 子查询中的 where 条件）。然后进行连接，可能会是 nestloop 或 hash join... 这取决于你   
  
 的两个表数据过滤情况。然后进行汇总（ group by ）输出 m 结果集。   
  
2 、接下来会将 m 结果集与 tableA （外层）过滤后（ a.fieldC = 100 and a.fieldA = 'GG' ）的结果集进行连接，还是有多种连接方式。最后输   
  
 出 a. \*, m.amount  
  
 大致分析了一下执行的路径，就会对你的描述产生疑惑： “ 对同一个表扫描了两次 ” 肯定指的是 tableA 了。但是你没有建立相关的索引吗？如   
  
 果说外层的查询就算建立索引也会通过 rowid 定位到表中，我们权当这是 “ 表扫描 ” ，但是内层的查询应该不会发生产生表扫描（ all table  
  
access ）的情况！应该是索引扫描（ index scan ）才对。根据这一点，我们可以首先考虑建立索引来提高效率。   
  
 可以考虑建立的索引：   
  
create index idx\_1 on tableA(fieldC,fieldA,fieldId,fieldD)  
  
create index idx\_2 on tableB(fieldId,total\_amount)  
  
  
 建立完这两个索引后别忘了重新执行分析，以保证统计值准确。   
  
 建立完这两个索引后，内层的执行计划应该是对 idx\_1 和 idx\_2 进行索引扫描（ index scan ）然后连接输出 m 结果集，再与外层的经过索引扫描（   
  
index scan + rowid to table ）的结果集进行连接。   
  
 如果查询计划不对，请检查你的优化器参数设置，不要使用 rbo 要使用 cbo 。如果还是没有采用请用 /\* index\*/ 提示强制指定 ....  
  
  
 上面的是单纯从索引方面考虑。如果还是不能提高速度，考虑建立实体化视图（物化视图）。可以只将 m 部分进行实体化。如果 tableA 和 tableB  
  
 基本属于静态表，可以考虑将整条语句实体化。

**这里有个非常好的例子并总结了：**

SERVER[数据库][Link 5] 中实现 [快速][Link 6] 的数据提取和数据分页。以下代码说明了我们实例中数据库的 “ 红头文件 ” 一表的部分数据结构：   
  
CREATE[ table][table] \[[db][]o\].\[TGongwen\] ( 　　 \--TGongwen 是红头文件表名   
  
\[Gid\] \[int\] [ide][]NTITY (1, 1) NOT NULL ,  
\-- 本表的 id 号，也是主键   
  
\[title\] \[varchar\] (80) COLLATE Chinese\_PRC\_CI\_AS NULL ,  
\-- 红头文件的标题   
  
\[fariqi\] \[datetime\] NULL ,  
\-- 发布日期   
  
\[neibuYonghu\] \[varchar\] (70) COLLATE Chinese\_PRC\_CI\_AS NULL ,  
\-- 发布 [用户][Link 7]  
  
\[reader\] \[varchar\] (900) COLLATE Chinese\_PRC\_CI\_AS NULL ,  
  
\-- 需要浏览的用户。每个用户中间用分隔符 “,” 分开   
  
) ON \[PRIMARY\] TEXT[image][]\_ON \[PRIMARY\]  
  
GO  
  
 下面，我们来往数据库中添加 1000 万条数据：   
  
declare @i int  
  
set @i=1  
  
while @i<=250000  
  
begin  
  
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5',' 通信科 ',' 通信科 , 办公室 , 王局长 , 刘局长 , 张局长 ,admin, 刑侦支队 , 特勤支队 , 交巡警支队 , 经侦支队 , 户政科 , 治安支队 , 外事科 ',' 这是最先的 25 万条记录 ')  
  
set @i=@i+1  
  
end  
  
GO  
  
declare @i int  
  
set @i=1  
  
while @i<=250000  
  
begin  
  
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16',' 办公室 ',' 办公室 , 通信科 , 王局长 , 刘局长 , 张局长 ,admin, 刑侦支队 , 特勤支队 , 交巡警支队 , 经侦支队 , 户政科 , 外事科 ',' 这是中间的 25 万条记录 ')  
  
set @i=@i+1  
  
end  
  
GO  
  
declare @h int  
  
set @h=1  
  
while @h<=100  
  
begin  
  
declare @i int  
  
set @i=2002  
  
while @i<=[2003][]  
  
begin  
  
declare @j int  
  
set @j=0  
  
while @j<50  
  
begin  
  
declare @k int  
  
set @k=0  
  
while @k<50  
  
begin  
  
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),' 通信科 ',' 办公室 , 通信科 , 王局长 , 刘局长 , 张局长 ,admin, 刑侦支队 , 特勤支队 , 交巡警支队 , 经侦支队 , 户政科 , 外事科 ',' 这是最后的 50 万条记录 ')  
  
set @k=@k+1  
  
end  
  
set @j=@j+1  
  
end  
  
set @i=@i+1  
  
end  
  
set @h=@h+1  
  
end  
  
GO  
  
declare @i int  
  
set @i=1  
  
while @i<=9000000  
  
begin  
  
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5',' 通信科 ',' 通信科 , 办公室 , 王局长 , 刘局长 , 张局长 ,admin, 刑侦支队 , 特勤支队 , 交巡警支队 , 经侦支队 , 户政科 , 治安支队 , 外事科 ',' 这是最后添加的 900 万条记录 ')  
  
set @i=@i+1000000  
  
end  
  
GO  
  
 通过以上语句，我们创建了 25 万条由于 2004 年 2 月 5 日 发布的记录， 25 万条由办公室于 2004 年 9 月 6 日 发布的记录， 2002 年和 2003 年各 100 个 2500 条相同日期、不同分秒的记录（共 50 万条），还有由通信科于 2004 年 5 月 5 日 发布的 900 万条记录，合计 1000 万条。

**一、因情制宜，建立“适当”的[索引][Link 8]**   
 建立 “ 适当 ” 的索引是实现查询 [优化][Link 9] 的首要前提。   
  
 索引（ index ）是除表之外另一重要的、用户定义的 [存储][Link 10] 在物理介质上的数据结构。当根据索引码的值 [搜索][Link 11] 数据时，索引提供了对数据的快速访问。事实上，没有索引 , 数据库也能根据 [select][] 语句成功地检索到结果，但随着表变得越来越大，使用 “ 适当 ” 的索引的效果就越来越明显。注意，在这句话中，我们用了 “ 适当 ” 这个词，这是因为，如果使用索引时不认真考虑其实现过程，索引既可以提高也会破坏数据库的工作 [性能][Link 12] 。   
  
 （一）深入浅出理解索引结构   
  
 实际上，您可以把索引理解为一种特殊的目录。 [微软][Link 13] 的 [SQL SERVER][] 提供了两种索引：聚集索引（ clustered index ，也称聚类索引、簇集索引）和非聚集索引（ nonclustered index ，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：   
  
 其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查 “ 安 ” 字，就会很自然地翻开字典的前几页，因为 “ 安 ” 的拼音是 “an” ，而按照拼音排序汉字的字典是以英文字母 “a” 开头并以 “z” 结尾的，那么 “ 安 ” 字就自然地排在字典的前部。如果您翻完了所有以 “a” 开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查 “ 张 ” 字，那您也会将您的字典翻到最后部分，因为 “ 张 ” 的拼音是 “zhang” 。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。   
  
 我们把这种正文内容本身就是一种按照一定规则排列的目录称为 “ 聚集索引 ” 。   
  
 如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据 “ 偏旁部首 ” 查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合 “ 部首目录 ” 和 “ 检字表 ” 而查到的字的排序并不是真正的正文的排序方法，比如您查 “ 张 ” 字，我们可以看到在查部首之后的检字表中 “ 张 ” 的页码是 672 页，检字表中 “ 张 ” 的上面是 “ 驰 ” 字，但页码却是 63 页， “ 张 ” 的下面是 “ 弩 ” 字，页面是 390 页。很显然，这些字并不是真正的分别位于 “ 张 ” 字的上下方，现在您看到的连续的 “ 驰、张、弩 ” 三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。   
  
 我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为 “ 非聚集索引 ” 。   
  
 通过以上例子，我们可以理解到什么是 “ 聚集索引 ” 和 “ 非聚集索引 ” 。   
  
 进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。   
  
 （二）何时使用聚集索引或非聚集索引   
  
 下面的表总结了何时使用聚集索引或非聚集索引（很重要）。   
  
 动作描述   
 使用聚集索引   
 使用非聚集索引   
  
 列经常被分组排序   
 应   
 应   
  
 返回某范围内的数据   
 应   
 不应   
  
 一个或极少不同值   
 不应   
 不应   
  
 小数目的不同值   
 应   
 不应   
  
 大数目的不同值   
 不应   
 应   
  
 频繁 [更新][Link 14] 的列   
 不应   
 应   
  
 外键列   
 应   
 应   
  
 主键列   
 应   
 应   
  
 频繁修改索引列   
 不应   
 应   
  
 事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询 2004 年 1 月 1 日 至 2004 年 10 月 1 日 之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。   
  
 （三）结合实际，谈索引使用的误区   
  
 理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。   
  
1 、主键就是聚集索引   
  
 这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然 SQL SERVER 默认是在主键上建立聚集索引的。   
  
 通常，我们会在每个表中都建立一个 ID 列，以区分每条数据，并且这个 ID 列是自动增大的，步长一般为 1 。我们的这个办公自动化的实例中的列 Gid 就是如此。此时，如果我们将这个列设为主键， SQL SERVER 会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照 ID 进行物理排序，但笔者认为这样做意义不大。   
  
 显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。   
  
 从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为 ID 号是自动生成的，我们并不知道每条记录的 ID 号，所以我们很难在实践中用 ID 号来进行查询。这就使让 ID 号这个主键作为聚集索引成为一种 [资源][Link 15] 浪费。其次，让每个 ID 号都不同的字段作为聚集索引也不符合 “ 大数目的不同值情况下不应建立聚合索引 ” 规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。   
  
 在办公自动化 [系统][Link 16] 中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是 “ 日期 ” 还有用户本身的 “ 用户名 ” 。   
  
 通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的 where 语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户 1 个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近 3 个月来未阅览的文件，通过 “ 日期 ” 这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的 2 年，那么您的首页显示速度理论上将是原来速度 8 倍，甚至更快。   
  
 在这里之所以提到 “ 理论上 ” 三字，是因为如果您的聚集索引还是盲目地建在 ID 这个主键上时，您的查询速度是没有这么高的，即使您在 “ 日期 ” 这个字段上建立的索引（非聚合索引）。下面我们就来看一下在 1000 万条数据量的情况下各种查询的速度表现（ 3 个月内的数据为 25 万条）：   
  
 （ 1 ）仅在主键上建立聚集索引，并且不划分时间段：   
  
Select gid,fariqi,neibuyonghu,title from tgongwen  
  
 用时： 128470 毫秒（即： 128 秒）   
  
 （ 2 ）在主键上建立聚集索引，在 fariq 上建立非聚集索引：   
  
select gid,fariqi,neibuyonghu,title from Tgongwen  
  
where fariqi> dateadd(day,-90,getdate())  
  
 用时： 53763 毫秒（ 54 秒）   
  
 （ 3 ）将聚合索引建立在日期列（ fariqi ）上：   
  
select gid,fariqi,neibuyonghu,title from Tgongwen  
  
where fariqi> dateadd(day,-90,getdate())  
  
 用时： 2423 毫秒（ 2 秒）   
  
 虽然每条语句提取出来的都是 25 万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有 1000 万容量的话，把主键建立在 ID 列上，就像以上的第 1 、 2 种情况，在 [网页][Link 17] 上的表现就是超时，根本就无法显示。这也是我摒弃 ID 列作为聚集索引的一个最重要的因素。   
  
 得出以上速度的方法是：在各个 select 语句前加： declare @d datetime  
  
set @d=getdate()  
  
 并在 select 语句后加：   
  
select \[ 语句 [执行][Link 18] 花费时间 ( 毫秒 )\]=datediff(ms,@d,getdate())  
  
2 、只要建立索引就能显著提高查询速度   
  
 事实上，我们可以发现上面的例子中，第 2 、 3 条语句完全相同，且建立索引的字段也相同；不同的仅是前者在 fariqi 字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。   
  
 从建表的语句中，我们可以看到这个有着 1000 万数据的表中 fariqi 字段有 5003 个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的： “ 既不能绝大多数都相同，又不能只有极少数相同 ” 的规则。由此看来，我们建立 “ 适当 ” 的聚合索引对于我们提高查询速度是非常重要的。   
  
3 、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度   
  
 上面已经谈到：在进行数据查询时都离不开字段的是 “ 日期 ” 还有用户本身的 “ 用户名 ” 。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引（ compound index ）。   
  
 很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是 25 万条数据）：（日期列 fariqi 首先排在复合聚集索引的起始列，用户名 neibuyonghu 排在后列）   
  
 （ 1 ） select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'  
  
 查询速度： 2513 毫秒   
  
 （ 2 ） select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu=' 办公室 '  
  
 查询速度： 2516 毫秒   
  
 （ 3 ） select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=' 办公室 '  
  
 查询速度： 60280 毫秒   
  
 从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句 1 、 2 的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成 “ 索引覆盖 ” ，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。   
  
 （四）其他书上没有的索引使用 [经验][Link 19] 总结   
  
1 、用聚合索引比用不是聚合索引的主键速度快   
  
 下面是实例语句：（都是提取 25 万条数据）   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'  
  
 使用时间： 3326 毫秒   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000  
  
 使用时间： 4470 毫秒   
  
 这里，用聚合索引比用不是聚合索引的主键速度快了近 1/4 。   
  
2 、用聚合索引比用一般的主键作 order by 时速度快，特别是在小数据量情况下   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi  
  
 用时： 12936  
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid  
  
 用时： 18843  
  
 这里，用聚合索引比用一般的主键作 order by 时，速度快了 3/10 。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如 10 万以上，则二者的速度差别不明显。   
  
3 、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'  
  
 用时： 6343 毫秒（提取 100 万条）   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'  
  
 用时： 3170 毫秒（提取 50 万条）   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'  
  
 用时： 3326 毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'  
  
 用时： 3280 毫秒

4  、日期列不会因为有分秒的输入而减慢查询速度   
  
 下面的例子中，共有 100 万条数据， 2004 年 1 月 1 日 以后的数据有 50 万条，但只有两个不同的日期，日期精确到日；之前有数据 50 万条，有 5000 个不同的日期，日期精确到秒。   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi  
  
 用时： 6390 毫秒   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi  
  
 用时： 6453 毫秒   
  
 （五）其他注意事项   
  
“ 水可载舟，亦可覆舟 ” ，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。   
  
 所以说，我们要建立一个 “ 适当 ” 的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。   
  
 当然，在实践中，作为一个尽职的数据库管理员，您还要多测试一些方案，找出哪种方案 [效率][Link 20] 最高、最为有效。   
  
 **二、改善SQL语句**   
 很多人不知道 SQL 语句在 SQL SERVER 中是如何执行的，他们担心自己所写的 SQL 语句会被 SQL SERVER 误解。比如：   
  
select \* from table1 where name='zhangsan' and tID > 10000  
  
 和执行 :  
  
select \* from table1 where tID > 10000 and name='zhangsan'  
  
 一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果 tID 是一个聚合索引，那么后一句仅仅从表的 10000 条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个 name='zhangsan' 的，而后再根据限制条件条件 tID>10000 来提出查询结果。   
  
 事实上，这样的担心是不必要的。 SQL SERVER 中有一个 “ 查询分析优化器 ” ，它可以计算出 where 子句中的搜索条件并确定哪个索引能缩小表扫描的搜索 [空间][Link 21] ，也就是说，它能实现自动优化。   
  
 虽然查询优化器可以根据 where 子句自动的进行查询优化，但大家仍然有必要了解一下 “ 查询优化器 ” 的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。   
  
 在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（ SARG ），那么就称之为可优化的，并且可以利用索引快速获得所需数据。   
  
SARG 的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的 AND 连接。形式如下：   
  
 列名 操作符  < 常数 或 变量 >  
  
 或   
  
< 常数 或 变量 >  操作符列名   
  
 列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：   
  
Name=’ 张三 ’  
  
 价格 >5000  
  
5000< 价格   
  
Name=’ 张三 ’ and  价格 >5000  
  
 如果一个 [表达式][Link 22] 不能满足 SARG 的形式，那它就无法限制搜索的范围了，也就是 SQL SERVER 必须对每一行都判断它是否满足 WHERE 子句中的所有条件。所以一个索引对于不满足 SARG 形式的表达式来说是无用的。   
  
 介绍完 SARG 后，我们来总结一下使用 SARG 以及在实践中遇到的和某些资料上结论不同的经验：   
  
1 、 Like 语句是否属于 SARG 取决于所使用的通配符的类型   
  
 如： name like ‘ 张 %’  ，这就属于 SARG  
  
 而： name like ‘% 张 ’ , 就不属于 SARG 。   
  
 原因是通配符 % 在字符串的开通使得索引无法使用。   
  
2 、 or  会引起全表扫描   
  
Name=’ 张三 ’ and  价格 >5000  符号 SARG ，而： Name=’ 张三 ’ or  价格 >5000  则不符合 SARG 。使用 or 会引起全表扫描。   
  
3 、非操作符、 [函数][Link 23] 引起的不满足 SARG 形式的语句   
  
 不满足 SARG 形式的语句最典型的情况就是包括非操作符的语句，如： NOT 、 != 、 <> 、 !< 、 !> 、 NOT EXISTS 、 NOT IN 、 NOT LIKE 等，另外还有函数。下面就是几个不满足 SARG 形式的例子：   
  
ABS( 价格 )<5000  
  
Name like ‘% 三 ’  
  
 有些表达式，如：   
  
WHERE  价格 \*2>5000  
  
SQL SERVER 也会认为是 SARG ， SQL SERVER 会将此式转化为：   
  
WHERE  价格 >2500/2  
  
 但我们不推荐这样使用，因为有时 SQL SERVER 不能保证这种转化与原始表达式是完全等价的。   
  
4 、 IN  的作用相当与 OR  
  
 语句：   
  
Select \* from table1 where tid in (2,3)  
  
 和   
  
Select \* from table1 where tid=2 or tid=3  
  
 是一样的，都会引起全表扫描，如果 tid 上有索引，其索引也会失效。   
  
5 、尽量少用 NOT  
  
6 、 exists  和  in  的执行效率是一样的   
  
 很多资料上都显示说， exists 要比 in 的执行效率要高，同时应尽可能的用 not exists 来代替 not in 。但事实上，我试验了一下，发现二者无论是前面带不带 not ，二者之间的执行效率都是一样的。因为涉及子查询，我们试验这次用 SQL SERVER 自带的 pubs 数据库。运行前我们可以把 SQL SERVER 的 statistics I/O 状态打开。   
  
 （ 1 ） select[ title][title],price from titles where title\_id in (select title\_id from sales where qty>30)  
  
 该句的执行结果为：   
  
 表  'sales' 。扫描计数  18 ，逻辑读  56  次，物理读  0  次，预读  0  次。   
  
 表  'titles' 。扫描计数  1 ，逻辑读  2  次，物理读  0  次，预读  0  次。   
  
 （ 2 ） select title,price from titles where exists (select \* from sales where sales.title\_id=titles.title\_id and qty>30)  
  
 第二句的执行结果为：   
  
 表  'sales' 。扫描计数  18 ，逻辑读  56  次，物理读  0  次，预读  0  次。   
  
 表  'titles' 。扫描计数  1 ，逻辑读  2  次，物理读  0  次，预读  0  次。   
  
 我们从此可以看到用 exists 和用 in 的执行效率是一样的。   
  
7 、用函数 charindex() 和前面加通配符 % 的 LIKE 执行效率一样   
  
 前面，我们谈到，如果在 LIKE 前面加上通配符 % ，那么将会引起全表扫描，所以其执行效率是低下的。但有的资料介绍说，用函数 charindex() 来代替 LIKE 速度会有大的提升，经我试验，发现这种说明也是错误的：   
  
select gid,title,fariqi,reader from tgongwen where charindex(' 刑侦支队 ',reader)>0 and fariqi>'2004-5-5'  
  
 用时： 7 秒，另外：扫描计数  4 ，逻辑读  7155  次，物理读  0  次，预读  0  次。   
  
select gid,title,fariqi,reader from tgongwen where reader like '%' + ' 刑侦支队 ' + '%' and fariqi>'2004-5-5'  
  
 用时： 7 秒，另外：扫描计数  4 ，逻辑读  7155  次，物理读  0  次，预读  0  次。   
  
8 、 union 并不绝对比 or 的执行效率高   
  
 我们前面已经谈到了在 where 子句中使用 or 会引起全表扫描，一般的，我所见过的资料都是推荐这里用 union 来代替 or 。事实证明，这种说法对于大部分都是适用的。   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000  
  
 用时： 68 秒。扫描计数  1 ，逻辑读  404008  次，物理读  283  次，预读  392163  次。   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'  
  
union  
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000  
  
 用时： 9 秒。扫描计数  8 ，逻辑读  67489  次，物理读  216  次，预读  7499  次。   
  
 看来，用 union 在通常情况下比用 or 的效率要高的多。   
  
 但经过试验，笔者发现如果 or 两边的查询列是一样的话，那么用 union 则反倒和用 or 的执行速度差很多，虽然这里 union 扫描的是索引，而 or 扫描的是全表。   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'  
  
 用时： 6423 毫秒。扫描计数  2 ，逻辑读  14726  次，物理读  1  次，预读  7176  次。   
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'  
  
union  
  
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where 　 fariqi='2004-2-5'  
  
 用时： 11640 毫秒。扫描计数  8 ，逻辑读  14806  次，物理读  108  次，预读  1144  次。   
  
9 、字段提取要按照 “ 需多少、提多少 ” 的原则，避免 “select \*”  
  
 我们来做一个试验：   
  
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc  
  
 用时： 4673 毫秒   
  
select top 10000 gid,fariqi,title from tgongwen order by gid desc  
  
 用时： 1376 毫秒   
  
select top 10000 gid,fariqi from tgongwen order by gid desc  
  
 用时： 80 毫秒   
  
 由此看来，我们每少提取一个字段，数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。   
  
10 、 count(\*) 不比 count( 字段 ) 慢   
  
 某些资料上说：用 \* 会统计所有列，显然要比一个世界的列名效率低。这种说法其实是没有根据的。我们来看：   
  
select count(\*) from Tgongwen  
  
 用时： 1500 毫秒   
  
select count(gid) from Tgongwen  
  
 用时： 1483 毫秒   
  
select count(fariqi) from Tgongwen  
  
 用时： 3140 毫秒   
  
select count(title) from Tgongwen  
  
 用时： 52050 毫秒   
  
 从以上可以看出，如果用 count(\*) 和用 count( 主键 ) 的速度是相当的，而 count(\*) 却比其他任何除主键以外的字段汇总速度要快，而且字段越长，汇总的速度就越慢。我想，如果用 count(\*) ，  SQL SERVER 可能会自动查找最小字段来汇总的。当然，如果您直接写 count( 主键 ) 将会来的更直接些。   
  
11 、 order by 按聚集索引列排序效率最高   
  
 我们来看：（ gid 是主键， fariqi 是聚合索引列）   
  
select top 10000 gid,fariqi,reader,title from tgongwen  
  
 用时： 196  毫秒。 扫描计数  1 ，逻辑读  289  次，物理读  1  次，预读  1527  次。   
  
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc  
  
 用时： 4720 毫秒。 扫描计数  1 ，逻辑读  41956  次，物理读  0  次，预读  1287  次。   
  
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc  
  
 用时： 4736 毫秒。 扫描计数  1 ，逻辑读  55350  次，物理读  10  次，预读  775  次。   
  
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc  
  
 用时： 173 毫秒。 扫描计数  1 ，逻辑读  290  次，物理读  0  次，预读  0  次。   
  
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc  
  
 用时： 156 毫秒。 扫描计数  1 ，逻辑读  289  次，物理读  0  次，预读  0  次。   
  
 从以上我们可以看出，不排序的速度以及逻辑读次数都是和 “order by  聚集索引列 ”  的速度是相当的，但这些都比 “order by  非聚集索引列 ” 的查询速度是快得多的。   
  
 同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。   
  
12 、高效的 TOP  
  
 事实上，在查询和提取超大容量的数据集时，影响数据库响应时间的最大因素不是数据查找，而是物理的 I/0 操作。如：   
  
select top 10 \* from (  
  
select top 10000 gid,fariqi,title from tgongwen  
  
where neibuyonghu=' 办公室 '  
  
order by gid desc) as a  
  
order by gid asc  
  
 这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是 10000 条记录，而整条语句仅返回 10 条语句，所以影响数据库响应时间最大的因素是物理 I/O 操作。而限制物理 I/O 操作此处的最有效方法之一就是使用 TOP 关键词了。 TOP 关键词是 SQL SERVER 中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用，发现 TOP 确实很好用，效率也很高。但这个词在另外一个大型数据库 [ORACLE][] 中却没有，这不能说不是一个遗憾，虽然在 ORACLE 中可以用其他方法（如： rownumber ）来解决。在以后的关于 “ 实现千万级数据的分页显示 [存储过程][Link 24]” 的讨论中，我们就将用到 TOP 这个关键词。   
  
 到此为止，我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然，我们介绍的这些方法都是 “ 软 ” 方法，在实践中，我们还要考虑各种 “ 硬 ” 因素，如： [网络][Link 25] 性能、 [服务器][Link 26] 的性能、 [操作系统][Link 27] 的性能，甚至 [网卡][Link 28] 、 [交换][Link 29] 机等。

**三、实现小数据量和海量数据的通用分页显示存储过程**   
 建立一个 [web][] 应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是 :ADO  纪录集分页法，也就是利用 ADO 自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据量的情形，因为游标本身有缺点：游标是存放在 [内存][Link 30] 中，很费内存。游标一建立，就将相关的记录锁住，直到取消游标。游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使 [程序][Link 31] 进入一个漫长的等待甚至死机。   
  
 更重要的是，对于非常大的数据模型而言，分页检索时，如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据，而非检索所有的数据，然后单步执行当前行。   
  
 最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是 “ 俄罗斯存储过程 ” 。这个存储过程用了游标，由于游标的局限性，所以这个方法并没有得到大家的普遍认可。   
  
 后来，网上有人改造了此存储过程，下面的存储过程就是结合我们的办公自动化实例写的分页存储过程：   
  
CREATE procedure pagination1  
  
(@pagesize int, 　 \-- 页面大小，如每页存储 20 条记录   
  
@pageindex int 　  \-- 当前页码   
  
)  
  
as  
  
set nocount on  
  
begin  
  
declare @indextable table(id int identity(1,1),nid int) 　 \-- 定义表变量   
  
declare @PageLowerBound int 　 \-- 定义此页的底码   
  
declare @PageUpperBound int 　 \-- 定义此页的顶码   
  
set @PageLowerBound=(@pageindex-1)\*@pagesize  
  
set @PageUpperBound=@PageLowerBound+@pagesize  
  
set rowcount @PageUpperBound  
  
insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc  
  
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid  
  
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id  
  
end  
  
set nocount off  
  
 以上存储过程运用了 SQL SERVER 的最新技术 ―― 表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然，在这个过程中，您也可以把其中的表变量写成临时表： CREATE TABLE \#Temp 。但很明显，在 SQL SERVER 中，用临时表是没有用表变量快的。所以笔者刚开始使用这个存储过程时，感觉非常的不错，速度也比原来的 ADO 的好。但后来，我又发现了比此方法更好的方法。   
  
 笔者曾在网上看到了一篇小短文《从数据表中取出第 n 条到第 m 条的记录的方法》，全文如下：   
  
 从 publish  表中取出第  n  条到第  m  条的记录：   
SELECT TOP m-n+1 \*  
FROM publish  
WHERE (id NOT IN  
(SELECT TOP n-1 id  
FROM publish))  
  
id  为 publish  表的关键字   
  
 我当时看到这篇文章的时候，真的是精神为之一振，觉得思路非常得好。等到后来，我在作办公自动化系统（ [ASP][].net+ [C\#][C] ＋ SQL SERVER ）的时候，忽然想起了这篇文章，我想如果把这个语句改造一下，这就可能是一个非常好的分页存储过程。于是我就满网上找这篇文章，没想到，文章还没找到，却找到了一篇根据此语句写的一个分页存储过程，这个存储过程也是目前较为流行的一种分页存储过程，我很后悔没有争先把这段文字改造成存储过程：   
  
CREATE PROCEDURE pagination2  
(  
@SQL nVARCHAR(4000), 　　 \-- 不带排序语句的 SQL 语句   
@Page int, 　　　　　　　 \-- 页码   
@RecsPerPage int, 　　　  \-- 每页容纳的记录数   
@ID VARCHAR(255), 　　　  \-- 需要排序的不重复的 ID 号   
@Sort VARCHAR(255) 　　　 \-- 排序字段及规则   
)  
AS  
  
DECLARE @Str nVARCHAR(4000)  
  
SET @Str='SELECT 　  TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' \* FROM ('+@SQL+') T WHERE T.'+@ID+'NOT IN  
(SELECT 　  TOP '+CAST((@RecsPerPage\*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort  
  
PRINT @Str  
  
EXEC sp\_ExecuteSql @Str  
GO  
  
 其实，以上语句可以简化为：   
  
SELECT TOP  页大小  \*  
  
FROM Table1  
  
WHERE (ID NOT IN  
  
(SELECT TOP  页大小 \* 页数  id  
  
FROM  表   
  
ORDER BY id))  
  
ORDER BY ID  
  
 但这个存储过程有一个致命的缺点，就是它含有 NOT IN 字样。虽然我可以把它改造为：   
  
SELECT TOP  页大小  \*  
  
FROM Table1  
  
WHERE not exists  
  
(select \* from (select top ( 页大小 \* 页数 ) \* from table1 order by id) b where b.id=a.id )  
  
order by id  
  
 即，用 not exists 来代替 not in ，但我们前面已经谈过了，二者的执行效率实际上是没有区别的。   
  
 既便如此，用 TOP  结合 NOT IN 的这个方法还是比用游标要来得快一些。   
  
 虽然用 not exists 并不能挽救上个存储过程的效率，但使用 SQL SERVER 中的 TOP 关键字却是一个非常明智的选择。因为分页优化的最终目的就是避免产生过大的记录集，而我们在前面也已经提到了 TOP 的优势，通过 TOP  即可实现对数据量的控制。   
  
 在分页算法中，影响我们查询速度的关键因素有两点： TOP 和 NOT IN 。 TOP 可以提高我们的查询速度，而 NOT IN 会减慢我们的查询速度，所以要提高我们整个分页算法的速度，就要彻底改造 NOT IN ，同其他方法来替代它。   
  
 我们知道，几乎任何字段，我们都可以通过 max( 字段 ) 或 min( 字段 ) 来提取某个字段中的最大或最小值，所以如果这个字段不重复，那么就可以利用这些不重复的字段的 max 或 min 作为分水岭，使其成为分页算法中分开每页的参照物。在这里，我们可以用操作符 “>” 或 “<” 号来完成这个使命，使查询语句符合 SARG 形式。如：   
  
Select top 10 \* from table1 where id>200  
  
 于是就有了如下分页方案：   
  
select top  页大小  \*  
  
from table1  
  
where id>  
  
(select max (id) from  
  
(select top (( 页码 \-1)\* 页大小 ) id from table1 order by id) as T  
  
)  
  
order by id  
  
 在选择即不重复值，又容易分辨大小的列时，我们通常会选择主键。下表列出了笔者用有着 1000 万数据的办公自动化系统中的表，在以 GID （ GID 是主键，但并不是聚集索引。）为排序列、提取 gid,fariqi,title 字段，分别以第 1 、 10 、 100 、 500 、 1000 、 1 万、 10 万、 25 万、 50 万页为例，测试以上三种分页方案的执行速度：（单位：毫秒）   
  
 页　码   
 方案 1  
 方案 2  
 方案 3  
  
1  
60  
30  
76  
  
10  
46  
16  
63  
  
100  
1076  
720  
130  
  
500  
540  
12943  
83  
  
1000  
17110  
470  
250  
  
1 万   
24796  
4500  
140  
  
10 万   
38326  
42283  
1553  
  
25 万   
28140  
128720  
2330  
  
50 万   
121686  
127846  
7168  
  
 从上表中，我们可以看出，三种存储过程在执行 100 页以下的分页命令时，都是可以信任的，速度都很好。但第一种方案在执行分页 1000 页以上后，速度就降了下来。第二种方案大约是在执行分页 1 万页以上后速度开始降了下来。而第三种方案却始终没有大的降势，后劲仍然很足。   
  
 在确定了第三种分页方案后，我们可以据此写一个存储过程。大家知道 SQL SERVER 的存储过程是事先编译好的 SQL 语句，它的执行效率要比通过 WEB 页面传来的 SQL 语句的执行效率要高。下面的存储过程不仅含有分页方案，还会根据页面传来的参数来确定是否进行数据总数统计。   
  
\--  获取指定页的数据   
  
CREATE PROCEDURE pagination3  
  
@tblName 　  varchar(255), 　　　  \--  表名   
  
@strGetFields varchar(1000) = '\*', 　 \--  需要返回的列   
  
@fldName varchar(255)='', 　　　 \--  排序的字段名   
  
@PageSize 　  int = 10, 　　　　　 \--  页尺寸   
  
@PageIndex 　 int = 1, 　　　　　  \--  页码   
  
@doCount 　 bit = 0, 　  \--  返回记录总数 ,  非  0  值则返回   
  
@OrderType bit = 0, 　 \--  设置排序类型 ,  非  0  值则降序   
  
@strWhere 　 varchar(1500) = '' 　 \--  查询条件  ( 注意 :  不要加  where)  
  
AS  
  
declare @strSQL 　  varchar(5000) 　　　  \--  主语句   
  
declare @strTmp 　  varchar(110) 　　　　 \--  临时变量   
  
declare @strOrder varchar(400) 　　　　 \--  排序类型   
  
if @doCount != 0  
  
begin  
  
if @strWhere !=''  
  
set @strSQL = "select count(\*) as Total from \[" + @tblName + "\] where "+@strWhere  
  
else  
  
set @strSQL = "select count(\*) as Total from \[" + @tblName + "\]"  
  
end  
  
\-- 以上代码的意思是如果 @doCount 传递过来的不是 0 ，就执行总数统计。以下的所有代码都是 @doCount 为 0 的情况   
  
else  
  
begin  
  
if @OrderType != 0  
  
begin  
  
set @strTmp = "<(select min"  
  
set @strOrder = " order by \[" + @fldName +"\] desc"  
  
\-- 如果 @OrderType 不是 0 ，就执行降序，这句很重要！   
  
end  
  
else  
  
begin  
  
set @strTmp = ">(select max"  
  
set @strOrder = " order by \[" + @fldName +"\] asc"  
  
end  
  
if @PageIndex = 1  
  
begin  
  
if @strWhere != ''  
  
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " 　 from \[" + @tblName + "\] where " + @strWhere + " " + @strOrder  
  
else  
  
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " 　 from \["+ @tblName + "\] "+ @strOrder  
  
\-- 如果是第一页就执行以上代码，这样会加快执行速度   
  
end  
  
else  
  
begin  
  
\-- 以下代码赋予了 @strSQL 以真正执行的 SQL 代码   
  
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " 　 from \["  
  
\+ @tblName + "\] where \[" + @fldName + "\]" + @strTmp + "(\["+ @fldName + "\]) from (select top " + str((@PageIndex-1)\*@PageSize) + " \["+ @fldName + "\] from \[" + @tblName + "\]" + @strOrder + ") as tblTmp)"+ @strOrder  
  
if @strWhere != ''  
  
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " 　 from \["  
  
\+ @tblName + "\] where \[" + @fldName + "\]" + @strTmp + "(\["  
  
\+ @fldName + "\]) from (select top " + str((@PageIndex-1)\*@PageSize) + " \["  
  
\+ @fldName + "\] from \[" + @tblName + "\] where " + @strWhere + " "  
  
\+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder  
  
end  
  
end  
  
exec (@strSQL)  
  
GO  
  
 上面的这个存储过程是一个通用的存储过程，其注释已写在其中了。   
  
 在大数据量的情况下，特别是在查询最后几页的时候，查询时间一般不会超过 9 秒；而用其他存储过程，在实践中就会导致超时，所以这个存储过程非常适用于大容量数据库的查询。   
  
 笔者希望能够通过对以上存储过程的解析，能给大家带来一定的启示，并给工作带来一定的效率提升，同时希望同行提出更优秀的实时数据分页算法。   
  
 **四、聚集索引的重要性和如何选择聚集索引**   
 在上一节的标题中，笔者写的是：实现小数据量和海量数据的通用分页显示存储过程。这是因为在将本存储过程应用于 “ 办公自动化 ” 系统的实践中时，笔者发现这第三种存储过程在小数据量的情况下，有如下现象：   
  
1 、分页速度一般维持在 1 秒和 3 秒之间。   
  
2 、在查询最后一页时，速度一般为 5 秒至 8 秒，哪怕分页总数只有 3 页或 30 万页。   
  
 虽然在超大容量情况下，这个分页的实现过程是很快的，但在分前几页时，这个 1 － 3 秒的速度比起第一种甚至没有经过优化的分页方法速度还要慢，借用户的话说就是 “ 还没有 ACCESS 数据库速度快 ” ，这个认识足以导致用户放弃使用您开发的系统。   
  
 笔者就此分析了一下，原来产生这种现象的症结是如此的简单，但又如此的重要：排序的字段不是聚集索引！   
  
 本篇文章的题目是： “ 查询优化及分页算法方案 ” 。笔者只所以把 “ 查询优化 ” 和 “ 分页算法 ” 这两个联系不是很大的论题放在一起，就是因为二者都需要一个非常重要的东西 ―― 聚集索引。   
  
 在前面的讨论中我们已经提到了，聚集索引有两个最大的优势：   
  
1 、以最快的速度缩小查询范围。   
  
2 、以最快的速度进行字段排序。   
  
 第 1 条多用在查询优化时，而第 2 条多用在进行分页时的数据排序。   
  
 而聚集索引在每个表内又只能建立一个，这使得聚集索引显得更加的重要。聚集索引的挑选可以说是实现 “ 查询优化 ” 和 “ 高效分页 ” 的最关键因素。   
  
 但要既使聚集索引列既符合查询列的需要，又符合排序列的需要，这通常是一个矛盾。   
  
 笔者前面 “ 索引 ” 的讨论中，将 fariqi ，即用户发文日期作为了聚集索引的起始列，日期的精确度为 “ 日 ” 。这种作法的优点，前面已经提到了，在进行划时间段的快速查询中，比用 ID 主键列有很大的优势。   
  
 但在分页时，由于这个聚集索引列存在着重复记录，所以无法使用 max 或 min 来最为分页的参照物，进而无法实现更为高效的排序。而如果将 ID 主键列作为聚集索引，那么聚集索引除了用以排序之外，没有任何用处，实际上是浪费了聚集索引这个宝贵的资源。   
  
 为解决这个矛盾，笔者后来又添加了一个日期列，其默认值为 getdate() 。用户在写入记录时，这个列自动写入当时的时间，时间精确到毫秒。即使这样，为了避免可能性很小的重合，还要在此列上创建 UNIQUE[约束][Link 32] 。将此日期列作为聚集索引列。   
  
 有了这个时间型聚集索引列之后，用户就既可以用这个列查找用户在插入数据时的某个时间段的查询，又可以作为唯一列来实现 max 或 min ，成为分页算法的参照物。   
  
 经过这样的优化，笔者发现，无论是大数据量的情况下还是小数据量的情况下，分页速度一般都是几十毫秒，甚至 0 毫秒。而用日期段缩小范围的查询速度比原来也没有任何迟钝。   
  
 聚集索引是如此的重要和珍贵，所以笔者总结了一下，一定要将聚集索引建立在：   
  
1 、您最频繁使用的、用以缩小查询范围的字段上；   
  
2 、您最频繁使用的、需要排序的字段上。

**应用程序设计**

应用程序设计在决定使用  Microsoft® SQL Server™ 2000  的系统的性能方面起关键作用。将客户端视为控制实体而非数据库服务器。客户端确定查询类型、何时提交查询以及如何处理查询结果。这反过来对服务器上的锁类型和持续时间、 I/O  活动量以及处理  (CPU)  负荷等产生主要影响，并由此影响总体性能的优劣。

正因为如此，在应用程序的设计阶段做出正确决策十分重要。然而，即使在使用总控应用程序时（这种情况下似乎不可能更改客户端应用程序）出现性能问题，也不会改变影响性能的根本因素：客户端具有支配作用，如果不更改客户端则许多性能问题都无法解决。设计优秀的应用程序允许  SQL Server  支持成千上万的并发用户。反之，设计差的应用程序会防碍即使是最强大的服务器平台处理少数用户的请求。

客户端应用程序的设计准则包括：

·                      消除过多的网络流量。

客户端和  SQL Server  之间的网络往返通常是数据库应用程序性能较差的首要原因，甚至超过了服务器和客户端之间传送的数据量这一因素的影响。网络往返描述在客户端应用程序和  SQL Server  之间为每个批处理和结果集发送的会话流量。通过使用存储过程，可以将网络往返减到最小。例如，如果应用程序根据从  SQL Server  收到的数据值采取不同的操作，只要可能就应直接在存储过程中做出决定，从而消除过多的网络流量。

如果存储过程中有多个语句，则默认情况下， SQL Server  在每个语句完成时给客户端应用程序发送一条消息，详细说明每个语句所影响的行数。大多数应用程序不需要这些消息。如果确信应用程序不需要它们，可以禁用这些消息，以提高慢速网络的性能。请使用  SET NOCOUNT  会话设置为应用程序禁用这些消息。有关更多信息，请参见 SET NOCOUNT 。

·                      使用小结果集。

检索没必要大的结果集（如包含上千行）并在客户端浏览将增加  CPU  和网络  I/O  的负载，使应用程序的远程使用能力降低并限制多用户可伸缩性。最好将应用程序设计为提示用户输入足够的信息，以便查询提交后生成大小适中的结果集。有关更多信息，请参见 [使用高效数据检索优化应用程序性能][Link 33] 。

可帮助实现上述目标的应用程序设计技术包括：在生成查询时对通配符进行控制，强制某些输入字段，不允许特殊查询，以及使用  TOP 、 PERCENT  或  SET ROWCOUNT  等  Transact-SQL  语句限制查询返回的行数。有关更多信息，请参见 使用 TOP 和 PERCENT 限制结果集 和 SET ROWCOUNT 。

·                      允许在用户需要重新控制应用程序时取消正在执行的查询。

应用程序决不应强迫用户重新启动客户机以取消查询。无视这一点将导致无法解决的性能问题。如果应用程序取消查询（例如使用开放式数据库连接  (ODBC) **sqlcancel** 函数取消查询），应对事务级别予以适当的考虑。例如，取消查询并不会提交或回滚用户定义的事务。取消查询后，所有在事务内获取的锁都将保留。因此，在取消查询后始终要提交或回滚事务。同样的情况也适用于可用于取消查询的  DB-Library  和其它应用程序接口  (API) 。

·                      始终实现查询或锁定超时。

不要让查询无限期运行。调用适当的  API  以设置查询超时。例如，使用  ODBC **SQLSetStmtOption** 函数。

有关设置查询超时的更多信息，请参见  ODBC API  文档。

有关设置锁定超时的更多信息，请参见 自定义锁超时 。

·                      不要使用不允许显式控制发送到  SQL Server  的  SQL  语句的应用程序开发工具。

如果工具基于更高级的对象透明地生成  Transact-SQL  语句，而且不提供诸如查询取消、查询超时和完全事务控制等关键功能，则不要使用这类工具。如果应用程序生成透明的  SQL  语句，通常不可能维护好的性能或解决性能问题，因为在这种情况下不允许对事务和锁定问题进行显式控制，而这一点对性能状况至关重要。

·                      不要将决策支持和联机事务处理  (OLTP)  查询混在一起。有关更多信息，请参见 联机事务处理与决策支持 。

·                      只在必要时才使用游标。

游标是关系数据库中的有用工具，但使用游标完成任务始终比使用面向集合的  SQL  语句花费多。

当使用面向集合的  SQL  语句时，客户端应用程序让服务器更新满足指定条件的记录集。服务器决定如何作为单个工作单元完成更新。当通过游标更新时，客户端应用程序要求服务器为每行维护行锁或版本信息，而这只是为了客户端在提取行后请求更新行。

而且，使用游标意味着服务器通常要在临时存储中维护客户端的状态信息，如用户在服务器上的当前行集。为众多客户端维护这类状态信息需消耗大量的服务器资源。对于关系数据库，更好的策略是让客户端应用程序快速进出，以便在各次调用之间不在服务器上维护客户端的状态信息。面向集合的  SQL  语句支持此策略。

然而，如果查询使用游标，请确定如果使用更高效的游标类型（如快速只进游标）或单个查询能否更高效地编写游标查询。有关更多信息，请参见 [使用高效数据检索优化应用程序性能][Link 33] 。

·                      使事务尽可能简短。有关更多信息，请参见 [事务和批处理对应用程序性能的影响][Link 34] 。

·                      使用存储过程。有关更多信息，请参见 [存储过程对应用程序性能的影响][Link 35] 。

·                      使用  Prepared Execution  来执行参数化  SQL  语句。有关更多信息，请参见 Prepared Execution (ODBC) 。

·                      始终处理完所有结果。

不要设计或使用在未取消查询时就停止处理结果行的应用程序。否则通常会导致阻塞和降低性能。有关更多信息，请参见 [了解和避免阻塞][Link 36] 。

·                      确保将应用程序设计为可避免死锁。有关更多信息，请参见 将死锁减至最少 。

·                      确保已设置所有能够优化分布式查询性能的适当选项。有关更多信息，请参见 [优化分布式查询][Link 37] 。

在应用系统的设计中，要着重考虑以下几点：

1 ．合理使用索引

索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用 IBM 最先提出的 ISAM 索引结构。索引的使用要恰到好处，其使用原则如下：

● 在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。

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

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

● 如果待排序的列有多个，可以在这些列上建立复合索引（ compound index ）。

● 使用系统工具。如 Informix 数据库有一个 tbcheck 工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用 tbcheck 工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。

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 ．使用临时表加速查询

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：

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

**优化实用工具和工具性能**

可在生产数据库上执行以获得最佳性能收益的三个操作包括：

·                      备份和还原操作。

·                      将数据大容量复制到表中。

·                      执行数据库控制台命令  (DBCC)  操作。

一般情况下，不需要优化这些操作。然而，在性能很关键的情形中，可采用一些技巧优化性能。

Microsoft SQL Server 数据库内核用 1 个基于费用的查询优化器自动优化向 SQL 提交的数据查询操作。数据操作查询是指支持 SQL 关键字 WHERE 或 HAVING 的查询，如 SELECT 、 DELETE 和 UPDATE 。基于费用的查询优化器根据统计信息产生子句的费用估算。

了解优化器数据处理过程的简单方法是检测 SHOWPLAN 命令的输出结果。如果用基于字符的工具 ( 例如 isql) ，可以通过键入 SHOW SHOWPLAN ON 来得到 SHOWPLAN 命令的输出。如果使用图形化查询，比如 SQL Enterprise Manager 中的查询工具或 isql/w ，可以设定配置选项来提供这一信息。

SQL Server 的优化通过 3 个阶段完成 : 查询分析、索引选择、合并选择 :

1. 查询分析

在查询分析阶段， SQL Server 优化器查看每一个由正规查询树代表的子句，并判断它是否能被优化。 SQL Server 一般会尽量优化那些限制扫描的子句。例如，搜索和 / 或合并子句。但是不是所有合法的 SQL 语法都可以分成可优化的子句，如含有 SQL 不等关系符 “<>” 的子句。因为 “<>” 是 1 个排斥性的操作符，而不是 1 个包括性的操作符，所在扫描整个表之前无法确定子句的选择范围会有多大。当 1 个关系型查询中含有不可优化的子句时，执行计划用表扫描来访问查询的这个部分，对于查询树中可优化的 SQL Server 子句，则由优化器执行索引选择。

2. 索引选择

对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用于访问数据。只有当索引中的列的 1 个前缀与查询子句中的列完全匹配时，这个索引才被认为是有用的。因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。对于分簇索引，原来的数据也是根据索引列顺序排序的。想用索引的次要列访问数据，就像想在电话本中查找所有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否符合条件。如果 1 个子句有可用的索引，那么优化器就会为它确定选择性。

所以在设计过程中，要根据查询设计准则仔细检查所有的查询，以查询的优化特点为基础设计索引。

(1) 比较窄的索引具有比较高的效率。对于比较窄的索引来说，每页上能存放较多的索引行，而且索引的级别也较少。所以，缓存中能放置更多的索引页，这样也减少了 I/O 操作。

(2)SQL Server 优化器能分析大量的索引和合并可能性。所以与较少的宽索引相比，较多的窄索引能向优化器提供更多的选择。但是不要保留不必要的索引，因为它们将增加存储和维护的开支。对于复合索引、组合索引或多列索引， SQL Se

**优化服务器性能**

Microsoft® SQL Server™ 2000  自动调整很多服务器配置选项，因此系统管理员只需做很少的调整（如果有）。这些配置选项可以由系统管理员修改，但一般建议保留为默认值，以使  SQL Server  能根据运行时的情况自动对自身进行调整。

不过，如果需要，可以配置下列组件以优化服务器性能：

·                      SQL Server  内存

·                      I/O  子系统

·                      Microsoft Windows NT®  选项

MSSQL 是怎样使用内存的 :  
  
 　　最大的开销一般是用于数据缓存，如果内存足够，它会把用过的数据和觉得你会用到的数据统统扔到内存中，直到内存不足的时候，才把命中率低的数据给清掉。所以一般我们在看 statistics io 的时候，看到的 physics read 都是 0 。   
  
 　　其次就是查询的开销，一般地说， hash join 是会带来比较大的内存开销的，而 merge join 和 nested loop 的开销比较小，还有排序和中间表、游标也是会有比较大的开销的。   
  
 　　所以用于关联和排序的列上一般需要有索引。   
  
 　　再其次就是对执行计划、系统数据的存储，这些都是比较小的。

我们先来看数据缓存对性能的影响，如果系统中没有其它应用程序来争夺内存，数据缓存一般是越多越好，甚至有些时候我们会强行把一些数据 pin 在高速缓存中。但是如果有其它应用程序，虽然在需要的时候 MSSQL 会释放内存，但是线程切换、 IO 等待这些工作也是需要时间的，所以就会造成性能的降低。这样我们就必须设置 MSSQL 的最大内存使用。可以在 SQL Server  属性（内存选项卡）中找到配置最大使用内存的地方，或者也可以使用 sp\_configure 来完成。如果没有其它应用程序，那么就不要限制 MSSQL 对内存的使用。

然后来看查询的开销，这个开销显然是越低越好，因为我们不能从中得到好处，相反，使用了越多的内存多半意味着查询速度的降低。所以我们一般要避免中间表和游标的使用，在经常作关联和排序的列上建立索引。

**不更改代码的情况下如何优化数据库系统**

这个问题很多 DBA 可能都碰到过吧：比如刚接手一个旧有系统，原来的厂商不允许对代码修改，或者是系统应用比较关键。不允许作修改，或者是源代码出于商业目的，进行了一定程度的加密，还有的时候可能是行政因素 \-- 领导为了避免责任，不允许你这样做，但这个时候，系统的性能上的问题还比较严重，还有其他办法怎么对系统进行优化么 ?

在这里我尝试总结一下可能有的途径。

**针对特定的SQL进行"外科手术"**  (Metalink 122812.1) ，**改进执行计划**

·                                  更新统计信息  ( 调整采样率 / 柱状图统计 )

·                                  调整索引  ( 添加或调整合适的索引，删除不必要的索引 )

·                                  创建物化试图 ( 用空间开销来换取时间收益 )

**优化OS和数据库以外的其他东西**

首先优化操作系统 \- 比如核心参数的合理调整，操作系统资源的合理分配 ;  磁盘 IO 的调整 , 这是很重要的一部分，因为磁盘 IO 速度很容易造成系统瓶颈 ; 网络资源的优化 \-TCP/IP 的参数调整 ;

**调整Oracle初始化参数**

优化器模式的设定 ,db\_cache  参数等设定 ,sga  大小等参数设定，都对数据库性能有着重要的影响。

**合理的系统资源调度**

在一些批处理操作为主的系统中，系统资源的调度是比较重要的，调度不合理，很容易造成资源争用。有的系统可能在系统创建之初调度是比较合理的，经过一段时间运行之后，可能因为数据量的变化， SQL 语句的执行计划变化等会造成操作时间上的重叠，这肯定会给系统带来压力上的问题。

**调整数据库对象**

·                                  调整 pctfree ,freelist  ，存储参数

·                                  调整表空间文件和数据库对象（表、索引）的磁盘分布。

·                                  cache  一些常用的数据库对象。

**系统Bug问题带来的影响/升级改进性能**

Oracle 软件 Bug 多多，系统运行初期有的 Bug 带来的危害还不够明显，随着时间的推移，个别的 Bug 会给系统性能造成问题。这个时候对系统的 Bug  修复已经对数据库系统进行升级就是必要的。通过升级，修正 Oracle 软件缺陷，同时在升级后也可能会增强数据库引擎的效率。当然，也要注意升级可能带来的不良的影响。

**操作系统相关优化**

1.  操作系统性能的好坏直接影响数据库的使用性能，如果操作系统存在问题，如 CPU 过载、过度内存交换、磁盘 I/O 瓶颈等，在这种情况下，单纯进行数据库内部性能调整是不会改善系统性能的。我们可以通过 Windows NT 的系统监视器 (System Monitor) 来监控各种设备，发现性能瓶颈。 　　 CPU  一种常见的性能问题就是缺乏处理能力。系统的处理能力是由系统的 CPU 数量、类型和速度决定的。如果系统没有足够的 CPU 处理能力，它就不能足够快地处理事务以满足需要。我们可以使用 System Monitor 确定 CPU 的使用率，如果以 75% 或更高的速率长时间运行，就可能碰到了 CPU 瓶颈问题，这时应该升级 CPU 。但是升级前必须监视系统的其他特性，如果是因为 SQL 语句效率非常低，优化语句就有助于解决较低的 CPU 利用率。而当确定需要更强的处理能力，可以添加 CPU 或者用更快的 CPU  替换。 　　内存  SQL Server 可使用的内存量是 SQL Server 性能最关键因素之一。而内存同 I/O 子系统的关系也是一个非常重要的因素。例如，在 I/O 操作频繁的系统中， SQL Server 用来缓存数据的可用内存越多，必须执行的物理 I/O 也就越少。这是因为数据将从数据缓存中读取而不是从磁盘读取。同样，内存量的不足会引起明显的磁盘读写瓶颈，因为系统缓存能力不足会引起更多的物理磁盘 I/O 。 　　可以利用 System Monitor 检查 SQL Server 的 Buffer Cache Hit Ratio 计数器，如果命中率经常低于 90% ，就应该添加更多的内存。 　　 I/O 子系统 由 I/O 子系统发生的瓶颈问题是数据库系统可能遇到的最常见的同硬件有关的问题。配置很差的 I/O 子系统引起性能问题的严重程度仅次于编写很差的 SQL 语句。 I/O 子系统问题是这样产生的，一个磁盘驱动器能够执行的 I/O 操作是有限的，一般一个普通的磁盘驱动器每秒只能处理 85 次 I/O 操作，如果磁盘驱动器超载，到这些磁盘驱动器的 I/O 操作就要排队， SQL 的 I/O 延迟将很长。这可能会使锁持续的时间更长，或者使线程在等待资源的过程中保持空闲状态，其结果就是整个系统的性能受到影响。

解决 I/O 子系统有关的问题也许是最容易的，多数情况下，增加磁盘驱动器就可以解决这个性能问题。

当然，影响性能的因素很多，而应用又各不相同，找出一个通用的优化方案是很困难的，只能是在系统开发和维护的过程中针对运行的具体情况，不断加以调整。

2 　与 SQL Server 相关的硬件系统

与 SQL Server 有关的硬件设计包括系统处理器、内存、磁盘子系统和网络，这 4 个部分基本上构成了硬件平台， Windows NT 和 SQL Server 运行于其上。

2.1 　系统处理器 (CPU)

根据自己的具体需要确定 CPU 结构的过程就是估计在硬件平台上占用 CPU 的工作量的过程。从以往的经验看， CPU 配置最少应是 1 个 80586/100 处理器。如果只有 2 ～ 3 个用户，这就足够了，但如果打算支持更多的用户和关键应用，推荐采用 Pentium Pro 或 P Ⅱ 级 CPU 。

2.2 　内存 (RAM)

为 SQL Server 方案确定合适的内存设置对于实现良好的性能是至关重要的。 SQL Server 用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。 SQL Server 最多能利用 2GB 虚拟内存，这也是最大的设置值。还有一点必须考虑的是 Windows NT 和它的所有相关的服务也要占用内存。

Windows NT 为每个 WIN32 应用程序提供了 4GB 的虚拟地址空间。这个虚拟地址空间由 Windows NT 虚拟内存管理器 (VMM) 映射到物理内存上，在某些硬件平台上可以达到 4GB 。 SQL Server 应用程序只知道虚拟地址，所以不能直接访问物理内存，这个访问是由 VMM 控制的。 Windows NT 允许产生超出可用的物理内存的虚拟地址空间，这样当给 SQL Server 分配的虚拟内存多于可用的物理内存时，会降低 SQL Server 的性能。

这些地址空间是专门为 SQL Server 系统设置的，所以如果在同一硬件平台上还有其它软件 ( 如文件和打印共享，应用程序服务等 ) 在运行，那么应该考虑到它们也占用一部分内存。一般来说硬件平台至少要配置 32MB 的内存，其中， Windows NT 至少要占用 16MB 。 1 个简单的法则是，给每一个并发的用户增加 100KB 的内存。例如，如果有 100 个并发的用户，则至少需要 32MB+100 用户 \*100KB=42MB 内存，实际的使用数量还需要根据运行的实际情况调整。可以说，提高内存是提高系统性能的最经济的途径。

2.3 　磁盘子系统

设计 1 个好的磁盘 I/O 系统是实现良好的 SQL Server 方案的一个很重要的方面。这里讨论的磁盘子系统至少有 1 个磁盘控制设备和 1 个或多个硬盘单元，还有对磁盘设置和文件系统的考虑。智能型 SCSI-2 磁盘控制器或磁盘组控制器是不错的选择，其特点如下 :

(1) 控制器高速缓存。　　 (2) 总线主板上有处理器，可以减少对系统 CPU 的中断。　　 (3) 异步读写支持。　　 (4)32 位 RAID 支持。　　 (5) 快速 SCSI—2 驱动。　　 (6) 超前读高速缓存 ( 至少 1 个磁道 ) 。

3 　检索策略

在精心选择了硬件平台，又实现了 1 个良好的数据库方案，并且具备了用户需求和应用方面的知识后，现在应该设计查询和索引了。有 2 个方面对于在 SQL Server 上取得良好的查询和索引性能是十分重要的，第 1 是根据 SQL Server 优化器方面的知识生成查询和索引 ; 第 2 是利用 SQL Server 的性能特点，加强数据访问操作。

[Web _1]: http://writeblog.csdn.net/Editor/FCKeditor/editor/fckeditor.html?InstanceName=ctl00_ContentPlaceHolder1_EntryEditor1_richTextEditor_richTextEditor&Toolbar=Default#_Toc164757438
[... 2]: http://writeblog.csdn.net/Editor/FCKeditor/editor/fckeditor.html?InstanceName=ctl00_ContentPlaceHolder1_EntryEditor1_richTextEditor_richTextEditor&Toolbar=Default#_Toc164757440
[... 3]: http://writeblog.csdn.net/Editor/FCKeditor/editor/fckeditor.html?InstanceName=ctl00_ContentPlaceHolder1_EntryEditor1_richTextEditor_richTextEditor&Toolbar=Default#_Toc164757441
[4]: http://writeblog.csdn.net/Editor/FCKeditor/editor/fckeditor.html?InstanceName=ctl00_ContentPlaceHolder1_EntryEditor1_richTextEditor_richTextEditor&Toolbar=Default#_Toc164757442
[Microsoft SQL Server 2000 _... 5]: http://writeblog.csdn.net/Editor/FCKeditor/editor/fckeditor.html?InstanceName=ctl00_ContentPlaceHolder1_EntryEditor1_richTextEditor_richTextEditor&Toolbar=Default#_Toc164757443
[Microsoft Windows NT_Microsoft Windows_ 95_Microsoft Windows 98 _ Microsoft Windows 2000_ SQL Server _... 6]: http://writeblog.csdn.net/Editor/FCKeditor/editor/fckeditor.html?InstanceName=ctl00_ContentPlaceHolder1_EntryEditor1_richTextEditor_richTextEditor&Toolbar=Default#_Toc164757444
[Link 1]: http://writeblog.csdn.net/Editor/FCKeditor/editor/FreeICQ.lnk
[Link 2]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1_6583.htm
[Link 3]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1_2upf.htm
[RAID]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1_0m5g.htm
[Link 4]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1_33lf.htm
[Link 5]: http://www.jjzx.cn/leibie-4-1.html
[Link 6]: http://www.jjzx.cn/tag-37-1.html
[table]: http://www.jjzx.cn/tag-156-1.html
[db]: http://www.jjzx.cn/tag-136-1.html
[ide]: http://www.jjzx.cn/tag-382-1.html
[Link 7]: http://www.jjzx.cn/tag-59-1.html
[image]: http://www.jjzx.cn/tag-260-1.html
[2003]: http://www.jjzx.cn/tag-218-1.html
[Link 8]: http://www.jjzx.cn/tag-126-1.html
[Link 9]: http://www.jjzx.cn/tag-3-1.html
[Link 10]: http://www.jjzx.cn/tag-337-1.html
[Link 11]: http://www.jjzx.cn/tag-9-1.html
[select]: http://www.jjzx.cn/tag-115-1.html
[Link 12]: http://www.jjzx.cn/tag-34-1.html
[Link 13]: http://www.jjzx.cn/tag-175-1.html
[SQL SERVER]: http://www.jjzx.cn/tag-132-1.html
[Link 14]: http://www.jjzx.cn/tag-328-1.html
[Link 15]: http://www.jjzx.cn/tag-8-1.html
[Link 16]: http://www.jjzx.cn/tag-262-1.html
[Link 17]: http://www.jjzx.cn/tag-22-1.html
[Link 18]: http://www.jjzx.cn/tag-49-1.html
[Link 19]: http://www.jjzx.cn/tag-331-1.html
[Link 20]: http://www.jjzx.cn/tag-35-1.html
[Link 21]: http://www.jjzx.cn/tag-25-1.html
[Link 22]: http://www.jjzx.cn/tag-51-1.html
[Link 23]: http://www.jjzx.cn/tag-54-1.html
[title]: http://www.jjzx.cn/tag-155-1.html
[ORACLE]: http://www.jjzx.cn/tag-121-1.html
[Link 24]: http://www.jjzx.cn/tag-134-1.html
[Link 25]: http://www.jjzx.cn/tag-263-1.html
[Link 26]: http://www.jjzx.cn/tag-321-1.html
[Link 27]: http://www.jjzx.cn/tag-339-1.html
[Link 28]: http://www.jjzx.cn/tag-418-1.html
[Link 29]: http://www.jjzx.cn/tag-422-1.html
[web]: http://www.jjzx.cn/tag-167-1.html
[Link 30]: http://www.jjzx.cn/tag-76-1.html
[Link 31]: http://www.jjzx.cn/tag-42-1.html
[ASP]: http://www.jjzx.cn/tag-176-1.html
[C]: http://www.jjzx.cn/tag-79-1.html
[Link 32]: http://www.jjzx.cn/tag-127-1.html
[Link 33]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1a_71nw.htm
[Link 34]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1a_325h.htm
[Link 35]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1a_6x45.htm
[Link 36]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1a_4uav.htm
[Link 37]: http://www.java-dotnet.net/online/sqlserver/optimsql/odp_tun_1a_6oxf.htm