【MySQL】指定したテーブルのインデックスを確認する

SHOW INDEXでインデックスを確認する

MySQLで指定したテーブルのインデックスを確認するにはSHOW INDEX文を使う。

SHOW INDEX FROM テーブル名;

例えばusersテーブルのインデックスを確認するには以下のように実行する。

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         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          0 | uniq_users_email   |            1 | email       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_users_name_age |            1 | name        | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_users_name_age |            2 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

複数カラムからなる複合インデックスは、カラムの数だけ行が表示される。
上記のidx_users_name_ageはnameとageの2カラムからなる複合インデックスであり、Seq_in_indexがインデックス内でのカラムの並び順を表す。

SHOW KEYS FROM テーブル名でも同じ結果になる。INDEXとKEYSは同義である。

主な列の意味

SHOW INDEXの結果には以下のような列が含まれる。

列	内容
`Table`	テーブル名
`Non_unique`	ユニークインデックスでない場合は`1`、ユニークインデックスの場合は`0`
`Key_name`	インデックス名。主キーは常に`PRIMARY`
`Seq_in_index`	複合インデックス内でのカラムの並び順（1始まり）
`Column_name`	カラム名
`Cardinality`	インデックス中のユニークな値の推定数
`Null`	カラムが`NULL`を許可する場合は`YES`
`Index_type`	インデックスの種類（`BTREE`・`FULLTEXT`・`HASH`など）
`Visible`	オプティマイザから見えるインデックスかどうか

Cardinalityは統計情報に基づく推定値であり、ANALYZE TABLEを実行すると更新される。

インデックス名で絞り込む

WHERE句を付けると、特定のインデックスだけに絞り込める。

mysql> SHOW INDEX FROM users WHERE Key_name = 'idx_users_name_age';
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          1 | idx_users_name_age |            1 | name        | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_users_name_age |            2 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

information_schema.statisticsでインデックスを確認する

SQLでインデックス情報を取得する場合はinformation_schema.statisticsビューを参照する。
SHOW INDEXの結果は、内部的にはこのビューを参照して生成される。

mysql> SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE, CARDINALITY
    -> FROM information_schema.statistics
    -> WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_NAME = 'users';
+--------------------+-------------+--------------+------------+-------------+
| INDEX_NAME         | COLUMN_NAME | SEQ_IN_INDEX | NON_UNIQUE | CARDINALITY |
+--------------------+-------------+--------------+------------+-------------+
| idx_users_name_age | name        |            1 |          1 |           0 |
| idx_users_name_age | age         |            2 |          1 |           0 |
| PRIMARY            | id          |            1 |          0 |           0 |
| uniq_users_email   | email       |            1 |          0 |           0 |
+--------------------+-------------+--------------+------------+-------------+

TABLE_SCHEMAはMySQLでは「データベース」と同じ意味である。
テーブル一覧やカラム一覧を取得する方法は以下も参照。
参考: 【MySQL】テーブル一覧を取得する
参考: 【MySQL】テーブル定義とカラム一覧を確認する

information_schema.statisticsにはMySQL 8.0で18個のカラムが存在する。
主なカラムは次のとおり。

TABLE_SCHEMA: テーブルが属するデータベース名
TABLE_NAME: テーブル名
NON_UNIQUE: ユニークインデックスでない場合は1
INDEX_NAME: インデックス名
SEQ_IN_INDEX: 複合インデックス内でのカラムの並び順
COLUMN_NAME: カラム名
CARDINALITY: インデックス中のユニークな値の推定数
INDEX_TYPE: インデックスの種類
IS_VISIBLE: オプティマイザから見えるインデックスかどうか

複合インデックスのカラムをまとめて表示する

SHOW INDEXやinformation_schema.statisticsはカラムごとに行が分かれるため、複合インデックスの構成を一目で確認しにくい。
GROUP_CONCATでカラム名を連結すると、インデックスごとに1行で確認できる。

SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns_in_index, NON_UNIQUE
FROM information_schema.statistics
WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_NAME = 'users'
GROUP BY INDEX_NAME, NON_UNIQUE;

+--------------------+------------------+------------+
| INDEX_NAME         | columns_in_index | NON_UNIQUE |
+--------------------+------------------+------------+
| idx_users_name_age | name,age         |          1 |
| PRIMARY            | id               |          0 |
| uniq_users_email   | email            |          0 |
+--------------------+------------------+------------+

GROUP_CONCATのORDER BYにはSEQ_IN_INDEXを指定し、複合インデックスのカラム順を保つ。

SHOW CREATE TABLEでも確認できる

テーブル全体のDDLを確認するSHOW CREATE TABLEでも、インデックスの定義を確認できる。

mysql> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_users_email` (`email`),
  KEY `idx_users_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SHOW INDEXとは異なり、インデックスの定義がテーブル定義と一体化した形式で表示される。
カラム一覧の確認方法と併せて以下も参照。
参考: 【MySQL】テーブル定義とカラム一覧を確認する