tiger

2013.06.15

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

MySQL

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

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

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

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

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

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

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

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

またはmy.cnfで

slow_query_log=1 # またはON
long_query_time=5 # 5秒以上かかるクエリを対象にする

1 2	slow_query_log=1 # またはON long_query_time=5 # 5秒以上かかるクエリを対象にする

と設定してもOKです。

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

# Time: 130601 10:10:10
# User@Host: root[root] @ localhost []
# Query_time: 13.535508  Lock_time: 0.000024 Rows_sent: 1  Rows_examined: 7847403
SELECT * FROM table WHERE column = 0;

# Time: 130601 10:10:10

# User@Host: root[root] @ localhost []

# Query_time: 13.535508 Lock_time: 0.000024 Rows_sent: 1 Rows_examined: 7847403

SELECT * FROM table WHERE column = 0;

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

"2013-06-01 12:12:12","root[root] @ localhost []","00:00:13","00:00:00",1,7847403,"database",0,0,1,"SELECT * FROM table WHERE column = 0;

1	"2013-06-01 12:12:12","root[root] @ localhost []","00:00:13","00:00:00",1,7847403,"database",0,0,1,"SELECT * FROM table WHERE column = 0;

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を使用すると

EXPLAIN
SELECT
	*
FROM
	person p
        INNER JOIN person_diary pd ON p.id = pd.person_id
WHERE
	p.gender = 1;

EXPLAIN

SELECT

FROM

person p

INNER JOIN person_diary pd ON p.id = pd.person_id

WHERE

p.gender = 1;

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

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

ケーススタディ

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

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

EXPLAIN
SELECT
	* 
FROM
	person_diary
WHERE
	person_id = 10;

EXPLAIN

SELECT

FROM

person_diary

WHERE

person_id = 10;

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)	INDEX
comment	text
comment_person_id	int(11)	INDEX

インデックス

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

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

EXPLAIN
SELECT
	*
FROM
	person_diary_comment pdc
	INNER JOIN person_diary pd ON pd.id = pdc.person_diary_id
WHERE
	pdc.person_diary_id = 10;

EXPLAIN

SELECT

FROM

person_diary_comment pdc

INNER JOIN person_diary pd ON pd.id = pdc.person_diary_id

WHERE

pdc.person_diary_id = 10;

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を条件に加えてみます。

EXPLAIN
SELECT
	*
FROM
	person_diary_comment pdc
	INNER JOIN person_diary pd ON pd.id = pdc.person_diary_id
WHERE
	pdc.person_id = 1 AND /* 条件を追加 */
	pdc.person_diary_id = 10;

EXPLAIN

SELECT

FROM

person_diary_comment pdc

INNER JOIN person_diary pd ON pd.id = pdc.person_diary_id

WHERE

pdc.person_id = 1 AND /* 条件を追加 */

pdc.person_diary_id = 10;

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を使っていてパフォーマンスが出ない場合の話です。

EXPLAIN
SELECT
	*
FROM
	person_diary_comment
WHERE
	person_id = 1
ORDER BY
	comment_person_id
LIMIT 100;

EXPLAIN

SELECT

FROM

person_diary_comment

WHERE

person_id = 1

ORDER BY

comment_person_id

LIMIT 100;

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ですね。

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

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

EXPLAIN
SELECT
	*
FROM
	person_diary_comment
WHERE
	comment_person_id >= 100 AND
        comment_person_id <= 500

EXPLAIN

SELECT

FROM

person_diary_comment

WHERE

comment_person_id >= 100 AND

comment_person_id <= 500

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に判断されたということです。

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

EXPLAIN
SELECT
	comment_person_id /* SELECT句をcomment_person_idのみに */
FROM
	person_diary_comment
WHERE
	comment_person_id >= 100 AND
        comment_person_id <= 500

EXPLAIN

SELECT

comment_person_id /* SELECT句をcomment_person_idのみに */

FROM

person_diary_comment

WHERE

comment_person_id >= 100 AND

comment_person_id <= 500

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

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

RELATION ENTRY

tigerの記事一覧へ

▶︎

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

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

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

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

ケーススタディ

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

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

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

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

最後に

RELATION ENTRY

HOT ENTRY

RECENT