読者です 読者をやめる 読者になる 読者になる

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

MySQL version : 5.5.38

目次

  1. 前書き
  2. 遅いクエリの見つけ方
  3. 解決策の決定
  4. チューニングの方法
    1. クエリの改善
    2. INDEX
    3. パーティション
    4. コマンドの大文字小文字
  5. おわりに


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
    ロックされた時間です。ロックとは、異なるセッションによる競合を防ぐため、他のクエリは実行されないような状態になっていることです。UPDATEINSERT実行中に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
      JOINONにおいて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した時に、keyNULLだったり、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. おわりに

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