淺談Oracle數(shù)據(jù)庫SQL調(diào)整與優(yōu)化

孟津平 李易東

(長春理工大學(xué) 計(jì)算機(jī)科學(xué)技術(shù)學(xué)院,吉林長春 130012)

Oracle數(shù)據(jù)庫作為如今市場占有率最高的數(shù)據(jù)庫,擁有著極為突出的優(yōu)勢最為顯著的是高效處理事物的能力。但作為大型數(shù)據(jù)庫難免會(huì)存在著較多問題,這些都影響數(shù)據(jù)庫對(duì)數(shù)據(jù)的高效處理。其中大部分問題都出現(xiàn)在數(shù)據(jù)庫應(yīng)用系統(tǒng)性能方面,涉及到數(shù)據(jù)庫服務(wù)器、數(shù)據(jù)庫硬件、數(shù)據(jù)庫內(nèi)存、數(shù)據(jù)庫參數(shù)等方面。但是在實(shí)際的數(shù)據(jù)庫操作中SQL語句的錯(cuò)誤設(shè)計(jì)會(huì)導(dǎo)致整個(gè)查詢過程變的低效甚至癱瘓。由此,本文將對(duì)數(shù)據(jù)庫的SQL優(yōu)化進(jìn)行探索和研究,分析并使用合理的優(yōu)化方法,從而達(dá)到數(shù)據(jù)庫高效處理的目的。

1 Oracle數(shù)據(jù)庫的查詢過程

Oracle DBA通過SQL語言對(duì)數(shù)據(jù)庫進(jìn)行通信和操作,數(shù)據(jù)庫應(yīng)用系統(tǒng)通過執(zhí)行用戶或DBA提交的SQL語句完成查詢過程。

(1) 客戶端將語句發(fā)給服務(wù)器端執(zhí)行,由服務(wù)器端的進(jìn)程處理這些語句。

(2)服務(wù)器進(jìn)程接收到SQL語句并開始解析。查詢高速緩存,查看是否存在相同語句的執(zhí)行計(jì)劃。進(jìn)行語法合法性檢查,看是否合乎語法規(guī)則。語言含義檢查,查看涉及的表、索引、視圖等進(jìn)行解析。獲得對(duì)象解析鎖及數(shù)據(jù)訪問權(quán)限的核對(duì)。確定最佳執(zhí)行計(jì)劃。

(3)綁定變量賦值。在SQL語句中如果使用了綁定變量,掃描綁定變量的聲明,給綁定變量賦值,將變量值帶入執(zhí)行計(jì)劃。若在一開始解析時(shí),SQL在高速緩沖中存在,則直接跳到該步驟。

(4)語句執(zhí)行。

2 索引的合理建立

數(shù)據(jù)庫中的索引是一種排序的數(shù)據(jù)結(jié)構(gòu),是通過B樹和變形的B+樹實(shí)現(xiàn)的。在數(shù)據(jù)庫的使用中SQL語句的查詢速度會(huì)隨著數(shù)據(jù)量的增加變的越來越慢,因此我總結(jié)出以下幾點(diǎn)關(guān)于索引的使用:

(1)經(jīng)常與其他表進(jìn)行連接的表,在連接字段上應(yīng)該建立索引。

(2)經(jīng)常出現(xiàn)在Where子句中的字段,特別是大表的字段,應(yīng)該建立索引。

(3)索引應(yīng)該建在選擇性高的字段上。

(4)索引應(yīng)該建在小字段上,對(duì)于大的文本字段,不要建索引。

(5)復(fù)合索引的建立需要進(jìn)行仔細(xì)分析;盡量考慮用單字段索引代替。

正確選擇復(fù)合索引的主列字段,一般是選擇性較好的字段。

復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是,則可以建立復(fù)合索引;否則考慮單字段索引。

如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中,則分解為多個(gè)單字段索引。

如果復(fù)合索引所包含的字段超過3個(gè),那么仔細(xì)考慮其必要性,考慮減少復(fù)合的字段。

如果既有單字段索引,又有這幾個(gè)字段上的復(fù)合索引,一般可以刪除復(fù)合索引。

(6)頻繁進(jìn)行數(shù)據(jù)操作的表,不要建立太多的索引。

(7)刪除無用的索引,避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響。

(8)特殊字段的數(shù)據(jù)庫,如BLOB,CLOB字段不適合建立索引。

