STRAIGHT_JOINをつかったSQLチューニング

2021年2月4日

こんにちは。PHPエンジニアの千葉です。
近所の金木犀が咲いたとおもったら、すぐに散ってしまって少しさびしいです。秋ですね…。

問題となった箇所

ゲソてんには、メッセージ機能というものがあり、
フレンド同士でメッセージのやりとりをしたり、
管理画面からメッセージを配信したりすることができます。

このメッセージ機能をお知らせとして使いたい、
という要望があり、管理画面から配信したものだけに絞り込んで
マイページに表示させてほしいという依頼がありました。

↓マイページをスクロールすると下の方にあります。

このSELECTクエリが今回の主役です。

こんなふうに変わっていきました

第1段階

既存のクエリをコピペして、それをベースに
要件を満たすように手を入れて作ったクエリ。filesortになっています。

mysql> explain SELECT
    `message_relation_status`.*,
    `message_status`.`to_status`
FROM
    `message_status`
    INNER JOIN
        `message_relation_status`
    ON  (
            `message_status`.`message_relation_status_id` = `message_relation_status`.`message_relation_status_id`
        )
    INNER JOIN
        messages
    ON  message_status.message_id = messages.message_id
WHERE
    (
        `message_status`.`to_user_id` = yyy
    AND `message_status`.`to_status` <= zzz
    )
ORDER BY
    `message_relation_status`.`message_relation_status_id` DESC
LIMIT 5 OFFSET 0;

| id | select_type | table                   | type   | possible_keys                                                                         | key                   | key_len | ref                                           | rows | Extra                                                  |
|----|-------------|-------------------------|--------|---------------------------------------------------------------------------------------|-----------------------|---------|-----------------------------------------------|------|--------------------------------------------------------|
| 1  | SIMPLE      | message_status          | range  | PRIMARY,idx_message_status_01,idx_message_status_02,idx_message_status_03,index_test3 | idx_message_status_01 | 6       | NULL                                          | 165  | Using index condition; Using temporary; Using filesort |
| 1  | SIMPLE      | messages                | eq_ref | PRIMARY,idx_messages_03                                                               | PRIMARY               | 8       | XXX.message_status.message_id                 | 1    | Using index                                            |
| 1  | SIMPLE      | message_relation_status | eq_ref | PRIMARY                                                                               | PRIMARY               | 8       | XXX.message_status.message_relation_status_id | 1    | NULL                                                   |

3 rows in set (0.00 sec)

第2段階

第1段階のクエリはfilesortになっていたので、DBチームに相談しました。

修正事項）
・WHERE句のmessage_status.to_user_idをmessage_relation_status.to_user_idに変更

結果）
駆動表がmessage_status からmessage_relation_statusに変わりました。
ソート済みの行からフェッチできるようになりfilesortが解消されました！

このクエリでリリースをしました。

mysql> explain SELECT
    `message_relation_status`.*,
    `message_status`.`to_status`
FROM
    `message_status`
    INNER JOIN
        `message_relation_status`
    ON  (
            `message_status`.`message_relation_status_id` = `message_relation_status`.`message_relation_status_id`
        )
    INNER JOIN
        messages
    ON  message_status.message_id = messages.message_id
WHERE
    (
        `message_relation_status`.`to_user_id` = yyy
    AND `message_status`.`to_status` <= zzz
    )
ORDER BY
    `message_relation_status`.`message_relation_status_id` DESC
LIMIT 5 OFFSET 0;
| id | select_type | table                   | type   | possible_keys                                                   | key                            | key_len | ref                                                    | rows | Extra       |
|----|-------------|-------------------------|--------|-----------------------------------------------------------------|--------------------------------|---------|--------------------------------------------------------|------|-------------|
| 1  | SIMPLE      | message_relation_status | ref    | PRIMARY,idx_message_relation_status_02,idx_test2                | idx_message_relation_status_02 | 4       | const                                                  | 196  | Using where |
| 1  | SIMPLE      | message_status          | eq_ref | PRIMARY,idx_message_status_02,idx_message_status_03,index_test3 | PRIMARY                        | 8       | XXX.message_relation_status.message_relation_status_id | 1    | Using where |
| 1  | SIMPLE      | messages                | eq_ref | PRIMARY,idx_messages_03                                         | PRIMARY                        | 8       | XXX.message_status.message_id                          | 1    | Using index |

3 rows in set (0.00 sec)

第3段階

第1段階/第2段階のクエリはテスト環境で確認しましたが、
本番でスロークエリとして流れてくるようになった模様です。

