SQL

【SQL】インデックス（INDEX）を作成・削除する

インデックス（INDEX）を作成する

SQLの「CREATE  INDEX」または「ALTER  TABLE」を使うことで、データベースのテーブルにインデックス（INDEX）を付与することができます。

INDEX作成の基本構文

CREATE  INDEX  インデックス名  ON  テーブル名 ( カラム名1, カラム名2・・・ );

または

ALTER  TABLE  テーブル名  ADD  INDEX  インデックス名  ( カラム名1, カラム名2・・・ );

スポンサーリンク

「CREATE INDEX」でインデックスを作成する

■CREATE INDEXの実行例 ※MySQLの例

CREATE INDEX idx_sample ON sample (id, age);

■INDEXの確認

> SHOW INDEX FROM sample;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sample |          1 | idx_sample |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sample |          1 | idx_sample |            2 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

「ALTER TABLE」でインデックスを作成する

■ALTER TABLE ADD INDEXの実行例 ※MySQLの例

ALTER TABLE sample ADD INDEX idx_sample2 (name);

■INDEXの確認

> SHOW INDEX FROM sample;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sample |          1 | idx_sample2 |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

インデックス（INDEX）を削除する

SQLの「DROP  INDEX」または「ALTER  TABLE」を使うことで、テーブルのインデックス（INDEX）を削除することができます。

INDEX削除の基本構文

DROP  INDEX  インデックス名  ON  テーブル名; ※MySQLの場合

DROP  INDEX  インデックス名 ※Oracle、PostgreSQLの場合

または

ALTER  TABLE  テーブル名  DROP  INDEX  インデックス名;

「DROP INDEX」でインデックスを削除する

■INDEX削除前

> SHOW INDEX FROM sample;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sample |          1 | idx_sample2 |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

■DROP INDEXの実行例 ※MySQLの例

DROP INDEX idx_sample2 ON sample;

■INDEX削除後

> SHOW INDEX FROM sample;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)

「ALTER TABLE」でインデックスを削除する

■INDEX削除前

> SHOW INDEX FROM sample;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sample |          1 | idx_sample |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sample |          1 | idx_sample |            2 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

■ALTER TABLE DROP INDEXの実行例 ※MySQLの例

ALTER TABLE sample DROP INDEX idx_sample;

■INDEX削除後

> SHOW INDEX FROM sample;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

helpful