Allied ArchitectsAllied ArchitectsEngineer Blog

MySQLでクエリチューニングことはじめ

2013/06/15 MySQL

最近、寒暖の差が激しいですがみなさん体調は崩されていないでしょうか?
こんにちわ。モニプラ for Facebookを担当しています高橋です。

サービス開始当初は問題なかったものの稼働が高くなりデータ量が多くなって
クエリのパフォーマンスが悪化すること…よくありますよね?

今回はクエリチューニングの基本的な手順とケース別に解決方法を解説したいと思います。

クエリチューニングの手順

1.スロークエリログで問題のクエリをあぶり出す

まずはどのクエリが問題なのか特定する必要があります。
アプリケーション側でクエリの実行時間を測定し自前でログを出力しておくというのも手ですが、
お手軽にMySQLの設定で一定時間以上掛かったクエリをログに出力しておくことができます。

スロー クエリ ログ(MySQL 5.1 リファレンスマニュアル)

mysqldを–log-slow-queriesオプションつきで起動するとデフォルトで10秒以上かかったクエリをログに出力することができます。
/{MySQLインストールパス}/data/{ホスト名}-slow.log
にファイルが作成されます。

またはmy.cnfで

と設定してもOKです。

ログの内容はこんな感じでクエリにかかった時間、対象レコード数などの情報が出力されます。

この形式では解析が少々面倒なので–log-outputオプションを付けて起動するもしくはmy.cnfにlog_output=TABLEを設定すると
以下のようなCSVで出力されるようになり、Excelなどで簡単に解析できるようになります。
一般クエリとスロー クエリのログ出力先の選択(MySQL 5.1 リファレンスマニュアル)

CSVは
/{MySQLインストールパス}/data/mysql/slow_log.CSV
に出力されるようになります。

2.EXPLAINでクエリを解析する

どのクエリが問題かわかったら、次にどうしてそのクエリのパフォーマンスが悪いのか
EXPLAINを使って解析します。

EXPLAINを使用して、クエリを最適化する(MySQL 5.1 リファレンスマニュアル)

EXPLAINを使うとそのクエリがどのような順番でどのような方法でテーブルを
検索するかを知ることができます。

例えばこんなテーブルがあり

person

カラム インデックス
id int(11) PRIMARY
name varchar(20)
gender varchar(1)
person_diary

カラム インデックス
id int(11) PRIMARY
title varchar(20)
content text
person_id int(11) INDEX

こんなクエリにEXPLAINを使用すると

このような結果が返ってきます。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE pd ALL NULL NULL NULL NULL 1035
1 SIMPLE p eq_ref PRIMARY,id PRIMARY 4 test.pd.person_id 1 Using where

見方を簡単に説明します。
まず1行が1テーブルに相当し、上から順に検索が実行されます。
どのように検索されるかはtypeとkeyとExtraを見るとわかります。

typeはALLがテーブルフルスキャン、それ以外がkeyで表示されたインデックスを使った検索となります。
ALLが出た際は要注意です。
またExtraにはその他の検索の挙動について捕捉情報が表示されます。
例えば”Using temporary”が出たときはテンポラリテーブルが作成され結果セットが一時的にディスクに保存されます。

各項目の説明は上記のリファレンスマニュアルにもありますが、漢のコンピュータ道さんのこちらの記事で
わかりやすく解説されていますので参考にしてください。
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

ケーススタディ

ケース① 検索条件のカラムにインデックスが張られていない

そんなバカなと思いますが、ケースとしては一番多いのではないでしょうか。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE person_diary ALL NULL NULL NULL NULL 1096 Using where

インデックスが効いてなく(keyがNULL)、テーブルフルスキャンが実行されてしまっています(typeがALL)。

person_idにインデックスを張ってみると…

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE person_diary ref index_person_id index_person_id 4 const 20

インデックスを使った検索に変わりました。
これでこのケースはOKです。

ケース② 複合インデックスが張られているけど使われない

こんなテーブルがあったとしてperson_idとperson_diary_idにはこの順で複合インデックスが張られているとします

person_diary_comment

カラム インデックス
id int(11) PRIMARY
person_id int(11) INDEX
person_diary_id int(11)
comment text
comment_person_id int(11) INDEX
インデックス

キー名 カラム
PRIMARY id
index_complex person_id
person_diary_id
index_comment_person_id comment_person_id

こんなクエリを投げます。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE pd const id id 4 const 1
1 SIMPLE pdc ALL NULL NULL NULL NULL 1471 Using where

person_diary_commentテーブルの検索がインデックスを張っているにも関わらずテーブルフルスキャンとなってしまいました。

なぜでしょうか。
MySQLのリファレンスにこのような記述があります。

テーブルに複合インデックスがある場合、オプティマイザではインデックスの左端の先頭部分のいずれかをレコードの検索に使用できます。たとえば、(col1, col2, col3)に 3 カラムのインデックスがある場合、(col1)、(col1, col2)そして(col1, col2, col3)に対して、インデックスの検索機能を使用できます。

