谈数据库的性能优化

 

这篇文章是我花了很多时间写出来的，曾经发表在javaeye论坛上，今天居然不见了，幸好网上有人转载这篇文章，没办法，只好再一份在博客里。这个是我以前写给我部门的一个技术心得，鄙人才疏学浅，知道javaeye高人很多，如果我写的不对的地方，欢迎指教。 

我靠这么多关键字过滤啊，“fapiao”也成了关键字

1:前言

      数据库优化是一个很广的范围,涉及到的东西比较多,并且每个特定的数据库,其具体的优化过程也是不一样的.因为优化的很大一部分最终都要跟具体的数据库系统细节打交道,在此不可能针对所有的数据库都一一详细阐述,如果那样,恐怕写几本书都写不完.只能针对一些比较通用的,经常用到的的东西进行一个讨论,一般情况下,数据库的优化指的就是查询性能的优化(虽然严格上来说不应该是这样的),让数据库对查询的响应尽可能的快.仅对数据库系统本身而言,影响到查询性能的因素从理论上来讲，包括数据库参数设置(其实就是通过参数控制数据库系统的内存,i/o,缓存，备份等一些管理性的东西),索引,分区,sql语句.数据库参数设置本身是一个很复杂的东西，分区则主要是针对大数据量的情况下，它分散了数据文件的分布，减少磁盘竞争，使效率得到提升。

      每种数据库或多或少都有一些自己特定的索引,如oracle除了常规索引之外还有反向索引,位图索引,函数索引,应用程序域索引等等,能够让用户对数据的逻辑组织有着更为精确的控制,而sqlserver没有这么多的索引,大体来说,sqlserver的索引分为两种:聚集索引和非聚集索引.在分区方面,oracle和sqlserver比较相似,不过sqlserver的分区更为繁琐一些,但随着sqlserver的版本越来越高,其分区操作也趋向于简洁.sql语句优化则基本上比较独立，目前的一些数据库系统处理sql的机制都比较类似，因为sql本身就是一个标准。这三种将会在下面作一个详细的讨论.本讨论建立在sqlserver上,因为目前部门的很多系统的数据库用到的是sqlserver,虽然oracle会给与我们更多的可探讨的范围. 

 

 

2:测试数据库的建立 
      因为要讨论索引,分区,sql等,因此有必要建立一个数据库,不然只是泛泛而谈,我在sqlserver2000上建立了一个名为ipanel的数据库,该数据库只有一张表,名为person,person的定义如下:

