EXISTS与IN的子查询-MySQL [英] Subqueries with EXISTS vs IN - MySQL

问题描述

下面两个查询是子查询.两者相同，都对我有效.但是问题是方法1的查询执行大约需要10秒，而方法2的查询不到1秒.

Below two queries are subqueries. Both are the same and both works fine for me. But the problem is Method 1 query takes about 10 secs to execute while Method 2 query takes under 1 sec.

我能够将方法1的查询转换为方法2，但是我不了解查询中正在发生什么.我一直想自己弄清楚.我真的很想了解以下两个查询之间的区别是什么，以及如何获得性能提升?它背后的逻辑是什么?

I was able to convert method 1 query to method 2 but I don't understand what's happening in the query. I have been trying to figure it out myself. I would really like to learn what's the difference between below two queries and how does the performance gain happen ? what's the logic behind it ?

我是这些先进技术的新手.我希望有人能在这里帮助我.鉴于我阅读了 docs 但这并不给我头绪.

I'm new to these advance techniques. I hope someone will help me out here. Given that I read the docs which does not give me a clue.

方法1:

SELECT
   *       
FROM
   tracker       
WHERE
   reservation_id IN (
      SELECT
         reservation_id                                 
      FROM
         tracker                                 
      GROUP  BY
         reservation_id                                 
      HAVING
         (
            method = 1                                          
            AND type = 0                                          
            AND Count(*) > 1 
         )                                         
         OR (
            method = 1                                              
            AND type = 1                                              
            AND Count(*) > 1 
         )                                         
         OR (
            method = 2                                              
            AND type = 2                                              
            AND Count(*) > 0 
         )                                         
         OR (
            method = 3                                              
            AND type = 0                                              
            AND Count(*) > 0 
         )                                         
         OR (
            method = 3                                              
            AND type = 1                                              
            AND Count(*) > 1 
         )                                         
         OR (
            method = 3                                              
            AND type = 3                                              
            AND Count(*) > 0 
         )
   )

方法2:

SELECT
   *                                
FROM
   `tracker` t                                
WHERE
   EXISTS (
      SELECT
         reservation_id                                              
      FROM
         `tracker` t3                                              
      WHERE
         t3.reservation_id = t.reservation_id                                              
      GROUP BY
         reservation_id                                              
      HAVING
         (
            METHOD = 1 
            AND TYPE = 0 
            AND COUNT(*) > 1
         ) 
         OR                                                     
         (
            METHOD = 1 
            AND TYPE = 1 
            AND COUNT(*) > 1
         ) 
         OR                                                    
         (
            METHOD = 2 
            AND TYPE = 2 
            AND COUNT(*) > 0
         ) 
         OR                                                     
         (
            METHOD = 3 
            AND TYPE = 0 
            AND COUNT(*) > 0
         ) 
         OR                                                     
         (
            METHOD = 3 
            AND TYPE = 1 
            AND COUNT(*) > 1
         ) 
         OR                                                     
         (
            METHOD = 3 
            AND TYPE = 3 
            AND COUNT(*) > 0
         )                                             
   )

推荐答案

Explain Plan 将会向您显示为什么确切地应该使用Exists.通常，问题来自Exists vs Count(*). Exists更快.为什么?

An Explain Plan would have shown you why exactly you should use Exists. Usually the question comes Exists vs Count(*). Exists is faster. Why?

• 关于NULL所带来的挑战:当子查询返回Null时，对于IN，整个查询将变为Null .因此，您也需要处理该问题.但是使用Exist，它只是一个false.应付起来容易得多.只是IN不能与Null进行任何比较，但是Exists可以.

• With regard to challenges present by NULL: when subquery returns Null, for IN the entire query becomes Null. So you need to handle that as well. But using Exist, it's merely a false. Much easier to cope. Simply IN can't compare anything with Null but Exists can.

例如Exists (Select * from yourtable where bla = 'blabla');发现/匹配一个匹配项时，您将获得对/错.

e.g. Exists (Select * from yourtable where bla = 'blabla'); you get true/false the moment one hit is found/matched.

在这种情况下，IN会采用Count(*)的位置来根据WHERE选择 ALL 匹配行，因为它正在比较所有值.

In this case IN sort of takes the position of the Count(*) to select ALL matching rows based on the WHERE because it's comparing all values.

但也不要忘记这一点:

• EXISTS相对于IN高速执行:当子查询结果非常大时.
• IN领先于EXISTS:当子查询结果非常小时.

• EXISTS executes at high speed against IN : when the subquery results is very large.
• IN gets ahead of EXISTS : when the subquery results is very small.

参考以获取更多详细信息:

• 使用IN的子查询.
• IN-子查询优化
• 加入与子查询.

• subquery using IN.
• IN - subquery optimization
• Join vs. sub-query.

这篇关于EXISTS与IN的子查询-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！