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

この例では、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 を使って想定を検証し、インデックスを使ってクエリを最適化する必要があります。

次の手順

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