第23卷計算機應(yīng)用Vol. 232003年6月Computer ApplicationsJune, 2003文章編號:1001 -9081(2003)06Z -0172 -03數(shù)據(jù)庫性能優(yōu)化呂華,杜忠軍(四川大學(xué)計算機系,四川成都610065)摘要:文中介紹正確設(shè)計一個數(shù)據(jù)庫的經(jīng)驗,其重點是提高其性能,分析和比較不同設(shè)計方法給數(shù)據(jù)庫性能帶來的影響，同時給初學(xué)者-點建議。關(guān)鍵詞:數(shù)據(jù)庫;性能;優(yōu)化中圖分類號: TP311.53文獻標識碼:A能問題就會不斷出現(xiàn)。如果軟件中還有大量復(fù)雜的數(shù)據(jù)處理引言和數(shù)據(jù)庫對象,又或者涉及到分布式數(shù)據(jù)庫的聯(lián)機分析決策對一個要處理.上百萬數(shù)據(jù)量的大型數(shù)據(jù)庫系統(tǒng)，比如聯(lián)支持系統(tǒng),那么整個系統(tǒng)的響應(yīng)速度就會變得很慢,或者根本機事務(wù)處理OLIP或決策支持系統(tǒng)DSS,要求很高數(shù)據(jù)庫端就無法忍受。據(jù)作者實驗表明,兩個同樣功能的數(shù)據(jù)庫軟件的執(zhí)行效率。雖然各種數(shù)據(jù)庫如Sybase、Oracle、SQL Server、在執(zhí)行時的效率比較，在數(shù)據(jù)量達到百萬級,經(jīng)過優(yōu)化和正確DB2有各自的性能優(yōu)勢和優(yōu)化器,但是作為開發(fā)人員還是必設(shè)計的軟件是劣質(zhì)軟件的上百倍甚至是幾百倍。可見對于一需要注意數(shù)據(jù)庫產(chǎn)品的性能調(diào)節(jié)和SQL語句的高質(zhì)量編寫，個系統(tǒng)不是簡單地能實現(xiàn)其功能就可,而是要規(guī)范數(shù)據(jù)庫的否則即使選擇再好的數(shù)據(jù)庫產(chǎn)品也不能彌補軟件設(shè)計的低劣設(shè)計,寫出高質(zhì)量的SQL語句,提高系統(tǒng)的可用性。所帶來的性能損失。作者發(fā)現(xiàn)有很多初學(xué)者把數(shù)據(jù)庫的前端開發(fā)產(chǎn)品(如2數(shù)據(jù)庫產(chǎn)品的選擇.PowerBuilder, Delphi等)作為重點學(xué)習(xí)對象,注重于漂亮的界顯然做數(shù)據(jù)庫項目時后端的數(shù)據(jù)庫用什么產(chǎn)品是個重要面和花哨的功能而忽略了整個數(shù)據(jù)庫端的效率。雖然友好的指標。既要考慮開發(fā)難度,又要考慮數(shù)據(jù)庫產(chǎn)品的性能、產(chǎn)品人機界面也很重要,但軟件的性能和效率也是不容忽視的,否則就會本末倒置。隨著軟件運行后數(shù)據(jù)量的增加,軟件的性不同版本提供的功能和價格因素。以下這個表僅供參考:產(chǎn)品DB性能指標SQL Server 2000Oracle 8iDB2安全性沒有獲得任何安全證書獲得最高認證級別的Iso標準認證執(zhí)行效率多用戶時性能不佳性能最高，保持Windows NT下的TPC-D適用于數(shù)據(jù)倉庫和在線事物處理，性能較高和TPC-C的世界記錄Oraele平行服務(wù)器通過使-組結(jié)點共享DB2具有很好的并行性。DB2把數(shù)據(jù)庫管理并行實施和共存模型并不成熟。很難處理日益同一簇中的工作來擴展 Windows NT的能擴充到了并行的、多節(jié)點的環(huán)境數(shù)據(jù)庫分并行性區(qū)是數(shù)據(jù)庫的一部分， 包含自己的數(shù)據(jù)、索增多的用戶數(shù)和數(shù)據(jù)卷。伸縮性有限力，提供高可用性和高伸縮性的簇的解決引配置文件和事務(wù)日志。數(shù)據(jù)庫分區(qū)有時方案.被稱為節(jié)點或數(shù)據(jù)庫節(jié)點只能在Windows上運行，沒有絲毫的開放性，操作系統(tǒng)的系統(tǒng)的穩(wěn)定對數(shù)據(jù)庫是十分重要能在所有主流平臺上運行(包括能在所有主流平臺上運行(包括Windows)。的。Windows 9X系列產(chǎn)品是偏重于桌面應(yīng)用，Windows)。完全支持所有的工業(yè)標準。最適于海量數(shù)據(jù)。DB2在企業(yè)級的應(yīng)用最為開放性NT Server只適合中小型企業(yè)。而且Windows采用完全開放策略?？梢允箍蛻暨x擇最適廣泛,在全球的500 家最大的企業(yè)中兒乎平臺的可靠性，安全性和伸縮性是非常有限的。合的解決方案。對開發(fā)商全力支持85%以上用DB2 數(shù)據(jù)庫服務(wù)器,而國內(nèi)到它不象UNX那樣久經(jīng)考驗，尤其是在處理大1997年約占5%數(shù)據(jù)量的關(guān)鍵業(yè)務(wù)時cIs結(jié)構(gòu),只支持Windows客戶,可以用ADO、較復(fù)雜,同時提供GUI和俞令行.在操作簡單,同時提供GUI 和命令行，在其它評價DAO、 OLEDB. ODBC連接。操作簡單,但只Windows NT和UNX下操作相同。多層Windows NT和UNIX下操作相同?？缙脚_，次網(wǎng)絡(luò)計算，支持多種工業(yè)標準，可以用有圈形界面ODBC. JDBC、0CI等網(wǎng)絡(luò)客戶連接多層結(jié)構(gòu)，支持0DBC、JDBC等客戶中國煤化工收稿日期:2002-11-25作者簡介:昌華(1978-),男,碩士研究生,主要研究方向:數(shù)據(jù)庫原理及應(yīng),MYHCN M H G杜忠軍(1966-),男,副教授,主要研究方向:數(shù)據(jù)庫原理及應(yīng)用多媒體數(shù)據(jù)庫、面向?qū)ο髷?shù)據(jù)庫.呂華等:數(shù)據(jù)庫性能優(yōu)化6月如果應(yīng)用程序設(shè)計不當(dāng)就會造成過多的網(wǎng)絡(luò)V0,嚴重影響3優(yōu)化的數(shù)據(jù)庫設(shè)計整個系統(tǒng)的性能。通常對于C/S系統(tǒng)網(wǎng)絡(luò)流量是整個系統(tǒng)要提高- -個數(shù)據(jù)庫產(chǎn)品的質(zhì)量,最重要的就是要提高其的瓶頸。提高網(wǎng)絡(luò)vo原則是盡量使數(shù)據(jù)處理在數(shù)據(jù)庫端批執(zhí)行效率。整個數(shù)據(jù)庫產(chǎn)品的性能取決于三方面:前端功能量執(zhí)行 ,而網(wǎng)絡(luò)Vvo只是一次傳輸最終結(jié)果,切忌不要是因為的執(zhí)行效率，網(wǎng)絡(luò)傳輸vo效率;后端數(shù)據(jù)庫的執(zhí)行效率。性一個很小的請求就要傳輸 -次。一般常用的方法有:在服務(wù)能的瓶頸在于網(wǎng)絡(luò)vO。所以在設(shè)計和開發(fā)過程中-般要注器端大量使用存儲過程 觸發(fā)器和SQL函數(shù)的方法。初學(xué)者往往比較了解前端語句,而不熟悉數(shù)據(jù)庫端的SQL的寫法，意以下技術(shù)運用:(1)合理利用索引(群集索引和組合索引)為了圖方便直接在前端寫嵌人式SQL語句,殊不知這樣會造關(guān)于索引的建立有很多原則,以下是一些基本的原則:成網(wǎng)絡(luò)VO的急劇增大,嚴重降低性能。例如:.首先不要認為索引越多越好,濫用索引會嚴重降低表SELECT Depl, SUM( Selary) FROM recod WHERE DepINo>0 andDeptNo < 1000的更新速度;.通常要在那些經(jīng)常進行連接,但是沒有指定為外鍵的這條語句本該執(zhí)行- 次網(wǎng)絡(luò)I0,但是如果不用數(shù)據(jù)庫端的SUM函數(shù),那么同樣的語句就會造成1000次網(wǎng)絡(luò)V0列上建立索引;在條件表達式中經(jīng)常用到的不同值較多的列上建立的開支。再如:SELECT sum((a. Price+ b. Pice)) proft froma, b wherea.id =檢索。比如在職工表的“性別”列上只有“男"與“女”兩個不b.id anda.id < 0000同值,因此就無必要建立索引;利用系統(tǒng)工具對索引進行優(yōu)化，比如DB2的REORC,這樣- -個稍微復(fù)雜的SQL語句,初學(xué)者寫不出這樣復(fù)雜的語句時就簡單的在客戶端寫嵌入式SQL(如下):Oracle 的tbeheck等;float ld. a,ld b, Id. proft.如果經(jīng)常對某幾個列查詢,可以對這幾個列建立組合int索引。for 1=0 two 10000 do下面就群集和組合索引進行探討，缺省情況下建立的索引是非群集索引,而經(jīng)常同時存取多列,且每列都含有重復(fù)值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簡單的說群集和非群集索引的差別就是群集索引要求存儲時數(shù)據(jù)在物理.上按順序排在數(shù)據(jù)頁上,重復(fù)值也排列在一起,因而在有“范圍查找”時(有關(guān)鍵字between,>,<,>=,(3)使用優(yōu)化的SQL語句<= ,orderby ,groupby) ,可以先找到這個范圍的起點和終點，在軟件業(yè)有句名言叫“用80%的時間寫出20%的代碼"，且只在這個范圍內(nèi)掃描數(shù)據(jù)頁,避免了大范圍的掃描,提高了可見寫程序時重要的并非數(shù)量的多少而是質(zhì)量的高低。所謂查詢速度;而非群集索引則不要求數(shù)據(jù)的順序存放,因此非群優(yōu)化的 sQL語句是指寫出的SQL語句效率高、執(zhí)行快。盡量集索引的表在更新時數(shù)據(jù)隨機存放,速度更快。因此合理的利用索引 寫出WHERE子句可優(yōu)化的代碼。- -般應(yīng)遵循以索引設(shè)計:主要看對這個表長期進行的操作,存多查少,則只需下原則:建立缺省索引,即非群集索引;如果此表每次都是少量存儲，盡量不要對列進行操作,例如: .而又長期要執(zhí)行范圍查詢的話,則應(yīng)考慮在有大量重復(fù)值且se●from rcond where sbring(Id,1,4)) = 2313'經(jīng)常有范圍查詢(between, >，<, >=, <= )和orderby 、select * frorm rcord where Salary/2 < 1200groupby發(fā)生的列上建立群集索引。例:where子句中對列的任何操作結(jié)果都是在SQL運行時逐select Dept, sum( Salay) from record group by Dept列計算得到的,因此它不得不進行表搜索,而沒有使用該列上這樣一個簡單的SQL語句在處理的數(shù)據(jù)量達到- -百萬面的索引 ;如果這些結(jié)果在查詢編譯時就能得到,那么就可以行的時候,在Dept列上建群級索引時執(zhí)行時間是建非群集索被 SQL優(yōu)化器優(yōu)化,使用索引,避免表搜索,因此將SQL重寫引的1/3(時間分別為22秒和67秒) ,而如果根本就沒建索成 下面這樣:引,這個簡單而常用的SQL語句大概要執(zhí)行近3分鐘。gelect * from record where Id like '2313% '組合索引是指由表的多個列組成的- -個索引。利用這種sele●from record where Selay < 1200*2索引的特點是注意盡量使關(guān)鍵查淘形成索引覆蓋,其前導(dǎo)列兩相比較,當(dāng)表中的數(shù)據(jù)量達到十萬數(shù)量級,后者的速度一定是使用最頻繁的列。例:是前者的5倍。seleet Id, Salary from record where Id > 20000 and避免相關(guān)子查詢ld < 50000'一個列的標簽同時在主查詢和where子句中的查詢中出組合索引必須以Id列和Salay列建立才能以Id列為前現(xiàn),那 么很可能當(dāng)主查詢中的列值改變之后,子查詢必須重新導(dǎo)列,體現(xiàn)出索引的優(yōu)勢。相反如果以Salary列和Id列建立查詢- 次。 查詢嵌套層次越多,效率越低,因此應(yīng)當(dāng)盡量避免索引的話,SQL語句根本就利用不上索引。執(zhí)行時間分別為子查詢。 如果子查詢不可避免，那么要在子查詢中過濾掉盡2秒和49秒?？梢娺@種性能差別有多大?？赡芏?2)網(wǎng)絡(luò)IV0效率中國煤化工c/s系統(tǒng)中,客戶端的應(yīng)用程序?qū)⑻幚硪蟀l(fā)給數(shù)據(jù)庫HC N M H G使用索引,如:服務(wù)器,數(shù)據(jù)庫服務(wù)器接受請求將處理的數(shù)據(jù)返回客戶端。select * Throm Tecord where Id like '響23%”174計算機應(yīng)用2003年這里由于通配符(% )在搜尋詞首出現(xiàn)因此數(shù)據(jù)庫不會再建立唯- -索引以去掉重復(fù)行,最后從這個臨時表中計算結(jié)使用ld索引。但即使下面的語句: .果。因此,實際過程沒有利用Id上索引。根據(jù)作者實驗表selet * from record where Id like 23%'明,當(dāng)數(shù)據(jù)量上一百萬時,后者的執(zhí)行時間只有前者的1/16。也大大不如下面的SQL語句執(zhí)行快:為了提高效率,有時候無法簡單的用UNION來解決問sele from rerd whereld > = 23000 ( Id列的定義為char(5) )題,這時可以考慮用存儲過程,如這條語句:同理在使用IN關(guān)鍵字的時候,盡量用EXISTS來代替,可elet sum( salay) from rcord where Id in (0',1)以更好的利用優(yōu)化器對SQL語句作出優(yōu)化處理,使語句執(zhí)行用存儲過程改寫:更快。例如: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的復(fù)雜度來換取效率。常用的方式有: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'在邏輯上相當(dāng)于'or’,所以語法[{1] Slberchee A.數(shù)據(jù)庫系統(tǒng)概念[ M].北京:機械工業(yè)出版社，分析器會將in('0' ,'1')轉(zhuǎn)化為Id='0' or Id='1'來執(zhí)行。我1999.們期望它會根據(jù)每個or子句分別查找,再將結(jié)果相加,這樣可以利用Id上的索引:但實際上，它卻采用了“OR策略”,即(2) 王瑞 數(shù)據(jù)庫系統(tǒng)甌理教程[ M.北京:清華大學(xué)出版社200先取出滿足每個or子句的行,存人臨時數(shù)據(jù)庫的工作表中，[3]薛華臧. 管理信息系統(tǒng)[ M].北京:清華大學(xué)出版社, 1993.(上接第171頁)下面給出簡化的客戶端的代碼://獲得數(shù)據(jù)庫連接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//釋放數(shù)據(jù)庫連接可以看出,客戶端只能看見并且調(diào)用EJB遠程接口(即本例中的Account 接口)中提供的服務(wù)接口。至于這些方法的實現(xiàn),以及怎樣與數(shù)據(jù)庫進行交互，客戶端一概不知。petmt. cose(); .conn. close();4結(jié)束語catch( Exception)隨著現(xiàn)代企業(yè)規(guī)模的不斷增長和Intemet的發(fā)展,三層體(}系結(jié)構(gòu)必將取代傳統(tǒng)的C/S兩層結(jié)構(gòu)。而EJB組件就是三層體系結(jié)構(gòu)實現(xiàn)的關(guān)鍵，它能顯著的提高效率,縮短開發(fā)周期,增加系統(tǒng)的靈活性和易維護性。//獲得數(shù)據(jù)庫連接的方法:public Connection getConction() throws SQLExcepion[String url = "jdbe: odbe: Bank"[1]中國煤化工s, 黃志聰，等譯.北京://Bank是Accont表所在的數(shù)據(jù)庫;retum DriverManager. getConnection( wurl, prope);[2]TYHC N M H G專家級解決方案[M].//prope是系統(tǒng)的環(huán)境屬性張志偉，譚邵松。張明杰,導(dǎo)譯.北京:衢華大學(xué)出版社, 2001.