CREATE TABLE [dbo].[person] ( 
[id] [bigint] NOT NULL , --记录的id 
[name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,--姓名 
[age] [int] NULL ,--年龄 
[addr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--地址 
[sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,--性别 
[dept] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--部门 
[pos] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--邮编 
[tel] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,--电话 
[fax] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,--传真 
[emdate] [datetime] NULL --入职日期 
) ON [PRIMARY]

      ON[PRIMARY]表示该表建在系统的默认文件组上，在sqlserver里，文件组的概念就相当于oracle的表空间，是一种逻辑概念,它包含了数据文件，所谓数据文件，当然就是存储数据的文件.默认情况下，sqlserver会在默认的路径建立文件组和初始的数据文件，如果用户在建立数据库或表的时候没有指定文件组，则用默认的。数据文件,日志文件,参数文件是所有数据库系统最主要的文件,oracle还有控制文件,在很多的专业书籍里面,从数据库系统的物理结构上来讲,数据库就是指的静态的数据文件,数据库系统或者数据库实例指的是一组进程,如日志进程,数据缓冲进程，网络监听进程等,这些进程作用在各种文件上面。不说了,扯远了.建了一个数据插入的存储过程: 

CREATE PROCEDURE initPerson @start int, @end int , --起始条数，结束条数 
@name varchar(10),@age int, --姓名，年龄 
@addr varchar(10),@sex char(2), --地址，性别 
@dept varchar(20),@emdate varchar(10 --部门，入职日期 
AS 
declare @id int 
set @id=@start 
while @id<=@end 
begin 
insert into person values(@id,@name,@age,@addr,@sex,@dept , 
'438200','82734664','82734665',@emdate) 
set @id=@id+1 
end 
GO 

以下插入记录 
exec initPerson 1,100000, ‘王**’,24,’深圳’,’男’,’应用开发部’,’2007-06-04’ 
插入10万条名叫王**的记录，因为在当前的例子中，姓名不重要，所以相同的姓名不碍事。如下依次执行 
exec initPerson 100001,200000, ‘韩**’,25,’深圳’,’男’,’应用工程部’,’2007-06-05’ 
exec initPerson 200001,300000, ‘徐*’,26,’ 深圳’,’男’,’系统终端部’,’2007-06-06’ 
exec initPerson 300001,500000, ‘程*’,23’, 深圳’,’男’,’研发中心’,’2007-06-07’ 
exec initPerson 500001,750000, ‘卓*’,22,’ 深圳’,’男’,’行政部’,’2007-06-08’ 
exec initPerson 750001,1000000, ‘流*’,20,’ 深圳’,’男’,’业务合作部’,’2007-06-09’ 
接着依次插入类似的记录,我就不一一列举了. 
执行完毕，person表便有了200万条记录。为什么我不用更多的数据呢,因为我要频繁的改变数据库的设置,如果数据非常多,那当我改变数据库设置时候,会耗费很长的时间,比如索引更新维护等,不太方便.值得一提的是，如果没有指定聚集索引，那么sqlserver默认在主键上建立聚集索引，在当前情况下，系统在id列上建立了聚集索引。 
数据库建立完毕,下面将会对索引,分区,sql做比较详细的讨论 

 

 

 

 

 

3:索引 

 

      索引是各种关系数据库系统最常见的一种逻辑单元,是关系数据库系统举足轻重的重要组成部分,对于提高检索数据速度有着至关重要的作用,索引的原理是根据索引值得到行指针,然后快速定位到数据库记录..

 

3.1:常见索引介绍

  

1: B*树索引 
      这是最常见的索引,几乎所有的关系型数据库系统都支持B*树结构的索引,也是被最多使用的,其树结构与二叉树比较类似,根据行id快速定位到行.大部分数据库默认建立的索引就是这种索引.B*树索引在检索高基数数据列(高基数列是指该列有很多不同的值,该列所有不同值的个数之和与该列所有值的个数之和的比成为列基数)时提供了比较好的性能,B*树索引是基于二叉树的,由分支块和叶块组成.在树结构中,位于最底层的快成为叶块,包含每个被索引列的值和行所对应的rowid.在叶节点的上面是分支块,用来导航结构,包含了索引列(关键字)范围和另一索引快的地址,如图所示: （图片插入做的不够好，插图进来我觉得很好麻烦）





假设要查找索引中值为80的行，从索引树的最上层入口开始，首先定位到大于等于50，然后往左找，找到第二个分支块，定位到75―100，然后定位到叶块，定位到叶块，找到80所对应的rowid，然后根据rowid到数据块读取对应的数据。如果查询条件是范围选择的，比如colume>20 and colume<80,那么会先定位到20的块，然后再横向查找到80的块为止，不是每次都从入口进去重新定位的。 
要说明的是,这种索引是用得最多的,基本上所有的数据库系统都支持这种索引,它是索引里最主要最普遍的,它之所以称为B*树索引,更多是因为它的存储结构有着普遍的意义,很多索引都基于这种结构,当然sqlserver里没有名为B*树的索引,但是不妨碍我们以对B*树索引的认识去理解sqlserver的索引,不是吗?这是我为什么把它放在最前面的原因.

 

 

 

2:聚集索引 

 

      没错,这是sqlserver里很重要的一个索引.也叫群集索引。 聚集索引是相对于常规索引而言的,oracle也有类似的索引,不过叫聚簇索引,注意,虽然聚簇和聚集仅有一字之差,但是oracle的聚簇索引和sqlserver的聚集索引还是有很多的不同的,oracle的聚簇索引可以针对多表,根据多个表相同列的不同值,将相关数据聚集在周围.sqlserver聚集索引也有类似的意思,但是只能针对单表.在oracle里,聚簇”是oralce内部的一个对象,就像基本表,视图,触发器这些概念一样. 聚簇索引就是对聚簇进行的索引,由于比较复杂,在此不详细讨论,但在sqlserver里,聚集索引直接作用在表上,因此不可以将二者混淆.反正不能等同来看就是了.

 
      举个例子说明来说明sqlserver的聚集索引:我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。 我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。 聚集索引都是排好序的.

      如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。 
      总而言之, 聚集索引就是使与被索引的值相关的行数据块集中在一起,不是物理上的散列分布.这样,首先缩小了扫描范围,而且定位数据的时间短,可以想象一下查字典的时候,根据拼音查找汉字,找以”a”发音开头的字,你只会在a字母里面找,如果a字母找完了,那么不管有没有这个字,查找过程也就结束了。 
非聚集索引也是B*树结构，只不过每个索引值对应的不是行id，而是数据行本身，聚集索引会对表排序，就像字典一样，它按照英文字母的顺序排序的，所以在基于某个范围搜索的时候，它的查询效率是很高的，但同时我们也可以看到，它占据了更多的空间，在插入更新的时候，它会花多一点的时间维持自己的索引顺序。每个表只能有一个聚集索引，这是当然的，因为每个表肯定只可能有一个全表排序的规则。

3:非聚集索引 
      非聚集索引是一种典型的B*树索引,每个叶块只包含两种数据,一种是索引项,一种是该索引项所在行的行指针,当查询的数据匹配该索引项数据的时候,将会取出对应的行指针,取得该行的数据.如果要根据键值从大型 SQL Server 表提取具有良好选择性的少数几行，非聚集索引最有用。B*树的底部或叶级包含组成该索引的列中的所有数据。当用非聚集索引检索表中与键值匹配的信息时，将搜索整个索引 B 树，直到在索引叶级找到一个与键值匹配的值。 
在非聚集索引中，叶级节点仅包含参与索引的数据以及快速找到相关数据页上其它行数据的指针。最糟糕的情况是，从非聚集索引中获得的每一行都要求一个额外的不连续磁盘 I/O 才能检索行数据。最好的情况是，所需要的行有许多都位于相同的数据页，因此在提取每个数据页时可检索多行。如果是聚集索引，索引的叶级节点是表的实际数据行。因此，检索表数据时不需要指针跳动。基于聚集索引的范围扫描执行情况很好，因为聚集索引的叶级（即表的所有行）在物理上按照组成聚集索引的列顺序排列在磁盘上.

4:覆盖索引 
      覆盖索引是非聚集索引的一个特例。覆盖索引的定义是在选择条件和 WHERE 谓词上均满足 SQL 查询的所有列的基础上建立的非聚集索引。覆盖索引可以节省大量的 I/O，因此可极大地改善查询的性能。但是有必要在新建索引（以及与它相关的 B 树索引结构维护）所需要的代价和覆盖索引所带来的 I/O 性能增益之间进行权衡。如果覆盖索引对于 SQL Server 上经常运行的查询或查询组极其有利，那么创建覆盖索引是值得的。 
覆盖索引的示例

Select col1,col3 from table1 where col2 = 'value'. 
Create index indexname1 on table1(col2,col1,col3). 
本例中创建出来的索引“indexname1”是一个覆盖索引，因为它包括 SELECT 语句和 WHERE 谓词中的所有列。即在执行此查询期间，SQL Server 不需要访问与 table1 相关的数据页。SQL Server 使用索引 indexname1 可以获得满足查询所需要的全部信息。在 SQL Server 已遍历与 indexname1 相关的 B 树，并找到 col2 等于“value”的索引关键字范围，SQL Server 就知道它可以从覆盖索引的叶级（底层）提取所有需要的数据 (col1,col2,col3)。这从两个方面改进了 I/O 性能：

      SQL Server 仅从索引页而不是数据页获取所有需要的数据，因此数据的压缩率更高，使 SQL Server 可以节省磁盘 I/O 操作。 
覆盖索引按照 col2 将所有需要的数据以物理方式组织在磁盘上。使硬盘得以连续返回与 where 谓词 (col2 = "value") 相关的所有索引行。从而为我们提供了更好的 I/O 性能。 总而言之，如果覆盖索引中的所有列的字节数比该表中单行的字节数少，并且可以肯定将反复执行使用此覆盖索引的查询，那么使用覆盖索引是有意义的。 

5:位图索引 
      这个不是sqlserver的索引，它是oracle的，所以请不要混淆。之所以提出来，是因为它不是B*树结构的索引。位图索引相对于B*tree索引来说,它的存储结构是不一样的,通常在B*tree索引中,在索引条目和行之间有一对一的关系.对于位图索引,一个索引条目使用一个位图同时指向许多行.这对于基本上只读的低基数(数据只有很少的几个截然不同的值)数据是合适的.比如说,一个person表,有个性别字段sex,Y代表男,N代表女,对于有几百万行数据的表来说, 位图索引是一个非常好的选择。它可以迅速的扫描出来，而不用象对B*树索引那样的查找。

 

 

3.2 有效的利用索引

      索引在数据库的查询优化中起着至关重要的作用，一个数据库索引的好与坏，其查询性能相差很多倍，下面将谈一下各种索引的使用场合和一些观点。如何选择索引可显著影响所产生的磁盘 I/O，并因而影响查询性能。在非聚集索引中，选择性很重要，因为如果在只有少量唯一值的大型表上创建非聚集索引，使用非聚集索引将不会节省数据检索中的 I/O。因为B*树结构的索引都注重一种比较性，这样它可以快速的确定范围，定位位置,例如，person表的性别字段，非男即女，不具有可比性，如果以它为非聚集索引，查询的时候也只能一个个叶节点去比较。在这种情况下产生的 I/O 可能比对表进行连续扫描所产生的 I/O 多得多。比较适合非聚集索引的有票据编号、唯一的客户编号、社会安全号码和电话号码，简单来说，就是基于某种可比较的，有规律的数据。 
创建聚集索引之前，应先了解数据是如何被访问的。

 

考虑对具有以下特点的查询使用聚集索引：

使用运算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。 使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。例如，如果某个查询在一系列销售订单号间检索记录，销售单号列的聚集索引可   快速定位包含起始销售订单号的行，然后检索表中所有连续的行，直到检索到最后的销售订单号。 
返回大型结果集。 
使用 JOIN 子句；一般情况下，使用该子句的是外键列。 
使用 ORDER BY 或 GROUP BY 子句。 
在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使数据库引擎 不必对数据进行排序，因为这些行已经排序，这样可以提高数据库性能 

一般情况下，定义聚集索引键时使用的列越少越好。考虑具有下列一个或多个属性的列： 

 唯一或包含许多不重复的值 
例如，雇员 ID 唯一地标识雇员。EmployeeID 列的聚集索引或 PRIMARY KEY 约束将改善基于雇员 ID 号搜索雇员信息的查询的性能。另外，可对 LastName、FirstName、MiddleName 列创建聚集索引，因为经常以这种方式分组和查询雇员记录，而且这些列的组合还可提供高区分度。

按顺序被访问 
例如，id唯一地标识person表中的记录，在其中指定顺序搜索的查询（如 WHERE ID BETWEEN 1000 and 2000）将从id的聚集索引受益。这是因为行将按该键列的排序顺序存储。

经常用于对表中检索到的数据进行排序。 
按该列对表进行聚集（即物理排序）是一个好方法，它可以在每次查询该列时节省排序操作的成本。 
聚集索引不适用于具有下列属性的列：

频繁更改的列 
这将导致整行移动，因为数据库引擎 必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。

宽键 
宽键是若干列或若干大型列的组合。所有非聚集索引将聚集索引中的键值用作查找键。为同一表定义的任何非聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。

 

 

 

3.3 谈索引使用的误区 

      理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区。 

1:主键就是聚集索引 
      这种想法我认为不是太合理，大多数情况下，主键上的聚集索引是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但我认为这样做意义不大。因为在很多情况下，由于主键的唯一性，对id或者主键进行范围扫描 是比较少的。显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。 从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。 
在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。 如果您的聚集索引盲目地建在ID这个主键上时，查询速度不一定会提高的，即使你在其他字段上建立非聚集索引。下面我们就来看一下在200万条数据量的情况下各种查询的速度表现： 

（1）全表扫描 
 只在主键上建立聚集索引： 
Select id,name,dept,emdate from person 
用时：20546毫秒（即：21秒） 
 不在主键上建立聚集索引，只建普通索引 
Select id,name,dept,emdate from person 
用时：17923毫秒（即：18秒） 
以上查询执行的实际上索引不会发挥作用，因为提取的是全部数据。聚集索引在这里会耗费更多的资源，所以会看到，不建立聚集索引比建立聚集索引还要快 

(2):按日期进行过滤(用到索引) 
 在主键上建立聚集索引，在emdate上建立非聚集索引： 
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate()) 
用时：12376毫秒（12秒） 

 在主键上建立聚集索引，在emdate上没有索引： 
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate()) 
用时：21296毫秒（21秒） 

 在主键上建立非聚集索引，在emdate上建立非聚集索引： 
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate()) 
用时：11590毫秒（12秒） 

 在主键上建立非聚集索引，在emdate上建立聚集索引： 
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate()) 
andemdate<dateadd(day,+3,getdate()) 
用时：5233毫秒（5秒） 

