Oracle數(shù)據(jù)庫的SQL語句優(yōu)化

何文硯

摘 要：隨著數(shù)據(jù)庫應用系統(tǒng)中數(shù)據(jù)的增加， 系統(tǒng)的性能提高成為數(shù)據(jù)庫系統(tǒng)中需要解決的主要問題， 在系統(tǒng)硬件不變的情況下， SQL語句優(yōu)化成為系統(tǒng)性能提高的主要途徑。本文針對Oracle數(shù)據(jù)庫的SQL調整和優(yōu)化，論述了SQL優(yōu)化的目的和原則，并通過若干實例，介紹了SQL語句優(yōu)化的一些方法。

關鍵詞：Oracle；SQL；查詢；優(yōu)化；索引

對Oracle數(shù)據(jù)庫的調優(yōu)是進行有目的的調整組件以改善性能， 即增加吞吐量和減少響應時間。從整體上看調優(yōu)是對各個組件的優(yōu)化，包括應用程序優(yōu)化、實例和數(shù)據(jù)庫優(yōu)化和環(huán)境優(yōu)化。對應用程序的優(yōu)化通常可分為兩個方面：源代碼的優(yōu)化和SQL語句的優(yōu)化。由于涉及到對程序邏輯的改變，源代碼的優(yōu)化在時間成本和風險上代價很高（尤其是對正在使用中的系統(tǒng)進行優(yōu)化），另一方面，源代碼的優(yōu)化對數(shù)據(jù)庫系統(tǒng)性能的提升收效有限，并且應用程序對數(shù)據(jù)庫的操作最終要表現(xiàn)為SQL語句對數(shù)據(jù)庫的操作。所以本文從Oracle數(shù)據(jù)庫執(zhí)行SQL語句的過程入手，逐步研究優(yōu)化SQL語句的方法。

1 SQL優(yōu)化的必要性與目的

數(shù)據(jù)庫系統(tǒng)是管理信息系統(tǒng)的核心，查詢操作在各類數(shù)據(jù)庫操作中占據(jù)比重最大，查詢速度直接影響數(shù)據(jù)庫的應用效率，對于大型數(shù)據(jù)庫來說，顯得更為重要，由于查詢操作在SQL語句中比重最大，因此優(yōu)化的查詢語句可以大幅提高應用系統(tǒng)的性能與效率。

1.1 優(yōu)化應用程序中SQL語句的必要性

SQL語句是對數(shù)據(jù)庫中的數(shù)據(jù)進行操作的惟一途徑，應用程序的執(zhí)行最終要體現(xiàn)為SQL語句的執(zhí)行，因此，SQL語句的效率在數(shù)據(jù)庫系統(tǒng)的性能優(yōu)化中起到了決定性的作用。根據(jù)統(tǒng)計，對硬件系統(tǒng)進行優(yōu)化所獲得的性能提升，只占數(shù)據(jù)庫系統(tǒng)性能提升的40%左右，另外的60%系統(tǒng)性能提升來自于對應用程序的優(yōu)化。對應用程序進行優(yōu)化通常主要從源代碼和SQL語句這兩個方面進行。目前SQL是使用最為廣泛的數(shù)據(jù)庫語言，SQL語句執(zhí)行將消耗70%至90%的數(shù)據(jù)庫資源，而應用程序對數(shù)據(jù)庫的操作，最終體現(xiàn)在用SQL語句對數(shù)據(jù)庫的操作，因此SQL語句的執(zhí)行效率決定了數(shù)據(jù)庫的性能。通過對SQL語句以及訪問數(shù)據(jù)庫方法的優(yōu)化調整，可以顯著地改善整個系統(tǒng)的性能，對提高數(shù)據(jù)庫內存區(qū)的命中率、減少I/O訪問、減少對網(wǎng)絡資源的占用等有著非常重要的意義。

程序員在使用SQL時往往會太多關注于SQL查詢的結果是否正確，而忽略了不同的查詢實現(xiàn)方法之間可能存在的性能效率上的差異，相同功能的SQL語句，使用不同的寫法來實現(xiàn)，在性能上會產(chǎn)生非常大的差異，執(zhí)行效率也會千差萬別。

