データベースでのインデックスの使い方について解説します。
テーブルに大量のデータがある場合、特定のデータを検索するのに時間がかかります。
そういった時の検索を高速化できるのがインデックスです。
この記事の内容は以下の通りです。
- インデックスとは何か
- インデックスの使い方
- インデックスを貼る効果
- インデックスを貼るメリット・デメリット
簡潔にまとめたので、より詳細を知る場合は参考リンクなどを辿るのがおすすめです。
目次
インデックスとは何か
インデックスとは「索引」という意味の単語です。
ざっくり言うと、「インデックスは作ったらデータの参照が高速になるもの」です。
例えば本での索引は、特定のキーワードを探す際に役立ちますよね。１つ１つのページで探していたら日が暮れてしまいます。
この便利な索引をデータベースで再現するのがインデックスです。
インデックスの特徴は以下の通りです。
- インデックスは、指定した列に作られる。
- インデックスが存在する列に対して検索が行われた場合、DBMSは自動的にインデックスの使用を試みるため、高速になることが多い
- インデックスには名前をつけなければならない。
ただし、検索の内容によってはインデックスの利用はできず性能も向上しない場合があります。
参考: インデックスが使えない検索条件
インデックスの使い方
インデックスは列ごとに作るものです。
例えば以下のテーブルがあったとします。
| 商品ID CHAR(5) |
商品名 VARCHAR(100) |
単価 INTEGER(10) |
商品区分 INTEGER(10) |
関連商品コード CHAR(5) |
| A0001 | 秋のニットスカート | 3800 | 1 | (NULL) |
| A0052 | 裏起毛パーカー | 2900 | 1 | (NULL) |
| A0100 | 手編みのてぶくろ | 2500 | 3 | A0101 |
| A0101 | 手編みのマフラー | 3900 | 3 | A0100 |
参考: dokoQL
これに対してインデックスを作成・削除してみましょう。
インデックスの作成
(商品IDに対して、インデックスを貼った場合)
CREATE INDEX 商品IDインデックス ON 商品(商品ID);
CREATE INDEX index_name ON table(index_name);
※インデックスには、重複しない自由な名前を付けられます。
index_nameは好きに変更できます。
インデックスの削除
DROP INDEX index_name;
複数の列を一列として扱う複合インデックスも作成することが可能です。
インデックスを使うべきタイミングについて
インデックスを作成した方がいいケース
- データ数が多く、少量のレコードを検索する場合
- WHERE・結合・ORDER BYの条件が頻繁に使用されている列である場合
- 列の値が比較的一意である場合
- 参照生合成制約で使用されている外部キーになっている列である場合
インデックスを作成しない方がいいケース
- データ登録件数が少ないテーブルである場合
- WHEREの条件があまり使用されない場合
- 列の値が頻繁にINSERT/UPDATE/DELETEされる場合
- 列内の値がほぼユニークである場合
- NULL値が多く、NULL以外の値検索をしない場合
参考
インデックスの効果が得られる例
インデックスを貼って得られる効果の例を３つみていきます。
- WHEREによる絞り込み
- ORDER BY による絞り込み
- JOINによる結合条件
WHEREによる絞り込み
SELECT * FROM 商品
WHERE 商品ID = 2001;完全一致の検索では、インデックスによる効果が得やすいです。
またLIKEによる前方一致の場合でも同様の効果を得られます。
ORDER BY による絞り込み
SELECT * FROM 商品
ORDER BY 商品ID;JOINによる結合条件
SELECT * FROM 明細表
JOIN 商品
ON 明細表.商品ID = 商品.ID
効果について参考になる記事一覧を載せておきます。
インデックスを貼るメリット・デメリット
インデックスを貼るメリットとデメリットは以下の通りです。
インデックスを貼るメリット
- データ参照の高速化
- ソート作業の省略化
インデックスを貼るデメリット
- インデックスの情報を保存するため、ディスク容量を消費する
- データが書き換えられるとインデックスも書き換えるので、INSERT/UPDATE/DELETE文のオーバーヘッドが増える
参考文献
MySQLの練習は、スッキリわかるSQL入門で使える dokoQLが楽です。
本も簡潔にまとめられているので、初心者の方には非常におすすめです。
