数据库性能优化

第23卷计算机应用Vol. 232003年6月Computer ApplicationsJune, 2003文章编号:1001 -9081(2003)06Z -0172 -03数据库性能优化吕华,杜忠军(四川大学计算机系,四川成都610065)摘要:文中介绍正确设计一个数据库的经验,其重点是提高其性能,分析和比较不同设计方法给数据库性能带来的影响，同时给初学者-点建议。关键词:数据库;性能;优化中图分类号: TP311.53文献标识码:A能问题就会不断出现。如果软件中还有大量复杂的数据处理引言和数据库对象,又或者涉及到分布式数据库的联机分析决策对一个要处理.上百万数据量的大型数据库系统，比如联支持系统,那么整个系统的响应速度就会变得很慢,或者根本机事务处理OLIP或决策支持系统DSS,要求很高数据库端就无法忍受。据作者实验表明,两个同样功能的数据库软件的执行效率。虽然各种数据库如Sybase、Oracle、SQL Server、在执行时的效率比较，在数据量达到百万级,经过优化和正确DB2有各自的性能优势和优化器,但是作为开发人员还是必设计的软件是劣质软件的上百倍甚至是几百倍。可见对于一需要注意数据库产品的性能调节和SQL语句的高质量编写，个系统不是简单地能实现其功能就可,而是要规范数据库的否则即使选择再好的数据库产品也不能弥补软件设计的低劣设计,写出高质量的SQL语句,提高系统的可用性。所带来的性能损失。作者发现有很多初学者把数据库的前端开发产品(如2数据库产品的选择.PowerBuilder, Delphi等)作为重点学习对象,注重于漂亮的界显然做数据库项目时后端的数据库用什么产品是个重要面和花哨的功能而忽略了整个数据库端的效率。虽然友好的指标。既要考虑开发难度,又要考虑数据库产品的性能、产品人机界面也很重要,但软件的性能和效率也是不容忽视的,否则就会本末倒置。随着软件运行后数据量的增加,软件的性不同版本提供的功能和价格因素。以下这个表仅供参考:产品DB性能指标SQL Server 2000Oracle 8iDB2安全性没有获得任何安全证书获得最高认证级别的Iso标准认证执行效率多用户时性能不佳性能最高，保持Windows NT下的TPC-D适用于数据仓库和在线事物处理，性能较高和TPC-C的世界记录Oraele平行服务器通过使-组结点共享DB2具有很好的并行性。DB2把数据库管理并行实施和共存模型并不成熟。很难处理日益同一簇中的工作来扩展 Windows NT的能扩充到了并行的、多节点的环境数据库分并行性区是数据库的一部分， 包含自己的数据、索增多的用户数和数据卷。伸缩性有限力，提供高可用性和高伸缩性的簇的解决引配置文件和事务日志。数据库分区有时方案.被称为节点或数据库节点只能在Windows上运行，没有丝毫的开放性，操作系统的系统的稳定对数据库是十分重要能在所有主流平台上运行(包括能在所有主流平台上运行(包括Windows)。的。Windows 9X系列产品是偏重于桌面应用，Windows)。完全支持所有的工业标准。最适于海量数据。DB2在企业级的应用最为开放性NT Server只适合中小型企业。而且Windows采用完全开放策略。可以使客户选择最适广泛,在全球的500 家最大的企业中儿乎平台的可靠性，安全性和伸缩性是非常有限的。合的解决方案。对开发商全力支持85%以上用DB2 数据库服务器,而国内到它不象UNX那样久经考验，尤其是在处理大1997年约占5%数据量的关键业务时cIs结构,只支持Windows客户,可以用ADO、较复杂,同时提供GUI和俞令行.在操作简单,同时提供GUI 和命令行，在其它评价DAO、 OLEDB. ODBC连接。操作简单,但只Windows NT和UNX下操作相同。多层Windows NT和UNIX下操作相同。跨平台，次网络计算，支持多种工业标准，可以用有圈形界面ODBC. JDBC、0CI等网络客户连接多层结构，支持0DBC、JDBC等客户中国煤化工收稿日期:2002-11-25作者简介:昌华(1978-),男,硕士研究生,主要研究方向:數据库原理及应,MYHCN M H G杜忠军(1966-),男,副教授,主要研究方向:数据库原理及应用多媒体数据库、面向对象数据库.吕华等:数据库性能优化6月如果应用程序设计不当就会造成过多的网络V0,严重影响3优化的数据库设计整个系统的性能。通常对于C/S系统网络流量是整个系统要提高- -个数据库产品的质量,最重要的就是要提高其的瓶颈。提高网络vo原则是尽量使数据处理在数据库端批执行效率。整个数据库产品的性能取决于三方面:前端功能量执行 ,而网络Vvo只是一次传输最终结果,切忌不要是因为的执行效率，网络传输vo效率;后端数据库的执行效率。性一个很小的请求就要传输 -次。一般常用的方法有:在服务能的瓶颈在于网络vO。所以在设计和开发过程中-般要注器端大量使用存储过程 触发器和SQL函数的方法。初学者往往比较了解前端语句,而不熟悉数据库端的SQL的写法，意以下技术运用:(1)合理利用索引(群集索引和组合索引)为了图方便直接在前端写嵌人式SQL语句,殊不知这样会造关于索引的建立有很多原则,以下是一些基本的原则:成网络VO的急剧增大,严重降低性能。例如:.首先不要认为索引越多越好,滥用索引会严重降低表SELECT Depl, SUM( Selary) FROM recod WHERE DepINo>0 andDeptNo < 1000的更新速度;.通常要在那些经常进行连接,但是没有指定为外键的这条语句本该执行- 次网络I0,但是如果不用数据库端的SUM函数,那么同样的语句就会造成1000次网络V0列上建立索引;在条件表达式中经常用到的不同值较多的列上建立的开支。再如:SELECT sum((a. Price+ b. Pice)) proft froma, b wherea.id =检索。比如在职工表的“性别”列上只有“男"与“女”两个不b.id anda.id < 0000同值,因此就无必要建立索引;利用系统工具对索引进行优化，比如DB2的REORC,这样- -个稍微复杂的SQL语句,初学者写不出这样复杂的语句时就简单的在客户端写嵌入式SQL(如下):Oracle 的tbeheck等;float ld. a,ld b, Id. proft.如果经常对某几个列查询,可以对这几个列建立组合int索引。for 1=0 two 10000 do下面就群集和组合索引进行探讨，缺省情况下建立的索引是非群集索引,而经常同时存取多列,且每列都含有重复值seleet price into:ld, a from a where id :=Iselet price int: Id_. b from b where id:=I可考虑建立组合索引。ld_proft =ld_a + ld_b简单的说群集和非群集索引的差别就是群集索引要求存储时数据在物理.上按顺序排在数据页上,重复值也排列在一起,因而在有“范围查找”时(有关键字between,>,<,>=,(3)使用优化的SQL语句<= ,orderby ,groupby) ,可以先找到这个范围的起点和终点，在软件业有句名言叫“用80%的时间写出20%的代码"，且只在这个范围内扫描数据页,避免了大范围的扫描,提高了可见写程序时重要的并非数量的多少而是质量的高低。所谓查询速度;而非群集索引则不要求数据的顺序存放,因此非群优化的 sQL语句是指写出的SQL语句效率高、执行快。尽量集索引的表在更新时数据随机存放,速度更快。因此合理的利用索引 写出WHERE子句可优化的代码。- -般应遵循以索引设计:主要看对这个表长期进行的操作,存多查少,则只需下原则:建立缺省索引,即非群集索引;如果此表每次都是少量存储，尽量不要对列进行操作,例如: .而又长期要执行范围查询的话,则应考虑在有大量重复值且se●from rcond where sbring(Id,1,4)) = 2313'经常有范围查询(between, >，<, >=, <= )和orderby 、select * frorm rcord where Salary/2 < 1200groupby发生的列上建立群集索引。例:where子句中对列的任何操作结果都是在SQL运行时逐select Dept, sum( Salay) from record group by Dept列计算得到的,因此它不得不进行表搜索,而没有使用该列上这样一个简单的SQL语句在处理的数据量达到- -百万面的索引 ;如果这些结果在查询编译时就能得到,那么就可以行的时候,在Dept列上建群级索引时执行时间是建非群集索被 SQL优化器优化,使用索引,避免表搜索,因此将SQL重写引的1/3(时间分别为22秒和67秒) ,而如果根本就没建索成 下面这样:引,这个简单而常用的SQL语句大概要执行近3分钟。gelect * from record where Id like '2313% '组合索引是指由表的多个列组成的- -个索引。利用这种sele●from record where Selay < 1200*2索引的特点是注意尽量使关键查淘形成索引覆盖,其前导列两相比较,当表中的数据量达到十万数量级,后者的速度一定是使用最频繁的列。例:是前者的5倍。seleet Id, Salary from record where Id > 20000 and避免相关子查询ld < 50000'一个列的标签同时在主查询和where子句中的查询中出组合索引必须以Id列和Salay列建立才能以Id列为前现,那 么很可能当主查询中的列值改变之后,子查询必须重新导列,体现出索引的优势。相反如果以Salary列和Id列建立查询- 次。 查询嵌套层次越多,效率越低,因此应当尽量避免索引的话,SQL语句根本就利用不上索引。执行时间分别为子查询。 如果子查询不可避免，那么要在子查询中过滤掉尽2秒和49秒。可见这种性能差别有多大。可能多(2)网络IV0效率中国煤化工c/s系统中,客户端的应用程序将处理要求发给数据库HC N M H G使用索引,如:服务器,数据库服务器接受请求将处理的数据返回客户端。select * Throm Tecord where Id like '响23%”174计算机应用2003年这里由于通配符(% )在搜寻词首出现因此数据库不会再建立唯- -索引以去掉重复行,最后从这个临时表中计算结使用ld索引。但即使下面的语句: .果。因此,实际过程没有利用Id上索引。根据作者实验表selet * from record where Id like 23%'明,当数据量上一百万时,后者的执行时间只有前者的1/16。也大大不如下面的SQL语句执行快:为了提高效率,有时候无法简单的用UNION来解决问sele from rerd whereld > = 23000 ( Id列的定义为char(5) )题,这时可以考虑用存储过程,如这条语句:同理在使用IN关键字的时候,尽量用EXISTS来代替,可elet sum( salay) from rcord where Id in (0',1)以更好的利用优化器对SQL语句作出优化处理,使语句执行用存储过程改写:更快。例如:create proe count _employ 晒.where colunn in( sleet * from .... where ..declre @ a int就不如:declare @ b int..where exists (slect 'X from ... where ..declare @c int●拆分语句declare @ d char( 10)语句并不是越简洁越好,有时迫不得以还要以牺牲语句begin的复杂度来换取效率。常用的方式有:UNION,临时表,存储过程。例如以下语句:@b= slee sum( salury) from rcord where Id='selet * from record where Id in('0','1)endselet @c=@a+@b最好改写成:select @ d = covr( char( 10), @ c)(eleet * from rcord where Idin = '0) UNIONprint @dselcct ◆from reord where ld = '1')参考文献原因:where条件中的'in'在逻辑上相当于'or’,所以语法[{1] Slberchee A.数据库系统概念[ M].北京:机械工业出版社，分析器会将in('0' ,'1')转化为Id='0' or Id='1'来执行。我1999.们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用Id上的索引:但实际上，它却采用了“OR策略”,即(2) 王瑞 数据库系统瓯理教程[ M.北京:清华大学出版社200先取出满足每个or子句的行,存人临时数据库的工作表中，[3]薛华臧. 管理信息系统[ M].北京:清华大学出版社, 1993.(上接第171页)下面给出简化的客户端的代码://获得数据库连接public static void main( String[] arp)conn = getConnection();pstmt = conn. prepureStatement( " insert into Account( accountID,Account account = null;name, blance) valuee(?,?, ?)");petmt. setSring( 1, accountID); .pstmt. seSting(2, name);//获得一个账户本地对象的引用pstmt. setDouble(3, balance);Context ctx = new InitialContext( System. getPropertie());pstmt. exuetuplate();//AccountHome 是EJB对象本地接口的jndi别名//生成新的主健并返回它AccountHome home = ( AccountHone) etx. . lookupretum accountID;("CcountHome"); .Home. create(“001", "kate"); .catch( Exception e)Enumeration e = home. fndByName( "kate");{ throw new CreateException( e. toString());}finally//释放数据库连接可以看出,客户端只能看见并且调用EJB远程接口(即本例中的Account 接口)中提供的服务接口。至于这些方法的实现,以及怎样与数据库进行交互，客户端一概不知。petmt. cose(); .conn. close();4结束语catch( Exception)随着现代企业规模的不断增长和Intemet的发展,三层体(}系结构必将取代传统的C/S两层结构。而EJB组件就是三层体系结构实现的关键，它能显著的提高效率,缩短开发周期,增加系统的灵活性和易维护性。//获得数据库连接的方法:public Connection getConction() throws SQLExcepion[String url = "jdbe: odbe: Bank"[1]中国煤化工s, 黄志聪，等译.北京://Bank是Accont表所在的数据库;retum DriverManager. getConnection( wurl, prope);[2]TYHC N M H G专家级解决方案[M].//prope是系统的环境属性张志伟，谭邵松。张明杰,导译.北京:衢华大学出版社, 2001.