[SQLのはじめ方] Part20: INDEXの基本と作成方法

データベースの検索速度を劇的に向上させるINDEXの仕組みと使い方を学びましょう。

1. INDEX（インデックス）とは？ 🤔

INDEX（インデックス）は、データベースのテーブルから特定のデータを高速に検索するための「索引」のようなものです。分厚い本の巻末にある索引を使うと、目的のページをすぐに見つけられますよね？データベースのINDEXも同じ原理で、大量のデータの中から目的の行を素早く見つけ出すのに役立ちます。

特にデータ量が多くなると、INDEXがないテーブルの検索は非常に時間がかかることがあります（全データを確認する必要があるため）。INDEXを適切に設定することで、検索パフォーマンスを大幅に改善できます。🚀

ポイント: INDEXは主にSELECT文のWHERE句やJOIN句のパフォーマンスを向上させるために使用されます。

更新速度の低下: データの追加（INSERT）、更新（UPDATE）、削除（DELETE）を行う際に、INDEXも更新する必要があるため、処理速度が遅くなる可能性があります。
ストレージ容量の消費: INDEX自体もデータを保持するため、ディスク容量を消費します。データ量やINDEXの種類によっては、無視できないサイズになることもあります。💾
設計・管理コスト: どのカラムにINDEXを作成すべきか、適切に管理する必要があります。不要なINDEXはデメリットしかもたらしません。

INDEXは万能薬ではありません。メリットとデメリットを理解し、必要な箇所に適切に作成することが重要です。

INDEXはCREATE INDEX文を使って作成します。基本的な構文は以下の通りです。


CREATE INDEX index_name
ON table_name (column1, column2, ...);

index_name: 作成するINDEXの名前（テーブル内で一意である必要があります）。分かりやすい名前をつけましょう（例: idx_users_email）。
table_name: INDEXを作成するテーブルの名前。
column1, column2, ...: INDEXを作成するカラムの名前。複数のカラムを指定すると、それらのカラムを組み合わせた複合インデックスが作成されます。

usersテーブルのemailカラムにINDEXを作成する場合：


CREATE INDEX idx_users_email
ON users (email);

これにより、WHERE email = '...'のような検索が高速化されます。

ordersテーブルでcustomer_idとorder_dateの両方で検索することが多い場合：


CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

複合インデックスでは、指定したカラムの順番が重要になります。一般的には、絞り込み条件としてよく使われるカラム、または値の種類が多い（カーディナリティが高い）カラムを先に指定します。

カラムの値が一意であることを保証したい場合は、UNIQUEキーワードを追加します。例えば、usersテーブルのusernameが一意である必要がある場合：


CREATE UNIQUE INDEX uq_users_username
ON users (username);

すでに重複する値が存在する場合、UNIQUE INDEXの作成は失敗します。また、UNIQUE INDEXが設定されたカラムに重複する値をINSERTまたはUPDATEしようとするとエラーになります。

不要になったINDEXや、設計を見直して削除したい場合はDROP INDEX文を使います。データベースによってはテーブル名を指定する必要がある場合もあります。


DROP INDEX index_name;

※MySQLではDROP INDEX index_name ON table_name;のようにテーブル名も指定します。

先ほど作成したidx_users_emailインデックスを削除する場合：


-- PostgreSQLなど
DROP INDEX idx_users_email;

-- MySQL
DROP INDEX idx_users_email ON users;

注意: DROP INDEXは慎重に行いましょう。パフォーマンスに影響を与える可能性があります。削除前に、本当に不要かどうか、影響範囲を確認することが重要です。

どのカラムに作成するか？:
- WHERE句で頻繁に検索条件として使われるカラム。
- JOIN句で結合キーとして使われるカラム（特に外部キー）。
- ORDER BY句で頻繁にソートに使われるカラム。
INDEXの貼りすぎに注意: 前述の通り、INDEXは更新処理のオーバーヘッドとストレージ消費を伴います。本当に必要なカラムだけに絞って作成しましょう。
カーディナリティを考慮する: 値の種類が非常に少ないカラム（例：性別カラムで ‘男性’, ‘女性’, ‘その他’ しかない場合）に対するINDEXは、効果が薄いか、逆効果になることがあります。データベースがINDEXを使うかどうかは、データの分布状況にも依存します。
メンテナンス: データが頻繁に追加・更新・削除されるテーブルでは、INDEXの断片化が進み、パフォーマンスが低下することがあります。データベースの種類によっては、定期的なINDEXの再構築（REINDEX）や統計情報の更新（ANALYZE）が推奨されます。

INDEXの設計は、アプリケーションの特性やデータの傾向を考慮して行う必要があります。次のステップ「クエリの実行計画とEXPLAINの使い方」で、作成したINDEXが実際にクエリで使われているかを確認する方法を学びます。

INDEXは、SQLのパフォーマンスチューニングにおいて非常に重要な要素です。基本的な仕組み、作成・削除方法、そしてメリット・デメリットを理解し、適切に活用することで、データベースの応答速度を大きく改善することができます。

ただし、闇雲に作成するのではなく、クエリの傾向やデータの特性を分析し、効果的なINDEX設計を心がけましょう！💪