MySQL - INのソースを表示

== 概要 ==
IN句は、MySQLにおいて指定した値のリストまたはサブクエリの結果に対して、カラムの値が含まれるかを判定するための演算子である。<br>
<br>
複数のOR条件を簡潔に記述できるため、コードの可読性が向上し、特に多数の値を比較する場合に有用である。<br>
例えば、<u>WHERE id = 1 OR id = 2 OR id = 3</u> という冗長な条件式を、<u>WHERE id IN (1, 2, 3)</u> と簡潔に記述できる。<br>
<br>
IN句は、WHERE句やHAVING句と組み合わせて使用され、データの絞り込みやフィルタリングに広く用いられる。<br>
また、サブクエリと組み合わせることにより、別テーブルの検索結果を条件として動的に指定することも可能である。<br>
<br>
IN句の主な使用シーンを以下に示す。<br>
<br>
* 特定のIDリストに該当するレコードの取得
*: ユーザーが選択した複数の商品IDや注文IDに基づくデータ抽出
*: <br>
* カテゴリやステータスによるフィルタリング
*: 複数のカテゴリに属する商品や、特定のステータスを持つ注文の検索
*: <br>
* 他テーブルとの関連データの取得
*: サブクエリを使用して、関連テーブルに存在するレコードのみを抽出
*: <br>
* バッチ処理における対象データの指定
*: 処理対象となる複数のレコードを一度に指定
<br>
IN句を使用する場合は、パフォーマンスへの影響を考慮する必要がある。<br>
特に、IN句に指定する値が数千件を超える場合や、サブクエリが大量のデータを返す場合は、クエリの実行時間が大幅に増加する可能性がある。<br>
<br>
MySQL 8.0以降では、セミジョイン最適化によりIN句を含むサブクエリのパフォーマンスが改善されているが、<br>
大量データを扱う場合は、JOINへの書き換えや一時テーブルの活用を検討することを推奨する。<br>
<br><br>

== IN句の基本構文 ==
IN句は、指定したリスト内に値が存在するかを判定する。<br>
<br>
==== リスト値の指定 ====
最も基本的な使用方法は、カッコ内に値のリストを直接指定する形式である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 基本構文
 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');
 </syntaxhighlight>
<br>
上記のIN句は、以下のOR条件と同等である。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT * FROM employees
    WHERE department_id = 10
       OR department_id = 20
       OR department_id = 30;
 </syntaxhighlight>
<br>
==== サブクエリとの組み合わせ ====
IN句は、サブクエリの結果セットに対しても使用できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # サブクエリを使用した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'
    );
 </syntaxhighlight>
<br>
==== 複数カラムでのIN句 ====
MySQL 5.7以降では、<code>Row Constructor</code> を使用して複数カラムの組み合わせでIN句を使用できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 複数カラムの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()
    );
 </syntaxhighlight>
<br><br>

== NOT IN句 ==
NOT IN句は、指定したリスト内に値が存在しないことを判定する。<br>
<br>
==== 基本構文 ====
NOT IN句の基本的な使用方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 基本構文
 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'
    );
 </syntaxhighlight>
<br><br>

==== NULLに関する注意点 ====
NOT IN句を使用する場合、リスト内にNULLが含まれると予期しない結果になる可能性がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 # NULLが含まれる場合の問題
 SELECT * FROM employees
    WHERE department_id NOT IN (10, 20, NULL);
 
 # 結果 : 0件 (NULLの影響で全ての行が除外される)
 </syntaxhighlight>
<br>
これは、SQLの3値論理 (TRUE, FALSE, UNKNOWN) に基づく動作である。<br>
NULLとの比較は常にUNKNOWNを返すため、NOT IN句全体がFALSEまたはUNKNOWNとなり、結果が0件になる。<br>
<br>
<u>※対策方法</u><br>
* IS NULL条件を併用する方法
*: <code>WHERE department_id NOT IN (...) OR department_id IS NULL</code>
*: <br>
* IFNULL関数またはCOALESCE関数を使用する方法
*: <code>WHERE IFNULL(department_id, -1) NOT IN (...)</code>
*: <br>
* NOT EXISTS句を使用する方法
*: <code>WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ... AND (... = department_id OR ... IS NULL))</code>
<br><br>

== パフォーマンス最適化 ==
IN句を使用する際、パフォーマンスに影響を与える要因がいくつか存在する。<br>
<br>
==== IN句の値の数に関する制限 ====
IN句に指定できる値の数に明確な上限はないが、実用上の推奨最大値が存在する。<br>
<br>
* 推奨最大値 : 約7,000個
*: これを超えるとパフォーマンスが大幅に低下する可能性がある。
*: <br>
* 技術的上限 : max_allowed_packet設定値に依存
*: デフォルトでは64MBまでのクエリサイズが許容される。
<br>
==== range_optimizer_max_mem_size ====
range_optimizer_max_mem_sizeは、範囲最適化に使用できるメモリの上限を制御するシステム変数である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 現在の設定値を確認
 SHOW VARIABLES LIKE 'range_optimizer_max_mem_size';
 
 # デフォルト値: 8388608 (8MB)
 </syntaxhighlight>