1.2 優(yōu)化的實質與目的

在一個正確設計的數(shù)據(jù)庫中，如果使用的SQL結構不合理，仍然會遇到性能問題。調整SQL的關鍵是使數(shù)據(jù)庫尋找數(shù)據(jù)的路徑最簡化，主要通過調整SQL語句，來提高Oracle的性能。SQL語句優(yōu)化的實質就是用優(yōu)化器可以識別的語句，充分利用索引來減少表掃描的I/O次數(shù)，盡量避免表搜索的發(fā)生。優(yōu)化的目的就是將性能低下的SQL語句轉換性能優(yōu)異的SQL語句，使數(shù)據(jù)查找的路徑最佳化，并盡量保持CPU時間和I/O時間的平衡。在進行SQL語句優(yōu)化時，根據(jù)系統(tǒng)需求找出最有可能提高性能的語句，例如執(zhí)行頻率高的語句、整體消耗資源最多的語句以及每行消耗資源最多的語句等，并對其進行優(yōu)化。

2 SQL語句優(yōu)化方法

數(shù)據(jù)庫應用程序的執(zhí)行最終將歸結為數(shù)據(jù)庫中的SQL語句執(zhí)行。SQL語句消耗了數(shù)據(jù)庫的大部分資源。因此SQL語句的執(zhí)行效率最終決定ORACLE數(shù)據(jù)庫的性能，也就決定的應用系統(tǒng)的性能。下面通過實例介紹SQL語句優(yōu)化的若干方法。

2.1 建立有效的索引

Oracle服務器索引是一種模式對象，通過指針訪問來提高檢索數(shù)據(jù)的速度。如果沒有為表建立索引，檢索數(shù)據(jù)將會掃描整個表。建立索引后，對于索引列的訪問，可以通過指針快速定位表中的數(shù)據(jù)，從而有效減少磁盤的I/0操作。如果多表之問經(jīng)常聯(lián)接，那么可以在外鍵表的外鍵列上創(chuàng)建索引。這樣可以提高查詢與主鍵表的主鍵列匹配記錄的速度。

索引也不是越多越好，因為每一次對含有索引的表進行DML操作后就意味著索引必須更新，這也是要花費相當時間的。一般以下情況可以建立索引：

（1）經(jīng)常用于WHERE子句或作為連接條件的列；

（2）所含數(shù)據(jù)值范嗣比較的列；

（3）含有大量空值的列；

（4）絕大多數(shù)情況F只查詢出其總記錄的2%-4%的表。

2.2 SELECT中盡量不要使用“*”

當我們想顯示某表中的所有列時，一般在SELECT語句中使用“*”，這是一個比較方便的方法。例如select * from dept，但是這種方法非常低效。0RACLE服務器在解析的過程中，會通過數(shù)據(jù)字典將“*”依次轉換成所有的列名，這意味著將耗費更多的時間來完成，所以建議使用select deptno，dname，loc from dept這個命令，書寫起來似乎麻煩一些，但是執(zhí)行效率高多了。

2.3 減少訪問數(shù)據(jù)庫的次數(shù)

每條SQL語句被執(zhí)行時，Oracle服務器需要完成一系列任務：解析SQL語句、估算索引的利用率、綁定變量、讀數(shù)據(jù)塊等。因此減少數(shù)據(jù)庫訪問，就能減少服務器T作量。例如，要想查詢deptno是20與40的這兩個部門的信息時，用一條最高效的命令就是select deptno，dname from dept where deptno in（20，40），而不需要用兩條查詢命令或其他什么方法來完成，會無形中增加訪問數(shù)據(jù)庫的次數(shù)，從而降低效率。

2.4 盡量多使用COMMIT提交事務

Oracle對DDL語句是自動提交的，其他的比如DML語句是手工提交或者回滾事務。應用程序編寫中，在進行insert、delete、update等復雜語境操作時，應適當?shù)氖褂肅OMMIT進行事務的提交，以釋放會話中所持有的鎖，快速清理緩存中未修改的數(shù)據(jù)塊，從而釋放系統(tǒng)資源，提高系統(tǒng)性能。

2.5 用TRUNCATE替代DELETE

