1年目による1年目のためのMySQLチューニング手順

MySQL version : 5.5.38

1. 前書き

こんにちは、入社1年目の紺野です。
入社後、予約TOP10チームにJOINしてから、MySQL関連の改修を任されることが結構ありました。大学ではPostgreSQLを習っていたのですが、実務として取り組むと、ただ受け身でやっている時よりも知識のUPDATEが早いですね。
今回は1年目の自分が取り組んできた、遅いクエリの見つけ方から改善までの手順と解決法をSELECTしましたので、記事として書かせて頂きます。
どうでもいい前置きはここまで本題に入りましょう。

2. 遅いクエリの見つけ方

MySQLにはスロークエリログという、結果が返ってくるのが遅いクエリをログとして残してくれる機能が備わっています。まずはこのログを出力する設定をして、チューニングすべきクエリを洗い出しましょう。

スロークエリログの設定がどうなっているかは、MySQLコンソールからSHOW VARIABLESコマンドで確認できます。

mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

slow_query_log
ログを出力するか否かの設定です。OFFになっていますのでスロークエリログは吐き出されない状態です。
slow_query_log_file
ログが書き込まれるファイルの場所です。
long_query_time
この値を超えると遅いクエリとして判断され、ログに出力されます。単位は秒です。

スロークエリログの設定

コンソールから設定
コンソールからは、以下のコマンドを入力することにより設定できます。

mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
mysql> SET GLOBAL long_query_time = 1;
mysql> SET GLOBAL slow_query_log = ON;

my.cnfから設定
設定ファイルからは、rootユーザーで編集します。

# vi /etc/my.cnf

以下を追記

# slow query
##
slow_query_log
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

これでlong_query_timeで設定した値以上の時間がかかったクエリをログとして残してくれます。

スロークエリログの見方

実際にログを見ていきましょう。

# less /var/log/mysql/mysql-slow.log

# Time: 170118 16:51:22
# User@Host: root[root] @ localhost []
# Query_time: 21.486755  Lock_time: 0.000033 Rows_sent: 10057  Rows_examined: 2256448
SET timestamp=1484725882;
SELECT t1.id, t1.fiald1, t2.fiald1 FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.table1_id WHERE t2.fiald1 IS NOT NULL ORDER BY t2.fiald1;
※実際のDBの内容とはもちろん異なります

Time
クエリが実行された日時です。この場合は2017年1月18日 16:51:22ですね。
User@Host
ユーザーIDとリクエストした端末です。ここを見ればどのサーバーでクエリがたたかれたかが分かります。
Query_time
クエリの結果が帰ってくるまでの時間です。先程設定したlong_query_timeの値以上の数値になっているはずです。21秒ってやばいですね。21秒間ページが表示されなければ一般的にユーザーはキレます。
Lock_time
ロックされた時間です。ロックとは、異なるセッションによる競合を防ぐため、他のクエリは実行されないような状態になっていることです。UPDATEやINSERT実行中にSELECTなどしてしまうと正確な結果でなくなってしまいます。
Rows_sent
クエリにヒットした件数です。
Rows_examined
処理対象となった行数です。
SQL文
実行されたクエリです。これが改善すべきクエリということになります。

3. 解決策の決定

EXPLAINコマンドを使って調べていきましょう。このコマンドはクエリの先頭につけることによって、どういった手順でデータを取得するかの実行計画を確認できます。実際にクエリがたたかれ、結果が返ってくる訳ではないので安心して使ってください。
ただし、サブクエリのみ実行してみないと見積もりができないようで、重いサブクエリを使うとEXPLAINも返ってくるのが遅くなります。

実行例

f:id:AdwaysEngineerBlog:20170120181349p:plain

どういった項目をチェックしてチューニング方法を練るかを先に申しますと、以下のようになります。

①最初にデータを取ってくるテーブルのrowsを減らせそうか
②インデックスが使われているか否か
③ExtraにUsing filesortやUsing temporaryが入っていてそれを取り除けないか

ではこのチェック項目の判断を下すうえで必要な情報の説明をしていきましょう。

idとselect_type
この2つはクエリ内での実行順を表しています。JOINのみから構成されるクエリの場合単純に上から順に実行されますが、サブクエリ、UNIONなどが絡んでくる場合は順番が変わってきます。最初に取得するデータ量(後述するrowsの数)が少ないほどクエリ全体の処理が早くなるため、実行順を理解することはチューニングの手助けになるでしょう。
table
対象のテーブルを表します。
type
かなり端折って説明すると、インデックス使っているか否か、インデックスをどう使っているかを表す項目です。
- const
  PRIMARY KEYまたはユニークインデックスによる等価検索(例:WHERE id = 1)。最速。
