SQLのインデックスとは？作成、確認、削除方法からメリットまで解説！

インデックスとは

インデックスとは、テーブルにある情報を検索する場合に「どこに何があるか」をわかりやすくする索引のことです。

データの検索は上から1つずつ探していく方法もありますが、大量にデータがある場合は効率が悪いですよね。

多くの情報を扱う場合、本の最後のページにあるように「索引」が用意されていると、目的の情報をすぐに確認できて便利です。

 

具体的なイメージを確認してみましょう。

例えば、次のようなテーブルがあるとします。

No	name	age	address	position
1	Yamamoto	32	Nagoya	employee
2	Abe	28	Tokyo	employee
3	Kimura	36	Chiba	leader
4	Hatano	41	Osaka	manager
5	Ono	25	Tokyo	employee

このテーブルからnameの値を検索する場合、データが順番に並んでいないためインデックスを作成すると効率がよくなります。

この程度のデータ数であれば問題ありませんが、データ数が膨大になる程その差は顕著です。

今回の場合であれば、インデックスを作成すると次のようになります。

No	name
2	Abe
3	Kimura
4	Hatano
5	Ono
1	Yamamoto

列数を「id」と「name」だけをだけにし、nameの値を並び替えているので、元のテーブルよりも効率的に検索可能です。

このような索引をSQLでも設定ができるので、この記事ではその方法を解説します。

インデックスの種類

インデックスには大きく分けて次の3つの方式があります。

• B木：アクセス回数が多いノードをroot近くに設定することで、速度向上が見込める
• B+木：リーフノードがポインタで接続されているので、範囲検索の向上が見込める
• ビットマップ：カーディナリティが低い場合に速度向上が見込める

その他、DBMSごとに特有の種類を扱っている場合もあります。

詳しくは、各DBMSのリファレンスなどを確認するとよいでしょう。

インデックスのメリット・デメリット

インデックスの作成はメリットがある一方でデメリットも存在します。

インデックス作成のメリット・デメリットは次の通りです。

インデックスを作成すると、元のテーブルとは別にデータを保持することになるため、データ追加を行うとインデックスにもデータを追加します。

単純に、データ追加数が2倍になるため、その分処理速度に影響を及ぼします。

インデックスの書き方・使い方

では、インデックスの書き方・使い方を確認しましょう。

ここでは次の3つを「PostgreSQL」で紹介します。

• インデックスの作成
• インデックスの確認
• インデックスの削除

ひとつずつ、サンプルを用いながら確認します。

インデックスの作成

インデックスを作成するには「CREATE INDEX」構文を使います。

基本的な記述方法は次の通りです。

CREATE INDEX インデックス名 ON [ ONLY ] テーブル名 ( カラム名... )

インデックスは、指定したテーブルのカラム名を対象に作成され、テーブルと同じスキーマ上になります。

なお、インデックス名は省略も可能です。

インデックス作成用に次のテーブルを用意します。

mydb=# CREATE TABLE myschema.user(No integer, name varchar(20), age integer, address varchar(20), position varchar(20));
CREATE TABLE

スキーマを作成していない人は、次のコマンドで作成しましょう。

CREATE SCHEMA myschema;

上記を実行後に、nameを対象としたインデックスを作成します。

mydb=# CREATE INDEX ON myschema.user(name);
CREATE INDEX

これでインデックスの作成は完了です。

テーブル情報を確認すると、次のように表示されます。

mydb=# \d myschema.user
                       Table "myschema.user"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 no       | integer               |           |          | 
 name     | character varying(20) |           |          | 
 age      | integer               |           |          | 
 address  | character varying(20) |           |          | 
 position | character varying(20) |           |          | 
Indexes:
    "user_name_idx" btree (name)

テーブル情報の下に「Indexes」とありますね。

これがインデックス情報です。

インデックス名が「user_name_idx」となり、対象がnameカラムであること、インデックスの種類が「btree」であることがわかります。

 

インデックスは、複数のカラムを指定して作成可能です。

複数指定したい場合は、カンマ区切りで記述しましょう。

mydb=# CREATE INDEX ON myschema.user(name, address);
CREATE INDEX