當執(zhí)行DELETE這個語句時。其刪除的內容通常情況下在回滾段（rollback segments）中暫時保存著，以便意外時需要恢復，直到執(zhí)行COMMIT語句時才真正生效。而當執(zhí)行TRUNCATE時，回滾段不保存任何可被恢復的信息，執(zhí)行該命令后，表中所有記錄被刪除.數(shù)據(jù)不能被恢復，因此占用很少的資源，執(zhí)行時間也會很短。因此如果刪除表中所有記錄時，建議使用TRUNCATE命令。

2.6 使用表的別名（Alias）

當要查詢的信息來自多個表時，建議在SQL語句中使用表的別名，并把別名前綴加于每個列上。這樣一來，就可以減少Oracle服務器解析的時間，也減少那些由列歧義引起的語法錯誤。例如： select e.empno，e.ename，d.deptno，d.dname from emp e，dept d where e.deptno=d.deptno；

2.7 用EXISTS替代IN

在多個基于基礎表的查詢中，往往需要對另一個表進行聯(lián)接。由于在使用IN 的子查詢中的表Oracle 將對其進行全表掃描，所以使用EXISTS（或NOT EXISTS）而替代IN（或NOT IN）通常能明顯提高查詢的效率。例如：

Select ename，job from emp where deptno in（select deptno from dept where dname=‘SALES3；（優(yōu)化前，低效）

Select ename，job from emp where exists（select deptno from dept where emp.deptno=dept.deptno and dname=‘SALES3；（優(yōu)化后，高效）

2.8 用修改語句（UPDATE）代替插入語句（INSERT）

如果需要向一個大型表中填入大量數(shù)據(jù)行（是已經(jīng)能確定的幾萬條記錄），一般常用的方法是使用INSERT語句，但事實證明，這并不足一個很好的選擇。如果該表有主鍵約束且是聚族索引，并且插入的數(shù)據(jù)行并不能保證按主鍵值的順利進行時。數(shù)據(jù)域達到上千條后，插入的速度將明顯減慢。比較可取的方法是，先按主鍵值順序將主鍵值插入表中，其他列值都為空，然后用修改語句來修改每個記錄的值，用具體值替代空值。速度會明顯加快。

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

在WHERE子句引用函數(shù)計算時，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而是使用全表掃描。應避免在索引列上使用函數(shù)。

優(yōu)化前：（FEE 列上的索引不被使用）

Select * from emp where fee*12>40000；

優(yōu)化后：（fee 列上的索引被使用，從而提高了查詢效率）

Select * from emp where fee>40000/12；

通過試驗，由表1對比顯示，采用改進后的SQL語句優(yōu)化算法后，高速緩存命中率明顯提高，程序運行的時間和CPU 成本也得到了改善，整個系統(tǒng)的性能比優(yōu)化前得到了改進，實現(xiàn)了數(shù)據(jù)庫系統(tǒng)總體性能的提高。

3 總結

信息量的不斷增長使得數(shù)據(jù)庫的優(yōu)化調整成為一個重要問題，尤其是對大型數(shù)據(jù)庫更為重要。良好的系統(tǒng)架構設計、合理的資源配置和開發(fā)過程中使用高效SQL語句，是影響數(shù)據(jù)庫運行性能高低的關鍵。在實際應用中，遵從主要的一些SQL優(yōu)化方法，通過不斷的實踐和總結，必將降低系統(tǒng)響應時間，提高應用系統(tǒng)的運行效率，使Oracle數(shù)據(jù)庫的性能得到提高。

[參考文獻]

[1]Hassan A.afyouni，著，吳越勝，張耀輝，等，譯.Oracle9i數(shù)據(jù)庫性能調整與優(yōu)化[M].北京：清華大學出版社，2005.

[2]王海鳳，王海亮，等，編著.Oracle 11g SQL和PL SQL從入門到精通[M].北京：中國水利出版社，2008.

[3]劉光霆.ORACLE中SOL查詢優(yōu)化研究[J].計算機與信息技術，2008，32（5）.

[4]蓋國強，楊廷琨，主編.OracleDBA手記3 數(shù)據(jù)庫性能優(yōu)化與內部原理解析[M].北京：電子出版社，2011.