[MySQL]インデックスの要点まとめ

# はじめに

MySQLのインデックスについて勉強をしたい。
インデックスについての記事がすでにたくさんあるので、何を押さえるべきかをまとめる。

https://dev.mysql.com/doc/refman/8.0/ja/optimization.html

## 検証環境

* Docker
    * Docker version 24.0.6, build ed223bc
    * Docker Compose version v2.23.0-desktop.1
    * イメージ: 「mysql:8.0」

```
bash-4.4# mysql --version
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
```

# Question

下記の質問にきちんと解答できることがゴールです。

* インデックスとは何ですか。
* インデックスを使うと処理速度が速くなる理由は何ですか。データ構造を用いて説明して下さい。
* カーディナリティとは何ですか。
* インデックスを使用する時のポイントは何ですか。
* インデックスが効かない場合は、どのような場合ですか。
* クラスタ化インデックス、セカンダリインデックス、複合インデックス、カバリングインデックスについて説明して下さい。
* クエリのパフォーマンスを見るステートメントは何ですか。
* 特定のテーブルのインデックスを確認できるステートメントは何ですか。


## インデックスとは何ですか

<details>
<summary>解答</summary>
<div>
インデックスとは、特定のカラム値のある行をすばやく見つけるために使用される。
</div>
</details>

https://dev.mysql.com/doc/refman/8.0/ja/mysql-indexes.html


## インデックスを使うと処理速度が速くなる理由は何ですか。データ構造を用いて説明して下さい。

<details>
<summary>解答</summary>
<div>
インデックスは、B-treeと呼ばれるデータ構造を用いているから。線形探索(O(N))ではなく、二分探索(O(logN))を行うため目的のデータに少ない計算量で到達することができる。
実際は、B+tree構造を採用しているそうです。
</div>
</details>

https://zenn.dev/hk_206/articles/ec5f4e347caff4

## カーディナリティとは何ですか。

<details>
<summary>解答</summary>
<div>
カーディナリティとは、テーブルカラム内の異なる値の数(バリエーション)のこと。
あるカラムが持つ値が3つしかない場合は、カーディナリティが低い状態である。反対に、全レコードのうち常に一意であるものは、レコードの数だけ値があることになるので、カーディナリティが高い状態である。
</div>
</details>

https://dev.mysql.com/doc/refman/8.0/ja/glossary.html#glos_cardinality

## インデックスを使用する時のポイントは何ですか。

<details>
<summary>解答</summary>
<div>

* カーディナリティが高いカラムにインデックスを貼ること。
* インデックスをむやみに貼らない。

カーディナリティが高いカラムにインデックスを貼ることで、カーディナリティが低い場合と比べて、インデックスを効果的に用いることができる。
インデックスをむやみに貼るということは、データベースに加えて新たな検索用の辞書のようなものを作るということなので、データ容量が増加する。また、データ挿入時、更新時に辞書も更新しなくてはならないので、インデックスが多いと処理が重くなってしまう。

</div>
</details>

https://qiita.com/hmatsu47/items/2d44c173a9114fd06853

https://qiita.com/ikenji/items/b868877492fee60d85ce

https://qiita.com/YumaInaura/items/2b71467456b694760b29


## インデックスが効かない場合は、どのような場合ですか

<details>
<summary>解答</summary>
<div>

* 否定形(<>、!=、NOT IN)を使用している場合
* LIKEが%で始まる場合
* オプティマイザが、インデックスを使用することによってMySQLがテーブルの大部分の行にアクセスする必要があると推定した場合
* テーブル自体が小さく、データ全体をスキャンする方がインデックスを使用するよりも高速である場合
    * 実務ではあまりなさそう。

※余談ですが、IS NULLについてです。「IS NULLの場合、インデックスが使われない」と主張した記事をたまに見かけましたが、正しくはインデックスが使われるので注意です。


```
mysql> show columns from users;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | bigint unsigned  | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(255)     | NO   | MUL | NULL    |                |
| last_name  | varchar(255)     | NO   |     | NULL    |                |
| age        | tinyint unsigned | YES  | MUL | NULL    |                |
| status     | tinyint          | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
7 rows in set (0.02 sec)

mysql> show index from users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY   |            1 | id          | A         |      191541 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | full_name |            1 | first_name  | A         |      194067 |     NULL |   NULL |      | BTREE      |         | for full_name | YES     | NULL       |
| users |          1 | full_name |            2 | last_name   | A         |      199638 |     NULL |   NULL |      | BTREE      |         | for full_name | YES     | NULL       |
| users |          1 | users_age |            1 | age         | A         |         101 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.02 sec)

mysql> explain select count(*) from users where age is null;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | ref  | users_age     | users_age | 2       | const | 149109 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

```

