MySQLで運用中のシステムにSQLチューニングする方法　その2　～SQL解析～

ビヨンドの原岡です。

前回の内容からの続きになります。

MySQLで運用中のシステムにSQLチューニングする方法～ボトルネックを発見せよ～

今回はボトルネックとして見つけたSQLの解析方法となります。

重いSQLを発見したらその原因を取り除きます。
SQLの修正方法はいろいろありますが、
基本はインデックスを適切に使用するようにすることです。

・インデックスとは

インデックスとはインデックス（索引）の言葉通り、
データに素早くアクセスするための見出しです。
最もよく使われる基本的なインデックスはBツリーインデックスという構造です。

【詳しくは下記参照】
wiki 索引 (データベース)
B-treeインデックス入門

インデックスを適切に使う、使わないで
数倍どころか数百倍、数千倍以上の差が出ることも多いため、効果が非常に高いです。

・EXPLAINの使い方

SQLの解析にはEXPLAINを利用します。
使い方は簡単で、解析したいSQLの前に「EXPLAIN」を付けます。

explainを使って解析すると下記のような解析結果が出力されます。

主キー検索の場合

mysql > explain select col_b from tbl_a where col_b=100000;
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | tbl_a | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

インデックス検索の場合

mysql > explain select col_b from tbl_a where col_c=2000000;
+----+-------------+---------------+-------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | tbl_a | const | col_c | col_c | 4 | const | 1 | NULL |
+----+-------------+---------------+-------+---------------+-------------+---------+-------+------+-------+

フルスキャンの場合

mysql > explain select col_b from tbl_a where col_d='aaa';
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+

「using filesort」がある場合

mysql > explain select col_b from tbl_a where col_d='aaa' order by col_e;
+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------------------+

表 8.1 EXPLAIN 出力カラム

・注目する箇所

select_type
DEPENDENT SUBQUERY（相関サブクエリ）があれば要注意

type
ALL　テーブルをフルスキャンしてるので重い、インデックスを使いましょう
ただしデータ件数が少ない（数百件くらい）場合はあまり影響は出ない
INDEX インデックスのフルスキャンが行われている、重い

key
使用されたインデックス、NULLの場合使用されていない

rows
読み込まれるデータ検索の推定値、基本的にこの値が大きい場合は要注意

Extra
Using filesortとUsing temporaryがあれば要注意

【参照】
MySQL初心者に贈るインデックスチューニングのポイントまとめ2014

・テーブル構造とインデックスの確認

descでテーブル構造を確認

mysql > desc tbl_a;
+-----------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+-------+
| col_b | int(15) unsigned | NO | PRI | NULL | |
| col_c | int(9) | NO | MUL | NULL | |
| col_d | varchar(20) | NO | | 0 | |
| col_e | int(5) | NO | | NULL | |
+-----------------------+---------------------+------+-----+---------------------+-------+

show indexでインデックスを確認

mysql > show index from acount_public;
+---------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_a | 0 | PRIMARY | 1 | col_b | A | 500000 | NULL | NULL | | BTREE | | |
| tbl_a | 0 | col_c | 1 | col_c | A | 80000 | NULL | NULL | | BTREE | | |
+---------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

「Cardinality」という行がありますが、これはカラムの値の種類になります。
この数が大きいほどインデックスの絞り込み効率が高く（カーディナリティーが高い）、
小さいほどインデックスの絞り込み効率が低いです（カーディナリティーが低い）。
なるべく効率の良いインデックスを作成しましょう。

・インデックスを張る

ALLのフルスキャン、インデックスフルスキャン、Using filesortなどを見つけたら対象のテーブルにインデックスを作成します。

CREATE INDEX col_d ON tbl_a(col_d);

参照
インデックスの作成・削除・情報表示・追加

インデックスが張れたら、再度explainをおこなって効果を確認します。
またSQLをそのまま実行してインデックスを張る前後での実行速度を比較します。
効果が出る方法を見つけるまで根気よく繰り返しましょう。

・まとめ

• SQLの解析にはEXPLAINを使う
• できるだけインデックスを使った検索に変える
• type=ALL,type=INDEXの場合はインデックスを使わせる
• Using filesort、Using temporaryは要注意
• rowsが大きい場合はなるべく小さくする
• カーディナリティーの大きい高いインデックスを作る、使う

ついでに

• サブクエリは遅いのでなるべく避ける
• 無駄な結合をしない

【その他参考記事】
ヤフー社内でやってるMySQLチューニングセミナー大公開