SQL最佳化--邏輯最佳化--子查詢最佳化（MySQL），sqlmysql

SQL最佳化--邏輯最佳化--子查詢最佳化（MySQL），sqlmysql

1）子查詢概念：當一個查詢是另一個查詢的子部分時，稱之為子查詢（查詢語句中嵌套有查詢語句）。

子查詢出現的位置有：

a）目標列位置：子查詢如果位於目標列，則只能是標量子查詢，否則資料庫可能返回類似“錯誤:  子查詢必須只能返回一個欄位”的提示。

b）FROM子句位置：相互關聯的子查詢出現在FROM子句中，資料庫可能返回類似“在FROM子句中的子查詢無法參考相同查詢層級中的關係”的提示，所以相互關聯的子查詢不能出現在FROM子句中；非相互關聯的子查詢出現在FROM子句中，可上拉子查詢到父層，在多表串連時統一考慮串連代價然後擇優。

c）WHERE子句位置：出現在WHERE子句中的子查詢，是一個條件運算式的一部分，而運算式可以分解為操作符和運算元；根據參與運算的不同的資料類型，操作符也不盡相同，如INT型有“>、<、=、<>”等操作，這對子查詢均有一定的要求（如INT型的等值操作，要求子查詢必須是標量子查詢）。另外，子查詢出現在WHERE子句中的格式，也有用謂詞指定的一些操作，如IN、BETWEEN、EXISTS等。

d）JOIN/ON子句位置：JOIN/ON子句可以拆分為兩部分，一是JOIN塊類似於FROM子句，二是ON子句塊類似於WHERE子句，這兩部分都可以出現子查詢。子查詢的處理方式同FROM子句和WHERE子句。

e）GROUPBY子句位置：目標列必須和GROUPBY關聯1。可將子查詢寫在GROUPBY位置處，但子查詢用在GROUPBY處沒有實用意義。

f）ORDERBY子句位置：可將子查詢寫在ORDERBY位置處。但ORDERBY操作是作用在整條SQL語句上的，子查詢用在ORDERBY處沒有實用意義。

2）子查詢的分類

從對象間的關係看：

a）相互關聯的子查詢。

子查詢的執行依賴於外層父查詢的一些屬性值。子查詢因依賴於父查詢的參數，當父查詢的參數改變時，子查詢需要根據新參數值重新執行（查詢最佳化工具對相互關聯的子查詢進行最佳化有一定意義），如：

SELECT * FROM t1 WHERE col_1 = ANY

 (SELECT col_1 FROM t2 WHERE t2.col_2 = t1.col_2);

/* 子查詢語句中存在父查詢的t1表的col_2列 */

b）非相互關聯的子查詢。

子查詢的執行，不依賴於外層父查詢的任何屬性值。這樣子查詢具有獨立性，可獨自求解，形成一個子查詢計劃先於外層的查詢求解，如：

SELECT * FROM t1 WHERE col_1 = ANY

(SELECT col_1 FROM t2 WHERE t2.col_2 = 10);

//子查詢語句中（t2）不存在父查詢（t1）的屬性

從特定謂詞看：

a）[NOT] IN/ALL/ANY/SOME子查詢。

語義相近，表示“[取反] 存在/所有/任何/任何”，左面是運算元，右面是子查詢，是最常見的子查詢類型之一。

b）[NOT] EXISTS子查詢。

半串連語義，表示“[取反] 存在”，沒有左運算元，右面是子查詢，也是最常見的子查詢類型之一。

c）其他子查詢。

除了上述兩種外的所有子查詢。

從語句的構成複雜程度看：

a）SPJ子查詢。

由選擇、串連、投影操作組成的查詢。

b）GROUPBY子查詢。

SPJ子查詢加上分組、聚集操作組成的查詢。

c）其他子查詢。

GROUPBY子查詢中加上其他子句如Top-N 、LIMIT/OFFSET、集合、排序等操作。

後兩種子查詢有時合稱非SPJ子查詢。

從結果的角度看：

a）標量子查詢。

子查詢返回的結果集類型是一個簡單值。

b）單行單列子查詢。

子查詢返回的結果集類型是零條或一條單元組。相似於標量子查詢,但可能返回零條元組。

c）多行單列子查詢。

子查詢返回的結果集類型是多條元組但只有一個簡單列。

d）表子查詢。

子查詢返回的結果集類型是一個表（多行多列）。

3）子查詢的最佳化方法

a）子查詢合并（Subquery Coalescing）

在某些條件下（語義等價：兩個查詢塊產生同樣的結果集），多個子查詢能夠合并成一個子查詢（合并後還是子查詢，以後可以通過其他技術消除掉子查詢）。這樣可以把多次表掃描、多次串連減少為單次表掃描和單次串連，如：

SELECT * FROM t1 WHERE a1<10 AND (

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR 

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2) 

);

可最佳化為：

SELECT * FROM t1 WHERE a1<10 AND (

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND(t2.b2=1 OR t2.b2=2) 

/*兩個ESISTS子句合并為一個，條件也進行了合并 */

);

b）子查詢展開（Subquery Unnesting）

又稱子查詢反嵌套，又稱為子查詢上拉。把一些子查詢置於外層的父查詢中，作為串連關係與外層父查詢並列，其實質是把某些子查詢重寫為等價的多表串連操作（展開後，子查詢不存在了，外部查詢變成了多表串連）。帶來的好處是，有關的訪問路徑、串連方法和串連順序可能被有效使用，使得查詢語句的層次儘可能的減少。

常見的IN/ANY/SOME/ALL/EXISTS依據情況轉換為半串連（SEMI JOIN）、普通類型的子查詢消除等情況屬於此類，如：

SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10)v_t2 

WHERE t1.a1<10 AND v_t2.a2<20;

可最佳化為：

SELECT * FROM t1, t2 WHERE t1.a1<10 AND t2.a2<20AND t2.a2 >10; 

/* 子查詢變為了t1、t2表的串連操作，相當於把t2表從子查詢中上拉了一層 */

子查詢展開的條件：

a）如果子查詢中出現了聚集、GROUPBY、DISTINCT子句，則子查詢只能單獨求解，不可以上拉到外層。

b）如果子查詢只是一個簡單格式的（SPJ格式）查詢語句，則可以上拉子查詢到外層，這樣往往能提高查詢效率。子查詢上拉，討論的就是這種格式，這也是子查詢展開技術處理的範圍。

把子查詢上拉到上層查詢，前提是上拉（展開）後的結果不能帶來多餘的元組，所以子查詢展開需要遵循如下規則：

a）如果上層查詢的結果沒有重複（即SELECT子句中包含主碼），則可以展開其子查詢。並且展開後的查詢的SELECT子句前應加上DISTINCT標誌。

b）如果上層查詢的SELECT語句中有DISTINCT標誌，可以直接進行子查詢展開。

如果內層查詢結果沒有重複元組，則可以展開。

子查詢展開的具體步驟：

a）將子查詢和外層查詢的FROM子句串連為同一個FROM子句，並且修改相應的運行參數。

b）將子查詢的謂詞符號進行相應修改(如：“IN”修改為“=”)。

c）將子查詢的WHERE條件作為一個整體與外層查詢的WHERE條件合并，並用AND條件串連詞串連，從而保證新產生的謂詞與原舊謂詞的上下文意思相同，且成為一個整體。

c）聚集子查詢消除（Aggregate Subquery Elimination）

通常，一些系統支援的是標量聚集子查詢消除。如：

SELECT * FROM t1 WHERE t1.a1>(SELECT avg(t2.a2) FROM t2);

摘自《資料庫查詢最佳化工具的藝術》一書