新規テーブルの作成やクエリチューニング時にインデックスを作成する機会があります。
インデックスを作成することでSQLの実行時間を短くできます。
そこで今回はインデックスを作成する際のチェックポイントについて紹介します。
なお、本記事ではB-treeインデックスの前提で話を進めます。
また、データベースの種類やデータベースのバージョン、実データの構造によっては今回紹介する内容が当てはまらない可能性があるのでご注意ください。
インデックスに関する基礎知識
インデックスを作成する上で理解しておきたい基礎知識について紹介します。
インデックスは必要なところにだけ作成する
インデックスはテーブルとは別のオブジェクトとして保存されるためデータベースのディスク容量を使用します。
不要なインデックスの作成はディスク容量の浪費につながるので控えましょう。
主キーとユニークキーはインデックス作成の考慮不要
主キー（primary key）およびユニークキー（UNIQUE制約があるキー）には値の重複を許さないユニークインデックスが自動で作成されます。
ですので、主キーおよびユニークキーに対しては手動でインデックスを作成する必要はありません。
外部キーに対するインデックス作成の考慮はRDBMSに依存する
外部キー（FOREIGN KEY制約があるキー）に自動でインデックスが作成されるかはデータベースの種類に依存します。
MySQLの場合は外部キーに自動でインデックスが作成されるため、インデックス作成の考慮が不要です。1
PostgreSQLの場合はインデックス作成の考慮が必要です。2
インデックスの見直しが必要になる時もある
レコードの増加に伴いテーブルのデータ構造が変わると、インデックスがうまく機能しなくなる場合があります。
インデックスは一度作成したら終わりではありません。SQLのパフォーマンスが低下した場合はインデックスを見直す必要があります。
インデックスが作成されたカラムはソート状態で管理される
インデックスが作成されたカラムはソートされた状態で管理されます。そのためソートを実行する際にインデックスは有効に働きます。
複合インデックスのカラム順には意味がある
複合インデックス（結合インデックス）とは複数のカラムを組み合わせたインデックスのことです。
検索条件で一緒に使われるカラムに対して複合インデックスを作成することで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;
*************************** 1. row ***************************
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)の算出で利用される
インデックスによりカラムがソート済みの状態であるため、最大値・最小値の算出が高速になります。
まとめ
- レコード数は1万件以上あるか
- 検索によって全体の5~15%まで絞り込めるカラムか
- 更新頻度が低いカラムか
- WHERE, ORDER BY, GROUP BY, COUNT, JOIN, MAX, MIXで利用されるカラムか
なお、今回紹介したチェックポイントはあくまで原則です。
インデックスを作成したほうがよさそうなカラムでも、テーブルの構造やデータの分散によってはインデックスが有効に働かないケースもあります。
ですので、インデックスを作成したらインデックスがきちんと機能するか検証が必要です。
インデックスがSQLで利用されているかはEXPLAINで確認できます。
EXPLAINの詳細解説はMySQLのEXPLAINの読み方とチューニング時のチェックポイントで紹介していますのであわせてご覧になってください。
Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！
