基于Oracle數據庫的SQL語句優化

摘要:該文主要介紹了SQL語句優化技術，并對數據查詢語句自身的書寫提出一些方法用以優化性能低下的SQL語句。結論表明:對于Oracle這樣結構復雜但性能高度可調的數據庫，從查詢語句的書寫這方面進行優化，在不增加軟硬件成本投入的情況下，優化效果非常明顯，具有一定的實用價值。

關鍵詞:Oracle;數據庫;SQL;查詢;優化

中圖分類號:TP311文獻標識碼:A文章編號:1009-3044(2010)01-20-03

SQL Optimization Based on Oracle DataBase

ZHANG Xue-qin

(North University for the Nationalities， Ningxia 750021， China)

Abstract: This paper is introduced technique of SQL optimization，and puts up some methods on writing data query statement to enhance the low performance SQL lastly.The Findings indicate as follows:For oracle database whose structure is more complicate but the performance height is tunable，Optimizing from writing of data query statement.Without increasing hardware and software cost，the result is significant and is practicable to certain degree.

Key words: oracle; database; SQL; query; optimization

數據庫系統是管理信息系統的核心，從大多數系統的應用實例來看，查詢操作[1]在各種數據庫操作中占據的比重最大，查詢速度的快慢直接影響數據庫的推廣和應用，對于大型數據庫來說，這一點顯得尤其重要。由于查詢操作在SQL語句中代價最大，因此優質的查詢語句可以大大提高應用系統的性能。在檢查可能的性能改進時，首先應檢查最有可能導致性能變差的方面，包括編寫拙劣的應用SQL、效率差的SQL執行計劃、大小不合適的SGA內存結構、過度的文件I/O、訪問數據庫資源的紊亂等。事實上效率差的SQL會導致以上所列舉的所有問題。因為效率差的SQL會導致執行這些語句時使用的低效率執行計劃;低效率執行計劃會導致過度I/O;過度I/O會降低各SGA內存結構的效率;進而又導致過度的I/O和其它數據庫資源的激烈爭用。由此可見，SQL語句的執行效率決定了數據庫系統的性能。通過調整有問題的SQL語句，可以顯著改善一個系統的性能，對提高數據庫內存區的命中率、減少I/O訪問和對網絡帶寬的占用等有著非常重要的意義。

1 SQL語句優化技術簡介

1.1 SQL語句優化的實質

語句優化的實質就是在結果正確的前提下，用優化器可以識別的語句，充分利用索引來減少表掃描的I/O次數，盡量避免表搜索的發生。優化的目的就是將性能低下的SQL語句轉換成目的相同的、性能優異的SQL語句，使數據查找的路徑最簡化，并盡量保持處理器時間和I/O時間的平衡。

1.2 查找有問題的SQL語句

執行效率差的應用系統所具有的問題常歸因于構造不良的SQL語句。有效的數據庫調整需要準確地對系統內正在發生的具體SQL活動情況進行測量，以便及時對有問題的SQL語句進行調整，改進數據庫性能[2]。在進行SQL語句優化時，需要根據系統需求找出最有可能提高性能的語句，如執行頻次高的語句、整體消耗資源最多的語句以及每行消耗資源最多的語句等，并對其進行優化。

1.3 建立合適的語句

一個糟糕的數據庫系統往往是由若干條頻繁執行的問題SQL造成的。建立SQL語句時可以參考一些原則[3]。

2SQL 語句優化

2.1 SQL 語句的優化

SQL 語言是一種靈活的語言，相同的功能可以使用不同的語句來實現，但是語句的執行效率是很不相同的。程序員可以使用 EXPLAIN PLAN 語句來比較各種實現方案[4]，并選出最優的實現方案。在考慮 SQL 語句優化之前，有必要了解一下數據庫對表的訪問。Oracle 采用兩種訪問表中記錄的方式[5]:

1)全表掃描

全表掃描就是順序地訪問表中每條記錄。Oracle 采用一次讀入多個數據塊的方式優化全表掃描。

2)通過 ROWID 訪問表。

