MySQL中的反連接有什么用

180次閱讀

共計 3243 個字符，預計需要花費 9 分鐘才能閱讀完成。

這篇文章給大家分享的是有關 MySQL 中的反連接有什么用的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

在表的連接上，半連接，反連接本身很平常，但是統計信息的不夠豐富導致執行計劃的評估中可能會出現較大差別，會很可能把半連接，反連接的實現方式和執行路徑的差異放大，導致 SQL 性能變差，同時 MySQL 里面 in 和 exists 的差距也在減小。

我就簡化一下我的描述，拿 MySQL 5.6 版本的一些差別來說明。算是對 5.5 和 5.7 的承上啟下。

我們創建一個表 t_fund_info，數據量在兩百萬，創建另外一個表 t_user_login_record 數據量和 t_fund_info 一樣。t_fund_info 有主鍵字段 account,t_user_login_record 沒有索引。

SQL 語句如下：

select account
from t_fund_info
where money = 300
and account not in (select distinct (account)
from t_user_login_record
where add_time = 2016-06-01 執行計劃如下：

里面的列 select_type PRIMARY 代表子查詢中的最外層查詢，此處不是主鍵查詢。而 SUBQUERY 代表是子查詢內層查詢的第一個 SELECT, 結果不會依賴于外部查詢的結果集。

從 type 為 ALL 代表是全表掃描，所以這樣一個查詢兩個表都是全表掃描，在 MySQL 內部解析的時候是怎么分解的呢。我們通過 explain extended 的方式來得到更詳細的信息。

/* select#1 */
select test . t_fund_info . account AS account
from test . t_fund_info
where ((test . t_fund_info . money = 300) and
(not ( in_optimizer
(test . t_fund_info . account, test . t_fund_info .
account in
(materialize
(/* select#2 */
select test . t_user_login_record . account
from test . t_user_login_record
where (test . t_user_login_record . add_time = 2016-06-01)),
primary_index_lookup
(test . t_fund_info . account in temporary
table on auto_key
where((test . t_fund_info . account = materialized – subquery .
account)))))))) 可以看到啟用了臨時表，查取了子查詢的數據作為后續的緩存處理數據.

這樣的處理，究竟對性能提升有多大呢，其實不大，而且性能改進也很有限。

我們換一個思路，那就是使用 not exists

explain extended select t1.account from t_fund_info t1 where t1.money =300 and not exists (select distinct(t2.account) from t_user_login_record t2 where t1.account=t2.account and t2.add_time = 2016-06-01 這種方式在 MySQL 是如何分解的呢。

select test . t1 . account AS account
from test . t_fund_info t1
where ((test . t1 . money = 300) and
(not
(exists ( /* select#2 */
select test . t2 . account
from test . t_user_login_record t2
where ((test . t1 . account = test . t2 . account) and
(test . t2 . add_time = 2016-06-01)))))) 可以看到幾乎沒有做什么特別的改動。

這一點在 5.5,5.6,5.7 中都是很相似的處理思路。

當然這種方式相對來說性能提升都不大。一個局限就在于統計信息不夠豐富，所以自動評估就會出現很大的差距。

這個地方我們稍放一放，我們添加一個索引之后再來看看。

create index ind_account_id2 on t_user_login_record(account);
然后使用 not in 的方式查看解析的詳情。

select test . t_fund_info . account AS account
from test . t_fund_info
where ((test . t_fund_info . money = 300) and
(not ( in_optimizer
(test . t_fund_info .
account, exists
(index_lookup
(cache (test . t_fund_info . account) in t_user_login_record on
ind_account_id2
where((test . t_user_login_record . add_time = 2016-06-01) and
(cache (test . t_fund_info . account) = test .
t_user_login_record . account))))))))

可以看到這個方式有了索引，not in 和 not exits 的解析方式很相似。有一個差別就是在子查詢外有了 cache 的處理方式。

我們來看看兩者的差別，同樣的步驟，有了索引之后，估算的 key_len(使用索引的長度) 為 182，估算行數為 1

/* select#1 */
select test . t1 . account AS account
from test . t_fund_info t1
where ((test . t1 . money = 300) and
(not
(exists ( /* select#2 */
select test . t2 . account
from test . t_user_login_record t2
where ((test . t1 . account = test . t2 . account) and
(test . t2 . add_time = 2016-06-01))))))

以上可以看出，和沒有添加索引的解析方式沒有差別。哪里會差別呢，就是執行的估算行數上，有天壤之別。
所以通過這樣一個反連接的小例子，可以看出來存在索引的時候，not in 會內部轉換為 not exists 的處理方式，而 not exists 的方式在存在索引和不存在，兩者通過執行計劃可以看出很大的差別，其中的一個瓶頸點就在于估算的行數。

感謝各位的閱讀！關于“MySQL 中的反連接有什么用”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

正文完