- eq_ref
  JOINのONにおいてPRIMARY KEYまたはユニークインデックスが使われている状態。早い。
- ref
  ユニークでないインデックスによる等価検索(例:WHERE age = 18)。いい感じに早い。
- range
  インデックスによる範囲検索(例:WHERE age BETWEEN 13 AND 15)。わりと早い。
- index
  indexとあるので早そうですが、これはインデックス全体をスキャンしているため、まぁまぁ早い程度です。
- ALL
  インデックスを全く使わず、テーブル全体をスキャンするため最遅。改善すべき。
possible_keys
使用する候補として挙げられたキーやインデックスです。
key
どのキーやインデックスが使われたか。貼ったインデックスが実際に活用されているかがわかります。
key_len
使われたキーの長さ。キーの長さが短いほど高速なのでインデックスをつけるカラムを選ぶ時などは注意してください。
rows
そのテーブルから抽出したカラム数の大まかな値。EXPLAINは実際に実行するわけではないので予測数を出してくれます。また、この値はWHEREを適用する前なので、検索条件を設定している場合の実行結果はおおよそこれより少なくなります。ただ、先程書いたようにサブクエリは実際に実行してみないと結果の数がわからないため、この場合は正確な値が出てきます。
Extra
クエリを実行するために使用する方法がここに記載されます。数が多いのでよく見るものをここでは説明します。
- Using where
  WHEREを使っており、インデックスを使っただけでは絞り込めない場合にでます。
- Using index
  インデックスだけをもって絞りこみ、ソートできている場合にでます。かなり早いはずです。
- Using filesort
  テーブルからデータを取ってきた後にクイックソートを使ってソートしている場合にでます。クイックソートとかいう名前ですが取ってきたデータが多い場合、ソートに時間がかかるため殆どの場合遅いです。
- Using temporary
  結果をソートしたりするのにテンポラリテーブル(仮のテーブル)を作る場合に出ます。Using filesortと同じく遅くなることが多いので注意が必要です。

4. チューニングの方法

さあいよいよチューニングに入っていきます。
EXPLAINで表示されたrowsを減らすことを目標とし、以下のような改修を加えましょう。

1. クエリの改善

JOINやサブクエリ、UNIONを使っている場合は、それぞれ対応するテーブルからデータを取ってくる順番が決まります。その時重要なのが、最初に取ってきたデータの量です。このデータ量が多いと、次に取ってくるデータとマージするときに見る行が増えるため実行速度が落ちます。
10万件のユーザーテーブルと100万件の予約テーブルをINNER JOINする時、予約テーブルから始めるよりもユーザーテーブルから始める方が早くなるでしょう。

2. INDEXを貼る

EXPLAINした時に、keyがNULLだったり、WHEREで指定した条件のカラムにINDEXが貼られていない場合は検討しましょう。WHERE の他に、JOINの条件で指定されているカラムにも貼ると効果が出ます。

今までさんざん出てきたINDEXですが、とりあえず貼りまくれば良いというわけではありません。INDEXを貼ったカラムはメモリ上にのるため、貼りすぎるとMySQL以外のプロセスを圧迫し、メモリが溢れてしまう危険があります。よって、本当に使うものにのみINDEXを貼るようにしましょう。

3. パーティション

データ量が膨大になると、クエリの改善やINDEXを貼っても遅いときがあります。そんな時は、検索条件のカラムのパーティションを切ると良いでしょう。

4. コマンドの大文字小文字

MySQLはクエリの実行手順としてまずクエリの読み込みをし、その時コマンドが小文字で入力されていた場合、それを大文字に置き換える処理が入ります。なのでこの処理を削るため、SQLのコマンドは大文字で入力しましょう。とはいっても今のコンピュータの性能からすると無視しても問題ない程度らしいのでおまじない程度に考えておいてください。ECサイトの表示速度が1秒早くなっただけで売上1200億円上がったなんて話もあるので0.0000001秒早くなれば年1万円くらいの収入アップになるでしょう。

結果

チューニングを試してみた結果、rowsは減りましたでしょうか。そうしたら実際にクエリを叩いて実行速度を比べてみましょう。早くなってたら良いですね。

ただここで一つ注意してほしいことがあります。
MySQLではクエリの実行結果をキャッシュしておく機能があるので、同じクエリを叩くと0.0秒で返ってきます。クエリのテストは何回か叩くと思うので、いきなり早くなってワロタと混乱することを防ぐためにSQL_NO_CACHEを使いましょう。

SELECT SQL_NO_CACHE t1.id, t1.fiald1, t2.fiald1
  FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.table1_id

5. おわりに

いかがだったでしょうか。初めてのエンジニアブログだったので要領を得ていないかもしれませんが、少しでも同胞の助けになればと思います。

Adwaysエンジニアブログ