Enjoy IT Life

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

（更新: 2021-06-01）
データベース

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

スロークエリを引き起こす最も多い原因のひとつが『正しくインデックスが作成・利用されていない』です。

そこで今回はEXPLAINとインデックスを利用した具体的なチューニング手順について紹介します。

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

チューニングの大原則: 推測ではなく計測する

クエリチューニングを始める前に押さえておきたい原則は「推測ではなく計測する」です。

「なんとなく遅そうなクエリ」を直しても、実際のボトルネックでなければ性能改善にはつながりません。

具体的には、以下の順序でボトルネックを特定します。

  1. スロークエリログで遅いクエリを抽出する — 体感ではなくログで遅延の事実を確認する。詳細は遅いクエリを突き止める!MySQLクエリ解析にスロークエリログを導入する手順を参照
  2. EXPLAINで実行計画を確認する — 遅いクエリに対して実行計画を取得し、フルスキャン・filesort・テンポラリテーブルなどの兆候を探す
  3. インデックス設計を見直す — 必要なインデックスを追加・修正する

書籍『SQLアンチパターン』12章で紹介されているMENTOR原則（Measure / Explain / Nominate / Test / Optimize / Rebuild）も、この流れを体系化したものです。

本記事の手順はMENTORのM・E・N・Tに相当します。

チューニング対象の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;

*************************<strong> 1. row </strong>*************************
           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;
*************************<strong> 1. row </strong>*************************
        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;
*************************<strong> 1. row </strong>*************************
        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:

*************************<strong> 2. row </strong>*************************
        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;
*************************<strong> 1. row </strong>*************************
           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（ユニークではないインデックスによる等価検索）に変更されました。

補足: アクセス述語とフィルタ述語の確認

typeがref/rangeになっていればインデックスが利用されている、と一旦は判断できますが、複合インデックスを使うクエリでは「インデックスのうちどこまでが絞り込みに使われているか」をさらに確認する必要があります。

検索条件は以下の2つに分類できます。

  • アクセス述語: B-treeを走査する範囲を決める条件（インデックスで絞り込みに使われる）
  • フィルタ述語: 走査範囲は変えず、走査中の行を判定する条件（インデックスでは絞り込めない）

アクセス述語とフィルタ述語の確認方法は以下の通りです。

-- MySQL 8.0以降: ツリー形式でアクセス述語を確認できる
EXPLAIN FORMAT=TREE SELECT ...;
-- もしくは EXPLAIN ANALYZE で実時間ベースの内訳も取得可能
EXPLAIN ANALYZE SELECT ...;

-- PostgreSQL: Index Cond がアクセス述語、Filter がフィルタ述語
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

複合インデックスでtype=ref/rangeになっていても、フィルタ述語が多くて結果的に大量の行を読んでいるケースがあります。Extra: Using index condition（ICP, Index Condition Pushdown）が出ているか、rowsの見積もりが妥当か、までセットで確認するとよいです。

詳細は【図解】B-treeを理解し、複合インデックスの順番を正しく作るを参照してください。

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

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

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

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

まとめ

EXPLAINとインデックスを利用したSQLチューニング手順
  • スロークエリログでチューニング対象のSQLを特定する（推測ではなく計測する）
  • チューニング対象のSQLの先頭にEXPLAINを追加し、実行する
  • EXPLAINのうち、インデックスが正しく使われていないクエリを見つける
  • 複合インデックスを利用するクエリではアクセス述語／フィルタ述語の内訳も確認する
  • 『SHOW INDEX』で当該クエリに関係するテーブルのインデックスを確認する
  • 『CREATE INDEX』で必要なインデックスを作成する
  • EXPLAINを再実行し、クエリが改善されたことを確認する

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

タグ: MySQL , パフォーマンスチューニング