虽然每条语句提取出来的都是30万条数据，各种情况的差异却是比较大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有几千万条记录的话，差距会更明显。 

2:只要建立索引就能显著提高查询速度 
      这个想法是错误的。事实上，我们可以发现上面的例子中，上面按日期过滤的语句完全相同，且建立索引的字段也相同，但查询速度却有着非常大的差别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。索引的建立，会带来更多的系统开销，因为系统要耗费资源去维护它 ，如果建立了没有用到的索引，不适当的索引，过多的索引，反而会导致查询性能下降。总之索引的建立，要看表的结构，数据的分布，还有你要用到哪些数据，如果把索引建立在你根本不需要的数据列上，是根本不会发挥作用的。 

3:把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度 
      这个不一定正确。上面已经谈到。假设现在查询要用到用户名和日期这两个字段，我们可以把他们合并起来，建立一个复合索引（compound index）。 很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条）：（日期列emdate首先排在复合聚集索引的起始列，用户名name排在后列） 

 select id,name,dept,emdate from person where emdate>'2007-06-01' 
查询速度：1664毫秒 
 select id,name,dept,emdate from person 
where emdate>'2007-06-01' and name=’王小雪’ 
查询速度：1640毫秒 
 select gid,fariqi,neibuyonghu,title from person 