MySQLにおけるインデックスの使用(MySQL 5.1 リファレンスマニュアル)

つまり複合インデックスの場合、順番が重要で上からセットで使わないと効きませんよという話です。
であれば今回は複合インデックスが効くようにperson_idを条件に加えてみます。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE pd const id id 4 const 1
1 SIMPLE pdc ref index_complex index_complex 8 const,const 1

複合インデックスが使われるようになりました。
今回は条件を追加してもビジネスロジック的には問題ないという想定ですが、
問題がある場合は個別にインデックスを張ることでも解消できます。

ケース③ ORDER BY をつかったクエリが遅い

次はORDER BYを使っていてパフォーマンスが出ない場合の話です。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE person_diary_comment ref index_complex index_complex 4 const 31 Using where; Using filesort

ExtraにUsing filesortが出ています。
これはソート処理にインデックスが使われず、メモリ上でソートされることを意味します。
対象件数が少ない場合はまだよいですが、多い場合はこれがUsing temporaryに変わり
上記で説明したように一時的にディスク上でソート処理が行われます。
そうなるとたった100件を取得するのに見合わない時間が掛かってしまいます。

ORDER BYのカラムにもインデックスが張られているにもかかわらず、Using filesortが出てしまっているのは、検索条件のカラムと異なるためです。

こんなときは複合インデックスを使えば解決できます。
 検索条件のカラム, ソートのカラム
の順で複合インデックスを作成すると、検索にもソートにもインデックスが使えるようになります。

今回はperson_idとcomment_person_idで複合インデックスを作ります。

インデックス

キー名 カラム
PRIMARY id
index_complex person_id
person_diary_id
index_comment_person_id comment_person_id
index_orderby person_id
comment_person_id
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE person_diary_comment ref index_complex,index_orderby index_orderby 4 const 31 Using where

インデックスを張ったら、Using filesortがなくなりました。
これでOKですね。

ケース④ 使えるインデックスがあるのにフルスキャンされてしまう

次のケースはインデックスが張られているカラムに対し範囲指定をしたときによく起こるケースです。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE person_diary_comment ALL index_comment_person_id NULL NULL NULL 1671 Using where

範囲指定しているカラムcomment_person_idには問題なくインデックスが張られていてpossible_keysにも表示されています。しかし、なぜか使われていません。

理由はこちらにあります。

MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% を超えるレコードにアクセスする必要が生じる場合が挙げられます(この場合は、必要なシークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなります)。
MySQLにおけるインデックスの使用(MySQL 5.1 リファレンスマニュアル)

つまり
 インデックス検索 → テーブル検索
より
 テーブルフルスキャン
の方が早いであろうとMySQLに判断されたということです。

試しにこのようなクエリに書き換えてみます。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE person_diary_comment range index_comment_person_id index_comment_person_id 4 NULL 1284 Using where; Using index

インデックスが効くようになりました。
これはSELECT句をインデックスが張られている抽出条件のカラムのみとすることで
 インデックス検索 → テーブル検索(不要)
となったのでインデックスを使った方が高速だろうと判断されたということです。

理屈はわかりましたがどのように解決すればよいのでしょう?
これは本当にケースバイケースになってしまいますが、仕様に無理があることが多いように思います。
可能であれば仕様自体を変えてしまいましょう。

最後に

5.6では解消する予定の問題ですが、こちらの記事も参考にしてください。
MySQLではIN句とサブクエリの組み合わせはインデックスが効かない!?

以上となります。
他にもたくさんパターンはあると思いますが、MySQLの挙動をイメージできるようになると
自ずと解決法が分かってくると思います。

tiger
  • tiger
  • Webエンジニア
  • 最近ではコードを書く機会がめっきり減って来てプログラマー35歳限界説に恐怖しています。 このブログを口実に無理矢理新しい技術に触れていきたいと思っています。

RELATION ENTRY

シェルスクリプトだけでMySQLのクエリ結果をメールで通知する

シェルスクリプトだけでMySQLのクエリ...

2014/10/9 Shell

こんにちは。tigerです。 8月のことになりますが僕がPM的な立ち位置で関わっているGreenSnapという新サービスをリリースいたしました。 今回はそのときに作成したシェルスクリプトを晒しちゃいたいと思います。 はじめに WebサービスにつきものなのがKPIですよね。 KPIは...

続きを読む

Facebook発の新言語HackをHeroku上で動かす

Facebook発の新言語HackをHe...

2014/06/17 PHP

こんにちは。tigerです。 最近ではブログのネタが思いつかず、このブログを引退しようかと本気で考えています。 今回は奇跡的に思いついた「Hack × Heroku」をお送りしたいと思います。 Hackとは http://hacklang.org/ 2014年3月に発表されたFac...

続きを読む

tigerの記事一覧へ

ページTOPへ