導語：ORACLE中SQL查詢優化研究論文一文來源于網友上傳，不代表本站觀點，若需要原創文章可咨詢客服老師，歡迎參考。

摘要數據庫性能問題一直是決策者及技術人員共同關注的焦點，影響數據庫性能的一個重要因素就是SQL查詢語句的低效率。論文首先分析了導致SQL查詢語句性能低下的四個常見原因以及SQL調優的一般步驟，然后分別針對如何降低I/O操作、在查詢語句中如何避免對查詢結果的高成本操作以及在多表連接時如何提高查詢效率進行了分析。

關鍵詞oracle；sql；優化；連接

1引言

隨著網絡應用不斷發展，系統性能已越來越引起決策者的重視。影響系統性能的因素很多，低效的SQL語句就是其中一個不可忽視的重要原因。論文首先分析導致SQL性能低下的常見原因，然后分析SQL調優應遵循的一般步驟，最后從如何降低I/O、避免對查詢結果的高成本操作和多表連接中如何提高SQL性能進行了研究。鑒于目前ORACLE在數據庫市場上的主導地位，論文將只針對ORACLE進行討論。

2影響SQL性能的原因

影響SQL性能的因素很多，如初始化參數設置不合理、導入了不準確的系統及模式統計數據從而影響優化程序(CBO)的正確判斷等，這些往往和DBA密切相關。純粹從SQL語句出發，筆者認為影響SQL性能不外乎以下四個重要原因：

(1)在大記錄集上進行高成本操作，如使用了引起排序的謂詞等。

(2)過多的I/O操作(含物理I/O與邏輯I/O)，最典型的就是未建立恰當的索引，導致對查詢表進行全表掃描。

(3)處理了太多的無用記錄，如在多表連接時過濾條件位置不當導致中間結果集包含了太多的無用記錄。

(4)未充分利用數據庫提供的功能，如查詢的并行化處理等。

第(4)個原因處理起來相對簡單。論文將針對前三個原因論述如何提高SQL查詢語句的性能。

3SQL優化的一般步驟

SQL優化一般需經過發現問題、分析問題、提出解決措施、應用措施、測試性能幾個步驟，如圖1所示。“發現問題就是解決問題的一半”，因此在SQL調優過程中，定位問題SQL是非常重要的一步，一般可借助于ORACLE自帶的性能優化工具如STATSPACK、TKPROF、AUTOTRACE等輔助用戶進行，同時還應該重視動態性能視圖如V$SQL、V$MYSTAT、V$SYSSTAT等的研究。

圖1SQL優化的一般步驟

4SQL語句的優化

4.1優化排序操作

排序的成本十分高昂，當在查詢語句中使用了引起結果集排序的謂詞時，SQL性能必然受到影響。

4.1.1排序過程分析

當待排序數據集不是太大時，服務器在內存(排序區)完成排序操作，如果排序需要更多的內存空間，服務器將進行如下處理：

(1)將數據分成多個小的集合，對每一集合進行排序。

(2)服務器向磁盤申請臨時空間，將排好序的中間結果寫入臨時段，再對另外的集合進行排序。

(3)在所有的集合均排好序后，服務器再將它們進行合并得到最終的結果，如果排序區尺寸太小，合并無法一次完成時，將分多次進行。

從上述分析可知，排序是一種十分昂貴的操作，它消耗大量的CPU時間和內存，觸發磁盤分頁和交換操作，因此只要有可能，我們就應該在SQL語句中盡量避免排序操作。

4.1.2SQL中引起排序的操作

SQL查詢語句中引起排序的操作大致有：ORDERBY和GROUPBY從句；DISTINCT修飾符；UNION、INTERSECT、MINUS集合操作符；多表連接時的排序合并連接(SORTMERGEJOIN)等。

4.1.3如何避免排序

1)建立恰當的索引

對經常進行排序和連接操作的字段建立索引。在建立索引后，當服務器向這些字段發出排序請求時，將直接引用索引而不進行排序操作；當進行等值連接查詢操作時，若建立連接的字段未建立索引，服務器進行的是排序合并連接(SORTMERGEJOIN)，連接操作的過程如下：

對進行連接的兩個或多個表分別進行全掃描；

對每一個表中的行集分別進行全排序；

合并排序結果。

如果建立連接的字段已建立索引，服務器進行嵌套循環連接(NESTEDLOOPJOINS)，該連接方式不需要任何排序，其過程如下：

對驅動表進行全表掃描；

對返回的每一行利用連接字段值實施索引惟一掃描；

