タイトル: インデックスの削除
SEOタイトル: SQLでインデックスを削除する方法｜DROP INDEXのRDBMS別の書き方
SQLでインデックスを削除するには、DROP INDEX文を使う。MySQLではDROP INDEX インデックス名 ON テーブル名;またはALTER TABLE テーブル名 DROP INDEX インデックス名;、PostgreSQL・OracleではDROP INDEX インデックス名;、SQL ServerではDROP INDEX テーブル名.インデックス名;のように記述するのが基本だが、対象とするRDBMSによって構文が異なる点に注意したい。
| この記事の要点 |
|---|
|
DROP INDEX文の基本構文
インデックスを削除する基本的なSQL文がDROP INDEXである。どのインデックスを削除するかを名前で指定し、RDBMSによってはどのテーブルに属するインデックスかも併せて指定する。代表的なRDBMSごとの書き方を順に見ていく。
MySQL / MariaDBの場合
MySQLおよびMariaDBでは、削除対象のインデックスがどのテーブルに属するかをON テーブル名で指定する。
|
DROP INDEX idx_user_email ON users; |
また、ALTER TABLE文の一部としてインデックスを削除することもできる。こちらの書き方は、同じALTER TABLE文の中で列の追加や変更とまとめて記述できる点が便利である。
|
ALTER TABLE users DROP INDEX idx_user_email; |
どちらの構文も結果は同じで、idx_user_emailというインデックスがusersテーブルから削除される。なお、MySQLではINDEXの代わりにKEYと書くこともできる。
PostgreSQLの場合
PostgreSQLでは、インデックス名がスキーマ内で一意であるため、テーブル名を指定せずインデックス名だけで削除する。
|
DROP INDEX idx_user_email; |
存在しないインデックスを指定するとエラーになるため、存在する場合のみ削除したいときはIF EXISTSを付ける。また、テーブルへの書き込みをブロックせずに削除したい場合はCONCURRENTLYを指定できる（この場合トランザクションブロック内では実行できない）。
|
DROP INDEX IF EXISTS idx_user_email; DROP INDEX CONCURRENTLY idx_user_email; |
Oracle Databaseの場合
OracleもPostgreSQLと同様に、インデックス名のみを指定して削除する。
|
DROP INDEX idx_user_email; |
SQL Serverの場合
SQL Serverでは、どのテーブルのインデックスかをテーブル名.インデックス名の形式で指定する。
|
DROP INDEX users.idx_user_email; |
新しいバージョンのSQL Serverでは、次のようにON句を使う構文やIF EXISTSも利用できる。
|
DROP INDEX idx_user_email ON users; DROP INDEX IF EXISTS idx_user_email ON users; |
RDBMS別の構文の違い
同じ「インデックスの削除」でも、テーブル名の指定方法やオプションはRDBMSごとに差がある。主要なRDBMSの書き方を比較すると次のようになる。
| RDBMS | 基本構文 | テーブル名の指定 | 主な補足 |
|---|---|---|---|
| MySQL / MariaDB | DROP INDEX idx ON テーブル; |
ON テーブル名で指定（必須） |
ALTER TABLE ... DROP INDEXも可 |
| PostgreSQL | DROP INDEX idx; |
不要（名前のみ） | IF EXISTS / CONCURRENTLYが使える |
| Oracle | DROP INDEX idx; |
不要（名前のみ） | 制約由来のインデックスは制約側で削除 |
| SQL Server | DROP INDEX テーブル.idx; |
テーブル名.またはON テーブル名 |
新しい版ではON句・IF EXISTSに対応 |
このように、MySQLとSQL Serverはテーブル名の指定が必要で、PostgreSQLとOracleはインデックス名のみで削除できる、という違いがある。バージョンによって対応する構文や使えるオプションが異なる場合があるため、利用するRDBMSの公式ドキュメントも確認すると確実である。
削除前にインデックスを確認する
削除を実行する前に、対象のテーブルにどのようなインデックスが存在し、それぞれの名前が何かを確認しておくとよい。インデックス名を間違えるとエラーになったり、意図しないインデックスを消してしまったりするおそれがあるためである。インデックスの一覧を確認する方法もRDBMSによって異なる。
|
-- MySQL / MariaDB SHOW INDEX FROM users;
-- PostgreSQL（psql のメタコマンド、または pg_indexes ビュー） \d users SELECT indexname FROM pg_indexes WHERE tablename = 'users';
-- Oracle SELECT index_name FROM user_indexes WHERE table_name = 'USERS';
-- SQL Server SELECT name FROM sys.indexes WHERE object_id = OBJECT_ID('users'); |
これらでインデックス名や対象列を確認したうえで、DROP INDEXを実行すると安全である。
いつインデックスを削除するか
インデックスは検索（SELECT）を高速化するために作成するものだが、すべてのインデックスが常に有益とは限らない。次のようなケースでは、インデックスの削除を検討する価値がある。
- 使われていない不要なインデックス：作成したものの、実際のクエリでは利用されていないインデックス。残しておくと無駄にディスク領域を消費し、更新時のオーバーヘッドだけが残る。
- 重複したインデックス：同じ列、または先頭列が同一の複合インデックスが重複して存在するケース。一方は冗長になりやすく、整理の対象となる。
- 書き込み性能を劣化させているインデックス：インデックスは
INSERT/UPDATE/DELETEのたびに更新されるため、数が多いと書き込み性能が低下する。検索の恩恵より更新のコストが上回るインデックスは削除を検討する。
逆に、新しくインデックスを作成する方法については関連記事のインデックスの作成を参照してほしい。削除と作成はセットで理解しておくと、インデックスの設計・見直しがしやすくなる。
削除時の落とし穴
| 注意すべきポイント |
|---|
|
よくある質問（FAQ）
Q. 存在しないインデックスを削除しようとするとどうなりますか？
多くのRDBMSでは、指定したインデックスが存在しない場合にエラーとなる。エラーを避けて「存在する場合のみ削除」したいときは、PostgreSQLやSQL Server（新しいバージョン）、MySQLなどで利用できるIF EXISTSを付ける。例えばPostgreSQLではDROP INDEX IF EXISTS idx_user_email;のように記述する。
Q. インデックスを削除するとテーブルのデータも消えますか？
消えない。DROP INDEXはインデックス（検索を高速化するための補助的なデータ構造）だけを削除するものであり、テーブル本体の行データには影響しない。削除後はそのインデックスが効いていた検索が遅くなる可能性はあるが、データそのものはそのまま残る。
Q. 削除したインデックスを元に戻すにはどうすればよいですか？
削除を取り消す専用の操作はないため、再度CREATE INDEXで同じ定義のインデックスを作り直す。元の列構成やインデックス名を控えておけば、同じインデックスを再作成できる。具体的な作成方法はインデックスの作成を参照してほしい。