[[195280]]

當(dāng)你想對兩個表進(jìn)行差分運算時，你有兩種選擇：使用NOT EXISTS 的子查詢或者NOT IN 。后者可以說更易于編寫，可以使查詢方法更加明顯。現(xiàn)代數(shù)據(jù)庫系統(tǒng)可以優(yōu)化兩種執(zhí)行計劃從而查詢到類似的結(jié)果，可以在外部和內(nèi)部處理查詢的相關(guān)性(我說“現(xiàn)代”，因為在上世紀(jì)90年代中期我已經(jīng)吸取教訓(xùn)，當(dāng)時我正在使用Oracle 7.3，它沒有這個功能)。

兩種結(jié)構(gòu)有一個很大的不同：如果子查詢返回的結(jié)果為NULL，那么 NOT IN 的條件將不執(zhí)行，因為 NULL不等于它或不等于其它值。但是如果你注意到這一點，它們是等價的。事實上，這些消息告訴我們，NOT IN 查詢更快，人們更喜歡用它查詢。

這篇文章是關(guān)于一個數(shù)據(jù)庫顯著變慢的情況，而空值正是罪魁禍?zhǔn)住?/p>

考慮以下兩個可能是用來追蹤點擊流數(shù)據(jù)的表。由于我們跟蹤匿名和注冊用戶, EVENTS.USER_ID是可空的。然而，當(dāng)用戶不空，二級指標(biāo)標(biāo)就會具有較高的基數(shù)。

create table USERS 
( 
  ID    integer auto_increment primary key, 
  ... 
) 
 
create table EVENTS 
( 
  ID      integer auto_increment primary key, 
  TYPE    smallint not null, 
  USER_ID integer 
  ... 
) 
 
create index EVENTS_USER_IDX on EVENTS(USER_ID); 

好的，現(xiàn)在讓我們使用這些表：從一小部分用戶開始，我們想找到那些沒有特定事件的用戶。 使用NOT IN子句，并確保null值不出現(xiàn)在內(nèi)部結(jié)果中，查詢?nèi)缦滤荆?/p>

select  ID 
from    USERS 
where   ID in (1, 7, 2431, 87142, 32768) 
and     ID not in 
        ( 
        select  USER_ID 
        from    EVENTS 
        where   TYPE = 7 
        and     USER_ID is not null 
        ); 

對于我的測試數(shù)據(jù)集，USERS表有100,000行，EVENTS表有10,000,000行，并且EVENTS表中大約75%的USER_ID為空。 我在我的筆記本電腦上運行這條查詢，它有一個Core i7處理器，12 GB的RAM和一個SSD。

我一直運行了約2分鐘，這真是...哇。

讓我們用NOT EXISTS和相關(guān)的子句替換NOT IN：

select  ID 
from    USERS 
where   ID in (1, 7, 2431, 87142, 32768) 
and     not exists 
        ( 
        select  1 
        from    EVENTS 
        where   USER_ID = USERS.ID 
        and     TYPE = 7 
        ); 

這個版本運行在0.01秒，這比我預(yù)期的時間更短。

是時候比較一下執(zhí)行計劃了。 ***個計劃來自NOT IN查詢，第二個來自NOT EXISTS。

+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 
| id | select_type        | table  | partitions | type           | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                    | 
+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 
|  1 | PRIMARY            | USERS  | NULL       | range          | PRIMARY         | PRIMARY         | 4       | NULL |    5 |   100.00 | Using where; Using index | 
|  2 | DEPENDENT SUBQUERY | EVENTS | NULL       | index_subquery | EVENTS_USER_IDX | EVENTS_USER_IDX | 5       | func |  195 |    10.00 | Using where              | 
+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 

執(zhí)行計劃幾乎相同：都是從USERS表中選擇行，然后使用嵌套循環(huán)連接(“DEPENDENT SUBQUERY”)從EVENTS表中檢索行。都聲稱使用EVENTS_USER_IDX在子查詢中選擇行。并且他們在每一步都估計了相似的行數(shù)。

但更仔細(xì)地查看連接類型。 NOT IN版本使用 index_subquery，而NOT EXISTS版本使用 ref。再查看ref列：NOT EXISTS版本使用了對其它列的顯式引用，而NOT IN使用了一個函數(shù)。這里發(fā)生了什么?

index_subquery連接類型表示MySQL將掃描索引以查找子查詢的相關(guān)行?？赡苁沁@個問題嗎?我不這么認(rèn)為，因為EVENTS_USER_IDX索引是“narrow”類型：它只有一列，所以引擎不應(yīng)該讀取大量的塊來查找對應(yīng)的外部查詢的ID行(的確，我嘗試了各種查詢來測試這個索引，并且所有的運行都在幾百分之一秒內(nèi))。

為了獲取更多信息，我轉(zhuǎn)向使用“extended”執(zhí)行計劃。 要查看此計劃，請使用explain extended作為查詢前綴，并接著使用 show warnings得到被MySQL優(yōu)化器優(yōu)化后的查詢語句。 這是從NOT IN查詢得到的(為了清晰重新格式化了)：

/* select#1 */  select `example`.`USERS`.`ID` AS `ID`  
                from    `example`.`USERS`  
                where   ((`example`.`USERS`.`ID` in (1,7,2431,87142,32768))  
                        and (not( 
 
    (`example`.`USERS`.`ID`, 
 
 
     ( 
 
      ( 
 
       (`example`.`USERS`.`ID`) in EVENTS on EVENTS_USER_IDX checking NULL where ((`example`.`EVENTS`.`TYPE` = 7) and (`example`.`EVENTS`.`USER_ID` is not null)) having  
 
        (`example`.`EVENTS`.`USER_ID`)))))))  

我找不到“on EVENTS_USER_IDX checking NULL”的解釋，但我認(rèn)為發(fā)生的是：優(yōu)化器認(rèn)為它正在執(zhí)行一個IN查詢，可以在結(jié)果中包含NULL; 在做出此決定時，它不考慮where子句中的空檢查。 因此，它將檢查(examine)USER_ID為null的750萬行，以及與外部查詢的值匹配的幾十行。 通過“檢查(examine)”，我的意思是它將讀取表行，然后應(yīng)用不為null條件。 此外，基于運行查詢所花費的時間，我認(rèn)為它為外部查詢中的每個候選值執(zhí)行了此操作。

所以，本文的論點是：每當(dāng)你想在可為空的列上使用IN或NOT IN子查詢時，請重新思考并使用EXISTS或NOT EXISTS代替。