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

概要 MySQLでIN句を使用する時はIN句に渡す要素数に注意する必要があるとよく先輩エンジニアの方から聞いていたのですが、実際に大量の要素を渡すと何がまずいのかはっきり分かっていなかったので調べてみました。 この記事で伝えたいこと MySQLでIn句に大量の要素を渡すとまずい理由 まずい状況を回避するために気をつけるべきポイント 先に結論 MySQLでIN句に大量の要素を渡すとインデックスを貼っていたカラムだとしてもフルスキャンが発生しスロークエリになる可能性があります。 フルスキャンが発生してしまう条件はテーブルに設定してあるインデックスの内容とrange_optimizer_max_mem_size の設定値に依存しており、MySQL8でデフォルトの設定値 & シンプルなテーブルであってもおおよそ数万件の要素数をIN句に渡すとフルスキャンが発生する可能性があると考えられます。 検証環

[asakura-t]

「数万件の要素数をIN句に渡す」のは設計がおかしい気もするけれど／↑MySQLならdatabaseが別でもJOINできたような＞https://qiita.com/EastResident/items/cf556b7005e60a4ee19f ／tempテーブルでJOIN使うのが本筋？（制限あるかもだけど）

[chikurou]

OracleかPostgreSQLか忘れたけどIN句に渡せる要素数最大1000だったなー

[ton-boo]

MySQLは久しく使ってないけどTempテーブルかせめてCTE使ってjoinの形にした方が駆動表がわかりやすくなってDBに優しいクエリになりそうな気がする感覚的に

[hase0510]

結論に書いてる対処はどちらも微妙じゃないかな......2つの集合の共通成分が高速に得られればいいわけだけど、一方の集合がDBにあってもう一方がクエリ文字列だというのは筋が悪そうだな〜という感覚がある。

[murasuke]

数万件のor条件と等価だから、そもそもコスト的にIndex使って早くなるのか？と思う（１億レコードとかあればだけども）

[atodeyomuyomu]

サイトのデザインがいい

[jiro68]

そもそもこの表定義とSQLが合ってない。多分, number 列が id 列なのだろうけど。優秀なコストベースオプティマイザなら列値のヒストグラムを見て選択率によってプランを変えそう。MySQLはそこまではやってくれないけど。

[PrivateIntMain]

そういう仕組みがあるのは参考になるけど、ピンポイントで数万件検索とかどういう要件で実現しうるのか不明すぎる。先にidで件数だけ調べたにしても結局まとめてロードするなら先に調べる意味無いし。

[sinoh]

数万件の要素数をIN句に渡すと←渡し杉ぃい

[xlc]

INじゃなくてEXISTSを使えという話かと思ったら違った。フィルター的な操作は全件検索になるのが当たり前なのでポイントを外してると思うよ。パーティションを使うなどが解になるのでは？

[strawberryhunter]

性能を考慮すると思ったよりもIN (...)に渡せる個数が少ない（たった17件?）のは意外だった。←テスト用の設定で誤解だった。

[daira4000]

結合用のテーブル用意するのがよさそう？

[oakbow]

そもそもサブクエリ使えばいいのに案件だしOracleだとエラーになるかな。IN句の中身のID列っぽいものを操作しているアプリケーションサーバ側でもメモリ消費が大きくなりすぎると思う

[knok]

巨大なクエリを投げてパース指せること自体のコストは気にならないのかな

[htn_sui]

昔IN句の上限が1000個のRDBMSってなかったけ？Oracleだったかな？

[leiqunni]

話はちがうけど、フルスキャンが頻繁に起こるなら自動でインデックスはってくれへんやろか。

[oktnzm]

数万件てｗ 元データ自体DBにありそうな雰囲気だが…

[threetea0407]

select の結果を IN に食わせたりするとまれによく発生する。複数 DB を取り扱うようなサービスだと join できないからこうなっちゃうんよね。。。