SQL語句執(zhí)行原理及性能優(yōu)化

李強(qiáng) 楊巋 吳天吉

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

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

中圖分類號(hào)：TB

文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1672-3198（2013）05-0186-02

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

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

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

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

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

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

第二步：語句解析。

當(dāng)客戶端把SQL語句傳送到服務(wù)器后，服務(wù)器進(jìn)程會(huì)對該語句進(jìn)行解析。語句解析是一個(gè)復(fù)雜的過程，主要流程如下：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

參考文獻(xiàn)

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

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