ZENKIGEN「harutaka」開発チームの川俣です。私が開発に携わっている採用DXサービス「harutaka」で発生したスロークエリを改善した際のプロセスを書いていきます。
SQLチューニングの手順
SQLチューニングは大まかに3つの手順で行っていきます。
1. スロークエリの検知
MySQLの設定ファイルからスロークエリログを出力するようにすることで検知できます。
弊社サービス「harutaka」ではDatadogによる監視をおこなっていて、レイテンシが閾値を超えるスロークエリが発生した場合Slackにアラートが飛ぶようになっています。
アラートから発生したスロークエリを確認します。
今回発生したスロークエリはこちらです。（テーブル名/カラム名は適宜変えています）
SELECT
*
FROM
hoges
WHERE
cloumn_1 = 1
AND
cloumn_2 = 2;
2. 原因の調査
実行計画の確認
検知したスロークエリの原因を突き止めるため実行計画を確認します。
クエリの先頭にEXPLAINをつけて実行します。
EXPLAIN
SELECT
*
FROM
hoges
WHERE
cloumn_1 = 1
AND
cloumn_2 = 2;
実行計画はこちら
| id | table | select_type | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | hoges | NULL | ref | index_hoges_on_column_1,index_hoges_on_column_2 | index_hoges_on_column_2 | 1 | const | 100 | 100.0 | Using where |
実行計画で特に見なければいけないのはtypeの列です。
スロークエリの原因はインデックスであることが多いのでまずはここを確認します。
■ 代表的なtype
| type | 意味 |
|---|---|
| const | Primary keyもしくはユニークインデックスによるアクセス |
| eq_ref | JOIN時にPrimary keyもしくはユニークインデックスを利用したアクセス |
| ref | ユニークではないインデックスによる等価検索 |
| range | インデックスを用いた範囲検索 |
| index | フルインデックススキャン |
| ALL | フルテーブルスキャン |
スロークエリの場合typeがindexとALLとなっていることが多いです。
この場合はインデックスが利用できていないので、where句で指定されているカラムにインデックスが作成されているか？などを調査して対応する必要があります。
しかし、今回の例ではrefとなっていてスロークエリの原因は見当たらないように思えます。
その場合はさらに調査するためテーブルのインデックス情報を見ていきましょう。
インデックス情報の確認
確認方法はこちら
SHOW INDEX FROM hoges;
インデックス情報はこちら
| Table | Non_unique | Key_name | Seq_in_index | Cloumn_name | Collation | Cardinality | Sub_part | Packed | Null | index_type | Comment | index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| hoges | 0 | PRIMARY | 1 | id | A | 1000000 | NULL | NULL | BTREE | |||
| hoges | 0 | index_hoges_on_column_1 | 1 | column_1 | A | 500000 | NULL | NULL | YES | BTREE | ||
| hoges | 0 | index_hoges_on_column_2 | 1 | column_2 | A | 5 | NULL | NULL | YES | BTREE |
SQLチューニングの際はCardinalityに着目します。
Cardinalityとは、カラムに格納されているデータの種類がどのくらいあるのか(カラムの値の種類の絶対値)を指しています。
基本的にインデックスのCardinalityが高いほど絞り込みが高速化され、パフォーマンス改善につながります。
改めてインデックス情報を見ると、スロークエリでインデックスとして利用されているindex_hoges_on_column_2のCardinalityは5に対してもう一つのインデックスindex_hoges_on_column_1は500000となっていて、Cardinalityが低いインデックスが利用されていることがわかります。これがスロークエリの原因のようです。
なぜCardinalityが低いインデックスが利用されてしまうのかというと、MySQLのオプティマイザを理解する必要があります。
オプティマイザ
オプティマイザとは具体的なデータの取得処理（実行計画）をクエリ発行ごとに生成する機能です。
なぜ実行計画をクエリ発行ごとに生成するかというと、統計情報によって最適な実行計画が変わるためです。
MySQLのオプティマイザは、以下の統計情報を元に実行計画を作成します。
• テーブルに含まれる行数
• 各インデックスのサイズ（ページ数）
• 各インデックスのリーフページのサイズ（ページ数）
• 各インデックスのカーディナリティ（何種類の値が存在するか？）
例えば今回のクエリでfrom句に指定しているhogesテーブルのレコード数が1行しかない場合、インデックスを使わない実行計画の方が無駄なアクセス処理が無いので、インデックスを使わない時に比べ実行速度は早くなるでしょう。
一方レコード数が数万行もある場合は、インデックスを使った方が効率良く該当レコードを絞り込めるので、インデックス無しより早くなります。
つまりクエリが同じでも統計情報が変わればオプティマイザの作成する実行計画は変わり、パフォーマンスも大きく変わる可能性があります。
そしてオプティマイザの判断は間違うこともあり、必ずしも最適だとは限りません。
オプティマイザがより良い実行計画を作成できるように、SQLチューニングを行う必要があります。
3. スロークエリの改善
今回のスロークエリの原因はCardinalityが低いインデックスが利用されていたからでした。
これに対しての改善案として、既存インデックスでよりCardinalityが高いインデックスを利用させることにしました。
インデックスヒント
MySQLではオプティマイザに対してインデックスの選択を変更させるインデックスヒントがあります。
今回はFORCE INDEXを利用してクエリで利用するインデックスを指定します。
SELECT
*
FROM
hoges
FORCE INDEX (index_hoges_on_column_1)
WHERE
cloumn_1 = 1
AND
cloumn_2 = 2;
実行計画を見ると
| id | table | select_type | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | hoges | NULL | ref | index_hoges_on_column_1,index_hoges_on_column_2 | index_hoges_on_column_1 | 1 | const | 100 | 100.0 | Using where |
インデックスにFORCE INDEXで指定したindex_hoges_on_column_1が利用されていますね。
このSQLで実行時間を計測するとスロークエリの時と比べて数百倍短縮されていました。
インデックスヒントは便利な一方、動的にインデックスを選択してくれるオプティマイザの利点を消してしまう部分もあるので使い方には注意が必要です。
where句が動的に変わるようなクエリの場合はテストをしっかり行い、逆にパフォーマンスが低下することがないか確認しましょう。
まとめ
今回はスロークエリ改善のプロセスをご紹介しました。参考頂けると嬉しいです。
お知らせ
少しでも弊社や harutaka 、revii に興味を持っていただいたという方は、お気軽にご連絡頂けると幸いです！カジュアルにお話という形でも、副業したいという形でも大歓迎です。
参照
Discussion