ISUCON14の延長戦をやってます
以下の記事の続きです。
前回はN+1どころじゃない問題を解消するために、椅子の空き状況と最新位置情報をデータベースで管理できるようにしました。 エンドポイント単体はとても軽量になりましたが、残念ながらスコアは思うように伸びませんでした。
なお、最終的なコードは以下のリポジトリで公開しています。
重すぎる集計クエリ
さて、前回の修正を入れると次に目立ってくるのが /api/owner/chairs エンドポイントです。
出題者の解説記事にも「いわゆる観光名所」と言われている箇所ですね。
/api/owner/chairs エンドポイントはオーナー向けのエンドポイントで、自身が管理している椅子の総走行距離を計算して出力します。初期実装では、この情報の計算にはchair_locationsテーブルが利用されていました。chair_locationsテーブルは過去の位置情報をすべて保存している履歴テーブルになっているため、目を背けたくなるような複雑なクエリが実装されています。
このクエリの改善について、解説記事には次のように記述されています。
また、最新以外の
chair_locationsはここでのtotal_distanceを計算することにしか使われないことと、新しく椅子の位置情報が記録されるとき次のtotal_distanceは1つ前の位置情報とそれまでのtotal_distanceから計算できることに気がつけると、各椅子ごとに最新の位置情報と総走行距離を持つテーブルを作りUPSERTだけで管理できるようになり、ここでのクエリをはじめ様々なクエリをシンプルにすることができます。
前回の記事で椅子の最新の位置情報を記録する chair_last_locations テーブルを追加していました。このテーブルを少し調整すれば、問題のクエリを簡素にできそうです。
総走行距離を記録するカラム追加
解説記事に従い、chair_last_locationsテーブルに総走行距離を記録するtotal_distanceカラムを追加しました。デフォルト値に0を入れ、位置情報を更新する度に加算していく運用にします。
total_distance INTEGER NOT NULL DEFAULT 0 COMMENT '総移動距離'
前回の修正では位置情報を更新する度にUPSERTするクエリを実装していましたが、ここにtotal_distanceの更新を追加します。total_distanceは更新前のレコードを参照して更新しないといけないので、ちょっと書き方が難しくなります。この辺りが少し難しくてChatGPTに色々相談しながら作りました。
INSERT INTO chair_last_locations (chair_id, latitude, longitude, updated_at, total_distance) VALUES (?, ?, ?, ?, 0) AS new ON DUPLICATE KEY UPDATE total_distance = chair_last_locations.total_distance + ABS(chair_last_locations.latitude - new.latitude) + ABS(chair_last_locations.longitude - new.longitude), latitude = new.latitude, longitude = new.longitude, updated_at = new.updated_at;
INSERTのときは0を挿入し、UPDATEするときに元のレコードとの差分を計算して加算する、という実装です。
更新するカラムを記述する順序が大事らしく、total_distanceの更新式を最後に記述すると計算結果が0になってしまいます。
おそらくですがlatitudeとlongitudeの更新式を先に書いてしまうと、 chair_last_locations.latitude と chair_last_locations.longitude が new の値になってしまうんだと思います（よくわかってないので有識者の方、教えてください！）。
試行錯誤している中でしばらくこの現象に気付けず、だいぶ長いこと頭を抱えました。
改善された集計クエリ
total_distanceカラムが誕生したことによって、初期実装にあった集計クエリが一切不要になりました。該当クエリは以下のようにchair_last_locationsテーブルを参照するだけで良くなりました。
SELECT c.id, c.owner_id, c.name, c.access_token, c.model, c.is_active, c.created_at, c.updated_at, IFNULL(cl.total_distance, 0) AS total_distance, cl.updated_at AS total_distance_updated_at FROM chairs AS c LEFT JOIN chair_last_locations AS cl ON c.id = cl.chair_id WHERE c.owner_id = ?
リクエスト時点でまだ椅子が位置情報を送信していない可能性があるため、chair_last_locationsテーブルとはLEFT JOINする必要があります。その点にだけ注意が必要です。 これらを取り込んだ完全な変更内容は以下のPull Requestを参照してください。
まとめ・次回予告
非常に重い集計クエリを取っ払い、またひとつエンドポイントを軽量化できました。しかしまたしてもスコアは伸びず、3,500点程度に留まってます。 次スコアが伸びるのはいつになるんでしょうか。。つづく。
