何文硯

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

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

對Oracle數(shù)據(jù)庫的調(diào)優(yōu)是進(jìn)行有目的的調(diào)整組件以改善性能， 即增加吞吐量和減少響應(yīng)時間。從整體上看調(diào)優(yōu)是對各個組件的優(yōu)化，包括應(yīng)用程序優(yōu)化、實(shí)例和數(shù)據(jù)庫優(yōu)化和環(huán)境優(yōu)化。對應(yīng)用程序的優(yōu)化通?？煞譃閮蓚€方面：源代碼的優(yōu)化和SQL語句的優(yōu)化。由于涉及到對程序邏輯的改變，源代碼的優(yōu)化在時間成本和風(fēng)險上代價很高（尤其是對正在使用中的系統(tǒng)進(jìn)行優(yōu)化），另一方面，源代碼的優(yōu)化對數(shù)據(jù)庫系統(tǒng)性能的提升收效有限，并且應(yīng)用程序?qū)?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ù)庫的應(yīng)用效率，對于大型數(shù)據(jù)庫來說，顯得更為重要，由于查詢操作在SQL語句中比重最大，因此優(yōu)化的查詢語句可以大幅提高應(yīng)用系統(tǒng)的性能與效率。

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

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

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

1.2 優(yōu)化的實(shí)質(zhì)與目的

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

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

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

2.1 建立有效的索引

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

索引也不是越多越好，因?yàn)槊恳淮螌兴饕谋磉M(jìn)行DML操作后就意味著索引必須更新，這也是要花費(fèi)相當(dāng)時間的。一般以下情況可以建立索引：

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

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

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

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

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

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

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

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

2.4 盡量多使用COMMIT提交事務(wù)

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

2.5 用TRUNCATE替代DELETE

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

2.6 使用表的別名（Alias）

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

2.7 用EXISTS替代IN

在多個基于基礎(chǔ)表的查詢中，往往需要對另一個表進(jìn)行聯(lián)接。由于在使用IN 的子查詢中的表Oracle 將對其進(jìn)行全表掃描，所以使用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í)證明，這并不足一個很好的選擇。如果該表有主鍵約束且是聚族索引，并且插入的數(shù)據(jù)行并不能保證按主鍵值的順利進(jìn)行時。數(shù)據(jù)域達(dá)到上千條后，插入的速度將明顯減慢。比較可取的方法是，先按主鍵值順序?qū)⒅麈I值插入表中，其他列值都為空，然后用修改語句來修改每個記錄的值，用具體值替代空值。速度會明顯加快。

2.9 避免在索引列上使用計(jì)算

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

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

Select * from emp where fee*12>40000；

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

Select * from emp where fee>40000/12；

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

3 總結(jié)

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

[參考文獻(xiàn)]

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

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

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

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