SQLチューニングことはじめ

2015.06.11

今回はMySQLを使っていて、DBの負荷が上がったときどこから手をつけるべきかを書いていきます。
どのように修正すべきかは、プロジェクトの特性によって左右されると思うので、そこは工夫してみてください。

1.SQLログを出力する

SlowQueryを出力して確認してみましょう。
SlowQueryの設定はMySQLのバージョンによって違いがあるので、
公式ページを確認して設定してみましょう。

1-1. 設定ファイル編

MySQL5.6の/etc/my.cnf設定例

[mysqld]
slow_query_log=1
long_query_time=5
slow_query_log_file=/var/log/mysql/slow.log
log_queries_not_using_indexes

slow_query_log

0のときはログを取得しない。1のときはログを取得

long_query_time

ログに出力するSQLの実行時間(秒単位)。
今回はSQL実行時に5秒以上かかっているSQLをログ出力する。

slow_query_log_file

ログファイルの出力先
今回は/var/log/mysql/slow.logに出力する。

log_queries_not_using_indexes

long_query_timeで設定した超過時間を超えていなくても、フルスキャンやフルインデックススキャンが行われているSQLを出力する。

上記のようにmy.cnfを設定して、mysqlを再起動すると設定が反映されます。

1-2. 直接設定編

MySQL5.6での実行例

mysql> set global slow_query_log = 1;
mysql> set global slow_query_log_file = '/var/log/mysql/slow.log';
mysql> set global long_query_time = 5;
mysql> set global log_queries_not_using_indexes = 1;

上記のようにSQLを実行することで、MySQLを再起動せずに設定を反映させられます。
ただしアプリケーションがコネクションをつかんでいる状態である場合は、コネクションが再度生成されるタイミングか、アプリケーションの再起動によって設定が反映されます。

また注意してほしい点として、本番環境などで実行すると、大量のログ出力が発生しデータベースサーバの負荷を跳ね上げる可能性があるため慎重に検討して実行してください。

※show global variablesを実行するとデータベース全体の設定情報を確認することが出来ます。
show global variables like ‘%slow%’などLike文で設定ファイルと同様の文言を検索すると現在の情報が確認できます。
設定が反映されていないと思われる場合などは確認してみましょう。

1-3. MySQLの起動時オプション編

MySQL5.6での実行例

/etc/rc.d/init.d/mysqld start --slow_query_log=1 --slow_query_log_file='/var/log/mysql/slow.log' --long_query_time=5 --log_queries_not_using_indexes=1

また他にも色々なログを見たいときは公式ページの記載を参考に設定を変更してみましょう。

2. SlowQueryを見る

これで実行時間が遅い、ボトルネックになっているSQLが見えてくるようになりました。
今度はSlowQueryとして出て来たSQLに対して解析していきましょう。

• 同じSQLが過剰に複数回発行されていないか。
• クエリ文字列が異様に長くなってないか。

まずはSQLログををみてすぐ分かるものから確認しましょう。

SQLログをそのまま眺めても良いのですが、ある程度整形したほうが見やすいので、mysqldumpslowを使いサマリーしてみましょう。
mysqldumpslowの詳細は公式へ

mysqldumpslow {SlowQueryログ} > ~/slowquery.dump

適宜オプションを設定して内容を確認すると、実行回数や検索時間などが整形された状態で出力されるのでわかりやすいです。

• 複数回SQLが発行されると、その分データベースに負荷がかかるので、減らしましょう。

内容が同じにも関わらずアプリケーション側のプログラムの書き方で複数回問い合わせをしてしまっている場合があります。
プロジェクトの規模が大きかったり、運用期間が長いとよくみかけます

• クエリ文字列が長いとSQL文の解析量が多くなるので不要な部分は削りましょう。

この二つは比較的簡単にわかるのでバグの出ないように順次修正するといいでしょう。

• 実行時間が長くないか。
• Indexは効いているか。
• 過剰なIndexになってないか。
• パーテション跨ぎ検索になってないか。

次に先ほどサマリーしたSlowQueryのSQLに対してそれぞれEXPLAINをかけていきましょう。
実際にどのように修正するかはそのプロジェクトごとの特性で変わってくると思いますので、今回はEXPLAINについて解説していきたいと思います。

