[DB設計]インデックス設計の基本と注意点

はじめに

初期開発プロジェクトでDB設計を担当することになった際、今まで深く考えず使っていたインデックスの設計で思考の沼にハマりました。
ではどのような基準でインデックス設計を行うべきなのか？
基本的な考え方と、設計初心者が迷ってしまいがちなポイントについてまとめてみました。

本記事の対象者
・今までなんとなくDB操作を行なっていたがあらためてインデックスについて知りたい人
・初めてデータベース設計に挑戦する人

１．そもそもインデックスって？

データベース内のデータを高速に検索するための仕組みです。データベースに大量のデータが格納されている場合、特定のデータを見つけるためには全体を順番に探索するのではなく、効率的な方法で目的のデータを見つける必要があります。そのために、インデックスが利用されます。
インデックスは、特定のカラム（列）やフィールドに対して作成されます。インデックスは、そのカラムの値と、それが存在するデータの位置情報（物理的なディスク上の場所など）を関連付けるデータ構造です。
インデックスが作成されると、データベースはそのインデックスを使用してデータを効率的に検索します。インデックスは通常、二分木やBツリーなどのデータ構造を使用して実装されます。これにより、データベースは迅速かつ効率的に目的のデータを見つけることができます。

・・・と長々と書きましたが、よく例えられるのは本の末尾にある索引です。
単語があいうえお順、アルファベット順など特定のルールに従って並べられているので目的の単語を探しやすいですよね。
初めのページから地道に探していくと途方もない時間がかかるので、できることなら索引を用意しておきたいわけです。

２．インデックスのメリットとデメリット

インデックスは検索処理の高速化ができるためとても便利なように思えますが、以下のようなデメリットも存在します。

・インデックスのためのディスク領域が必要になる
・テーブルに変更（追加・更新・削除）を行う際の処理速度が遅くなる
・運用に伴いメンテナンスが必要となる

上記のように、闇雲に作成すると容量が増えるだけではなくかえってパフォーマンスが下がる場合があります。
よく検索される項目だからと安易に判断せず、メリット・デメリットを比較した上でインデックスを作成すべきか検討が必要です。

３．インデックスを作成すべきパターン

その１：レコード数の多いテーブル

レコード数の少ないテーブルではそもそもフルスキャンした方が高速な場合があります。
ひとつの目安として1万レコード以下であればまずインデックスの効果は発揮できないでしょう。
この閾値について明確な基準はなく、サーバーのスペックなどにもよるため理想としては実測しながら決定することが望ましいです。

その２：カーディナリティの高いカラム

カーディナリティとは「値の分散度」を示す言葉であり、これがインデックスを作る際に最も重要な指針となります。
特定の列について多くの種類の値を持っていればカーディナリティが高く、少なければカーディナリティが低いということを指します。
例えば、フラグやステータスのようにパターン数が決まっている項目はインデックスの効果が望みにくいですが、何百、何千パターンも入るような項目はインデックスの効果が高くなります。

その３：SQLの抽出条件や結合条件に使用されるカラム

SQLを発行する際、以下のように使われるカラムにインデックスを作成することで効果を発揮します。
・WHERE句に使用されるカラム
・テーブル結合でON句に使用されるカラム
・ORDER BY句に使用されるカラム
逆に言えば検索条件や結合条件に利用されないカラムにインデックスを張るのは無意味なので気をつけましょう。

４．インデックス作成で注意したいこと

その１：主キー制約や一意制約が付与されているカラム

制約としてプライマリキー、ユニークキーを付けている場合、そのカラムには自動的にインデックスが付与されています。そのため明示的にインデックスを作成する必要はありません。
※一部DBでは対象外の場合もあるため、使用するDBの仕様を確認してください。

その２：データに極端な偏りがある

カラムに入ることが想定されるデータに偏りが多いとインデックスの効果が薄くなります。
例えば100パターンのデータが入りうるが、ほとんどはデータAが入っているというようにデータに偏りのある場合、結局はフルスキャンしているのと同じ状態となってしまうためです。
実際にバラバラのデータが入ることが予想されるカラムにインデックスを作成しましょう。

その３：テーブルのデータ量に対して抽出対象のデータが少ない

抽出対象となるデータが全データに対して多い場合も同様に、インデックスの効果を生かしきれません。

その４：データ更新の処理速度を遅くする

インデックスを貼っているテーブルのデータの変更が行われる際、INSERT、UPDATE、DELETEいずれにおいてもインデックスの変更も必要になるため、インデックスを作成していないテーブルよりも処理速度が遅くなります。
テーブルの使用目的を十分に理解して、インデックスを作成するメリットが上回るのか検討が必要です。

５．インデックスを活用できないSQL

その１：NULL検索

SELECT * FROM users WHERE age IS NULL;

その２：OR検索

SELECT * FROM users WHERE age = 20 OR age = 30;

その３：否定系

SELECT * FROM users WHERE age <> 20;

その４：演算の使用

/* NG */
SELECT * FROM users WHERE age + 1 = 20;

/* OK */
SELECT * FROM users WHERE age = 20 - 1;

その５：中間一致、後方一致

/* NG */ 
SELECT * FROM users WHERE name LIKE '%田中%';
SELECT * FROM users WHERE name LIKE '%田中';

/* OK */
SELECT * FROM users WHERE name LIKE '田中%';

その６：暗黙の型変換

/* NG */ 
SELECT * FROM users WHERE age = '20'; -- NUMBER型に文字列を指定

/* OK */
SELECT * FROM users WHERE age = 20;

おわりに

インフラ担当や初期開発プロジェクトでない限り、DB設計について深く考える機会ってありませんよね。
エンジニア歴の長い方でも意外とインデックスについての知識が曖昧なままの方も少なくないと思います。
これからDB設計に挑戦したいと考えている方や初学者の最初の取っ掛かりとして、参考にしてもらえると幸いです。

参考文献

・ミック、木村明治.「おうちで学べるデータベースのきほん」.翔泳社
・ミック.「達人に学ぶDB設計徹底指南書: 初級者で終わりたくないあなたへ」 .翔泳社