MySQL中的反連接有什么用

這篇文章給大家分享的是有關(guān)MySQL中的反連接有什么用的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

   在表的連接上，半連接，反連接本身很平常，但是統(tǒng)計(jì)信息的不夠豐富導(dǎo)致執(zhí)行計(jì)劃的評(píng)估中可能會(huì)出現(xiàn)較大差別，會(huì)很可能把半連接，反連接的實(shí)現(xiàn)方式和執(zhí)行路徑的差異放大，導(dǎo)致SQL性能變差，同時(shí)MySQL里面in和exists的差距也在減小。

   我就簡(jiǎn)化一下我的描述，拿MySQL 5.6版本的一些差別來(lái)說(shuō)明。算是對(duì)5.5和5.7的承上啟下。

我們創(chuàng)建一個(gè)表t_fund_info，數(shù)據(jù)量在兩百萬(wàn)，創(chuàng)建另外一個(gè)表t_user_login_record數(shù)據(jù)量和t_fund_info一樣。 t_fund_info有主鍵字段account,t_user_login_record沒(méi)有索引。

SQL語(yǔ)句如下：

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');執(zhí)行計(jì)劃如下：

里面的列select_type PRIMARY代表子查詢中的最外層查詢，此處不是主鍵查詢。而SUBQUERY代表是子查詢內(nèi)層查詢的第一個(gè)SELECT,結(jié)果不會(huì)依賴于外部查詢的結(jié)果集。

從type為ALL代表是全表掃描，所以這樣一個(gè)查詢兩個(gè)表都是全表掃描，在MySQL內(nèi)部解析的時(shí)候是怎么分解的呢。我們通過(guò)explain extended的方式來(lái)得到更詳細(xì)的信息。

/* 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))))))))可以看到啟用了臨時(shí)表，查取了子查詢的數(shù)據(jù)作為后續(xù)的緩存處理數(shù)據(jù).

  這樣的處理，究竟對(duì)性能提升有多大呢，其實(shí)不大，而且性能改進(jìn)也很有限。

 我們換一個(gè)思路，那就是使用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'))))))  可以看到幾乎沒(méi)有做什么特別的改動(dòng)。

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

  當(dāng)然這種方式相對(duì)來(lái)說(shuō)性能提升都不大。一個(gè)局限就在于統(tǒng)計(jì)信息不夠豐富，所以自動(dòng)評(píng)估就會(huì)出現(xiàn)很大的差距。

  這個(gè)地方我們稍放一放，我們添加一個(gè)索引之后再來(lái)看看。

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))))))))

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

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

-----------------+---------+------+---------
 key             | key_len | ref  | rows    
-----------------+---------+------+---------
 NULL            | NULL    | NULL | 1875524
 ind_account_id2 | 182     | func |       1而之前沒(méi)有索引的時(shí)候，這個(gè)結(jié)果差別就很大了，是190多萬(wàn)。

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

/* 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'))))))

   以上可以看出，和沒(méi)有添加索引的解析方式?jīng)]有差別。哪里會(huì)差別呢，就是執(zhí)行的估算行數(shù)上，有天壤之別。
   所以通過(guò)這樣一個(gè)反連接的小例子，可以看出來(lái)存在索引的時(shí)候，not in會(huì)內(nèi)部轉(zhuǎn)換為not exists的處理方式，而not exists的方式在存在索引和不存在，兩者通過(guò)執(zhí)行計(jì)劃可以看出很大的差別，其中的一個(gè)瓶頸點(diǎn)就在于估算的行數(shù)。

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