where name='王小雪' 
查询速度：5920毫秒 
从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。

3.4 其他索引经验总结 
1:用聚合索引比用不是聚合索引的主键速度快 
下面是实例语句：（都是提取25万条数据） 
select id,name,dept,emdate from person where emdate='2007-06-04' 
使用时间：906毫秒 

select id,name,dept,emdate from person where id<=100000 
使用时间：1153毫秒 
这里，用聚合索引比用不是聚合索引的主键速度略快一些。 

2:用聚合索引比用一般的主键作order by时速度快，特别是小数据量时 
select id,name,dept,emdate from person order by emdate 
用时：17856 (约18秒) 

select id,name,dept,emdate from person order by id 
用时：44046 (约45秒) 

这里可以看到，用聚集索引比用一般的主键作order by时，速度几乎快了2.5倍。事实上，有的资料说小数据量情况下，用聚集索引排序列比非聚集索引作为排序列快，10万以上，则二者的速度差别不明显。但据当前200万条数据情况来看，在大数据量的情况下，这个结论依然成立。 

3:使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比 
比例减少，而无论聚合索引使用了多少个 
select id,name,dept,emdate from person where 
emdate='2007-06-04 00:00:00.000' 
用时：1123毫秒（提取10万条） 

select id,name,dept,emdate from person where 
emdate='2007-06-04 00:00:00.000' 
用时：1843毫秒（提取20万条） 

