IT二刀流にはプロモーションが含まれています。

MySQLでindex(インデックス)を確認する

MYSQLロゴ

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を貼ると効果あるかもしれません。

ITエンジニアの転職

いまITエンジニアの需要は急拡大しています。
ITエンジニアの経験があれば好条件で転職することも難しくありません。

Geekly

☆ 支援ご協力のお願い ☆

この記事が「役に立った」と感じたら

投げ銭の「OFUSEで応援」で支援して頂けたら励みになります!

OFUSEのやり方(説明)

関連記事
記事特集