本記事の目的

本記事では、データベースにおけるインデックスがパフォーマンスにどのような影響を与えるのか また、インデックスを付与すべきカラムの選定基準について学びます。

インデックスとは

インデックスとは、データベースにおける「検索の目次」のようなものです。書籍で特定のキーワードやページを素早く見つけられる索引と同じ役割を果たします。 データベースでは、テーブル内のデータを効率的に検索するためにインデックスを使用します。インデックスを付与すると、データベースは対象のカラムに基づいた「効率的なデータ構造」を作成し、データの検索や並び替えを高速化します。 ※ただし、インデックスにはストレージ使用量が増える、データの更新が遅くなるといったデメリットもあるため、適切なカラムに付与することが重要です。

インデックスは、アプリやデータに影響を与えない

インデックスは、アプリケーション開発者がデータベースのパフォーマンスを向上させるための強力な手段ですが、ユーザーやアプリケーションの動作に影響を与えない点で「透過的（トランスペアレント）」であるという特徴があります。これにより、適切に設計されたインデックスは、アプリケーションのロジックや機能を変更することなく、性能向上を実現できます。

1. クエリの変更が不要

インデックスはデータベースエンジン内で動作する仕組みのため、開発者がSQLクエリを変更する必要はありません。たとえば、SELECT文で検索条件を指定する場合、インデックスが存在すればデータベースエンジンが自動的に最適な検索方法を選択します。これにより、アプリケーションコードに依存せずにパフォーマンスを改善できます。

2. ユーザーの体験を損なわない

インデックスはデータの内部構造に影響を与えますが、クライアント側やエンドユーザーにはその存在が全く見えません。結果として、ユーザーエクスペリエンス（UX）を向上させつつ、動作やデータ取得の速さを改善します。

3. アプリケーションの複雑性を増やさない

パフォーマンス向上のためにアプリケーションコードを最適化する場合、ロジックが複雑化することがあります。一方、インデックスの利用はアプリケーションの外部（データベース側）で完結するため、開発者はビジネスロジックの実装に集中できます。

どのカラムにインデックスを付与すればよいのか

①大規模なテーブル

小規模なテーブルの場合、インデックスを付与せずに全件を順番に検索する「フルテーブルスキャン」であっても、検索速度に大きな差が出ないことがあります。 これは、テーブルのデータ量が少ないため、インデックスを使用して検索するよりも直接データをスキャンした方が効率的な場合があるためです。

大規模かどうかの基準については、システムの設計やハードウェア、データベースエンジンによって異なりますが、 大体数万件を超える程度でインデックスの検討を行った方がよいのではないでしょうか。

②カーディナリティ

カーディナリティとは、特定のカラムに含まれる「ユニークな値の数」のことです。 簡単に言うと、そのカラムのデータがどれだけバラバラかを示します。

「高いカーディナリティ」: ユニークな値が多い場合。ユーザーIDやメールアドレスなど。 「低いカーディナリティ」：性別（男・女）やステータス（有効・無効）など。

高いカーディナリティには、絞り込むデータが少なくなるので、インデックスが効果的です。 一方、低いカーディナリティは多くの行をスキャンする必要があるので、インデックスがあまり役立ちません。

※ただ、いくらカーディナリティが高いからといって、1~100までの値が入るカラムのうち、「99%が100」などといった時は性能を発揮できません。

③SQLのWHEREやJOINで使われているカラム

SQLで検索条件や結合条件として使用されていないカラムにインデックスを付与しても無意味です。 ただ、その中でもいくつか注意点があります。 よくやりそうなものをいくつかピックアップします。

NGパターン①

SELECT * FROM users 
    WHERE col IS NULL;

NULLについてはデータの値とはみなされないので、インデックスで保持されません。

NGパターン②

SELECT * FROM users WHERE col <> 3000;

SQLにおける否定系の条件（例えば、NOTや<>など）は、インデックスの効力を制限することがあります。 インデックスは、特定の値や範囲を効率的に検索するために設計されていますが、否定系の条件を使うと、検索対象が広がりすぎるため、インデックスのメリットが失われることがあります。

NGパターン③

SELECT * FROM users WHERE DATE(col) = '2024-12-15';

インデックスが設定されているカラムに対して、関数や演算を使用するとインデックスが無効になります。

NGパターン④

SELECT * FROM users WHERE col LIKE '%a';
SELECT * FROM users WHERE col LIKE '%a%';

インデックスは通常、先頭からの一致を利用してデータを絞り込む仕組みです。 しかし、検索条件がワイルドカードで始まる場合、インデックスが使われません。 なので、部分一致検索では前方一致のみインデックスが利用されます。

SELECT * FROM users WHERE col LIKE 'a%';

終わり

インデックスは業務で頻繁に扱っており、パフォーマンス向上が求められるタスクでよく利用してきました。今回改めて学習してみて、「カーディナリティが低いカラムにはあまりインデックスをつけない」という点が新たな発見でした。今までは検索で頻繁に使用するカラムには必ずインデックスを付けていたため、よい振り返りとなりました。

また、インデックスに関しては改修コストも大きいわけではないので、アプリケーションのメンテナンスの際はインデックスも見直すことも 今後はもっと意識しようと思います。

それでは、ここまでお付き合いいただきありがとうございました:)