吳健
【摘 要】相比較一般數(shù)據(jù)庫,Oracle的性能更加優(yōu)秀,存儲的級別更大,使用的范圍更加廣泛,因而對Oracle數(shù)據(jù)庫的研究才更顯得有必要。對于Oracle數(shù)據(jù)庫性能的研究中, 查詢語句的執(zhí)行效率十分重要, 特別對于大型數(shù)據(jù)庫系統(tǒng), SQL查詢語句效率的高低相比可能有上百倍之差。下文將重點介紹了Oracle數(shù)據(jù)庫SQL查詢語句的處理過程, 再對不同優(yōu)化器工作原理進行了解析, 分析若干方法去提升Oracle數(shù)據(jù)庫查詢優(yōu)化性能, 同時也能使我們進一步去了解Oracle數(shù)據(jù)庫,方便我們的工作生活。
【關(guān)鍵詞】Oracle數(shù)據(jù)庫;SQL語句;優(yōu)化;效率
0 導(dǎo)言
數(shù)據(jù)庫技術(shù)是計算機科學(xué)技術(shù)發(fā)展最快、應(yīng)用最為廣泛的信息技術(shù)之一,數(shù)據(jù)庫技術(shù)已經(jīng)從相對傳統(tǒng)的商業(yè)領(lǐng)域不斷擴大到許多新的領(lǐng)域,如醫(yī)療、多媒體、經(jīng)濟、政治、科研等領(lǐng)域。其中Oracle數(shù)據(jù)庫是應(yīng)用最為廣泛的數(shù)據(jù)庫,其中在通信領(lǐng)域范圍內(nèi),全球20家排名頂尖的通信公司都是使用Oracle產(chǎn)品;在IT服務(wù)公司中,中國前100大的IT公司(HP、IBM、凱建、埃森哲、東軟等)全部都用Oracle相關(guān)技術(shù)。對Oracle數(shù)據(jù)庫的優(yōu)化研究就是進行有目的性的優(yōu)化其組件以改善Oracle數(shù)據(jù)庫的性能,即增加數(shù)據(jù)庫的吞吐量和減少數(shù)據(jù)庫的響應(yīng)時間,因而從大多數(shù)Oracle系統(tǒng)的應(yīng)用實例來觀察,其中數(shù)據(jù)庫的查詢操作在各種操作中所占的比率是最大的,查詢效率成為了影響Oracle數(shù)據(jù)庫各項性能的關(guān)鍵因素。所以本文將介紹Oracle數(shù)據(jù)庫查詢語句的一些優(yōu)化研究方案。
1 Oracle數(shù)據(jù)庫SQL語句處理過程
一般來說,數(shù)據(jù)庫處理SQL語句都要經(jīng)過三個過程:分析、執(zhí)行、返回結(jié)果,比如Congnos ReportNet通過拖放完成表現(xiàn)層后,能夠自動地生成SQL語句,然后將生成的SQL語句傳遞到Oracle數(shù)據(jù)庫中進行數(shù)據(jù)處理。
1.1 分析
分析是處理SQL語句的第一步,它包含了以下幾個方面:
(1)語法分析:Oracle是采用數(shù)據(jù)庫常用的自下向上的分析方法,包含檢驗語法規(guī)范,命名規(guī)范,它是處理SQL語句中消耗時間做多且代價最高的步驟;增加Oracle數(shù)據(jù)庫的查詢效率可以主要表現(xiàn)在綁定變量和存儲過程兩個方面:
A.綁定變量:如果使用SQL語句查詢的頻率越高,系統(tǒng)硬件消耗的資源也將會變大,以至于了減少系統(tǒng)的資源使用空間,導(dǎo)致用戶的訪問數(shù)量降低,另外同時其它查詢語句也將被從共享池中踢出, 而使用綁定變量,提交給相同對象的同樣的查詢的用戶唯一使用就能夠被重復(fù)使用,其效率能大大地增強。
B.存儲過程:存儲過程只會是在創(chuàng)建時編譯,然后的每次執(zhí)行都再也不需要進行編譯,而普通的SQL語句執(zhí)行一次都需要進行編譯執(zhí)行,相比較,盡量使用存儲過程就會增強Oracle數(shù)據(jù)庫的執(zhí)行效率。
(2)語義分析,學(xué)過編譯原理的應(yīng)該會知道這一步是相當(dāng)重要的,Oracle主要是分析SQL語句格式正確與否,各個對象是否存在,以及它是否擁有足夠的權(quán)限執(zhí)行。
SQL>SELECT DEPT_NO,DEPT_NAME,DEPT_LOC FROM DEPT;
提示:ERROR at line 1:
ORA-00942: table or view does not exist
由于查詢沒有可供訪問的DEPT(部門表)對象,因此該SQL語句無法通過語義檢測,不能查詢結(jié)果。
(3)表達(dá)式轉(zhuǎn)換,將復(fù)雜的SQL表達(dá)式轉(zhuǎn)換成為相對應(yīng)的對于基表的SQL查詢語句,寫出SQL的標(biāo)準(zhǔn)。
A.不要在WHERE中進行表達(dá)式計算,例如SELECT * FROM EMP WHERE DEPT_NO>=10*10 AND DEPT_NO >(10+1)*10是不被允許的查詢語句。
B.數(shù)據(jù)類型的匹配
SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100 沒有SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100.00好了。
(4)選擇優(yōu)化器。Oracle的優(yōu)化器共有3種:
1)RULE (基于規(guī)則 2)COST (基于成本 3)CHOOSE (選擇性)
在默認(rèn)的情況下,Oracle數(shù)據(jù)庫一般選用的是選擇性優(yōu)化器, 目的就是規(guī)避不需要的全表掃描,但是實際應(yīng)用中還是盡量不使用選擇性優(yōu)化器,而采用的是基于規(guī)則或者基于成本的優(yōu)化器。當(dāng)然,最好是能根據(jù)批量的SQL自己編制一個特定的優(yōu)化器來幫助自己優(yōu)化SQL查詢。
(5)選擇連接方式。優(yōu)化器使用了六種不同的連接方式(不做詳解):
1 嵌套循環(huán)連接(NESTED LOOP JOIN)
2 群集連接 (CLUSTER JOIN)
3 排序連接(SORT MERGE JOIN)
4 笛卡爾連接(CARTESIAN JOIN)
5 哈希連接(HASH JOIN)
6 索引連接(INDEX JOIN)
這六種連接方式都存在各自獨特的技術(shù)特性,在特定的條件下,各自高效的性能都能夠得到充分的發(fā)揮。
(6)選擇連接順序。在Oracle SQL查詢語句中最好的方法是手工指定表的連接順序。為了盡快創(chuàng)建最小的解決方案集,這里所遵循的規(guī)則是將表結(jié)合起來,通常優(yōu)先使用限制最嚴(yán)格的WHERE子句來連接表。
選擇數(shù)據(jù)搜索路徑。根據(jù)以上條件選擇合適的數(shù)據(jù)搜索路徑,能使SQL語句執(zhí)行的更加有效率。
1.2 執(zhí)行
主要在于使用更新和刪除的SQL語句時,必須要鎖定數(shù)據(jù)列,防止其他用戶進行修改。Oracle會先在數(shù)據(jù)庫緩沖中尋找是否存在所需要的數(shù)據(jù)塊,如果存在這樣的數(shù)據(jù)塊,就可以直接進行讀或者修改操作,否則數(shù)據(jù)會被從物理文件之中讀取到數(shù)據(jù)庫緩沖區(qū)中。
1.3 返回結(jié)果
對SELECT 語句需要返回結(jié)果的語句,看返回的結(jié)果集是否需要按照實際應(yīng)用中排序,若需要,則排序后輸出。
2 Oracle優(yōu)化器工作原理
Oracle優(yōu)化器分為三類:
1)RULE優(yōu)化器:基于規(guī)則的優(yōu)化器相對而言比較簡單,工作原理概括為檢查數(shù)據(jù)庫當(dāng)中的所有的可用路徑并且將這些可用的路徑與已經(jīng)存在的路徑表進行比較,來確定SQL語句具體的執(zhí)行過程。RULE優(yōu)化的過程中不需要任何表或索引的統(tǒng)計信息,而且也會忽略任何表或索引的統(tǒng)計信息 。
2)COST優(yōu)化器:基于開銷的優(yōu)化器相對比較復(fù)雜,通過使用數(shù)據(jù)庫的結(jié)構(gòu)和數(shù)據(jù)等信息來選擇最優(yōu)的執(zhí)行計劃。優(yōu)化的過程中需要相關(guān)表和索引的統(tǒng)計信息。
3)CHOOSE優(yōu)化器:在存在相關(guān)統(tǒng)計信息的情況下采用基于開銷的優(yōu)化器,在不存在相關(guān)統(tǒng)計信息的情況下才用基于規(guī)則的優(yōu)化器。這是8I中默認(rèn)的優(yōu)化器工作方式。
同樣的查詢語句可以有很多不同的執(zhí)行計劃, Cost優(yōu)化器將自動計算不同執(zhí)行計劃所消耗的系統(tǒng)資源, 接著就會選出消耗最低的執(zhí)行計劃。一條查詢的消耗大致被分為3個基本成分:I/O消耗、CPU消耗、Network消耗。
3 Oracle查詢優(yōu)化方法
3.1 共享SQL語句
Oracle在執(zhí)行每條SQL語句的時候都會先對語句進行語法分析,而這個過程是比較消耗資源的,為了能夠省略這個步驟來提高SQL查詢語句的執(zhí)行效率,Oracle采用的共享SQL語句的方式,就是把每條從用戶發(fā)出的SQL存儲到系統(tǒng)全局區(qū)(System Global Area)的共享池當(dāng)中,這個共享池當(dāng)中的所有SQL語句將會被數(shù)據(jù)庫當(dāng)中的所有有權(quán)限的用戶共享使用;所以當(dāng)你每天執(zhí)行一條SQL語句時,Oracle會先去共享池當(dāng)中查找是否存在這樣一條SQL語句的執(zhí)行記錄,如果存在則會直接得到該SQL語句的執(zhí)行計劃和執(zhí)行路徑,這么就大大的節(jié)省了系統(tǒng)內(nèi)存并且提高了SQL語句的執(zhí)行效率。
如果某一查詢語句需要匹配共享池之中的SQL, 都必須遵照以下三條規(guī)則。
(1)SQL語句之間字符之間必須滿足相互匹配,另外字符的大小寫也需要相互匹配。
(2)SQL語句中的對象必須完全一致,對于如下查詢:
SELECT MAX(EMP_SALARY) FROM EMP LIMIT;
(3)SQL語句中使用命名的綁定變量必須相同。
3.2 創(chuàng)建和使用索引
在關(guān)系型數(shù)據(jù)庫中,索引是數(shù)據(jù)庫之中的一種結(jié)構(gòu)。簡而言之,索引的作用類似于于圖書的目錄,使用索引相當(dāng)于使用這個目錄快速找到所需文章的內(nèi)容,它能夠使作用于表的查詢語句執(zhí)行得更加有效率。對于一個數(shù)據(jù)庫而言,索引是必須的,但是對于現(xiàn)在存在的各種大型數(shù)據(jù)庫而言,索引強大的作用大大提升數(shù)據(jù)庫的查詢性能,使其變成了現(xiàn)在數(shù)據(jù)庫不可或缺的重要部分。索引的創(chuàng)建需要去維護, 如果對表進行更新、插入、刪去等操作的時候, 已經(jīng)創(chuàng)建好的索引也需要去更新。因而在使用索引時需要遵循以下的原則:
(1)對于查詢操作時較少需要的列或者列的重復(fù)值比較多,則不需要建立索引。
(2)對于按范圍查詢的列,最好建立索引。
(3)如果一個表中包含了主鍵和外鍵,則必須要建立索引。
(4)對于一些特殊的數(shù)據(jù)類型,不要建立索引。
(5)如果對數(shù)據(jù)庫經(jīng)常執(zhí)行一些插入、刪除等操作時, 不能建立太多索引。
(6)索引可以跟Where語句的集合融為一體。
當(dāng)創(chuàng)建完索引后, 若SQL查詢語句存在如下情況, 優(yōu)化器生成的執(zhí)行計劃也會對創(chuàng)建好的所有進行使用, 因而盡量不要發(fā)生以下狀況。
(1)如果對某個表中的兩個列進行比較時,索引必不會一直被數(shù)據(jù)庫使用;
如查詢操作:SELECT EMP_NO,EMP_SALARY FROM EMP WHERE EMP_NO>MGR;
(2)數(shù)據(jù)庫的表中存在NULL值。一般索引中并不會有NULL值。如果WHERE條件語句中出現(xiàn)is null或者is not null時, 索引就無效,不能被使用;
如下查詢將將不會對comm列的索引進行訪問:
SELECT EMP_NAME,EMP_NO,EMP_SALARY FROM EMP WHERE COMM IS NOT NULL;
(3)在WHERE條件語句中使用了不等于操作符號,如!=、<>;
SELECT EMP_NAME, EMP_SALARY,EMP_NO,DEPT_NO FROM EMP WHERE DEPT_NO<>20 GROUP BY EMP_NO;
可以改寫為:
SELECT EMP_NAME, EMP_SALARY,EMP_NO,DEPT_NO FROM EMP WHERE DEPT_NO<20 OR DEPT_NO>20 GROUP BY EMP_NO;
(4)當(dāng)對操作的列使用函數(shù)時;
SELECT EMP_NAME, EMP_NO, JOB,EMP_SALARY FROM EMP WHERE UPPER(DEPT_NO)=10 GROUP BY EMP_NO;
可以創(chuàng)建基于函數(shù)的索引來優(yōu)化此查詢。
Create index DEPT_NO_idx on emp(upper(DEPT_NO));
(5)對相互不匹配的數(shù)據(jù)類型進行比較,DEPT_NO字段類型是VARCHAR(2), 如查詢:
SELECT EMP_NAME, EMP_NO, EMP_SALARY FROM EMP WHERE DEPT_NO=30;
Oracle數(shù)據(jù)庫可以自動把where條件子句變成TO_NUMBER(DEPT_NO)=30, 這樣就限制了DEPT_NO列上索引的使用。
3.3 重寫SQL語句
3.3.1 使用WHERE代替HAVING
盡量不要使用HAVING條件子句, HAVING在檢索出所有滿足條件的結(jié)果集之后,再對這些結(jié)果集進行篩選。 整個的處理過程需要排序,總計等一系列操作。而WHERE條件子句限制了記錄的數(shù)量,減少資源在這方面的消耗;
例如查詢語句:
SELECT EMP_NAME,EMP_NO, AVG(EMP_SALARY) FROM EMP GROUP BY DEPT_NO HAVING EMP_NO<1000;
對表EMP中所有列項進行匯總之后, 再踢除EMP_NO小于1000的記錄, 可以將上列查詢語句改寫為如下語句:
SELECT EMP_NAME,EMP_NO, AVG(EMP_SALARY) FROM EMP WHERE EMP_NO<1000 GROUP BY DEPT_NO ;
原因是在匯總前排除掉了EMP_NO<1000的記錄,從而提升了查詢語句的執(zhí)行效率。
3.3.2 UNION ALL代替UNION
當(dāng)我們需要UNION兩個查詢結(jié)果集合時,這兩個結(jié)果集將會以UNION ALL的方式被Oracle數(shù)據(jù)庫進行合并,然后進行排序后再輸出結(jié)果。如果使用 UNION ALL替代UNION,就不需要這樣的排序了,將會提升Oracle數(shù)據(jù)庫的查詢執(zhí)行的效率。另外特別注意的是,UNION ALL會重復(fù)輸出兩個結(jié)果集中相同的記錄。
3.3.3 使用DECODE函數(shù)
使用DECODE函數(shù)能夠避免重復(fù)掃描表中相同的數(shù)據(jù)。
例如:
SELECT COUNT(EMP_NO),SUM(EMP_SALARY) FROM EMP WHERE DEPT_NO = 20 AND EMP_NAME LIKE‘JORDAN%;
SELECT COUNT(EMP_NO),SUM(EMP_SALARY) FROM EMP WHERE DEPT_NO = 30 AND EMP_NAME LIKE‘JORDAN%;
可以使用DECODE函數(shù)高效的得出查詢結(jié)果:
SELECT COUNT(DECODE(DEPT_NO,20,X,NULL))
D20_COUNT,COUNT(DECODE(DEPT_NO,30,X,NULL))
D30_COUNT,SUM(DECODE(DEPT_NO,20,EMP_SALARY,NULL))
D20_EMP_SALARY,SUM(DECODE(DEPT_NO,30,EMP_SALARY,NULL))
D30_EMP_SALARY FROM EMP WHERE EMP_NAME LIKE‘JORDAN%;
3.3.4 用TRANCATE代替DELETE
當(dāng)刪除表中的記錄時,在一般情形中,回滾段存放的是能夠被恢復(fù)的信息。如果事務(wù)沒有被提交,Oracle數(shù)據(jù)庫之中的數(shù)據(jù)將會恢復(fù)到最初沒有被刪除的的狀態(tài),而使用TRUNCATE,被恢復(fù)的信息不再存放于回滾段之中。因而調(diào)用的資源將變得很少,SQL語句執(zhí)行的效率也將變得越快。
3.3.5 物化視圖查詢重寫
所謂物化視圖查詢重寫就是,如果初始化參數(shù)query_rewrite_ enabled設(shè)置為TRUE,并且數(shù)據(jù)庫運行在COST優(yōu)化模式下,當(dāng)對基表進行查詢時,Oracle會自動判斷是否能利用這個基表的所有包含ENABLE QUERY REWRITE關(guān)鍵字的物化視圖,如果可以且根據(jù)統(tǒng)計信息判斷通過查詢物化視圖代價更小,則Oracle數(shù)據(jù)庫的查詢語句將被重寫,通過查詢物化視圖可以得出正確的查詢結(jié)果。
3.3.6 創(chuàng)建位圖索引
由于索引是位圖,所以很多時候可以對這些索引中的位圖進行位運算(and 和 or),在某些情況下這樣的速度明顯比b樹快;由于位圖索引可以存儲null,所以可以直接通過位圖索引計數(shù)。
SELECT EMP_NAME FROM EMP WHERE EMP_SALARY>10000 and JOB=SALES AND DEPT_NO=10;已用時間:00:00:01.13
在對EMP_SALARY(月薪), JOB(職位), DEPT_NO(部門編號)三列創(chuàng)建位圖索引, 上面的SQL查詢語句在執(zhí)行時話費時間為:00:00:00.08, 對比兩次執(zhí)行所消耗的時間,SQL執(zhí)行的效率的得到提升,若將這種方式使用到實際應(yīng)用當(dāng)中,數(shù)據(jù)庫性能的提升等級不言而喻。
4 備注
本文所用的表為EMP(員工表),表中字段結(jié)構(gòu)為EMP_NO(員工編號)、EMP_NAME(員工姓名)、DEPT_NO(所屬部門編號)、EMP_SALARY(工資)、JOB(職位)等;DEPT(部門表),表中字段結(jié)構(gòu)為DEPT_NAME(部門名稱),DEPT_NO(部門編號),DEPT_LOC(部門所在城市)等。
【參考文獻(xiàn)】
[1]張學(xué)義,王觀玉,黃雋.基于Oralce數(shù)據(jù)庫SQL查詢優(yōu)化研究[J].制造業(yè)自動化,2011(02).
[2]劉星.Oracle數(shù)據(jù)庫的性能優(yōu)化與調(diào)整[J].科技資訊,2011(04).
[3]張學(xué)琴. 基于Oracle數(shù)據(jù)庫的SQL語句優(yōu)化[J].電腦知識與技術(shù),2010(01).
[責(zé)任編輯:湯靜]