為什麼這兩個查詢有如此不同的執行？

我正在嘗試獲取在給定日期內未收到消息的所有使用者。我有兩張表，一張是帶有reporterid 索引列的消息，一張是reporter 表。

讓我帶您了解一下：

此內部查詢返回在給定日期收到消息的記者。在我的本地開發機器上需要十分之一秒：

select  m.reporterid
   from  nubamessage m
   where  m.messagetypeenum =7
     and  m.createdOn>='2016-06-18 00:00:00'
     and  m.createdOn<='2016-06-18 09:30:00';

如果我執行 select(distinct) 與否，上述查詢的執行時間沒有區別，並且它返回相同的行。

但是如果我在外部連接查詢中使用它，它基本上永遠不會完成：

select  *
   from  reporter r
   left outer join  
   (
       SELECT  m.reporterid
           from  nubamessage m
           where  m.messagetypeenum =7
             and  m.createdOn>='2016-06-18 00:00:00'
             and  m.createdOn<='2016-06-18 09:30:00'
   ) as mm  ON mm.reporterid=r.id
   where  r.enabled=1 and m.reporterid is null;

但是，如果我將內部查詢更改為如下所示的不同，則只需幾分之一秒：

select  *
   from  reporter r
   left outer join  
   (
       SELECT  distinct(m.reporterid)   -- the only difference
           from  nubamessage m
           where  m.messagetypeenum =7
             and  m.createdOn>='2016-06-18 00:00:00'
             and  m.createdOn<='2016-06-18 09:30:00'
   ) as mm  ON mm.reporterid=r.id
   where  r.enabled=1 and m.reporterid is null;

未完成查詢的執行計劃：

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  r   NULL    ALL NULL    NULL    NULL    NULL    11538   50.00   "Using where"
1   SIMPLE  m   NULL    ALL NULL    NULL    NULL    NULL    968388  100.00  "Using where; Using join buffer (Block Nested Loop)"

我剛剛添加“distinct”的查詢的執行計劃，它添加了一些自動鍵：

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY r   NULL    ALL NULL    NULL    NULL    NULL    11538   50.00   "Using where"
1   PRIMARY <derived2>  NULL    ref <auto_key0> <auto_key0> 5   r.id    10  100.00  "Using index"
2   DERIVED m   NULL    ALL NULL    NULL    NULL    NULL    968388  1.11    "Using where; Using temporary"

如果有人能在心裡向我解釋為什麼 1. 第一個查詢永遠不會完成/這麼慢 2. 為什麼 distinct 讓它執行得很快，即使內部查詢在兩種情況下都返回完全相同的行，我將不勝感激。

謝謝

編輯：請注意，我寫了“所有尚未發送消息的使用者。這就是我有左連接的原因 - 稍後在查詢中我有“m.reporterid 為空”，以便只讓記者離開消息表中還沒有消息。我已經編輯了上面的問題以反映這一點。

我本可以使用“not in”來代替，但從我讀過的內容和我的測試來看，它比左連接要慢一些。

LEFT正在扼殺性能；除非您有充分的理由保留它，否則請刪除它。詳細說明…

（（**但首先……**原始查詢中缺少的目的LEFT；此答案假定LEFT沒有必要。））

LEFT JOIN表示您想要來自右表的數據，無論是否與左表匹配。

JOIN說只顯示匹配（通過ON）兩個表的行。

（OUTER是可選的，不添加任何語義。）

如果你執行的是 5.6 之前的版本，則派生表（中的子查詢LEFT JOIN）將沒有索引，因此必須重複掃描。這是擺脫LEFT.

如果沒有LEFT，優化器可能會評估一次子查詢，然後有效JOIN地reporter完成查詢。

對於JOIN，內部查詢需要這個複合（和覆蓋）索引INDEX(messagetypeenum, createdOn, reporterid)。

還有另一種技術（我認為）……擺脫內部SELECT，簡單地JOIN（或LEFT JOIN）到表：

select  r.*
   from  reporter r
   left join  nubamessage m  ON m.reporterid = r.id
     AND  m.messagetypeenum = 7
     and  m.createdOn >= '2016-06-18 00:00:00'
     and  m.createdOn <= '2016-06-18 09:30:00'
   WHERE  r.enabled = 1
     AND  m.reporterid is null;

在這種情況下，它可能需要INDEX(reporterid, messagetypeenum, createdOn).

另一個變體將使用EXISTS 並且，我認為，提供相當於LEFT

select  r.*
   from  reporter r
   WHERE  r.enabled=1
     AND NOT EXISTS 
   (
       SELECT  *
           from  nubamessage m
           where  m.messagetypeenum =7
             and  m.createdOn>='2016-06-18 00:00:00'
             and  m.createdOn<='2016-06-18 09:30:00'
             AND  m.reporterid=r.id 
   ) 

我無法預測哪種變體最快。它部分取決於 : 的 many:1 映射中有多少“多少m.reporterid” r.id。

在大多數情況下LEFT JOIN是優化的第一點。

此查詢與您的查詢相同，但它使用聚合而不是LEFT JOIN：

SELECT counters.id
FROM
(
   SELECT r.id, 0 as cnt, 1 as enabl
   FROM reporter r
   WHERE r.enabled=1
   UNION
   SELECT
       m.reporterid as id,
       count(1) as cnt,
       0 as enabl
   FROM nubamessage m
   WHERE m.messagetypeenum =7
       and m.createdOn>='2016-06-18 00:00:00'
       and m.createdOn<='2016-06-18 09:30:00'
   GROUP BY m.reporterid
) AS counters
GROUP BY counters.id
HAVING sum(counters.cnt)=0
   and sum(counters.enabl)>0

引用自：https://dba.stackexchange.com/questions/152587