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