この記事でわかること
MySQL Index 確認とは？
MySQLではPRIMARY KEY（PK）、FOREIGN KEY（FK）、UNIQUEはINFORMATION_SCHEMA.TABLE_CONSTRAINTSで参照しますが、単一カラムIndexやマルチカラムIndexはINFORMATION_SCHEMA.STATISTICSで参照することになります。
ここではSQL文を発行しながら単一カラムIndexやマルチカラムIndexの追加や削除を行いその結果を確認していきます。
使用したMySQLのバージョンとデータ
MySQLは、mysql-installer-community-8.0.24.0.msiをDeveloperDefaultモードでインストールして使用しています。
使用したデータはMariaDB Sample Databaseよりnation.zipをダウンロード後解凍して使用しています。解凍後Windows PowerShellを起動して以下の手順でMySQLに取込んでください。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
PS C:\> mysql -u root -p
Enter password:
～
mysql> source /path/to/nation.sql
以下の様に表示されます
mysql> use nation;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_nation |
+-------------------+
| continents |
| countries |
| country_languages |
| country_stats |
| guests |
| languages |
| region_areas |
| regions |
| vips |
+-------------------+
|
テスト用データを作成する
nationデータベースにはIndexを持つテーブルが存在しないので、テスト用に下記のように追加します。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
DROP TABLE IF EXISTS `vips_idx`;
SET character_set_client = utf8mb4 ;
CREATE TABLE `vips_idx` (
`vip_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `vips_idx` WRITE;
INSERT INTO `vips_idx` VALUES (1,'Jane'),(2,'Charles'),(3,'John'),(4,'Wolverine'),(5,'Rogue');
UNLOCK TABLES;
DROP TABLE IF EXISTS `regions_mlt`;
SET character_set_client = utf8mb4 ;
CREATE TABLE `regions_mlt` (
`region_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`continent_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
LOCK TABLES `regions_mlt` WRITE;
INSERT INTO `regions_mlt` VALUES (1,'Caribbean',1),(2,'Southern and Central Asia',2),(3,'Central Africa',3);
UNLOCK TABLES;
|
成功すれば以下の様に表示されます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> SHOW TABLES;
+-------------------+
| Tables_in_nation |
+-------------------+
| continents |
| countries |
| country_languages |
| country_stats |
| guests |
| languages |
| region_areas |
| regions |
| regions_mlt |
| vips |
| vips_idx |
+-------------------+
|
Indexを追加する
vips_idxテーブルに単一カラムIndexとしてidx_vip_idを追加してみましょう。
MySQLでは以下のSQL文を実行します。
ALTER TABLE テーブル名 ADD INDEX Index名(カラム名);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> ALTER TABLE vips_idx ADD INDEX idx_vip_id(vip_id);
mysql> SHOW INDEX FROM vips_idx\G
*************************** 1. row ***************************
Table: vips_idx
Non_unique: 1
Key_name: idx_vip_id
Seq_in_index: 1
Column_name: vip_id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
|
Table:vips_idxのColumn_name:vip_idにKey_name:idx_vip_idの追加が確認できます。
次にregions_mltにマルチカラムIndexとしてidx_multiを追加してみましょう。
MySQLでは以下のSQL文を実行します。
ALTER TABLE テーブル名 ADD INDEX Index名(カラム名1, カラム名2, ・・・);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> ALTER TABLE regions_mlt ADD INDEX idx_multi(region_id, continent_id);
mysql> SHOW INDEX FROM regions_mlt\G
*************************** 1. row ***************************
Table: regions_mlt
Non_unique: 1
Key_name: idx_multi
Seq_in_index: 1
Column_name: region_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: regions_mlt
Non_unique: 1
Key_name: idx_multi
Seq_in_index: 2
Column_name: continent_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
|
Table:regions_mltのColumn_name:region_idとColumn_name:continent_idにKey_name:idx_multiの追加が確認できます。
INFORMATION_SCHEMA.STATISTICSを確認する
それではMySQLのテーブルのIndex情報を提供するINFORMATION_SCHEMA.STATISTICSを確認します。
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA = 'nation' AND INDEX_NAME <>'PRIMARY' AND INDEX_NAME <> COLUMN_NAME ;
+--------------+-------------+------------+--------------+------------+--------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME |
+--------------+-------------+------------+--------------+------------+--------------+--------------+
| nation | vips_idx | 1 | nation | idx_vip_id | 1 | vip_id |
| nation | regions_mlt | 1 | nation | idx_multi | 1 | region_id |
| nation | regions_mlt | 1 | nation | idx_multi | 2 | continent_id |
+--------------+-------------+------------+--------------+------------+--------------+--------------+
|
追加したIndexが確認できました。
表示する項目は以下から適宜選択してください。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> desc INFORMATION_SCHEMA.STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(64) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | YES | | NULL | |
| TABLE_NAME | varchar(64) | YES | | NULL | |
| NON_UNIQUE | int | NO | | 0 | |
| INDEX_SCHEMA | varchar(64) | YES | | NULL | |
| INDEX_NAME | varchar(64) | YES | | NULL | |
| SEQ_IN_INDEX | int unsigned | NO | | NULL | |
| COLUMN_NAME | varchar(64) | YES | | NULL | |
| COLLATION | varchar(1) | YES | | NULL | |
| CARDINALITY | bigint | YES | | NULL | |
| SUB_PART | bigint | YES | | NULL | |
| PACKED | binary(0) | YES | | NULL | |
| NULLABLE | varchar(3) | NO | | | |
| INDEX_TYPE | varchar(11) | NO | | | |
| COMMENT | varchar(8) | NO | | | |
| INDEX_COMMENT | varchar(2048) | NO | | NULL | |
| IS_VISIBLE | varchar(3) | NO | | | |
| EXPRESSION | longtext | YES | | NULL | |
+---------------+---------------+------+-----+---------+-------+
|
EXPLAINの出力
EXPLAINを使用して、SELECTが使用するテーブルの情報を確認します。
possible_keys:idx_vip_id
key:idx_vip_id
Extra: Using index
このように、Indexが使用されていることが確認できます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> EXPLAIN SELECT vip_id FROM vips_idx GROUP BY vip_id HAVING COUNT(vip_id) = 1
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: vips_idx
partitions: NULL
type: index
possible_keys: idx_vip_id
key: idx_vip_id
key_len: 4
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index
|
同様にEXPLAINを使用して、SELECTが使用するテーブルの情報を確認します。
possible_keys:idx_multi
key:idx_multi
Extra: Using index
このように、Indexが使用されていることが確認できます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> EXPLAIN SELECT region_id, continent_id FROM regions_mlt GROUP BY region_id, continent_id HAVING COUNT(region_id) = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: regions_mlt
partitions: NULL
type: index
possible_keys: idx_multi
key: idx_multi
key_len: 8
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
|
INFORMATION_SCHEMA.TABLE_CONSTRAINTSを確認する
MySQLのテーブルにある制約の情報を提供するINFORMATION_SCHEMA.TABLE_CONSTRAINTSを確認します。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'nation';
+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+
| def | nation | PRIMARY | nation | continents | PRIMARY KEY | YES |
| def | nation | country_code2 | nation | countries | UNIQUE | YES |
| def | nation | country_code3 | nation | countries | UNIQUE | YES |
| def | nation | PRIMARY | nation | countries | PRIMARY KEY | YES |
| def | nation | countries_ibfk_1 | nation | countries | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | country_languages | PRIMARY KEY | YES |
| def | nation | country_languages_ibfk_1 | nation | country_languages | FOREIGN KEY | YES |
| def | nation | country_languages_ibfk_2 | nation | country_languages | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | country_stats | PRIMARY KEY | YES |
| def | nation | country_stats_ibfk_1 | nation | country_stats | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | guests | PRIMARY KEY | YES |
| def | nation | PRIMARY | nation | languages | PRIMARY KEY | YES |
| def | nation | PRIMARY | nation | region_areas | PRIMARY KEY | YES |
| def | nation | PRIMARY | nation | regions | PRIMARY KEY | YES |
| def | nation | regions_ibfk_1 | nation | regions | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | vips | PRIMARY KEY | YES |
+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+
|
Indexはこのテーブルに含まれていない事が確認できます。
Indexを削除する
MySQLでは以下のSQL文を実行してIndexを削除します。
ALTER TABLE テーブル名 DROP INDEX Index名;
|
1
2
|
mysql> ALTER TABLE vips_idx DROP INDEX idx_vip_id;
mysql> ALTER TABLE regions_mlt DROP INDEX idx_multi;
|
INFORMATION_SCHEMA.STATISTICSを確認してみましょう。
|
1
2
3
4
|
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA = 'nation' AND INDEX_NAME <>'PRIMARY' AND INDEX_NAME <> COLUMN_NAME ;
Empty set (0.11 sec)
|
Indexが削除されたことが確認できました。
MySQLのIndexを試してみよう
いかがでしたでしょうか。MySQLで単一カラムIndexやマルチカラムIndexの追加や削除を行いその結果をINFORMATION_SCHEMA.STATISTICSで確認しました。
ぜひご自身で実際にSQLを実行して理解を深めてください。
インフラエンジニア専門の転職サイト「FEnetインフラ」
FEnetインフラはサービス開始から10年以上『エンジニアの生涯価値の向上』をミッションに掲げ、多くのエンジニアの就業を支援してきました。
転職をお考えの方は気軽にご登録・ご相談ください。
