EXISTS 与 IN 的子查询 - MySQL

2022-01-23 00:00:00 sql subquery mysql phpmyadmin query-optimization

以下两个查询是子查询.两者都是一样的，对我来说都很好.但问题是方法 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.

方法一:

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

方法二:

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(*) 的位置来选择 ALL 匹配的行，基于WHERE 因为它比较所有值.

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 - 子查询优化
  • 加入与子查询.

相关文章