MySQL - IN

IN句とEXISTS句の違い

パフォーマンスは、データベースエンジンの最適化機能に依存する。
また、インデックスの有無や統計情報によって実行計画が変わる可能性がある。

また、多くのデータベースエンジンでは、IN句とEXISTS句を内部的に相互に変換することがある。

IN句

サブクエリの結果を全て評価 (全走査) する。

サブクエリの結果をメモリ上に一時テーブルとして保持する必要がある。
そのため、サブクエリの結果セットが小さい場合に効率的である。

 /* IN句の場合 */
 SELECT *
 FROM orders 
 WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Korea')

EXISTS句

条件に一致するレコードが見つかった時点で評価を終了する。(Semi-join)

結果セットをメモリに保持する必要がない。
そのため、サブクエリの結果が大きい場合に効率的である。

 /* EXISTS句の場合 */
 SELECT *
 FROM orders o 
 WHERE EXISTS (SELECT 1 FROM customers c 
               WHERE c.id = o.customer_id AND c.country = 'Japan')

EXPLAIN PLAN (実行計画)

EXPLAIN PLANは、クエリがどのように実行されるかを確認するための機能である。

実行計画で確認できる主な情報を、以下に示す。

• テーブルスキャンの方法

  フルテーブルスキャン (全件検索)

  インデックススキャン

  インデックスオンリースキャン

• 結合方式

  ネステッドループ結合

  ハッシュ結合

  マージ結合

• コスト情報

  実行にかかる予想時間

  必要なメモリ量

  処理対象の予想行数

 -- 基本的な記述
 EXPLAIN SELECT * FROM users WHERE age > 20;
 
 -- より詳細な情報を見たい場合
 -- フォーマットされた出力
 EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age > 20;
 
 -- JSON形式で詳細情報を表示
 EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20;
 
 -- 実行時情報も含めて確認する場合
 -- ANALYZEを付けて実行時情報を表示
 EXPLAIN ANALYZE SELECT * FROM users WHERE age > 20;

 EXPLAIN SELECT * FROM users u 
         INNER JOIN orders o ON u.id = o.user_id 
         WHERE u.age > 20;
 
 -- 出力
 +----+-------------+-------+------------+-------+---------------+
 | id | select_type | table | type       | rows  | filtered     |
 +----+-------------+-------+------------+-------+---------------+
 |  1 | SIMPLE      | u     | ALL        | 1000  |         33.33|
 |  1 | SIMPLE      | o     | ref        |   10  |        100.00|
 +----+-------------+-------+------------+-------+---------------+

EXPLAIN PLANの結果の主な項目を、以下に示す。

• id

  SELECT文の識別子

  JOINの順序を示す

• select_type
  • SIMPLE

    単純なSELECT

  • PRIMARY

    サブクエリのある外部クエリ

  • SUBQUERY

    サブクエリ

  • DERIVED

    FROM句内の導出テーブル

• table

  対象のテーブル名

• type (アクセス方法、左に行くほど効率的)
  • system

    テーブルに1行のみ

  • const

    主キーで1件のみ取得

  • eq_ref

    ユニークインデックス参照

  • ref

    非ユニークインデックス参照

  • range

    インデックス範囲検索

  • index

    インデックスフルスキャン

  • ALL

    フルテーブルスキャン

    特に、type=ALL (フルテーブルスキャン) が出力されているかどうかを確認する。

• possible_keys

  利用可能なインデックス

• key

  実際に使用されるインデックス

• rows

  処理が必要な推定行数

  特に、rowsが想定より多くないかどうかを確認する。

• filtered

  フィルタ条件で絞られる行の割合

EXPLAIN PLANを確認することにより、以下に示すことが分かる。

• クエリが非効率な場合の原因特定
• インデックスの適切な使用
• 予想される処理時間やリソース使用量
• パフォーマンスチューニングの必要性

そのため、特に以下に示すような場合に、実行計画の確認が重要である。

• クエリの実行が遅い場合
• 大量のデータを扱う場合
• 複雑な結合を含むクエリが存在する場合
• インデックスの追加や変更を検討する場合

特に、適切なインデックスの使用、または、結合順序の最適化等を確認してパフォーマンスチューニングを行う。