技術資料

同じカラムについて「IDが10、20、30のどれか」という条件を書くとき、id = 10 OR id = 20 OR id = 30と並べる方法と、id IN (10, 20, 30)にまとめる方法があります。

書きやすさ、見やすさではINの方が良さそうですね。では、検索速度にも違いがあるのでしょうか。今回はMySQLを対象に、書き換えられる条件と、実行計画・時間の比較を整理してみます。

先に結果を書くと、今回の検証では主キーの候補が3個ならほぼ同程度、1000個ではINの方が速くなりました。同じカラムの等値条件はINで読みやすくまとめつつ、速度は実際の条件で確かめるのがよさそうです。

同じカラムの等値条件ならINでまとめる

INは、左側の値がリスト中のいずれかの値と等しいかを調べます。例えば、次の2つは同じ商品を検索するSQLです。

SELECT id, price
FROM products
WHERE id = 10 OR id = 20 OR id = 30;

SELECT id, price
FROM products
WHERE id IN (10, 20, 30);

INにすると、カラム名と等号を繰り返さずに済みます。「この候補のどれか」という意図も読み取りやすいので、同じカラムへの等値条件ならINを使うのがよいかと思います。値はカラムに合う型でそろえ、数値と文字列をむやみに混在させないようにします。

一方、category_id = 10 OR price < 100のように別々のカラム・条件を組み合わせるORは、この形のINへ単純に置き換えられません。今回は、サブクエリを使うINやNOT INの最適化は扱いません。

ANDと組み合わせる場合は括弧に注意

「公開中の商品から、IDが10・20・30のどれか」を探すなら、OR側を括弧でまとめます。ここではactive = 1を公開中としています。

SELECT id, price
FROM products
WHERE active = 1 AND (id = 10 OR id = 20 OR id = 30);

INなら次のように書けます。

SELECT id, price
FROM products
WHERE active = 1 AND id IN (10, 20, 30);

ORの括弧を外した次のSQLは、意味が変わります。

SELECT id, price
FROM products
WHERE active = 1 AND id = 10 OR id = 20 OR id = 30;

MySQLではANDがORより優先されるので、「公開中でIDが10、またはIDが20、またはIDが30」という条件になります。IDが20・30の商品は非公開でも対象です。速度を比較する前に、同じ結果になる条件かを確認しておきたいところです。優先順位はMySQL公式のOperator Precedenceで確認できます。

NULLと空のリストは別に考える

リストにNULLを入れても、NULLの行を検索する指定にはなりません。次の結果は、左から1、NULL、NULLです。

SELECT
    10 IN (10, NULL) AS matched,
    20 IN (10, NULL) AS unmatched,
    NULL IN (10, NULL) AS null_value;

WHERE句で対象になるのは条件が真の行です。NULLの行も含めたい場合は、例えばcategory_id IN (10, 20) OR category_id IS NULLのようにIS NULLを組み合わせます。また、候補が0件のときにIN ()という空のリストは書けません。アプリケーション側で検索を省略して0件を返すなど、扱いを決めておきます。

型やNULLを含む比較の詳細は、MySQL公式のComparison Functions and Operatorsを参照してください。アプリケーションから候補を渡す場合は、値をSQL文字列へ直接連結せず、候補数に応じたプレースホルダーとバインドを使います。

INにすれば速くなるとは限らない

MySQL公式のRange Optimizationでは、インデックス付きの同じカラムについて、col IN (値1, 値2, ...)とcol = 値1 OR col = 値2 OR ...を、どちらも等値範囲比較として説明しています。

つまり、ORと書いただけでテーブル全体を読むとは限らず、INへ書き換えただけで必ず速くなるわけでもありません。ただし、同じインデックスを使う計画でも、SQLの解析・最適化や条件の評価などまで同じ時間になるとは限りません。実行計画と実測の両方を見てみます。

10万件のデータで比較する

Docker DesktopのLinuxコンテナ上で、MySQL 8.4.13・InnoDB、PHP 8.3.36・PDO_MYSQL（mysqlnd）を使って確認しました。Linux x86_64環境で、DB側はCPU上限2コア・メモリ上限1GiB、PHP側はCPU上限1コア・メモリ上限256MiB、InnoDBバッファプールは128MiBです。DBとPHPはUnixソケットで接続しています。

以下は、テーブルのない専用の検証用DBを選択して実行するSQLです。productsとデータ生成用の補助テーブルdigitsを作り、10万件を投入します。既存の商品テーブルへ投入するものではありません。

主キーのidに加えて、同じ値を持つ行が複数あるcategory_idにもインデックスを付けます。取得するpriceはcategory_idのインデックスには含めません。

CREATE TABLE products (
    id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NULL,
    active TINYINT UNSIGNED NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_category_id (category_id)
) ENGINE=InnoDB;

CREATE TABLE digits (n INT NOT NULL PRIMARY KEY);
INSERT INTO digits (n) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO products (id, category_id, active, price)
SELECT n, MOD(n, 1000), IF(MOD(n, 3) = 0, 0, 1),
       100 + MOD(n, 10000) / 100
FROM (
    SELECT 1 + a.n + 10*b.n + 100*c.n + 1000*d.n + 10000*e.n AS n
    FROM digits AS a
    CROSS JOIN digits AS b
    CROSS JOIN digits AS c
    CROSS JOIN digits AS d
    CROSS JOIN digits AS e
) AS numbers;

