目錄

• 物化表
• 物化表轉連接
• 總結

物化表

首先提出一個不相關的IN子查詢

SELECT * FROM s1  WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = "a");

對于不相關的 IN 子查詢來說，如果子查詢的結果集中的記錄條數很少，那么把子查詢和外層

查詢分別看成兩個單獨的單表查詢效率還是蠻高的，但是如果單獨執行子查詢后的結果集太多的話，就會導致這
些問題：

• 結果集太多，可能內存中都放不下～
• 對于外層查詢來說，如果子查詢的結果集太多，那就意味著 IN 子句中的參數特別多，這就導致：

無法有效的使用索引，只能對外層查詢進行全表掃描。
在對外層查詢執行全表掃描時，由于 IN 子句中的參數太多，這會導致檢測一條記錄是否符合和 IN 子句中的參數匹配花費的時間太長。
比如說 IN 子句中的參數只有兩個：
SELECT * FROM tbl_name WHERE column IN (a, b);
這樣相當于需要對 tbl_name 表中的每條記錄判斷一下它的 column 列是否符合 column = a OR column= b 。在 IN 子句中的參數比較少時這并不是什么問題，如果 IN 子句中的參數比較多時，比如這樣：
SELECT * FROM tbl_name WHERE column IN (a, b, c …, …);
那么這樣每條記錄需要判斷一下它的 column 列是否符合 column = a OR column = b OR column = c
OR … ，這樣性能耗費可就多了。

所以提出一個解決方案：不直接將不相關子查詢的結果集當作外層查詢的參數，而是將該結果集寫入一個臨時表里。

臨時表的特性：

1. 該臨時表的列就是子查詢結果集中的列。
2. 寫入臨時表的記錄會被去重。
3. 一般情況下子查詢結果集不會大的離譜，所以會為它建立基于內存的使用 Memory 存儲引擎的臨時表，而且會為該表建立哈希索引。
4. 如果子查詢的結果集非常大，超過了系統變量 tmp_table_size 或者 max_heap_table_size ，臨時表會轉而
   使用基于磁盤的存儲引擎來保存結果集中的記錄，索引類型也對應轉變為 B+ 樹索引。
   這個將子查詢結果集中的記錄保存到臨時表的過程稱之為 物化。

物化表轉連接

當我們把子查詢進行物化之后，假設子查詢物化表的名稱為 materialized_table ，該物化表存儲的子查詢結果集的列為 m_val ，那么這個查詢其實可以從下邊兩種角度來看待：

SELECT * FROM s1WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a");

從表 s1 的角度來看待，整個查詢的意思其實是：對于 s1 表中的每條記錄來說，如果該記錄的 key1 列的值

在子查詢對應的物化表中，則該記錄會被加入最終的結果集。畫個圖表示一下就是這樣：

從子查詢物化表的角度來看待，整個查詢的意思其實是：對于子查詢物化表的每個值來說，如果能在 s1 表
中找到對應的 key1 列的值與該值相等的記錄，那么就把這些記錄加入到最終的結果集。

也就是說其實上邊的查詢就相當于表 s1 和子查詢物化表 materialized_table 進行內連接：

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

如果使用 s1 表作為驅動表的話，總查詢成本由下邊幾個部分組成：

1. 物化子查詢時需要的成本
2. 掃描 s1 表時的成本
3. s1表中的記錄數量 × 通過 m_val = xxx 對 materialized_table 表進行單表訪問的成本（物化表中的記錄是不重復的，并且為物化表中的列建立了索引，所以這個步驟顯然是非常快的）。

如果使用 materialized_table 表作為驅動表的話，總查詢成本由下邊幾個部分組成：

1. 物化子查詢時需要的成本
2. 掃描物化表時的成本
3. 物化表中的記錄數量 × 通過 key1 = xxx 對 s1 表進行單表訪問的成本

總結

到此這篇關于Mysql查詢優化之IN子查詢優化方法的文章就介紹到這了,更多相關Mysql IN子查詢優化內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！