利用從索引掃描中返回的ROWID值在從表中定位記錄；

合并主、從表中的匹配記錄。

因此，建立索引可避免多數排序操作。

2)用UNIIONALL替換UNION

UNION在進行表鏈接后會篩選掉重復的記錄，所以在表鏈接后會對所產生的結果集進行排序運算，刪除重復的記錄再返回結果。大部分應用中是不會產生重復記錄的，最常見的是過程表與歷史表UNION。因此，采用UNIONALL操作符替代UNION，因為UNIONALL操作只是簡單的將兩個結果合并后就返回。

4.2優化I/O

過多的I/O操作會占用CPU時間、消耗大量內存和占用過多的栓鎖，因此有必要對SQL的I/O進行優化。優化I/O的最有效方式就是用索引掃描代替全表掃描。

4.2.1應用基于函數的索引

基于函數的索引(FUNCTIONBASEDINDEX，簡記為FBI)提供了索引計算列并在查詢中使用這些索引的能力。FBI的實質是對查詢所需中間結果進行預處理。如果一個FBI與查詢語句中的內嵌函數完全匹配，CBO在生成查詢計劃時，將自動啟用索引范圍掃描(INDEXRANGESCAN)替換全表掃描(FULLTABLESCAN)。考察下面的代碼段并用AUTOTRACE觀察創建FBI前后執行計劃的變化。

select*fromempwhereupper(ename)=’SCOTT’

創建FBI前，很明顯是全表掃描。

ExecutionPlan

……

10TABLEACCESS(FULL)OF''''EMPLOYEES''''(Cost=2Card=1Bytes=22)

idle>CREATEINDEXEMP_UPPER_FIRST_NAMEONEMPLOYEES(UPPER(FIRST_NAME))；

索引已創建。

再次運行相同查詢，

ExecutionPlan

……

10TABLEACCESS(BYINDEXROWID)OF''''EMPLOYEES''''(Cost=1Card=1Bytes=22)

21INDEX(RANGESCAN)OF''''EMP_UPPER_FIRST_NAME''''(NON-UNIQUE)(Cost=1Card=1)

這一簡單的例子充分說明了FBI在SQL查詢優化中的作用。FBI所用的函數可以是用戶自己創建的函數，該函數越復雜，基于該函數創建FBI對SQL查詢性能的優化作用越明顯。

4.2.2應用物化視圖和查詢重寫

物化視圖是一個預計算結果集，其中通常包含聚集與多表連接等復雜操作。數據庫自動維護物化視圖，且隨用戶的要求進行刷新。查詢重寫機制就是用數據庫中的替代對象(如物化視圖)將用戶提交的查詢重寫為完全不同但功能等價的查詢。查詢重寫對用戶透明，用戶完全按常規編寫訪問數據庫的查詢語句，優化程序(CBO)自動決定是否對用戶提交的查詢進行重寫。查詢重寫是提高查詢性能的一種非常有效的方法，尤其是在數據倉庫環境中針對匯總、多表連接以及其它高成本的操作方面。

下面以一個非常簡單的例子來演示物化視圖和查詢重寫在優化SQL查詢性能方面的作用。

selectdept.deptno，dept.dname，count(*)

fromemp，dept

whereemp.deptno=dept.deptno

groupbydept.deptno，dept.dname

查詢計劃及主要統計數據如下：

執行計劃：

-----------------------------------------

……

21HASHJOIN(Cost=5Card=14Bytes=224)

32TABLEACCESS(FULL)OF''''DEPT''''(Cost=2Card=4Bytes=52)

42TABLEACCESS(FULL)OF''''EMP''''(Cost=2Card=14Bytes=42)

主要統計數據：

-----------------------------------------

305recursivecalls

46consistentgets

創建物化視圖EMP_DEPT：

creatematerializedviewemp_deptbuildimmediate

refreshondemand

enablequeryrewrite

as

selectdept.deptno，dept.dname，count(*)

fromemp，dept

whereemp.deptno=dept.deptno

groupbydept.deptno，dept.dname

/

再次執行查詢，執行計劃及主要統計數據如下：

執行計劃：

-------------------------------------

……

10TABLEACCESS(FULL)OF''''EMP_DEPT''''(Cost=2Card=327Bytes=11445)

主要統計數據：

------------------------------------

79recursivecalls

28consistentgets

可見，在建立物化視圖之前，首先執行兩個表的全表掃描，然后進行HASH連接，再進行分組排序和選擇操作；而建立物化視圖后，CBO自動將上述復雜操作轉換為對物化視圖EMP_DEPT的全掃描，相關的統計數據也有了很大的改善，遞歸調用(RECURSIVECALLS)由305降到79，邏輯I/O(CONSISTENTGETS)由46降為28。