select id,name,dept,emdate from person where 
emdate='2007-06-09 00:00:00.000' 
用时：4500毫秒（提取45万条） 

从以上统计的数据看来，这个规律基本上是正确的 


其他注意事项 
    “水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。在实际的开发中，会遇到很多意想不到的情况，最好是多测试一些方案，找出哪种方案效率最高、最为有效。

 

 

 

4:SQL语句改善 
       一个sql语句大约要经过三个阶段，编译优化，执行，取值，而编译阶段，而第一阶段大部分情况下都要花掉60％的时间，所以绑定变量是很重要的，sqlserver和oracle都有缓存区，存放最近使用的sql语句，当有一条sql语句到达数据库服务器时，数据库会首先搜索缓存区，看它是否存在可以重用的sql语句，如果存在，则无需编译优化，因为缓存区的sql语句都是编译优化好了的，可以直接执行，节省相当多的时间。如果没有发现该语句，则必须要完全经历语句编译分析，优化计划，安全检查等过程，这不仅耗费了大量的cpu功率，而且还在相当长的一段时间内锁住了一部分数据库缓存，这样执行sql语句的人越多，等待的时间越长，系统的性能会大幅度的下降。

很多人不知道SQL语句在SQL SERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。比如： 
select id,name,dept,emdate from person 
where name='王小雪' and id<100000 用时：1220毫秒 
和执行: 
select * from table1 where id< 100000 and name='王小雪' 用时：1173毫秒 
一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果id是一个聚合索引，那么后一句仅仅从表的100000条以内的记录中查找就行了；而前一句则要先从全表中查找看有几个name='王小雪'的，而后再根据限制条件条件id<100000来提出查询结果。事实上，这样的担心是不必要的。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。 
原因是通配符%在字符串的开通使得索引无法使用。 如以下查询 
没有对name进行索引 
select id,name,dept,emdate from person where name like '%小雪' 
用时 3654毫秒 
对name进行非聚集索引 
select id,name,dept,emdate from person where name like '%小雪' 
用时 3673毫秒 
对name进行聚集索引 
select id,name,dept,emdate from person where name like '%小雪' 
用时 3673毫秒 
由以上数据可以看到，将匹配符号放在被查询字段的前面，索引根本就不会发生作用，所以这也是要注意的地方，如果不会用到，最好少用 

