なぜDBから引くときに1000件ずつchunkingするのか、説明できますか

MySQLやPostgreSQLといったRDBMSからデータを引いてくるとき、扱うデータの規模によっては、1000件ずつLIMITをかけて順に引いていくということがある。

以前slow queryが出たらよくやっていたのを思い出して、ふとこのあたりってどういう根拠があってやっているのだっけ、自分が知っている他に効能があったりするのかな、と思ってSlackに書き込んだところ、同僚の id:onk に教えていただいた。その内容に加えて軽く調べた内容をまとめてみる。

Web系の話です。みなさまの知見がありましたら教えてください。

TL;DR

刺さる*1から
刺さったら困るから
- あたりまえ

詳細

もともとSlackに書いた原文は以下の通り(MySQL前提で書いているけどPostgresといった他のRDBMSにも適用できる話。):

DB引くとき、Perl時代(？)によく1000件単位でchunkingしてたのって、I/Oでプロセスが刺さるのを回避したいからだったっけ

このときは、Perl固有の事情もあったっけ、というつもりで書いていた。これに対して、教えていただいた内容は以下の通り:

デッドロックの発生率が小さくなる
- 一発一発が小さくなり、すぐにロックが解放されるから
Indexが効きやすくなる
- デカすぎるクエリを発射するとtable scanになることがあるらしい
- MySQL :: MySQL 9.0 Reference Manual :: 10.2.1.23 Avoiding Full Table Scans
IO刺さりづらくもなると思う
- (一発が小さくなるためI/O待ちの時間も小さくなる)

というわけで、このやり方はおおむね現代でも通用しそう。以下は自分の感想。

デッドロックは発生するものなのでクエリを分割して確率を下げられるならそれでいい
- 名前がおどろおどろしいけどまともにトランザクション張ってるならクエリ殺すだけで大抵なんとかなる
- Web系ならこの感覚でいい
Indexについては、RDBMSの進化によってオプティマイザなどが賢くなっていくので、そのうち気にしなくてよくなるかもしれない？
- 構造的にどうしようもない場合もありそう
IOでスレッドが刺さるのは場合によりそう
- Perlのようなシングルスレッド言語でStarmanといったpreforkするタイプのサーバを使っていたりすると問題となりうる
  - 刺さるとそのプロセスを占領するから
- 現代のマルチスレッド/イベントループで駆動するシングルスレッド言語ではそれほど強い問題にはならないのではないか
  - それはそうと、クエリが刺さりまくるとマルチスレッド言語でも詰まる
  - それはそうと、100000件引いた結果を普通に処理しているとシングルスレッド言語ではそこでCPU boundになって刺さる
そもそもデータがデカすぎてメモリに載らなかったりもする
- バッチとかだったら当然考慮するべきだが、件数による
- オンラインの処理で1000件超えちゃうこともままある
それはそうと、引く件数が同じなら当然だいたい同じ時間がかかるので、時間を減らすための弾丸ではない
- たまに走らすバッチジョブとかでとんでもない数のレコードが引かれてサービスを巻き添えにして詰まる、ということを回避するという保険の意味合いもある

IN句のサイズを制約することもある

IN句に渡すリストのサイズを制約して分割することもある。

Postgresは65535個以上のクエリパラメータを一度に扱うことができない
- https://www.postgresql.org/docs/current/limits.html による
- 例えば巨大なIN句を実行してしまうと発生する
Oracle DBではIN句の中身は1000個までという制限(ORA-01795)がある
MySQLでは特段の制限はない
- クライアントの実装にもよりそう

こういう本を読んで勉強したい・・・