LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@4649rixxxz(Ryota I)

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

Last updated at Posted at 2023-11-21

はじめに

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

検証環境

  • 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

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

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

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

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

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

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

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

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

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

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

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

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

解答
  • 否定形(<>、!=、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)

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

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

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

# 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カラムを設定した一見複合インデックスを活かせそうなクエリに見えるが、インデックスのフルスキャンになってしまっている。複合インデックスを設定する場合は、設定しているカラムの使う順番に注意しなければならない。

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

解答

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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+---------------------+

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

解答

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 より

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

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

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

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

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

さいごに

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

参考

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?