SQLのインデックス（INDEX）を完全ガイド！初心者でもわかる検索速度の向上方法

新人と先輩の会話形式で理解しよう

新人

「SQLでデータ検索の速度を上げる方法ってありますか？」

先輩

「それならインデックス（INDEX）を使うといいよ！インデックスを適切に設定すれば、データベースの検索速度を大幅に向上させることができるんだ。」

新人

「インデックスって何ですか？」

先輩

「簡単に言うと、本の目次のようなものだよ。目次があると、ページを探すのが速くなるよね？同じように、データベースでも特定のデータをすぐに見つけられるようにするのがインデックスなんだ。」

1. インデックスとは？

SQLのインデックス（INDEX）とは、テーブルのデータを高速に検索するための仕組みです。インデックスを作成すると、データベースが効率よくデータを探し出すことができます。

インデックスの特徴

データ検索の速度が向上する
テーブルのデータが多くなるほど効果を発揮する
INSERTやUPDATEの処理が遅くなることがある

例えば、数百万件のデータがある社員テーブルemployeeで、特定の社員を検索するとき、インデックスがないと1件ずつチェックすることになります。しかし、インデックスがあればデータベースは高速に検索できます。

2. インデックスの作成（CREATE INDEX）の基本

インデックスを作成するには、CREATE INDEXを使用します。

1. インデックスを作成する

例えば、employeeテーブルのnameカラムにインデックスを作成すると、社員名での検索が高速になります。


CREATE INDEX idx_employee_name ON employee(name);

このSQLを実行すると、nameカラムにインデックスが作成されます。

2. インデックスを利用した検索

インデックスが作成されると、以下のような検索が高速になります。


SELECT * FROM employee WHERE name = '田中 太郎';

このSQLを実行すると、インデックスを利用して高速に検索できます。

3. インデックスがない場合の検索との比較

インデックスなしの場合、データベースはすべてのデータを順番に調べるため、検索に時間がかかります。しかし、インデックスがあると、データベースは目次のように検索できるため、処理が高速化されます。

検索方法	処理時間
インデックスなし（全件検索）	3.5秒
インデックスあり	0.02秒

このように、インデックスを活用することで、検索速度が大幅に向上します。

4. 複合インデックス（複数のカラムにインデックスを作成する方法）

複合インデックスとは、複数のカラムを組み合わせたインデックスのことです。通常のインデックスは1つのカラムに設定されますが、複合インデックスを使用すると、複数のカラムを組み合わせた検索を高速化できます。

1. 複合インデックスの作成

例えば、employeeテーブルで、department（部署）とsalary（給与）の両方を検索条件にする場合、複合インデックスを作成すると検索が高速になります。


CREATE INDEX idx_department_salary ON employee(department, salary);

このインデックスを作成すると、次のような検索が高速になります。


SELECT * FROM employee WHERE department = '営業' AND salary > 5000000;

2. 複合インデックスの注意点

複合インデックスは指定した順番で検索する場合に最適化されます。例えば、以下のクエリは最適化されます。


SELECT * FROM employee WHERE department = '営業';

しかし、以下のようにsalaryだけを検索すると、インデックスが適用されない可能性があります。


SELECT * FROM employee WHERE salary > 5000000;

複合インデックスを使用する場合は、先に指定したカラムを条件に含めることが重要です。

5. インデックスの利点と欠点（パフォーマンス向上とデメリット）

インデックスはデータ検索の速度を向上させますが、デメリットもあります。

1. インデックスの利点

検索速度の向上 - 大量のデータの中から素早く目的のデータを取得できる
データ取得の負荷軽減 - データベースの処理負担を軽減し、システムの応答速度を改善
複数の条件検索を最適化 - 複合インデックスを使えば、多くの条件を組み合わせた検索も高速化

2. インデックスの欠点

INSERT・UPDATE・DELETEが遅くなる - データが変更されるたびにインデックスも更新されるため、書き込み処理が遅くなる
ストレージの使用量が増える - インデックス用のデータが増え、大量のインデックスを作成するとストレージの消費が大きくなる
不適切なインデックスは逆効果 - インデックスを作りすぎると、かえってパフォーマンスが低下することがある

インデックスは検索を最適化するために必要ですが、適切な設計が重要です。

6. インデックスを利用した検索の最適化（EXPLAINでの確認）

SQLの実行計画を確認することで、インデックスが適切に使われているかを確認できます。

1. EXPLAINを使ってインデックスを確認する

EXPLAINを使用すると、クエリの実行計画を確認できます。


EXPLAIN SELECT * FROM employee WHERE name = '田中 太郎';

このSQLを実行すると、以下のような結果が得られます。

id	select_type	table	type	possible_keys	key	rows
1	SIMPLE	employee	ref	idx_employee_name	idx_employee_name	1

