MySQL 子查询中的 EXISTS 与 IN：哪个性能更高？

子查询优化：MySQL 中的 EXISTS 与 IN

在 MySQL 中，使用 EXISTS 和 IN 的子查询可以获得类似的结果，但性能影响却截然不同。本文深入研究了这些技术之间的差异，并探讨了它们对查询执行时间的影响。

性能比较查询

提供的示例演示了使用不同方法产生相同结果的两个子查询：

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

观察到方法 1 (IN) 执行大约需要 10 秒，而方法 2 (EXISTS) 在不到一秒的时间内完成。这种巨大的性能差异值得探索。

子查询行为

仔细检查发现，这些子查询利用不同的技术来识别跟踪器表中满足特定条件的行。

IN 子查询（方法一）：该子查询返回一组满足指定条件的reservation_ids。然后，外部查询检查该集合中是否存在 tracker 中每一行的reservation_id，并返回匹配的行。

EXISTS 子查询（方法 2）：EXISTS 确定该集合中是否有任何行tracker表满足给定reservation_id的指定条件。然后，外部查询评估此条件并返回 EXISTS 返回 true 的行。

性能注意事项

这些方法之间的性能差异源于其底层逻辑和效率：

• EXISTS： EXISTS 执行一系列分组操作以确定是否有任何行满足条件。当跟踪器表中的行数很大时，这种方法效率更高。
• IN: IN 将跟踪器表中的每个reservation_id 与子查询返回的集合进行比较，这是计算性的更昂贵，尤其是当子查询返回大量行时。

结论

中一般来说，EXISTS 是处理大型子查询结果时的首选方法，并且它对子查询中的 NULL 值不敏感。另一方面，当子查询结果相对较小且不关心 NULL 值时，IN 会更高效。

为了进一步优化性能，建议考虑以下因素：

• 使用适当的索引：确保tracker表中的reservation_id列有索引，以提高效率查找。
• 限制子查询结果：使用 LIMIT 或 WHERE 过滤等技术减少子查询返回的行数。

以上是MySQL 子查询中的 EXISTS 与 IN：哪个性能更高？的详细内容。更多信息请关注PHP中文网其他相关文章！