谈数据库的性能优化

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 

以下插入记录 
*** initPerson 1,100000, ‘王**’,24,’深圳’,’男’,’应用开发部’,’2007-06-04’ 
插入10万条名叫王**的记录，因为在当前的例子中，姓名不重要，所以相同的姓名不碍事。如下依次执行 
*** initPerson 100001,200000, ‘韩**’,25,’深圳’,’男’,’应用工程部’,’2007-06-05’ 
*** initPerson 200001,300000, ‘徐*’,26,’ 深圳’,’男’,’系统终端部’,’2007-06-06’ 
*** initPerson 300001,500000, ‘程*’,23’, 深圳’,’男’,’研发中心’,’2007-06-07’ 
*** initPerson 500001,750000, ‘卓*’,22,’ 深圳’,’男’,’行政部’,’2007-06-08’ 
*** 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”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。 我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。 聚集索引都是排好序的.