EXPLAIN を使用して Azure Database for MySQL - フレキシブル サーバーのクエリ パフォーマンスをプロファイリングする

  • [アーティクル]

適用対象: Azure Database for MySQL - 単一サーバー Azure Database for MySQL - フレキシブル サーバー

重要

Azure Database for MySQL の単一サーバーは提供終了パスにあります。 Azure Database for MySQL フレキシブル サーバーにアップグレードすることを強くお勧めします。 Azure Database for MySQL フレキシブル サーバーへの移行の詳細については、「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

この例では、キーの値は 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 フレキシブル サーバーではインデックスが使用されていないことが出力に示されています。 出力には、一時的な使用も表示されます。filesort を使用します。これは、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

EXPLAIN ステートメントの出力は、Azure Database for MySQL フレキシブル サーバーが結合インデックスを使用して、インデックスが既に並べ替えられているため、追加の並べ替えを回避することを示しています。

まとめ

EXPLAIN をさまざまなインデックスと共に使うことで、パフォーマンスを大幅に向上させることができます。 テーブルにインデックスがあると、必ずしも Azure Database for MySQL フレキシブル サーバーがクエリに使用できるとは限りません。 常に、EXPLAIN を使って想定を検証し、インデックスを使ってクエリを最適化する必要があります。

次の手順

  • 重要度の高い質問に対する回答を見つける、質問を投稿する、または回答するには、Stack Overflow にアクセスしてください。