4.2.3將頻繁訪問的小表讀入CACHE

邏輯I/O總是快于物理I/O。如果數據庫中存在被應用程序頻繁訪問的小表，可將這些表強行讀入KEEP池，從而避免物理I/O的發生。

4.3多表連接優化

最能體現查詢復雜性的就是多表連接，多表連接操作往往要耗費大量的CPU時間和內存，因此多表連接查詢性能優化往往是SQL優化的重點與難點。

4.3.1消除外部連接

通過消除外部連接，不僅使得到的查詢更易于讀取，而且性能也經常可以得到改善。一般的思路是，有以下形式的查詢：

SELECT…，OUTER_JOINED_TABLE.COLUMN

FROMSOME_TABLE，OUTER_JOINED_TO_TABLE

WHERE…=OUTER_JOINED_TO_TABLE(+)

可轉換為如下形式的查詢：

SELECT…，(SELECTCOLUMNFROMOUTER_JOINED_TO_TABLEWHERE…)FROMSOME_TABLE；

4.3.2謂詞前推，優化中間結果

多表連接的性能低下多數是因為連接操作與過濾操作的次序不合理，大多數用戶在編寫多表連接查詢時，總是先進行連接操作再應用過濾條件，這導致服務器做了太多的無用功。針對這類問題，其優化思路就是盡可能將過濾謂詞前推，使不符合條件的記錄提前被篩選掉，只對符合條件的少數記錄進行連接處理，這樣可成倍的提高SQL查詢效能。

如下圖所示的星形模型，現要統計最近三個月進貨的商品在各種銷售渠道上的銷售業績。

圖2產品銷售的星形模型

標準連接查詢如下：

Selecta.prod_name，sum(b.sale_quant)，

sum(c.sale_quant)，sum(d.sale_quant)

Fromproducta，tele_saleb，online_salec，store_saled

Wherea.prod_id=b.prod_idanda.prod_id=c.prod_id

anda.prod_id=d.prod_idAnda.order_date>sysdate-90

Groupbya.prod_id；

啟用內嵌視圖，且將條件a.order_date>sysdate-90前移，優化后代碼如下：

Selecta.prod_name，b.tele_sale_sum，c.online_sale_sum，d.store_sale_sumFromproducta，

(selectsum(sal_quant)tele_sale_sumfromproduct，tele_sale

Whereproduct.order_date>sysdate-90andproduct.prod_id=tele_sale.prod_id)b，

(selectsum(sal_quant)online_sale_sum

fromproduct，tele_sale

Whereproduct.order_date>sysdate-90andproduct.prod_id=online_sale.prod_id)c，

(selectsum(sal_quant)store_sale_sum

fromproduct，store_sale

Whereproduct.order_date>sysdate-90andproduct.prod_id=store_sale.prod_id)d，

Wherea.prod_id=b.prod_idand

a.prod_id=c.prod_idanda.prod_id=d.prod_id；

5結束語

SQL語言在數據庫應用中占有非常重要的地位，其性能的優劣直接影響著整個信息系統的可用性。論文從影響SQL性能的最主要的三個方面入手，分析了如何優化SQL查詢的I/O、避免高成本的排序操作和優化多表連接。需要強調的一點是，理解SQL語句所解決的問題比SQL調優本身更重要，因此SQL調優需要系統分析人員、開發人員和數據庫管理員密切協作。

參考文獻

[1]ThomasKyte.EffectiveOraclebyDesign：DesignandBuildHigh-performanceOracleApplication[M]，TheMcGral-HillCompanies，Inc，2003

[2]KevinLoney，GeorgeKoch，Oracle9i：TheCompleteReference[M]，TheMcGral-HillCompanies，Inc，2002

[3]Oracle9iSQLReferencerelease2(9.2)[OL/M]，2002.10.http：///technology/

[4]Oracle9iDataWarehousingGuiderelease2(9.2)[OL/M]，2002.03.http：///technology/

[5]AlexeyDanchenkov，DonaldBurleson，OracleTuning：TheDefinitiveReference[OL/M]，RampantTechpress，2006.

[6]Oracle9iDatabaseConceptsrelease2(9.2)[OL/M]，2002.08.http：///technology/

[7]Oracle9isuppliedplsqlpackagesandtypesreferencerelease2(9.2)[OL/M]，2002.12.http：///technology/