科技信息博士●專家論壇基于ORACLE的查詢優(yōu)化寧波大紅鷹職業(yè)技術(shù)學(xué)院唐興通危烽吉林大學(xué)軟件學(xué)院危明摘要]Oracle數(shù)據(jù)庫系統(tǒng)性能優(yōu)化是一個(gè)復(fù)雜的系統(tǒng)工程。本文論述了通過Oracle數(shù)據(jù)庫查詢的工作機(jī)制,并分析了-些影響應(yīng)用性能的可能因素,主要從SQL語句的方面優(yōu)化進(jìn)行闡述,給出了--些可行的調(diào)整數(shù)據(jù)庫應(yīng)用性能的策略與方法。[關(guān)鍵詞]ORCLE數(shù)據(jù)庫優(yōu)化 索引RDBMSORACLE是一個(gè)優(yōu)秀的大型關(guān)系型數(shù)據(jù)庫,支持關(guān)系對(duì)象下來對(duì)輸出的SQL語句進(jìn)行過濾,選出具有不同執(zhí)行計(jì)劃的模型的分布式數(shù)據(jù)庫產(chǎn)品。目前在銀行.電信、證券市場的數(shù)據(jù)SQL語句(不同的執(zhí)行計(jì)劃意味著不同的執(zhí)行效率),最后,對(duì)庫方面占有絕對(duì)的優(yōu)勢(shì)。具權(quán)威部門的統(tǒng)計(jì),在全世界的范圍得到的SQL語句進(jìn)行批量測(cè)試,找出性能最好的SQL語句內(nèi),ORACLE市場占有率為46. 1%是SQLSERVER的7倍,而ORACLE的優(yōu)化器共有3種: a. RULE (基于規(guī)則) bIBM的DB2市場占有率也只有23.6%。一個(gè)選用ORACLE為COST (基于成本) c. CHOOSE (選擇性).數(shù)據(jù)庫的系統(tǒng)的規(guī)模一般比較大,用戶-般比較多而且空間上基于開銷在基于開銷的優(yōu)化策略中(CBO),對(duì)于給定查詢比較分離,事務(wù)處理的效率對(duì)于這樣的系統(tǒng)來說是至關(guān)重要的,生成多個(gè)執(zhí)行計(jì)劃,然后對(duì)每個(gè)計(jì)劃估算開銷查詢優(yōu)化程序選但是在實(shí)際應(yīng)用中,隨著系統(tǒng)數(shù)據(jù)庫中數(shù)據(jù)的增加,訪問量的加用估算開銷最低的計(jì)劃,(代價(jià)= I/O代價(jià)+ CPU代價(jià)+內(nèi)大，數(shù)據(jù)庫系統(tǒng)性能將會(huì)下降。這樣,就需要我們數(shù)據(jù)庫管理人存代價(jià))。員對(duì)數(shù)據(jù)庫進(jìn)行性能調(diào)整,使數(shù)據(jù)庫系統(tǒng)能夠在合理、可靠、低另外基于規(guī)則的優(yōu)化程序(RBO)不同于基于開銷的優(yōu)化程風(fēng)險(xiǎn)和高效率的狀態(tài)下運(yùn)行。序(CSO)。它按照-系列既成的規(guī)則來對(duì)SQL語句進(jìn)行分析，數(shù)據(jù)庫應(yīng)用的性能調(diào)優(yōu)是一個(gè)系統(tǒng)工程。在實(shí)踐中必須分從而得出較好的執(zhí)行策略。RBO在分析過程中不考慮開銷因析影響性能的各方面因素.針對(duì)不同的具體情況選擇合理的優(yōu)素。實(shí)際應(yīng)用中-般不考慮化措施,對(duì)于ORACLE來說性能的優(yōu)化主要有:內(nèi)存分配的優(yōu)數(shù)據(jù)庫的反應(yīng)速度經(jīng)常體現(xiàn)在優(yōu)化器的優(yōu)化算法上。此外，化,1/O的調(diào)整,SQL語句的優(yōu)化與調(diào)整以及數(shù)據(jù)庫各種動(dòng)態(tài)參優(yōu)化器的優(yōu)化策略還與所查詢表的內(nèi)容和其他一些與服務(wù)器有數(shù)的調(diào)整。本文主要探討有關(guān)SQL語句的優(yōu)化與調(diào)整的內(nèi)容。關(guān)的因素有關(guān),如Cache大小.Cache策略.I/O大小等。通常來.數(shù)據(jù)庫性能優(yōu)化目標(biāo)就是使數(shù)據(jù)庫應(yīng)用運(yùn)行得更快。“更說硬盤訪問是成本最高的操作。快”通常指的是更高的吞吐量或者說是更短的應(yīng)用響應(yīng)時(shí)間。數(shù)2.數(shù)據(jù)庫性能優(yōu)化目標(biāo)就是使數(shù)據(jù)庫應(yīng)用運(yùn)行得更快據(jù)庫性能優(yōu)化的基本原則是通過盡可能少的磁盤訪問獲得所需“更快”通常指的是更高的吞吐量或者說是更短的應(yīng)用響應(yīng)要的數(shù)據(jù)。時(shí)間。SQL語句優(yōu)化的要點(diǎn)，就是盡量減少頁面存取，或使用內(nèi)實(shí)際數(shù)據(jù)庫使用過程中大部分的性能問題是由于程序員或存的頁面代替訪問磁盤。在查詢優(yōu)化過程中要對(duì)語法樹中的每用戶使用了不恰當(dāng)?shù)牟樵冋Z句造成的,甚至達(dá)到80%以上的問.一個(gè)操作,計(jì)算各種執(zhí)行算法的執(zhí)行代價(jià)，選擇代價(jià)小的執(zhí)行算題,因此SQL語句的質(zhì)量對(duì)整個(gè)系統(tǒng)效率有相當(dāng)影響。SQL語法。其中就要涉及到從關(guān)系代數(shù)方面的一些優(yōu)化規(guī)律:句優(yōu)化的要點(diǎn)，就是盡量減少頁面存取,或使用內(nèi)存的頁面代替(1)選擇操作是根據(jù)條件對(duì)關(guān)系做水平分割,即選取符合條訪問磁盤。件的元組。應(yīng)盡可能早地執(zhí)行選擇操作,以得到較小的中間關(guān)我們要想很好的處理好查詢優(yōu)化,可以借鑒一些目前商品系,這樣可以減少運(yùn)算量和讀外存的次數(shù)化的優(yōu)化器中,它們是如何進(jìn)行處理的，這樣我們?cè)谟脩魰鴮懖?2)把笛卡爾積和選擇操作合并成F聯(lián)接運(yùn)算,也就是做詢時(shí)就有個(gè)好的思路和方法。笛卡爾積操作時(shí)立即檢查聯(lián)接后的元組是否滿足選擇條件,并.1. RDBMS優(yōu)化器決定取舍,以減少系統(tǒng)開銷。隨著人工智能技術(shù)的發(fā)展和在數(shù)據(jù)庫優(yōu)化領(lǐng)域應(yīng)用的深(3)選擇和投影都是--元操作符,而關(guān)系的元組是獨(dú)立的單入,在20世紀(jì)90年代末優(yōu)化技術(shù)取得了突破性的進(jìn)展,出現(xiàn)了位,可以對(duì)每個(gè)元組連續(xù)做-串操作,即同時(shí)計(jì)算-連串的選擇人工智能自動(dòng)SQL優(yōu)化。人工智能自動(dòng)SQL優(yōu)化的本質(zhì)就是和投影操作,以免多次掃描文件,從而節(jié)省時(shí)間。借助人工智能技術(shù),自動(dòng)對(duì)SQL語句進(jìn)行重寫,找到性能最好(4)如果在-個(gè)表達(dá)式中多次出現(xiàn)-個(gè)子表達(dá)式,應(yīng)該將此的等效SQL語句。像LECCO SQL Expert就采用了這種人工子表達(dá)式預(yù)先計(jì)算出結(jié)果并保存起來,以免重復(fù)計(jì)算。智能技術(shù),其SQL Expert支持Oracle .Sybase、MS SQL Server(5)根據(jù)具體情況對(duì)文件進(jìn)行排序或建立索引文件,即對(duì)關(guān)和IBMDB2數(shù)據(jù)庫平臺(tái)。其突出特點(diǎn)是自動(dòng)優(yōu)化SQL語句。能.系文件進(jìn)行預(yù)處理,可以提高關(guān)系聯(lián)接的速度。通過比較源SQL和待選SQL的不同之處,為開發(fā)人員提供“邊(6)當(dāng)查詢涉及多個(gè)關(guān)系時(shí)最好使用嵌套結(jié)構(gòu),因?yàn)榍短撞樽鲞厡W(xué)式訓(xùn)練”,迅速提高開發(fā)人員的SQL編程技能等。詢的執(zhí)行效率比聯(lián)接查詢的笛卡爾積的效率高?！癝QL 語法優(yōu)化器”的工作原理大致如下:輸入一條初始中國煤化工意的問題SQL語句，“人工智能反饋式搜索引擎"對(duì)輸入的SQL語句結(jié)最有效的方法之一合檢測(cè)到的數(shù)據(jù)庫結(jié)構(gòu)和索引進(jìn)行重寫,產(chǎn)生N條等效的SQLMYHCNMH G校,是優(yōu)化的基礎(chǔ),建立索引語句輸出,產(chǎn)生的N條等效SQL語句再送入“人工智能反饋式的根本目的是提高查詢效率。不論怎樣優(yōu)化都會(huì)充分利用索引搜索引擎”進(jìn)行重寫,直至無法產(chǎn)生新的輸出或搜索限額滿,接進(jìn)行記錄定位,減少磁盤的讀寫次數(shù),從而達(dá)到提高查詢速度的基金項(xiàng)目:本文獲得寧波大紅鷹職業(yè)技術(shù)學(xué)院軟件學(xué)院的青年基金的贊助。(06-2070821高維數(shù)據(jù)的聚類分析)-7-科技信息博士●專家論壇目的。-個(gè)建有合理索引的數(shù)據(jù)庫應(yīng)用系統(tǒng)可能比一個(gè)沒有建數(shù)據(jù),而不是讓用戶繼續(xù)等待。立索引的數(shù)據(jù)庫應(yīng)用系統(tǒng)效率高幾+倍,但并不是索引越多越3.6使用顯式的游標(biāo)(Cursors)好，在那些經(jīng)常需要修改的數(shù)據(jù)列上建立索引,將導(dǎo)致索引B*使用隱式的游標(biāo)將會(huì)執(zhí)行兩次操作:第1次檢索記錄;第2樹的不斷重組,造成系統(tǒng)性能的下降和存儲(chǔ)空間的浪費(fèi)。次檢查TOOMANYROWS這個(gè)EXCEPTION。而顯式游標(biāo)不.(1)應(yīng)對(duì)經(jīng)常出現(xiàn)在檢索條件中的數(shù)據(jù)域建立索引,如果執(zhí)行第2次操作,速度提高。某些數(shù)據(jù)域經(jīng)常-起出現(xiàn)在檢索條件中,那么建立復(fù)合索引。3.7 LIKE關(guān)鍵字的處理(2)在頻繁進(jìn)行排序或分組(即進(jìn)行g(shù)roup by或order by例如:學(xué)生表中要查詢2005級(jí)學(xué)生的情況: SELECT:操作)的列上建立索引。應(yīng)對(duì)經(jīng)常用于表連接操作中的數(shù)據(jù)域FROM學(xué)生WHERE XH LIKE“05___” 即使在xh字段建立索引,如果是多域連接則建立復(fù)合索引。上建立了索引,在這種情況下也還是采用順序掃描的方式。如果(3)在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索把語句改為SELECT * FROM學(xué)生WHERE XH >“05000”引,在不同值少的列上不要建立索引。比如在雇員表的“性別”列在執(zhí)行查詢時(shí)就會(huì)利用索引來查詢,顯然會(huì)大大提高速度。上只有“男”與“女"兩個(gè)不同值,因此就沒有必要建立索引。如果3.8使用連接,避免對(duì)數(shù)據(jù)表的順序存取建立索引不但不會(huì)提高查詢效率,反而會(huì)嚴(yán)重降低更新速度。在嵌套查詢中,對(duì)表的順序存取可能會(huì)采用順序存取來進(jìn)(4)對(duì)于復(fù)合索引,索引中的數(shù)據(jù)域的順序要和實(shí)際應(yīng)用的行,我們可以使用并集來避免順序存取。雖然在所有的檢查列上檢索和連接順序保持一致。都有索引,下面的查詢將對(duì)學(xué)生表執(zhí)行順序操作:SELECT*(5)不要在索引中包含經(jīng)常修改、增加和刪除的列。FROM學(xué)生WHERE (TSH = 00810 AND XH> 05000) OR(6)ORACLE在外鍵上并不建立索引,因此在利用外鍵連XH=04008雖然在TSH和XH上建有索引,但是這語句中仍接的表格中，外鍵上也應(yīng)該建立索引。然使用順序存取路徑掃描整個(gè)表。如果改為:SELECT(7)很少在where子句中出現(xiàn)的列,不宜建立索引。FROM學(xué)生WHERE TSH = 00810 AND XH> 05000 UNION(8)不能用null作索引,任何包含null值的列都將不會(huì)被SELECT*FROM學(xué)生WHEREXH=04008,可以避免順序包含在索引中。也就是說如果某列存在空值,即使對(duì)該列建索引存取,是高效的。也不會(huì)提高查詢效率。3.9SELECT子句中避免使用.(9)對(duì)查詢型的表,建立多個(gè)索引會(huì)大大提高查詢速度,對(duì)如果我們要選取表中所有的列，常用的方法就是使用* ,其更新型的表,如果索引過多,會(huì)增大開銷。實(shí)這樣的操作是低效果的，日常使用過程中如果表中的列沒有ORACLE采用兩種訪問表中記錄的方式:1.全表掃描,全幾個(gè),最好把這幾個(gè)字段直接寫出來。表掃描就是順序地訪問表中每條記錄。ORACLE采用一次讀結(jié)束語入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描。在海量數(shù)以上只是一些常用的優(yōu)化ORACLE數(shù)據(jù)查詢的方法,其據(jù)庫中要避免。2.通過ROWID訪問表,你可以采用基于他還有好多像對(duì)數(shù)據(jù)表的切割，使用存儲(chǔ)過程,避免不兼容數(shù)據(jù)ROWID的訪問方式情況.提高訪問表的效率, ROWID包含了的使用等等?？傮w說來ORACLE數(shù)據(jù)庫的優(yōu)化是一個(gè)動(dòng)態(tài),系表中記錄的物理位置信息。ORACLE采用索引(INDEX)實(shí)現(xiàn)統(tǒng)的過程。僅僅調(diào)整某一方面,不考慮整體情況很難收到預(yù)期的了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系。通常索引效果。對(duì)于查詢是這樣，對(duì)于整個(gè)系統(tǒng)也是同樣的道理。因此,對(duì)提供了快速訪問ROWID的方法,因此那些基于索引列的查詢ORACLE相關(guān)的查詢調(diào)整和優(yōu)化,需要我們不斷總結(jié)經(jīng)驗(yàn)還得就可以得到性能上的提高。結(jié)合特定的系統(tǒng)來進(jìn)行操作。3.2在WHERE子句中使用子查詢時(shí)盡量使用EXISTS而參考文獻(xiàn)不是IN。[1]William G Page等著;王磊,蔣蕊,王眾等譯. Oracle 8/在使用EXIST中，DBMS系統(tǒng)會(huì)首先檢查主查詢,然后運(yùn)8i開發(fā)使用手冊(cè)[M].北京:機(jī)械工業(yè)出版社,2000.行子查詢直到找到第-個(gè)匹配項(xiàng),這就節(jié)省了時(shí)間。DBMS系統(tǒng)[2]Marlene Theriault, Rachel Carmichael. Oracle 數(shù)據(jù)庫在執(zhí)行IN子查詢時(shí),首先執(zhí)行子查詢,并將獲得的結(jié)果列表存管理員基礎(chǔ)教程[M].北京:機(jī)械土業(yè)出版社,2000.放在一個(gè)加了索引的臨時(shí)表中。待子查詢執(zhí)行完畢,存放在臨時(shí)[3]George KOCh、 Keviri Loney等著;梅剛等譯. Oracle8表中以后再執(zhí)行主查詢。這也就是使用EXISTS比使用IN通常完全參考手冊(cè)(四)[M].北京:機(jī)械工業(yè)出版社,1998..查詢速度快的原因。[4]Singh, Leigh, Zafianet al等著;史森、夏麗麗等譯. Or-3.3盡量多使用COMMITacle數(shù)據(jù)庫開發(fā)指南[M].北京:清華大學(xué)出版社,1998.在自動(dòng)事務(wù)提交模式下,每執(zhí)行一個(gè)SQL語句就進(jìn)行一次[5]翁正科,王新英. racle8.X For Windows NT實(shí)用教程事務(wù)提交,程序的開銷很大?？梢酝ㄟ^取消自動(dòng)提交模式,將[M].北京:清華大學(xué)出版社,1998.SQL語句匯集成-組執(zhí)行后集中提交,程序還可以通過顯式地[6]Jonathan Gennick , Carol McCullough - Dieter, Gerrit .用COMMIT和ROLLBACK進(jìn)行提交和回滾事務(wù)。只要有可一Jan Linker著、趙艷勤,劉冠英,秦玉杰等譯. ORACLE 8i能,在程序中盡量多使用COMMIT,這樣程序的性能得到提DBA寶典[M].北京:電子工業(yè)出版社,2001.高，需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少.COMMIT[7]Colbum Rafe. SQL使用全書[M].北京:電子工業(yè)出版所釋放的資源: a.回滾段上用于恢復(fù)數(shù)據(jù)的信息. b. 被程序社,200芐于Oracle數(shù)據(jù)庫性能優(yōu)化中國煤化工語句獲得的鎖c. redo log buffer中的空間d. ORACLE為管理的研究YH上述3種資源中的內(nèi)部花費(fèi)。c N M H G軍優(yōu)化設(shè)計(jì)案[J].計(jì)算機(jī)系3.4用TRUNCATE替代DELETE .統(tǒng)應(yīng)用,2001.(5):16- 18.3.5檢索大量數(shù)據(jù)時(shí)費(fèi)時(shí)很長,設(shè)置行預(yù)取數(shù)則能改善系.[9][美JAbraham Silberschatz, Henry F. Korth, S. Sudar-統(tǒng)的工作表現(xiàn),設(shè)置一個(gè)最大值,當(dāng)SQL語句返回行超過該值，shan.數(shù)據(jù)庫系統(tǒng)概念.楊冬青,唐世渭等譯.北京:中國機(jī)械工數(shù)據(jù)庫暫時(shí)停止執(zhí)行,除非用戶發(fā)出新的指令,開始組織并顯示業(yè)出版社,2000.--8