MySQL - IN

概要

IN句は、MySQLにおいて指定した値のリストまたはサブクエリの結果に対して、カラムの値が含まれるかを判定するための演算子である。

複数のOR条件を簡潔に記述できるため、コードの可読性が向上し、特に多数の値を比較する場合に有用である。
例えば、WHERE id = 1 OR id = 2 OR id = 3 という冗長な条件式を、WHERE id IN (1, 2, 3) と簡潔に記述できる。

IN句は、WHERE句やHAVING句と組み合わせて使用され、データの絞り込みやフィルタリングに広く用いられる。
また、サブクエリと組み合わせることにより、別テーブルの検索結果を条件として動的に指定することも可能である。

IN句の主な使用シーンを以下に示す。

特定のIDリストに該当するレコードの取得
ユーザーが選択した複数の商品IDや注文IDに基づくデータ抽出
カテゴリやステータスによるフィルタリング
複数のカテゴリに属する商品や、特定のステータスを持つ注文の検索
他テーブルとの関連データの取得
サブクエリを使用して、関連テーブルに存在するレコードのみを抽出
バッチ処理における対象データの指定
処理対象となる複数のレコードを一度に指定

IN句を使用する場合は、パフォーマンスへの影響を考慮する必要がある。
特に、IN句に指定する値が数千件を超える場合や、サブクエリが大量のデータを返す場合は、クエリの実行時間が大幅に増加する可能性がある。

MySQL 8.0以降では、セミジョイン最適化によりIN句を含むサブクエリのパフォーマンスが改善されているが、
大量データを扱う場合は、JOINへの書き換えや一時テーブルの活用を検討することを推奨する。

IN句の基本構文

IN句は、指定したリスト内に値が存在するかを判定する。

リスト値の指定

最も基本的な使用方法は、カッコ内に値のリストを直接指定する形式である。

 # 基本構文
 SELECT * FROM テーブル名
    WHERE カラム名 IN (値1, 値2, 値3, ...);
 
 # 数値の例
 SELECT * FROM employees
    WHERE department_id IN (10, 20, 30);
 
 # 文字列の例
 SELECT * FROM products
    WHERE category IN ('Electronics', 'Books', 'Clothing');

上記のIN句は、以下のOR条件と同等である。

 SELECT * FROM employees
    WHERE department_id = 10
       OR department_id = 20
       OR department_id = 30;

サブクエリとの組み合わせ

IN句は、サブクエリの結果セットに対しても使用できる。

 # サブクエリを使用したIN句
 SELECT * FROM employees
    WHERE department_id IN (
       SELECT department_id FROM departments
          WHERE location = 'Tokyo'
    );
 
 # 複雑なサブクエリの例
 SELECT * FROM orders
    WHERE customer_id IN (
       SELECT id FROM customers
          WHERE registration_date >= '2024-01-01'
             AND status = 'active'
    );

複数カラムでのIN句

MySQL 5.7以降では、Row Constructor を使用して複数カラムの組み合わせでIN句を使用できる。

 # 複数カラムのIN句
 SELECT * FROM employees
    WHERE (department_id, job_id) IN (
       (10, 'MANAGER'),
       (20, 'CLERK'),
       (30, 'SALESMAN')
    );
 
 # サブクエリとの組み合わせ
 SELECT * FROM order_details
    WHERE (order_id, product_id) IN (
       SELECT order_id, product_id FROM special_promotions
          WHERE promotion_date = CURDATE()
    );

NOT IN句

NOT IN句は、指定したリスト内に値が存在しないことを判定する。

基本構文

NOT IN句の基本的な使用方法を以下に示す。

 # 基本構文
 SELECT * FROM テーブル名
    WHERE カラム名 NOT IN (値1, 値2, 値3, ...);
 
 # 使用例
 SELECT * FROM employees
    WHERE department_id NOT IN (10, 20, 30);
 
 # サブクエリとの組み合わせ
 SELECT * FROM customers
    WHERE id NOT IN (
       SELECT customer_id FROM orders
          WHERE order_date >= '2024-01-01'
    );

NULLに関する注意点

NOT IN句を使用する場合、リスト内にNULLが含まれると予期しない結果になる可能性がある。

 # NULLが含まれる場合の問題
 SELECT * FROM employees
    WHERE department_id NOT IN (10, 20, NULL);
 
 # 結果 : 0件 (NULLの影響で全ての行が除外される)

これは、SQLの3値論理 (TRUE, FALSE, UNKNOWN) に基づく動作である。
NULLとの比較は常にUNKNOWNを返すため、NOT IN句全体がFALSEまたはUNKNOWNとなり、結果が0件になる。

※対策方法

IS NULL条件を併用する方法
WHERE department_id NOT IN (...) OR department_id IS NULL
IFNULL関数またはCOALESCE関数を使用する方法
WHERE IFNULL(department_id, -1) NOT IN (...)
NOT EXISTS句を使用する方法
WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ... AND (... = department_id OR ... IS NULL))

パフォーマンス最適化

IN句を使用する際、パフォーマンスに影響を与える要因がいくつか存在する。

IN句の値の数に関する制限

IN句に指定できる値の数に明確な上限はないが、実用上の推奨最大値が存在する。

