以一個懵懂無知的青蛙來想像子查詢的執行方式..

如果是不相關子查詢

mysql> select * from single_table where key1 in (select common_field from single_table2);

先執行子查詢select common_field from single_table2得到結果後，再將其當作參數，去執行外層查詢
select * from single_table where key1 in(...)

如果是相關子查詢

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where s1.key2 = s2.key2);

要先從外層獲得第一筆資料，利用這個值去找到子查詢中符合的值，再將符合的值帶入外層條件判斷是否成立，是的話加入結果集，不是就捨棄，重複此步驟去執行獲取第二筆紀錄，一直往下。

純量子查詢、行子查詢的執行方式，確實如我們所想這麼直觀，但事情總不會這麼簡單。

in子查詢最佳化

先了解什麼是物化表？
對於一個不相關的子查詢如下：

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where key3 = 'a');

之所以無法單純地得到子查詢select common_field from single_table2 s2 where key3 = 'a'的結果後，帶入去查詢的原因是當結果的資料量很大的時候，效能就會變得很慢@@
因此mysql工程師想到的招式是將結果先存入一個臨時表，裡面的列就是子查詢結果中的列，然後寫入的紀錄都會被去重。而當然這個表一樣也可以像其他的表一樣建立索引，加快速度。
這個將子查詢結果的紀錄保存到臨時表的過程稱為物化。
方便起見我們把臨時表叫做物化表。

物化表轉連接

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where key3 = 'a');

我們重新來看這個敘述，把子查詢物化後，我們把物化表的名稱叫materialized_table，該物化表儲存的子查詢結果集的列為m_val。
以物化表的角度來看，就是掃描物化表的每一筆紀錄找到值與s1的key1相同的紀錄，將其加到最終結果。
因此這個敘述也可以看成是

select * from single_table inner join materialized_table on key1 = m_val;

這是一個內連接，所以用那一個當驅動表都是可以的，因此要分別計算以那個當驅動表所耗費的成本最低，來當作最終方案。

將子查詢轉為半連接

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where key3 = 'a');

透過前面的方式將子查詢物化轉為連接的方式提高了不少效能，但由於還是有臨時表的成本，因此工程師思考可不可以在沒有物化的情況下直接將子查詢轉為連接呢？
有的！可以透過半連接的方式，具體的策略有以下五種，有興趣的人可以在深入研究

• table pullout
• duplicate weedout
• LooseScan
• Semi-join Materialization
• FirstMatch

而當in子查詢不符合轉為半連接的條件，查詢最佳化工具會從下面的兩種策略找出成本最低的方式來執行

1. 子查詢物化，在執行查詢
2. 執行in到exists的轉換

mysql在處理帶有衍生表的敘述時，優先嘗試把衍生表和外層查詢進行合併
如果不行，再把衍生表物化掉，然後執行查詢。