Mysql查詢優化之IN子查詢優化方法詳解

2023-02-10 06:02:13

物化表

首先提出一個不相關的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 … ，這樣效能耗費可就多了。

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

臨時表的特性：

該臨時表的列就是子查詢結果集中的列。
寫入臨時表的記錄會被去重。
一般情況下子查詢結果集不會大的離譜，所以會為它建立基於記憶體的使用 Memory 儲存引擎的臨時表，而且會為該表建立雜湊索引。
如果子查詢的結果集非常大，超過了系統變數 tmp_table_size 或者 max_heap_table_size ，臨時表會轉而
使用基於磁碟的儲存引擎來儲存結果集中的記錄，索引型別也對應轉變為 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 表作為驅動表的話，總查詢成本由下邊幾個部分組成：

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

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

物化子查詢時需要的成本
掃描物化表時的成本
物化表中的記錄數量 × 通過 key1 = xxx 對 s1 表進行單表存取的成本

總結

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

Mysql查詢優化之IN子查詢優化方法詳解

目錄

物化表

物化錶轉連線

總結

熱門文章