物化表

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

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

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

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

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

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

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

臨時表的特性：

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

物化表轉連接

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

SELECT * FROM s1
WHERE 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表中的記錄數(shù)量 × 通過 m_val = xxx 對 materialized_table 表進行單表訪問的成本（物化表中的記錄是不重復的，并且為物化表中的列建立了索引，所以這個步驟顯然是非?？斓模?。

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

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

總結

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

最新評論