可以采用基于 ROWID 的訪問方式情況，提高訪問表的效率，ROWID 包含了表中記錄的物理位置信息。Oracle 采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系。通常索引提供了快速訪問 ROWID 的方法，因此那些基于索引列的查詢就可以得到性能上的提高[6]。

基于以上兩種 Oracle 訪問表的方式，程序員寫 SQL 語句需要滿足考慮如下規則:

(以下SQL語句均已學生-課程-教師數據庫為例:包括(student表、 sc表、 course表、teacher表、tc表、location表、category表)

1)盡量使用索引。試比較下面兩條 SQL 語句:

語句 A: SELECT deptno， deptname FROM dept WHERE deptno NOT IN

(SELECT deptno FROMstudent);

語句 B: SELECT deptno， deptname FROM dept WHERE NOT EXISTS

(SELECT * FROM student WHERE dept.deptno=student.deptno);

這兩條查詢語句實現的結果是相同的，但是執行語句 A 的時候，Oracle 會對整個 student表進行掃描，沒有使用建立在 student 表上的 deptno 索引，執行語句 B 的時候，由于在子查詢中使用了聯合查詢，Oracle 只是對 student 表進行的部分數據掃描，并利用了 deptno 列的索引，所以語句 B 的效率要比語句 A 的效率高一些。

2)選擇聯合查詢的聯合次序。考慮下面的例子:

SELECT sname FROM student，sc，course

WHERE sage between 18 and 20

AND score between 90 and 99

AND ccredit between 1 and 4

AND student.sno=sc.sno

AND sc.cno=course.cno;

這個 SQL 例子中，程序員首先需要選擇要查詢的主表，因為主表要進行整個表數據的掃描，所以主表應該數據量最小，所以例子中表 student 的 sage 列的范圍應該比表 sc 和表 course 相應列的范圍小。

1) 在子查詢中慎重使用 IN 或者 NOT IN 語句，使用 (NOT) EXISTS 的效果要好的多。

2) 慎用視圖的聯合查詢，尤其是比較復雜的視圖之間的聯合查詢。一般對視圖的查詢最好都分解為對數據表的直接查詢效果要好一些。

3) 可以在參數文件中設置 SHARED_POOL_RESERVED_SIZE 參數，這個參數在 SGA 共享池中保留一個連續的內存空間，連續的內存空間有益于存放大的 SQL 程序包。

4) Oracle 公司提供的 DBMS_SHARED_POOL 程序可以幫助我們將某些經常使用的存儲過程“釘”在 SQL 區中而不被換出內存，對于經常使用并且占用內存很多的存儲過程“釘”到內存中有利于提高最終用戶的響應時間。

2.2 優化 SQL 語句實例

2.2.1 選擇最有效率的表名順序

Oracle 的解析器按照從右到左的順序處理 FROM 子句中的表名，因此 FROM子句中寫在最后的表(基礎表 driving table)將被最先處理。在 FROM 子句中包含多個表的情況下，你必須選擇記錄條數最少的表作為基礎表。當 Oracle 處理多個表時，會運用排序及合并的方式連接它們。首先，掃描第一個表(FROM 子句中最后的那個表)并對記錄進行排序，然后掃描第二個表(FROM 子句中倒數第二個表)，最后將所有從倒數第二個表中檢索出的記錄與第一個表中合適記錄進行合并。

例如:

表 student:10000 條記錄

表 dept :20 條記錄

選擇 dept 作為基礎表(最好的方法)

SELECT count(*) FROM student，dept 執行時間 2.38 秒

選擇 student 作為基礎表(不佳的方法)

SELECT count(*) FROM dept，student 執行時間 23.19 秒

如果有3個以上的表連接查詢，那就需要選擇交叉表(intersection table)作為基礎表，交叉表是指那個被其他表所引用的表。

例如:

student 表描述了 location 表和 category 表的交集。

SELECT * FROM location L， category C， student S

WHERE S.sno BETWEEN 1000 AND 2000

AND S.catno=C.catno

AND S.locn=L.locn

將比下列 SQL 更有效率

SELECT * FROM student S ，location L，category C

WHERE S.catno=C.catno

AND S.locn=L.locn

AND S.sno BETWEEN 1000 AND 2000

2.2.2 WHERE 子句中的連接順序

