フレームワーク開発時代のSQLチューニング基礎（１）EXPLAIN句

http://mtl.recruit.co.jp/blog/2011/02/sql.htmlの転載です。

MTLは大小さまざまなサイトを運営しています。素早く開発するためにフレームワークを利用していますが、公開後のパフォーマンス不足に対応するためには、フレームワークでは隠れてしまっているDBのindexingやSQLチューニングが不可欠だと考えています。

今後数回にわたって、私たちのこれまでの経験からえられたDBチューニングについて、基本的なところを研究員の棚橋から紹介したいと思います。

１．はじめに

RAD(Rapid Application Development)開発において、各種フレームワーク(以下FW)やO/Rマッパーは非常に有効であり、いまでは利用しない選択肢はないほどになっています。機能面や性能面でも非常に優れているのですが、素直なシステム向けに最適化されている？が故に、特殊な要件を実装する場合でも、素直すぎるSQLクエリを出力します。ほとんどの場合パフォーマンス的にも問題はないのですが、複雑な条件を指定した場合や大量のデータを扱う場合、SQLクエリのチューニングが必要になる場面があると思います。MTLでも各種サイトを構築運用していく上で、アクセス数の増大や機能追加などの場面で、SQLクエリのチューニングの必要性に迫られる場面があります。

SQLクエリのチューニングをテーマに記述するにあたり、一般的なSQLクエリの「やっちゃ駄目」を列記しても仕方ないので、実際にありそうなデータ件数とクエリを例にとって実践的に効率の良いSQLクエリを追求してみたいと思います。

今回用意した環境

MacBook Pro上のVM
RAM：2GB
HDD：60GB
MySQL 5.0.77
データ件数

usersテーブル：900,000件
itemsテーブル：6,000件
sales_historyテーブル：3,600,000件

表１：usersテーブル
項目名	データ型	備考
id	INT	auto_increment
user_id	VARCHAR(10)	NOT NULL
user_name	VARCHAR(32)	NOT NULL
user_type	INT	NOT NULL
user_level	INT	NOT NULL default 1
user_exp	INT	NOT NULLdefault 0
user_comment	VARCHAR(160)	default NULL
created_at	DATETIME	NOT NULL
delete_flg	TINYINT	NOT NULL default 0

itemsテーブル
項目名	データ型	備考
id	INT	auto_increment
item_name	VARCHAR(60)	NOT NULL
item_type	INT	default NULL
item_price1	INT	default NULL
item_price2	INT	default NULL
delete_flg	TINYINT	default 0

sales_historyテーブル
項目名	データ型	備考
id	INT	auto_increment
user_id	VARCHAR(10)	default NULL
item_id	INT	default NULL
sales_type	INT	default NULL
complete_flg	TINYINT	default NULL
created_at	DATETIME	default NULL

各テーブルにはid列にPRIMARYインデックスが作成されています。（2011/2/14追記）ストレージエンジンはInnoDBです。

ここで、MySQLのPRIMARYインデックスについて少し解説します。
　MySQLのPRIMARYインデックスは通常のインデックスではありません、一般的にはクラスタ化インデックスと呼ばれるインデックスです。クラスタ化インデックスとは、インデックスのリーフノードにテーブルの全データが格納されているインデックスをいいます。言い換えればインデックスとでーたが一体化されているインデックスといえます。インデックスの常で、インデックスはソート済みですから、MySQLのデータはPRIMARY Keyの順番でソートされて格納されています。

２．単純なSQLクエリ

　まずは、usersテーブルを検索してみましょう。usersテーブルを全件取得するSQLクエリは以下のようにします。

mysql> select * from users;
| 899997 | 0000899996 | USER899996 |         2 |        321 |    32030 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899998 | 0000899997 | USER899997 |         2 |        113 |    11288 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899999 | 0000899998 | USER899998 |         2 |        398 |    39751 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 900000 | 0000899999 | USER899999 |         2 |         52 |     5188 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
900000 rows in set (3.01 sec)

検索に3.01秒掛かりました。
全件を検索するというのはあまりないと思いますので、今度はuser_idで検索してみたいと思います。

mysql> select * from users where user_id='0000654321';
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
| id     | user_id    | user_name  | user_type | user_level | user_exp | user_comment | created_at          | delete_flg |
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
| 654322 | 0000654321 | USER654321 |         2 |        208 |    20719 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
1 row in set (0.54 sec)

