ブログ

こんにちは！Ry.Kです。
Javaでプログラムを書いていると、データベースを参照する機会が多く、「このテーブルにはインデックスを作成したほうがいいのか？」と迷うことがあります。特にバッチ処理などで大量のデータを検索する場合、インデックスの有無によってSQLの実行速度が大きく変わることがある一方で、必要以上に作成するとデータの登録や更新時に負荷が増えるなど、別の問題につながることもあります。
そこで今回は、インデックスの基本的な考え方や、どのような場合にインデックスを作成すると効果的なのかを改めて整理してみたいと思います。

インデックスとは？

インデックスとは、データベースの検索処理を高速化するための仕組みです。
例えば、数十万件のデータが入っているテーブルから特定の会員番号を条件にデータを検索する際、インデックスが作成されていない場合は、テーブル全体を読み取って条件に一致するレコードを探すため、SQLの実行時間が長くなることがあります。
一方、検索条件として使用するカラムにインデックスが作成されていれば、データベースがインデックスを利用することで対象のデータを効率よく検索できるため、検索処理の高速化が期待できます。
イメージとしては本にある「索引」のようなものです。数百ページある本から特定の用語について書かれているページを探す場合、最初のページから1ページずつ確認するよりも、巻末の索引から目的のページを探したほうが効率的です。
データベースのインデックスもこれと似たような仕組みで、検索対象となるデータを効率よく見つけることで、検索処理の高速化につながります。
ですが、インデックスは検索処理だけに影響するものではありません。インデックスを作成すると、データの追加や更新、削除が発生した際に、インデックスの情報も更新する必要があります。
そのため、必要以上にインデックスを作成すると、SELECTの検索処理は高速化できても、INSERTやUPDATE、DELETEなどの処理に負荷がかかります。
検索処理の高速化だけを見るのではなく、データの登録や更新、削除がどの程度行われるのかも考慮して、インデックスを作成する必要があります。

どのカラムにインデックスを作成すべきか

SQLでは、WHERE句を使って検索するデータを絞り込みますが、その際に検索条件として頻繁に使用するカラムは、インデックスを作成する候補になります。特にデータ件数が多いテーブルでは、インデックスによる検索処理の高速化が期待できます。

まずは、単一のカラムを検索条件に使用するSQLの例を見てみます。

 SELECT * FROM 会員情報 WHERE 会員番号 = '000001'; 

このSQLでは会員番号を条件にデータを検索しているため、データ件数が多い場合は会員番号にインデックスを作成することで、検索処理の高速化が期待できます。
今回のように、1つのカラムを対象として作成するインデックスは「単一インデックス」と呼ばれます。単一インデックスは、特定のカラムを条件にした検索を効率化したい場合に利用されます。
今回の例では、会員番号に以下のような単一インデックスを作成できます。

CREATE INDEX IDX_会員番号 ON 会員情報 (会員番号);

このように、特定のカラムを検索条件として使用するSQLでは、そのカラムに単一インデックスを作成することで、検索処理の効率化が期待できます。
一方で、実際のSQLでは1つのカラムだけではなく、複数のカラムを組み合わせて検索するケースもあります。

複数のカラムを条件に検索する場合

では、検索条件に複数のカラムを使用する場合について見てみます。

 SELECT * FROM 会員情報 WHERE 会員番号 = '000001' AND 会員区分 = '1'; 

このSQLでは、会員番号と会員区分の2つのカラムを検索条件として使用しています。︎
このように、複数のカラムを検索条件として使用するSQLでは、インデックスの作り方にもいくつかの方法があります。

単一インデックス

まずは、会員番号と会員区分それぞれに単一インデックスを作成する方法です。

CREATE INDEX IDX_会員番号 ON 会員情報 (会員番号);
CREATE INDEX IDX_会員区分 ON 会員情報 (会員区分);

この場合、会員番号と会員区分それぞれに対して、個別のインデックスが作成されます。

複合インデックス

次に、複数のカラムを1つのインデックスにまとめる方法を見てみます。複数のカラムを組み合わせて作成するインデックスを「複合インデックス」と呼びます。
今回の例では、会員番号と会員区分を組み合わせて、以下のような複合インデックスを作成できます。

CREATE INDEX IDX_会員番号_会員区分 ON 会員情報 (会員番号, 会員区分);

