基于SQL的數據庫性能優化分析

[摘 要] 數據庫性能調整與優化，對于提高數據庫的穩定性、可靠性，保障業務高效運行有著重要意義。本文從數據庫性能優化角度出發，探討程序開發過程中，Oracle數據庫的SQL語句調整和優化技術。

[關鍵詞] SQL； 數據庫； 性能優化

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2013 . 15. 039

[中圖分類號] TP392 [文獻標識碼] A [文章編號] 1673 - 0194（2013）15- 0064- 02

0 引 言

隨著計算機技術和網絡技術的普及與廣泛應用，數據庫技術也得到了長足發展，并成為現代計算機信息與應用系統的核心基礎技術。Oracle數據庫是目前使用較為廣泛的數據庫系統，用戶規模和應用范圍不斷擴展，由此帶來的系統性能問題愈來愈突出，因此，對數據庫優化技術方法的探索，對解決系統瓶頸，節約系統開銷，確保數據庫和業務系統穩定、高效地運行，有著至關重要的意義。

數據庫的優化，主要從硬件、操作系統、數據庫參數和應用程序等方面進行配置和調整。據統計，對硬件系統和數據庫參數進行優化所獲得的性能提升，只占數據庫系統性能提升的40%左右，另外的60%系統性能提升來自于對應用程序的優化。其中，應用程序的優化通常可分為兩個方面：源代碼和SQL語句。一方面，由于涉及對程序邏輯的改變，源代碼的優化在時間成本和風險上代價很高，而對數據庫系統性能的提升收效有限；另一方面，應用程序對數據庫的操作，最終體現在SQL語句對數據庫的操作，因此SQL語句的執行效率決定了數據庫的性能。由此可見，應用程序的優化應著重于SQL語句的優化。在數據庫應用系統中，相同功能的程序可以采用不同的SQL語句編寫實現，而不同的SQL語句存在著性能及效率上的差異，這種差異在大型數據庫環境中表現得尤為明顯。因此，通過優化調整SQL語句，從而顯著改善整個系統的性能，對提高數據庫內存區的命中率、減少I/O訪問、減少對網絡資源的占用等有著重要意義。

1 SQL優化技術分析及實現方法

要對SQL語句進行優化，首先應該清楚SQL語句的執行過程。Oracle會為每個用戶進程分配一個服務器進程，當服務器進程接收到用戶進程提交的SQL語句時，服務器進程會對SQL語句進行語法和詞法分析。檢查通過后，服務器進程會將SQL語句轉變為ASCII碼，并通過一個Hash函數將ASCII碼生成Hash值，服務器進程會到系統全局區（System Global Area，SGA）的共享池（Shared Pool）中查詢此Hash值是否存在。如果存在，服務器進程會在共享池中讀取已經解析好的語句來執行，這就是軟解析；如果不存在，則需要通過優化器生成執行計劃和生成執行編碼，這就是硬解析，硬解析完成后，Oracle會將SQL語句本身代碼、Hash值、執行計劃和所有與此語句相關的統計數據放到共享池中。

根據以上所述的SQL語句執行過程，分析優化SQL語句，應主要從以下幾個方面來進行：

（1） 共享SQL語句。

（2） 高質量的SQL語句。

（3） 使用索引。

1.1 共享SQL語句

為了不重復解析相同的SQL語句，在第一次解析之后，Oracle將SQL語句存放在內存中。這塊位于系統全局區域的共享池中的內存可以被所有的數據庫用戶共享。如果用戶提交的SQL語句和之前的執行過的語句完全相同，Oracle就能很快獲得已經被解析的語句以及最好的執行路徑，從而大大地提高了SQL的執行性能并節省了內存的使用。要共享SQL語句，必須滿足以下3個條件：

（1） 當前被執行的語句和共享池中的語句必須完全相同。

（2） 兩個語句所指的對象必須完全相同。

