実行計画を読んでインデックスが有効かを判断する(MySQL)

環境 - MySQL 5.7

TL;DR

あるクエリについて、目的のインデックスが有効かを調べたい場合

• (1) クエリの冒頭に EXPLAIN をつける
• (2) 実行計画の出力項目を確認する
  • (2-1) type ... ALL, index ではないこと
  • (2-2) possible_keys ... 目的のインデックスが「含まれている」こと
  • (2-3) key ... 目的のインデックスが「存在する」こと
  • (2-4) rows ... 取得予測行数が、目算と大体あっていること
  • (2-5) Extra ... オプティマイザ戦略が意図に沿っていること

やりたいこと

以下の店舗テーブル ( shops ) があるとする。

CREATE TABLE shops (
  id          INTEGER      AUTO_INCREMENT,
  name        VARCHAR(255) NOT NULL DEFAULT '',
  manage_type VARCHAR(255) NOT NULL DEFAULT '',
  debt        INTEGER      NOT NULL DEFAULT 0,

  PRIMARY KEY(id),
  INDEX manage_type_index (manage_type),
  INDEX manage_type_and_debt_index (manage_type, debt)
);

次のクエリは 複合インデックス manage_type_and_debt_index が有効なのかを調べたい。

SELECT * FROM shops WHERE manage_type = 'online' AND debt < 1000;

データは次の通りとする(クリック・タップすると開く)

SELECT * FROM shops;

+----+------+-------------+------+
| id | name | manage_type | debt |
+----+------+-------------+------+
|  1 | A    | online      | 4000 |
|  2 | B    | online      |   50 |
|  3 | C    | online      |  100 |
|  4 | D    | in_store    | 4000 |
|  5 | E    | franchise   |  300 |
|  6 | F    | online      |  100 |
|  7 | G    | in_store    | 3000 |
|  8 | H    | online      | 2000 |
|  9 | I    | franchise   |  200 |
| 10 | J    | in_store    |  200 |
| 11 | K    | online      |  300 |
| 12 | L    | in_store    |   50 |
| 13 | M    | franchise   |  100 |
| 14 | N    | online      |  100 |
+----+------+-------------+------+

-- 参考までに、INSERT 文
INSERT INTO shops (name, manage_type, debt) VALUES
  ('A', 'online', 4000),
  ('B', 'online', 50),
  ('C', 'online', 100),
  ('D', 'in_store', 4000),
  ('E', 'franchise', 300),
  ('F', 'online', 100),
  ('G', 'in_store', 3000),
  ('H', 'online', 2000),
  ('I', 'franchise', 200),
  ('J', 'in_store', 200),
  ('K', 'online', 300),
  ('L', 'in_store', 50),
  ('M', 'franchise', 100),
  ('N', 'online', 100);

(1) 目的クエリの冒頭に EXPLAIN をつける

いわゆる実行計画。クエリ末尾に \G を付けているに注意。

EXPLAIN SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: range
possible_keys: manage_type_index,manage_type_and_debt_index
          key: manage_type_and_debt_index
      key_len: 1026
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index condition

(2) 実行計画の出力項目を確認する

(2-1) type ... ALL, index ではないこと

テーブルへのアクセス方法(access method)を示している

EXPLAIN SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000 \G;

*************************** 1. row ***************************
...
         type: range
...

ALL もしくは index でないことを確認する。

• ALL - フルテーブルスキャン
  • テーブル全体をスキャンする
• index - インデックススキャン
  • インデックス全体をスキャンしてから、テーブルをスキャンする
  • 紛らわしいが、ALL とほぼ変わりない
  • 仕組・原理はリンク先の記事 > 2. What is the Index Scan in a database? を参照
• その他
  • ALL, index でなければ、インデックスが有効な項目になっている可能性がある
  • 詳しくは ドキュメントの テーブル結合型 を参照する

(2-2) possible_keys ... 目的のインデックスが「含まれている」こと

インデックスの 候補 になりうるもの。

複数候補がある場合、カンマ区切りで表示される。どれが使われるかは、key に表示される。

EXPLAIN SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000 \G;

*************************** 1. row ***************************
...
possible_keys: manage_type_index,manage_type_and_debt_index
...

(2-3) key ... 目的のインデックスが「存在する」こと

実際に使われるインデックス。

possible_keys に複数候補がある場合、オプティマイザによって 1 つに絞り込まれる。

EXPLAIN SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000 \G;

*************************** 1. row ***************************
...
          key: manage_type_and_debt_index
...

(2-4) rows ... 取得予測行数が、目算と大体あっていること

取得が 予想される行数 (実行計画なので、正確な値ではない)

おおよそ合っていれば問題なし。

EXPLAIN SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000 \G;

*************************** 1. row ***************************
...
         rows: 5
...

(2-5) Extra ... オプティマイザの戦略が意図に沿っていること

オプティマイザが下した戦略の追加情報。

EXPLAIN SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000 \G;

*************************** 1. row ***************************
...
        Extra: Using index condition
...

インデックスが効いているかどうかを知りたい場合は、次が存在するかを把握する。

• Using index
  • 全取得行がインデックス検索される
  • これが出力されれば一番良い
• Using index condition
  • 一部取得行がインデックス検索される
• NULL(追加情報なし) かつ、type が const の場合はインデックス検索が効いている

その他 Extra の情報を知りたい場合は EXPLAIN の追加情報 を参照する

目的に立ち戻り

SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000;

には manage_type_and_debt_index が有効かを確認する。

EXPLAIN SELECT * FROM shops WHERE manage_type = 'online' and debt < 1000 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: range
possible_keys: manage_type_index,manage_type_and_debt_index
          key: manage_type_and_debt_index
      key_len: 1026
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index condition

• type = range ... ALL, index でない = OK
• possible_keys = manage_type_and_debt_index が含まれている
• key = manage_type_and_debt_index である
• rows = 5 ... 大体あってそう
• Extra = Using index condition でインデックスを使っている

ので、対象のクエリは manage_type_and_debt_index が有効だと分かる。

おまけ

次のクエリはインデックスが効いていない。経緯は別記事を参照お願いします

SELECT * FROM shops WHERE manage_type = 'online' OR debt < 1000;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ALL
possible_keys: manage_type_index,manage_type_and_debt_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 40.00
        Extra: Using where

参考資料

• https://dev.mysql.com/doc/refman/5.6/ja/execution-plan-information.html
• Efficient MySQL Performance