目次
くっそ重いバッチについて調査を頼まれたんよ
参画したプロジェクトのバッチ処理の1つで1ユーザー当たり10分以上処理が掛かるバッチがあって、全ユーザーの処理が1日で終わらずに集計出来ない分が溢れてるという報告があり調査をすることに。問題の前提として、ものすごい量のレコードがあるテーブル（確認時点で1,100万レコード以上）を対象として集計処理を行うというそもそもそれ大変じゃね？という処理があるのですけど、その処理自体をどうこう言い始めるとインフラの話やビジネスサイドの話まで広がってしまうので、とりあえずは今やってる処理が1日で収まる程度には効率化することを目標にします。
問題のコード=サン
集計処理の前段階としてレコードを取得してるんですが、Eloquentモデルをつかってこんな感じに書かれてました。$user_logs = UserLog::whereDate('created_at', $aggregate_day)
->where('user_id', $user->id)
->get();やりたいことは「特定日に生成された特定ユーザーのログを抽出する」なのですけど、これがまた遅い。与える条件にもよりますが、実際に本番環境で実行してみると1~5分掛かる。同じような集計処理が他に5つほどあるので、そりゃまあ10分以上掛かるよね、ユーザー数によっては1日では終わらないよねということになっていました。
問題点
DBとこのコードから解る問題点は次の2つ。- INDEXの設定方法が適切でない
- whereDate() 超遅い
1. INDEXの設定方法が適切でない
EXPLAINして見ると、何一つとしてINDEXが使われていないことがわかりました。user_idにもINDEXが張られていなくて適切に設定がなされていないようです。1,100万レコードは確かにでかいですけど、でも経験上、ちゃんと設定されていればフリーズするほど重くはならないはずなんですよね。サブクエリとかあれば別ですが。2. whereDate() 超遅い
そしてもうひとつ、INDEXの設定と相まってかなり致命的なのがこちら。先ほどのコードで発行されるクエリをダンプしてみるとこうなります。select * from impression_logs where date(created_at) = "2024-01-10" and user_id = 1whereDate() を使うとどうなるかというと、created_atをdate型に変換したものと与えられたパラメータを比較してるんですね。created_atにはINDEXは張られていましたが、あくまでdatetime型として張られているので、こういう使い方をするとINDEXは有効になりません。つまり、全件チェェェェェェェェック！
マジかよ。1,100万レコードだぞ。
（この場合は）whereBetween() 使おう
whereDate() を使えば時分秒を考慮することなく日付でレコードが抽出できるということでこういう構成になっているんだと思いますが、それが普通のテーブルならいざ知らずログテーブルでやっちゃダメってことですね。例えば、
- created_atを元にDATE型の仮想カラム（たとえばcreated_date）を作ってそれにINDEXを張る
- MySQL8.0だとDATE型に変換したデータに対してINDEXを張るみたいなことも出来るらしい
みたいなことをすればこのままでも大丈夫かなと思いますけど、インフラに手を入れるのはなかなかにアレなので（出来なくはないけどそれなりの決裁が必要）、ここはやはり素直に
whereBetween() で何とかするべきではないでしょうか、、というわけで：whereDate() 使うときは影響をよく考えましょう
対応策についてはまだ調整中なので（元のバッチがそもそもアレなので書き直してる）最終的にどうなるかはわかりませんが、とりあえずwhereBetween() 使って影響ないクエリを吐き出すようにはしておく予定です。クエリビルダって怖いですね。「詳しいこと知らなくてもデータベース操作できる！」と手放しで言えるようなものではないかなー。ほとんどの場合は問題なくて、ほとんどの場合は超便利なんですけどね。