推奨最大値 : 約7,000個
これを超えるとパフォーマンスが大幅に低下する可能性がある。
技術的上限 : max_allowed_packet設定値に依存
デフォルトでは64MBまでのクエリサイズが許容される。

range_optimizer_max_mem_size

range_optimizer_max_mem_sizeは、範囲最適化に使用できるメモリの上限を制御するシステム変数である。

 # 現在の設定値を確認
 SHOW VARIABLES LIKE 'range_optimizer_max_mem_size';
 
 # デフォルト値: 8388608 (8MB)

デフォルト: 8[MB]
IN句に大量の値を指定すると、この制限を超える可能性がある。
制限超過時の動作: フルテーブルスキャンに切り替わる
約3万件程度のIN句でこの制限に達し、インデックスが使用されなくなる。
設定変更方法
SET SESSION range_optimizer_max_mem_size = 16777216; (16[MB]に増加)

eq_range_index_dive_limit

eq_range_index_dive_limitは、IN句の値の数がこの閾値を超えると、インデックスダイブが無効化される設定である。

 # 現在の設定値を確認
 SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
 
 # デフォルト値: 200

デフォルト : 200
IN句の値が200個を超えると、インデックス統計が使用される代わりに、簡易的な推定が行われる。
影響 : クエリ実行計画の精度が低下する可能性がある
ただし、プランニング時間は短縮される。
設定変更方法
SET SESSION eq_range_index_dive_limit = 500;

パフォーマンス改善のベストプラクティス

IN句を使用する時のパフォーマンス改善方法を以下に示す。

クエリの分割
IN句の値が数千件を超える場合、複数のクエリに分割して実行する。
JOINの使用
サブクエリのIN句は、JOIN句に書き換えることで高速化できる場合がある。
一時テーブルの活用
大量の値をIN句に指定する代わりに、一時テーブルに格納してJOINする。

 # 一時テーブルを使用した改善例
 CREATE TEMPORARY TABLE temp_ids (id INT);
 
 INSERT INTO temp_ids VALUES (1), (2), (3), ..., (10000);
 
 SELECT e.* FROM employees e
    INNER JOIN temp_ids t ON e.id = t.id;
 
 DROP TEMPORARY TABLE temp_ids;

インデックスの確認
IN句で使用するカラムに適切なインデックスが作成されているか確認する。

MySQL 8.0のセミジョイン最適化

MySQL 8.0では、IN句とEXISTS句を使用したサブクエリに対して、セミジョイン最適化が適用される。

セミジョイン最適化とは

セミジョイン最適化は、サブクエリを効率的に実行するための最適化技術である。

目的 : IN句やEXISTS句のパフォーマンスを大幅に向上させる
サブクエリを通常のJOINに変換して実行計画を最適化する。
MySQL 8.0.16以降の改善
セミジョイン最適化がさらに強化され、より多くのケースで適用されるようになった。
適用条件
サブクエリがWHERE句またはON句内のIN/EXISTS句で使用されている場合

サブクエリが相関サブクエリでない場合

セミジョイン戦略の種類

MySQLは、下表に示す4つのセミジョイン戦略を使用する。

セミジョイン最適化戦略
戦略	説明	使用場面
Materialization	サブクエリの結果を一時テーブルに格納し、それとJOINする。	サブクエリの結果セットが小さい場合に効果的。
FirstMatch	最初にマッチした行を返し、重複を排除する。	サブクエリの選択性が高い場合に有効。
LooseScan	インデックスの特定の値に対して最初の行のみをスキャンする。	サブクエリのカラムにインデックスがある場合に効率的。
DuplicateWeedout	重複する行を一時テーブルで除外する。	他の戦略が適用できない場合のフォールバック。

どの戦略が使用されているかは、EXPLAIN 文で確認することができる。

 # セミジョイン戦略の確認
 
 EXPLAIN FORMAT=TREE
 SELECT * FROM employees
    WHERE department_id IN (
       SELECT id FROM departments WHERE location = 'Tokyo'
    );

ヒント句による制御

MySQL 8.0では、セミジョイン最適化を制御するためのヒント句が提供されている。

 # セミジョイン最適化を有効化 (特定の戦略を指定)
 SELECT /*+ SEMIJOIN(MATERIALIZATION) */ * FROM employees
    WHERE department_id IN (
       SELECT id FROM departments WHERE location = 'Tokyo'
    );
 
 # セミジョイン最適化を無効化
 SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM employees
    WHERE department_id IN (
       SELECT /*+ QB_NAME(subq1) */ id FROM departments
          WHERE location = 'Tokyo'
    );
 
 # 複数の戦略を許可
 SELECT /*+ SEMIJOIN(MATERIALIZATION, FIRSTMATCH) */ * FROM orders
    WHERE customer_id IN (
       SELECT id FROM customers WHERE status = 'active'
    );

利用可能なヒント句を以下に示す。

SEMIJOIN(戦略名)
指定した戦略でセミジョイン最適化を適用する。

戦略名 : MATERIALIZATION, FIRSTMATCH, LOOSESCAN, DUPLICATEWEEDOUT
NO_SEMIJOIN
セミジョイン最適化を無効化する。

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を確認することにより、以下に示すことが分かる。

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

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

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

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