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中文網其他相關文章！