2:or 是否会引起全表扫描 
有很多资料上说or会引起全表扫描。 
如name=’王小雪’ and emdate>’2007-01-10’不会全表扫描,而 
name=’王小雪’ or emdate>’2007-01-10’则会,但是据我观察,情况不是这样的.对于这样的一个sql语句select id,name,dept,emdate from person where name='王小雪' or emdate>'2007-06-08'，我们可以看sqlserver对于它们的执行计划 

在有聚集索引的情况下(无论聚集索引建在哪些字段上) 


没有聚集索引但是主键索引的情况下 




没有任何索引的情况下 




由上可以得出结论,在用到or的时候，如果有聚集索引,就不会引起全表扫描,没有聚集索引,就会引起全表扫描，所以说，只要用or就会引起全表扫描是片面的，不正确的。

3:非操作符、函数引起的不满足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 id,name,dept,emdate from person where name in('王小雪','聂海') 
所花时间:8936ms, 
select id,name,dept,emdate from person where name='王小雪' or name='聂海' 
所花时间:5390ms, 

没有聚集索引 
select id,name,dept,emdate from person where name in('王小雪','聂海') 
所花时间:5310ms, 
select id,name,dept,emdate from person where name='王小雪' or name='聂海' 
所花时间:5326ms, 

可见,or 比 in速度快,因为作了聚集索引,所以它们都没有执行table scan,不过因为聚集索引作用在日期字段emdate上,所以虽然查询使用了聚集索引,但并不意味着比全表扫描快,其实使用作用在emdate上的聚集索引查询,在某种意义上来说,也是一种全表扫描,只不过数据的扫描顺序不同而已,在这种情况下,甚至没有聚集索引反而更快 

5:exists 和 in 的执行效率是一样的 
     很多资料上都显示说，exists要比in的执行效率要高，同时应尽可能的用not exists来代替not in。但事实上，我试验了一下，发现二者无论是前面带不带not，二者之间的执行效率都是一样的。因为涉及子查询，我试验这次用SQL SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics I/O状态打开。 语法为:set statistics io on, 要查看语句的执行过程,打开查询分析器的消息栏就可以看到,但是在查询语句之前要加上set statistics io on 
（1）select 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的执行效率是一样的。 

6:用函数charindex()和前面加通配符%的LIKE执行效率一样 
      前面，我们谈到，如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。但有的资料介绍说，用函数charindex()来代替LIKE速度会有大的提升，但据我测试，发现这种说明也是错误的： 
select id,name,dept,emdate from person where charindex('小雪',name)>0 
用时：4010ms 
扫描计数 1，逻辑读 29905 次，物理读 0 次，预读 0 次。 

select id,name,dept,emdate from person where name like '%小雪' 
用时：4123ms 
扫描计数 1，逻辑读 29905 次，物理读 0 次，预读 0 次。 

7:union并不绝对比or的执行效率高 
      很多资料都推荐用union来代替or。事实证明，这种说法对于大部分都是适用的。 
(1):select id,name,dept,emdate from person where name='王小雪' or emdate>'2007-06-04' 
用时：85626ms。扫描计数 1，逻辑读 129905 次，物理读 0 次，预读 0 次。次。 
(2):select id,name,dept,emdate from person where name='王小雪' 
union 
select id,name,dept,emdate from person where emdate>'2007-06-04' 
用时：17373ms。扫描计数 2，逻辑读 59810 次，物理读 0 次，预读 0 次。 
看来，用union在通常情况下比用or的效率要高的多。 