ANALYZE TABLE products;

idは1〜100000、category_idは0〜999で各100件です。activeは3の倍数のIDだけ0にしています。先ほどの括弧の例では、括弧ありとINはIDが10・20の2件、括弧なしは非公開の30を含む3件になり、違いを確認できます。

まずは実行計画を比べる

検索対象と取得列をそろえて、両方にEXPLAINを付けます。

EXPLAIN FORMAT=TRADITIONAL
SELECT id, price FROM products
WHERE id = 10 OR id = 20 OR id = 30;

EXPLAIN FORMAT=TRADITIONAL
SELECT id, price FROM products
WHERE id IN (10, 20, 30);

実行計画の読み方は、MySQLのEXPLAIN入門で整理しています。今回はtype・key・rows・Extraを比べ、使うインデックスや候補行数に違いがあるかを確認します。rowsは推定値なので、処理時間そのものではありません。

今回確認した計画は次のとおりです。表内の各項目は、ORとINで同じでした。

同じ条件で複数回測る

計測にはPHPのPDO_MYSQLを使います。PDO_MYSQLが有効なPHPのCLI環境で、専用DBへ接続済みの$pdoを用意して実行します。比較するカラム名と値は検証用の固定値で、外部からの入力をそのまま渡すコードではありません。

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

$column = 'id';
$values = [10, 20, 30];
$queries = [
    'OR' => 'SELECT id, price FROM products WHERE '
        . implode(' OR ', array_map(fn($n) => "$column = $n", $values)),
    'IN' => "SELECT id, price FROM products WHERE $column IN ("
        . implode(', ', $values) . ')',
];

// 比較は計測の外で行う。ORDER BYなしの返却順には依存しない。
$results = [];
foreach ($queries as $name => $sql) {
    $rows = $pdo->query($sql)->fetchAll();
    usort($rows, fn($a, $b) => $a[0] <=> $b[0]);
    $results[$name] = $rows;
}
if ($results['OR'] !== $results['IN']) {
    throw new RuntimeException('検索結果が一致しません。');
}

// 各形式を5回ずつ実行してから、計測を始める。
foreach ($queries as $sql) {
    for ($i = 0; $i < 5; $i++) {
        $pdo->query($sql)->fetchAll();
    }
}

$samples = ['OR' => [], 'IN' => []];
for ($i = 0; $i < 30; $i++) {
    $order = $i % 2 === 0 ? ['OR', 'IN'] : ['IN', 'OR'];
    foreach ($order as $name) {
        $start = hrtime(true);
        $rows = $pdo->query($queries[$name])->fetchAll();
        $samples[$name][] = (hrtime(true) - $start) / 1_000_000;
        unset($rows);
    }
}

foreach ($samples as $name => $times) {
    sort($times, SORT_NUMERIC);
    $median = ($times[14] + $times[15]) / 2;
    printf("%s: 中央値 %.3f ms\n", $name, $median);
}

接続とSQL文字列の作成は計測に含めず、query()の開始からfetchAll()で全件を取得するまでを測ります。DBだけの処理時間ではなく、通信やPDOでの結果取得も含む時間です。SQL文字列を毎回送っており、プリペアドステートメントの比較ではありません。

主キーはrange(10, 30, 10)、range(10, 1000, 10)、range(10, 10000, 10)の3・100・1000値で比較します。category_idは$column = 'category_id'にして、range(1, 3)、range(1, 30)の3・30値で比較します。どのケースも取得する列はidとpriceのまま、ORDER BYとLIMITは付けません。

比較結果

各形式5回のウォームアップ後、先に実行する形式を交互に変えて30回ずつ測った中央値です。全ケースで、ORとINの検索結果が一致することも確認しています。

主キーの候補が3個のケースでは、ごくわずかな差でした。一方、1000個ではORが約12ms、INが約1.9msです。どちらもPRIMARYを使うrangeの計画ですが、時間には差がありました。実行計画のtypeやkeyが同じなら、速さも同じ、とは言えないですね。

ただし、この計測だけでは、差がSQLの解析・最適化・条件評価などのどこで生じたかまでは分かりません。また、表は繰り返し実行した環境での値で、初回の時間やコールドキャッシュの性能を示すものではありません。小さな差を含め、この数値を他のデータや環境へそのまま当てはめられるとは限りません。

読みやすさを選び、速度は条件をそろえて確かめる

同じカラムに対する複数の等値条件なら、INでまとめると意図が読み取りやすくなります。一方、検索速度はORかINかという名前だけでは決められません。比較値の件数、インデックス、取得列、実際に返る件数も含めて確認する必要があります。

特に候補が大量になる場合は、公式資料でも行数見積もりや範囲最適化のメモリ制限について説明されています。INへ書き換えるだけで済ませず、実際の候補数で計画と時間を調べるのがよさそうです。今回の比較はMySQLの特定の環境・データによるもので、別のDBや本番の処理時間を保証するものではありません。

参考資料

• MySQL 8.4 Reference Manual: Range Optimization
• MySQL 8.4 Reference Manual: Comparison Functions and Operators
• MySQL 8.4 Reference Manual: Operator Precedence
• MySQL 8.4 Reference Manual: EXPLAIN Statement
• PHPマニュアル: hrtime
• PHPマニュアル: PDO::query
• PHPマニュアル: PDOStatement::fetchAll