　検索に0.54秒掛かりました。900,000万件検索には約3秒かかり、1件検索には0.54秒かかる、1件の検索があまりにも遅い結果になりました。（厳密には違うのですが、ここは目をつぶってもらいます。）

　なぜこのような結果になったのか、MySQLにはMySQLがどのように検索を行ったかを表示してくれる機能(explain)があります。早速実行してみましょう。

mysql> explain select * from users;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 900153 |       | 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

mysql> explain select * from users where user_id='0000654321';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 900153 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

　注目するべきは、検索対象となった行数で、この場合、両方とも全件を検索している事がわかります。いわゆる全表検索が行われた事になります。

　なぜこのような結果になったか。答えはMySQLがデータ検索する時に有効な情報（インデックス）がなかったために、全件をチェックし条件に合致した行を探したからです。この場合、条件に指定されたuser_idにインデックスが設定されていないため、MySQLが全表検索を選択しました。

　インデックスはテーブルからデータを検索するときに非常に有効な手段です。インデックスの有る無しでパフォーマンスは雲泥の差が出ます。実際にuser_idにインデックスを設定してみましょう。

mysql> create index users_idx01 on users(user_id);
Query OK, 900000 rows affected (18.36 sec)
Records: 900000  Duplicates: 0  Warnings: 0

これでusersテーブルにインデックスusers_idx01が作成されました。実際に先ほどのSQLクエリを実行してみましょう。

mysql> select * from users where user_id='0000654321';

+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
| id     | user_id    | user_name  | user_type | user_level | user_exp | user_comment | created_at          | delete_flg |
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
| 654322 | 0000654321 | USER654321 |         2 |        208 |    20719 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
1 row in set (0.00 sec)

mysql> explain select * from users where user_id='0000654321';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | users_idx01   | users_idx01 | 32      | const |    1 | Using where | 
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

　検索時間が0秒！！（実際はごく短い時間がかかってますが）。explainの結果も一行のみになり、非常に効率よく検索できたのがわかります。まさに、インデックスを作成する前と後では雲泥の差です。

　さて、今度はusersテーブルをuser_typeで検索してみましょう。

mysql>select * from users where user_type=2;
| 899997 | 0000899996 | USER899996 |         2 |        321 |    32030 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899998 | 0000899997 | USER899997 |         2 |        113 |    11288 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899999 | 0000899998 | USER899998 |         2 |        398 |    39751 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 900000 | 0000899999 | USER899999 |         2 |         52 |     5188 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
300000 rows in set (1.40 sec)

mysql>explain select * from users where user_type=2;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 900126 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

300,000件の検索に1.4秒かかりました。この列にもインデックスを張ってみましょう。

mysql> create index users_idx02 on users(user_type);
Query OK, 900000 rows affected (20.43 sec)
Records: 900000  Duplicates: 0  Warnings: 0

早速検索してみましょう。

mysql>select * from users where user_type=2;
| 899997 | 0000899996 | USER899996 |         2 |        321 |    32030 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899998 | 0000899997 | USER899997 |         2 |        113 |    11288 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899999 | 0000899998 | USER899998 |         2 |        398 |    39751 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 900000 | 0000899999 | USER899999 |         2 |         52 |     5188 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
300000 rows in set (1.43 sec)

mysql> explain select * from users where user_type=2;
+----+-------------+-------+------+---------------+-------------+---------+-------+--------+-------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows   | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-------+--------+-------+
|  1 | SIMPLE      | users | ref  | users_idx02   | users_idx02 | 4       | const | 447365 |       | 
+----+-------------+-------+------+---------------+-------------+---------+-------+--------+-------+
1 row in set (0.00 sec)

　あれ？検索時間が1.43秒かえって遅くなってしまいました。実は、この項目は値の種類が３種類しかないのです。高速化に有効なインデックスですが、項目に格納される値の種類が少ないと、あまり効果が期待できないか、かえって悪い結果になってしまいます。

では、項目user_levelを条件にした検索はどうでしょう？この項目は値のばらつきがありますので、インデックスが有効なはずです。

mysql> select * from users where user_level=55;