5:sqlserver的分区 
      对于一些超大型的表，分区是非常有用的。分区是一种逻辑概念，和oracle的分区概念是一样的.在通常情况下，一个表就是一个整体，当发生数据访问的时候，也是对整个表或整个表的索引进行访问，所谓分区，通俗点讲，就是把表按一定的规律划分成更小的逻辑单位,当发生访问的时候,不以表为单位进行访问,而先在表的基础上,判断数据在哪个分区,然后对特定的分区进行访问.正确的分区有利于提高查询性能.例如,有一个非常大的表,存储了一些销售记录,现在查询总是按销售季度来执行这个查询----每个销售季度包含几十万个记录,通常你只是要查询这个数据集的一个相当小的数据,但是给予销售季度的检索却的确是不太可行的.这个索引可能指向无数个记录,而以这种方式执行索引范围扫描是可怕的.为了处理许多查询任务,系统需要执行全表扫描,但是结果却必须扫描几百万个记录,其中绝大部分不使用我们的查询任务.使用智能分区方案,就可以按季度隔离数据.这样当我们为任意指定的季度去查询数据时,结果将只是扫描那个季度的数据.这是所有可能的解决方案种最好的方案.下面将介绍sqlserver的分区使用.

      分区是比较复杂的，以分区的对象来分类的话，则分为两种，表分区和索引分区。 
表分区主要指的是范围分区，(貌似比较单一，oracle里有散列分区等等，不过在sqlserver里我目前还没有看到).就这么说可能不清不楚，下面将以我们已经建立好的ipanel数据库为例，对person表进行按日期分区，假设ipanel每个月都要进出几十万人，然后HR每月还要作很多的统计吧。下面一步一步来，common 

创建文件组 
各种数据最终是存储在数据文件里，在实际应用中，表的分区都会分布在多个数据文件中，这样以便获得更好的 I/O 平衡，对于文件，是以文件组为单位进行管理，文件组相当于目录，数据文件就相当于目录里的文件。为数据库添加文件组，这个文件组分布存储person表的数据： 
ALTER DATABASE ipanel ADD FILEGROUP [person_fg] 
现在为ipanel数据库创建了一个名为person_fg的文件组。下面为该文件组添加数据文件。 

添加数据文件 
ALTER DATABASE ipanel 
ADD FILE 
(NAME = N'person001', 
FILENAME = N'C:\ipanel\person001.ndf', 
SIZE = 5MB, 
MAXSIZE = 100MB, 
FILEGROWTH = 5MB) 
TO FILEGROUP [person_fg] 
如上，为文件组添加了一个数据文件 

创建分区函数 
既然分区，那么就应该有一个分区的标准，就是说数据将以什么标准来分区，分区函数就是做这件事情的，它定义数据划分的标准，对表进行逻辑上的划分。 
CREATE PARTITION FUNCTION personRangePFN(datetime) 
AS 
RANGE LEFT FOR VALUES ('20030930', 
'20050930', 
'20070930', 
'20090930') 
上面的分区函数创建了5个分区，并且定义了分区列的数据类型为datetime,因为分区的标准要建在表的某一列上，在此定义，分区列必须是日期时间型。RANGE LEFT表示范围分区，LEFT所在的选项有两个：LEFT,RIGHT.分区标识着数据的上界和下界。如当前选项是LEFT，则表示： 
分区1：<=20030930 
分区2：>20030930,<=20050930 
分区3：>20050930,<=20070930 
分区4：>20070930,<=20090930 
分区5：>20030930 
如果当前选项是RIGHT,则表示： 
分区1：<20030930 
分区2：>=20030930,<20050930 
分区3：>=20050930,<20070930 
分区4：>=20070930,<20090930 
分区5：>=20030930 


创建分区架构 
创建分区函数后，必须将其与分区架构相关联，以便将分区定向至特定的文件组。定义分区架构时，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。对于前面创建的范围分区 (personRangePFN)，存在五个分区；最后一个空分区将在 PRIMARY 文件组中创建。因为此分区永远不包含数据，所以不需要指定特殊的位置 
CREATE PARTITION SCHEME PersonEmdateScheme 
AS 
PARTITION personRangePFN 
TO ([person001], [person002], [person003], [person004], [PRIMARY])

