【MySQL】インデックスの基本操作 - 作成・一覧表示・使用確認・削除

MySQLのインデックスの基本操作について解説します。

検証環境

インデックス

インデックスは“データの検索速度を高速化するための索引”です。

MySQLはデータ検索時に全てのデータを読み取るため、データ量に比例して検索時間が増加します。

そのため、インデックスを用意することが一般的です。

インデックスは特定のカラムの値について、検索用にあらかじめ整理したモノで、MySQLはインデックスが存在する場合、自動でインデックスを使って検索を高速化します。

このドキュメントでは次のテーブルに対するインデックスの操作を解説します。

Command Line

mysql> DESC items;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| price | int         | YES  |     | NULL    |       |
| stock | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM items;
+------+-----------+-------+-------+
| id   | name      | price | stock |
+------+-----------+-------+-------+
|    1 | Apple     |   200 |    10 |
|    2 | Orange    |   150 |     3 |
|    3 | Pineapple |  1100 |     3 |
|    4 | Grapes    |  3500 |     1 |
+------+-----------+-------+-------+
4 rows in set (0.00 sec)

作成

インデックスの作成はCREATE INDEX ONを使います。

基本構文

CREATE INDEX インデックス名 ON テーブル名(カラム名)

カラム名の部分にインデックスを作成するカラムを記述します。

サンプル

Command Line

___ih_hl_start
mysql> CREATE INDEX name_index ON items(name);
___ih_hl_end
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

１行目でitemsテーブルのnameカラムのインデックスを作成しています。

一覧表示

インデックスの一覧表示はSHOW INDEX FROMを使います。

基本構文

SHOW INDEX FROM テーブル名

サンプル

Command Line

mysql> SHOW INDEX FROM items;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          1 | name_index |            1 | name        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

Key_nameにインデックス名が表示されます。

使用

MySQLは検索時に最適なインデックスを自動で適用します。

クエリ実行計画を確認できるEXPLAINを使って確認してみましょう。

Command Line

mysql> SHOW INDEX FROM items;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          1 | name_index |            1 | name        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM items WHERE name = 'Apple';
+------+-------+-------+-------+
| id   | name  | price | stock |
+------+-------+-------+-------+
|    1 | Apple |   200 |    10 |
+------+-------+-------+-------+
1 row in set (0.00 sec)

___ih_hl_start
mysql> EXPLAIN SELECT * FROM items WHERE name = 'Apple';
___ih_hl_end
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | items | NULL       | ref  | name_index    | name_index | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAINはクエリの先頭に付けます。

possible_keysは利用可能なインデックス、keyが実際に使用したインデックスです。

実行結果からname_indexを使用したことが分かります。

また、name_indexインデックスはnameカラムのインデックスであるため、priceカラムで検索条件を指定するとMySQLはこのインデックスを使用しません。

Command Line

mysql> SELECT * FROM items WHERE price = 200;
+------+-------+-------+-------+
| id   | name  | price | stock |
+------+-------+-------+-------+
|    1 | Apple |   200 |    10 |
+------+-------+-------+-------+
1 row in set (0.00 sec)

___ih_hl_start
mysql> EXPLAIN SELECT * FROM items WHERE price = 200;
___ih_hl_end
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | items | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAINで表示した情報から、keyがNULLになり、インデックスが使用されていないことが分かります。

削除

インデックスの削除はDROP INDEX ONを使います。

基本構文

DROP INDEX インデックス名 ON テーブル名

サンプル

Command Line

mysql> DROP INDEX name_index ON items;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM items;
Empty set (0.00 sec)