Qexists与in-mysql的子查询

下面两个查询是子查询。两者都一样,对我来说都很好。但问题是,方法1查询的执行时间大约为10秒,而方法2查询的执行时间不到1秒。

我能够将方法1查询转换为方法2,但我不明白查询中发生了什么。我一直想自己弄清楚。我真的很想知道下面两个查询的区别是什么,以及性能的提高是如何发生的?背后的逻辑是什么?

我对这些先进的技术还不熟悉。我希望有人能帮我。鉴于我阅读了文档,它没有给我任何线索。

方法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
         )                                             
   )
2013-01-07 11:14:04  bonCodigo

A回答

  • 1

    一个Explain Plan将向您展示为什么您应该确切地使用Exists。通常问题是Exists vs Count(*)Exists更快。为什么?< >

    • 关于空的挑战:当子查询返回Null时,因为在整个查询中变成Null。所以你也要处理好。但是使用Exist,它仅仅是一个false。更容易应付。简单的{ }无法将任何东西与{ }进行比较,但{ } }可以。

    • 例如IN当一次命中被发现/匹配时,就得到真/假。

    • 在本例中,Nullsort采用Exists的位置来选择基于Exists (Select * from yourtable where bla = 'blabla');的匹配行,因为它比较所有值。

    但也不要忘记:

    • IN在子查询结果非常大时对Count(*)高速执行。
    • WHERE领先于EXISTS:当子查询结果非常小时。

    有关详细信息,请参阅:

    IN
    2017-05-23 12:10:12  Community bonCodigo
    • 1
      20行是大行还是小行?<跨度> – Shafizadeh Dec 30 '15 at 12:22
    • @shafizadeh当然很小 – Xiaokun Jul 13 '17 at 9:10
  • 2

    方法2很快,因为它使用EXISTS运算符,其中iMySQL不加载任何结果。正如您在文档链接中提到的,它忽略了SELECT子句中的任何内容。它只检查与条件匹配的第一个值,一旦找到,它就会设置条件TRUE,并移动以进行进一步的处理。< >

    另一方面,方法1有IN运算符,该运算符加载所有可能的值,然后与之匹配。只有在找到完全匹配时才设置条件TRUE,这是一个耗时的过程。

    因此,方法2很快。

    希望有帮助…< >

    2013-01-07 06:14:40  jsist
  • 3

    exists运算符是返回true或false的布尔运算符。exists运算符通常在子查询中用于测试exist条件。

    SELECT 
        select_list
    FROM
        a_table
    WHERE
        [NOT] EXISTS(subquery);
    

    如果子查询返回任何行,则存在运算符返回true,否则返回false。

    此外,存在运算符在找到匹配行后立即终止进一步处理。由于这个特性,您可以使用exists运算符在某些情况下提高查询的性能。

    not运算符否定exists运算符。换句话说,如果子查询不返回行,则不存在返回true,否则返回false。

    您可以使用select*select columnselect a_constant或子查询中的任何内容。结果是相同的,因为mysql忽略了出现在select子句中的select\u列表。

    原因是存在运算符基于至少找到原则工作。它返回true并在至少找到一个匹配行时停止扫描表。

    另一方面,当in运算符与子查询组合时,mysql必须先处理子查询,然后使用子查询的结果来处理整个查询。

    一般经验法则是,如果子查询包含大量数据,则存在运算符可提供更好的性能。

    但是,如果子查询返回的结果集非常小,则使用in运算符的查询将执行得更快。

    有关详细说明和示例:mysql存在-mysqltutorial.org

    2018-09-20 08:04:37  k.t
  • 4

    第二种方法更快,因为您得到了类似于where t3.reservation_id=t.reservation_id的结果。在第一种情况下,子查询必须对表进行完全扫描以验证信息。然而,在2o方法中,子查询确切地知道它在寻找什么,一旦找到它,就会检查having条件。< >

    2013-01-07 07:36:00  medina
  • 5

    他们的官方文档。subquery optimization with exists

    2014-04-15 14:12:23  kta