はじめに
こちらの記事は MySQL についての記載となります。
ほかの RDBMS でも概ね考え方は同じですが、細かい部分は異なりますので、ご留意ください。
最近実施した slow query の対策について、記載したいと思います。
slow query とは
slow query とは、SQLの実行速度が一定以上かかるクエリの事です。
一定以上とは、約0.5秒以上(0.5秒～2秒) かかるクエリになります。
閾値については、DBサーバのスペックやデータ量や、実行されるSQL頻度などによって変わってくると思います。
なお、何も対策していない場合、0.5秒を閾値にすると、大量に出てきますので、その場合は、1秒や2秒などに設定し、対策を行いつつ、閾値を下げていくという方法も良いと思います。
index とは
SQL を考える上で index は重要です。
index とは、簡単に言うと、本の目次の様なものです。
例えば、辞書を見るときに目次がないと全部のページを見ないといけなくて大変ですよね？
目次があることで、容易に目的の情報にアクセスする事が出来ると思います。
何か機能を実装する際に、新しいテーブルを用意すると思います。
新規テーブル作成時に、どういう使われ方をするかを明確にして、適切なindexを用意するようにしましょう。
基本的な slow query の主な要因
slow query の主な要因は、概ね、以下になります。
1. 適切なindexが貼られていない
実行されるクエリに対して、適切なindexが用意されていないと、indexが使用できないので、全部のデータにアクセスする必要が出てきます。
2. 無駄なクエリになっている
パターンはいろいろとあると思いますが、クエリの最適化を行う必要がある状態です。
- 必要ないテーブルが
外部結合(left join や right join)されている-
inner joinに変更したり、不要なら削除しましょう
-
- クエリでやるにはコストが高い処理で、アプリ側の処理で代替が可能な内容がクエリで行われている
- 例えば、集計処理などで、SQL実行後にアプリケーション側で行った方がコストが少ない場合があったりします。
実際に実行されているクエリを確認する方法
アプリケーションの処理を見ただけでは、実際に実行されるクエリが分からない場合があります。
ピュアに近い形でSQL文を作成しているアプリケーションは少ないでしょう。
そういう場合は、実行されるクエリすべてをログに出力する事が出来ます。
これは、開発環境や自分しかアクセスしない環境などで行いましょう。
show variables like 'general_log%';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/xxxxxxxxxxxx.log |
+------------------+---------------------------------+
general_log が OFF になっている場合は、ログが出力されません。
general_log_file のファイルに、実行されたクエリが出力されます。
OFF を ON にする場合は以下を実行
SET GLOBAL general_log = 'ON';
確認が終わったら、忘れずに OFF に変更しましょう。
ON のままだと、ログファイルの量がすごいことになります。
SET GLOBAL general_log = 'OFF';
詳細は以下を確認してください。
クエリの実行計画について
EXPLAINコマンドにてクエリの実行計画を確認しましょう。
EXPLAINコマンドでは、クエリがどのように実行されるのかという情報が確認できます。
EXPLAINの見方は、長くなるので、省きます。
簡単に説明しておくと、どういったindexを使用して、どれくらいのデータをスキャンして、テンポラリーが使われるか、ソート処理がおこなわれるか、
などの情報が確認できるので、クエリをどのように改善したら良いかを探るために重要な情報となります。
詳細は以下を確認してください。
今回対応したクエリの概要
今回、対策した slow query について説明します。
なお、実際の内容だと複雑なので、完結にまとめています。
以下の様な仕組みが存在します。
１．ユーザが行動した際にログが残る
２．ユーザの行動ログを、日次・月次で集計し、バックオフィスの画面に表示させる
まぁ、よくある行動分析のデータ集計処理ですね。
データ量の仮定は以下とする。
- 1分間にユーザが1回行動をする
- 常時のアクティブユーザ数は1000
1日 = 1 * 60 * 24 * 1,000 = 1,440,000 // 約150万件
1か月 = 1,440,000 * 30 = 43,200,000 // 約4300万件
1年 = 43,200,000 * 12 = 518,400,000 // 約5億1800万件
日次や月次で集計をする際に、データが作成された日時を条件にデータを取得します。
具体的には created_at を条件にデータを集計します。
-- 1か月間の行動ログを type毎 に集計
select type, count(*)
from user_action_logs
where created_at between '2023-12-01 00:00:00' and '2023-12-31 23:59:59'
group by type
created_at に index が貼られている状態なのですが、対象の日付が広くなると、適切に index が使用されないという状況になりクエリの実行に多大な時間がかかる事になっていました。
なぜ index が効かないのか？について、詳細は省きますが、要因はデータ量が多いことになります。
考えられる対策
1. type と created_at の複合indexを作成する
こちらは type の種類数にもよるけど、かなり効果的だと思います。
ただし、サンプルの様な簡単な条件や集計の場合になります。
他の条件や集計が必要な場合は、その集計毎にindexが必要になります。
なお、indexをたくさん用意すれば、参照時には有効ですが、データ登録時にindexの情報も作成する必要があるので、データ登録時のコストが増えます。
2. created_at とは別に created_date という日付型(date)のカラムを用意し、そちらを検索条件にする
こちらもかなり有効な方法かと思います。
日時型(datetime) によりデータが細分化されていることがindexが無効となる条件のため、
日付型(date) のデータを用意する事により、日付単位のデータとなるため index が有効になる可能性がかなり高いです。
ただし、1レコードの必要バイト数が増加します。
数バイトですが、レコード数が多い場合は気になるかもしれないです。
今回行った対応
今回行った対策は、上記とは異なります。
- 日付毎のid範囲を別テーブルに登録する
- 集計クエリにてidの条件を追加する
前提として
- ログテーブルには
AUTO_INCREMENTの項目が存在する(id) -
id(AUTO_INCREMENTの項目) はPRIMARYである
具体的に、以下のテーブルを用意します。
user_action_logs_id_range
| date | min_id | max_id |
|---|---|---|
| 2023-12-01 | 10,000 | 15,253 |
| 2023-12-02 | 15,254 | 18,521 |
| 2023-12-03 | 18,522 | 23,584 |
| 2023-12-04 | 23,585 | 35,124 |
| 2023-12-05 | 35,125 | 55,201 |
| ... | ... | ... |
| 2023-12-31 | 154,751 | 156,214 |
上記テーブルのデータは、日次バッチにて、毎日1レコード追加する形です。
※ 日付が変わったタイミングで前日分のレコードを作成
データ集計をする前にIDの範囲を取得します。
select min(min_id), max(max_id)
from user_action_logs_id_range
where date between '2023-12-01' and '2023-12-31'
group by type
取得した結果を元に、集計クエリに条件を追加します。
-- 1か月間の行動ログを type毎 に集計
select type, count(*)
from user_action_logs
where created_at between '2023-12-01 00:00:00' and '2023-12-31 23:59:59'
and id between 10000 and 156214
group by type
id の条件を追加することで created_at の index ではなく PRIMARY である id の index が使用される。
なお、他に有効な index がある場合、適切な index が使われる事になります。
さいごに
今回行った対策は、IDの範囲テーブルを作成するという方法ですが、
その時々で適切な対応は異なりますので、
どの様な実行計画なのか？をちゃんと吟味して、その時々で最良の対策を取っていきたいですね。