SQL改善によるパフォーマンス向上

事例

実際にアプリケーション開発を行っていて、検索機能を実装していたときに超絶重い検索ロジックに出会いました。
その対処をSQL文の改善で行なったので紹介します。

データ構造

環境
- MySQL 4.1

mysql> DESCRIBE users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     |      | PRI | NULL    | auto_increment |
| name  | varchar(40) | YES  |     |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE diaries;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  |      | PRI | NULL    | auto_increment |
| user_id    | int(11)  |      | MUL | 0       |                |
| body       | text     | YES  |     |         |                |
| created_at | datetime | YES  |     |         |                |
+------------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

重いクエリ

ユーザーにとって一番最近書いた日記で、内容に「最新」が含まれる日記を書いたユーザーを取得する

SELECT users.*
FROM users
LEFT OUTER JOIN diaries ON diaries.user_id = users.id
WHERE
  users.id IN (
    SELECT diaries.user_id
    FROM (
      SELECT MAX(diaries.created_at) AS created_at, diaries.user_id
      FROM diaries
      GROUP BY diaries.user_id
    ) recent_diaries, diaries
    WHERE
      diaries.user_id = recent_diaries.user_id AND
      diaries.created_at = recent_diaries.created_at AND
      diaries.body LIKE '%最新%'
  );

やりたかった事

ユーザーにとっての最新の日記群(recent_diaries)を作る
そのrecent_diariesのuser_idとcreated_atが一致するdiariesのbodyに「最新」が含まれているdiariesのuser_idを取得する
- recent_diariesはuser_idでGROUP BYしているので、意図したdiariesの情報が取得できない
  - この場合は、bodyの情報
- 意味があるデータとして取得できるのは、user_idと最新のcreated_atのセットのみになる
- ユーザーにとっての最新の日記を取得するためには、recent_diaries.user_idとrecent_diaries.created_atがdiaries.user_idとdiaries.created_atとが一致しているかを見ることによってのみ実現する
- さらに、そのユーザーにとっての最新の日記のbodyに「最新」が含まれているかを見ることによって、意図した日記群が取得することが出来る
取得したuser_id群にusers.idが含まれているusersを取得する
- 意図した日記群のuser_idをSELECTし、そのuser_id群にusers.idが含まれているかを見ることによって意図したユーザーを取得する

原因

相関サブクエリを使用している

相関サブクエリとは( DEPENDENT SUBQUERY )

サブクエリにおいて外部クエリのカラムを参照しているサブクエリのこと
つまり、外部クエリのカラムの値と相関関係を持っているサブクエリのこと
- 例えば、

SELECT users.id
FROM users
WHERE users.id = (
  SELECT diaries.user_id
  FROM diaries
  WHERE
    diaries.user_id = users.id AND
    diaries.body LIKE "%相関%"
  GROUP BY diaries.user_id
);

※相関サブクエリを使わなくても意図したレコードは取得できますが、あくまでも例としてあげています。

外部クエリusers.id = ()に応じて、()内のDEPENDENT SUBQUERYが実行される

基本的に、相関サブクエリは重いとされているが、インデックスを貼ることで、速度は大幅に改善される
- この場合、diaries.user_idにindexを貼ることで速度改善が見込める

相関サブクエリはどこ？

一見、問題のクエリに相関性は内容に見える
EXPLAINで出力

+----+--------------------+------------+-------+--------------------------------------------------------------+--------------------------+---------+---------------+-------+-------------+
| id | select_type        | table      | type  | possible_keys                                                | key                      | key_len | ref           | rows  | Extra       |
+----+--------------------+------------+-------+--------------------------------------------------------------+--------------------------+---------+---------------+-------+-------------+
|  1 | PRIMARY            | users      | ALL   | NULL                                                         | NULL                     |    NULL | NULL          | 23912 | Using where |
|  1 | PRIMARY            | diaries    | ref   | index_diaries_on_user_id,user_id                             | user_id                  |       4 | test.users.id |     1 |             |
|  2 | DEPENDENT SUBQUERY | <derived3> | ALL   | NULL                                                         | NULL                     |    NULL | NULL          |  3455 |             |
|  2 | DEPENDENT SUBQUERY | diaries    | ref   | index_diaries_on_created_at,index_diaries_on_user_id,user_id | index_diaries_on_user_id |       4 | func          |     1 | Using where |
|  3 | DERIVED            | diaries    | index | NULL                                                         | index_diaries_on_user_id |       4 | NULL          | 45561 | Using where |
+----+--------------------+------------+-------+--------------------------------------------------------------+--------------------------+---------+---------------+-------+-------------+
5 rows in set (2.95 sec)

3行目と4行目、「DEPENDENT SUBQUERY」との記載がある
- つまり、相関サブクエリが存在することなっている
ユーザーの気持ちとしては、users.id IN (DEPENDENT SUBQUERY)を以下の通りに処理してほしいはず
- ()内をusers.idとの相関性を持たせるのではなく、()内のSUBQUERYを実行した結果を元にusers.id IN ()を評価する
しかし、実際はusers.idと相関を持ち、()内が評価されてしまう