（3） 兩個SQL語句中必須使用相同名字的綁定變量（bind variables）。

1.2 高質量的SQL語句

對于海量數據，質量高的SQL語句和質量差的SQL語句的執行時間可能相差幾百秒，可見對于應用系統來說，編寫高質量的SQL語句將顯著提高系統的速度和可用性。

1.2.1 使用DECODE函數來減少處理時間

使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表，例如：

SELECT COUNT（*）， SUM（SAL） FROM EMP

WHERE DEPT_NO = 0200 AND ENAME LIKE ‘SMITH%’；

SELECT COUNT（*）， SUM（SAL） FROM EMP

WHERE DEPT_NO = 0300 AND ENAME LIKE ‘SMITH%’；

使用DECODE函數，可以高效地得到相同結果：

SELECT COUNT（DECODE（DEPT_NO， 0200， ’X’， NULL）） D0020_COUNT，

COUNT（DECODE（DEPT_NO， 0300， ’X’， NULL）） D0030_COUNT，

SUM（DECODE（DEPT_NO， 0200， SAL， NULL）） D0020_SAL，

SUM（DECODE（DEPT_NO， 0300， SAL， NULL）） D0030_SAL

FROM EMP WHERE ENAME LIKE ‘SMITH%’；

類似的，DECODE函數也可以運用于GROUP BY 和ORDER BY子句中。

1.2.2 用TRUNCATE替代DELETE

當刪除表中的記錄時，在通常情況下，回滾段（rollback segments）用來存放可以被恢復的信息。如果用戶沒有提交事務，Oracle會將數據恢復到刪除之前的狀態（準確地說是恢復到執行刪除命令之前的狀況），而當運用TRUNCATE時，回滾段不再存放任何可被恢復的信息。當命令運行后，數據不能被恢復，因此只有很少的資源被調用，執行時間也會很短。

1.2.3 多使用COMMIT

在程序中應盡量多使用COMMIT，這樣程序的性能會得到提高，需求也會因為COMMIT所釋放的資源而減少。COMMIT可以釋放的資源包括：回滾段上用于恢復數據的信息，被程序語句獲得的鎖，redo log buffer 中的空間，以及Oracle管理上述3種資源的內部花費。

1.2.4 用EXISTS替代IN，用NOT EXISTS替代NOT IN

在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這種情況下，使用EXISTS（或NOT EXISTS）通常將提高查詢的效率。例如：

低效：

SELECT * FROM EMP （基礎表）

WHERE EMPNO > 0 AND DEPTNO IN （SELECT DEPTNO FROM DEPT WHERE LOC = 在這種情況下）

高效：

SELECT * FROM EMP （基礎表）

WHERE EMPNO > 0 AND EXISTS （SELECT MPNO > 0 ANPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’）

在大數據量時，這個操作非常明顯。相對來說，用NOT EXISTS替換NOT IN 將更顯著地提高效率。

1.2.5 用EXISTS替換DISTINCT

當提交一個包含一對多表信息（比如部門表和員工表）的查詢時，避免在SELECT子句中使用DISTINCT。一般可以考慮用EXIST替換。例如：

低效：

SELECT DISTINCT DEPT_NO，DEPT_NAME

FROM DEPT D，EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效：

SELECT DEPT_NO，DEPT_NAME

FROM DEPT D

WHERE EXISTS （SELECT ‘X’ FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO）；

EXISTS 使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結果。

1.2.6 避免耗費資源的操作

帶有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序（SORT）功能。 DISTINCT需要一次排序操作， 而其他的至少需要執行兩次排序。

例如，一個UNION查詢，其中每個查詢都帶有GROUP BY子句， GROUP BY會觸發嵌入排序（NESTED SORT）； 這樣， 每個查詢需要執行一次排序， 然后在執行UNION時， 又一個唯一排序（SORT UNIQUE）操作被執行，而且它只能在前面的嵌入排序結束后才能開始執行，嵌入的排序的深度會大大影響查詢的效率。

