SQL查詢優(yōu)化分析

徐愛蕓

摘 要：SQL對(duì)數(shù)據(jù)執(zhí)行最多的操作是對(duì)數(shù)據(jù)的查詢，查詢的方法不唯一，因此查詢的速度和效率差別也很大。本文分析了幾種查詢的方法：相關(guān)子查詢、無關(guān)子查詢、集合的查詢，分析查詢執(zhí)行的過程，并對(duì)每一種查詢方法進(jìn)行了定量的分析，從而得出了比較優(yōu)化的查詢方法。

關(guān)鍵詞：SQL語(yǔ)句;相關(guān)子查詢;無關(guān)子查詢;集合查詢;查詢優(yōu)化

1前言

子查詢也稱嵌套查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語(yǔ)句或其它子查詢中，任何允許使用表達(dá)式的地方都可以使用子查詢。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關(guān)子查詢。相關(guān)子查詢可以用EXISTS、NOT EXISTS引入。子查詢可以計(jì)算一個(gè)變化的聚合函數(shù)值，并返回到外圍查詢進(jìn)行比較。子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合于操縱一個(gè)表的數(shù)據(jù)。但是在性能上，往往一個(gè)不合適的子查詢用法會(huì)形成一個(gè)性能瓶頸。

2子查詢分類

一個(gè)查詢語(yǔ)句塊（select-from-where）可以嵌套在另外一個(gè)查詢塊的where子句中，從子查詢的語(yǔ)法規(guī)則和執(zhí)行過程不同，子查詢分為無關(guān)子查詢和相關(guān)子查詢兩種。

2.1無關(guān)子查詢

無關(guān)子查詢的結(jié)構(gòu)如下：SELECT ——FROM——WHERE? （SELECT ——FROM——WHERE）

無關(guān)子查詢的工作方式是由內(nèi)向外處理：先執(zhí)行內(nèi)查詢，外層查詢利用內(nèi)層查詢的結(jié)果作條件，子查詢返回結(jié)果的數(shù)據(jù)類型必須與外查詢 WHERE 語(yǔ)句的數(shù)據(jù)類型相匹配。

2.2相關(guān)子查詢

相關(guān)子查詢的結(jié)構(gòu)如下：SELECT ——FROM——WHERE? NOT? EXISTS （SELECT? *? ?FROM——? WHERE? NOT? EXISTS? ? （SELECT? * FROM —— WHERE? ?））;

帶有EXISTS量詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯值，子查詢的查詢條件依賴于外層父查詢的某個(gè)屬性值，這就是相關(guān)子查詢的由來，內(nèi)查詢的執(zhí)行次數(shù)與外查詢執(zhí)行次數(shù)相同。

3查詢執(zhí)行過程分析

以下面的三張表為例子，要求查詢被所有學(xué)生都選修了的課程信息。

Student（Sno ，Sname，Sdep ） Course（Cno， Cname， Credit）? SC（Sno， Cno， Score）

3.1采用相關(guān)子查詢

根據(jù)傳統(tǒng)的方法，我們采用三層嵌套，對(duì)應(yīng)的SQL語(yǔ)句如下：

SELECT Cno， Cname? ?FROM? Course? ?WHERE NOT EXISTS （SELECT? *? FROM Student WHERE NOT EXISTS? ? ?（SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno））;

這個(gè)算是一個(gè)比較復(fù)雜的sql語(yǔ)句了，兩個(gè) NOT EXISTS和三個(gè)WHERE，這個(gè)sql語(yǔ)句可以分為3層：最外層語(yǔ)句，最內(nèi)層語(yǔ)句，中間層語(yǔ)句。

其執(zhí)行過程如下：①進(jìn)入第一層：在外層Course中取出一個(gè)元組的Cno，如Cno值‘C1;②進(jìn)入第二層：取Student表中的一個(gè)Sno值‘S1;③進(jìn)入第三層：判斷SC表中是否有 Cno=‘C1 并且Sno=‘S1這樣的元組，經(jīng)過NOT EXISTS的取反，存在F，不存在就是T—— 結(jié)果F④返回第二層：取第二個(gè)Sno值‘S2⑤進(jìn)入第三層：判斷是否存在 Cno=‘C1 并且Sno=‘S2這樣的元組，結(jié)果取反 —— 結(jié)果F;⑥經(jīng)過多次這樣的迭代，取最后一個(gè)Sno值，結(jié)果取反 —— 結(jié)果F，返回第一層的結(jié)果：T（NOT EXISTS再次取反），所以將Cno='C1課程信息取出放入結(jié)果表中，即對(duì)應(yīng)Course的'C1記錄，Student表中的所有的記錄對(duì)應(yīng)的中間層的返回值為假，所以最外層的NOT EXISTS對(duì)應(yīng)的值為真，最外層的WHERE的值也為真，則'C1對(duì)應(yīng)的課程的記錄符合查詢條件，裝入結(jié)果表中。

重復(fù)上述步驟，直到將Course中的元組取完。

