🐦

インデックスについて

2024/05/03に公開

SQL

インデックス

パフォーマンスチューニング

tech

はじめに

テーブル定義書を作成するたびにインデックスはどこに付けるべきか悩んだり、私自身の中でインデックスをつけることでのメリットやデメリットが言語化できていなかったので、書籍やWebサイトをもとにまとめてみました。

インデックスとは

DBのテーブルの検索のパフォーマンスを向上させる仕組みです。
具体例でよく使われる表現としては本で特定のワードが記載れているページを見つける際に目次を確認してページを特定するか、１ページずつ確認して特定するか、巻末の索引から特定するかの方法がありますが、インデックスを利用した検索とは「巻末の索引から特定するかの方法」になります。インデックスを利用することでユーザーは高速で目的のレコードを取得することができます。
インデックスには以下の種類と特徴があります。

B-treeインデックス
- 多くのDBでインデックスを作成する際に暗黙的に使用されている。
- 木構造でルートかリーフまでが一定のため、どの値に対してもパフォーマンスが一定
- 等号だけでなく不等号を用いた検索、範囲検索でも使用できる
ビットマップインデックス
- データをビットフラグに変換して管理するインデックス
- 性別や血液型などの値のばらつきが小さいカラム向き
ハッシュインデックス
- イコールを用いた検索が高速にできる。
- 範囲検索が使用できない。
- イコールを用いた検索の中でも全レコード中検索対象のレコードの割合が高い時に効果が高い

複数あるインデックスの中で今回は使用頻度の高いB-treeインデックスのみに焦点を当ててお話させて頂きます。そのため、以降に出てくるインデックスについての記載は全てB-treeインデックスに当てはまるものとなります。

インデックスのメリット

テーブルの検索が高速なること以外にもインデックスには以下の特徴があります。

インデックスを作成するにあたってアプリケーションへの影響がない
上記と同様にテーブルの既設のレコードにも影響がない
インデックス構築時にソートされているため、COUNTなどの集約関数、ORDER BYを使用した際にソートがスキップされる。
背の低い木構造のため、レコード数が増えても検索への影響が少ない

インデックスのデメリット

必ずしもパフォーマンスが向上するとは限らない
インデックスを作成したとしてもSQLやテーブルのレコード数次第ではパフォーマンスが向上しないことがあります。詳細については次節で述べます。
更新処理の遅延
データを更新する際にテーブルのレコードにも更新が実行されるだけでなく、インデックス上で管理されているデータにも更新が実行されます。そのため、検索のパフォーマンスが向上するからと多くのカラムをインデックスにすると更新時にインデックスの更新による遅延が生じてしまいます。
インデックスの構造の劣化
更新処理をしていく中でインデックスの構造の劣化が生じ平衡木だったのが非平衡木になります。
その結果、これまで値に関わらずパフォーマンスが一定だったのが、劣化によりパフォーマンスのばらつきが生じる
様になります。
なお、インデックスの構造が劣化した場合は再構築をする事で解消します。

インデックスが有効、無効、不要なケース

インデックスが有効ケース

大規模なテーブル※１
インデックスの検索は選択率が低いほど効果を発揮するため、
大規模なテーブルのほど選択率が低くなる可能性が高くなるためインデックスを設定することは有効
カーディナリティーが高い※１
カーディナリティーとは値のばらつきを示しています。
カーディナリティーが低いとは値のバラツキが少ない性別や血液型などが相当します。
逆にカーディナリティーが高いの一意の値になります。
選択率が低い
選択率とは全レコード中の検索にヒットするレコードの割合です。
この選択率が低いほどインデックスが有効になります。
WHERE句、結合の条件に設定されているカラムがある
基本的には有効ですが、選択率、演算やLIKE検索の内容次第ではインデックスが不要だったり無効化されます。
詳細については後述します。

インデックスが不要なケース

小規模なテーブル※１
レコードが少ない場合はインデックスを用いた検索よりもテーブルフルスキャンの方が速いことがあります。
選択率が高い※１
上記と内容が被りますが、WHERE句で絞り込んでも選択率が高い場合はテーブルフルスキャンの方が速いことがあります。
SQLでWHERE句、結合などの条件そのものが書かれていない
この場合はテーブル上のカラムをインデックスに設定する必要はないと考えています。
ただし、検索のパフォーマンスを向上させるために検索対象のカラムを全てインデックスに設定することがあります。
この手法を「カバリングインデックス」と言います。

インデックスが無効なケース

中間一致、後方一致を使用した検索
インデックスが有効になるのは前方一致のみです。
条件にIS NULL、IS NOT NULLを使用している。
インデックスにはNULLが存在しないため、インデックスが使用されません。
OR検索
OR検索ではインデックスが無効になります。しかしIN句ではインデックスが有効のため、こちらを利用して検索します。
WHERE句内でインデックスに設定したカラムが演算されている時
例えば以下ではカラムが演算されているためインデックスは適用されません。

SELECT * FROM tbl WHERE col/2 > 100では

暗黙的な型変換
条件指定する際に暗黙的な型変換をしている場合はインデックスは使用されません。
CASTなどの明示的な型変換を行なっている時はインデックスは使用されます。

※１：DBの種類やバージョンによってパフォーマンスが異なるため、実際の環境で確認が必要

まとめ

インデックスはテーブルの検索を高速にする仕組み。
状況次第ではテーブルスキャンの方が速くなる。
インデックスは更新処理の遅延、構造劣化による検索処理の遅延を起こす。
インデックスが不要だったり、無効になるケースがある。
インデックスを付けるかはテーブルの使用用途や作成された経緯を考慮する。