李強 楊巋 吳天吉

摘要：在關系數(shù)據(jù)庫中，隨著數(shù)據(jù)庫數(shù)據(jù)量的不斷增加，數(shù)據(jù)處理的速度幾乎決定了應用程序的響應速度，許多應用程序因處理的數(shù)據(jù)量過大而造成機器死機的情況時有發(fā)生。因此，如何有效地提高SQL語句的執(zhí)行效率，優(yōu)化SQL語句的性能，越來越成為開發(fā)人員關心的重要課題。

關鍵詞：SQL；執(zhí)行原理；性能優(yōu)化

中圖分類號：TB

文獻標識碼：A

文章編號：1672-3198（2013）05-0186-02

1造成SQL語句性能不佳的主要原因

（1）程序開發(fā)人員對查詢語句的效率重視程度不夠，只關注查詢結果的正確性，而忽視了查詢語句的效率。對于海量數(shù)據(jù)而言，劣質SQL語句和優(yōu)質SQL語句之間的速度差別可以達到上百倍，因此，開發(fā)一個應用系統(tǒng)不是簡單地能實現(xiàn)其功能即可，而是要寫出高質量的SQL語句，提高系統(tǒng)的可用性。

（2）開發(fā)人員在關注SQL語句效率的同時，對SQL語句執(zhí)行原理、影響SQL執(zhí)行效率的主要因素不清楚，這些也是影響SQL語句性能的優(yōu)化。

2SQL語句的執(zhí)行原理

Select語句可以說是DBA和數(shù)據(jù)庫開發(fā)者在工作中使用最多的語句，這條語句執(zhí)行效率的高低直接影響到應用程序的可用性。我們以Oracle數(shù)據(jù)庫為例，從Oracle數(shù)據(jù)庫后臺的運作機制來了解一條簡單的Select語句的執(zhí)行原理。這對SQL語句的優(yōu)化起到至關重要的作用。

第一步：客戶端把語句發(fā)給服務器端執(zhí)行。當我們在數(shù)據(jù)層執(zhí)行select語句時，應用程序客戶端會把SQL語句發(fā)送給服務器端，讓服務器端進程來處理這語句。

第二步：語句解析。

當客戶端把SQL語句傳送到服務器后，服務器進程會對該語句進行解析。語句解析是一個復雜的過程，主要流程如下：

（1）查詢高速緩存。服務器進程在接到客戶端傳送過來的SQL語句時，不會直接去數(shù)據(jù)庫查詢，而是會先在數(shù)據(jù)庫的高速緩存中去查找，是否存在相同語句的執(zhí)行計劃。如果存在，則服務器進程就會直接執(zhí)行這個SQL語句，省去后續(xù)的工作。所以，采用高速數(shù)據(jù)緩存的話，可以提高SQL語句的查詢效率。一方面是從內(nèi)存中讀取數(shù)據(jù)要比從硬盤中的數(shù)據(jù)文件中讀取數(shù)據(jù)效率要高，另一方面，也省去了語句解析等工作。

（2）語法檢查。當在高速緩存中找不到對應的SQL語句時，則數(shù)據(jù)庫服務器進程就會開始檢查這條語句的語法合法性。如果服務器進程認為這條SQL語句不符合語法規(guī)則的時候，就會把這個錯誤信息，反饋給客戶端。

（3）語義檢查。若SQL語句符合語法上的定義的話，則服務器進程接下去會對語句中的字段、表等內(nèi)容進行檢查?？纯催@些字段、表是否在數(shù)據(jù)庫中。如果表名與列名不準確的話，則數(shù)據(jù)庫會就會反饋錯誤信息給客戶端。

（4）獲得對象解析鎖。當語法、語義都正確后，系統(tǒng)就會對我們需要查詢的對象加鎖。這主要是為了保障數(shù)據(jù)的一致性，防止我們在查詢的過程中，其他用戶對這個對象的結構發(fā)生改變。

（5）數(shù)據(jù)訪問權限的核對。當語法、語義通過檢查之后，服務器進程還會檢查連接的用戶是否有數(shù)據(jù)訪問的權限。若用戶不具有數(shù)據(jù)訪問權限，則客戶端就不能夠取得這些數(shù)據(jù)。

（6）確定最佳執(zhí)行計劃。當語句與語法都沒有問題，權限也匹配的話，服務器進程開始根據(jù)一定的規(guī)則，對這條語句進行優(yōu)化。當服務器進程的優(yōu)化器確定這條查詢語句的最佳執(zhí)行計劃后，就會將這條SQL語句與執(zhí)行計劃保存到數(shù)據(jù)高速緩存。

第三步：語句執(zhí)行。

