EXPLAIN を使用して Azure Database for MySQL - フレキシブル サーバーでのクエリのパフォーマンスをプロファイリングする
EXPLAIN は、クエリを最適化するのに役立つ便利なツールです。 EXPLAIN ステートメントを使うと、SQL ステートメントの実行状況に関する情報を取得できます。 EXPLAIN ステートメントを実行したときの出力例を次に示します。
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 10.00
Extra: Using where
この例では、key の値は NULL です。これは、Azure Database for MySQL フレキシブル サーバーがクエリに最適化されたインデックスを見つけられないことを意味します。 その結果、完全テーブル スキャンが実行されます。 ID 列にインデックスを追加してこのクエリを最適化し、EXPLAIN ステートメントをもう一度実行してみましょう。
mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: id
key: id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
今度は、Azure Database for MySQL フレキシブル サーバーがインデックスを使って行数を 1 に制限することを出力が示します。これで検索時間が大幅に短縮されます。
カバリング インデックス
カバリング インデックスにはクエリのすべての列が含まれるので、データ テーブルからの値の取得が減ります。 次の GROUP BY ステートメントと関連する出力は、これを示しています。
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
適切なインデックスを使用できないため、Azure Database for MySQL フレキシブル サーバーがインデックスを使わないことを出力が示します。 出力には Using temporary; Using file sort も示されます。これは Azure Database for MySQL フレキシブル サーバーが GROUP BY 句に対応するために一時テーブルを作成することを示します。
c2 列だけにインデックスを作成しても違いはなく、Azure Database for MySQL フレキシブル サーバーは引き続き一時テーブルを作成する必要があります。
mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
この場合、c1 と c2 の両方に対して カバリング インデックス を作成することができ、それによりインデックスに c2 の値を直接追加してさらにデータの参照を減らすことができます。
mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: covered
key: covered
key_len: 108
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using index
上記の EXPLAIN の出力が示すように、Azure Database for MySQL フレキシブル サーバーがカバリング インデックスを使うようになり、一時テーブルを作成する必要を回避します。
結合インデックス
結合インデックスは、複数の列の値で構成され、インデックス付き列の連結値により並べ替えられた行の配列と見なすことができます。 この方法は、GROUP BY ステートメントで役に立ちます。
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using filesort
Azure Database for MySQL フレキシブル サーバーは、"ファイル並べ替え" 操作を実行します。これは、多くの行を並べ替える必要があるときは特に、かなり低速です。 このクエリを最適化するには、並べ替えられる両方の列に対して結合インデックスを作成します。
mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: NULL
key: my_sort2
key_len: 108
ref: NULL
rows: 10
filtered: 11.11
Extra: Using where; Using index
Azure Database for MySQL フレキシブル サーバーが結合インデックスを使い、インデックスは既に並べ替えられているため、追加の並べ替えを回避していることを EXPLAIN ステートメントの出力が示します。
まとめ
EXPLAIN をさまざまなインデックスと共に使うことで、パフォーマンスを大幅に向上させることができます。 テーブルにインデックスが設定されているからといって、Azure Database for MySQL フレキシブル サーバーでクエリにそれを使用できるとは限りません。 常に、EXPLAIN を使って想定を検証し、インデックスを使ってクエリを最適化する必要があります。