谈数据库性能优化.docx
《谈数据库性能优化.docx》由会员分享，可在线阅读，更多相关《谈数据库性能优化.docx（18页珍藏版）》请在冰豆网上搜索。
谈数据库性能优化
1:
前言
数据库优化是一个很广的范围,涉及到的东西比较多,并且每个特定的数据库,其具体的优化过程也是不一样的.因为优化的很大一部分最终都要跟具体的数据库系统细节打交道,在此不可能针对所有的数据库都一一详细阐述,如果那样,恐怕写几本书都写不完.只能针对一些比较通用的,经常用到的的东西进行一个讨论,一般情况下,数据库的优化指的就是查询性能的优化（虽然严格上来说不应该是这样的）,让数据库对查询的响应尽可能的快.仅对数据库系统本身而言,影响到查询性能的因素从理论上来讲，包括数据库参数设置（其实就是通过参数控制数据库系统的内存,i/o,缓存，备份等一些管理性的东西）,索引,分区,sql语句.数据库参数设置本身是一个很复杂的东西，分区则主要是针对大数据量的情况下，它分散了数据文件的分布，减少磁盘竞争，使效率得到提升。
每种数据库或多或少都有一些自己特定的索引,如oracle除了常规索引之外还有反向索引,位图索引,函数索引,应用程序域索引等等,能够让用户对数据的逻辑组织有着更为精确的控制,而sqlserver没有这么多的索引,大体来说,sqlserver的索引分为两种:
聚集索引和非聚集索引.在分区方面,oracle和sqlserver比较相似,不过sqlserver的分区更为繁琐一些,但随着sqlserver的版本越来越高,其分区操作也趋向于简洁.sql语句优化则基本上比较独立，目前的一些数据库系统处理sql的机制都比较类似，因为sql本身就是一个标准。
这三种将会在下面作一个详细的讨论.本讨论建立在sqlserver上,因为目前部门的很多系统的数据库用到的是sqlserver,虽然oracle会给与我们更多的可探讨的范围.
2:
测试数据库的建立
因为要讨论索引,分区,sql等,因此有必要建立一个数据库,不然只是泛泛而谈,我在sqlserver2000上建立了一个名为ipanel的数据库,该数据库只有一张表,名为person,person的定义如下:
CREATETABLE[dbo].[person]（
[id][bigint]NOTNULL,--记录的id
[name][varchar]（10）COLLATEChinese_PRC_CI_ASNULL,--姓名
[age][int]NULL,--年龄
[addr][varchar]（50）COLLATEChinese_PRC_CI_ASNULL,--地址
[sex][char]（10）COLLATEChinese_PRC_CI_ASNULL,--性别
[dept][varchar]（50）COLLATEChinese_PRC_CI_ASNULL,--部门
[pos][varchar]（50）COLLATEChinese_PRC_CI_ASNULL,--邮编
[tel][char]（15）COLLATEChinese_PRC_CI_ASNULL,--电话
[fax][char]（15）COLLATEChinese_PRC_CI_ASNULL,--传真
[emdate][datetime]NULL--入职日期
）ON[PRIMARY]
ON[PRIMARY]表示该表建在系统的默认文件组上，在sqlserver里，文件组的概念就相当于oracle的表空间，是一种逻辑概念,它包含了数据文件，所谓数据文件，当然就是存储数据的文件.默认情况下，sqlserver会在默认的路径建立文件组和初始的数据文件，如果用户在建立数据库或表的时候没有指定文件组，则用默认的。
数据文件,日志文件,参数文件是所有数据库系统最主要的文件,oracle还有控制文件,在很多的专业书籍里面,从数据库系统的物理结构上来讲,数据库就是指的静态的数据文件,数据库系统或者数据库实例指的是一组进程,如日志进程,数据缓冲进程，网络监听进程等,这些进程作用在各种文件上面。
不说了,扯远了.建了一个数据插入的存储过程:
CREATEPROCEDUREinitPerson@startint,@endint,--起始条数，结束条数
@namevarchar（10）,@ageint,--姓名，年龄
@addrvarchar（10）,@sexchar
（2）,--地址，性别
@deptvarchar（20）,@emdatevarchar（10--部门，入职日期
AS
declare@idint
set@id=@start
while@id<=@end
begin
insertintopersonvalues（@id,@name,@age,@addr,@sex,@dept,
'438200','82734664','82734665',@emdate）
set@id=@id+1
end
GO
以下插入记录
execinitPerson1,100000,‘王**’,24,’深圳’,’男’,’应用开发部’,’2007-06-04’
插入10万条名叫王**的记录，因为在当前的例子中，姓名不重要，所以相同的姓名不碍事。
如下依次执行
execinitPerson100001,200000,‘韩**’,25,’深圳’,’男’,’应用工程部’,’2007-06-05’
execinitPerson200001,300000,‘徐*’,26,’深圳’,’男’,’系统终端部’,’2007-06-06’
execinitPerson300001,500000,‘程*’,23’,深圳’,’男’,’研发中心’,’2007-06-07’
execinitPerson500001,750000,‘卓*’,22,’深圳’,’男’,’行政部’,’2007-06-08’
execinitPerson750001,1000000,‘流*’,20,’深圳’,’男’,’业务合作部’,’2007-06-09’
接着依次插入类似的记录,我就不一一列举了.
执行完毕，person表便有了200万条记录。
为什么我不用更多的数据呢,因为我要频繁的改变数据库的设置,如果数据非常多,那当我改变数据库设置时候,会耗费很长的时间,比如索引更新维护等,不太方便.值得一提的是，如果没有指定聚集索引，那么sqlserver默认在主键上建立聚集索引，在当前情况下，系统在id列上建立了聚集索引。
数据库建立完毕,下面将会对索引,分区,sql做比较详细的讨论
3:
索引
索引是各种关系数据库系统最常见的一种逻辑单元,是关系数据库系统举足轻重的重要组成部分,对于提高检索数据速度有着至关重要的作用,索引的原理是根据索引值得到行指针,然后快速定位到数据库记录..
3.1:
常见索引介绍
1:
B*树索引
这是最常见的索引,几乎所有的关系型数据库系统都支持B*树结构的索引,也是被最多使用的,其树结构与二叉树比较类似,根据行id快速定位到行.大部分数据库默认建立的索引就是这种索引.B*树索引在检索高基数数据列（高基数列是指该列有很多不同的值,该列所有不同值的个数之和与该列所有值的个数之和的比成为列基数）时提供了比较好的性能,B*树索引是基于二叉树的,由分支块和叶块组成.在树结构中,位于最底层的快成为叶块,包含每个被索引列的值和行所对应的rowid.在叶节点的上面是分支块,用来导航结构,包含了索引列（关键字）范围和另一索引快的地址,如图所示:
（javaeye的图片插入做的不够好，插图进来我觉得很好麻烦）
假设要查找索引中值为80的行，从索引树的最上层入口开始，首先定位到大于等于50，然后往左找，找到第二个分支块，定位到75―100，然后定位到叶块，定位到叶块，找到80所对应的rowid，然后根据rowid到数据块读取对应的数据。
如果查询条件是范围选择的，比如colume>20andcolume<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*树索引,每个叶块只包含两种数据,一种是索引项,一种是该索引项所在行的行指针,当查询的数据匹配该索引项数据的时候,将会取出对应的行指针,取得该行的数据.如果要根据键值从大型SQLServer表提取具有良好选择性的少数几行，非聚集索引最有用。
B*树的底部或叶级包含组成该索引的列中的所有数据。
当用非聚集索引检索表中与键值匹配的信息时，将搜索整个索引B树，直到在索引叶级找到一个与键值匹配的值。
在非聚集索引中，叶级节点仅包含参与索引的数据以及快速找到相关数据页上其它行数据的指针。
最糟糕的情况是，从非聚集索引中获得的每一行都要求一个额外的不连续磁盘I/O才能检索行数据。
最好的情况是，所需要的行有许多都位于相同的数据页，因此在提取每个数据页时可检索多行。
如果是聚集索引，索引的叶级节点是表的实际数据行。
因此，检索表数据时不需要指针跳动。
基于聚集索引的范围扫描执行情况很好，因为聚集索引的叶级（即表的所有行）在物理上按照组成聚集索引的列顺序排列在磁盘上.
4:
覆盖索引
覆盖索引是非聚集索引的一个特例。
覆盖索引的定义是在选择条件和WHERE谓词上均满足SQL查询的所有列的基础上建立的非聚集索引。
覆盖索引可以节省大量的I/O，因此可极大地改善查询的性能。
但是有必要在新建索引（以及与它相关的B树索引结构维护）所需要的代价和覆盖索引所带来的I/O性能增益之间进行权衡。
如果覆盖索引对于SQLServer上经常运行的查询或查询组极其有利，那么创建覆盖索引是值得的。
覆盖索引的示例
Selectcol1,col3fromtable1wherecol2='value'.
Createindexindexname1ontable1（col2,col1,col3）.
本例中创建出来的索引“indexname1”是一个覆盖索引，因为它包括SELECT语句和WHERE谓词中的所有列。
即在执行此查询期间，SQLServer不需要访问与table1相关的数据页。
SQLServer使用索引indexname1可以获得满足查询所需要的全部信息。
在SQLServer已遍历与indexname1相关的B树，并找到col2等于“value”的索引关键字范围，SQLServer就知道它可以从覆盖索引的叶级（底层）提取所有需要的数据（col1,col2,col3）。
这从两个方面改进了I/O性能：
SQLServer仅从索引页而不是数据页获取所有需要的数据，因此数据的压缩率更高，使SQLServer可以节省磁盘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子句；一般情况下，使用该子句的是外键列。
使用ORDERBY或GROUPBY子句。
在ORDERBY或GROUPBY子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行已经排序，这样可以提高数据库性能
一般情况下，定义聚集索引键时使用的列越少越好。
考虑具有下列一个或多个属性的列：
唯一或包含许多不重复的值
例如，雇员ID唯一地标识雇员。
EmployeeID列的聚集索引或PRIMARYKEY约束将改善基于雇员ID号搜索雇员信息的查询的性能。
另外，可对LastName、FirstName、MiddleName列创建聚集索引，因为经常以这种方式分组和查询雇员记录，而且这些列的组合还可提供高区分度。
按顺序被访问
例如，id唯一地标识person表中的记录，在其中指定顺序搜索的查询（如WHEREIDBETWEEN1000and2000）将从id的聚集索引受益。
这是因为行将按该键列的排序顺序存储。
经常用于对表中检索到的数据进行排序。
按该列对表进行聚集（即物理排序）是一个好方法，它可以在每次查询该列时节省排序操作的成本。
聚集索引不适用于具有下列属性的列：
频繁更改的列
这将导致整行移动，因为数据库引擎必须按物理顺序保留行中的数据值。
这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。
宽键
宽键是若干列或若干大型列的组合。
所有非聚集索引将聚集索引中的键值用作查找键。
为同一表定义的任何非聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。
3.3谈索引使用的误区
理论的目的是应用。
虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。
下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区。
1:
主键就是聚集索引
这种想法我认为不是太合理，大多数情况下，主键上的聚集索引是对聚集索引的一种浪费。
虽然SQLSERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。
此时，如果我们将这个列设为主键，SQLSERVER会将此列默认为聚集索引。
这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但我认为这样做意义不大。
因为在很多情况下，由于主键的唯一性，对id或者主键进行范围扫描是比较少的。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。
在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。
这就使让ID号这个主键作为聚集索引成为一种资源浪费。
其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
如果您的聚集索引盲目地建在ID这个主键上时，查询速度不一定会提高的，即使你在其他字段上建立非聚集索引。
下面我们就来看一下在200万条数据量的情况下各种查询的速度表现：
（1）全表扫描
只在主键上建立聚集索引：
Selectid,name,dept,emdatefromperson
用时：
20546毫秒（即：
21秒）
不在主键上建立聚集索引，只建普通索引
Selectid,name,dept,emdatefromperson
用时：
17923毫秒（即：
18秒）
以上查询执行的实际上索引不会发挥作用，因为提取的是全部数据。
聚集索引在这里会耗费更多的资源，所以会看到，不建立聚集索引比建立聚集索引还要快
（2）:
按日期进行过滤（用到索引）
在主键上建立聚集索引，在emdate上建立非聚集索引：
selectid,name,dept,emdatefrompersonwhereemdate>dateadd（day,+1,getdate（））
用时：
12376毫秒（12秒）
在主键上建立聚集索引，在emdate上没有索引：
selectid,name,dept,emdatefrompersonwhereemdate>dateadd（day,+1,getdate（））
用时：
21296毫秒（21秒）
在主键上建立非聚集索引，在emdate上建立非聚集索引：
selectid,name,dept,emdatefrompersonwhereemdate>dateadd（day,+1,getdate（））
用时：
11590毫秒（12秒）
在主键上建立非聚集索引，在emdate上建立聚集索引：
selectid,name,dept,emdatefrompersonwhereemdate>dateadd（day,+1,getdate（））
andemdate用时：
5233毫秒（5秒）
虽然每条语句提取出来的都是30万条数据，各种情况的差异却是比较大的，特别是将聚集索引建立在日期列时的差异。
事实上，如果您的数据库真的有几千万条记录的话，差距会更明显。
2:
只要建立索引就能显著提高查询速度
这个想法是错误的。
事实上，我们可以发现上面的例子中，上面按日期过滤的语句完全相同，且建立索引的字段也相同，但查询速度却有着非常大的差别。
所以，并非是在任何字段上简单地建立索引就能提高查询速度。
索引的建立，会带来更多的系统开销，因为系统要耗费资源去维护它，如果建立了没有用到的索引，不适当的索引，过多的索引，反而会导致查询性能下降。
总之索引的建立，要看表的结构，数据的分布，还有你要用到哪些数据，如果把索引建立在你根本不需要的数据列上，是根本不会发挥作用的。
3:
把所有需要提高查
升级会员 