【初心者でも怖くない】インデックスとは？使用するメリットとデメリット | データベース入門
はじめに
データベースやSQLについて学んでいくと必ずエンカウントする「インデックス」
「データベース内のテーブルのデータを効率的に検索するための機能」だということはなんとなく理解しているものの、 それが具体的にどんなもので、使えると何が嬉しいのかという部分がイメージしにくい初学者の方は少なくないのではないでしょうか。
今回はデータベースにおけるインデックスに関する概要と、具体的な作成方法・削除方法、メリットとデメリットについて簡潔にまとめてみました。
インデックスとは何か？
インデックスは、端的に言えばテーブルの「特定の列の値」と「レコードのID」の対応表のようなものです。
レコードを取得する際、データベースはこのインデックスを使用して検索対象のレコードを効率的に見つけ出すことが可能です。
テーブルの特定の行へのアクセスを、マンションへの宅配便に例えると以下のように表現することができます。
- インデックス無：マンションの1号室から順に1室ずつ回りながら配達先を探す
- インデックス有：住民の名前と部屋番号の対応表を確認して目的の配達先に向かう
インデックス無しの状態がインデックス有りの状態と比較して非効率であることは言うまでもありませんが、これはデータベースのテーブルに置き換えても同様です。
例えば数万のレコードを抱えたテーブルから目的のレコードを探し当てるのに、上からひとつひとつ見て回ったのでは非常に効率が悪く、全体の処理にその分時間も要してしまいます。
そのため多くのデータベースではこのインデックスという対応表を検索対象のテーブルとは別領域に設けることによって、検索の速度と効率の向上を図っています。
以下にもう少し具体的な例として、usersテーブルのname列に対してインデックスを追加した場合の例を示します。
インデックス
| name | ID |
|---|---|
| Alice | 1 |
| Bob | 2 |
| Carol | 3 |
usersテーブル
| ID | name | age |
|---|---|---|
| 1 | Alice | 23 |
| 2 | Bob | 37 |
| 3 | Carol | 42 |
name列に対してインデックスが張られたusersテーブルから「name = "Carol"」のレコードを取得する場合、まずインデックスからname="Carol"のIDを特定し、 その取得したIDを使ってusersテーブルからCarolのレコードを特定することができます。
仮にこのusersテーブルが10万行に及ぶユーザーデータを有していたとしても、この2ステップのクエリの実行でレコードを特定することができるのが インデックスを追加する大きなメリットのひとつです。
インデックスの作成方法
作成方法
ここまででインデックスを追加することによるメリットについてはイメージがついたかと思います。 ここからは具体的にインデックスを追加する方法について追っていきます。
インデックスの作成にはCREATE INDEX文を使用します。 以下は基本的なCREATE INDEX文の例です。
- 例文
CREATE INDEX <スキーマ名>.<インデックス名> ON <インデックスを作成するテーブル名>（<カラム名> , ... ）
- 商品テーブルの商品コードに対してインデックスを作成する場合
CREATE INDEX INDEX_PRODUCTS_ON_CODE ON PRODUCTS（CODE）
スキーマ: データベースの構造を表現する設計図のこと。CREATE INDEX文でスキーマを指定しなかった場合、インデックスは親テーブルと同じスキーマに作成されます。
削除方法
次にインデックスの削除について。 インデックスの削除にはDROP INDEX文を使用します。 以下は基本的なDROP INDEX文の例です。
- 例文
DROP INDEX <スキーマ名>.<インデックス名>
- 商品テーブルの商品コードに関するインデックスを削除する場合
DROP INDEX INDEX_PRODUCTS_ON_CODE
インデックス追加による弊害
オーバーヘッドの追加
インデックスの追加によるメリットについては冒頭で述べた通りですが、一見万能に思えるインデックスにも追加に伴う弊害があります。
インデックスは検索速度、効率を向上させる一方で、ストレージと更新時間に対するオーバーヘッドの追加に相当します。 オーバーヘッドとは、レコードの追加・更新・削除と並べ替えの際に、インデックスにかかるパフォーマンスコストのことを指します。
冒頭でも述べた通りテーブルとは別領域にインデックス用のデータを蓄積するため、データベースに追加のストレージスペースが必要となります。 また、インデックスが張られたカラムの追加・更新・削除と並べ替えに応じて、そのカラムに対するインデックスの更新も行われるため、更新処理速度が低下してしまうデメリットがあります。
従ってインデックスの対象とするべきカラムかどうか検討する際には、そのテーブルのレコードの件数やカーディナリティといった指標を元に慎重に吟味していくべきでしょう。
まとめ
インデックスとは何か？
インデックスはデータベース内のテーブルのレコードを効率的に検索するための機能。
インデックスの作成方法・削除方法
インデックスの追加
CREATE INDEX <スキーマ名>.<インデックス名> ON <インデックスを作成するテーブル名>（<カラム名> , ... ）
インデックスの削除
DROP INDEX <スキーマ名>.<インデックス名>
メリットとデメリット
メリット
書籍の目次のように、取得したいデータへアクセスする速度と効率を向上してくれる。
デメリット
インデックスが追加されたカラムを更新する際に、インデックスそのものの更新も行うため処理速度が低下してしまう。