</div>
</details>

https://oreno-it.info/archives/1953

https://zenn.dev/nasu/articles/410bdb9739cd35

https://dev.mysql.com/doc/refman/8.0/ja/index-btree-hash.html

https://dev.mysql.com/doc/refman/8.0/ja/column-indexes.html

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0111

## クラスタ化インデックス、セカンダリインデックス、複合インデックス、カバリングインデックスについて説明して下さい。

<details>
<summary>解答</summary>
<div>

* クラスタ化インデックス
    * 主キーのインデックス。
    * 主キーの場合、**自動的**にインデックスが作成される。
* セカンダリインデックス
    * クラスタ化インデックス以外のインデックス。
    * 後述する複合インデックス、カバリングインデックスもセカンダリインデックスの一つ。
* 複合インデックス
    * 複数のカラムを組み合わせたインデックス。
    * 複合インデックスとして設定しているカラムの使う順番がとても重要。
* カバリングインデックス
    * 通常のインデックスとは異なり、そのインデックス自体がクエリが必要とするデータを提供することができるインデックスの。カバリングインデックスを使用すると、クエリがデータベーステーブル自体にアクセスせずに、インデックスから直接必要な情報を取得できるようになる。

下記は、複合インデックスを活かせていない例です。

```
# usersテーブルのインデックス。first_name、last_nameの順序である複合インデックスfull_nameを設定
mysql> show index from users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY   |            1 | id          | A         |      191541 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | full_name |            1 | first_name  | A         |      194067 |     NULL |   NULL |      | BTREE      |         | for full_name | YES     | NULL       |
| users |          1 | full_name |            2 | last_name   | A         |      199638 |     NULL |   NULL |      | BTREE      |         | for full_name | YES     | NULL       |
| users |          1 | users_age |            1 | age         | A         |         101 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+


# typeがrefとなっている＝複合インデックスを活かせている
mysql> explain select first_name,last_name from users where first_name = 'Dino Crist' order by last_name;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref  | full_name     | full_name | 1022    | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

# typeがindexなので、インデックスのフルスキャンとなってしまっている
mysql> explain select first_name,last_name from users where last_name = 'Nathan Rice' order by first_name;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | index | full_name     | full_name | 2044    | NULL | 199638 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.02 sec)
```

2つ目のクエリは、1つ目のクエリとは異なり、where句にlast_nameカラムを、order by句にfirst_nameカラムを設定した一見複合インデックスを活かせそうなクエリに見えるが、インデックスのフルスキャンになってしまっている。複合インデックスを設定する場合は、設定しているカラムの使う順番に注意しなければならない。


</div>
</details>

https://dev.mysql.com/doc/refman/8.0/ja/innodb-index-types.html

https://qiita.com/ichi_zamurai/items/a8e5e4a37faecf9cd77a

## クエリのパフォーマンスを見るステートメントは何ですか。

<details>
<summary>解答</summary>
<div>

EXPLAIN。
EXPLAINの出力フォーマットは下記の通り。


| カラム           | 説明                                                                                               |
|------------------|------------------------------------------------------------------------------------------------------|
| id               | 実行計画のID。通常、サブクエリごとに異なるIDが割り当てられます。親クエリのIDは通常0です。                 |
| select_type      | クエリ内のSELECTのタイプ。例: SIMPLE（単純なSELECT）、PRIMARY（最も外側のSELECT）、SUBQUERY（サブクエリ内のSELECT）、UNION（UNION操作内のSELECT）など。 |
| table            | クエリが関連するテーブルの名前やエイリアス。                                                          |
| partitions       | クエリが実行されるパーティション。                                                                  |
| type             | クエリの各テーブルへのアクセス方法 |
| possible_keys    | クエリの実行に使用できるインデックスの一覧。                                                       |
| key              | 実際に選択されたインデックス。                                                                     |
| key_len          | 選択されたインデックスの長さ（バイト単位）。                                                       |
| ref              | インデックスを使用して行を絞り込む方法。                                                            |
| rows             | クエリがテーブルから取得する行の推定数。                                                           |
| filtered         | クエリの結果セットをフィルタリングするための条件の割合。                                           |
| Extra            | その他の情報。例: ファイルソート、テンポラリテーブルの作成など。                                    |

