Lifeプログラミングブログ運営ライフハックお金キャリアお問い合わせサイトマップHOMEプログラミングデータベース【データベース】基礎から学ぶインデックス作成時のチェックポイント【データベース】基礎から学ぶインデックス作成時のチェックポイント2021年6月5日データベースツイートシェアはてブ送るPocket新規テーブルの作成やクエリチューニング時にインデックスを作成する機会があります。インデックスを作成することでSQLの実行時間を短くできます。そこで今回はインデックスを作成する際のチェックポイントについて紹介します。本記事ではB-treeインデックスの前提で話を進めます。データベースの種類やデータベースのバージョン、実データの構造によっては今回紹介する内容が当てはまらない可能性があるのでご注意ください。参考インデックスに関する基礎知識インデックスを作成する上で理解しておきたい基礎知識について紹介します。インデックスは必要なところにだけ作成するインデックスはテーブルとは別のオブジェクトとして保存されるためデータベースのディスク容量を使用します。不要なインデックスの作成はディスク容量の浪費につながるので控えましょう。key)およびユニークキー(UNIQUE制約があるキー)には値の重複を許さないユニークインデックスが自動で作成されます。主キーおよびユニークキーに対しては手動でインデックスを作成する必要はありません。KEY制約があるキー)に自動でインデックスが作成されるかはデータベースの種類に依存します。MySQLの場合は外部キーに自動でインデックスが作成されるため、インデックス作成の考慮が不要です。1PostgreSQLの場合はインデックス作成の考慮が必要です。2インデックスの見直しが必要になる時もあるレコードの増加に伴いテーブルのデータ構造が変わると、インデックスがうまく機能しなくなる場合があります。インデックスは一度作成したら終わりではありません。SQLのパフォーマンスが低下した場合はインデックスを見直す必要があります。インデックスが作成されたカラムはソート状態で管理されるインデックスが作成されたカラムはソートされた状態で管理されます。そのためソートを実行する際にインデックスは有効に働きます。複合インデックスのカラム順には意味がある複合インデックス(結合インデックス)とは複数のカラムを組み合わせたインデックスのことです。検索条件で一緒に使われるカラムに対して複合インデックスを作成することでSQLの高速化が期待できます。複合インデックスのカラム順には意味があるため、検索条件によっては複合インデックスが利用されない可能性があります。C)というカラム順の複合インデックスの場合、検索条件とインデックス利用可否の対応は以下の通りです。複合インデックスの詳細解説は【図解】B-treeを理解し、複合インデックスの順番を正しく作るをご覧になってください。NULL検索ではインデックスが利用されないという記述をよく目にします。NULLでもインデックスが利用されるケースは存在します。sec)インデックスを作成する前にチェックすることテーブルやカラムの特徴によってはインデックスを作成しても意味のないケースがあります。場合によってはインデックスを作成したことによってパフォーマンスが低下する可能性もあります。インデックスを作成するか検討する際のチェックポイントは以下の通りです。インデックスを作成時のチェックポイントテーブルの規模は十分大きいか絞り込みが十分にできるカラムかカラムの更新(削除・更新・追加)頻度は低いかテーブルの規模は十分大きいか小規模なテーブルの場合、インデックスよりもフルスキャンのほうが高速です。目安となるレコード数は1万件です。5レコード数が1万件未満の小規模なテーブルの場合、インデックスは不要な可能性が高いです。絞り込みが十分にできるカラムか目安として、検索条件によって全体の5~15%程度までレコード数が絞り込めるカラムであればインデックスによる高速化が期待できます。4絞り込みが十分にできるカラムか検証する際に利用される指標として『カーディナリティ』というものがあります。カーディナリティとはカラムがとりうる値の種類です。『男』『女』『そのほか』『無回答』という値のみをもつ性別に関するカラムであればカーディナリティは4となります。均等に種類が存在していると仮定した場合、カーディナリティ20のカラムを利用した検索だと全体の5%までレコードを絞り込めます。10~20程度のカーディナリティがインデックスを作成する際の指標となります。カーディナリティはあくまでカラムに存在する値の種類を表現した指標です。繰り返しになりますが、重要なのは検索によって全体の5~15%程度までレコード数が絞り込めるということです。カーディナリティが低くくても、絞り込み条件によってはインデックスが利用されるケースもあります。6逆にカーディナリティが高くても、データに偏りがあって絞り込みが十分にできない場合はインデックスの効果が期待できないケースもあります。カラムの更新(削除・更新・追加)頻度は低いかインデックスが作成されたカラムを更新する場合、カラムだけでなくインデックスのオブジェクトも更新する必要があります。そのため、インデックスは更新の性能を劣化させる原因となります。更新頻度が高いカラムにインデックスを作成する場合は、検索性能と更新性能のトレードオフを検討する必要があります。インデックスを作成すべきカラムインデックスを作成することでSQLの高速化が期待できるカラムは以下の通りです。BY)で利用される集計(COUNT)で利用される結合条件(JOIN)で利用される最大値・最小値(MAX・MIN)の算出で利用される条件文(WHERE)で利用されるインデックスにより全表走査が不要になるため検索が高速になります。複数のカラムを組み合わせた条件文を利用する場合は、複合インデックスの作成も検討するとよいです。BY)で利用されるインデックスによりカラムがソート済みの状態であるため、ソートが高速になります。複数のカラムを組み合わせたソートを利用する場合は、複合インデックスの作成も検討するとよいです。BY)で利用されるインデックスによりカラムがソート済みの状態であるため、グルーピングが高速になります。集計(COUNT)で利用されるインデックスが作成されていないカラムに対してCOUNTを実行すると時間がかかります。インデックスが作成されたカラムをA、作成されていないカラムをBとした場合、SQLとインデックスによる集計可否の対応は以下の通りです。join(ハッシュ結合、ハッシュ値マッチング)の3種類があります。joinの場合は、内部表(内側テーブル)の結合キーにインデックスを作成することで全表走査が不要になるため結合に必要なループ数を削減できます。joinの場合は、結合キーにインデックスを作成することで結合前のソート時間を短縮できます。join)の仕組みを理解し、インデックスで高速化するをご覧になってください。最大値・最小値(MAX・MIN)の算出で利用されるインデックスによりカラムがソート済みの状態であるため、最大値・最小値の算出が高速になります。MIXで利用されるカラムかなお、今回紹介したチェックポイントはあくまで原則です。インデックスを作成したほうがよさそうなカラムでも、テーブルの構造やデータの分散によってはインデックスが有効に働かないケースもあります。インデックスを作成したらインデックスがきちんと機能するか検証が必要です。インデックスがSQLで利用されているかはEXPLAINで確認できます。EXPLAINの詳細解説はMySQLのEXPLAINの読み方とチューニング時のチェックポイントで紹介していますのであわせてご覧になってください。Twitter(@nishina555)やってます。↩RECOMMENDデータベースMySQLパフォーマンスチューニングツイートシェアはてブ送るPocketプロフィールnishina大企業、ベンチャーと色々なところでWebエンジニアやっています。退職手続きから案件参画まで。人気の記事iPhoneの連絡先の保存がうまくできない!?原因は同期設定かも。