EXPLAIN自体は実行計画と言われるように、オプティマイザがどのように実行されるのかの計画を表示してくれる機能ですが、さまざまな制限があり常に正しいとは限りません。
またEXPLAINをつけると実際にはクエリを実行しないと思われる方もいるようですが、MySQLではサブクエリを実際に実行して計画取得を行っています。
むやみに本番環境で実行するとサーバに負荷をかけることになるので、慎重に行いましょう。
ただ、調査分析をする場合の情報として効果的なので、EXPLAINの結果を足がかりに調査を進めるといいでしょう。
※MySQL5.5以前はSELECT以外は利用できない

出力例

mysql> EXPLAIN SELECT * FROM user_movie_views umv JOIN users u JOIN movie m JOIN (SELECT * FROM agency WHERE establishment_at BETWEEN '1980-01-01' AND '1980-06-01') a WHERE u.id = umv.user_id AND m.id = umv.movie_id AND a.id = m.agency_id;
+----+-------------+------------+--------+------------------------------------+---------+---------+-------------------------+-------+----------------------------------------------------+
| id | select_type | table      | type   | possible_keys                      | key     | key_len | ref                     | rows  | Extra                                              |
+----+-------------+------------+--------+------------------------------------+---------+---------+-------------------------+-------+----------------------------------------------------+
|  1 | PRIMARY     | umv        | ALL    | NULL                               | NULL    | NULL    | NULL                    | 12219 | NULL                                               |
|  1 | PRIMARY     | m          | eq_ref | PRIMARY,idx_id_agency_id,id_UNIQUE | PRIMARY | 4       | sample.umv.movie_id     |     1 | NULL                                               |
|  1 | PRIMARY     | u          | ref    | PRIMARY,id_UNIQUE                  | PRIMARY | 4       | sample.umv.user_id      |     1 | NULL                                               |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                               | NULL    | NULL    | NULL                    |    11 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | agency     | ALL    | idx_establishment_at               | NULL    | NULL    | NULL                    |    23 | Using where                                        |
+----+-------------+------------+--------+------------------------------------+---------+---------+-------------------------+-------+----------------------------------------------------+

※possible_keys, key, key_len, ref がすべてNULLの場合、
インデックスがまったく使用されていないということになります。

EXPLAINは2つのバリエーションがある。

・EXPLAIN EXTENDED
SHOW WARNINGSをすることで、オプティマイザによるクエリの書き換え結果などを見ることができます。
・EXPLAIN PARTITIONS
対象のSQLが検索するパーテション情報を返します。

次に読み解き方について簡単に説明します。

id列

ツリー構造のIDでselect_typeと合わせてツリー構造を意識して読むための情報

select_type列

UNIONとSUBQUERYには以下の二つのパターンがある。

table列

アクセスしているtable名

type列

アクセスタイプ

possible_keys列

クエリに使用可能なIndex名称。(ただし、オプティマイザが最適化段階の初期に作成する項目なので、実際にはIndexが使われていない可能性があるため、注意が必要。)
行の検索を効率化するために利用するKey

key列

オプティマイザにより選択されたKey
クエリコストを最小化するために利用するKey

key_len列

Indexに使用するバイト数
検索時のIndex対象が短ければ短いほど検索は早くなるので、Index作成時などには考慮したほうがいい。

ref列

key列で指定しているindex値を調べるときに使う。
定数(const)または対象の列名

rows列

行数の見積もり。
基本的には正確な値ではない。
ただし、前述した通りFrom句のサブクエリ部分は実際に実行した結果を解析するため、正確な値である。

Extra列

今まで紹介した項目以外の追加情報

これでEXPLAINの使い方はおおむね理解できたかと思います。
一番気をつけて欲しいことは、条件やサブクエリ、インデックスなど様々な要因により、ボトルネックが発生しており、必ず解決できる特定の銀の弾丸は存在しません。
データベースは年々アップデートされており、そのデータベースの種類やバージョンによって検索の最適化方法が、異なる場合が多々あります。
そのため、前述したEXPLAINなどを用いて、しっかりと検証しながら最適化を進めましょう。