keyに表示されるものは、必ずpossible_keysにあるとは限らないです。

```
mysql> explain select * from users order by id desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+---------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra               |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+---------------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | PRIMARY | 8       | NULL | 99201 |   100.00 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+---------------------+

```

</div>
</details>

https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html#explain-extra-information

https://nippondanji.blogspot.com/2009/03/mysqlexplain.html

## 特定のテーブルのインデックスを確認できるステートメントは何ですか

<details>
<summary>解答</summary>
<div>

SHOW INDEX。

```
show index from tbale_name;
```

| カラム名         | 説明                                                         |
|------------------|--------------------------------------------------------------|
| Table            | インデックスが属しているテーブルの名前                        |
| Non_unique       | インデックスが一意かどうか（0: 一意, 1: 一意でない）         |
| Key_name         | インデックスの名前                                           |
| Seq_in_index     | インデックス内のカラムの位置（1から始まる）                  |
| Column_name      | インデックス内のカラムの名前                                 |
| Collation        | インデックスのカラムの文字列の照合順序                       |
| Cardinality      | インデックスのカーディナリティ（ユニークな値の数）           |
| Sub_part         | 部分インデックスのバイト数（部分インデックスでない場合はNULL） |
| Packed           | インデックスが圧縮されている場合の情報                       |
| Null             | インデックスのカラムがNULLを許可するかどうか（YESまたはNO）   |
| Index_type       | インデックスのタイプ（BTREEなど）                            |
| Comment          | (調査中)                     |
| Index_comment    | インデックスが作成されたときに COMMENT 属性でインデックスに対して提供された任意のコメント。                   |
| Visible          | インデックスが可視か非可視か（MySQL 8.0以降）               |
| Expression       | インデックスが計算された式（MySQL 8.0以降）                 |

**Seq_in_index**

Seq_in_indexは、インデックス内でのカラムの位置を示す。たとえば、複合インデックス＝ `(Column1, Column2, Column3)` のようになっている場合、それぞれのカラムには1から始まる通し番号が割り当てられます。具体的には、`Seq_in_index`の値が1ならば最初のカラム、2ならば次のカラム、といった具合です。
これは、クエリの最適化や実行計画の構築時に役立ちます。データがインデックス内でどの順序で格納されているかを知ることで、クエリエンジンはより効率的な検索を行うことができます。

**Visible**

https://dev.mysql.com/doc/refman/8.0/ja/invisible-indexes.html より

>不可視のインデックスを使用すると、インデックスが必要になった場合に元に戻す必要がある破壊的な変更を行わずに、クエリーのパフォーマンスに対するインデックスの削除の影響をテストできます。 大規模なテーブルの場合、インデックスを削除して再追加するとコストがかかる可能性がありますが、インデックスを非表示にして可視にすると、高速なインプレース操作になります。

一時的にインデックスを無効にしたい時に用いるものという認識で良さそうです。
インデックスの検証を行うときに便利ですね！

</div>
</details>

https://dev.mysql.com/doc/refman/8.0/ja/show-index.html

# インデックス以外のパフォーマンスに欠かせないもの

インデックスについて調べていたところ、とても勉強になる記事がありましたので、確認してみてください！

https://recruit.gmo.jp/engineer/jisedai/blog/mysql-index-training/

**innodb_buffer_pool_size**というパラメータを適切に設定すると、パフォーマンスが上がるとのことです。


# さいごに

実システムで発行しているクエリのパフォーマンスを調べたくなりましたか？
いってらっしゃい！

# 参考


https://zenn.dev/m_yamashii/articles/mysql-index-cardinality

https://www.javadrive.jp/mysql/index/index2.html#google_vignette

https://zenn.dev/hk_206/articles/ec5f4e347caff4

https://mond.how/ja/topics/sv7z7ljlcyjax3q/otzqmzpvmse5oip

https://mebee.info/2021/12/02/post-49867/

https://qiita.com/higuo/items/c4d6629d28725118bc2d

https://ex1.m-yabe.com/archives/1697