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