Edited at

MySQL スロークエリ改善 初心者向け


概要

スロークエリを改善するために、年末くらいから勉強会をやっているのでその内容を一度まとめてみる。


環境


  • MySQL 5.6.22


スロークエリって?

ともかく遅い実行Queryということと、これから説明を入れる「実行計画」の「type」が「ALL」となっているものをそう言って良いのではないかと思っている。


スロークエリ改善のステップ


  1. スロークエリを見つける

  2. スロークエリ改善


スロークエリ改善に重要な言葉


  1. 実行計画

  2. インデックス


改善方法

後ほど解説するが


  1. クエリ自体を変更する(ただし、結果は変わらないようにする)

  2. インデックスをテーブルに貼る

が主な方法となる。

他にも実行自体を早くするための方法は存在するけれど、それはMySQLの方法とはかけ離れるし、一時的な対応となることも多いので割愛。


まずはスロークエリを見つける

そもそもスロークエリを簡単に見つけられるようにしておく必要がある。

MySQLには便利なことにその設定がある。

mysql> show variables like 'slow%';

+---------------------+----------------+
| Variable_name | Value |
+---------------------+----------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | mysql-slow.log |
+---------------------+----------------+

slow_query_log がOFFになっているとスロークエリの出力は行われない。

また、何秒以上がスロークエリとして吐き出されるかは以下のようになります。

mysql> show variables like 'long%';

+-----------------+-----------+
| Variable_name | Value |
+-----------------+------------+
| long_query_time | 10.000000 |
+-----------------+-----------+

上記の場合だと、slow_query_logがONのとき、long_query_timeを超えるクエリがスロークエリとして出力される。

ちなみに設定されていない場合は以下のように設定することができる。


MySQLのコンソールからの場合

mysql> set global slow_query_log_file = '/tmp/mysql-slow.log';

mysql> set global long_query_time = 5;
mysql> set global slow_query_log = ON;


my.cnfからの設定

my.cnf にパラメータを設定してスロークエリのログを出力する。

root権限で設定ファイルを編集する。(というか/etc以下はそうであろう・・・)

[mysqld]

slow_query_log=ON
long_query_time = 5
slow_query_log_file = /tmp/mysql-slow.sql

上記は「/tmp/mysql-slow.log」に5秒以上のスロークエリを出力するという設定。是非お試しあれ。

設定ファイルを更新したら、変更を反映するために以下コマンド(mysqldがserviceに登録されているならば、そちらの方が良いでしょうが)mysqldの再起動を忘れずに。

/etc/init.d/mysqld restart

ただこれだけだと実は最初のデータが少ないうちはクエリに問題があっても検出することができない。それを検出するために、実行計画というものが大事になってくる。


実行計画

実行計画とは文字通りMySQLを実行する際にどのように実行するかをあらわしたもの。

これが良い計画になっていると、データ量が大きくなっても、スロークエリにならずにパフォーマンスを発揮してくれる。また、それを補助してくれるものとして「インデックス」というものが存在する。インデックスに関しては後述。


実行計画の実例

実行計画を実際に見るにはSQL文の前に、「EXPLAIN」とつければ良い。例えば以下のような感じ。

mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code = C1.CountryCode;

+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 237 | |
| 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
| 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
3 rows in set (0.00 sec)

色々見るべきところはあるが、初心者がまず見るべきはidとselect_typeとtypeというカラム。ここが特に重要になってくる。


id/select_type

これについては見る前に場合分けして考える必要がある。


  1. JOINがある場合

  2. サブクエリがある場合

  3. UNIONがある場合

これらを場合分けして表示を見ると、どのテーブルにどの順番でアクセスされるのかを理解することができる。

idとselect_typeはEXPLAINの最初の2つのフィールドであるが、これらはセットにして考えると良い。select_typeはクエリの種類を表すものであり、ズバリツリーの構造にそのまま反映される。クエリの種類とはJOIN、サブクエリ、UNIONおよびそれらの組み合わせで、select_typeの内容もその組み合わせから導き出されたものなのである。


1. JOINの場合

MySQLが実行出来るJOINの種類はNested Look Join(NLJ)の一種類しかない。NLJとは例えばA、B、Cという3つのテーブルをJOINする際、最初にテーブルAから条件にマッチする行を全てフェッチして、次にBから条件にマッチする行をフェッチしてJOINし、次にCから条件にマッチする行をフェッチしてJOINする・・・というように、テーブルを一つずつ順に処理していく方式である。MySQL 6.0ではBKA JOINというのが追加されるが、これもNLJの発展系である。(JOINの方式にはソートマージやHASH JOINなどがあるが、MySQLには実装されていない。)

