MySQL サブクエリの EXISTS と IN: どちらの方がパフォーマンスが高いでしょうか?

サブクエリの最適化: MySQL における EXISTS と IN の比較

MySQL では、EXISTS と IN を使用したサブクエリは同様の結果を達成できますが、パフォーマンスへの影響は対照的です。この記事では、これらの手法の違いを詳しく掘り下げ、クエリの実行時間への影響を調査します。

パフォーマンス比較クエリ

提供された例は、異なるアプローチを使用して同一の結果をもたらす 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
        )                                             
    )

メソッド 1 (IN) の実行には約 10 秒かかることがわかります一方、方法 2 (EXISTS) は 1 秒以内に完了します。この大幅なパフォーマンスの違いには、調査の余地があります。

サブクエリの動作

詳しく調べると、これらのサブクエリは、特定の条件を満たすトラッカー テーブル内の行を識別するためにさまざまな手法を利用していることがわかります。

IN サブクエリ (メソッド 1): このサブクエリは、次のセットを返します。指定された基準を満たすreservation_id。次に、外側のクエリは、トラッカー内の各行のreservation_idがこのセットに存在するかどうかを確認し、一致する行を返します。

EXISTSサブクエリ(方法2): EXISTSは、トラッカー内の行が存在するかどうかを判断します。トラッカー テーブルは、指定されたreservation_idに対して指定された条件を満たします。次に、外側のクエリがこの条件を評価し、EXISTS が true を返す行を返します。

パフォーマンスに関する考慮事項

これらのアプローチのパフォーマンスの違いは、基礎となるロジックと効率に起因します。

• EXISTS: EXISTS は一連の group-by 操作を実行して、存在するかどうかを判断します。行が条件を満たしています。このアプローチは、トラッカー テーブル内の行数が多い場合に効率的です。
• IN: IN は、トラッカー テーブル内のすべてのreservation_idを、サブクエリによって返されたセットと比較します。これは計算上行われます。特にサブクエリが多数の値を返す場合、より高価になります。 rows.

結論

一般に、大規模なサブクエリの結果を処理する場合は、EXISTS が推奨されるアプローチであり、サブクエリ内の NULL 値の影響を受けません。一方、サブクエリの結果が比較的小さく、NULL 値が問題にならない場合、IN はより効率的です。

パフォーマンスをさらに最適化するには、次の点を考慮することをお勧めします。

• 適切なインデックスを使用します: トラッカー テーブルのreservation_id列に効率的なインデックスがあることを確認してください。 lookups.
• サブクエリの結果を制限する: LIMIT や WHERE フィルタリングなどの手法を使用して、サブクエリによって返される行数を減らします。

以上がMySQL サブクエリの EXISTS と IN: どちらの方がパフォーマンスが高いでしょうか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。