MySQL で IN 句 + サブクエリの処理時間が遅い場合の改善方法
概要
とあるプロジェクトで Rails 3.2 + MySQL 5.5 を利用してる。
管理画面の機能追加と改修をしているのだが、 ある機能の一覧ページが重くて開かないと報告を受けた!
1台しかない MySQL サーバが落ちて、 サービスに支障が出たことが何度かあるらしい。
問題のページについて
問題のページはよくある、管理画面の一覧画面。
Ajax により、JSON で一覧情報を受け取って表にするページなのだが、 集計値も表に含まれており、この集計値の算出が遅い原因になっていた。
調査・再現
本番同等のDBを準備してもらい、問題の画面を開く。
数分待っても、JSON が返却される様子がなく、そのままタイムアウト。
MySQL のクライアントからshow processlist;
で実行中の SQL を軽く覗いてみると、
集計値を出すための SQL が 15 〜 30 秒くらいかかっている。
Rails のソースでは hoge.join(...).where(...).count をしていて、 初期表示では 25 件分の表示があるため、 ざっくり 「15 秒 * 25 件」 の時間がかかってしまっていたのだ。
Rails で何も考えず作られた SQL だろうし、 できることならインデックス追加するだけで対応したいってのが本音。
複合インデックスを追加する簡易対応
何となく勘で、どうせインデックスが使われてないんやろと思って、
実行計画を見てみた。
(この時点で SQL はよく見てない)
+----+--------------------+------------------+------+--------------------------------------+--------------------------------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+------+--------------------------------------+--------------------------------------+---------+-------+------+----------------------------------------------+ | 1 | PRIMARY | hoge | ref | FK_hoge_fugaid | FK_hoge_fugaid | 8 | const | 2 | Using where | | 2 | DEPENDENT SUBQUERY | hoge | ref | FK_hoge_fugaid | FK_hoge_fugaid | 8 | const | 2 | Using where; Using temporary; Using filesort | +----+--------------------+------------------+------+--------------------------------------+--------------------------------------+---------+-------+------+----------------------------------------------+
Using temporary; Using filesort はあかん、 複合インデックスを新たに追加。
+----+--------------------+------------------+------+-----------------------------------------------------------------+--------------------------------------+-- -------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | k ey_len | ref | rows | Extra | +----+--------------------+------------------+------+-----------------------------------------------------------------+--------------------------------------+---------+-------+------+--------------------------+ | 1 | PRIMARY | hoge | ref | FK_hoge_fugaid,idx_add_test_key | FK_hoge_fugaid | 8 | const | 2 | Using where | | 2 | DEPENDENT SUBQUERY | hoge | ref | FK_hoge_fugaid,idx_add_test_key | idx_add_test_key | 8 | const | 2 | Using where; Using index | +----+--------------------+------------------+------+-----------------------------------------------------------------+--------------------------------------+---------+-------+------+--------------------------+
へ改善。
(あとで説明するが、
これらの実行計画で問題なのは 2 番目の select_type = DEPENDENT SUBQUERY)
問題のページを開いてみると、ページが開くようになり、 JSON 返却まで 3〜4 秒くらいになった。
だが、ページングでいろんなページを開いてみると、 10 秒くらいかかる遅いページもあるし、納得できない。
複合インデックス追加だけで逃げられないか、と SQL を調査。
SQL を調査
適当に省略 + 簡略化した問題の SQL を下記へ。
SELECT COUNT(*) FROM hoge WHERE hoge.id IN (SELECT max(id) as max_id FROM hoge WHERE hoge.fuga_id = 1 GROUP BY user_id) AND (flag IS NULL OR flag = 0)
どうやら IN 句内のサブクエリで取得する件数が多いほど、 SQL の実行速度が遅くなる傾向を掴んだ。
このケースでは 1000 件から 2 秒以上かかる感じだった。
IN を INNER JOIN へ
問題のSQL は id の突き合わせに IN 句を利用しているが、 これは上記の通り件数が増えるごとに遅くなっていく。
仮に 10 万件の集計値を求めるときには IN 句の指定が 10 万件必要になる。
MySQL がどう解釈して動いているか知らないけど、
人間の頭で考えても非効率そうだ。
INNER JOIN で id の突き合わせをした後の行数をカウントした方が速いだろうと、 SQL を下記のように修正。
SELECT COUNT(id) FROM hoge h1 INNER JOIN (SELECT max(id) AS max_id FROM hoge WHERE hoge.fuga_id = 1 GROUP BY user_id) h2 ON h1.id = h2.max_id WHERE h1.flag IS NULL OR h1.flag = 0
とある遅いクエリ、
8.73 sec -> 0.13 sec へ。
実行計画は下記。
+----+-------------+------------------+--------+-------------------------------------------------------------------------+----------------------------+---------+------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+--------+-------------------------------------------------------------------------+----------------------------+---------+------------+------+--------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2198 | | | 1 | PRIMARY | h1 | eq_ref | PRIMARY | PRIMARY | 8 | h2.max_id | 1 | Using where | | 2 | DERIVED | hoge | ref | idx_add_test_key | idx_add_test_key | 8 | | 2218 | Using where; Using index | +----+-------------+------------------+--------+-------------------------------------------------------------------------+----------------------------+---------+------------+------+--------------------------+
(あとで説明するが、select_type = DEPENDENT SUBQUERY が消えている)
Rails で上記 SQL を発行するように ArelTable を利用して書き直して、修正終わり。
# 修正前 Hoge.where(id: Fuga.where... ).where... # 修正後 to = hoge_arel_table.project(...).where... hoge_arel_table.project(...).join(to, Arel::Nodes::InnerJoin).on(...).where...
参考
無責任な記事は書けないので、さらなる調査をしたら下記サイトを見つけた。
漢(オトコ)のコンピュータ道 さんは大昔から何度もお世話になっている。
要点だけを書くと、
- IN 句 + サブクエリは EXISTS 句に書き換えられて実行される
- 実行計画の select_type が DEPENDENT SUBQUERY は遅い
- 遅い IN 句 は JOIN へ置き換えろ
MySQLにおいてDEPENDENT SUBQUERYが何故遅いか?それはクエリの評価方法にある。 現時点でのMySQL(バージョン5.1)では、サブクエリはまず外部クエリの条件から評価される。そして、外部クエリの条件に合致する行が見つかると、その行がサブクエリの条件に合致するかどうかが評価されるわけである。即ち、サブクエリにおいてフェッチしなければいけない行数が平均N行、外部クエリでフェッチされる行数がM行のとき、サブクエリにおいてM×N行の評価が行われることになる。これは膨大な計算量である。
サブクエリを使うと実行順が外側からと固定になってしまいますが、JOINの場合はMySQLが最適な実行順を勝手に計算して実行してくれます。
まとめ
- 実行計画の select_type が DEPENDENT SUBQUERY は遅いから要注意
- IN 句 + サブクエリでインデックスを適切に貼っても遅い場合は JOIN で書き直す
JOIN で書き直した実行計画は DEPENDENT SUBQUERY が消えて、 適切にクエリを処理できるようになったようだ。
実行計画は本当に奥が深くて難しいし、まだまだ解っていないことも多い。
Rails で開発しているから SQL と触れる機会は激減したけど、SQL の改善作業ってのは楽しい。
しかしこれ、MySQL Cluster だと JOIN 遅くて使い物にならないから、解決にならんだろうな・・・