| 897626 | 0000897625 | USER897625 |         2 |         55 |     5414 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 897815 | 0000897814 | USER897814 |         2 |         55 |     5499 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899314 | 0000899313 | USER899313 |         2 |         55 |     5437 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899478 | 0000899477 | USER899477 |         2 |         55 |     5412 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
2307 rows in set (0.55 sec)

mysql> explain select * from users where user_level=55;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 894731 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> create index users_idx03 on users(user_level);
Query OK, 900000 rows affected (23.89 sec)
Records: 900000  Duplicates: 0  Warnings: 0

mysql> select * from users where user_level=55;

| 897626 | 0000897625 | USER897625 |         2 |         55 |     5414 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 897815 | 0000897814 | USER897814 |         2 |         55 |     5499 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899314 | 0000899313 | USER899313 |         2 |         55 |     5437 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899478 | 0000899477 | USER899477 |         2 |         55 |     5412 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
2307 rows in set (0.01 sec)

mysql> explain select * from users where user_level=55;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
|  1 | SIMPLE      | users | ref  | users_idx03   | users_idx03 | 4       | const | 4478 |       | 
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
1 row in set (0.00 sec)

インデックス作成前は0.55秒掛かっていた検索が、作成後は0.01秒になりました。この項目にはインデックスが有効なようです。

では、ありえそうな検索で、user_levelとuser_typeを条件に検索してみましょう。

mysql> select * from users where user_type=2 and user_level=60;

| 898814 | 0000898813 | USER898813 |         2 |         60 |     5960 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899227 | 0000899226 | USER899226 |         2 |         60 |     5905 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899689 | 0000899688 | USER899688 |         2 |         60 |     5976 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899786 | 0000899785 | USER899785 |         2 |         60 |     5908 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
782 rows in set (0.08 sec)

mysql> explain select * from users where user_type=2 and user_level=60;
+----+-------------+-------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
| id | select_type | table | type        | possible_keys           | key                     | key_len | ref  | rows | Extra                                                 |
+----+-------------+-------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
|  1 | SIMPLE      | users | index_merge | users_idx02,users_idx03 | users_idx03,users_idx02 | 4,4     | NULL | 2311 | Using intersect(users_idx03,users_idx02); Using where | 
+----+-------------+-------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

検索に0.08秒？早いのか遅いのか判別しにくい結果です。explainをみてみると、typeにindex_mergeと表示され、extraにUsing intersect(users_idx03,users_idx02)と表示されています。昔からMySQLは「一つの検索で一つのテーブルには一つのインデックスしか使えない。」という制約がありました。ですが、MySQL5.0よりindex_merge（複数のインデックスを結合して一時的に使用する）という機能が実装されました。今回はこれが使用されたようです。通常ならばこの機能の恩恵に預かるのがいいのでしょうが、インデックスの結合に掛かるコスト（処理時間）が気になります。特に件数の多いテーブルではそのコストがネックになる可能性もあります。

　そこで、Using intersectで指定されている項目でインデックスを作成してみましょう。

mysql> create index users_idx04 on users(user_level,user_type);

Query OK, 900000 rows affected (28.40 sec)
Records: 900000  Duplicates: 0  Warnings: 0

| 898814 | 0000898813 | USER898813 |         2 |         60 |     5960 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899227 | 0000899226 | USER899226 |         2 |         60 |     5905 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899689 | 0000899688 | USER899688 |         2 |         60 |     5976 | NULL         | 2011-01-27 00:00:00 |          0 | 
| 899786 | 0000899785 | USER899785 |         2 |         60 |     5908 | NULL         | 2011-01-27 00:00:00 |          0 | 
+--------+------------+------------+-----------+------------+----------+--------------+---------------------+------------+
782 rows in set (0.01 sec)

mysql> explain select * from users where user_type=2 and user_level=60;
+----+-------------+-------+------+-------------------------------------+-------------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys                       | key         | key_len | ref         | rows | Extra |
+----+-------------+-------+------+-------------------------------------+-------------+---------+-------------+------+-------+
|  1 | SIMPLE      | users | ref  | users_idx02,users_idx03,users_idx04 | users_idx04 | 8       | const,const |  781 |       | 
+----+-------------+-------+------+-------------------------------------+-------------+---------+-------------+------+-------+
1 row in set (0.00 sec)

