【データベース】基礎から学ぶインデックス作成時のチェックポイント

新規テーブルの作成やクエリチューニング時にインデックスを作成する機会があります。

インデックスを作成することでSQLの実行時間を短くできます。

そこで今回はインデックスを作成する際のチェックポイントについて紹介します。

なお、本記事ではB-treeインデックスの前提で話を進めます。

また、データベースの種類やデータベースのバージョン、実データの構造によっては今回紹介する内容が当てはまらない可能性があるのでご注意ください。

インデックスに関する基礎知識

インデックスを作成する上で理解しておきたい基礎知識について紹介します。

インデックスは必要なところにだけ作成する

インデックスはテーブルとは別のオブジェクトとして保存されるためデータベースのディスク容量を使用します。

不要なインデックスの作成はディスク容量の浪費につながるので控えましょう。

主キーとユニークキーはインデックス作成の考慮不要

主キー（primary key）およびユニークキー（UNIQUE制約があるキー）には値の重複を許さないユニークインデックスが自動で作成されます。

ですので、主キーおよびユニークキーに対しては手動でインデックスを作成する必要はありません。

外部キーに対するインデックス作成の考慮はRDBMSに依存する

外部キー（FOREIGN KEY制約があるキー）に自動でインデックスが作成されるかはデータベースの種類に依存します。

MySQLの場合は外部キーに自動でインデックスが作成されるため、インデックス作成の考慮が不要です。1 PostgreSQLの場合はインデックス作成の考慮が必要です。2

インデックスの見直しが必要になる時もある

レコードの増加に伴いテーブルのデータ構造が変わると、インデックスがうまく機能しなくなる場合があります。

インデックスは一度作成したら終わりではありません。SQLのパフォーマンスが低下した場合はインデックスを見直す必要があります。

インデックスが作成されたカラムはソート状態で管理される

インデックスが作成されたカラムはソートされた状態で管理されます。そのためソートを実行する際にインデックスは有効に働きます。

オプティマイザは統計情報をもとにインデックス利用を判断する

インデックスを作成しても、オプティマイザがそれを使うとは限りません。

オプティマイザは統計情報（行数、カラムの値の分布、NULL率、カーディナリティなど）をもとに「インデックスを使ったほうが速いか／フルスキャンしたほうが速いか」を判断します。

統計情報は通常、ANALYZE TABLE（MySQL）やANALYZE（PostgreSQL）で更新されます。大量にデータを投入した直後やデータ分布が大きく変わった後は、統計情報の更新を意識する必要があります。

-- MySQL
ANALYZE TABLE users;

-- PostgreSQL
ANALYZE users;

「インデックスがあるはずなのに使われない」というケースの多くは、統計情報が古い・列の分布に偏りがある・コスト見積もりがフルスキャン優位と判断された、などが原因です。

複合インデックスのカラム順には意味がある

複合インデックス（結合インデックス）とは複数のカラムを組み合わせたインデックスのことです。

検索条件で一緒に使われるカラムに対して複合インデックスを作成することでSQLの高速化が期待できます。

複合インデックスのカラム順には意味があるため、検索条件によっては複合インデックスが利用されない可能性があります。

たとえば(A, B, C)というカラム順の複合インデックスの場合、検索条件とインデックス利用可否の対応は以下の通りです。

検索条件	インデックスの利用可否
A	可
B	否
C	否
AB	可
AC	否
BC	否

複合インデックスの詳細解説は【図解】B-treeを理解し、複合インデックスの順番を正しく作るをご覧になってください。

参考: NULLはインデックスに含まれない？

NULLはインデックスに含まれないのでIS NULL検索ではインデックスが利用されないという記述をよく目にします。3 4 しかし、MySQLのドキュメントや、IS NULLはインデックスを使うのかの記事にもあるようにIS NULLでもインデックスが利用されるケースは存在します。

-- MySQL 5.7.34を利用
-- keyにインデックスが記載されているため、IS NULLのSQLでもインデックスが使われていることがわかる

EXPLAIN SELECT * FROM users WHERE age IS NULL\G;

*************************<strong> 1. row </strong>*************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

インデックスを作成する前にチェックすること

テーブルやカラムの特徴によってはインデックスを作成しても意味のないケースがあります。

