MySQLでINDEX（インデックス）を確認、追加、削除する方法です。
テーブル作成時のINDEXや、あとからINDEXを追加したりもできます。
検証バージョン
- MySQL 8.1.0
テーブル作成
テーブルを作成時にINDEXを追加するSQLです。
mysql> CREATE TABLE idx_test(
id tinyint,
age tinyint DEFAULT 0,
name varChar(100),
index idx1(id)
);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO idx_test(id, age, name) values(1,10,'1st'), (2,20,'2nd'), (3,30,'3rd');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
CREATE TABLE時にindex インデックス名(カラム)で作成できます。
今回はidをidx1としてindexを作成しています。
テスト用のデータもINSERTしています。
INDEX（インデックス）を確認
既存のindexを確認します。
確認する方法はいくつかありますが、2パターン紹介します。
SHOW CREATE TABLEで確認
mysql> SHOW CREATE TABLE idx_test;
+----------+-------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------+
| idx_test | CREATE TABLE `idx_test` (
`id` tinyint DEFAULT NULL,
`age` tinyint DEFAULT '0',
`name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
KEY `idx1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+-------------------------------------------------+
1 row in set (0.00 sec)
KEY ‘idx1’ (‘id’)となっていますので、idがidx1というインデックス名で作成されています。
SHOW INDEX FROMで確認
mysql> SHOW INDEX FROM idx_test;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| idx_test | 1 | idx1 | 1 | id | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
Key_nameがidx1でColumn_nameがidのインデックスが作成されています。
INDEXの作成
後からINDEX（インデックス）を作成、追加する方法です。
CREATE INDEXで作成できます。
先ほどのテスト用のテーブルにidx2を追加します。
mysql> CREATE INDEX idx2 ON idx_test(age);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM idx_test;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| idx_test | 1 | idx1 | 1 | id | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
| idx_test | 1 | idx2 | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)
idx2が追加されました。
このように、INDEXは後からでも作成、追加することができます。
INDEXの削除
INDEXを削除する方法です。
INDEXは多すぎるとINSERT時のパフォーマンスを低下させます。
レコード追加するときにINDEXも追加するので多すぎると遅くなります。
不要なINDEXは削除しましょう。
INDEXのidx2を削除します。
mysql> DROP INDEX idx2 ON idx_test;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM idx_test;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| idx_test | 1 | idx1 | 1 | id | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
idx2が削除されました。
「不要なINDEXって何？」ってたまに聞かれます。
「使っていないINDEXだよ」って答えるのですが、ちょっと曖昧な気もします。
私は「不要なINDEX」の定義を、SELECTやUPDATE時にWHERE句で使用しないINDEXと考えています。
INDEXは検索パフォーマンスを上げるためのものです、SQLでレコードを絞り込むのはWHERE句です。
WHEREで使わないカラムはINDEX不要って考えです。
厳密に言うとJOIN（結合）やMIN()やMAX()など集計関数にも影響しますが。
まとめ
データの小さいテーブルにINDEXを追加してもあまり効果がありません。
データ量の大きなテーブルにINDEXを使用するとSELECTなどの検索時に速度が上がる可能性があります。
ですが、むやみやたらにINDEXを追加しまくるのも問題です。
INSERTなどのパフォーマンスが低下します。
とりあえずよくWHERE句で使用するカラム（idや日付）にINDEXを貼ると効果あるかもしれません。
