MySQLのサブクエリを改善する方法

MySQLのサブクエリは、一見問題なさそうなものも相関サブクエリに置換されて、極端に遅くなることがありますね。
INを適切に処理できないのが原因らしいです。

問題

今回は、以下のような「ユーザごとにスコアの履歴を保持していて、最新のスコアが有効」というテーブル設計で、「全ユーザの最新スコア合計を取得」しようとして問題が起きました。

  • score_log
カラム 説明
id int primary key auto_increment
user_id int ※外部キー
score int

最初考えたSQLは以下のようなものです。
ユーザごとの最新スコアIDを一括取得し、それに該当するスコアの合計を計算しました。

SELECT SUM(score_log.score) FROM score_log
   WHERE score_log.id IN (SELECT MAX(log2.id)
   FROM score_log AS log2 GROUP BY log2.user_id)

しかしこれだと、EXPLAINをつけると分かるように、DEPENDENT SUBQUERYになってしまいます。
参考サイトによると、MySQLがINをEXISTSに変換する際に、相関サブクエリにしてしまっているようです。
実行速度も、データ件数の二乗に比例し、極端に遅くなってしまいました。

解決方法

結果的に、以下のクエリに変更して解決しました。

SELECT SUM(score_log.score) FROM score_log,
   (SELECT MAX(log2.id) id FROM score_log AS log2 GROUP BY log2.user_id) each_cur
   WHERE score_log.id = each_cur.id

ユーザごとの最新スコアIDの一覧をテーブルとして、それをFROMに使ってしまうというわけですね。

デザインも頼めるシステム開発会社をお探しならBPS株式会社までどうぞ 開発エンジニア積極採用中です! Ruby on Rails の開発なら実績豊富なBPS

この記事の著者

baba

ゆとりプログラマー。 高校時代から趣味でプログラミングを初め、そのままコードを書き続けて現在に至る。慶應義塾大学環境情報学部(SFC)卒業。BPS設立初期に在学中から参加している最古参メンバーの一人。Ruby on Rails、PHP、Androidアプリ、Windows/Macアプリ、超縦書の開発などを気まぐれにやる。軽度の資格マニアで、情報処理技術者試験(16区分17回 + 情報処理安全確保支援士試験)、技術士(情報工学部門)、Ruby Programmer Gold、AWSソリューションアーキテクト(アソシエイト)、日商簿記2級、漢検準1級などを保有。

babaの書いた記事

夏のTechRachoフェア2019

週刊Railsウォッチ

インフラ

ActiveSupport探訪シリーズ