むしろ、場合によってはインデックスを作成したことによってパフォーマンスが低下する可能性もあります。

インデックスを作成するか検討する際のチェックポイントは以下の通りです。

インデックスを作成時のチェックポイント

テーブルの規模は十分大きいか
絞り込みが十分にできるカラムか
カラムの更新（削除・更新・追加）頻度は低いか

テーブルの規模は十分大きいか

小規模なテーブルの場合、インデックスよりもフルスキャンのほうが高速です。目安となるレコード数は1万件です。5 レコード数が1万件未満の小規模なテーブルの場合、インデックスは不要な可能性が高いです。

絞り込みが十分にできるカラムか

目安として、検索条件によって全体の5~15%程度までレコード数が絞り込めるカラムであればインデックスによる高速化が期待できます。

5 4 絞り込みが十分にできるカラムか検証する際に利用される指標として『カーディナリティ』というものがあります。カーディナリティとはカラムがとりうる値の種類です。

たとえば『男』『女』『そのほか』『無回答』という値のみをもつ性別に関するカラムであればカーディナリティは4となります。

均等に種類が存在していると仮定した場合、カーディナリティ20のカラムを利用した検索だと全体の5%までレコードを絞り込めます。

ですので、10~20程度のカーディナリティがインデックスを作成する際の指標となります。

ただし、カーディナリティはあくまでカラムに存在する値の種類を表現した指標です。

繰り返しになりますが、重要なのは検索によって全体の5~15%程度までレコード数が絞り込めるということです。

カーディナリティが低くくても、絞り込み条件によってはインデックスが利用されるケースもあります。6 逆にカーディナリティが高くても、データに偏りがあって絞り込みが十分にできない場合はインデックスの効果が期待できないケースもあります。

カラムの更新（削除・更新・追加）頻度は低いか

インデックスが作成されたカラムを更新する場合、カラムだけでなくインデックスのオブジェクトも更新する必要があります。

そのため、インデックスは更新の性能を劣化させる原因となります。

更新頻度が高いカラムにインデックスを作成する場合は、検索性能と更新性能のトレードオフを検討する必要があります。

インデックスを作成すべきカラム

インデックスを作成することでSQLの高速化が期待できるカラムは以下の通りです。

インデックスを作成すべきカラム

