dreamforday
- 浏览: 22159 次
- 性别:
- 来自: 北京
-
最新评论
转载SQL Server 数据库的性能优化
你是否在千方百计优化SQL Server 数据库的性能?如果你的数据库中含有大量的表格，把这些表格分区放入独立的文件组可能会让你受益匪浅。SQL Server 2005引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能。
SQL Server数据库表分区操作过程由三个步骤组成：
1. 创建分区函数
2. 创建分区架构
3. 对表进行分区
下面将对每个步骤进行详细介绍。
步骤一：创建一个分区函数
此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(how)。这个操作并不涉及任何表格，只是单纯的定义了一项技术来分割数据。
我们可以通过指定每个分区的边界条件来定义分区。例如，假定我们有一份Customers表，其中包含了关于所有客户的信息，以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区：
CREATE PARTITION FUNCTION customer_partfunc (int)
AS RANGE RIGHT
FOR VALUES (250000, 500000, 750000)
这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据，第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在500,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。
请注意，这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的，如果使用"RANGELEFT"语句，则上述第一个分区应该包括所有值小于或等于250,000的数据，第二个分区的数据值在250,001到500,000之间，以此类推。
步骤二：创建一个分区架构
一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置(where)。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。例如，如果有四个文件组，组名从"fg1"到"fg4"，那么以下的分区架构就能达到想要的效果：
CREATE PARTITION SCHEME customer_partscheme
AS PARTITION customer_partfunc
TO (fg1, fg2, fg3, fg4)
注意，这里将一个分区函数连接到了该分区架构，但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表，我们都可以使用该分区架构(或仅仅是分区函数)。
步骤三：对一个表进行分区
定义好一个分区架构后，就可以着手创建一个分区表了。这是整个分区操作过程中最简单的一个步骤。只需要在表创建指令中添加一个"ON"语句，用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数，所以不需要再指定分区函数了。
例如，使用以上的分区架构创建一个客户表，可以调用以下的Transact-SQL指令：
CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)
ON customer_partscheme (CustomerNumber)
关于SQL Server的表分区功能，你知道上述的相关知识就足够了。记住!编写能够用于多个表的一般的分区函数和分区架构就能够大大提高可复用性。
数据库性能调优是每一个优秀SQL Server管理员最终的责任。虽然保证数据的安全和可用性是我们的最高的目标，但是假如数据库应用程序无法满足用户的要求，那么DBA们会因为性能低下的设计和实现而受到指责。SQL Server 2005在数据库性能方面得到了很多提高，尤其是表分区的技术。如果你还没不了解表分区的特征，那么请你花点时间读这篇文章。
表分区的概念不是一个新的概念;只要你当过一段时间的SQL Server DBA，那么你可能已经对一些频繁访问的表进行过归档，当这个表中的历史数据变的不再经常被访问的时候。比如，假设你有一个打印时间报表的应用，你的报告很少会查询1995年的数据，因为绝大部分的预算规划会基于最近几年的数据。
在SQL Server的早期版本中，你可以创建多个表。每一个表都具有相同的列结构，用来保存不同年份的数据。这样，当存在着对历史数据访问的必要的时候，你可以创建一个视图来对这些表进行查询处理。将数据保存在多个表中是很方便的，因为相对于查询时扫描整个大表，扫描小表会更快。但是这种好处只有在你预先知道哪些时间段的数据会被访问。同时，一旦数据过期，你还需要创建新表并且转移新产生的历史数据。
SQL Server 7和SQL Server 2000支持分布式分区视图(distributedpartitioned views，又称为物化视图，materialized views).分布式分区视图由分布于多台服务器上的、具有相同表结构的表构成，而且你还需要为每一个服务器增加链接服务器定义(linked serverdefinitions)，最后在其中一台服务器上创建一个视图将每台服务器上返回的数据合并起来。这里的设计思想是数据库引擎可以利用多台服务器的处理能力来满足查询。
但是，分布式分区视图(DPV)受到很多限制，你可以在SQL Server的在线帮助文档中阅读到。虽然DPV在一些情况下能够提供性能上的提高，但是这种技术不能被广泛的应用。已经被证明它们不能满足逐步增长的企业级应用的要求。何况，DPV的实现是一个费力的过程，需要DBA进行很多工作。
SQL Server 2005开始支持表分区，这种技术允许所有的表分区都保存在同一台服务器上。每一个表分区都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个分区表。
在这种设计架构下，数据库引擎能够判定查询过程中应该访问哪个分区，而不用扫描整个表。如果查询需要的数据行分散在多个分区中，SQL Server使用多个处理器对多个分区进行并行查询。你可以为在创建表的时候就定义分区的索引。 对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此，当对大表进行查询，表分区可以产生相当大的性能提升。
现在让我们通过一个简单的例子来了解表分区是如何发挥作用的。在这篇文章中，我不想深入到分区的语法细节当中，这些你可以在SQL Server的在线帮助文档中找到。下面的例子基于存储着一个时间报表系统的数据的数据仓库。除了默认的文件组，我另外创建了7个文件组，每一个文件组仅包含一个文件，这个文件将存储由分区函数定义的一部分数据。
为了测试表分区的性能提升，我向这个分区表中插入了一千五百万行，同时向另外一个具有相同表结构、但是没有进行分区的表插入了同样的数据。对分区表执行的INSERT语句运行的更快一些。甚至在我的内存不到1G的笔记本电脑上，对分区表的INSERT语句比不分区的表的INSERT语句要快上三倍。当然，查询的执行时间依据硬件资源的差异而所有变化，但是你还是能够在你的环境中感到不同程度的提升。
我将检查更深入了一步，通过分别检查同一条返回所有行的、简单SELECT语句在分区表和非分区表上的执行计划，返回的数据范围通过WHERE语句来指定。同一条语句在这两个不同的表上有不同的执行计划。对于分区表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说，SQL Server将两个分区视为独立的表，因此使用一个嵌套循环将它们连接起来。对非分区的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的分区策略创建多个表，同时在查询中连接这些表，那么性能上的提升会更加明显
你可以使用下面的查询来了解每一个分区中的行的个数：
SELECT $PARTITION.TimeEntryDateRangePFN(time_entry_date) AS Partition,
COUNT(*) AS [COUNT] FROM fact_time_entry
GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date)
ORDER BY Partition
表分区对交易环境和数据仓库环境来说，都是一个重要的特征。数据仓库用户最主要的抱怨是移动事实表(fact table)会花费太多时间。当装载数据到事实表的时候，用户查询(立方体处理查询)的性能会明显下降，甚至是完全无法成功。因此，装载大量的数据到事实表的时候常常需要停机。如果使用表分区，就不再出现这样的情况——确切的讲，你一眨眼的工夫就可以移动事实表。为了演示这是如何生效的，我使用上面例子中相同的分区函数和表结构来创建一个新的表，这个表叫做fact_time_entry2。表的主键从五千万开始，这样fact_time_entry2就不会包含表fact_time_entry中已经有的数据。
现在我把2007年的数据移动到这张fact_time_entry2中。同时让我们假设fact_time_entry表中包含着2007年之前的数据。在fact_time_entry2表完成数据的转移，我执行下面的语句：
ALTER TABLE fact_time_entry2
SWITCH PARTITION 8 TO fact_time_entry PARTITION 8
这条语句将编号为8的分区，这个分区恰好包含着2007年的数据，从fact_time_entry2移动到了fact_time_entry表中，在我的笔记本电脑上，这个过程只花费了3毫秒。在这短短的3毫秒中，我的事实表就增加了五百万条记录!的确，我需要在交换分区之前，将数据移动到中间表，但是我的用户不需要担心——事实表随时都可以查询!在这幕后，实际上没有数据移动——只是两张表的元数据发生了变化。
我可以使用类似的查询删除事实表中不在需要的数据。例如，假设我们决定我们不再关心2004年的记录。下面的语句可以将这些记录转移到我们创建的工作表中：
ALTER TABLE fact_time_entry
SWITCH PARTITION 2 TO fact_time_entry2 PARTITION 2
这样的语句依旧在毫秒级内完成了。现在，我可以删除fact_time_entry2或者将它移到其他的服务器上。我的事实表不会包含2004年的任何记录。这个分区还是需要在目的表中存在，而且它必须是空的。你不能将分区转移到一个包含重复数据的表中。源表和目的表的分区必须一致，同时被转移的数据必须在同一个文件组中。即使受到这么多的限制，转换分区和无需停机就可以移动数据表的功能必将让数据仓库的实现变的前所未有的轻松。
SQL Server 表分区(partitioned table/Data Partitioning)
Partitioned Table
可伸缩性性是数据库管理系统的一个很重要的方面，在SQL Server 2005中可伸缩性方面提供了表分区功能。
其实对于有关系弄数据库产品来说，对表、数据库和服务器进行数据分区的从而提供大数据量的支持并不是什么新鲜事，但 SQL Server 2005 提供了一个新的体系结构功能，用于对数据库中的文件组进行表分区。水平分区可根据分区架构，将一个表划分为几个较小的分组。表分区功能是针对超大型数据库（从数百吉字节到数千吉字节或更大）而设计的。超大型数据库 (VLDB) 查询性能通过分区得到了改善。通过对广大分区列值进行分区，可以对数据的子集进行管理，并将其快速、高效地重新分配给其他表。
设想一个大致的电子交易网站，有一个表存储了此网站的历史交易数据，这此数据量可能有上亿条，在以前的SQL Server版本中存储在一个表中不管对于查询性能还是维护都是件麻烦事，下面我们来看一下在SQL Server2005怎么提高性能和可管理性：
-- 创建要使用的测试数据库，Demo
USE [master]
IF EXISTS (SELECT name FROMmaster.dbo.sysdatabases WHERE name = N'DEMO')
DROP DATABASE [DEMO]
CREATE DATABASE [DEMO]
--由于表分区使用使用新的体系结构，使用文件组来进行表分区，所以我们创建将要用到的6个文件组，来存储6个时间段的交易数据[<2000],[2001], [2002], [2003], [2004], [>2005]
ALTER DATABASE Demo ADD FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILEGROUP YEARFG6;
-- 下面为这些文件组添加文件来进行物理的数据存储
ALTER DATABASE Demo ADD FILE (NAME ='YEARF1', FILENAME = 'C:\ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF2', FILENAME = 'C:\ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF3', FILENAME = 'C:\ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF4', FILENAME = 'C:\ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF5', FILENAME = 'C:\ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF6', FILENAME = 'C:\ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;
-- HERE WE ASSOCIATE THE PARTITION FUNCTIONTO
-- THE CREATED FILEGROUP VIA A PARTITIONINGSCHEME
USE DEMO;
GO
-------------------------------------------------------
-- 创建分区函数
-------------------------------------------------------
CREATE PARTITION FUNCTION YEARPF(datetime)
AS
RANGE LEFT FOR VALUES ('01/01/2000'
,'01/01/2001'
,'01/01/2002'
,'01/01/2003'
,'01/01/2004')
-------------------------------------------------------
-- 创建分区架构
-------------------------------------------------------
CREATE PARTITION SCHEME YEARPS
AS PARTITION YEARPF TO (YEARFG1,YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6)
-- 创建使用此Schema的表
CREATE TABLE PARTITIONEDORDERS
(
ID INT NOT NULL IDENTITY(1,1),
DUEDATE DATETIME NOT NULL,
) ON YEARPS(DUEDATE)
--为此表填充数据
declare @DT datetime
SELECT @DT = '1999-01-01'
--start looping, stop at ending date
WHILE (@DT <= '2005-12-21')
BEGIN
INSERT INTO PARTITIONEDORDERS VALUES(@DT)
SET @DT=dateadd(yy,1,@DT)
END
-- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition
SELECT *, $PARTITION.YEARPF(DUEDATE) FROMPARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID= OBJECT_ID('PARTITIONEDORDERS')
--
--现在我们设想一下，如果我们随着时间的流逝，现在已经到了2005年，按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就看如果新加一个分区。
--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区YEARFG6分区中，这样此分区就存储了两段partition的数据。
ALTER PARTITION SCHEME YEARPS
NEXT USED YEARFG6;
--更改分区函数
ALTER PARTITION FUNCTION YEARPF()
SPLIT RANGE ('01/01/2005')
--现在我们可以看一下我们刚才插入的行都分布在哪个Partition？
SELECT *, $PARTITION.YEARPF(DUEDATE) FROMPARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID= OBJECT_ID('PARTITIONEDORDERS')
这就是对数据库的横向分割，把一个大表表分割成不同的小表，并把小表并分步到不同的物理文件（由不同的物理磁盘构成）组上。这是做海量数据查询的基础理论，sql2000以前不支持这个技术，不过有个分区视图有类似的功能。但是sql2005直接有这个功能，我想ax应该很快会支持这个功能。
其实，googl的查询原理也是这样的，它把每次查询同时提交给googl的50万台服务器，每个服务器上只保留了不多的信息，这个每个服务器能很快的返回自己找到的数据，并提供给集群服务器整理排序后显示给用户。理论上讲，若有经济条件，把数据分割成无限多个节点，这样每个节点查询自己的数据时的速度几乎是不耗时的，这样速度就会大大的加快
深入浅出理解索引结构
(一)深入浅出理解索引结构
实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引(clustered index，也称聚类索引、簇集索引)和非聚集索引(nonclustered index，也称非聚类索引、非簇集索引)。下面，我们举例来说明一下聚集索引和非聚集索引的区别：
其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字;同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字，您可以快速地从自典中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。
我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。
进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
(二)何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引(很重要)。
动作描述
使用聚集索引
使用非聚集索引
事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
(三)结合实际，谈索引使用的误区
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。
1、主键就是聚集索引
这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。
在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条)：
(1)仅在主键上建立聚集索引，并且不划分时间段：
Select gid,fariqi,neibuyonghu,title fromtgongwen
用时：128470毫秒(即：128秒)
(2)在主键上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi> dateadd(day,-90,getdate())
用时：53763毫秒(54秒)
(3)将聚合索引建立在日期列(fariqi)上：
select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi> dateadd(day,-90,getdate())
用时：2423毫秒(2秒)
虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。
得出以上速度的方法是：在各个select语句前加：
declare @d datetime
set @d=getdate()
并在select语句后加：
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
2、只要建立索引就能显著提高查询速度
事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。
从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。
3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度
上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引(compound index)。
很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗?带着这个问题，我们来看一下以下的查询速度(结果集都是25万条数据)：(日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列)
(1)select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi>'2004-5-5'
查询速度：2513毫秒
(2)select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi>'2004-5-5' and neibuyonghu='办公室'
查询速度：2516毫秒
(3)select gid,fariqi,neibuyonghu,title fromTgongwen
where neibuyonghu='办公室'
查询速度：60280毫秒
从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。
(四)其他书上没有的索引使用经验总结
1、用聚合索引比用不是聚合索引的主键速度快
下面是实例语句：(都是提取25万条数据)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
使用时间：3326毫秒
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen where gid<=250000
使用时间：4470毫秒
这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。
2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen order by fariqi
用时：12936
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen order by gid
用时：18843
这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话，如10万以上，则二者的速度差别不明显。
3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-1-1'
用时：6343毫秒(提取100万条)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-6-6'
用时：3170毫秒(提取50万条)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
用时：3326毫秒(和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-1-1' andfariqi<'2004-6-6'
用时：3280毫秒
4 、日期列不会因为有分秒的输入而减慢查询速度
下面的例子中，共有100万条数据，2004年1月1日以后的数据有50万条，但只有两个不同的日期，日期精确到日;之前有数据50万条，有5000个不同的日期，日期精确到秒。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-1-1' order by fariqi
用时：6390毫秒
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi<'2004-1-1' order by fariqi
用时：6453毫秒
(五)其他注意事项
“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。过多的索引甚至会导致索引碎片。
索引是从数据库中获取数据的最高效方式之一。95%的数据库性能问题都可以采用索引技术得到解决。
1. 不要索引常用的小型表
不要为小型数据表设置任何键，假如它们经常有插入和删除操作就更别这样作了。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。
2. 不要把社会保障号码(SSN)或身份证号码(ID)选作键
永远都不要使用 SSN 或 ID 作为数据库的键。除了隐私原因以外，SSN 或 ID 需要手工输入。永远不要使用手工输入的键作为主键，因为一旦你输入错误，你唯一能做的就是删除整个记录然后从头开始。
3. 不要用用户的键
在确定采用什么字段作为表的键的时候，可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。这样做会迫使你采取以下两个措施：
4. 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)
这样做会让你的索引占据大量的数据库空间
5. 使用系统生成的主键
假如你总是在设计数据库的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问。
采用系统生成键作为主键还有一个优点：当你拥有一致的键结构时，找到逻辑缺陷很容易
二、改善SQL语句
很多人不知道SQL语句在SQLSERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。比如：
select * from table1 where name='zhangsan'and tID > 10000
和执行:
select * from table1 where tID > 10000 andname='zhangsan'
一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。
事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。
虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。
在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数(SARG)，那么就称之为可优化的，并且可以利用索引快速获得所需数据。
SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下：
列名 操作符 <常数 或 变量>
或
<常数或 变量> 操作符列名
列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：
Name=’张三’
价格>5000
5000<价格
Name=’张三’ and 价格>5000
如果一个表达式不能满足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、非操作符、函数引起的不满足SARG形式的语句
不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。下面就是几个不满足SARG形式的例子：
ABS(价格)<5000
Name like ‘%三’
有些表达式，如：
WHERE 价格*2>5000
SQL SERVER也会认为是SARG，SQLSERVER会将此式转化为：
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数据库。运行前我们可以把SQLSERVER的statistics I/O状态打开。
(1)select title,price from titles wheretitle_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 whereexists
(select * from sales wheresales.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,titlefrom Tgongwen
where fariqi='2004-9-16' or gid>9990000
用时：68秒。扫描计数1，逻辑读 404008 次，物理读 283 次，预读 392163 次。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen where gid>9990000
用时：9秒。扫描计数8，逻辑读 67489 次，物理读 216 次，预读 7499 次。
看来，用union在通常情况下比用or的效率要高的多。
但经过试验，笔者发现如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16' or fariqi='2004-2-5'
用时：6423毫秒。扫描计数2，逻辑读 14726 次，物理读 1 次，预读7176 次。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-2-5'
用时：11640毫秒。扫描计数 8，逻辑读14806 次，物理读 108 次，预读 1144 次。
9、字段提取要按照“需多少、提多少”的原则，避免“select *”
我们来做一个试验：
select top 10000 gid,fariqi,reader,title fromtgongwen order by gid desc
用时：4673毫秒
select top 10000 gid,fariqi,title fromtgongwen order by gid desc
用时：1376毫秒
select top 10000 gid,fariqi from tgongwenorder 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 fromtgongwen
用时：196 毫秒。扫描计数 1，逻辑读289 次，物理读 1 次，预读 1527 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by gid asc
用时：4720毫秒。扫描计数 1，逻辑读41956 次，物理读 0 次，预读 1287 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by gid desc
用时：4736毫秒。扫描计数 1，逻辑读55350 次，物理读 10 次，预读 775 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by fariqi asc
用时：173毫秒。扫描计数 1，逻辑读290 次，物理读 0 次，预读 0 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by fariqi desc
用时：156毫秒。扫描计数 1，逻辑读289 次，物理读 0 次，预读 0 次。
从以上我们可以看出，不排序的速度以及逻辑读次数都是和“order by 聚集索引列” 的速度是相当的，但这些都比“orderby 非聚集索引列”的查询速度是快得多的。
同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。
12、高效的TOP
事实上，在查询和提取超大容量的数据集时，影响数据库响应时间的最大因素不是数据查找，而是物理的I/0操作。如：
select top 10 * from (
select top 10000 gid,fariqi,title fromtgongwen
where neibuyonghu='办公室'order by gid desc) as a
order by gid asc
这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是10000条记录，而整条语句仅返回10条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQLSERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用，发现TOP确实很好用，效率也很高。但这个词在另外一个大型数据库ORACLE中却没有，这不能说不是一个遗憾，虽然在ORACLE中可以用其他方法(如：rownumber)来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中，我们就将用到TOP这个关键词。
到此为止，我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然，我们介绍的这些方法都是“软”方法，在实践中，我们还要考虑各种“硬”因素，如：网络性能、服务器的性能、操作系统的性能，甚至网卡、交换机等。
写出性能优良的SQL
我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享！
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：
ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersectiontable)作为基础表, 交叉表是指那个被其他表所引用的表.
（2）WHERE子句中的连接顺序．：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
（3）SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名,这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
（4）减少访问数据库的次数：
ORACLE在内部执行了许多工作: 解析SQL语句,估算索引的利用率, 绑定变量, 读数据块等；
（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量,建议值为200
（6）使用DECODE函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
（7）整合简单,无关联的数据库访问：
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
（8）删除重复记录：
最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
（9）用TRUNCATE替代DELETE：
当删除表中的记录时,在通常情况下,回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
（10）尽量多使用COMMIT：
只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
（11）用Where子句替换HAVING子句：
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.(非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里
（12）减少对表的查询：
在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
（13）通过内部函数提高SQL效率.：
复杂的SQL往往牺牲了执行效率.能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
（14）使用表的别名(Alias)：
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
（15）用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOTIN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROMDEPT WHERE LOC = ‘MELB')
（16）识别'低效执行'的SQL语句：
虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
（17）用索引提高效率：
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列.通常, 在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT, DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
18） 用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
（19）sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行
（20）在java代码中尽量少用连接符“＋”连接字符串！
（21）避免在索引列上使用NOT 通常，
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
（22）避免在索引列上使用计算．
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例:
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
（23）用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
（24）用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意, 以上规则只针对多个索引列有效.如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中,LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
（25）用IN来替换OR
这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
（26）避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入).然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
（27）总是使用索引的第一个列：
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
28） 用UNION-ALL 替换UNION ( 如果有可能的话)：
当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNIONALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的.下面的SQL可以用来查询排序的消耗量
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
（29）用WHERE替代ORDER BY：
ORDER BY 子句只在两种严格的条件下使用索引.
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
低效: (索引不被使用)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
高效: (使用索引)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
（30）避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
SELECT … FROM EMP WHERE EMPNO = ‘123'
实际上,经过ORACLE类型转换, 语句转化为:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
（31）需要当心的WHERE子句:
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
在下面的例子里, (1)‘!=' 将不使用索引. 记住,索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘ ¦ ¦'是字符连接函数. 就象其他函数那样,停用了索引. (3) ‘+'是数学函数. 就象其他数学函数那样,停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.
（32）a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.
b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
（33）避免使用耗费资源的操作:
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS ,INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION, MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
（34）优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUPBY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP by JOB
SQL Server 存储过程的分页
SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
建立表：
CREATE TABLE[TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON
declare @iint
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName,Country,Note) values(@i,'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end
SETIDENTITY_INSERT TestTable OFF
-------------------------------------
分页方案一：(利用NotIn和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数id
FROM 表
ORDER BY id))
ORDER BY ID
-------------------------------------
分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
-------------------------------------
分页方案三：(利用SQL的游标存储过程分页)
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcountoutput
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpageas 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。
通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用NotIn和SELECT TOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用
在实际情况中，要具体分析。
数据库日常维护(参考)
数据库日常维护工作是系统管理员的重要职责。其内容主要包括以下几个部分：
一、备份系统数据
SYBASE 系统的备份与恢复机制保证了在系统失败时重新获取数据的可能性。SQL Server 提供了两种不同类型的恢复机制：一类是系统自动完成的恢复，这种措施在每次系统启动时都自动进行，保证了在系统瘫痪前完成的事务都写到数据库设备上，而未完成的事务都被回退；另一类是人工完成的恢复，这是通过 DUMP 和 LOAD 命令来执行人工备份和恢复工作。因此定期备份事务日志和数据库是一项十分重要的日常维护工作。
1、备份数据库
每一个数据库都应在创建之后卸出，从而提供一个装入基点。在此之后按排定的时间周期表卸出。比如每周五卸出数据库。对一般数据库系统卸出数据库周期建议为每周一次。
除了按计划周期卸出数据库之外，还需在每次运行没有日志的操作后卸出数据库。例如：
·每次强制地运行了 DUMP TRAN WITH NO_LOG （因为数据库的磁盘空溢出）；
·每次用 sp_dboption 允许 select into/bulkcopy 做快速拷贝，或用 SELECT INTO 命令创建一个永久性的表，或使用了 WRITETEXT 命令。
卸出数据库的命令为：
DUMP DATABASE database_name
TO dump_device
database_name 是要卸出的数据库名称，dump_device 是卸出设备的名称。用系统过程 sp_helpdevice 可以获得设备的信息。
下面一条命令用来卸出数据库 my_db ：
DUMP DATABASE my_db
TO db_bk_dev
2、备份事务日志
如果事务日志与数据库放在同一个设备上，则事务日志不应与数据库分开备份。master 数据库和小于 4M 的用户数据库就是这种情况。一般数据库系统的数据库和日志分别放在不同的设备上，因此，可以用 DUMP TRAN 命令单独备份日志。
备份事务日志的周期直接影响数据的恢复程度，因此建议每天备份。
备份事务日志的命令格式为：
DUMP TRANsaction database_name
[TO dump_device]
[WITH TRUNCATE_ONLY|WITH NO_LOG|WITH NO_TRUNCATE]
其中 database_name 是要备份事务的数据库名称，dump_device 是备份设备名称，仅当包含了 WITH TRUNCATE_ONLY 或 WITH NO_LOG 子句时，才可以备份到设备。
注意：如果总是用 DUMP DATEBASE （备份数据库及其日志），而不用 DUMP TRAN ，事务日志将不会刷新，而变得非常庞大。
对于 master 数据库和小型数据库每次运行 DUMP DATEBASE 之后应当运行 DUMP TRANsaction 命令刷新日志 。
下面一条命令备份数据库 db160 的事务日志到备份设备上：
DUMP TRANsaction db160
TO db_log_bk_dev
WITH TRUNCATE_ONLY
3、备份数据库及其日志间的相互作用
在至少卸出一次数据库前，卸出事务日志是毫无意义的。下图显示了备份数据库及其日志间的关系
如果在星期二下午5：01出现非硬件故障，需要做的所有工作是装入磁带5（参见下一节：数据恢复），由于磁带5是下午5：00刚备份的，因此只有备份和装入之间的一分钟内的数据损失。
但是，如果在星期二下午4：49失效会怎么样呢？在这种情况下，要装入磁带1（在星期五下午5：00的卸出）。然后，依次装入磁带2，3以及4。这样，系统将恢复到星期二上午10：00点的状态，星期二的大部分工作丢失了。此例显示了经常卸出事务的重要性。
二、万一系统失败时恢复数据库系统
如果用户数据库存储的设备失效，从而数据库被破坏或不可存取，通过装入最新的数据库备份以及后来的事务日志备份可以恢复数据库。假设当前的事务日志存在于一个并没有毁坏的设备上，带着 WITH NO_TRUNCATE 选项的 DUMP TRANsaction 命令卸出它。
要恢复数据库按如下步骤去做：
1、如果日志存在于一个分离的设备上，用带着 NO_TRUNCATE 选项的 DUMP TRANsaction 命令卸出被毁坏的或者不可存取的用户数据库事务日志。
2、用下面的查询检查设备分配已毁坏数据库的设备使用情况。必须为同一目的赋同样的空间块。
下面的查询显示了分配给数据库 mydb 设备使用和尺寸情况：
SELECT segmap，size FROM sysusages
WHERE dbid =
（ SELECT dbid FROM sysdatabases WHERE name =“mydb”）
3、检查查询的输出。在 segmap 列的 ‘3’代表数据分配，‘4’代表日志分配。size 列代表 2K 数据块的数目。注意此信息的次序、使用和尺寸部分。例如，输出为：
segmapSize
--------------------
310240//实际尺寸为：20M
35120//实际尺寸为：10M
45120//实际尺寸为：10M
31024//实际尺寸为：2M
42048//实际尺寸为：4M
4、用 DROP DATABASE 命令删除毁坏设备上的数据库。如果系统报错，用DBCC DBREPAIR 命令的 DROPDB 选项。
5、删除数据库后，用 sp_dropdevice 删除毁坏了的设备。
6、用 DISK INIT 初始化新的数据库设备。
7、重建数据库。用 CREATE DATABASE 命令从老的 sysusages 表拷贝所有的行，并包含第一逻辑设备。
对上例，命令为：
CREATE DATABASE mydb
ON datadev1=20，datadev2=10
LOG ON logdev1=10
8、用 ALTER DATABASE 命令重建其余入口。在此例中，在datadev1上分配更多的空间，命令为：
ALTER DATABASE mydb ON datadev1=2[page]
9、用 LOAD DATABASE 重新装入数据库，然后用 LOAD TRAN 装入前面卸出的日志。
LOAD DATABASE 命令语法是：
LOAD DATABASE database_name
FROM dump_device
LOAD TRANsaction 命令的语法是：
LOAD TRANsaction database_name
FROM dump_device
卸出数据库和事务日志的缺省权限归数据库所有者，且可以传递给其他用户；装载数据库和事务的权限也归数据库所有者，但不能传递。
二、产生用户信息表，并为信息表授权；
系统维护人员的另一个日常事务是为用户创建新的信息表，并为之授权。创建表以及为表授权的方法已经在讲过，在此只将有关命令语法写出来。
·创建表的命令为：
CREATE TABLE table_name
（ column_1 datatype [NULL | NOT NULL |IDENTITY]，
column_2 ……
）
go
ALTER TABLE table_name
ADD PRIMARY KEY （column_list）
go
·删除表的命令格式为：
DROP TABLE table_name
go
·为表授权的命令格式为：
GRANT {ALL|permission_list}
ON table_name TO user_name
go
·收回权限的命令格式为
REVOKE {ALL|permission_list}
ON table_name FROM user_name
go
三、监视系统运行状况，及时处理系统错误；
系统管理员的另一项日常工作是监视系统运行情况。主要有以下几个方面：
1、监视当前用户以及进程的信息
使用系统过程：sp_who
说明：该命令显示当前系统所有注册用户及进程信息，如下表是某系统的信息。
SpidStatusLoginamehostnameblkdbnamecmd
---------------------------------------------------------------
1RunningSascosysv0MasterSELECT
2SleepingNULL0MasterNETWORK HANDLE
3SleepingNULL0MasterDEADLOCK TUNE
4SleepingNULL0MasterMIRROR HANDLER
5SleepingNULL0MasterHOUSEKEEPER
6SleepingNULL0MasterCHECKPOINT SLEEP
从左向右依次显示：进程号、当前状态、注册用户名、主机名、占用块数、数据库名以及当前命令。
如果监视时发现进程总数接近最大连接数（用系统过程：sp_configure “userconn” 查看）时，应下掉不活动或无关进程，以保证系统正常运做；另外亦可监视非法用户或用户使用不属于自己使用范围的数据库等情况。
2、监视目标占用空间情况
使用系统过程：sp_spaceused
说明：该过程显示行数、数据页数以及当前数据库中由某个目标或所有目标所占用的空间。如下表是某数据库日志表的信息：
NameRow_totalreserveddataIndex_sizeunused
------------------------------------------------------------
SyslogsNot avail32KB32KB0KBNot avail
日常要监视的主要目标有：用户数据库、数据库日志表（syslogs）以及计费原始数据表等。如果发现占用空间过大，对日志表要进行转储；对其他目标则应扩充空间或清楚垃圾数据。
3、监视 SQL Server 统计数字
使用系统过程：sp_monitor
说明：sp_monitor 显示SQL Server 的历史统计数字，下表是某系统的统计数字：
Last_runCurrent_runSeconds
---------------------------------------------------------------
May 13 2000 1:27PMMay 13 2000 3:01PM5678
CPU_busyIO_busyIdle
---------------------------------------------------------------
16(6)-0%0(0)-0%5727(5672)-99%
Packets_receivedPackets_sentPacket_errors
---------------------------------------------------------------
21(17)100(97)0(0)
Total_readTotal_writeTotal_errorsConnections
--------------------------------------------------------
785(366)311(113)0(0)3(2)
上表依次给出该系统本次运行统计的上一次时间、本次时间、间隔秒数、CPU占用、IO占用、收发包情况、系统读入写出情况等信息
四、保证系统数据安全，周期更改用户口令；
为保证系统数据的安全，系统管理员必须依据系统的实际情况，执行一系列的安全保障措施。其中，周期性的更改用户口令是比较常用且十分有效的措施。
更改用户口令是通过调用系统过程sp_password 来实现的。Sp_password 的语法为：
sp_password caller_password,new_password [,loginame]
其中caller_password 是登录口令（老口令），new_password是新口令，loginame是登录名称。
聚集索引的区别
聚集索引:物理存储按照索引排序
非聚集索引:物理存储不按照索引排序
优势与缺点
聚集索引：插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）,查询数据比非聚集数据的速度快
聚集索引的区别
聚集索引:物理存储按照索引排序
非聚集索引:物理存储不按照索引排序
优势与缺点
聚集索引：插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）,查询数据比非聚集数据的速度快
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：
非聚集索引
聚集索引
一、索引块与数据块的区别
大家都知道，索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。让我们来算一道数学题：如果表中的一条记录在磁盘上占用1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，SQLServer的最小空间分配单元是“页（Page）”，一个页在磁盘上占用8K空间，那么这一个页可以存储上述记录8条，但可以存储索引800条。现在我们要从一个有8000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多。
二、索引优化技术
是不是有索引就一定检索的快呢？答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录，如果不用索引，需要访问8000条×1000 字节/8K字节=1000个页面，如果使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索所有数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。
SQL Server内部有一套完整的数据检索优化技术，在上述情况下，SQL Server的查询计划（SearchPlan）会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQL Server是怎么知道什么时候用索引，什么时候不用索引的呢？SQL Server除了日常维护数据信息外，还维护着数据统计信息，下图是数据库属性页面的一个截图：
聚簇索引与非聚簇索引的本质区别到底是什么？什么时候用聚簇索引，什么时候用非聚簇索引？
这是一个很复杂的问题，很难用三言两语说清楚。我在这里从SQL Server索引优化查询的角度简单谈谈(如果对这方面感兴趣的话，可以读一读微软出版的《Microsoft SQL Server 2000数据库编程》第3单元的数据结构引论以及第6、13、14单元)。
从图中我们可以看到，SQL Server自动维护统计信息，这些统计信息包括数据密度信息以及数据分布信息，这些信息帮助SQL Server决定如何制定查询计划以及查询是是否使用索引以及使用什么样的索引（这里就不再解释它们到底如何帮助SQL Server建立查询计划的了）。我们还是来做个实验。建立一张表：tabTest(ID, unqValue，intValue)，其中ID是整形自动编号主索引，unqValue是uniqueidentifier类型，在上面建立普通索引，intValue 是整形，不建立索引。之所以挂上一个没有索引的intValue字段，就是防止SQLServer使用索引覆盖查询优化技术，这样实验就起不到作用了。向表中录入10000条随机记录，代码如下：
Code
CREATE TABLE [dbo].[tabTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[unqValue] [uniqueidentifier] NOT NULL ,
[intValue] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] WITH NOCHECK ADD
CONSTRAINT [PK_tabTest] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] ADD
CONSTRAINT [DF_tabTest_unqValue] DEFAULT (newid()) FOR[unqValue]
GO
CREATE INDEX [IX_tabTest_unqValue] ON[dbo].[tabTest]([unqValue]) ON [PRIMARY]
GO
declare @i int
declare @v int
set @i=0
while @i<10000
begin
set @v=rand()*1000
insert into tabTest ([intValue]) values (@v)
set @i=@i+1
end
然后我们执行两个查询并查看执行计划，如图：（在查询分析器的查询菜单中可以打开查询计划，同时图上第一个查询的GUID是我从数据库中找的，大家做实验的时候可以根据自己数据库中的值来定）：
从图中可以看出，在第一个查询中，SQL Server使用了IX_tabTest_unqValue索引，根据箭头方向，计算机先在索引范围内找，找到后，使用Bookmark Lookup将索引节点映射到数据节点上，最后给出SELECT结果。在第二个查询中，系统直接遍历表给出结果，不过它使用了聚簇索引，为什么呢？不要忘了，聚簇索引的页节点就是数据节点！这样使用聚簇索引会更快一些（不受数据删除、更新留下的存储空洞的影响，直接遍历数据是要跳过这些空洞的）。
下面，我们在SQL Server中将ID字段的聚簇索引更改为非聚簇索引，然后再执行select * from tabTest，这回我们看到的执行计划变成了：
SQLServer没有使用任何索引，而是直接执行了Table Scan，因为只有这样，检索效率才是最高的。
三、聚簇索引与非聚簇索引的本质区别
现在可以讨论聚簇索引与非聚簇索引的本质区别了。正如本文最前面的两个图所示，聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引检点，并保留一个链接指向对应数据块。
还是通过一道数学题来看看它们的区别吧：假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节 /8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。
同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。
下面我们看看在执行插入操作时，非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复，那么SQL Server是怎么知道不出现重复的呢？唯一的方法就是检索。对于非聚簇索引，只需要检索20个页面中的16000个节点就知道是否有重复，因为所有主键键值在这16000个索引节点中都包含了。但对于聚簇索引，索引节点仅仅包含了8000个中间节点，至于会不会出现重复必须检索另外8000个页数据节点才知道，那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
让我们再来看看数据检索的效率，如果对上述两表进行检索，在使用索引的情况下（有些时候SQL Server执行计划会选择不使用索引，不过我们这里姑且假设一定使用索引），对于聚簇索引检索，我们可能会访问10个索引页面外加1000个数据页面得到结果（实际情况要比这个好），而对于非聚簇索引，系统会从20个页面中找到符合条件的节点，再映射到1000个数据页面上（这也是最糟糕的情况），比较一下，一个访问了1010个页面而另一个访问了1020个页面，可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。
结语
关于聚簇索引与非聚簇索引效率问题的实验就不做了，感兴趣的话可以自己使用查询分析器对查询计划进行分析。SQL Server是一个很复杂的系统，尤其是索引以及查询优化技术，Oracle就更复杂了。了解索引以及查询背后的事情不是什么坏事，它可以帮助我们更为深刻的了解我们的系统。
-------------------------------------
非聚簇对于更新肯定是有优势的而它在检索的性能损失也不会太大所以能不用聚簇当然是最好的了但是如果使用\order by的话聚簇的优势也应该是很明显的
索引有两种类型：聚簇索引和非聚簇索引。
在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。
在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。
聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。
通常情况下，你使用的是聚簇索引，但是你应该对两种类型索引的优缺点都有所理解。
每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。通常你要对一个表按照标识字段建立聚簇索引。但是，你也可以对其它类型的字段建立聚簇索引，如字符型，数值型和日期时间型字段。
从建立了聚簇索引的表中取出数据要比建立了非聚簇索引的表快。当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。例如，假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息，你应该对这个表的DATETIME型字段建立聚簇索引。
对聚簇索引的主要限制是每个表只能建立一个聚簇索引。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。你也可以对一个表同时建立聚簇索引和非聚簇索引。
假如你不仅想根据日期，而且想根据用户名从你的网点活动日志中取数据。在这种情况下，同时建立一个聚簇索引和非聚簇索引是有效的。你可以对日期时间字段建立聚簇索引，对用户名字段建立非聚簇索引。如果你发现你需要更多的索引方式，你可以增加更多的非聚簇索引。
非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。
索引属性
这两种类型的索引都有两个重要属性：
你可以用两者中任一种类型同时对多个字段建立索引（复合索引）；
两种类型的索引都可以指定为唯一索引。
你可以对多个字段建立一个复合索引，甚至是复合的聚簇索引。假如有一个表记录了你的网点访问者的姓和名字。如果你希望根据完整姓名从表中取数据，你需要建立一个同时对姓字段和名字字段进行的索引。这和分别对两个字段建立单独的索引是不同的。当你希望同时对不止一个字段进行查询时，你应该建立一个对多个字段的索引。如果你希望对各个字段进行分别查询，你应该对各字段建立独立的索引。
两种类型的索引都可以被指定为唯一索引。如果对一个字段建立了唯一索引，你将不能向这个字段输入重复的值。一个标识字段会自动成为唯一值字段，但你也可以对其它类型的字段建立唯一索引。假设你用一个表来保存你的网点的用户密码，你当然不希望两个用户有相同的密码。通过强制一个字段成为唯一值字段，你可以防止这种情况的发生。
http://hi.baidu.com/guobeilei/blog/item/51f55afbda311e116c22eb0e.html
聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。有关聚集索引体系结构的详细信息，请参阅聚集索引结构。
每个表几乎都对列定义聚集索引来实现下列功能：
可用于经常使用的查询。
提供高度唯一性。
注意：
创建 PRIMARY KEY 约束时，将在列上自动创建唯一索引。默认情况下，此索引是聚集索引，但是在创建约束时，可以指定创建非聚集索引。
可用于范围查询。
如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个 4 字节的uniqueifier 列。必要时，数据库引擎将向行自动添加一个 uniqueifier 值以使每个键唯一。此列和列值供内部使用，用户不能查看或访问。
查询注意事项
在创建聚集索引之前，应先了解数据是如何被访问的。考虑对具有以下特点的查询使用聚集索引：
使用运算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。
使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。例如，如果某个查询在一系列销售订单号间检索记录，SalesOrderNumber 列的聚集索引可快速定位包含起始销售订单号的行，然后检索表中所有连续的行，直到检索到最后的销售订单号。
返回大型结果集。
使用 JOIN 子句；一般情况下，使用该子句的是外键列。
使用 ORDER BY 或 GROUP BY子句。
在 ORDER BY 或 GROUP BY子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。
列注意事项
一般情况下，定义聚集索引键时使用的列越少越好。考虑具有下列一个或多个属性的列：
唯一或包含许多不重复的值
例如，雇员 ID 唯一地标识雇员。EmployeeID列的聚集索引或 PRIMARY KEY 约束将改善基于雇员 ID 号搜索雇员信息的查询的性能。另外，可对 LastName、FirstName、MiddleName 列创建聚集索引，因为经常以这种方式分组和查询雇员记录，而且这些列的组合还可提供高区分度。
按顺序被访问
例如，产品 ID 唯一地标识AdventureWorks 数据库的 Production.Product 表中的产品。在其中指定顺序搜索的查询（如
WHERE ProductID BETWEEN 980 and 999
）将从 ProductID 的聚集索引受益。这是因为行将按该键列的排序顺序存储。
由于保证了列在表中是唯一的，所以定义为 IDENTITY。
经常用于对表中检索到的数据进行排序。
按该列对表进行聚集（即物理排序）是一个好方法，它可以在每次查询该列时节省排序操作的成本。
聚集索引不适用于具有下列属性的列：
频繁更改的列
这将导致整行移动，因为数据库引擎必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。
宽键
宽键是若干列或若干大型列的组合。所有非聚集索引将聚集索引中的键值用作查找键。为同一表定义的任何非聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。
索引选项
创建聚集索引时，可指定若干索引选项。因为聚集索引通常都很大，所以应特别注意下列选项：
SORT_IN_TEMPDB
DROP_EXISTING
FILLFACTOR
ONLINE
SQL Server数据库表分区操作过程由三个步骤组成：
1. 创建分区函数
2. 创建分区架构
3. 对表进行分区
下面将对每个步骤进行详细介绍。
步骤一：创建一个分区函数
此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(how)。这个操作并不涉及任何表格，只是单纯的定义了一项技术来分割数据。
我们可以通过指定每个分区的边界条件来定义分区。例如，假定我们有一份Customers表，其中包含了关于所有客户的信息，以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区：
CREATE PARTITION FUNCTION customer_partfunc (int)
AS RANGE RIGHT
FOR VALUES (250000, 500000, 750000)
这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据，第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在500,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。
请注意，这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的，如果使用"RANGELEFT"语句，则上述第一个分区应该包括所有值小于或等于250,000的数据，第二个分区的数据值在250,001到500,000之间，以此类推。
步骤二：创建一个分区架构
一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置(where)。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。例如，如果有四个文件组，组名从"fg1"到"fg4"，那么以下的分区架构就能达到想要的效果：
CREATE PARTITION SCHEME customer_partscheme
AS PARTITION customer_partfunc
TO (fg1, fg2, fg3, fg4)
注意，这里将一个分区函数连接到了该分区架构，但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表，我们都可以使用该分区架构(或仅仅是分区函数)。
步骤三：对一个表进行分区
定义好一个分区架构后，就可以着手创建一个分区表了。这是整个分区操作过程中最简单的一个步骤。只需要在表创建指令中添加一个"ON"语句，用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数，所以不需要再指定分区函数了。
例如，使用以上的分区架构创建一个客户表，可以调用以下的Transact-SQL指令：
CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)
ON customer_partscheme (CustomerNumber)
关于SQL Server的表分区功能，你知道上述的相关知识就足够了。记住!编写能够用于多个表的一般的分区函数和分区架构就能够大大提高可复用性。
数据库性能调优是每一个优秀SQL Server管理员最终的责任。虽然保证数据的安全和可用性是我们的最高的目标，但是假如数据库应用程序无法满足用户的要求，那么DBA们会因为性能低下的设计和实现而受到指责。SQL Server 2005在数据库性能方面得到了很多提高，尤其是表分区的技术。如果你还没不了解表分区的特征，那么请你花点时间读这篇文章。
表分区的概念不是一个新的概念;只要你当过一段时间的SQL Server DBA，那么你可能已经对一些频繁访问的表进行过归档，当这个表中的历史数据变的不再经常被访问的时候。比如，假设你有一个打印时间报表的应用，你的报告很少会查询1995年的数据，因为绝大部分的预算规划会基于最近几年的数据。
在SQL Server的早期版本中，你可以创建多个表。每一个表都具有相同的列结构，用来保存不同年份的数据。这样，当存在着对历史数据访问的必要的时候，你可以创建一个视图来对这些表进行查询处理。将数据保存在多个表中是很方便的，因为相对于查询时扫描整个大表，扫描小表会更快。但是这种好处只有在你预先知道哪些时间段的数据会被访问。同时，一旦数据过期，你还需要创建新表并且转移新产生的历史数据。
SQL Server 7和SQL Server 2000支持分布式分区视图(distributedpartitioned views，又称为物化视图，materialized views).分布式分区视图由分布于多台服务器上的、具有相同表结构的表构成，而且你还需要为每一个服务器增加链接服务器定义(linked serverdefinitions)，最后在其中一台服务器上创建一个视图将每台服务器上返回的数据合并起来。这里的设计思想是数据库引擎可以利用多台服务器的处理能力来满足查询。
但是，分布式分区视图(DPV)受到很多限制，你可以在SQL Server的在线帮助文档中阅读到。虽然DPV在一些情况下能够提供性能上的提高，但是这种技术不能被广泛的应用。已经被证明它们不能满足逐步增长的企业级应用的要求。何况，DPV的实现是一个费力的过程，需要DBA进行很多工作。
SQL Server 2005开始支持表分区，这种技术允许所有的表分区都保存在同一台服务器上。每一个表分区都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个分区表。
在这种设计架构下，数据库引擎能够判定查询过程中应该访问哪个分区，而不用扫描整个表。如果查询需要的数据行分散在多个分区中，SQL Server使用多个处理器对多个分区进行并行查询。你可以为在创建表的时候就定义分区的索引。 对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此，当对大表进行查询，表分区可以产生相当大的性能提升。
现在让我们通过一个简单的例子来了解表分区是如何发挥作用的。在这篇文章中，我不想深入到分区的语法细节当中，这些你可以在SQL Server的在线帮助文档中找到。下面的例子基于存储着一个时间报表系统的数据的数据仓库。除了默认的文件组，我另外创建了7个文件组，每一个文件组仅包含一个文件，这个文件将存储由分区函数定义的一部分数据。
为了测试表分区的性能提升，我向这个分区表中插入了一千五百万行，同时向另外一个具有相同表结构、但是没有进行分区的表插入了同样的数据。对分区表执行的INSERT语句运行的更快一些。甚至在我的内存不到1G的笔记本电脑上，对分区表的INSERT语句比不分区的表的INSERT语句要快上三倍。当然，查询的执行时间依据硬件资源的差异而所有变化，但是你还是能够在你的环境中感到不同程度的提升。
我将检查更深入了一步，通过分别检查同一条返回所有行的、简单SELECT语句在分区表和非分区表上的执行计划，返回的数据范围通过WHERE语句来指定。同一条语句在这两个不同的表上有不同的执行计划。对于分区表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说，SQL Server将两个分区视为独立的表，因此使用一个嵌套循环将它们连接起来。对非分区的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的分区策略创建多个表，同时在查询中连接这些表，那么性能上的提升会更加明显
你可以使用下面的查询来了解每一个分区中的行的个数：
SELECT $PARTITION.TimeEntryDateRangePFN(time_entry_date) AS Partition,
COUNT(*) AS [COUNT] FROM fact_time_entry
GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date)
ORDER BY Partition
表分区对交易环境和数据仓库环境来说，都是一个重要的特征。数据仓库用户最主要的抱怨是移动事实表(fact table)会花费太多时间。当装载数据到事实表的时候，用户查询(立方体处理查询)的性能会明显下降，甚至是完全无法成功。因此，装载大量的数据到事实表的时候常常需要停机。如果使用表分区，就不再出现这样的情况——确切的讲，你一眨眼的工夫就可以移动事实表。为了演示这是如何生效的，我使用上面例子中相同的分区函数和表结构来创建一个新的表，这个表叫做fact_time_entry2。表的主键从五千万开始，这样fact_time_entry2就不会包含表fact_time_entry中已经有的数据。
现在我把2007年的数据移动到这张fact_time_entry2中。同时让我们假设fact_time_entry表中包含着2007年之前的数据。在fact_time_entry2表完成数据的转移，我执行下面的语句：
ALTER TABLE fact_time_entry2
SWITCH PARTITION 8 TO fact_time_entry PARTITION 8
这条语句将编号为8的分区，这个分区恰好包含着2007年的数据，从fact_time_entry2移动到了fact_time_entry表中，在我的笔记本电脑上，这个过程只花费了3毫秒。在这短短的3毫秒中，我的事实表就增加了五百万条记录!的确，我需要在交换分区之前，将数据移动到中间表，但是我的用户不需要担心——事实表随时都可以查询!在这幕后，实际上没有数据移动——只是两张表的元数据发生了变化。
我可以使用类似的查询删除事实表中不在需要的数据。例如，假设我们决定我们不再关心2004年的记录。下面的语句可以将这些记录转移到我们创建的工作表中：
ALTER TABLE fact_time_entry
SWITCH PARTITION 2 TO fact_time_entry2 PARTITION 2
这样的语句依旧在毫秒级内完成了。现在，我可以删除fact_time_entry2或者将它移到其他的服务器上。我的事实表不会包含2004年的任何记录。这个分区还是需要在目的表中存在，而且它必须是空的。你不能将分区转移到一个包含重复数据的表中。源表和目的表的分区必须一致，同时被转移的数据必须在同一个文件组中。即使受到这么多的限制，转换分区和无需停机就可以移动数据表的功能必将让数据仓库的实现变的前所未有的轻松。
SQL Server 表分区(partitioned table/Data Partitioning)
Partitioned Table
可伸缩性性是数据库管理系统的一个很重要的方面，在SQL Server 2005中可伸缩性方面提供了表分区功能。
其实对于有关系弄数据库产品来说，对表、数据库和服务器进行数据分区的从而提供大数据量的支持并不是什么新鲜事，但 SQL Server 2005 提供了一个新的体系结构功能，用于对数据库中的文件组进行表分区。水平分区可根据分区架构，将一个表划分为几个较小的分组。表分区功能是针对超大型数据库（从数百吉字节到数千吉字节或更大）而设计的。超大型数据库 (VLDB) 查询性能通过分区得到了改善。通过对广大分区列值进行分区，可以对数据的子集进行管理，并将其快速、高效地重新分配给其他表。
设想一个大致的电子交易网站，有一个表存储了此网站的历史交易数据，这此数据量可能有上亿条，在以前的SQL Server版本中存储在一个表中不管对于查询性能还是维护都是件麻烦事，下面我们来看一下在SQL Server2005怎么提高性能和可管理性：
-- 创建要使用的测试数据库，Demo
USE [master]
IF EXISTS (SELECT name FROMmaster.dbo.sysdatabases WHERE name = N'DEMO')
DROP DATABASE [DEMO]
CREATE DATABASE [DEMO]
--由于表分区使用使用新的体系结构，使用文件组来进行表分区，所以我们创建将要用到的6个文件组，来存储6个时间段的交易数据[<2000],[2001], [2002], [2003], [2004], [>2005]
ALTER DATABASE Demo ADD FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILEGROUP YEARFG6;
-- 下面为这些文件组添加文件来进行物理的数据存储
ALTER DATABASE Demo ADD FILE (NAME ='YEARF1', FILENAME = 'C:\ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF2', FILENAME = 'C:\ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF3', FILENAME = 'C:\ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF4', FILENAME = 'C:\ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF5', FILENAME = 'C:\ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILE (NAME ='YEARF6', FILENAME = 'C:\ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;
-- HERE WE ASSOCIATE THE PARTITION FUNCTIONTO
-- THE CREATED FILEGROUP VIA A PARTITIONINGSCHEME
USE DEMO;
GO
-------------------------------------------------------
-- 创建分区函数
-------------------------------------------------------
CREATE PARTITION FUNCTION YEARPF(datetime)
AS
RANGE LEFT FOR VALUES ('01/01/2000'
,'01/01/2001'
,'01/01/2002'
,'01/01/2003'
,'01/01/2004')
-------------------------------------------------------
-- 创建分区架构
-------------------------------------------------------
CREATE PARTITION SCHEME YEARPS
AS PARTITION YEARPF TO (YEARFG1,YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6)
-- 创建使用此Schema的表
CREATE TABLE PARTITIONEDORDERS
(
ID INT NOT NULL IDENTITY(1,1),
DUEDATE DATETIME NOT NULL,
) ON YEARPS(DUEDATE)
--为此表填充数据
declare @DT datetime
SELECT @DT = '1999-01-01'
--start looping, stop at ending date
WHILE (@DT <= '2005-12-21')
BEGIN
INSERT INTO PARTITIONEDORDERS VALUES(@DT)
SET @DT=dateadd(yy,1,@DT)
END
-- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition
SELECT *, $PARTITION.YEARPF(DUEDATE) FROMPARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID= OBJECT_ID('PARTITIONEDORDERS')
--
--现在我们设想一下，如果我们随着时间的流逝，现在已经到了2005年，按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就看如果新加一个分区。
--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区YEARFG6分区中，这样此分区就存储了两段partition的数据。
ALTER PARTITION SCHEME YEARPS
NEXT USED YEARFG6;
--更改分区函数
ALTER PARTITION FUNCTION YEARPF()
SPLIT RANGE ('01/01/2005')
--现在我们可以看一下我们刚才插入的行都分布在哪个Partition？
SELECT *, $PARTITION.YEARPF(DUEDATE) FROMPARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID= OBJECT_ID('PARTITIONEDORDERS')
这就是对数据库的横向分割，把一个大表表分割成不同的小表，并把小表并分步到不同的物理文件（由不同的物理磁盘构成）组上。这是做海量数据查询的基础理论，sql2000以前不支持这个技术，不过有个分区视图有类似的功能。但是sql2005直接有这个功能，我想ax应该很快会支持这个功能。
其实，googl的查询原理也是这样的，它把每次查询同时提交给googl的50万台服务器，每个服务器上只保留了不多的信息，这个每个服务器能很快的返回自己找到的数据，并提供给集群服务器整理排序后显示给用户。理论上讲，若有经济条件，把数据分割成无限多个节点，这样每个节点查询自己的数据时的速度几乎是不耗时的，这样速度就会大大的加快
深入浅出理解索引结构
(一)深入浅出理解索引结构
实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引(clustered index，也称聚类索引、簇集索引)和非聚集索引(nonclustered index，也称非聚类索引、非簇集索引)。下面，我们举例来说明一下聚集索引和非聚集索引的区别：
其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字;同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字，您可以快速地从自典中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。
我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。
进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
(二)何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引(很重要)。
动作描述
使用聚集索引
使用非聚集索引
事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
(三)结合实际，谈索引使用的误区
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。
1、主键就是聚集索引
这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。
在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条)：
(1)仅在主键上建立聚集索引，并且不划分时间段：
Select gid,fariqi,neibuyonghu,title fromtgongwen
用时：128470毫秒(即：128秒)
(2)在主键上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi> dateadd(day,-90,getdate())
用时：53763毫秒(54秒)
(3)将聚合索引建立在日期列(fariqi)上：
select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi> dateadd(day,-90,getdate())
用时：2423毫秒(2秒)
虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。
得出以上速度的方法是：在各个select语句前加：
declare @d datetime
set @d=getdate()
并在select语句后加：
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
2、只要建立索引就能显著提高查询速度
事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。
从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。
3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度
上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引(compound index)。
很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗?带着这个问题，我们来看一下以下的查询速度(结果集都是25万条数据)：(日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列)
(1)select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi>'2004-5-5'
查询速度：2513毫秒
(2)select gid,fariqi,neibuyonghu,title fromTgongwen
where fariqi>'2004-5-5' and neibuyonghu='办公室'
查询速度：2516毫秒
(3)select gid,fariqi,neibuyonghu,title fromTgongwen
where neibuyonghu='办公室'
查询速度：60280毫秒
从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。
(四)其他书上没有的索引使用经验总结
1、用聚合索引比用不是聚合索引的主键速度快
下面是实例语句：(都是提取25万条数据)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
使用时间：3326毫秒
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen where gid<=250000
使用时间：4470毫秒
这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。
2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen order by fariqi
用时：12936
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen order by gid
用时：18843
这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话，如10万以上，则二者的速度差别不明显。
3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-1-1'
用时：6343毫秒(提取100万条)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-6-6'
用时：3170毫秒(提取50万条)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
用时：3326毫秒(和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的)
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-1-1' andfariqi<'2004-6-6'
用时：3280毫秒
4 、日期列不会因为有分秒的输入而减慢查询速度
下面的例子中，共有100万条数据，2004年1月1日以后的数据有50万条，但只有两个不同的日期，日期精确到日;之前有数据50万条，有5000个不同的日期，日期精确到秒。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi>'2004-1-1' order by fariqi
用时：6390毫秒
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi<'2004-1-1' order by fariqi
用时：6453毫秒
(五)其他注意事项
“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。过多的索引甚至会导致索引碎片。
索引是从数据库中获取数据的最高效方式之一。95%的数据库性能问题都可以采用索引技术得到解决。
1. 不要索引常用的小型表
不要为小型数据表设置任何键，假如它们经常有插入和删除操作就更别这样作了。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。
2. 不要把社会保障号码(SSN)或身份证号码(ID)选作键
永远都不要使用 SSN 或 ID 作为数据库的键。除了隐私原因以外，SSN 或 ID 需要手工输入。永远不要使用手工输入的键作为主键，因为一旦你输入错误，你唯一能做的就是删除整个记录然后从头开始。
3. 不要用用户的键
在确定采用什么字段作为表的键的时候，可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。这样做会迫使你采取以下两个措施：
4. 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)
这样做会让你的索引占据大量的数据库空间
5. 使用系统生成的主键
假如你总是在设计数据库的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问。
采用系统生成键作为主键还有一个优点：当你拥有一致的键结构时，找到逻辑缺陷很容易
二、改善SQL语句
很多人不知道SQL语句在SQLSERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。比如：
select * from table1 where name='zhangsan'and tID > 10000
和执行:
select * from table1 where tID > 10000 andname='zhangsan'
一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。
事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。
虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。
在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数(SARG)，那么就称之为可优化的，并且可以利用索引快速获得所需数据。
SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下：
列名 操作符 <常数 或 变量>
或
<常数或 变量> 操作符列名
列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：
Name=’张三’
价格>5000
5000<价格
Name=’张三’ and 价格>5000
如果一个表达式不能满足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、非操作符、函数引起的不满足SARG形式的语句
不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。下面就是几个不满足SARG形式的例子：
ABS(价格)<5000
Name like ‘%三’
有些表达式，如：
WHERE 价格*2>5000
SQL SERVER也会认为是SARG，SQLSERVER会将此式转化为：
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数据库。运行前我们可以把SQLSERVER的statistics I/O状态打开。
(1)select title,price from titles wheretitle_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 whereexists
(select * from sales wheresales.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,titlefrom Tgongwen
where fariqi='2004-9-16' or gid>9990000
用时：68秒。扫描计数1，逻辑读 404008 次，物理读 283 次，预读 392163 次。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen where gid>9990000
用时：9秒。扫描计数8，逻辑读 67489 次，物理读 216 次，预读 7499 次。
看来，用union在通常情况下比用or的效率要高的多。
但经过试验，笔者发现如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16' or fariqi='2004-2-5'
用时：6423毫秒。扫描计数2，逻辑读 14726 次，物理读 1 次，预读7176 次。
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,titlefrom Tgongwen
where fariqi='2004-2-5'
用时：11640毫秒。扫描计数 8，逻辑读14806 次，物理读 108 次，预读 1144 次。
9、字段提取要按照“需多少、提多少”的原则，避免“select *”
我们来做一个试验：
select top 10000 gid,fariqi,reader,title fromtgongwen order by gid desc
用时：4673毫秒
select top 10000 gid,fariqi,title fromtgongwen order by gid desc
用时：1376毫秒
select top 10000 gid,fariqi from tgongwenorder 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 fromtgongwen
用时：196 毫秒。扫描计数 1，逻辑读289 次，物理读 1 次，预读 1527 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by gid asc
用时：4720毫秒。扫描计数 1，逻辑读41956 次，物理读 0 次，预读 1287 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by gid desc
用时：4736毫秒。扫描计数 1，逻辑读55350 次，物理读 10 次，预读 775 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by fariqi asc
用时：173毫秒。扫描计数 1，逻辑读290 次，物理读 0 次，预读 0 次。
select top 10000 gid,fariqi,reader,title fromtgongwen order by fariqi desc
用时：156毫秒。扫描计数 1，逻辑读289 次，物理读 0 次，预读 0 次。
从以上我们可以看出，不排序的速度以及逻辑读次数都是和“order by 聚集索引列” 的速度是相当的，但这些都比“orderby 非聚集索引列”的查询速度是快得多的。
同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。
12、高效的TOP
事实上，在查询和提取超大容量的数据集时，影响数据库响应时间的最大因素不是数据查找，而是物理的I/0操作。如：
select top 10 * from (
select top 10000 gid,fariqi,title fromtgongwen
where neibuyonghu='办公室'order by gid desc) as a
order by gid asc
这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是10000条记录，而整条语句仅返回10条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQLSERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用，发现TOP确实很好用，效率也很高。但这个词在另外一个大型数据库ORACLE中却没有，这不能说不是一个遗憾，虽然在ORACLE中可以用其他方法(如：rownumber)来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中，我们就将用到TOP这个关键词。
到此为止，我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然，我们介绍的这些方法都是“软”方法，在实践中，我们还要考虑各种“硬”因素，如：网络性能、服务器的性能、操作系统的性能，甚至网卡、交换机等。
写出性能优良的SQL
我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享！
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：
ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersectiontable)作为基础表, 交叉表是指那个被其他表所引用的表.
（2）WHERE子句中的连接顺序．：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
（3）SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名,这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
（4）减少访问数据库的次数：
ORACLE在内部执行了许多工作: 解析SQL语句,估算索引的利用率, 绑定变量, 读数据块等；
（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量,建议值为200
（6）使用DECODE函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
（7）整合简单,无关联的数据库访问：
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
（8）删除重复记录：
最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
（9）用TRUNCATE替代DELETE：
当删除表中的记录时,在通常情况下,回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
（10）尽量多使用COMMIT：
只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
（11）用Where子句替换HAVING子句：
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.(非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里
（12）减少对表的查询：
在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
（13）通过内部函数提高SQL效率.：
复杂的SQL往往牺牲了执行效率.能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
（14）使用表的别名(Alias)：
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
（15）用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOTIN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROMDEPT WHERE LOC = ‘MELB')
（16）识别'低效执行'的SQL语句：
虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
（17）用索引提高效率：
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列.通常, 在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT, DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
18） 用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
（19）sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行
（20）在java代码中尽量少用连接符“＋”连接字符串！
（21）避免在索引列上使用NOT 通常，
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
（22）避免在索引列上使用计算．
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例:
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
（23）用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
（24）用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意, 以上规则只针对多个索引列有效.如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中,LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
（25）用IN来替换OR
这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
（26）避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入).然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
（27）总是使用索引的第一个列：
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
28） 用UNION-ALL 替换UNION ( 如果有可能的话)：
当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNIONALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的.下面的SQL可以用来查询排序的消耗量
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
（29）用WHERE替代ORDER BY：
ORDER BY 子句只在两种严格的条件下使用索引.
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
低效: (索引不被使用)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
高效: (使用索引)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
（30）避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
SELECT … FROM EMP WHERE EMPNO = ‘123'
实际上,经过ORACLE类型转换, 语句转化为:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
（31）需要当心的WHERE子句:
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
在下面的例子里, (1)‘!=' 将不使用索引. 记住,索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘ ¦ ¦'是字符连接函数. 就象其他函数那样,停用了索引. (3) ‘+'是数学函数. 就象其他数学函数那样,停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.
（32）a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.
b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
（33）避免使用耗费资源的操作:
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS ,INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION, MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
（34）优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUPBY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP by JOB
SQL Server 存储过程的分页
SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
建立表：
CREATE TABLE[TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON
declare @iint
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName,Country,Note) values(@i,'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end
SETIDENTITY_INSERT TestTable OFF
-------------------------------------
分页方案一：(利用NotIn和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数id
FROM 表
ORDER BY id))
ORDER BY ID
-------------------------------------
分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
-------------------------------------
分页方案三：(利用SQL的游标存储过程分页)
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcountoutput
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpageas 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。
通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用NotIn和SELECT TOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用
在实际情况中，要具体分析。
数据库日常维护(参考)
数据库日常维护工作是系统管理员的重要职责。其内容主要包括以下几个部分：
一、备份系统数据
SYBASE 系统的备份与恢复机制保证了在系统失败时重新获取数据的可能性。SQL Server 提供了两种不同类型的恢复机制：一类是系统自动完成的恢复，这种措施在每次系统启动时都自动进行，保证了在系统瘫痪前完成的事务都写到数据库设备上，而未完成的事务都被回退；另一类是人工完成的恢复，这是通过 DUMP 和 LOAD 命令来执行人工备份和恢复工作。因此定期备份事务日志和数据库是一项十分重要的日常维护工作。
1、备份数据库
每一个数据库都应在创建之后卸出，从而提供一个装入基点。在此之后按排定的时间周期表卸出。比如每周五卸出数据库。对一般数据库系统卸出数据库周期建议为每周一次。
除了按计划周期卸出数据库之外，还需在每次运行没有日志的操作后卸出数据库。例如：
·每次强制地运行了 DUMP TRAN WITH NO_LOG （因为数据库的磁盘空溢出）；
·每次用 sp_dboption 允许 select into/bulkcopy 做快速拷贝，或用 SELECT INTO 命令创建一个永久性的表，或使用了 WRITETEXT 命令。
卸出数据库的命令为：
DUMP DATABASE database_name
TO dump_device
database_name 是要卸出的数据库名称，dump_device 是卸出设备的名称。用系统过程 sp_helpdevice 可以获得设备的信息。
下面一条命令用来卸出数据库 my_db ：
DUMP DATABASE my_db
TO db_bk_dev
2、备份事务日志
如果事务日志与数据库放在同一个设备上，则事务日志不应与数据库分开备份。master 数据库和小于 4M 的用户数据库就是这种情况。一般数据库系统的数据库和日志分别放在不同的设备上，因此，可以用 DUMP TRAN 命令单独备份日志。
备份事务日志的周期直接影响数据的恢复程度，因此建议每天备份。
备份事务日志的命令格式为：
DUMP TRANsaction database_name
[TO dump_device]
[WITH TRUNCATE_ONLY|WITH NO_LOG|WITH NO_TRUNCATE]
其中 database_name 是要备份事务的数据库名称，dump_device 是备份设备名称，仅当包含了 WITH TRUNCATE_ONLY 或 WITH NO_LOG 子句时，才可以备份到设备。
注意：如果总是用 DUMP DATEBASE （备份数据库及其日志），而不用 DUMP TRAN ，事务日志将不会刷新，而变得非常庞大。
对于 master 数据库和小型数据库每次运行 DUMP DATEBASE 之后应当运行 DUMP TRANsaction 命令刷新日志 。
下面一条命令备份数据库 db160 的事务日志到备份设备上：
DUMP TRANsaction db160
TO db_log_bk_dev
WITH TRUNCATE_ONLY
3、备份数据库及其日志间的相互作用
在至少卸出一次数据库前，卸出事务日志是毫无意义的。下图显示了备份数据库及其日志间的关系
如果在星期二下午5：01出现非硬件故障，需要做的所有工作是装入磁带5（参见下一节：数据恢复），由于磁带5是下午5：00刚备份的，因此只有备份和装入之间的一分钟内的数据损失。
但是，如果在星期二下午4：49失效会怎么样呢？在这种情况下，要装入磁带1（在星期五下午5：00的卸出）。然后，依次装入磁带2，3以及4。这样，系统将恢复到星期二上午10：00点的状态，星期二的大部分工作丢失了。此例显示了经常卸出事务的重要性。
二、万一系统失败时恢复数据库系统
如果用户数据库存储的设备失效，从而数据库被破坏或不可存取，通过装入最新的数据库备份以及后来的事务日志备份可以恢复数据库。假设当前的事务日志存在于一个并没有毁坏的设备上，带着 WITH NO_TRUNCATE 选项的 DUMP TRANsaction 命令卸出它。
要恢复数据库按如下步骤去做：
1、如果日志存在于一个分离的设备上，用带着 NO_TRUNCATE 选项的 DUMP TRANsaction 命令卸出被毁坏的或者不可存取的用户数据库事务日志。
2、用下面的查询检查设备分配已毁坏数据库的设备使用情况。必须为同一目的赋同样的空间块。
下面的查询显示了分配给数据库 mydb 设备使用和尺寸情况：
SELECT segmap，size FROM sysusages
WHERE dbid =
（ SELECT dbid FROM sysdatabases WHERE name =“mydb”）
3、检查查询的输出。在 segmap 列的 ‘3’代表数据分配，‘4’代表日志分配。size 列代表 2K 数据块的数目。注意此信息的次序、使用和尺寸部分。例如，输出为：
segmapSize
--------------------
310240//实际尺寸为：20M
35120//实际尺寸为：10M
45120//实际尺寸为：10M
31024//实际尺寸为：2M
42048//实际尺寸为：4M
4、用 DROP DATABASE 命令删除毁坏设备上的数据库。如果系统报错，用DBCC DBREPAIR 命令的 DROPDB 选项。
5、删除数据库后，用 sp_dropdevice 删除毁坏了的设备。
6、用 DISK INIT 初始化新的数据库设备。
7、重建数据库。用 CREATE DATABASE 命令从老的 sysusages 表拷贝所有的行，并包含第一逻辑设备。
对上例，命令为：
CREATE DATABASE mydb
ON datadev1=20，datadev2=10
LOG ON logdev1=10
8、用 ALTER DATABASE 命令重建其余入口。在此例中，在datadev1上分配更多的空间，命令为：
ALTER DATABASE mydb ON datadev1=2[page]
9、用 LOAD DATABASE 重新装入数据库，然后用 LOAD TRAN 装入前面卸出的日志。
LOAD DATABASE 命令语法是：
LOAD DATABASE database_name
FROM dump_device
LOAD TRANsaction 命令的语法是：
LOAD TRANsaction database_name
FROM dump_device
卸出数据库和事务日志的缺省权限归数据库所有者，且可以传递给其他用户；装载数据库和事务的权限也归数据库所有者，但不能传递。
二、产生用户信息表，并为信息表授权；
系统维护人员的另一个日常事务是为用户创建新的信息表，并为之授权。创建表以及为表授权的方法已经在讲过，在此只将有关命令语法写出来。
·创建表的命令为：
CREATE TABLE table_name
（ column_1 datatype [NULL | NOT NULL |IDENTITY]，
column_2 ……
）
go
ALTER TABLE table_name
ADD PRIMARY KEY （column_list）
go
·删除表的命令格式为：
DROP TABLE table_name
go
·为表授权的命令格式为：
GRANT {ALL|permission_list}
ON table_name TO user_name
go
·收回权限的命令格式为
REVOKE {ALL|permission_list}
ON table_name FROM user_name
go
三、监视系统运行状况，及时处理系统错误；
系统管理员的另一项日常工作是监视系统运行情况。主要有以下几个方面：
1、监视当前用户以及进程的信息
使用系统过程：sp_who
说明：该命令显示当前系统所有注册用户及进程信息，如下表是某系统的信息。
SpidStatusLoginamehostnameblkdbnamecmd
---------------------------------------------------------------
1RunningSascosysv0MasterSELECT
2SleepingNULL0MasterNETWORK HANDLE
3SleepingNULL0MasterDEADLOCK TUNE
4SleepingNULL0MasterMIRROR HANDLER
5SleepingNULL0MasterHOUSEKEEPER
6SleepingNULL0MasterCHECKPOINT SLEEP
从左向右依次显示：进程号、当前状态、注册用户名、主机名、占用块数、数据库名以及当前命令。
如果监视时发现进程总数接近最大连接数（用系统过程：sp_configure “userconn” 查看）时，应下掉不活动或无关进程，以保证系统正常运做；另外亦可监视非法用户或用户使用不属于自己使用范围的数据库等情况。
2、监视目标占用空间情况
使用系统过程：sp_spaceused
说明：该过程显示行数、数据页数以及当前数据库中由某个目标或所有目标所占用的空间。如下表是某数据库日志表的信息：
NameRow_totalreserveddataIndex_sizeunused
------------------------------------------------------------
SyslogsNot avail32KB32KB0KBNot avail
日常要监视的主要目标有：用户数据库、数据库日志表（syslogs）以及计费原始数据表等。如果发现占用空间过大，对日志表要进行转储；对其他目标则应扩充空间或清楚垃圾数据。
3、监视 SQL Server 统计数字
使用系统过程：sp_monitor
说明：sp_monitor 显示SQL Server 的历史统计数字，下表是某系统的统计数字：
Last_runCurrent_runSeconds
---------------------------------------------------------------
May 13 2000 1:27PMMay 13 2000 3:01PM5678
CPU_busyIO_busyIdle
---------------------------------------------------------------
16(6)-0%0(0)-0%5727(5672)-99%
Packets_receivedPackets_sentPacket_errors
---------------------------------------------------------------
21(17)100(97)0(0)
Total_readTotal_writeTotal_errorsConnections
--------------------------------------------------------
785(366)311(113)0(0)3(2)
上表依次给出该系统本次运行统计的上一次时间、本次时间、间隔秒数、CPU占用、IO占用、收发包情况、系统读入写出情况等信息
四、保证系统数据安全，周期更改用户口令；
为保证系统数据的安全，系统管理员必须依据系统的实际情况，执行一系列的安全保障措施。其中，周期性的更改用户口令是比较常用且十分有效的措施。
更改用户口令是通过调用系统过程sp_password 来实现的。Sp_password 的语法为：
sp_password caller_password,new_password [,loginame]
其中caller_password 是登录口令（老口令），new_password是新口令，loginame是登录名称。
聚集索引的区别
聚集索引:物理存储按照索引排序
非聚集索引:物理存储不按照索引排序
优势与缺点
聚集索引：插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）,查询数据比非聚集数据的速度快
聚集索引的区别
聚集索引:物理存储按照索引排序
非聚集索引:物理存储不按照索引排序
优势与缺点
聚集索引：插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）,查询数据比非聚集数据的速度快
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：
非聚集索引
聚集索引
一、索引块与数据块的区别
大家都知道，索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。让我们来算一道数学题：如果表中的一条记录在磁盘上占用1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，SQLServer的最小空间分配单元是“页（Page）”，一个页在磁盘上占用8K空间，那么这一个页可以存储上述记录8条，但可以存储索引800条。现在我们要从一个有8000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多。
二、索引优化技术
是不是有索引就一定检索的快呢？答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录，如果不用索引，需要访问8000条×1000 字节/8K字节=1000个页面，如果使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索所有数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。
SQL Server内部有一套完整的数据检索优化技术，在上述情况下，SQL Server的查询计划（SearchPlan）会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQL Server是怎么知道什么时候用索引，什么时候不用索引的呢？SQL Server除了日常维护数据信息外，还维护着数据统计信息，下图是数据库属性页面的一个截图：
聚簇索引与非聚簇索引的本质区别到底是什么？什么时候用聚簇索引，什么时候用非聚簇索引？
这是一个很复杂的问题，很难用三言两语说清楚。我在这里从SQL Server索引优化查询的角度简单谈谈(如果对这方面感兴趣的话，可以读一读微软出版的《Microsoft SQL Server 2000数据库编程》第3单元的数据结构引论以及第6、13、14单元)。
从图中我们可以看到，SQL Server自动维护统计信息，这些统计信息包括数据密度信息以及数据分布信息，这些信息帮助SQL Server决定如何制定查询计划以及查询是是否使用索引以及使用什么样的索引（这里就不再解释它们到底如何帮助SQL Server建立查询计划的了）。我们还是来做个实验。建立一张表：tabTest(ID, unqValue，intValue)，其中ID是整形自动编号主索引，unqValue是uniqueidentifier类型，在上面建立普通索引，intValue 是整形，不建立索引。之所以挂上一个没有索引的intValue字段，就是防止SQLServer使用索引覆盖查询优化技术，这样实验就起不到作用了。向表中录入10000条随机记录，代码如下：
Code
CREATE TABLE [dbo].[tabTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[unqValue] [uniqueidentifier] NOT NULL ,
[intValue] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] WITH NOCHECK ADD
CONSTRAINT [PK_tabTest] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] ADD
CONSTRAINT [DF_tabTest_unqValue] DEFAULT (newid()) FOR[unqValue]
GO
CREATE INDEX [IX_tabTest_unqValue] ON[dbo].[tabTest]([unqValue]) ON [PRIMARY]
GO
declare @i int
declare @v int
set @i=0
while @i<10000
begin
set @v=rand()*1000
insert into tabTest ([intValue]) values (@v)
set @i=@i+1
end
然后我们执行两个查询并查看执行计划，如图：（在查询分析器的查询菜单中可以打开查询计划，同时图上第一个查询的GUID是我从数据库中找的，大家做实验的时候可以根据自己数据库中的值来定）：
从图中可以看出，在第一个查询中，SQL Server使用了IX_tabTest_unqValue索引，根据箭头方向，计算机先在索引范围内找，找到后，使用Bookmark Lookup将索引节点映射到数据节点上，最后给出SELECT结果。在第二个查询中，系统直接遍历表给出结果，不过它使用了聚簇索引，为什么呢？不要忘了，聚簇索引的页节点就是数据节点！这样使用聚簇索引会更快一些（不受数据删除、更新留下的存储空洞的影响，直接遍历数据是要跳过这些空洞的）。
下面，我们在SQL Server中将ID字段的聚簇索引更改为非聚簇索引，然后再执行select * from tabTest，这回我们看到的执行计划变成了：
SQLServer没有使用任何索引，而是直接执行了Table Scan，因为只有这样，检索效率才是最高的。
三、聚簇索引与非聚簇索引的本质区别
现在可以讨论聚簇索引与非聚簇索引的本质区别了。正如本文最前面的两个图所示，聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引检点，并保留一个链接指向对应数据块。
还是通过一道数学题来看看它们的区别吧：假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节 /8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。
同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。
下面我们看看在执行插入操作时，非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复，那么SQL Server是怎么知道不出现重复的呢？唯一的方法就是检索。对于非聚簇索引，只需要检索20个页面中的16000个节点就知道是否有重复，因为所有主键键值在这16000个索引节点中都包含了。但对于聚簇索引，索引节点仅仅包含了8000个中间节点，至于会不会出现重复必须检索另外8000个页数据节点才知道，那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
让我们再来看看数据检索的效率，如果对上述两表进行检索，在使用索引的情况下（有些时候SQL Server执行计划会选择不使用索引，不过我们这里姑且假设一定使用索引），对于聚簇索引检索，我们可能会访问10个索引页面外加1000个数据页面得到结果（实际情况要比这个好），而对于非聚簇索引，系统会从20个页面中找到符合条件的节点，再映射到1000个数据页面上（这也是最糟糕的情况），比较一下，一个访问了1010个页面而另一个访问了1020个页面，可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。
结语
关于聚簇索引与非聚簇索引效率问题的实验就不做了，感兴趣的话可以自己使用查询分析器对查询计划进行分析。SQL Server是一个很复杂的系统，尤其是索引以及查询优化技术，Oracle就更复杂了。了解索引以及查询背后的事情不是什么坏事，它可以帮助我们更为深刻的了解我们的系统。
-------------------------------------
非聚簇对于更新肯定是有优势的而它在检索的性能损失也不会太大所以能不用聚簇当然是最好的了但是如果使用\order by的话聚簇的优势也应该是很明显的
索引有两种类型：聚簇索引和非聚簇索引。
在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。
在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。
聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。
通常情况下，你使用的是聚簇索引，但是你应该对两种类型索引的优缺点都有所理解。
每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。通常你要对一个表按照标识字段建立聚簇索引。但是，你也可以对其它类型的字段建立聚簇索引，如字符型，数值型和日期时间型字段。
从建立了聚簇索引的表中取出数据要比建立了非聚簇索引的表快。当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。例如，假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息，你应该对这个表的DATETIME型字段建立聚簇索引。
对聚簇索引的主要限制是每个表只能建立一个聚簇索引。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。你也可以对一个表同时建立聚簇索引和非聚簇索引。
假如你不仅想根据日期，而且想根据用户名从你的网点活动日志中取数据。在这种情况下，同时建立一个聚簇索引和非聚簇索引是有效的。你可以对日期时间字段建立聚簇索引，对用户名字段建立非聚簇索引。如果你发现你需要更多的索引方式，你可以增加更多的非聚簇索引。
非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。
索引属性
这两种类型的索引都有两个重要属性：
你可以用两者中任一种类型同时对多个字段建立索引（复合索引）；
两种类型的索引都可以指定为唯一索引。
你可以对多个字段建立一个复合索引，甚至是复合的聚簇索引。假如有一个表记录了你的网点访问者的姓和名字。如果你希望根据完整姓名从表中取数据，你需要建立一个同时对姓字段和名字字段进行的索引。这和分别对两个字段建立单独的索引是不同的。当你希望同时对不止一个字段进行查询时，你应该建立一个对多个字段的索引。如果你希望对各个字段进行分别查询，你应该对各字段建立独立的索引。
两种类型的索引都可以被指定为唯一索引。如果对一个字段建立了唯一索引，你将不能向这个字段输入重复的值。一个标识字段会自动成为唯一值字段，但你也可以对其它类型的字段建立唯一索引。假设你用一个表来保存你的网点的用户密码，你当然不希望两个用户有相同的密码。通过强制一个字段成为唯一值字段，你可以防止这种情况的发生。
http://hi.baidu.com/guobeilei/blog/item/51f55afbda311e116c22eb0e.html
聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。有关聚集索引体系结构的详细信息，请参阅聚集索引结构。
每个表几乎都对列定义聚集索引来实现下列功能：
可用于经常使用的查询。
提供高度唯一性。
注意：
创建 PRIMARY KEY 约束时，将在列上自动创建唯一索引。默认情况下，此索引是聚集索引，但是在创建约束时，可以指定创建非聚集索引。
可用于范围查询。
如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个 4 字节的uniqueifier 列。必要时，数据库引擎将向行自动添加一个 uniqueifier 值以使每个键唯一。此列和列值供内部使用，用户不能查看或访问。
查询注意事项
在创建聚集索引之前，应先了解数据是如何被访问的。考虑对具有以下特点的查询使用聚集索引：
使用运算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。
使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。例如，如果某个查询在一系列销售订单号间检索记录，SalesOrderNumber 列的聚集索引可快速定位包含起始销售订单号的行，然后检索表中所有连续的行，直到检索到最后的销售订单号。
返回大型结果集。
使用 JOIN 子句；一般情况下，使用该子句的是外键列。
使用 ORDER BY 或 GROUP BY子句。
在 ORDER BY 或 GROUP BY子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。
列注意事项
一般情况下，定义聚集索引键时使用的列越少越好。考虑具有下列一个或多个属性的列：
唯一或包含许多不重复的值
例如，雇员 ID 唯一地标识雇员。EmployeeID列的聚集索引或 PRIMARY KEY 约束将改善基于雇员 ID 号搜索雇员信息的查询的性能。另外，可对 LastName、FirstName、MiddleName 列创建聚集索引，因为经常以这种方式分组和查询雇员记录，而且这些列的组合还可提供高区分度。
按顺序被访问
例如，产品 ID 唯一地标识AdventureWorks 数据库的 Production.Product 表中的产品。在其中指定顺序搜索的查询（如
WHERE ProductID BETWEEN 980 and 999
）将从 ProductID 的聚集索引受益。这是因为行将按该键列的排序顺序存储。
由于保证了列在表中是唯一的，所以定义为 IDENTITY。
经常用于对表中检索到的数据进行排序。
按该列对表进行聚集（即物理排序）是一个好方法，它可以在每次查询该列时节省排序操作的成本。
聚集索引不适用于具有下列属性的列：
频繁更改的列
这将导致整行移动，因为数据库引擎必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。
宽键
宽键是若干列或若干大型列的组合。所有非聚集索引将聚集索引中的键值用作查找键。为同一表定义的任何非聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。
索引选项
创建聚集索引时，可指定若干索引选项。因为聚集索引通常都很大，所以应特别注意下列选项：
SORT_IN_TEMPDB
DROP_EXISTING
FILLFACTOR
ONLINE
分享到：
发表评论
相关推荐
SQL Server数据库性能优化 _数据库技巧_IT...SQL Server 数据库 性能优化 查询,SQL Server数据库性能优化,影响SQL Server数据库性能的一些因素及SQL Server进行性能优化的原理，并且提出了一些指导性的原则来优化...
SQLServer数据库性能优化[借鉴].pdf
超级全面的SQLserver2008 数据库性能优化 方法
为最有效地优化 Microsoft® SQL Server™ 2000 的性能，必须在极为多样化的情形中识别出会使性能提升最多的区域，并对这些区域集中分析。 虽然其它系统级性能问题（如内存、硬件等）也是研究对象，但经验表明从这些...
关于SQLSERVER数据库性能优化分析的论文
SQL Server数据库性能优化之SQL语句篇
SQL Server数据库性能优化.pdf
优化SQLServer数据库的50种方法
SQL Server数据库性能优化.doc
经典的SQLServer数据库性能优化技术
SQL Server数据库性能优化
医院HIS系统SQLServer数据库性能优化.pdf
SQL Server数据库性能优化方法.pdf
SQL Server数据库性能优化探究.pdf
SQL Server数据库性能优化策略.pdf
SQL SERVER数据库性能优化探讨.pdf
SQL Server数据库性能优化研究.pdf
SQLServer数据库性能优化技术.pdf