この結果のkey列にidx_employee_nameが表示されていれば、インデックスが使用されていることを示します。

2. インデックスが使われていない場合の対処

もしkeyがNULLになっている場合は、インデックスが利用されていない可能性があります。その場合、以下をチェックしましょう。

インデックスが適用されるカラムで検索しているか
ワイルドカード検索（LIKE '%文字列%'）を使っていないか
関数を使った検索（UPPER(name) = 'TANAKA' など）をしていないか

3. 効率的なインデックス設計のポイント

検索頻度の高いカラムにインデックスを作成する
更新頻度の高いカラムにはインデックスを作成しすぎない
複合インデックスを使って検索を最適化する

このように、インデックスを適切に活用することで、SQLの検索速度を大幅に向上させることができます。

7. インデックスの削除（DROP INDEX）

不要になったインデックスは削除することができます。インデックスが多すぎると、データの更新（INSERT・UPDATE・DELETE）のパフォーマンスが低下するため、不要なインデックスは適切に管理しましょう。

1. インデックスを削除する方法

インデックスを削除するには、DROP INDEXを使用します。例えば、以前作成したidx_employee_nameというインデックスを削除するには、以下のSQLを実行します。


DROP INDEX idx_employee_name ON employee;

このSQLを実行すると、employeeテーブルのidx_employee_nameインデックスが削除されます。

2. インデックス削除時の注意点

インデックスを削除しても、元のテーブルデータには影響しない
削除すると検索速度が低下する可能性がある
本当に不要なインデックスか確認するために、EXPLAINで使用状況をチェックする

8. インデックスの種類（ユニーク・フルテキスト・パーティション）

SQLには、さまざまな種類のインデックスがあります。用途に応じて適切なインデックスを選択することで、パフォーマンスを向上させることができます。

1. ユニークインデックス（UNIQUE INDEX）

ユニークインデックスは、特定のカラムの値が重複しないようにするインデックスです。主キー（PRIMARY KEY）とは異なり、テーブル内で複数のユニークインデックスを作成できます。


CREATE UNIQUE INDEX idx_unique_email ON employee(email);

このインデックスを作成すると、同じメールアドレスを持つデータを登録できなくなります。

2. フルテキストインデックス（FULLTEXT INDEX）

フルテキストインデックスは、テキスト検索を高速化するために使用されます。特にLIKE '%文字列%'のような検索を頻繁に行う場合に有効です。


CREATE FULLTEXT INDEX idx_fulltext_description ON products(description);

このインデックスを作成すると、descriptionカラムに対する全文検索が高速になります。

3. パーティションインデックス（PARTITIONED INDEX）

パーティションインデックスは、大量のデータを扱う場合にデータを分割して管理するインデックスです。データを特定の条件で分割し、それぞれのパーティションにインデックスを作成することで、検索を最適化できます。

MySQLのパーティションを使用するには、以下のようにテーブルを作成します。


CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p1 VALUES LESS THAN (2022),
    PARTITION p2 VALUES LESS THAN (2023),
    PARTITION p3 VALUES LESS THAN (2024)
);

このように、データを年ごとに分割すると、特定の年のデータ検索が高速になります。

9. 実践！適切なインデックス設計のテクニック

インデックスは適切に設計することで、データベースのパフォーマンスを最大限に引き出せます。ここでは、実践的なインデックスの設計方法を紹介します。

1. よく使う検索条件にインデックスを設定する

頻繁にWHERE句で使用するカラムにはインデックスを作成することで、検索が高速になります。


CREATE INDEX idx_department ON employee(department);

2. 文字列検索にはフルテキストインデックスを活用する

長いテキストデータに対してLIKE '%文字列%'を使用すると検索が遅くなるため、フルテキストインデックスを使うのが有効です。


CREATE FULLTEXT INDEX idx_fulltext_comment ON reviews(comment);

3. インデックスを作りすぎない

インデックスが多すぎると、INSERT・UPDATE・DELETEのパフォーマンスが低下します。不要なインデックスは削除し、適切な数に抑えましょう。

4. インデックスの最適化を定期的に行う

データの増加やクエリの変更に応じて、インデックスの最適化を行うことが重要です。


ANALYZE TABLE employee;
OPTIMIZE TABLE employee;

5. インデックスの活用事例まとめ

使用目的	適用するインデックス
検索を高速化	通常のインデックス（CREATE INDEX）
重複データを防ぐ	ユニークインデックス（CREATE UNIQUE INDEX）
全文検索を高速化	フルテキストインデックス（CREATE FULLTEXT INDEX）
大量データを効率的に管理	パーティションインデックス（PARTITION）

このように、インデックスを適切に活用することで、データベースのパフォーマンスを向上させることができます。

この記事を読んだ人からの質問