<br>
* デフォルト: 8[MB]
*: IN句に大量の値を指定すると、この制限を超える可能性がある。
*: <br>
* 制限超過時の動作: フルテーブルスキャンに切り替わる
*: 約3万件程度のIN句でこの制限に達し、インデックスが使用されなくなる。
*: <br>
* 設定変更方法
*: <code>SET SESSION range_optimizer_max_mem_size = 16777216;</code> (16[MB]に増加)
<br><br>

==== eq_range_index_dive_limit ====
eq_range_index_dive_limitは、IN句の値の数がこの閾値を超えると、インデックスダイブが無効化される設定である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 現在の設定値を確認
 SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
 
 # デフォルト値: 200
 </syntaxhighlight>
<br>
* デフォルト : 200
*: IN句の値が200個を超えると、インデックス統計が使用される代わりに、簡易的な推定が行われる。
*: <br>
* 影響 : クエリ実行計画の精度が低下する可能性がある
*: ただし、プランニング時間は短縮される。
*: <br>
* 設定変更方法
*: <code>SET SESSION eq_range_index_dive_limit = 500;</code>
<br>
==== パフォーマンス改善のベストプラクティス ====
IN句を使用する時のパフォーマンス改善方法を以下に示す。<br>
<br>
* クエリの分割
*: IN句の値が数千件を超える場合、複数のクエリに分割して実行する。
*: <br>
* JOINの使用
*: サブクエリのIN句は、JOIN句に書き換えることで高速化できる場合がある。
*: <br>
* 一時テーブルの活用
*: 大量の値をIN句に指定する代わりに、一時テーブルに格納してJOINする。
<br>
 <syntaxhighlight lang="mysql">
 # 一時テーブルを使用した改善例
 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;
 </syntaxhighlight>
<br>
* インデックスの確認
*: IN句で使用するカラムに適切なインデックスが作成されているか確認する。
<br><br>

== MySQL 8.0のセミジョイン最適化 ==
MySQL 8.0では、IN句とEXISTS句を使用したサブクエリに対して、セミジョイン最適化が適用される。<br>
<br>
==== セミジョイン最適化とは ====
セミジョイン最適化は、サブクエリを効率的に実行するための最適化技術である。<br>
<br>
* 目的 : IN句やEXISTS句のパフォーマンスを大幅に向上させる
*: サブクエリを通常のJOINに変換して実行計画を最適化する。
*: <br>
* MySQL 8.0.16以降の改善
*: セミジョイン最適化がさらに強化され、より多くのケースで適用されるようになった。
*: <br>
* 適用条件
*: サブクエリがWHERE句またはON句内のIN/EXISTS句で使用されている場合
*: サブクエリが相関サブクエリでない場合
<br>
==== セミジョイン戦略の種類 ====
MySQLは、下表に示す4つのセミジョイン戦略を使用する。<br>
<br>
<center>
{| class="wikitable"
|+ セミジョイン最適化戦略
! 戦略 !! 説明 !! 使用場面
|-
| Materialization || サブクエリの結果を一時テーブルに格納し、それとJOINする。 || サブクエリの結果セットが小さい場合に効果的。
|-
| FirstMatch || 最初にマッチした行を返し、重複を排除する。 || サブクエリの選択性が高い場合に有効。
|-
| LooseScan || インデックスの特定の値に対して最初の行のみをスキャンする。 || サブクエリのカラムにインデックスがある場合に効率的。
|-
| DuplicateWeedout || 重複する行を一時テーブルで除外する。 || 他の戦略が適用できない場合のフォールバック。
|}
</center>
<br>
どの戦略が使用されているかは、<code>EXPLAIN</code> 文で確認することができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # セミジョイン戦略の確認
 
 EXPLAIN FORMAT=TREE
 SELECT * FROM employees
    WHERE department_id IN (
       SELECT id FROM departments WHERE location = 'Tokyo'
    );
 </syntaxhighlight>
<br>
==== ヒント句による制御 ====
MySQL 8.0では、セミジョイン最適化を制御するためのヒント句が提供されている。<br>
<br>
 <syntaxhighlight lang="mysql">
 # セミジョイン最適化を有効化 (特定の戦略を指定)
 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'
    );
 </syntaxhighlight>
