EXISTS 与 IN 子查询：如何优化 SQL 查询性能？

带有 EXISTS 与 IN 的子查询：性能优化

使用子查询时，优化性能至关重要。两种常见的子查询方法是 EXISTS 和 IN，每种方法都有自己的优点和缺点。在这篇文章中，我们将探讨这些方法之间的关键区别，并演示它们的性能影响。

问题陈述

以下两个子查询在语义上是等效的，但是方法1 的执行时间明显长于方法 2：

方法 1（使用IN)

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（使用 EXISTS）

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

性能差异

通过分析解释计划，我们可以理解为什么方法 2 更快。关键区别在于这些方法处理子查询的方式：

• IN: 子查询会被计算多次，对于外部查询中的每一行计算一次。这是因为 IN 对子查询表执行全表扫描以进行每行比较。
• EXISTS: 无论外部查询中的行数有多少，子查询仅计算一次。这是因为一旦找到匹配的行，EXISTS 就会立即返回 true 或 false。

EXISTS 的优点

使用 EXISTS 比 IN 具有多个优点，特别是在处理大型子查询结果时：

• 更快执行： EXISTS 可以明显更快，因为它避免了不必要的表扫描。
• 简单性： EXISTS 通常比 IN 更容易阅读和理解，特别是对于复杂的子查询。
• NULL 的处理： EXISTS 可以比 IN 更好地处理 NULL 值，作为子查询的 NULL 结果，其计算结果只是 false。

IN 的优点

虽然 EXISTS 通常性能更高，但在某些情况下 IN 可能是首选:

• 小子查询结果：当子查询结果集较小时，IN 的性能开销相对于 EXISTS 来说变得可以忽略不计。
• 具体要求：在某些极少数情况下，IN 的语义可能是满足特定业务所必需的要求。

结论

在大多数情况下在这种情况下，EXISTS 因其性能优势和对 NULL 值的处理而成为子查询的首选方法。但是，在 EXISTS 和 IN 之间进行选择时，考虑特定用例和子查询大小以优化性能和查询效率非常重要。

以上是EXISTS 与 IN 子查询：如何优化 SQL 查询性能？的详细内容。更多信息请关注PHP中文网其他相关文章！