SQL優化–邏輯優化–子查詢優化(MySQL資料庫)

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<20 AND 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);

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *