275_DBA_子查詢優化

子查詢概述

1 子查詢分類

按照返回結果集區分子查詢

標量子查詢

只返回一個單一值的子查詢

Select （select m1 from t1 limit 1）

行子查詢

只返回一條記錄的子查詢

Select * from s1 where s1.key1 = (select key1 from s2 limit 1)

列子查詢

只返回一列數據的子查詢

Select * from s1 where s1.key1 in (select key1 from s2)

表子查詢

子查詢結果包很多記錄（類似表）

Select * from s1 where (s1.key1, s2.key2) in (select key1 ,key2 from s2)

按與外層查詢關系區分子查詢

不相關子查詢

子查詢可以自己運行 不依賴外層查詢

Select * from s1 where s1.key1 in (select key1 from s2)

相關子查詢

子查詢需要依賴外層查詢

Select * from s1 where s1.key1 in (select key1 from s2 where s2.id =s1.id)

2 子查詢在 布爾表達式中的使用

子查詢大部分場景在 where 或者 on 條件中充當搜索條件，與操作符(comparison_operator)形成布爾表達式

使用 =, <,> >= , != 作為布爾表達式的操作符

Select * from s1 where s1.key1 > (select min(key1) from t2 )

注: 子查詢只能是標量子查詢或者行子查詢

[NOT] IN /ANY/SOME/ALL子查詢

Select * from s1 where s1.key1 in (select key1 from s2)

注：

子查詢必須是列子查詢或者表子查詢 包括多個記錄

ANY/SOME意思相同

Select*from s1 where s1.key1 > any(select key1 from s2) ?#等價 > (select min(key1) from s2)

對于s1表的某條記錄的 key1列 如果子查詢中的結果集有任何一個比 key1小 則整個布爾表達式返回true

Select*from s1 where s1.key1 > all(select key1 from s2) #等價 > (select max(key1) from s2)

對于s1表的某條記錄的 key1列 如果子查詢中的結果集都比 key1小 則整個布爾表達式返回true

Exists 子查詢

只關心子查詢中是否有記錄 而不關心它記錄具體是啥 如果exists 后面有記錄則 exists表達式即為true

select count(*) from t1 where name in (select name from t2);

select count(*) from t1 where exists (select 1 from t2 where t1.name=t2.name);

3 注意事項

子查詢必須用小括號引起來

Select 子句中必須是標量子查詢

explain? select (select key1 , key2 from s2);

Operand should contain 1 column(s)

對于in/any/some/all 子查詢中 不允許有limit子句

explain select * from s1 where s1.key1 in (select * from s2 limit 2)

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

子查詢中 order by? distinct都是畫蛇添足 沒啥業務意義

select * from s1 where s1.key1 in (select distinct? key2 from s2 )

4 子查詢執行流程

例1 相關子查詢

select * from s1 where key1 in (select common_field from s2 where s1.key2 = s2.key2) # 其實是一個select 轉成了semi join

1 從外層 s1 去一條記錄 from s1 where s1.key2 = aaa;

2 將 key2=aaaa 這個常亮 代入內層子查詢 s2 去進行進行檢索 ?select common_field from s2 where s2.key2=aaa

3 將 common_field 返回給外層循環，外層s1 判斷 select * from s1 where key1 in (common_field) 如果滿足就返回 不滿足就繼續循環

例2 不相關子查詢

explain select * from s1 where key1 = (select commen_field from s2 limit 1 )

# 其實是相當于兩個 select 不能轉成semi join? 當初兩張表做單表查詢

5 子查詢的優化

5.1 物化表的提出

Mysql 將子查詢的結果集保存到臨時表的過程 稱為 物化(materialize)

例 explain? select * from s1 where s1.commen_field in? (select s2.commen_field from s2? )?? # 對s2結果集進行了物化

類似該SQL in （xxx,xxx,xxxx）很多結果集，相當于 select * from s1 where s1.commen_field = xxx1 or s1.commen_field=xxx2 or … or …

1 Mysql 做了一點點優化，不是直接將子查詢的結果集 當結果 直接當外層查詢的參數, 而是將結果集寫入一個臨時表， 將臨時表寫入涉及的列，將臨時表進行去重

2 臨時表去重大部分是在內存完成的，只要將列設為主鍵即可去重,或者使用memory引擎做臨時表 同時建立hash索引

3 如果臨時表過大 超過了 @@tmp_table_size 或 @@max_heap_table_size 會利用磁盤做臨時表

5.2? 物化轉連接

explain select * from s1 where s1.commen_field in (select s2.commen_field from s2 )

# 對s2結果集進行了物化 ?且與 s1 進行了 join （id 均為1 意味著只有一個select）

5.3 將子查詢轉為半連接

由于發現了 物化 join的優勢, 直接將子查詢轉為 join 發揮更大優勢 而且不需要進行臨時表的創建

例

explain select * from s1 where s1.commen_field in (select s2.commen_field from s2 ) 該SQL 其實可以理解為 select * from s1 join s2 on s1.commen_field = s2.commen_field select * from s1 semi join s2 on s1.commen_field = s2.commen_field # 并沒有半連接的語法

但我們無法預估 對于S1 表來說, 在S2表中 有多少條記錄滿足 s1.commen_field = s2.commen_field 可能一條沒有，可能只有一條或者多條

此時MySQL提出了 semi join 概念, s1 和 s2 semi join的意思是，對于S1 表中記錄，我們只care 在s2中是否存在匹配的記錄，而不關心具體多少條與之匹配，最終只報了S1匹配到的結果集即可

具體實現 semi join 方式如下

1 table pullout

當子查詢的查詢列只有主鍵 or 唯一索引列時, 可以吧子查詢的表上拉到外層查詢from子句中，并把子查詢搜索條件合并到外層查詢搜索條件中