現時点でのMySQL（バージョン5.1）では、サブクエリはまず外部クエリの条件から評価される。そして、外部クエリの条件に合致する行が見つかると、その行がサブクエリの条件に合致するかどうかが評価されるわけである。即ち、サブクエリにおいてフェッチしなければいけない行数が平均N行、外部クエリでフェッチされる行数がM行のとき、サブクエリにおいてM×N行の評価が行われることになる。これは膨大な計算量である。

参照：漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。

つまり、usersの数だけ()内のSUBQUERYが走ることになる
- この場合、()内の処理単体で0.3秒かかっていた
- 0.3sec * 23912 = 119.56min
- つまり、単純計算で、最低でも2時間はかかるクエリとなってしまっていたのだ！

改善

先に、ユーザーにとっての最新の日記群を作ってしまう

usersの数だけ、最新の日記群を作るクエリが走ることが問題であれば、先にその日記群を作ってしまえば、毎回最新の日記群を作らなくて済むはず

SELECT users.*
FROM users, (
  SELECT MAX(diaries.created_at) AS created_at, diaries.user_id
  FROM diaries
  WHERE
    diaries.event_name NOT LIKE '%選考' AND
    diaries.user_impression IS NOT NULL
  GROUP BY diaries.user_id
) recent_diaries
LEFT OUTER JOIN diaries ON diaries.user_id = users.id
WHERE
  users.id IN (
    SELECT diaries.user_id
    FROM diaries
    WHERE
      diaries.created_at = latest_records.created_at AND
      diaries.user_id = latest_records.user_id AND
      diaries.body LIKE '%最新%'
  );

こうすることで、最新の日記群を作るクエリをusersの数だけ走らせない様にすることは実現できた
しかし、次の問題が生じた
EXPALINで見てみよう

+----+--------------------+------------+-------+--------------------------------------------------------------+--------------------------+---------+------------------------+-------+-------------+
| id | select_type        | table      | type  | possible_keys                                                | key                      | key_len | ref                    | rows  | Extra       |
+----+--------------------+------------+-------+--------------------------------------------------------------+--------------------------+---------+------------------------+-------+-------------+
|  1 | PRIMARY            | <derived2> | ALL   | NULL                                                         | NULL                     |    NULL | NULL                   |  3455 |             |
|  1 | PRIMARY            | users      | ALL   | NULL                                                         | NULL                     |    NULL | NULL                   | 23912 | Using where |
|  1 | PRIMARY            | diaries    | ref   | index_diaries_on_user_id,user_id                             | user_id                  |       4 | test.users.id          |     1 | Using index |
|  3 | DEPENDENT SUBQUERY | diaries    | ref   | index_diaries_on_created_at,index_diaries_on_user_id,user_id | index_diaries_on_user_id |       4 | recent_diaries.user_id |     1 | Using where |
|  2 | DERIVED            | diaries    | index | NULL                                                         | index_diaries_on_user_id |       4 | NULL                   | 45561 | Using where |
+----+--------------------+------------+-------+--------------------------------------------------------------+--------------------------+---------+------------------------+-------+-------------+
5 rows in set (0.26 sec)

一行目のテーブル
は、id=2のDERIVED(5行目)に由来するテーブルであることを示している
- つまり、FROM句のサブクエリの結果のテーブル「recent_diaries」のことである
- このようなDERIVEDに由来するようなテーブルを「テンポラリーテーブル」と呼ぶ
テンポラリーテーブルrecent_diariesのkeyはNULLである
- つまり、indexが使えないのである！
テンポラリーテーブルはフルテーブルスキャンが走る！！
・・・テンポラリーテーブルのレコード数が多いと、爆弾クエリになってしまうorz

別の切り口を考えてみる

作戦
- そもそも、ユーザーにとっての最新の日記は一つである
- 相関サブクエリを利用して、ユーザーごとの最新の日記という条件を加える
- さらに、日記のbodyに「最新」が含まれているという条件を加える
- 上記の条件に当てはまった日記を持つ学生を取得する

SELECT users.*
FROM users
LEFT OUTER JOIN diaries ON diaries.user_id = users.id
WHERE
  diaries.id = (
    SELECT
      diaries.id
    FROM
      diaries
    WHERE
      diaries.user_id = users.id
    ORDER BY diaries.created_at DESC
    LIMIT 1
  ) AND
 diaries.body LIKE '%最新%';

うん！シンプルかつ高速なクエリができました！

まとめ

SQLを組むときは以下のことに注意したい

意図しない相関サブクエリが生成されていないか？
正確な場所にインデックスが貼られているか？
テンポラリーテーブルが巨大になっていないか？

ぐんぐん実力がつく! 逆算式SQL教科書

作者: 小野哲
出版社/メーカー: 技術評論社
発売日: 2007/12/25
メディア: 単行本（ソフトカバー）
購入: 4人クリック: 318回
この商品を含むブログ (17件) を見る

以上です。
最後までお読みいただきありがとうございました。