EXPLAINとインデックスを利用したスロークエリの高速化手順

データベース

前回、MySQLのEXPLAINの読み方とチューニング時のチェックポイントでEXPLAINの読み方について紹介しました。

スロークエリを引き起こす最も多い原因のひとつが『正しくインデックスが作成・利用されていない』です。
そこで今回はEXPLAINとインデックスを利用した具体的なチューニング手順について紹介します。

MySQLは5.7.34を利用しています。

チューニング対象のSQL

SELECT * FROM users WHERE age = 20;

なお、usersテーブルにはPrimary key以外にインデックスは作成されていないとします。

EXPLAINとインデックスを利用したチューニング手順

  1. EXPLAINでSQLの実行計画を取得する
  2. インデックスの確認と作成
  3. EXPLAINを再実行し、クエリが改善されたか確認する

以下では各項目について紹介します。

EXPLAINでSQLの実行計画を取得する

先頭にEXPLAINを追記してSQLを実行すると実行計画が取得できます。
SQLのチューニングをする際は、対象のSQLの先頭にEXPLAINを記述し、SQLを実行するところから始めます。

今回のチューニング対象のSQLの実行計画は以下の通りです。
なお、SQLの末尾にある\Gは結果を縦出力にするオプションです。

> EXPLAIN SELECT * FROM users WHERE age = 20\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

typeに表示されているALLはテーブルのフルスキャンが実行されていることを意味します。
typeにALLが表示されるクエリは改善が必要です。

possible_keysやkeyがNULLであることから、クエリに関連したインデックスは作成されていないことがわかります。

今回の場合ですと検索条件のageにインデックスを作成することでテーブルをフルスキャンせずに済みそうです。

インデックスの確認と作成

show index from [テーブル名]で当該テーブルのインデックスを確認します。
以下の結果からわかる通り、ageに対してはインデックスが作成されていません。

> SHOW INDEX FROM users\G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 996364
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

インデックスはcreate index [インデックス名] on テーブル名(カラム)で作成します。
usersテーブルのageカラムにインデックスを作成する場合は以下のようになります。

> CREATE INDEX index_users_on_age on users(age);

インデックス作成後、再びshow indexを実行すると新しくインデックスが追加されたことがわかります。

> SHOW INDEX FROM users\G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 951399
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: index_users_on_age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 84
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

EXPLAINを再実行し、クエリが改善されたか確認する

チューニング後、再びEXPLAINを実行します。
EXPLAINの結果が改善されていればチューニングは完了です。

> EXPLAIN SELECT * FROM users WHERE age = 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22248
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

keyに先ほど作成したindex_users_on_ageが表示されているので、インデックスが利用されていることがわかります。

インデックスが利用されるようになったため、typeもALLからref（ユニークではないインデックスによる等価検索）に変更されました。

参考: EXPLAINを実行する場合は実際のデータを利用する

同じテーブルでもレコード数やデータの分散によって実行計画の結果が異なります。

つまり、本番環境で数万件データが保存されているのであれば、クエリチューニングを行う際も同等のデータを用意しなければ意味がないので気を付けましょう。

たとえばRailsアプリケーションの場合、Rails 6のinsert_allで大量のダミーデータを短時間で作成するで紹介した方法で大量のダミーデータが作成できます。

まとめ

EXPLAINとインデックスを利用したSQLチューニング手順
  • チューニング対象のSQLの先頭にEXPLAINを追加し、実行する
  • EXPLAINのうち、インデックスが正しく使われていないクエリを見つける
  • 『SHOW INDEX』で当該クエリに関係するテーブルのインデックスを確認する
  • 『CREATE INDEX』で必要なインデックスを作成する
  • EXPLAINを再実行し、クエリが改善されたことを確認する

Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！

RECOMMEND