何万件もあるテーブル同士をJOINしていて超絶遅かったSQLの実行を早くした話です。
テーブル同士のJOINをしていたり、サブクエリを使っていたりで遅そうだなとは思っていましたが、実際に使ったら遅すぎて使えませんでした。
(少ない環境ならそれなりに動くので、本番で問題が顕著化した感じです)
高速化の前は全て終わるのに2時間はかかっていました。そのため、wait lookのタイムアウトが来たりMySQLのConnectionタイムアウトが発生していました。
フラグなりパラメーターを変更すれば解決できるのですが、それでは根本的な解決になりません。
結果はindexを考えてSQLをチューニングしたのですがその結果を記事にします。
### 環境
MySQL (バージョン忘れました)
### 問題点
元のSQLはこんな感じ (文法とか間違っているのでイメージです)
```sql
`gutter:true;
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句で絞り込みを行うようにしました。
### 改善後
```sql
`gutter:true;
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 件のコメント :
コメントを投稿