はじめに
前回の記事では、カラムに設定するデータ型について説明しました。
データベース設計では、
- どのようなテーブルを作るか
- どのようなカラムを持たせるか
- テーブル同士をどのように関連付けるか
- どのような制約を設定するか
- どのデータ型を使用するか
といったことを決めていきます。
しかし、データを正しく保存できるようになっただけでは、実際のシステムで十分とは限りません。
データ量が増えてくると、今度は
「検索に時間がかかる」
という問題が発生することがあります。
そこで重要になってくるのが、今回説明する**インデックス（INDEX）**です。
今回は、次のSQLを例にしながら、
SELECT *
FROM users
WHERE email = 'example@example.com';
「インデックスがない場合」と「インデックスがある場合」で、データの探し方がどのように変わるのかを見ていきます。
この記事の対象者
この記事は、次のような人を対象としています。
- エンジニアだけど、データベースについてあまり詳しくない
- データベース設計の経験が浅い
- INDEXという言葉は聞いたことがあるが、何のために使うのか分からない
- 「検索が遅かったら、とりあえずINDEXを付ければいい」と思っている
- 複合インデックスについてよく分からない
なお、この記事では特定のデータベース製品に依存した細かな実装ではなく、インデックス設計の基本的な考え方を中心に説明します。
インデックスとは
インデックスとは、簡単に言えば、
データベースから目的のデータを探しやすくするための仕組み
です。
本の巻末にある「索引」をイメージすると分かりやすいでしょう。
例えば、分厚い本の中から「データベース」という言葉について説明しているページを探すとします。
索引がなければ、
1ページ目を見る
↓
2ページ目を見る
↓
3ページ目を見る
↓
……
↓
目的のページを見つける
というように、最初から順番に探していく必要があります。
一方、索引があれば、
索引
----------------
インデックス 120ページ
外部キー 85ページ
データベース 10ページ
正規化 60ページ
のように、目的の情報がどこにあるのかを探しやすくなります。
データベースのインデックスも、考え方としてはこれに近いものです。
インデックスがない場合
例えば、次のような users テーブルがあるとします。
users
------------------------------------------------
id | name | email
------------------------------------------------
1 | 山田 太郎 | taro@example.com
2 | 佐藤 花子 | hanako@example.com
3 | 鈴木 一郎 | ichiro@example.com
...
ここから、メールアドレスを条件にユーザーを検索します。
SELECT *
FROM users
WHERE email = 'example@example.com';
email に適切なインデックスがない場合、データベースは状況によってはテーブル内のデータを順番に確認していく必要があります。
イメージとしては、次のような状態です。
usersテーブル
1件目 → 違う
2件目 → 違う
3件目 → 違う
4件目 → 違う
5件目 → 違う
:
:
100000件目 → 発見
このようにテーブル全体を順番に確認する検索を、一般にフルテーブルスキャンなどと呼びます。
数十件、数百件程度のデータであれば、大きな問題にならないこともあります。
しかし、
1万件
10万件
100万件
1000万件
とデータ量が増えていくと、検索コストも無視できなくなってきます。
インデックスがある場合
では、email カラムにインデックスが設定されている場合はどうでしょうか。
例えば、次のようにインデックスを作成します。
CREATE INDEX idx_users_email
ON users(email);
これによって、データベースは email を検索するためのインデックスを利用できるようになります。
イメージとしては、
email用インデックス
------------------------------
example@example.com → 対象データ
hanako@example.com → 対象データ
ichiro@example.com → 対象データ
taro@example.com → 対象データ
のような、検索しやすい構造が別途用意されていると考えると分かりやすいでしょう。
先ほどと同じSQLを実行したとします。
SELECT *
FROM users
WHERE email = 'example@example.com';
インデックスを利用できれば、
テーブルを最初から全部調べる
のではなく、
インデックスから
example@example.com
を探す
↓
対象のデータを取得する
という流れで検索できる可能性があります。
つまり、
インデックスなし
↓
テーブル全体から探す
インデックスあり
↓
目的のデータを探しやすくなる
という違いがあります。
インデックスを付けるメリット
インデックスの大きなメリットは、検索を高速化できる可能性があることです。
特に、
WHERE
で頻繁に検索されるカラムは、インデックスの候補になります。
例えば、
SELECT *
FROM users
WHERE email = 'example@example.com';
であれば、email は検索条件として使用されています。
ほかにも、
SELECT *
FROM orders
WHERE user_id = 100;
のように、特定ユーザーの注文一覧を頻繁に取得するのであれば、user_id にインデックスを設定することを検討できます。
インデックスは、検索だけでなく、
JOINORDER BYGROUP BY
などの処理でも利用される場合があります。
ただし、
「WHEREに使っているカラムには全部インデックスを付ける」
という考え方は適切ではありません。
ここがインデックス設計で重要なところです。
インデックスにもデメリットがある
インデックスを作成すると検索が速くなる可能性があります。
それなら、
「すべてのカラムにインデックスを付ければいいのでは？」
と思うかもしれません。
しかし、インデックスにもデメリットがあります。
# データの追加・更新・削除が遅くなる可能性がある
インデックスは、テーブルとは別に管理される検索用のデータ構造です。
そのため、
INSERT
でデータを追加した場合、テーブルにデータを保存するだけでなく、関連するインデックスも更新する必要があります。
同じように、
UPDATE
DELETE
でも、場合によってはインデックスの更新が発生します。
イメージすると、
インデックスなし
INSERT
↓
テーブルを更新
に対して、
インデックスあり
INSERT
↓
テーブルを更新
↓
インデックスも更新
となります。
そのため、インデックスを増やせば増やすほど、更新処理の負担も増える可能性があります。
インデックスは容量も使用する
インデックス自体もデータとして保存されます。
そのため、
テーブルのデータ
+
インデックスのデータ
という形で、ストレージを使用します。
大規模なテーブルに大量のインデックスを作成すると、インデックスだけでも相応の容量を使用する場合があります。
つまり、インデックスは無料で高速化してくれる仕組みではありません。
インデックスを付けすぎる問題
ここまでをまとめると、
インデックス
↓
検索が速くなる可能性がある
しかし
インデックスを増やす
↓
INSERT / UPDATE / DELETEの負荷が増える
↓
保存容量も増える
という関係があります。
例えば、次のテーブルがあったとします。
users
--------------------
id
name
email
age
address
phone_number
created_at
updated_at
ここで、
nameにもINDEX
emailにもINDEX
ageにもINDEX
addressにもINDEX
phone_numberにもINDEX
created_atにもINDEX
updated_atにもINDEX
と何でもインデックスを追加するのは、必ずしも良い設計ではありません。
重要なのは、
実際にどのようなSQLで検索されるのか
を考えることです。
例えば、ユーザーをメールアドレスで検索する処理が非常に多いのであれば、
WHERE email = ?
という検索に合わせて email のインデックスを検討します。
一方、ほとんど検索条件として使われないカラムにインデックスを作成しても、メリットより管理コストの方が大きくなる可能性があります。
値の種類が少ないカラムにも注意する
インデックスを考える際には、そのカラムにどの程度異なる値が存在するのかも重要です。
例えば、次のような is_active カラムがあるとします。
is_active
---------
true
false
値が true と false の2種類しか存在しない場合、インデックスを使用しても大量のデータが該当する可能性があります。
例えば100万件のユーザーのうち、
true : 950,000件
false : 50,000件
だった場合、
SELECT *
FROM users
WHERE is_active = true;
を実行すると、結局95万件ものデータが対象になります。
このような場合、インデックスを作ったからといって必ず高速になるとは限りません。
インデックスでは、
値をどれだけ絞り込めるか
も重要になります。
複合インデックスとは
インデックスは、1つのカラムだけでなく、複数のカラムを組み合わせて作ることもできます。
これを複合インデックスと呼びます。
例えば、次のようなSQLが頻繁に実行されるとします。
SELECT *
FROM orders
WHERE user_id = 100
AND status = 'completed';
この場合、
user_id
status
の2つを組み合わせたインデックスを検討できます。
CREATE INDEX idx_orders_user_id_status
ON orders(user_id, status);
これが複合インデックスです。
イメージとしては、
user_id + status
--------------------------------
100 + completed
100 + pending
101 + completed
101 + canceled
...
のように、複数の条件を組み合わせてデータを探しやすくするものです。
複合インデックスではカラムの順番が重要
複合インデックスでは、
(user_id, status)
と
(status, user_id)
は同じものではありません。
インデックスに指定するカラムの順番が重要になります。
例えば、
CREATE INDEX idx_orders_user_id_status
ON orders(user_id, status);
という複合インデックスを作った場合、
WHERE user_id = 100
や、
WHERE user_id = 100
AND status = 'completed'
といった検索では利用しやすい一方、
WHERE status = 'completed'
だけの検索では、同じように有効活用できない場合があります。
これは、複合インデックスが基本的に
user_id
↓
status
という順番を意識した構造になっているためです。
初心者のうちは、
複合インデックスは、カラムの組み合わせだけでなく順番も重要
ということを覚えておくとよいでしょう。
単一インデックスを2つ作ることと、複合インデックスは同じではない
例えば、
CREATE INDEX idx_orders_user_id
ON orders(user_id);
CREATE INDEX idx_orders_status
ON orders(status);
という2つのインデックスと、
CREATE INDEX idx_orders_user_id_status
ON orders(user_id, status);
という複合インデックスは別物です。
WHERE user_id = 100
AND status = 'completed'
という検索が多いのであれば、単純にそれぞれへインデックスを付けるだけではなく、複合インデックスを検討する場合があります。
どのインデックスが適しているかは、
- 実際に実行されるSQL
- データ件数
- 値の偏り
- DBMS
- 更新頻度
などによって変わります。
PRIMARY KEYやUNIQUE制約とインデックス
これまでの記事では、PRIMARY KEY や UNIQUE 制約について説明しました。
多くのデータベースでは、
PRIMARY KEY
や
UNIQUE
を設定すると、それを実現するためのインデックスが作成されることがあります。
例えば、
users
------------------------------
id PRIMARY KEY
email UNIQUE
という設計の場合、
id用のインデックス
email用のインデックス
がすでに存在している可能性があります。
そのため、
CREATE UNIQUE INDEX ...
などを無条件に追加すると、似た役割のインデックスを重複して作ってしまうことがあります。
インデックスを追加する際には、
すでにどのようなインデックスが存在しているのか
を確認することも重要です。
インデックスを付ければ必ず使われるわけではない
ここでもう一つ重要なのが、
インデックスを作成したからといって、必ずそのインデックスが使われるわけではない
という点です。
データベースには、SQLをどのように実行するかを判断する仕組みがあります。
例えば、
SELECT *
FROM users
WHERE email = 'example@example.com';
を実行したとき、
インデックスを使う
または
テーブル全体を検索する
といった実行方法の中から、データベースが適切だと判断した方法を選択します。
そのため、
INDEXを付けた
↓
絶対に高速になる
とは限りません。
実際のシステムでは、SQLの実行計画を確認しながら、
「このSQLでは本当にインデックスが利用されているのか」
を確認することが重要です。
代表的には EXPLAIN などの機能を使って実行計画を確認します。
ただし、実行計画の詳しい読み方については、今回の「インデックス設計の基本」からは少し発展的な内容になるため、ここでは
インデックスは作るだけではなく、実際にどう使われているかを確認する
という考え方だけ覚えておきましょう。
では、どのカラムにインデックスを付ければいいのか
初心者のうちは、次のような順番で考えると分かりやすいでしょう。
① 実際にどのようなSQLが実行されるか確認する
↓
② WHEREやJOINなどで頻繁に使われるカラムを確認する
↓
③ データ量や値の種類を確認する
↓
④ インデックスを検討する
↓
⑤ 実行計画や実際の性能を確認する
重要なのは、
テーブルを作るときに、とりあえず大量のインデックスを作っておく
という考え方ではありません。
インデックスは、
実際の検索処理に合わせて設計する
ことが重要です。
今回の例を整理する
今回使用したSQLをもう一度見てみます。
SELECT *
FROM users
WHERE email = 'example@example.com';
# インデックスがない場合
イメージとしては、
usersテーブル
↓
1件ずつ確認
↓
1件ずつ確認
↓
1件ずつ確認
↓
目的のemailを発見
となります。
テーブル全体を検索
する可能性があるため、データ量が多くなると検索コストも増えていきます。
# インデックスがある場合
CREATE INDEX idx_users_email
ON users(email);
のようなインデックスがあれば、
emailのインデックス
↓
example@example.comを探す
↓
対象となるusersのデータを取得
という形で、目的のデータを探しやすくなります。
ただし、
検索が速くなる可能性
と引き換えに、
INSERT / UPDATE / DELETEの負荷
インデックス用の保存容量
インデックス管理の複雑さ
も増えるため、必要なものだけを設計することが大切です。
インデックス設計で意識したいこと
インデックスについて、最初から細かな内部構造まで理解する必要はありません。
まずは、次の関係を理解しておくとよいでしょう。
インデックスなし
↓
目的のデータを探すために
多くのデータを確認する可能性がある
インデックスあり
↓
目的のデータを探しやすくなる
↓
検索が高速になる可能性がある
一方で、
インデックスを増やす
↓
更新処理の負荷が増える
↓
容量も使用する
というトレードオフがあります。
したがって、
インデックスは多ければ多いほど良いものではない
ということが重要です。
まとめ
今回は、データベース設計におけるインデックス設計の基本について説明しました。
重要なポイントをまとめると、次のようになります。
- インデックスは、目的のデータを探しやすくするための仕組み
- インデックスがない場合、テーブル全体を検索することがある
- インデックスによって検索を高速化できる可能性がある
-
WHEREやJOINなどで頻繁に利用するカラムはインデックスの候補になる - インデックスは
INSERT、UPDATE、DELETEの負荷を増やす場合がある - インデックス自体も保存容量を使用する
- インデックスを付けすぎればよいわけではない
- 複数カラムを組み合わせる「複合インデックス」もある
- 複合インデックスではカラムの順番も重要
- インデックスを作っても必ず利用されるとは限らない
- 実際のSQLや実行計画を確認しながら設計することが重要
データベース設計というと、
テーブル
カラム
主キー
外部キー
データ型
など、「どのようにデータを保存するか」に目が向きがちです。
しかし、実際のシステムでは、
保存したデータをどのように効率よく取得するか
も重要な設計要素になります。
インデックスは検索性能を改善する非常に強力な仕組みですが、何でも付ければよいわけではありません。
まずは、
どのSQLが頻繁に実行されるのか
を確認し、
その検索を効率よくするには
どのインデックスが必要なのか
という順番で考えていくことが、インデックス設計の第一歩です。