<br>
利用可能なヒント句を以下に示す。<br>
* SEMIJOIN(戦略名)
*: 指定した戦略でセミジョイン最適化を適用する。
*: 戦略名 : MATERIALIZATION, FIRSTMATCH, LOOSESCAN, DUPLICATEWEEDOUT
*: <br>
* NO_SEMIJOIN
*: セミジョイン最適化を無効化する。
<br><br>

== IN句とEXISTS句の違い ==
パフォーマンスは、データベースエンジンの最適化機能に依存する。<br>
また、インデックスの有無や統計情報によって実行計画が変わる可能性がある。<br>
<br>
また、多くのデータベースエンジンでは、IN句とEXISTS句を内部的に相互に変換することがある。<br>
<br>
==== IN句 ====
サブクエリの結果を全て評価 (全走査) する。<br>
<br>
サブクエリの結果をメモリ上に一時テーブルとして保持する必要がある。<br>
そのため、サブクエリの結果セットが小さい場合に効率的である。<br>
<br>
 <syntaxhighlight lang="tsql">
 /* IN句の場合 */
 SELECT *
 FROM orders 
 WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Korea')
 </syntaxhighlight>
<br>
==== EXISTS句 ====
条件に一致するレコードが見つかった時点で評価を終了する。(Semi-join)<br>
<br>
結果セットをメモリに保持する必要がない。<br>
そのため、サブクエリの結果が大きい場合に効率的である。<br>
<br>
 <syntaxhighlight lang="tsql">
 /* EXISTS句の場合 */
 SELECT *
 FROM orders o 
 WHERE EXISTS (SELECT 1 FROM customers c 
               WHERE c.id = o.customer_id AND c.country = 'Japan')
 </syntaxhighlight>
<br>
==== EXPLAIN PLAN (実行計画) ====
EXPLAIN PLANは、クエリがどのように実行されるかを確認するための機能である。<br>
<br>
実行計画で確認できる主な情報を、以下に示す。<br>
* テーブルスキャンの方法
*: フルテーブルスキャン (全件検索)
*: インデックススキャン
*: インデックスオンリースキャン
<br>
* 結合方式
*: ネステッドループ結合
*: ハッシュ結合
*: マージ結合
<br>
* コスト情報
*: 実行にかかる予想時間
*: 必要なメモリ量
*: 処理対象の予想行数
<br>
 <syntaxhighlight lang="mysql">
 -- 基本的な記述
 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;
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="mysql">
 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|
 +----+-------------+-------+------------+-------+---------------+
 </syntaxhighlight>
<br>
EXPLAIN PLANの結果の主な項目を、以下に示す。<br>
* id
*: SELECT文の識別子
*: JOINの順序を示す
* select_type
** SIMPLE
**: 単純なSELECT
** PRIMARY
**: サブクエリのある外部クエリ
** SUBQUERY
**: サブクエリ
** DERIVED
**: FROM句内の導出テーブル
* table
*: 対象のテーブル名
* type (アクセス方法、左に行くほど効率的)
** system
**: テーブルに1行のみ
** const
**: 主キーで1件のみ取得
** eq_ref
**: ユニークインデックス参照
** ref
**: 非ユニークインデックス参照
** range
**: インデックス範囲検索
** index
**: インデックスフルスキャン
** ALL
**: フルテーブルスキャン
**: <u>特に、type=ALL (フルテーブルスキャン) が出力されているかどうかを確認する。</u>
* possible_keys
*: 利用可能なインデックス
* key
*: 実際に使用されるインデックス
* rows
*: 処理が必要な推定行数
*: <u>特に、rowsが想定より多くないかどうかを確認する。</u>
* filtered
*: フィルタ条件で絞られる行の割合
<br>
EXPLAIN PLANを確認することにより、以下に示すことが分かる。<br>
* クエリが非効率な場合の原因特定
* インデックスの適切な使用
* 予想される処理時間やリソース使用量
* パフォーマンスチューニングの必要性
<br>
そのため、特に以下に示すような場合に、実行計画の確認が重要である。<br>
* クエリの実行が遅い場合
* 大量のデータを扱う場合
* 複雑な結合を含むクエリが存在する場合
* インデックスの追加や変更を検討する場合
<br>
<u>特に、適切なインデックスの使用、または、結合順序の最適化等を確認してパフォーマンスチューニングを行う。</u><br>
<br><br>

== 関連情報 ==
* [https://dev.mysql.com/doc/refman/8.0/ja/comparison-operators.html#operator_in MySQL 8.0 リファレンスマニュアル - IN演算子]
* [https://dev.mysql.com/doc/refman/8.0/ja/subquery-optimization-with-semijoin.html MySQL 8.0 リファレンスマニュアル - セミジョイン変換による IN および EXISTS サブクエリ述語の最適化]
* [https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html MySQL 8.0 リファレンスマニュアル - オプティマイザヒント]
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]