建立索引時(shí)應(yīng)該首先考慮表空間和磁盤空間是否足夠,其次,在對(duì)建立索引的時(shí)候要對(duì)表進(jìn)行加鎖,因此應(yīng)當(dāng)注意操作在業(yè)務(wù)空閑的時(shí)候進(jìn)行。在建立索引的時(shí)候要對(duì)表進(jìn)行全表掃描,同時(shí)還要對(duì)數(shù)據(jù)進(jìn)行大量的排序操作。因此,應(yīng)當(dāng)調(diào)整排序區(qū)的大小。最后,建立索引的時(shí)候,可加上nologging選項(xiàng)。以減少在建立索引過程中產(chǎn)生的大量redo,從而提高執(zhí)行的速度。

3 基于索引的SQL優(yōu)化的研究

(1)避免在含有索引的條件上使用隱式轉(zhuǎn)換,這會(huì)使得索引失效。且SQL語句更加不容易被理解,一旦上下文環(huán)境發(fā)生改變可能無法正常運(yùn)行。

(2)避免使用子查詢例如:select ename,deptno,(select sum(sal)from emp where deptno=e.deptno)tsal from emp e;執(zhí)行計(jì)劃表明,上面的查詢進(jìn)行了兩次全表掃描。改為select ename,deptno,sum(sal)over(partition by deptno)tsal from emp;使用分析函數(shù)后整個(gè)語句只有一次全表掃描速度增加至少一倍。

(3)當(dāng)針對(duì)大量相同的列如:類別、操作員、部門ID,時(shí)使用位圖索引。在索引塊的一個(gè)索引行中存儲(chǔ)鍵值和起止Rowid,以及這些鍵值的位置編碼。根據(jù)鍵值查詢時(shí)可以根據(jù)起始Rowid和位圖狀態(tài)快速定位數(shù)據(jù)、做and,or,或in查詢可直接用索引的位圖進(jìn)行或運(yùn)算快速得出結(jié)果。

(4)表建立索引后,不斷進(jìn)行增刪改等操作,會(huì)使索引中產(chǎn)生大量存儲(chǔ)碎片。這就必須要用到如下的兩種方法。

合并索引:將B樹中葉子節(jié)點(diǎn)的存儲(chǔ)碎片合并在一起,并不會(huì)改變索引的物理組織結(jié)構(gòu)。

重建索引:消除存儲(chǔ)碎片并改變索引的全部存儲(chǔ)參數(shù)設(shè)置,以及存儲(chǔ)表空間。

4 Oracle優(yōu)化思路的總結(jié)

在對(duì)數(shù)據(jù)庫的SQL語句進(jìn)行優(yōu)化時(shí)不能盲目優(yōu)化,首先找到該數(shù)據(jù)庫的top sql,因?yàn)閠op sql才是這個(gè)數(shù)據(jù)庫最需要完成的工作,其好壞直接影響數(shù)據(jù)庫的使用。首先對(duì)這些語句的執(zhí)行計(jì)劃進(jìn)行分析,主要是找出他們的瓶頸所在,看他們是否按照預(yù)定的計(jì)劃進(jìn)行查詢,再看看統(tǒng)計(jì)信息是否最新、是否沒有、是否不合理。一般執(zhí)行計(jì)劃變慢的原因本文認(rèn)為應(yīng)該從以下幾點(diǎn)進(jìn)行分析:

(1)訪問路徑的問題:在查詢的時(shí)候優(yōu)化器選擇其中自認(rèn)為是最優(yōu)化的路徑來定位和查詢出需要的數(shù)據(jù)。但是這可能并不是預(yù)期的結(jié)果所以需要人為干預(yù)和修改。

(2)表的鏈接或索引出現(xiàn)問題:這種情況應(yīng)該從索引創(chuàng)建是開始分析,哪些需要索引、是否每個(gè)索引都利用到。數(shù)據(jù)庫中如果對(duì)表頻繁操作,索引沒有定期的維護(hù)或重建索引,就有可能出現(xiàn)地址對(duì)應(yīng)不上、查詢慢等問題。

(3)語法使用問題。

(4)物化視圖的原因。

[1]劉春菊.Oracle數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能優(yōu)化[J].電子技術(shù)與軟件工程,2017,(17):180.

[2]楊嵩.淺談Oracle數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能優(yōu)化[J].計(jì)算機(jī)光盤軟件與應(yīng)用,2015,(03):111-112+115.

[3]莫佩宏.Oracle數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能優(yōu)化[J].電子制作,2014,(16):54.