mydb=# \d myschema.user
                       Table "myschema.user"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 no       | integer               |           |          | 
 name     | character varying(20) |           |          | 
 age      | integer               |           |          | 
 address  | character varying(20) |           |          | 
 position | character varying(20) |           |          | 
Indexes:
    "user_name_address_idx" btree (name, address)
    "user_name_idx" btree (name)

このように複数カラムを指定したインデックスが作成できました。

インデックスの確認

インデックスを確認する方法を見ていきましょう。

インデックスを確認するには、「¥di」コマンドを入力します。

mydb=# \di
                 List of relations
 Schema |     Name     | Type  |  Owner   |  Table  
--------+--------------+-------+----------+---------
 public | user_tb_pkey | index | postgres | user_tb
(1 row)

上記のように、インデックスリストが表示されます。

作成したスキーマのインデックスリストを確認したい場合は、引数に「スキーマ名.*」を記述します。

実際に、先ほど作成したmyschemaスキーマのインデックスリストを確認するには、次のように入力しましょう。

mydb=# \di myschema.*
                      List of relations
  Schema  |         Name          | Type  |  Owner   | Table 
----------+-----------------------+-------+----------+-------
 myschema | user_name_address_idx | index | postgres | user
 myschema | user_name_idx         | index | postgres | user
(2 rows)

上記のように先ほど作成したインデックスが表示されるはずです。

また、インデックスの詳細を確認したい場合は、「¥d」コマンドを記述します。

mydb=# \d myschema.user_name_idx
           Index "myschema.user_name_idx"
 Column |         Type          | Key? | Definition 
--------+-----------------------+------+------------
 name   | character varying(20) | yes  | name
btree, for table "myschema.user"

インデックスの削除

インデックスを削除したい場合は、「DROP INDEX」構文を使います。

DROP INDEX インデックス名 

では、先ほど作成した「user_name_address_idx」を削除してみましょう。

mydb=# DROP INDEX myschema.user_name_address_idx;
DROP INDEX

実行後に「DROP INDEX」と表示されていることから削除できているのがわかります。

インデックスリストを確認してみると、ちゃんと削除されているはずです。

mydb=# \di myschema.*
                  List of relations
  Schema  |     Name      | Type  |  Owner   | Table 
----------+---------------+-------+----------+-------
 myschema | user_name_idx | index | postgres | user
(1 row)

インデックスで高速化するパターン

インデックスを作成したものの、どんなSQLクエリを書いた場合に高速化できるのでしょうか？

インデックスの効果を得やすい記述パターンには、大きく分けて次の3つあります。

1. WHERE句での絞り込み
2. ORDER BY句での並び替え
3. JOINでの結合の条件

ひとつずつ説明します。

1.WHERE句での絞り込み

WHERE句の絞り込み検索で「完全一致検索」であれば、インデックスが使われ、高速に検索結果を表示可能です。

SELECT * FROM myschema.user WHERE no = 3;

ただし、「部分一致検索」や「後方一致検索」になるとインデックスを利用できないため注意してください。

SELECT * FROM myschema.user WHERE name LIKE 'A%';

2.ORDER BY句での並び替え

インデックスは並び替えも高速に行えるので、ORDER BY句での処理も高速化が期待できます。

SELECT * FROM myschema.user ORDER BY age;

3.JOINでの結合の条件

JOINでの結合処理は内部で並び替え処理を行っているので、インデックスのある列を用いると高速で行えます。

インデックスが効かない原因は？

インデックスを作成したのに効いていないと感じる場合があるかもしれません。

その原因にはさまざまなものが考えられますが、よくある例として次の6つが考えられます。

多くは、インデックスを作成しているが使用できないパターンです。

前述した中間一致、後方一致といった「部分一致検索」では、インデックスを使えません。

その他、演算や関数処理を行っている場合もインデックスは使用不可です。

上記に当てはまるような検索を行っている場合は、インデックスが効かないので覚えておきましょう。

まとめ

SQLのインデックスの概要や作成方法を解説しました。

インデックスは、テーブル内のデータが大量にある場合にパフォーマンスを発揮します。

うまく活用できるととても効率的にデータを取得できるので、ぜひこの記事を参考にインデックスの使い方を覚えてください！