条件文（WHERE）で利用される
ソート(ORDER BY）で利用される
グルーピング（GROUP BY）で利用される
集計（COUNT）で利用される
結合条件（JOIN）で利用される
最大値・最小値(MAX・MIN)の算出で利用される

条件文（WHERE）で利用される

インデックスにより全表走査が不要になるため検索が高速になります。

複数のカラムを組み合わせた条件文を利用する場合は、複合インデックスの作成も検討するとよいです。

ソート(ORDER BY）で利用される

インデックスによりカラムがソート済みの状態であるため、ソートが高速になります。

複数のカラムを組み合わせたソートを利用する場合は、複合インデックスの作成も検討するとよいです。

グルーピング（GROUP BY）で利用される

インデックスによりカラムがソート済みの状態であるため、グルーピングが高速になります。

集計（COUNT）で利用される

インデックスが作成されていないカラムに対してCOUNTを実行すると時間がかかります。

インデックスが作成されたカラムをA、作成されていないカラムをBとした場合、SQLとインデックスによる集計可否の対応は以下の通りです。

SQL	インデックスによる集計可否
SELECT COUNT(*) FROM “T1”	可
SELECT COUNT(“A”) FROM “T1”	可
SELECT COUNT(“B”) FROM “T1”	否

結合条件（JOIN）で利用される

JOINの方法には主にNested loop join（NLJ、ネステッドループ結合）、Merge join（マージ結合、ソートマージ）、Hash join（ハッシュ結合、ハッシュ値マッチング）の3種類があります。7 Nested loop joinの場合は、内部表（内側テーブル）の結合キーにインデックスを作成することで全表走査が不要になるため結合に必要なループ数を削減できます。

Merge joinの場合は、結合キーにインデックスを作成することで結合前のソート時間を短縮できます。

インデックスを利用したNested loop joinの高速化の詳細解説は【SQL】JOIN(Nested loop join)の仕組みを理解し、インデックスで高速化するをご覧になってください。

最大値・最小値(MAX・MIN)の算出で利用される

インデックスによりカラムがソート済みの状態であるため、最大値・最小値の算出が高速になります。

全文検索が必要な場合は専用インデックスを検討する

LIKE '%キーワード%'のような中間一致・後方一致の検索ではB-treeインデックスは利用できません。

文章中のキーワード検索が必要な場合は、B-treeではなく全文検索インデックス（Full-Text Index）の利用を検討します。

RDBMS	ネイティブ機能	追加プラグイン/拡張
MySQL	FULLTEXT INDEX（標準）	Mroonga（日本語対応強化）
PostgreSQL	`tsvector` + GINインデックス	PGroonga, pg_bigm, pg_trgm（日本語・あいまい検索強化）

MySQL標準のFULLTEXT INDEXはCJK圏（日本語・中国語・韓国語）の解析精度に課題があるため、本格的な日本語全文検索が必要な場合はMroonga / PGroonga / pg_bigmの導入を検討します。

検索負荷が大きく、RDBMSの全文検索機能では性能要件を満たせない場合はElasticsearchなどの専用全文検索エンジンを別途用意するのが定石です。

データの分布は時間とともに変わる

インデックス設計は「現在のデータ分布と現在のクエリパターン」に対して最適化したものに過ぎません。

サービスの成長やビジネスロジックの変更に伴い、以下のような変化が起こりえます。

カーディナリティの変化: 当初はほぼ均等に分布していたstatus列が、運用後ほとんどのレコードがactiveに偏ってインデックスの絞り込み効果がなくなる
クエリパターンの変化: 新機能の追加で別の列を組み合わせたWHERE句が頻発するようになる
データ件数の変化: 当初はインデックスが不要だった小さなテーブルが大規模化してインデックスが必要になる

つまり「一度作って終わり」ではなく、定期的に見直すべき運用対象です。逆に、過去に作成したが現在は使われていないインデックスを放置すると、書き込み性能の劣化やディスク容量の浪費につながります。

未使用インデックスは以下のシステムビューで検出できます。

-- MySQL（performance_schemaを有効化した状態で）
SELECT * FROM sys.schema_unused_indexes;

-- PostgreSQL
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

チューニング全体の流れ — MENTOR原則

インデックスチューニングを体系立てて行うためのフレームワークとして、書籍『SQLアンチパターン』（12章インデックスショットガン）で紹介されているMENTOR原則があります。

頭文字	内容
Measure	現状のクエリ性能を計測する（スロークエリログ・EXPLAIN・プロファイリング）
Explain	EXPLAINで実行計画を確認し、ボトルネックを特定する
Nominate	改善対象として候補となるインデックスを洗い出す
Test	ステージングや本番同等のデータで効果を検証する
Optimize	キャッシュやバッファプールなど、インデックス以外の最適化も含めて検討する
Rebuild	断片化したインデックスは再構築する（`OPTIMIZE TABLE`、`REINDEX`等）

「とりあえず付けてみる」「思いつきで足す」のではなく、計測→分析→施策→検証のサイクルを回すことで、インデックスショットガン（無闇にインデックスを作りすぎる）アンチパターンを防げます。

まとめ

インデックス作成時のチェックポイント

レコード数は1万件以上あるか
検索によって全体の5~15%まで絞り込めるカラムか
更新頻度が低いカラムか
WHERE, ORDER BY, GROUP BY, COUNT, JOIN, MAX, MINで利用されるカラムか
全文検索が必要な場合はB-treeではなく全文検索インデックスを検討したか
計測→分析→施策→検証（MENTOR原則）のサイクルを回しているか
未使用インデックスの定期見直し体制があるか

なお、今回紹介したチェックポイントはあくまで原則です。

インデックスを作成したほうがよさそうなカラムでも、テーブルの構造やデータの分散によってはインデックスが有効に働かないケースもあります。

ですので、インデックスを作成したらインデックスがきちんと機能するか検証が必要です。

インデックスがSQLで利用されているかはEXPLAINで確認できます。

EXPLAINの詳細解説はMySQLのEXPLAINの読み方とチューニング時のチェックポイントで紹介していますのであわせてご覧になってください。

Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！