SQLでのインデックス作成方法｜SQLServerなどのDBでのやり方を解説

システム
エンジニア

インデックスって聞いたことがあるのですが、具体的にはどのような役割を担っているのでしょうか？

プロジェクト
リーダー

一言でいうと「目次」の働きをしてくれます。テーブルに目次を付けるというイメージでインデックスについて確認していきましょう。

インデックスとは

システム開発やプログラミングに携わったことがないという人でも、「インデックス」という言葉を使ったり聞いたりしたことがあるでしょう。目次に対して、「あいうえお」や「ABC」などの言葉順にページを探せるものを「索引」「インデックス」と呼んでいます。

データベースで使われる「インデックス」も、書物の「インデックス」と同じように、情報を引き出すための「索引」として使われる仕組みです。

インデックスを作成するメリット・デメリット

データベースのインデックスは、必ず作成されている機能ではありません。システムの設計段階で、データベース項目ごとにインデックスを作成するか検討します。

インデックスを作成すると、検索速度があがります。データ量が多いほど、インデックスの有無で結果を得られるスピードが顕著になります。逆に、データ登録では、インデックスを作成する処理が必要になるので、その分、パフォーマンスが落ちてしまいます。

SQL Serverのインデックス作成方法

SQL Severのインデックスは大きく分けて2種類あります。それは「クラスター化インデックス」と「非クラスター化インデックス」です。

クラスター化インデックス

テーブルに格納されているレコードをインデックスで指定した列の値で並び替えて格納します。値が同じ場合は、2番目に指定した列の値で並び替えます。このクラスター化インデックスは、テーブルに対して1つしか作成できません。

非クラスター化インデックス

レコードの並び替えは行われずにテーブルに格納されています。インデックスで指定した列の値に対応するレコードへのポインタがインデックスファイルに格納されます。テーブルに対して複数のインデックスを付与することができるのも特徴のひとつです。

PostgreSQLでのインデックスの使い方4つ

ここでは、個人の学習用としても、企業の業務システムとしても、使われることの多い「PostgreSQL」で、インデックスを利用する方法を紹介します。

以下のコードを実行して作成されるテーブルを想定して、PostgreSQLのインデックス作成手順を解説します。

例）smplスキーマにjushoテーブルを作成

インデックスの作成方法

PostgreSQLのテーブルにインデックスを作成する基本構文は以下のとおりです。

例）jushoテーブルのaddress にインデックスを作成する

カンマ区切りで複数指定したい場合の作成方法

PostgreSQLで作成できるインデックスは項目単位だけでなく、複数の項目を組み合わせることも可能です。基本構文の「カラム名」を指定している箇所で、複数のカラム名を指定すると、複合キーとして利用できます。カラム名は「，（カンマ）」で区切って複数記述します。

例）nameとaddressで1つのインデックスを作成

¥diコマンドを使ってインデックスを確認する方法

PostgreSQLでは、¥diコマンドを使って、作成したインデックスの一覧を取得できます。¥diコマンドを使う前に、インデックス一覧を取得したいデータベースに接続しておきましょう。¥diコマンドを実行すると、インデックス名と所有者、テーブル名などが一覧表示されます。

作成したスキーマのインデックスリストを確認したい場合

接続したデータベースで¥diコマンドを実行すると、対象データベースに作成されているすべてのインデックスがスキーマ、テーブル、所有者ごとに表示されます。

自分が利用しているスキーマに作成されているインデックス一覧だけが必要な場合は、以下のようにコマンドを発行します。

例）smplスキーマのインデックスのみを表示させる

更に詳細にインデックスの確認をしたい場合

PostgreSQLで作成したインデックスの一覧だけでなく、各インデックスの詳細な情報を参照したいときは、¥dコマンドを使用します。¥dコマンドを使うと、インデックスを構成するカラム名やデータ型など、テーブル項目の詳細情報が表示されます。

