數據庫性能優(yōu)化

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