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列
- SIMPLE
- サブクエリやUNIONなどが含まれていない状態
- SUBQUERY
- FROM句ではなくSELECTリストにサブクエリが含まれている状態
- DERIVED
- FROM句にサブクエリが含まれている状態
- UNION
- UNIONを含む状態
UNIONとSUBQUERYには以下の二つのパターンがある。
- DEPENDENT
- 外側のクエリに依存している場合
- UNCACHEABLE
- Item_cacheがされていない場合
table列
アクセスしているtable名
type列
アクセスタイプ
- ALL
- 全件検索
- index
- indexを用いた全件検索
- range
- 範囲検索
- ref
- UNIQUEKey以外での単一行取得
- eq_ref
- JOIN時のUNIQUEによる単一行取得
- const
- PRIMARYまたはUNIQUEによる単一行取得
possible_keys列
クエリに使用可能なIndex名称。(ただし、オプティマイザが最適化段階の初期に作成する項目なので、実際にはIndexが使われていない可能性があるため、注意が必要。)
行の検索を効率化するために利用するKey
key列
オプティマイザにより選択されたKey
クエリコストを最小化するために利用するKey
key_len列
Indexに使用するバイト数
検索時のIndex対象が短ければ短いほど検索は早くなるので、Index作成時などには考慮したほうがいい。
ref列
key列で指定しているindex値を調べるときに使う。
定数(const)または対象の列名
rows列
行数の見積もり。
基本的には正確な値ではない。
ただし、前述した通りFrom句のサブクエリ部分は実際に実行した結果を解析するため、正確な値である。
Extra列
今まで紹介した項目以外の追加情報
- Using Index
- テーブルアクセスを回避するためにカバリングindexを使用する
- Using where
- indexではwhere句の条件をすべて適用できない場合
- Using temporary
- ソートするときに一時テーブルを使用する
- Using filesort
- Index順序でのソートではなく、外部指定のソートを行う
これでEXPLAINの使い方はおおむね理解できたかと思います。
一番気をつけて欲しいことは、条件やサブクエリ、インデックスなど様々な要因により、ボトルネックが発生しており、必ず解決できる特定の銀の弾丸は存在しません。
データベースは年々アップデートされており、そのデータベースの種類やバージョンによって検索の最適化方法が、異なる場合が多々あります。
そのため、前述したEXPLAINなどを用いて、しっかりと検証しながら最適化を進めましょう。
CONTACT
お問い合わせ
あなたの「想い」に挑戦します。
どうぞお気軽にお問い合わせください。
受付時間:平日9:00〜18:00 日・祝日・弊社指定休業日は除く
