SQL

[SQL] 超絶遅いSQLを高速化した (同じテーブルをjoin)

何万件もあるテーブル同士をJOINしていて超絶遅かったSQLの実行を早くした話です。
テーブル同士のJOINをしていたり、サブクエリを使っていたりで遅そうだなとは思っていましたが、実際に使ったら遅すぎて使えませんでした。
(少ない環境ならそれなりに動くので、本番で問題が顕著化した感じです)
高速化の前は全て終わるのに2時間はかかっていました。そのため、wait lookのタイムアウトが来たりMySQLのConnectionタイムアウトが発生していました。
フラグなりパラメーターを変更すれば解決できるのですが、それでは根本的な解決になりません。
結果はindexを考えてSQLをチューニングしたのですがその結果を記事にします。

環境

MySQL (バージョン忘れました)

問題点

元のSQLはこんな感じ (文法とか間違っているのでイメージです)

?
1
2
3
4
5
6
7
8
9
10
11
SELECT a, b, c, d FROM table_1 T1
LEFT JOIN
    (
        SELECT a, b, c, d
        FROM table_1 T2
    ) T3
    ON T1.a = T3.a
    :
GROUP BY
T1.a
:

explainしてみると、何万件ものテーブルに対してindexが全く使われていませんでした。
やりたいことは全てのテーブルに対して更新をかけたかったので、全部のテーブルを対象に処理を行いたかったのですが、indexが使われないために、不要なレコード同士のJOINが発生して遅くなっていたようです 。
そこで、indexが使われるように、それぞれのSELECT文にWHERE句で絞り込みを行うようにしました。

改善後

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT a, b, c, d FROM table_1 T1
LEFT JOIN
    (
        SELECT a, b, c, d
        FROM table_1 T2
        -- WHEREでの絞り込みをindexの順番に実施
        WHERE T2.a = 'xx'
        AND T2.b = 'cc'
    ) T3
    ON T1.a = T3.a
    :
-- WHEREでの絞り込みをindexの順番に実施
WHERE
    T1.a = 'xx'
    AND T1.b = 'cc'
GROUP BY
T1.a
:

改善後のSQLを実行してみると、10分経っても終わらなかったSQLが数秒で処理が終わりました。
しかしながら、全部のテーブルに対してSELECTできた訳では無いので
プログラムを書いてWHERE句を変えながら全部のテーブルに対して処理を行うようにしました。

その結果、2時間かかっていた処理が2分程度で終わりました。
みんな盛んに言っていますが、indexは使わないとダメです。

教訓

  • 重たいSQLはexplainでどうなっているか調べてみる
  • indexが効いていないようであれば、indexを使えないか検討してみる
  • 場合によっては幾つかに分けた方が早いかもしれない (スキルの問題かもしれませんが)

0 件のコメント :

コメントを投稿