MySQLでIN句の中に大量の値の入ったクエリがフルスキャンを起こす話 - freee Developers Hub

こんにちは、freee Developers Advent Calendar 2021、19日目のid:shallow1729です。昨日はtdtdsさんで【マジで】サイバー演習シナリオの作り方【怖い】でした！障害訓練後に攻撃方法を解説された時はリアリティの高さに驚きました。 僕はMySQLを使っていて発生した不思議な挙動の調査の話をしようと思います。 今回問題となったクエリ 今回話題にするクエリは以下のようなシンプルなものです。 SELECT * FROM hoge WHERE id IN (...) MySQLのパラメーター次第ですが、デフォルトの設定だとこのIN句の中の値の数が数万になると適切なインデックスが用意されていてもフルスキャンが発生する事がありました。このクエリがテーブルのほとんどのレコードを網羅するような場合や高速でレコードを大量にinsertして統計情報が追いつかないケー

[shallow1729]

書きました！シンプルなクエリでも想定外にフルスキャンを起こす事がある話です

[tettekete37564]

これね前任者 INどころかが数千数万のIDをORで繋いでプレースホルダにぶっこむという暴挙やっててね。DB設計がクソだからサブクエリも作りようがなくてね。でもねこれをなんとかした俺よりもね前任者の評価高いんだよ

[sfujiwara]

昔からINは最大1000個とか分割して処理してる。SQLが長大になるとクエリサイズ制限に掛かったり、設定で回避してもslowlogに1行数MBで記録されたりといろいろ面倒

[taruhachi]

ヒット率が一定数を超えたら当然一回スキャンしただけの方が高効率になるので直感的には妥当というか当たり前な挙動ではあるがちゃんと検証したのはすごい。

[programmablekinoko]

INの中に数万のパラメータともなるとパースだけでコスト相当っぽい、最適化もききづらいだろうし

[legnum]

IN句に万単位のパラメータ入れなきゃならない場面がよくわからない。そんだけあったら属性として列追加した方がよくない？DB設計変えられないのかな

[oktnzm]

IN句内なら多くてもせいぜい数百だろうに・・・数万とか一時テーブル使った方がいい。ドキュメントなめるよりRDBの基本構造学んだ方が有益だろう。

[shag]

昔の oracle は in 区に 3000 以上書けないとか、制約色々あったりして、in 区にいっぱい値を書くのはアンチパターンって感じだよな。

[s025236]

auroraとmysqlの実行計画が同じ前提で話が始まってるんだけど…一昔前は全然違ったんだけど今は一緒なの？

[t-murachi]

そも数万個とか書いたのちゃんと動いちゃうんだ… (他のDBMSだと誤動作もありそうなパターン)

[do_su_0805]

助かった

[kobito19]

で、原因分かってそのクエリのパフォーマンスは改善したん？(そもそもそのフルスキャンはパフォーマンス問題を引き起こしてたん？ 何の目的で調査してたのか読んでもわからん)

[k_wizard]

INの中にそんなに入れるの…

[otcsr]

m

[moccos_info]

クソ長IN句を「シンプルなもの」と言い張るの正気か

[noblejasper]

IN句に数万。まれによくあるよね

[zu2]

“MySQLのパラメーター次第ですが、デフォルトの設定だとこのIN句の中の値の数が数万になると適切なインデックスが用意されていてもフルスキャンが発生する事がありました”

[shimarisugi]

この辺の話が分かるようにsql触っておいて良かった。IN句の中身をサブクエリ(or with)で取得したIDのリストなんかにするとヤバそうね

[wkwkhautbois]

IN句の中に何千もパラメータが入っていたら正気か疑うけど、MySQLだとやる方法なのか…。あるいはORマッパーが生成か。

[khtno73]

まずin 使うならexistsのがたいていのRDBMSだと速いし、この件数ならダミーテーブルにそのid突っ込んで元テーブルとinner joinする方が普通は速そう。

[ghostbass]

IN句の中の値の数が数万＜ちょっと考えにくいけどどういうユースケースなんだろ

[rryu]

件数を絞らずにN+1の+1を取得するクエリーがこうなりがちではあるが、そもそもそういうことをするのが良くない感じはする。

[ene0kcal]

ここ数年SQL全く触ってないけど、こういった時のIN句よりベターなプラクティスがあった気がするんだけど何だったけな？？

[s_rsak]

INの中に数万個入れるとか、初心者でも何かマズイことが起こりそうだと分かりそうなものだが。

[rx7]

なるほど。ロジックと実際に検証された結果がまとまっていて勉強になった。

[bopperjp]

DBA って職種があるだけに、DBはどこまでも沼。

[abryhryk]

高木ひとみ○を思い出した

[rokujyouhitoma]

IN句に2^23/2も突っ込むことはないかな。その規模のデータ量扱う時にはシャーディングして、もっと散らしてしまっている。

[krrrr]

静的解析で分割してないとCIでエラーにするみたいなlint入れたりして対応してるんだけど、他の皆どうしてんの

[kagehiens]

想定外というのも違和感があるが、何億レコードあるテーブルでも一律の個数上限でモード変えられると困ると言うのは分かる。でもこれだけの数inで書くのも可能なら避けたい。

[queeuq]

勉強になる。

[daishi_n]

昔のMySQLはサブクエリが遅くて鬼門だったんだけどね。

[regularexception]

長大なin句より、tmpテーブル作ってで処理した方がいいかなぁって最近思う

[shikiarai]

下手なIN句を見つけると手を加える前に前任者はクソと周りに丁寧に説明してから直すようにしてる。

[ms2sato]

単に私が経験不足なのかもしれないが、サブクエリとか使うものに感じた。大量のIDだけ配列で持ってるならそれも検索の結果に感じるので。何か前提が違うとかなのだろう。

[strawberryhunter]

↓Oracleの制限で昔話を思い出したんだけど、このフルスキャンの話もIN (SELECT ...)にしたら解決する話ちゃうん。ちょっとググったら↓DEPENDENT SUBQUERYは改善されてるらしいし、検証してもらいたいわ。

[highqwerty8]

後で確認

[findup]

ある条件に一致するレコードのIDを先にSELECTしておいて、そのIDをn件単位でSELECTしたい時とかだとIN区に詰め込むことがあるかなあ。