1.2.7 聯合查詢中，注意選取連接順序

查詢工作所包含表的數量在5個以上時，執行的連接效果會受到 From 語句中體現出來的次序影響。因為會選擇不同的優化處理器，選擇的基礎表也會不同。如果采用的是CBO，SQL 語句就會被優化器檢查每一個物理大小的表，索引的工作狀態，執行路徑最終要選取最低消費。假如選擇的是RBO，全部的索引都要對應連接的條件，這時候那個位于From 語句中的表應該就是基礎表，因為解析器處理表名的順序是從左至右。

1.3 使用索引

1.3.1 用索引提高效率

索引是表的一個概念部分，用來提高檢索數據的效率。實際上，Oracle使用了一個復雜的自平衡B-tree結構。通常，通過索引查詢數據比全表掃描要快。當Oracle找出執行查詢和Update語句的最佳路徑時，Oracle優化器將使用索引。同樣在聯結多個表時使用索引也可以提高效率。另一個使用索引的好處是，它提供了主鍵（primary key）的唯一性驗證。

除了那些LONG或LONG RAW數據類型，可以索引幾乎所有的列。通常，在大型表中使用索引特別有效。當然，在掃描小表時，使用索引同樣能提高效率。

雖然使用索引能得到查詢效率的提高，但是也必須注意到它的代價。索引需要空間來存儲，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4～5次的磁盤I/O。因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。這就涉及用戶所使用的系統是OLTP還是OLAP類型的，以此決定索引的建立。

1.3.2 避免在索引列上使用計算

當數據量很大時，在SQL語句中使用索引可將SQL語句的執行速度加快許多，但是不正確的SQL語句可能會使索引失效，所以必須注意一些限制索引使用的操作，例如：

WHERE子句中，如果索引列是函數的一部分，優化器將不使用索引而使用全表掃描，從而引起索引失效，所以應該盡量把此類操作移至比較符號的右邊。

SELECT * FROM DEPT WHERE SAL * 12 > 500000； ——索引失效

SELECT * FROM DEPT WHERE SAL > 500000/12； ——索引有效

1.3.3 避免潛在的數據類型轉換

當字符型數據與數值型數據比較時，Oracle就會自動將字符型用TO_NUM-BER（）函數進行轉換，這樣就導致了全表掃描。

SELECT * FROM T_SALARY WHERE SALARY=’1500’ ；

——SALARY字段為NUMBER型，卻與字符型比較，導致索引失效

1.3.4 避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以為空的列，Oracle將無法使用該索引。對于單列索引，如果列包含空值，索引中將不存在此記錄。對于復合索引，如果每個列都為空，索引中同樣不存在此記錄。如果至少有一個列不為空，則記錄存在于索引中。例如：

SELECT * FROM DEPT WHERE DEPT_CODE IS NOT NULL； ——索引失效

SELECT * FROM DEPT WHERE DEPT_CODE >=0； ——索引有效

2 小 結

對大型數據庫而言，數據海量增長，使得數據庫的優化調整成為一個重要問題。良好的系統架構設計、合理的資源配置和開發過程中高效SQL 語句編寫，是提高數據庫性能的關鍵因素。在應用系統開發設計中，通過對SQL的優化調整，可以顯著地改善整個數據庫系統的性能，降低系統響應時間，提高應用系統的運行效率，從而使 Oracle 數據庫獲得最優的性能。

主要參考文獻

[1] 黃河. Oracle 9i for Windows NT/2000 數據庫系統培訓教程[M]. 北京：清華大學出版社，2003：275-292.

[2] 謝東. 基于Oracle的數據庫安全策略[J]. 現代情報，2006，26（1）：119-120.

[3] 郭霞. 基于Oracle數據庫的SQL語句優化分析[J]. 電腦知識與技術，2011，7（21）：5063-5065.