実行計画を読んでインデックスが有効かを判断する(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;`

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

```
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);
```

</details>




## (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?](https://www.java67.com/2017/12/difference-between-table-scan-index.html) を参照
* その他
  * ALL, index でなければ、インデックスが有効な項目になっている可能性がある
  * 詳しくは [ドキュメントの テーブル結合型](https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html#explain-join-types) を参照する

### (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 の追加情報](https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html#explain-output-interpretation) を参照する

## 目的に立ち戻り

`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` が有効だと分かる。

## おまけ


次のクエリはインデックスが効いていない。経緯は[別記事を参照お願いします](https://qiita.com/ymatsukawa/items/9b798b73b1c7af4e51e1#2-2-%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E3%81%8C%E5%8A%B9%E3%81%8B%E3%81%AA%E3%81%84%E5%A0%B4%E5%90%88)

`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](https://learning.oreilly.com/library/view/efficient-mysql-performance/9781098105082/)