「それぞれに単一インデックスを作成する方法」と「複合インデックスを作成する方法」では、どちらも会員番号と会員区分を対象としていますが、インデックスの構成が異なります。そのため、実際にSQLを実行した際に、データベースがインデックスをどのように利用できるかにも違いがあります。
また、複合インデックスでは、カラムを組み合わせる順番によって、利用できるSQLの条件が変わってきます。
例えば、会員番号 → 会員区分の順番で複合インデックスを作成した場合、会員番号を検索条件として使用するSQLや、会員番号と会員区分の両方を検索条件として使用するSQLでは、この複合インデックスを利用できる可能性があります。一方で、会員区分だけを検索条件として使用するSQLでは、複合インデックスの先頭に会員番号が設定されているため、複合インデックスを効率的に利用できない場合があります。
逆に、会員区分 → 会員番号の順番で作成した場合は、会員区分を検索条件とするSQLで利用しやすくなる一方、会員番号だけを検索条件とするSQLでは複合インデックスを効率的に利用できない場合があります。

単一インデックスと複合インデックスの使い分け

では、実際にどのような場合に単一インデックスや複合インデックスを選べばよいのでしょうか？
例えば、上で挙げたSQL以外でも、プログラムの中で会員番号と会員区分をそれぞれ単独で検索することが多い場合は、それぞれに単一インデックスを作成する方法が考えられます。一方で、会員番号と会員区分をセットで検索することが多い場合は、複合インデックスを検討するとよいでしょう。

このように、インデックスを作成する際は、単純にWHERE句で使用されているカラムだけを見るのではなく、実際にどのような検索が多いのかを確認したうえで、適したインデックスを選択することが重要です。

インデックスがあれば必ず速くなるのか？

ここまでインデックスを作成することで検索処理を高速化できることを説明してきましたが、インデックスを作成すれば、必ずSQLの実行が高速になるというわけではありません。
例えば、検索条件に一致するデータが非常に多い場合、インデックスを利用して検索するよりも、テーブル全体を読み取ったほうが効率的だとデータベースが判断する場合があります。
また、SQLの書き方や検索条件によっては、作成したインデックスがうまく利用されない場合もあります。
そのため、インデックスを作成した場合は、実際にSQLを実行した際の実行計画を確認し、想定したインデックスが利用されているか、SQLが効率的に実行されているかを確認することが重要です。

実行計画とは？

実行計画とは、データベースがSQLを実行する際に、どのような方法でデータを取得するのかを確認するための情報です。
EXPLAIN PLANを使用することで実行計画を確認できます。

EXPLAIN PLAN FOR SELECT * FROM 会員情報 WHERE 会員番号 = '000001';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

実行すると、以下のような実行計画が表示されます。

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID | 会員情報      |     1 |     2   (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN            | IDX_会員番号  |     1 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

実行計画にはさまざまな情報が表示されますが、まずは作成したインデックスが実際に利用されているかを確認しましょう。
今回の例では、INDEX RANGE SCANが表示されており、会員番号に作成したインデックスが検索に利用されていることが確認できます。

このように、実行計画を見ることで、作成したインデックスが実際に利用されているか、SQLがどのような方法でデータを取得しているかが分かります。
インデックスを作成した際は、実行計画を確認し、想定したSQLの実行方法になっているかを確認することが重要です。

インデックスを作成するときに意識したいこと

次にインデックスを作成する際に、意識したい点をまとめてみます。

• WHERE句などで頻繁に検索条件として使用されるカラムか
• データ件数が増えた場合に検索処理が問題になりそうか
• 単一インデックスが適切か、複合インデックスが適切か
• INSERTやUPDATE、DELETEへの影響はないか
• 実際のSQLや実行計画を確認したうえで、インデックスの必要性を判断しているか

まとめ

今回は、データベースのインデックスについて簡単に整理してみました。
インデックスは検索処理を高速化するために非常に便利な仕組みですが、検索に利用されるカラムやデータ量、更新処理への影響などを考慮しながら、適切に作成することが重要だと改めて感じました。
また、今回の記事を書くことで、SQLの書き方だけでなく、そのSQLがデータベース上でどのように実行されるのかを考えることの重要性についても整理することができました。
今後もデータの扱い方や適切なインデックスの作成方法などを考えながら、SQLやデータベースの設計について理解を深めていきたいと思います。

株式会社ウイングドアは福岡のシステム開発会社です。
現在、私達と一緒に"楽しく仕事が出来る仲間"として、新卒・中途採用を絶賛募集しています！
ウイングドアの仲間達となら楽しく仕事できるかも？と興味をもった方、
お気軽にお問い合わせ下さい！