SQLのIN句は、指定した値の集合に含まれる行を取り出すための条件です。値の数が多いだけで必ず遅くなるわけではなく、インデックス、選択される行の割合、データ型、実行計画によって処理は変わります。まず結果の意味を保ち、そのうえで実測して改善するのが基本です。
本記事はMySQL 8.4／InnoDBを対象に、インデックス、サブクエリ、一時テーブル、パーティション、JOIN・EXISTSへの書き換えを比較します。SHOW INDEXやパーティション定義はMySQLの構文です。PostgreSQL、SQL Server、Oracle Databaseなどにそのまま適用せず、各製品・バージョンの実行計画や制限を確認してください。
通常の例では、employees.employee_idが主キー、employees.department_idがNULLを許すINT型、departments.idがINT型の主キー、departments.locationが文字列型であるとします。CREATE・INSERT・DROPの例は検証用です。既存の本番テーブルにそのまま実行しないでください。本稿は速度差を示すMySQLベンチマークや、MySQL実機で取得した実行計画を掲載していません。
最初に実行計画と処理全体の時間を確認する
同じデータと検索条件で、変更前の結果と計画を保存します。まずEXPLAINで計画を確認し、負荷を許容できる検証環境でのみEXPLAIN ANALYZEを使います。後者は対象のSELECTを実際に実行するため、大きなテーブルでは負荷が発生します。公式リファレンス：EXPLAIN。
EXPLAIN FORMAT=TRADITIONAL
SELECT employee_id, department_id
FROM employees
WHERE department_id IN (10, 20, 30);
-- 対象のSELECTを実行するため、検証環境で確認する
EXPLAIN ANALYZE FORMAT=TREE
SELECT employee_id, department_id
FROM employees
WHERE department_id IN (10, 20, 30);- 結果：行数だけでなく、主キーと行の重複回数が同じかを確認します。順序が必要ならORDER BYを明示します。
- 計画：アクセス方式、実際に選ばれた
key、推定行数を確認します。possible_keysは使用候補であり、使用した証拠ではありません。 - 実行：実行時の行数と
loops、時間を見ます。複数ループの時間はループ当たりの平均で、親ノードには子の処理時間が含まれるため、各ノードを単純合計しません。 - 全体時間：アプリ側のSQL生成・転送・結果取得、一時テーブルの準備と破棄も含めて測定します。代表的な件数と偏りで繰り返し、キャッシュ状態や同時実行負荷をそろえます。
読み方はEXPLAIN Output Formatも参照してください。小さなサンプルで結果が一致しても、本番相当の性能がよいとは限りません。
インデックスの活用
既存のインデックスを確認する
SHOW INDEX FROM employees;
SHOW INDEX FROM departments;INの対象列に利用可能なインデックスがあれば、複数の値への検索に使われることがあります。ただし、ほとんどの行が条件に一致する場合などは全表走査が合理的なこともあります。インデックスが存在することと、選ばれることは別です。公式リファレンス：インデックスの利用。
必要な場合だけ追加候補を検証する
-- 同等のインデックスがない場合の検証候補
CREATE INDEX idx_employees_department
ON employees (department_id);この追加は検索だけでなく、保存容量やINSERT・UPDATE・DELETEのコストにも影響します。既存の複合インデックスの先頭部分で代用できないか、変更の運用負荷も含めて判断します。たとえば(department_id, employee_id)はdepartment_idを先頭に持ちますが、列順を逆にした索引と同じ用途とは限りません。公式リファレンス：複合インデックス。
型を合わせる
数値IDは数値として、文字列コードは文字列として比較します。列同士の比較では型・サイズ・文字セット・照合順序も確認します。特に文字列列と数値の比較では暗黙変換により、意図しない一致や索引検索を使えない状況が生じます。検索列に安易にCASTをかけるのではなく、入力と列定義をそろえましょう。公式リファレンス：型変換。
-- department_idはINT型
SELECT employee_id FROM employees
WHERE department_id IN (10, 20, 30);
-- 別のテーブルでdepartment_codeが文字列型の場合
SELECT department_code FROM department_codes
WHERE department_code IN ('010', '020', '030');サブクエリの利用
対象IDがデータベース内にある場合
別テーブルの条件で絞り込むなら、IDをアプリへ取得して巨大な文字列に組み立てる前に、IN (SELECT ...)を検討します。これは「IN句の代わり」ではなく、IN句の右辺にサブクエリを使う書き方です。
SELECT e.employee_id, e.department_id
FROM employees AS e
WHERE e.department_id IN (
SELECT d.id
FROM departments AS d
WHERE d.location = 'New York'
);INとEXISTSは同じ最適化を受けることがある
MySQL 8.4では、条件を満たすIN／EXISTSサブクエリをセミジョインに変換できます。内側に一致する行が存在するかを調べ、外側の各行を一致数だけ増やさない処理です。FirstMatch、Duplicate Weedout、LooseScan、マテリアライズなどからコストに応じた戦略が選ばれるため、「INは常にサブクエリを行ごとに繰り返す」「EXISTSへ変えれば必ず速い」とは言えません。
変換には条件があります。代表例は、WHERE／ONの上位条件として使われること、サブクエリがUNIONを含まない単一SELECTであること、集約・HAVING・LIMITを含まないことです。すべてのサブクエリが変換対象ではないため、採用された計画を確認します。公式リファレンス：セミジョインとアンチジョイン。
SELECT e.employee_id, e.department_id
FROM employees AS e
WHERE EXISTS (
SELECT 1
FROM departments AS d
WHERE d.id = e.department_id
AND d.location = 'New York'
);上の2つは、この肯定形のWHERE条件では同じ従業員行を選びます。ただしINとEXISTSの式そのものが、NULLを含む場合まで同じ真理値を返すわけではありません。否定形への置き換えは後述のNULLの扱いを確認してください。
大量の値リストは件数・型・転送コストを見直す
リストの適切な長さに、環境を問わず通用する「何件から遅い」という境界はありません。重複したIDを入力側で取り除き、通常・最大・偏りのある入力で測定します。短いリストなら、そのままINを使う方法が最も単純なこともあります。
MySQLの定数INリストには、索引の等値範囲として評価されるケースがあります。値が増えると行数推定の負担が増え、range optimizerのメモリー上限に達した場合には、範囲アクセスの検討が打ち切られることもあります。遅いケースではEXPLAINの直後にSHOW WARNINGSを確認します。警告が出たからといって、最初からサーバー設定を変更するのではなく、入力サイズやID集合の渡し方を見直します。公式リファレンス：Range Optimization。
アプリから渡す値はパラメータ化してください。一般的なバインドではIN (?, ?, ?)のように値ごとのプレースホルダーを使い、IN (?)へ"10,20,30"という1個の文字列を渡しても3個の値には展開されません。配列展開の有無や上限はドライバー側も確認します。空の入力はアプリで「0件を返す」などの仕様を決め、MySQLで無効なIN ()を生成しないようにします。公式リファレンス：PREPARE。
テンポラリテーブルの活用
同じ大きなID集合を複数の検索で再利用する場合は、一時テーブルへ格納する方法も比較候補です。作成・投入・索引維持・検索・破棄を合わせた時間で比較し、検索だけを計時して有利と判断しないでください。1回だけの検索なら準備コストの方が大きい場合もあります。
型と一意性を明示したID集合を作る
次の例は、同じMySQLセッション内で実行する検証用です。元のdepartments.idが主キーなので、投入時のIDは重複しません。アプリからIDを投入する場合も重複を除き、NULLや範囲外の値を検証してください。CREATE TABLE ... AS SELECTだけでは意図した主キーや索引を備えるとは限らないため、ここでは先に定義します。公式リファレンス：CREATE TABLE … SELECT。
CREATE TEMPORARY TABLE in_tuning_department_ids (
id INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO in_tuning_department_ids (id)
SELECT id
FROM departments
WHERE location = 'New York';
SELECT e.employee_id, e.department_id
FROM employees AS e
JOIN in_tuning_department_ids AS t
ON t.id = e.department_id;
-- 同じID集合を使う処理を終えたら、この一時テーブルを削除
DROP TEMPORARY TABLE in_tuning_department_ids;主キーにより、上のJOINではID集合側の重複による行増加を防げます。接続プールで別の接続に切り替わると同じ一時テーブルは使えません。MySQLの一時テーブルはセッションに属し、接続終了で削除されますが、プールへ接続を返すだけでは終了しない場合があるため後片付けを管理します。また、IDの抽出後に元の条件が変わっても集合は自動更新されません。公式リファレンス：CREATE TEMPORARY TABLE。
INサブクエリの実行計画でも、MySQLが内部的に一時テーブルを使う場合があります。手作業の一時テーブルが、それより有利とは限りません。
パーティショニングの導入は最後に検討する
パーティショニングはテーブル設計・運用の変更です。IN句があるだけで速くなるわけではありません。条件から不要なパーティションを除外できるパーティションプルーニングが働くか、パーティション内の索引も適切かを確認します。日付で分割した表をcustomer_idだけで検索しても、通常は日付パーティションを絞れません。公式リファレンス：Partition Pruning。
日付範囲を欠落なく定義する例
以下は既存のordersを変更するコマンドではなく、別名の検証用テーブルの定義例です。過去年を受ける先頭パーティションと、将来の日付を受けるMAXVALUEを用意しています。上限までのパーティションしか作らないと、それ以降の日付を格納できません。公式リファレンス：RANGE COLUMNS。
CREATE TABLE in_tuning_orders_demo (
order_id BIGINT NOT NULL,
order_date DATE NOT NULL,
customer_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (order_id, order_date),
KEY idx_customer (customer_id)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (order_date) (
PARTITION p_before_2020 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p2021 VALUES LESS THAN ('2022-01-01'),
PARTITION p_future VALUES LESS THAN (MAXVALUE)
);MySQLでは、パーティション式で使う列をすべてのユニークキーに含める制約があります。この例の主キーは(order_id, order_date)であり、order_id単独の一意性は保証しません。既存の主キーを機械的に変更せず、一意性や外部キーなど業務上の制約を先に検討してください。公式リファレンス：パーティションキーとユニークキー。
EXPLAIN FORMAT=TRADITIONAL
SELECT order_id, order_date, customer_id, amount
FROM in_tuning_orders_demo
WHERE order_date >= '2020-01-01'
AND order_date < '2021-01-01'
AND customer_id IN (1, 2, 3);この例では日付条件がp2020の範囲に収まります。実環境ではEXPLAINのpartitions列を確認してください。HASHやLISTなども選択肢ですが、分割数や均等さだけでは高速化を判断できません。索引・クエリ・入力の見直しで改善できるなら、大きな設計変更を先に行う必要はありません。
クエリの再構成では結果の意味を守る
JOINの利用：右側が一意かを確認する
SELECT e.employee_id, e.department_id
FROM employees AS e
JOIN departments AS d ON d.id = e.department_id
WHERE d.location = 'New York';departments.idが主キーであれば、このJOINは先ほどのINと同じ従業員行を返します。一方、結合先に同じIDが2行あれば、そのIDに一致する各従業員行も2回ずつ返されます。INは右辺の同じ値が何回現れても一致回数を結果に増やしません。安易なSELECT DISTINCTの追加では、元から必要だった重複まで消える場合があります。存在確認が目的ならINまたはEXISTSが意図を表しやすい書き方です。
小さなデータで重複を確認する
次のSELECTはCTE内のデータだけを使い、実テーブルを作成・変更しません。候補IDに10が2回あるケースです。
WITH employees AS (
SELECT 1 AS employee_id, 10 AS department_id
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, NULL
UNION ALL SELECT 4, 30
UNION ALL SELECT 5, 10
), candidate_ids AS (
SELECT 10 AS id
UNION ALL SELECT 10
UNION ALL SELECT NULL
)
SELECT 'IN' AS method, e.employee_id
FROM employees AS e
WHERE e.department_id IN (SELECT id FROM candidate_ids)
UNION ALL
SELECT 'JOIN', e.employee_id
FROM employees AS e
JOIN candidate_ids AS c ON c.id = e.department_id
UNION ALL
SELECT 'EXISTS', e.employee_id
FROM employees AS e
WHERE EXISTS (
SELECT 1 FROM candidate_ids AS c
WHERE c.id = e.department_id
)
ORDER BY method, employee_id;論理的な結果は、INとEXISTSがそれぞれ従業員ID 1, 5、JOINが1, 1, 5, 5です。単純なJOINへの置き換えで、行数が変わることを確認できます。
NOT INとNULL：NOT EXISTSとは無条件に交換できない
SELECT
10 IN (10, NULL) AS matched,
20 IN (10, NULL) AS unmatched_in,
20 NOT IN (10, NULL) AS unmatched_not_in;結果は順に1, NULL, NULLです。右辺にNULLがあると、一致しなかった値について「含まれない」と確定できません。WHEREはTRUEの行だけを残すので、NULLになる行は選ばれません。公式リファレンス：IN／NOT INとNULL。
-- 「候補に一致せず、部門未設定でもない従業員」を選ぶ仕様
SELECT e.employee_id, e.department_id
FROM employees AS e
WHERE e.department_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM candidate_ids AS c
WHERE c.id = e.department_id
);上のcandidate_idsは候補IDの表を想定しています。CTE例のデータなら従業員ID 2, 4を選びます。IS NOT NULLを外すと、外側のdepartment_idがNULLである従業員ID 3も含まれます。対して元のNOT IN (SELECT id FROM candidate_ids)は、この候補にNULLがあるため0件です。これは同値な高速化ではなく、NULLをどう扱うかという仕様の違いです。
右辺が空集合の場合もテストしてください。NOT IN (空のサブクエリ)は外側の値がNULLでもTRUEになります。右辺からNULLを除くだけで十分か、外側のNULLを含めるかは用途によって異なります。公式リファレンス：ALLと空集合。
UNIONの利用：重複排除と分岐の重なりに注意する
-- 同じ部門の従業員が複数いれば、部門IDも複数行返る
SELECT department_id
FROM employees
WHERE department_id IN (10, 20);
-- UNIONは出力行の重複を除くため、上と同じとは限らない
SELECT department_id FROM employees WHERE department_id = 10
UNION
SELECT department_id FROM employees WHERE department_id = 20;CTE例のemployeesなら、最初は10が2行と20が1行、UNIONでは10と20が各1行です。UNION ALLは重複を残すので、このように互いに重ならない条件で分割すれば元の行数を保てますが、同じ行が複数の分岐に一致するとその分だけ増えます。公式リファレンス：集合演算とALL／DISTINCT。
単純なINを値ごとにUNIONへ展開しても、表へのアクセスや重複排除のコストが増える場合があります。分岐ごとに異なる索引を使えるなど、実行計画上の根拠があるときに、結果の重複を含めて比較しましょう。
まとめ
- まずMySQLのバージョン、結果の意味、実行計画、アプリを含む全体時間を確認する。
- 索引は型・列順・選択性・更新コストまで検討し、「作れば速い」と決めつけない。
- 対象IDがDB内にあるならINサブクエリを候補にし、EXISTSと同じ最適化を受ける可能性も見る。
- 大きなID集合は入力サイズを測り、再利用する一時テーブルは準備・後片付けを含めて比較する。
- JOINの重複、NOT INのNULL、UNIONの重複排除を確認してから書き換える。
- パーティショニングはプルーニングと制約が合う場合に検討し、IN句の万能な高速化策としない。
改善策を1つずつ変更し、正しさと本番相当の負荷での効果を確認してから採用してください。
コメント