這是一個(gè)帶有全稱量詞的謂詞查詢（題中帶有“全部”），將其轉(zhuǎn)換為等價(jià)的存在量詞的謂詞查詢，即：不存在一個(gè)學(xué)生沒有選修這門課程的，即所有學(xué)生都選了這門課程。

可以看出：相關(guān)子查詢從上往下順序執(zhí)行，主查詢的每一行查詢都執(zhí)行一次完整的子查詢。EXISTS 操作符檢查在子查詢中是否存在滿足條件的行：如果在子查詢中存在滿足條件的行：不在子查詢中繼續(xù)查找，條件返回 TRUE;如果在子查詢中不存在滿足條件的行，條件返回 FALSE， 繼續(xù)在子查詢中查找。假設(shè)學(xué)生表有10條記錄，課程表有8條記錄，選課表按平均每人選6門課算，則整個(gè)查詢要執(zhí)行10×8×6=480次。

3.2 采用綜合查詢法

如果將外層設(shè)計(jì)為相關(guān)子查詢，而在中間層為無關(guān)子查詢，對(duì)應(yīng)的SQL語(yǔ)句如下：SELECT Cno，Cname? FROM Course WHERE NOT EXISTS （SELECT * FROM Student WHERE Sno? NOT IN （SELECT Sno? FROM? SC? WHERE Cno=C.Cno）） ;

其執(zhí)行過程如下：① 進(jìn)入第一層：在外層Course中取出一個(gè)元組的Cno，如Cno值‘C1;② 進(jìn)入第二層：執(zhí)行無關(guān)子查詢，按照無關(guān)子查詢執(zhí)行的順序，先執(zhí)行最內(nèi)層的查詢，在選課表中查詢選修了C1課程的學(xué)生的學(xué)號(hào)，返回到中間層;③ 經(jīng)過NOT? IN 的取反，得到的是沒有選修‘C1課程的學(xué)生的學(xué)號(hào);④ 根據(jù)相關(guān)子查詢的執(zhí)行原理，將中間層的結(jié)果返回到第一層，再一次經(jīng)過NOT EXISTS的取反，得到的是不存在沒有選修‘C1課程的學(xué)生，說明所有學(xué)生都選修了‘C1課程。

可以看出：這里采用了相關(guān)子查詢和無關(guān)子查詢兩種方法，同樣按上面的例子，由于第二層和第三層之間采用的無關(guān)子查詢，只需要在子查詢中查找滿足條件的元組，不需要外層查詢中的字段值，整個(gè)查詢要執(zhí)行10×8=80次。顯然，這種方法更好理解，且只有一層相關(guān)子查詢，執(zhí)行速度快。

3.3 采用集合的查詢

SQL的SELECT語(yǔ)句查詢結(jié)果是元組的集合，如果將外層設(shè)計(jì)為相關(guān)子查詢，而把內(nèi)層設(shè)計(jì)為集合的查詢，集合操作可以用差運(yùn)算。這樣三層嵌套查詢可以用兩層嵌套查詢完成。對(duì)應(yīng)的SQL語(yǔ)句如下：SELECT? Cno，Cname? FROM C? WHERE NOT EXISTS? （（ SELECT? Sno? ?FROM S）? ? EXCEPT? ?（SELECT Sno? FROM? SC? ）? WHERE Cno=C.Cno? ） ;

其執(zhí)行過程如下：① 進(jìn)入第一層：在外層Course中取出一個(gè)元組的Cno，如Cno值‘C1;② 進(jìn)入第二層：執(zhí)行集合的子查詢，在學(xué)生表中投影全部學(xué)生學(xué)號(hào)Sno，同時(shí)在選課表中查選修了‘C1課程的學(xué)生學(xué)號(hào)，然后執(zhí)行集合的差運(yùn)算，得到的是沒有選修‘C1課程的學(xué)生學(xué)號(hào)，返回到外層;③ 經(jīng)過NOT? EXISTS的取反，得到的是不存在沒有選修‘C1課程的學(xué)生，說明所有學(xué)生都選修了‘C1課程。

采用了相關(guān)子查詢和集合查詢，同樣按上面的例子，只有兩層查詢，則整個(gè)查詢要執(zhí)行10次。顯然，這種方法最簡(jiǎn)單，方法直接，易理解，執(zhí)行速度最快，效率最高。

結(jié)束語(yǔ)

一個(gè)查詢是可以用多種方法來實(shí)現(xiàn)的，一個(gè)復(fù)雜的查詢要用到嵌套查詢，相關(guān)子查詢最不好理解，執(zhí)行的過程長(zhǎng)，效率低;如果采用無關(guān)子查詢、集合查詢等，可以加快查詢的速度，提高查詢效率。

參考文獻(xiàn)

[1] 姜代紅，蔣秀蓮 .數(shù)據(jù)庫(kù)原理及應(yīng)用（第2版）清華大學(xué)出版社，2010.12

[2] 何玉潔 數(shù)據(jù)庫(kù)系統(tǒng)教程（第2版）人民郵電出版社，2015.12