EXPLAIN結果を見ると第2段階のときと変わっていますね…。。

mysql> explain SELECT
    `message_relation_status`.*,
    `message_status`.`to_status`
FROM
    `message_status`
    INNER JOIN
        `message_relation_status`
    ON  (
            `message_status`.`message_relation_status_id` = `message_relation_status`.`message_relation_status_id`
        )
    INNER JOIN
        `messages`
    ON  (
            `messages`.`message_id` = `message_status`.`message_id`
        )
WHERE
    `message_relation_status`.`to_user_id` = yyy
AND `message_status`.`to_status` <= zzz
ORDER BY
    `message_relation_status`.`message_relation_status_id` DESC
LIMIT 5 OFFSET 0;

| id | select_type | table                   | type   | possible_keys                                       | key                   | key_len | ref                                           | rows | Extra                                        |
|----|-------------|-------------------------|--------|-----------------------------------------------------|-----------------------|---------|-----------------------------------------------|------|----------------------------------------------|
| 1  | SIMPLE      | messages                | index  | PRIMARY                                             | idx_messages_01       | 6       | NULL                                          | 2253 | Using index; Using temporary; Using filesort |
| 1  | SIMPLE      | message_status          | ref    | PRIMARY,idx_message_status_02,idx_message_status_03 | idx_message_status_02 | 8       | XXX.messages.message_id                       | 2    | Using where; Using index                     |
| 1  | SIMPLE      | message_relation_status | eq_ref | PRIMARY,idx_message_relation_status_02              | PRIMARY               | 8       | XXX.message_status.message_relation_status_id | 1    | Using where                                  |

3 rows in set (0.02 sec)

修正事項）
・FROM句のテーブルをmessage_status→message_relation_statusに変更
・message_statusとのJOINをINNER JOINからSTRAIGHT_JOINに変更

…STRAIGHT_JOINって初めて聞きました（。。）

STRAIGHT_JOIN は、左側のテーブルが常に右側のテーブルの前に読み取られる点を除き、JOIN と同じです。

https://dev.mysql.com/doc/refman/5.6/ja/join.html

結果）
ORDER BYのカラムがmessage_relation_statusのPKなので
message_relation_statusを駆動表にできれば最初からソート済になっています。
これを狙うためSTRAIGHT_JOINを使って、常に左側のテーブル（message_relation_status）が
先に読まれるようにすることで、第2段階のときと同じ実行計画になりました…！！

mysql> explain SELECT 
    `message_relation_status`.*,
    `message_status`.`to_status`
FROM
    `message_relation_status`
        STRAIGHT_JOIN
    `message_status` ON (`message_status`.`message_relation_status_id` = `message_relation_status`.`message_relation_status_id`)
        INNER JOIN
    `messages` ON (`messages`.`message_id` = `message_status`.`message_id`)
WHERE
    `message_relation_status`.`to_user_id` = yyy
        AND `message_status`.`to_status` <= zzz
ORDER BY `message_relation_status`.`message_relation_status_id` DESC
LIMIT 5 OFFSET 0;

| id | select_type | table                   | type   | possible_keys                                       | key                            | key_len | ref                                                    | rows | Extra       |
|----|-------------|-------------------------|--------|-----------------------------------------------------|--------------------------------|---------|--------------------------------------------------------|------|-------------|
| 1  | SIMPLE      | message_relation_status | ref    | PRIMARY,idx_message_relation_status_02              | idx_message_relation_status_02 | 4       | const                                                  | 4020 | Using where |
| 1  | SIMPLE      | message_status          | eq_ref | PRIMARY,idx_message_status_02,idx_message_status_03 | PRIMARY                        | 8       | XXX.message_relation_status.message_relation_status_id | 1    | Using where |
| 1  | SIMPLE      | messages                | eq_ref | PRIMARY                                             | PRIMARY                        | 8       | XXX.message_status.message_id                          | 1    | Using index |

3 rows in set (0.01 sec)

参考）データ件数

テスト環境と本番環境でmessage_relation_statusのデータ件数が大きく異なったため、
テスト環境でのEXPLAIN結果と違う結果となり、リリース後にスロークエリとして流れてくるようになっていました。

messages message_relation_status
テスト環境 405 22,984
本番環境 2,281 44,747,015

GMOメディアでは仲間を募集しております

当社は、「For your Smile,with Internet.」という企業理念のもと、多くの笑顔と感動を生み出していくべく、インターネットメディア媒体を多ブランドでサービス展開しています。
募集内容について詳しくはコチラをご覧ください

TechMySQL