📝

MySQLでIn句に大量の要素を渡すとまずい理由

2023/01/21に公開

MySQL

tech

概要

MySQLでIN句を使用する時はIN句に渡す要素数に注意する必要があるとよく先輩エンジニアの方から聞いていたのですが、実際に大量の要素を渡すと何がまずいのかはっきり分かっていなかったので調べてみました。

この記事で伝えたいこと

MySQLでIn句に大量の要素を渡すとまずい理由
まずい状況を回避するために気をつけるべきポイント

先に結論

MySQLでIN句に大量の要素を渡すとインデックスを貼っていたカラムだとしてもフルスキャンが発生しスロークエリになる可能性があります。
フルスキャンが発生してしまう条件はテーブルに設定してあるインデックスの内容とrange_optimizer_max_mem_size の設定値に依存しており、MySQL8でデフォルトの設定値 & シンプルなテーブルであってもおおよそ数万件の要素数をIN句に渡すとフルスキャンが発生する可能性があると考えられます。

検証環境

mysql Ver 8.0.31 for macos10.15 on x86_64 (Homebrew)

まずいケースの確認

今回は検証用としてレストランの情報を管理するテーブルとしてrestraurantsテーブルを作成しました。(検証用なので、かなりシンプル & ツッコミどころはありますが)
検証の値がより分かりやすくなるように1000万件のレコードを持つものとします。

カラム名	型	補足
id	Int	プライマリーキー
name	Char	店舗名
prefecture_id	Int	都道府県毎に設定した1~47のid
category_id	Int	400種類に分類したカテゴリー

まずは要素数10個ほどのIN句をExplainで確認してみます。
この場合は通常通り type = range での検索が使われるようになっており、パフォーマンス的には恐らく問題がないと思います。

mysql> explain select * from restaurants where number in (1,3,5,7,9,11,13,15,17,19);
+----+-------------+-------------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+-------------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | restaurants | NULL       | range | number        | number | 4       | NULL | 475842 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+

では、例えばここで約3万件のIN句を渡してみると、index は使われずにフルスキャンになっていました。

$ explain select * from restaurants where number in (1,3,5....60000);

id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  restaurants     NULL    ALL     number  NULL    NULL    NULL    9809034 50.00   Using where

原因を調査

ある程度想像はつきますが、やはり多くの要素数を渡すとパフォーマンス的には問題がありそうです。公式ドキュメントを調べると、次のような記述がありました。

範囲オプティマイザで使用可能なメモリーを制御するには、range_optimizer_max_mem_size システム変数を使用します:
値 0 は「制限なし」を表します。
0 より大きい値を指定すると、オプティマイザは範囲アクセス方法を考慮する際に消費されるメモリーを追跡します。指定した制限を超えると、レンジアクセス方法が破棄され、全テーブルスキャンなどの他の方法がかわりに考慮されます。

つまり、range による検索で使用するメモリ量が range_optimizer_max_mem_size を超えると range が使えずにフルスキャンなどになってしまうようです。
MySQL8だとrange_optimizer_max_mem_sizeのデフォルト値は8388608に設定されており、シンプルな検索だとIN句に渡す要素数1つあたり約230バイトが消費されると書いてあるので、要素数が数万件あたりからフルスキャンになるということだと考えられます。

確認

では実際に range_optimizer_max_mem_size を超えたタイミングでフルスキャンに切り替わるのか設定値を変えてみることで検証したいと思います。
要素数をそこまで渡さずとも検証できるように、range_optimizer_max_mem_sizeはだいぶ低めに設定してみました。

mysql> SET range_optimizer_max_mem_size=10000;
# 要素数が17個の場合
mysql> explain select * from restaurants where number in (1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33);
+----+-------------+-------------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+-------------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | restaurants | NULL       | range | number        | number | 4       | NULL | 809612 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+

# 要素数が18個の場合
mysql> explain select * from restaurants where number in (1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35);
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | restaurants | NULL       | ALL  | number        | NULL | NULL    | NULL | 9809034 |    50.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

今回だと要素数が17個まではrangeが使われていましたが18個からフルスキャンになっており、ドキュメントに書いてあった通り、range_optimizer_max_mem_sizeを超えたタイミングで検索方法が変わったため、スロークエリになってしまいました！
(localで試した結果データ取得に17個だと約3秒,18個だと約9秒かかりました。)

要素数1つあたり約230バイトの計算値とはだいぶ乖離がありますが、この辺りはテーブルに設定されているインデックスの内容やデフォルトで消費するメモリ量なども関係がありそうです。

結論

MySQLでIN句に大量の要素を渡した場合消費されるメモリがrange_optimizer_max_mem_sizeを超えてしまうと、rangeアクセスが破棄されスロークエリになる可能性が高いです。
IN句を利用する必要がある場合は下記などの対応を検討すると必要がありそうです。

range_optimizer_max_mem_size の設定値を見直す
複数回のSQLに分割し1回のSQLでIN句に渡す要素数を抑える