创建分区表 
      定义分区函数（逻辑结构）和分区架构（物理结构）后，即可创建表来利用它们。表定义应使用的架构，而架构又定义函数。要将这三者结合起来，必须指定应该应用分区函数的列。范围分区始终只映射到表中的一列，此列应与分区函数中定义的边界条件的数据类型相匹配。另外，如果表应明确限制数据集（而不是从负无穷大到正无穷大），则还应添加 CHECK 约束。 

CREATE TABLE [dbo].[person] ( 
[id] [bigint] NOT NULL , --记录的id 
[name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,--姓名 
[age] [int] NULL ,--年龄 
[addr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--地址 
[sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,--性别 
[dept] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--部门 
[pos] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--邮编 
[tel] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,--电话 
[fax] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,--传真 
[emdate] [datetime] NULL --入职日期 
) 
On PersonEmdateScheme (emdate) 
如果要限制的emdate的值的范围，则必须给它加上约束，如只允许emdate的值从2002年9月1日到2010年9月1日， 
则将[emdate] [datetime] NULL 改为 

[emdate] [datetime] NULL 
CONSTRAINT personRangeYear 
CHECK ([emdate] >= '20020901' 
AND [emdate] <= '20100901 11:59:59.997') 

分区总结 
     到此，对于分区表person已经设置完毕，person的数据会根据emdate的值分布到几个不同的数据文件里，在查询的时候，系统会首先判断emdate的值，看它在哪个分区，然后只进入该分区查找数据，这对于超大规模的系统来说，是很有用的，如果一个表有几千万上亿的数据，即使是索引扫描也是一个很费时的过程，不要忘记，索引也就像相当于简化了的表。对于索引，sqlserver里有索引分区，如果索引分区和表分区对齐的话，就是说和表一样使用了相同的分区函数和相同的分区架构，那么对于索引的查找，就不是对整个索引的查找了，而是先判断在哪个索引分区，然后再取查找该索引值，然后找到数据，这样就会节省很多时间。分区还有一个好处就是，对于一些数据可以更好的进行管理，比如说，定义了2006年度的销售数据存储在对应的分区area6，而area6对应的数据文件是sale006.ndf,到2007年的时候，一般情况下，可能不用06年的数据，按照分区的理论，它也不会访问06年的数据所在的区域。 
分区的应用是比较复杂的，上面只是介绍了其中一部分，其他还有索引分区，分区合并，分区移出等比较多的的东西。在oracle里，分区的概念是比较多的，包括对索引的分区都会有很多介绍，如散列分区，混合分区，局部索引，全局索引，原理上是差不多的。在此谈分区只是一个抛砖引玉的过程，如果对sqlserver分区想更深入了解的话，可以看看msdn，有中文的，不过翻译得很烂。 


6:后记 
     我以前看到过很多项目，数据库系统只是被纯粹的当作了一个存储数据的地方，建完表能增删改查就万事大吉了，有的连索引都没有，对于数据库的建立也很不严谨，更谈不上管理，虽然很多人认为数据库的管理是DBA的事情，但是我想作为一个技术人员，加深对数据库的了解是绝对没有坏处的，开发大型的系统，数据库肯定是非常重要的。如果想深入学习一门数据库的话，我建议大家从oracle开始学，因为sqlserver作了很多封装，而oracle更为复杂，是的，虽然它概念很多，比较复杂，但是却有助你了解更多的数据库细节，在很多方面，大部分的数据库系统都是相同的，oracle学好再来学其他的数据库，上手就非常容易，如果你会写PL/SQL程序，那有什么理由不会写TRANACT-SQL的数据库程序呢，语法只是一些细微的差别，而很多的概念却是相同的。

评论

2 楼 chenxiang105 2012-04-19  

copy走勒... 

1 楼 cues 2010-11-17  

案例相当丰富的一篇文章。受益匪浅

正确的理论谁都知道怎么搬用，但从理论推导出案例进行测试和论证才是学习的最快途径。

曾在itpub上看到一位工程师的签名为：学习数据库光有理论还不够，更多需要的是耐心。

颇有感触，博主准备这些case肯定花了不少时间。

谢谢分享:)，口水这些cases