クエリがJOINだけから構成される場合、select_typeはSIMPLEと表示される。如何に複雑なJOINであってもCOMPLEXとはならずにSIMPLEなのである。従って「これはシンプルなクエリを示すのだ」などと誤解をしてはならない。SIMPLEではidが全て同じ値になる。これはそのクエリが一つのNLJで処理されることを示すからである。NLJではどのテーブルから処理するのかということが最も重要になるが、EXPLAINの出力の順序がどのテーブルから処理するかということを反映している。


2. サブクエリの場合

サブクエリが絡むと次のselect_typeには次の5種類のうちいずれかが表示される。

PRIMARY・・・外部クエリを示す。

SUBQUERY・・・相関関係のないサブクエリ。

DEPENDENT SUBQUERY・・・相関関係のあるサブクエリ。

UNCACHEABLE SUBQUERY・・・実行する度に結果が変わる可能性のあるサブクエリ。

DERIVED・・・FROM句で用いられているサブクエリ。

サブクエリの場合は実行順序に気をつける必要がある。DERIVEDの場合、サブクエリ→外部クエリの順番でクエリが実行される。例えば次のような場合はCityテーブルから最初に行がフェッチされてテーブルとなり、その次にCountryテーブルとのJOINが実行される。


3. UNIONの場合

次の5種類のいずれかがselect_typeに表示される。

1. PRIMARY => UNIONにおいて最初にフェッチされるテーブル

2. UNION => 2番目以降にフェッチされるテーブル
3. UNION RESULT => UNIONの実行結果
4. DEPENDENT UNION => DEPENDENT SUBQUERYがUNIONになっている場合
5. UNCACHEABLE UNION => UNCACHEABLE SUBQUERYがUNIONになっている場合

UNIONは前から順番に処理されていくだけなので、テーブルが処理される順序という観点ではわかり易いと言えるだろう。


type

select_typeの次に意識しなければいけないのは、typeフィールドである。このフィールドはレコードアクセスタイプとも呼ばれ、対象のテーブルに対してどのような方法でアクセスするかを示す。致命的なクエリはこのフィールドを見れば一目で分かるのでとても重要なフィールドである。よく見かけるものは次の通り。


  • const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。

  • eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。

  • ref・・・ユニーク(PRIMARY or UNIQUE)でないインデックスを使って等価検索(WHERE key = value)を行った時に使われるアクセスタイプ。

  • range・・・インデックスを用いた範囲検索。

  • index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。

  • ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。

indexまたはALLを見かけたらすかさずクエリをチューニングしよう。


引用元:

http://nippondanji.blogspot.com/2009/03/mysqlexplain.html


インデックス

インデックスとはざっくり言うとSQLを早く実行するための仕組みのこと。MySQLではBtreeと言うアルゴリズムを使って実装されている。詳しいところはQiitaで「MySQL インデックス」で調べるとたくさん出てくるので、調べてみると良い。以下は参考記事一覧。良い記事抜けてるよ!などありましたら教えていただきたく。


参考記事一覧

B-treeインデックス入門 | @kiyodori さん

【MySQL】マルチインデックスの制約の話とか | @rm-rf-slant さん

[MySQL]インデックスによるチューニング〜ログ出力、EXPLAIN、インデックス〜 | @shuntaro_tamura さん

SQLチューニング: ソートを発生させないインデックス設計 | @emotu さん


インデックスをどう貼ったら良いのか?

はじめのうちは色々貼ってみるのが良いかなと。

入り口としてちょうど良いのがtype = ALL となっているクエリ、またはサブクエリ。

一つ一つ自分が作ったものでやってみると良いです。


type ALLとなっている原因の切り分け


  1. まず、WHERE句に使っている箇所を徐々に削除していく

  2. そこでtypeがALLじゃ無くなったらそこが原因の一つ(それが原因のすべてでない事も多い)

  3. 原因となったカラムにインデックスを貼ってみる!→早くなったらそれで良い

  4. サブクエリを無くしてみる→サブクエリでもWHERE句やSELECTなら出力カラムの調整とか。JOINなくすとか。

上記を一度試してみると良いかも。

JOINしているのであれば、


注意点

この記事ではインデックスを貼ってみよう!とか簡単に書いている。

が、インデックスを貼るときにやっていることはALTER TABLE。

ということは、実サービスを運用している場合において、

ユーザがそれなりに集まっている状態だと、簡単にできないことも多い

実際に動いているサービスでやる場合にはインデックスだけでなく、

サブクエリを使わない形にSQLを変更してみる(十分に検証は必要だが)なども

考慮に入れると良い。


スロークエリ改善まとめ記事

MySQL スロークエリ リンク集