SQL語句執(zhí)行也分兩種情況。若被選擇行所在的數(shù)據(jù)塊已經(jīng)被讀取到數(shù)據(jù)緩沖區(qū)的話，則服務器進程會直接把這個數(shù)據(jù)傳遞給客戶端。若數(shù)據(jù)不在緩沖區(qū)中，則服務器進程將從數(shù)據(jù)庫文件中查詢相關數(shù)據(jù)，并把這些數(shù)據(jù)放入到數(shù)據(jù)緩沖區(qū)中。

第四步：提取數(shù)據(jù)。

當語句執(zhí)行完成之后，查詢到的數(shù)據(jù)還是在服務器進程中，還沒有被傳送到客戶端的用戶進程。所以，在服務器端的進程中，有一個專門負責數(shù)據(jù)提取的代碼。他的作用就是把查詢到的數(shù)據(jù)結果返回給用戶端進程，從而完成整個查詢動作。

3SQL優(yōu)化的一般性原則

SQL優(yōu)化的主要目標是減少服務器資源消耗（主要是磁盤IO）；設計優(yōu)化上盡量依賴oracle的優(yōu)化器，并為其提供條件，并創(chuàng)建合適的索引，注重列的可選擇性；在編碼優(yōu)化上要合理利用索引，避免大表FULL TABLE SCAN，合理使用臨時表，避免寫過于復雜的SQL語句，在不影響業(yè)務的前提下減小事務的粒度。

4SQL語句性能優(yōu)化技巧

（1）創(chuàng)建索引提高系統(tǒng)性能，但要避免在索引列上進行有關操作。

創(chuàng)建索引可以大大提高系統(tǒng)的性能。第一，通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。第三，可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。第四，在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。第五，通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

但是，增加索引也有許多不利的方面。第一，創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間。第三，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。

在使用索引的同時，避免在索引列上使用NOT，IS NULL和IS NOT NULL等操作，避免在索引列上使用計算，函數(shù)或改變索引列類型等。使用這些操作時，SQL語句就會停止使用索引轉而執(zhí)行全表掃描。

（2）優(yōu)化FROM表的選擇和WHERE條件的語句順序。ORACLE解析器按照從右到左的順序處理FROM子句中的表名，按照自下而上的順序解析WHERE子句。在FROM子句中包含多個表的情況下，寫在最后的表（基礎表driving table）將被最先處理，選擇記錄條數(shù)最少的表作為基礎表可以大幅提升處理效率；同樣WHERE子句中可以過濾最大數(shù)量記錄的條件寫在的末尾，性能會有本質的提升。

例1：表TABLE1有15000條記錄，表TABLE2僅有2條記錄，選擇TABBLE2作為基礎表，select count（*） from tab1，tab2執(zhí)行時間0.92秒。選擇TABLE1作為基礎表select count（*） from tab2，tab1執(zhí)行時間27.09秒。

例2：（低效，執(zhí)行時間150.4秒）

SELECT COL FROM EMP E WHERE SAL>49850 AND JOB=‘MANAGER AND 26 < （SELECT COUNT（*） FROM EMP WHERE MGR = E.EMPNO）

（高效，執(zhí)行時間9.9秒）

SELECT COL FROM EMP E WHERE 26 < （SELECT COUNT（*） FROM EMPWHERE MGR=E.EMPNO） AND SAL>49850 AND JOB=‘MANAGER

（3）使用高效的關鍵字替代低效的關鍵字。用EXISTS替代IN和DISTINCT，用Where子句替換HAVING子句等：

當提交一個包含一對多表信息（比如部門表和雇員表）的查詢時，避免在SELECT子句中使用DISTINCT或者IN，一般可以考慮用EXISTS替換，因為RDBMS核心模塊將在EXISTS子查詢的條件一旦滿足后，立刻返回結果。HAVING子句只會在檢索出所有記錄之后才對結果集進行過濾。這個處理需要排序，總計等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。

（4）避免使用耗費資源的操作：帶有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序（SORT）功能。DISTINCT需要一次排序操作，而其他的至少需要執(zhí)行兩次排序。通常，帶有UNION，MINUS，INTERSECT的SQL語句都可以用其他方式重寫。如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好，使用UNION，MINUS，INTERSECT也是可以考慮的，畢竟它們的可讀性很強。

通過以上分析可知，SQL語句的執(zhí)行效率其實就是計算機時間和空間的一個平衡。節(jié)省了查詢時間，提高了時間效率，就需要在空間方面給出犧牲；節(jié)約了系統(tǒng)空間，提高了空間效率，就需要在時間方面做出犧牲，因此在采取適當?shù)臄?shù)據(jù)策略時，應考慮SQL語句優(yōu)化，使得有效的硬件資源性能得到最大化的發(fā)揮。

參考文獻

[1]徐鳳梅.關系數(shù)據(jù)庫中SQL語言查詢的優(yōu)化策略[J].廣西輕工業(yè)，2009，（5）：77.

[2]郭玉棟，左金平.SQL語言查詢優(yōu)化方案探究[J].晉中學院學報，2006（6）：19.