Oracle 采用自下而上的順序解析 WHERE 子句，根據這個原理，表之間的連接必須寫在其他 WHERE 條件之前，那些可以過濾掉最大數量記錄的條件必須寫在 WHERE 子句的末尾。

例如:

(低效，執行時間 145.53 秒)

SELECT *

FROM teacher T

WHERE sal>5000

AND prof='rank'

AND 50<(SELECT COUNT(*) FROM T

WHERE rnk =T.tno);

(高效，執行時間 11.86 秒)

SELECT *

FROM teacher T

WHERE 50<(SELECT COUNT(*) FROM T

WHERE rnk=T.tno

AND sal>5000

AND prof='rank');

2.2.3 用 EXISTS 替代 IN

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

低效:

SELECT *

FROM student

WHERE sno>0

AND deptno IN (SELECT deptno FROM dept

WHERE loc='jxl1');

高效:

SELECT *

FROM student

WHERE sno>0

AND EXISTS (SELECT * FROM dept

WHERE dept.deptno=student.deptno

AND loc='jxl1');

2.2.4 用 NOT EXISTS 替代 NOT IN

在子查詢中，NOT IN 子句將執行一個內部的排序和合并。無論在哪種情況下，NOT IN 都是最低效的(因為它對子查詢中的表執行了一個全表遍歷)。為了避免使用 NOT IN，我們可以把它改寫成外連接(OUTER JOINS)或 NOT EXISTS。

例如:

SELECT sno

FROM sc

WHERE cno NOT IN (SELECT cno FROM course

WHERE cname='DB');

為了提高效率。改寫為:

SELECT sno

FROM sc

WHERE NOT EXISTS (SELECT *FROM course WHERE sc.cno=course.cno

AND cname='DB');

2.2.5 用 UNION 替換 OR(適用于索引列)

通常情況下，用 UNION 替換 WHERE 子句中的 OR 將會起到較好的效果。對索引列使用 OR 將造成全表掃描。注意，以上規則只針對多個索引列有效，如果有列沒有被索引，查詢效率可能會因為沒有選擇 OR 而降低。

低效:

SELECT sno，sname

FROM student

WHERE sage>20 OR ssex=’f’;

高效:

SELECT sno，sname

FROM student

WHERE sage>20

UNION

SELECT sno，sname

FROM student

WHERE ssex=’f’;

2.2.6 優化 GROUP BY

提高 GROUP BY 語句的效率，可以通過將不需要的記錄在 GROUP BY 之前過濾掉。下面兩個查詢返回相同結果但第二個明顯就快了許多。

低效:

SELECT cno，AVG(score)

FROM sc

GROUP BY cno

HAVING score>60 AND score<100;

高效:

SELECT cno，AVG(score)

FROM sc

WHERE score>60 AND score<100

GROUP BY cno;

3 結束語

SQL 語句優化對 Oracle 數據庫進行性能優化調整之后[7]，其系統響應時間已明顯下降，使程序運行速度有所提高，良好的保障了本系統的順利運行。并且可以看到的是，優化SQL語句對Oracle數據庫性能優化起到了主要的作用，高效 SQL 語句的編寫，是數據庫運行性能高低的關鍵，所以在實際的性能管理工作中，應加強這方面的優化[8]。

參考文獻:

[1] 李建中，王珊.數據庫系統原理[M].北京:電子工業出版社，2004.

[2] 郭敏，郭靖.Oracle 10g數據庫性能優化的研究[J].武漢理工大學學報，2005，27(30):104.

[3] 卞榮兵等.基于ORACLE數據庫性能優化的研究[J].應用技術，2002，9:36-38.

[4] 鄒俊，吳京慧.Oracle數據庫系統性能調整與優化[J].現代計算機，2006，9:28.

[5] 文宏.Oracle 9i簡明教程[M].北京:清華大學出版社，2003.

[6] 肖軍.Oracle數據庫性能調整與優化[M].武漢:武漢大學，2004.

[7] 胡杰.數據庫應用系統的性能分析與優化方法研究[D].南京:河海大學，2004.

[8] 張俊紅.ORACLE數據庫性能優化的主要方法[J].計算機應用系統，2001(8):59-61.