検索に0.01秒、explainも期待した通りになっています。どうやら今回の環境では事前にインデックスを作成した方が高速なようです。

　ちなみに、今回のように複数の項目に跨がるインデックスを複合インデックスといい、一つの項目のみのインデックスを単項目インデックスもしくは単純インデックスといいます。

　このように単純な検索でも、インデックスのあるなしで結果が大きく変わる事が理解できたと思います。

1.5 explainの詳細

　先ほどから利用しているexplainですが、これを読み解けるのとそうではないのとでは、SQLクエリのチューニングの難易度が変わってきます。そこですこし、explainについて解説しましょう。

　explainは実行計画ともいいます、この場合だとMySQLがSQLクエリを実行するときに、どうゆう方法でデータを検索しようとしているかを示しています。こういった機能はMySQLに限らず他のDBMSでも利用できます。実行計画はMySQLのオプティマイザという機能が、最適と思える実行経路を判断した結果ですが、場合によっては最適解ではない場合があります。オプティマイザは大変便利な機能ですが、過信せず実行計画の検証を行う事は非常に重要です。

　では、explainの出力を細かくみていきます。

　explainは以下の項目を出力します。

id SQLクエリの識別番号
select_type SQLクエリの種類
table 検索対象のテーブル名
type データ検索の範囲の種別
possible_keys インデックス候補一覧
key 使用されたインデックス名
key_len 選択されたkeyの長さ
ref keyと条件の比較方法
rows 取得される予測行数
Extra 追加情報

上記の中で重要な部分は以下になります。

select_type

SQLクエリがどのような種類かを表します。

SIMPLE

UNIONやサブクエリがない場合、またJOINを行う場合。

PRIMARY

UNIONで最初のSELECTか、サブクエリで最も外側のもの。

SUBQUERY

サブクエリで親と相関関係がないとき。

DEPENDENT SUBQUERY

サブクエリで親と相関関係にあるとき。

UNCACHEABLE SUBQUERY

実行時まで結果のわからないサブクエリ。毎回評価されてしまいコストが高い。

DERIVED’

FROM句でサブクエリを指定した場合。

UNION’/UNION RESULT

UNIONで２番移行めに実行されるSELECTはUNION、UNIONの結果の結合処理がUNION RESULT

DEPENDENT UNION

２番移行めのUNIONで、かつサブクエリで用いられる場合。

UNCACHEABLE UNION

UNCACHEABLE SUBQUERYがUNIONになっている場合

type

SQLクエリがどのようなアクセスを行うかを表します。

const
- PRIMARYやUNIQUE keyによる等価比較が行われた場合。高速。
system
- MyISAMで用いられる。高速。
ALL
- フルテーブルスキャンが発生している場合。低速。要チューニング。
index
- インデックスフルスキャンが発生している場合。条件指定がインデックスと違っている場合、要チューニング。
eq_ref
- JOINでPRIMARYまたはUNIQUE Keyが利用されている場合。高速。
ref
- NonUNIQUEなインデックスを使って等価検索が行われている場合。高速。
ref_or_null
- OR条件でIS NULLが指定されている場合。低速。
range
- インデックスを用いた範囲検索。そこそこ高速。
fulltext
- フルテキストインデックスを利用した検索の場合。
index_merge
- ２個の別々のインデックスを一時的にマージして使用した場合。
unique_subquery
- DEPENDENT SUBQUERYでPRIMARYもたはUNIQUE Keyが用いられた場合。サブクエリは高速。
index_subquery
- サブクエリでNonUNIQUEなインデックスが利用された場合。サブクエリはそこそこ高速。

Extra

Using where
- WHERE句の条件で検索した場合。
Using index
- インデックスだけでクエリが完結する場合。
Using filesort
- ソート処理でインデックスを利用してソート出来ない場合。大量の行をソートしてる場合はチューニング。
Using temporary
- クエリの実行にテンポラリテーブルが作成された事を示す。
Using index for group-by
- インデックスだけでGROUP BYによるMIN/MAX処理が行われたとき。
Index Merge
- 複数インデックスを結合して使用した場合。

次回はSQLクエリのチューニングでよくいわれる「やっちゃ駄目」とその対策を見ていきます。

hiroohiのメモ

はてななのでITやスタートアップ周りの話（ほとんどが自分への備忘録だけど）を書いています。