MySQL中的反連接有什么用

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

   在表的連接上，半連接，反連接本身很平常，但是統計信息的不夠豐富導致執行計劃的評估中可能會出現較大差別，會很可能把半連接，反連接的實現方式和執行路徑的差異放大，導致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

-----------------+---------+------+---------
 key             | key_len | ref  | rows    
-----------------+---------+------+---------
 NULL            | NULL    | NULL | 1875524
 ind_account_id2 | 182     | func |       1而之前沒有索引的時候，這個結果差別就很大了，是190多萬。

------+---------+------+---------
 key  | key_len | ref  | rows    
------+---------+------+---------
 NULL | NULL    | NULL | 1875524
 NULL | NULL    | NULL | 1945902而順帶看看有了索引之后，not exists的方式是否會有改變。

/* 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中的反連接有什么用”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！