¥dの後ろに参照したいインデックス名かテーブル名を指定します。

例）インデックス名を指定

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

作成したインデックスに誤りがあったり不要になったりしたときは、「DROP INDEX インデックス名」構文でインデックスを削除できます。インデックスを削除しても、テーブルそのものや、テーブルに保存されているデータには影響ありません。

例）smplスキーマのインデックスidx_nameを削除する

Oracle Databaseでのインデックスの使い方

Oracle Databaseでインデックスを作成・削除するためのSQL文は、PostgreSQLを利用するときの構文と似ています。Oracleには「表領域」という概念があるので、構文の末尾に表領域の指定を記載します。

例）インデックス用表領域idx_spにインデックスを作成する

インデックスの効果があまりないパターン6つ

インデックスを利用するとデータ検索のパフォーマンスがあがるはずですが、インデックスを実際に作成してみると、想定していたほど効果があらわれないこともあります。

ここでは、インデックスの効果が発揮されない原因や理由を紹介します。効果がみられないインデックスを見直してみましょう。

インデックスで演算を使っている場合

インデックスを作成した項目使って、SQL文の中で演算を行っていると、インデックスは無効となるため、パフォーマンスがあがりません。

5教科の平均点が50点を超える生徒のみを抽出する以下のSQL文では、総得点totalにインデックスを作成しても「/5」という計算式を使っているのでインデックスは無効になります。インデックスと関係ない式の右側に演算を用いると、インデックスが活かされます。

インデックスで否定形を使っている場合

SQL文の検索条件が否定形の場合、インデックスが活かされません。「～ではない」という条件では、条件に合ったデータを直接検索することができず、全件検索することになってしまいます。

例）総得点が0点ではない人を検索する（<>や!=など否定形は全件検索が行われる）

インデックスでIS NULLを使っている場合

指定した項目にデータが存在しないレコードのみを検索したいときに、SQL文で「IS NULL」という条件を使いますが、NULLについてもインデックスが使えません。データ不存在の条件をSQL文で利用する可能性がある場合は、ダミーデータを設定しておくことをおすすめします。

インデックスで関数処理を使っている場合

SQL文の検索条件となるWhere句の中で関数を使っている場合、インデックスを作成した項目を演算するのと同じ意味合いになるので、インデックスが無効となります。SQL文の中でインデックスを作成した項目に関数を使うのも避けましょう。

ORを使っている場合

SQL文のWhere句でORを使ってしまうとインデックスが使われません。複数の条件のいずれかに合致すれば抽出するので、全件検索になってしまいます。

インデックスを活かすSQL文として、Oracleの場合は、ORで条件をつながずに条件ごとのSQL文を書き、UNION ALLで結果を合体させられます。

LIKEの部分一致検索を使っている場合

SQL文のWhere句で、部分一致の条件として使えるLIKE文も、使い方に注意が必要です。前方一致の場合はインデックスが有効になりますが、中間一致や後方一致は全件検索になってしまいます。

例）インデックス有効：〇　無効：×

システム
エンジニア

インデックスって目次のような働きをしてくれるのですね。これからは積極的に活用していきます。

プロジェクト
リーダー

本も目次があると、読みたい内容がどこにあるかすぐに分かると思います。それと一緒でテーブルに対してインデックスを付与してあげると、検索スピードが当然上がります。検索画面で検索結果がなかなか返ってこないプログラムを作らないように、インデックスを付与することは忘れずに行いたいところですね。

SQL インデックス検索のパフォーマンスを考えたテーブル設計を行いましょう

今回はSQL Serverでのインデックスがメインでしたが、インデックス自体は他のデータベースにも存在します。インデックスの概念を理解しておけば、検索のパフォーマンスを考慮したテーブルの作成をどのデータベースに対しても行えます。

エンジニアとしてテーブル設計に携わる機会が訪れた時には、ぜひインデックスは有効活用するように心がけましょう。