MySQL サブクエリの EXISTS と IN: どちらのパフォーマンスが優れていますか?

MySQL における EXISTS と IN のサブクエリ: パフォーマンスの比較

サブクエリは、データベースから特定のデータを抽出する際に重要な役割を果たします。一般的なサブクエリ メソッドは、EXISTS と IN の 2 つです。どちらも同様の結果を達成できますが、異なるパフォーマンス特性を示します。

次の 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
        )
);

パフォーマンスの点では、方法 2 は方法 1 よりも大幅に優れており、実行にかかる時間は 10 秒を超える場合と比較して 1 秒未満です。この矛盾の理由を理解するには、各メソッドの内部動作を詳しく調べる必要があります。

EXISTS と IN: 主な違い

• EXISTS : 少なくとも 1 つの行がサブクエリに一致するかどうかを確認します。そうであれば、true を返します。それ以外の場合は false を返します。行自体を取得するのではなく、行の存在に依存します。
• IN: 外部クエリの値をサブクエリ内の各行と比較します。一致するものが見つかった場合は true を返します。それ以外の場合は false を返します。サブクエリ内のすべての行に対して実行されます。

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

• サブクエリのサイズ: サブクエリが大きな値を返す場合行数が多いため、IN はすべての行と比較するとコストが高くなる可能性があります。逆に、EXISTS は一致する行を 1 つ検索するだけで済むため、大規模なサブクエリの効率が向上します。
• Null 値: EXISTS は IN よりも効率的に Null 値を処理できます。 IN を含むサブクエリが null を返すと、その null が外側のクエリに伝播される可能性があります。ただし、EXISTS は null を false として扱います。
• 最適化: MySQL はインデックスを使用して EXISTS を最適化できますが、IN ではマテリアライズド ビューやマテリアライズド ビューの使用など、追加の最適化が必要になる場合があります。

結論

で一般に、サブクエリが多数の行を返すことが予想される場合、または null 値が含まれる場合は、EXISTS をお勧めします。小さなサブクエリの場合は、IN の方がパフォーマンスが高い場合があります。特定のクエリに対する最適なアプローチを決定するには、Explain Plan を使用することを常にお勧めします。

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