explain select * from s1 where key2 in (select key2 from s2 where key3 = "alex")

從執行計劃中 發現 id 均為1 說明 使用了join , 所以SQL 理論上等于 select * from s1 join s2 on s1.key2=s2.key2 and s2.key3=”alex”

因為是 key2 是唯一索引 所以肯定唯一且不重復，所以直接將子查詢轉為了連接查詢

2 duplicate weedout

優化器會嘗試將in 轉成半連接, 當策略為 duplicate_Weedout時（通過創建臨時表方式為外層查詢中的記錄進行去重操作） 驅動表執行計劃對應extra為start temporary, 被驅動表為 end temporary

explain select * from s1 where key2 in (SELECT key2 from s2 where s2.commen_field = "alex")

# 其實優化器將SQL 優化成 select * from s1 semi join s2 on s1.key2 = s2.key2 where s2.commen_field = "alex"?? 將s2 當成了驅動表

3 loose scan

將in 子查詢 轉成 semi join ，如果采用looseScan （雖然是掃描索引，但只取鍵值相同的第一條記錄去匹配） 則驅動表執行計劃 extra 顯示looseScan

# 轉成semi join? select * from s1 semi join s2 on ?s1.key3= s2.key1 where s2.key1 like “a%”

# 轉成semi join 后 s2 作為驅動表; 基于索引 key1檢索中結果集符合 s2.key1 like “a%” 可能有 3865條，例 aalex aalex abob abob, 再做代入內層循環時候，只會取第一個 s2.key1 =aalex 作為條件代入 內層循環去判斷 是否能在 s1 找到對應的記錄，如果能找到就返回結果集，不能找到則進行下一次判斷

備注： 只會取鍵值相同的第一條記錄去進行判斷

explain select * from s1 where s1.key3 in (SELECT key1 from s2 where s2.key1 like "a%" )

4 semi-join Materialization

先把外層不相關子查詢物化的方式 ，再講外層查詢與物化表進行 join 方式 也算一種半連接

explain select * from s1 where s1.commen_field in (select s2.commen_field from s2 ) # 對s2結果集進行了物化 且與 s1 進行了 join （id 均為1 意味著只有一個select）

5 first match

首次匹配時最原始的semi join 執行方式, 先去外層查詢一條記錄, 然后到子查詢中尋找服務匹配條件的記錄

# s2 是子查詢 意味著會被執行多次 且要判斷是否符合外層傳入的列值

explain select * from s1 where s1.commen_field in (SELECT key1 from s2 where s2.key1 = s1.key1)

6? semi join 適用條件

不是所有的in 都能轉成 semi join 適用條件如下

子查詢必須是與IN 操作符組成的布爾表達式，且在外查詢的where 或者 ?on子句中

外層查詢可以有其它條件，必須適用 and 操作符與 IN子查詢的搜索條件連接起來

子查詢必須是單一查詢，不能有union 連起來的若干查詢

子查詢不能包括 group by， having語句或者聚合函數

其它情況

# or 條件 ; # not in; 位于select子句中

1 # or 條件

select * from s1 where key1 in (select * from xxx) or key2 > “alex”

2 ?# not in

select * from s1 where key1 not in (select * from xxx)

3 select key1 in? select 子句

explain select key1 in (select key2 from s2 where key1 > "bob") from s1;

4 包括union? 情況

explain select * from s1 where key1 in (select s2.commen_field from s2 where key3 ="a" union select s2.commen_field from s2 where key3 ="b")

注意 MySQL扔有相關辦法來優化 in 不能轉為 semi join 的方式 就是 exists , 對于任意一個 in 都可以寫成 exists方式

語法

select count(*) from t1 where name in (select name from t2); select count(*) from t1 where exists (select 1 from t2 where t1.name=t2.name);

注意 子查詢是個大表時候 使用exists的目的其實是讓大表走到索引上，如果外查詢是個大表 in 效率會好一些

總結

如何 IN 子查詢符合轉為semi? join 條件，優化器會把子查詢轉為 semi join 查詢，然后從下面集中方式中選取最優成本進行執行

Table Pullout

Duplicate Weedout

LooseScan

Semi-join Materialization

FirstMatch execution

如果 IN 子查詢不符合轉為半連接條件，優化器從下面策略中選取成本最低執行

先將子查詢物化，再執行查詢? primary subquery

執行IN 轉為 exists 的轉換

補充

對于派生表的優化

如果把子查詢放在from子句后面，這個子查詢相當于一個派生表，對于派生表MySQL提出兩種執行策略

1 把派生表進行物化

先將結果集寫到臨時表進行物化，然后把物化表當成普通表一樣進行訪問，但是MySQL選擇了類似懶加載/延遲物化的思想，用到了在物化，而不是在查詢時候直接進行物化

例 下面SQL 會先執行 s2.key3 = “bob”?如果有結果在 物化子查詢

explain SELECT * from ( select * from s1 where key1 ="alex") as ss join s2 on ss.id =s2.id where s2.key3 = "bob"

2 將派生表和外層查詢合并 （將派生表重寫成沒有派生表的形式） mysql優先會使用這種方式

可以將派生表與外層查詢合并，將派生表的搜索條件放在外層循環，例下面這個SQL

explain SELECT * from ( select * from s1 where key1 ="alex") as ss join s2 on ss.id =s2.id where s2.key3 = "bob"

根據執行計劃 發現ID均為1 說明進行了合并，這樣就消除了派生表，但派生表中有如下函數/語句時候就不能合并

聚合函數 max(), min(), sum()

Distinct

Group by

Having

Limit

Union / union all

派生表中的子查詢select子句中含有另一個子查詢

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。