索引とは？
テーブルの中身を検索する際、全部のデータにアクセスすることがないように工夫されたデータ構造。
これがないと全件を検索することになるため、パフォーマンスが劣化します。
また、インデックスが作られていてもそれを使うようにsqlを書く必要があります
索引の種類
Oracleでは
・B*Tree索引
・ビットマップ索引
・ファンクション索引
・逆キー索引
を定義できます
B*Tree索引
データ構造は以下の感じ
https://www.shift-the-oracle.com/index/btree-index.html
から抜粋
ルートブロック：キー値の範囲と下位ブロックのポインタ情報
ブランチブロック：キー値のより細かい範囲と下位ブロックのポインタ情報
リーフブロック：キー値とテーブルの具体的なアドレスを示すROWID情報および前後のブロックのポインタ情報
をそれぞれ保持しています。
また、キー値はあらかじめ全てソートされてます。
具体例として、A〜Zの２６文字のアルファベットから「C」を検索する場合を考えます。
(書籍から抜粋ですが、写真ですみません…)
まずルートブロックのデータはソートされているため、「C」は前半にあるとわかります。
そこから左のブランチブロックに入り、Cがある範囲を探します。
A~Dの範囲にCがあるため、もっとも左のリーフブロックに入ります。
「C」に紐づいているROWIDから結果を返します。
ROWIDはアドレスを直接指定しているため、ここは高速です。
n分木の構造をしているため、フルスキャンするよりも高速にデータを返します。
postgresで
CREATE INDEX
コマンドを使うと、デフォルトでこの索引を作成します。
メリット
・データ量が多いテーブルでも深さが深くなりにくいため、高パフォーマンスを発揮します。
ただし、ブロック間のアクセス速度はデータ量が少ない時と比べて多少落ちます。
・ツリー構造が効果的に作用するため、データの種類が多い列に対して高パフォーマンスを発揮します。
・前後のリーフブロックの情報も保持しているため、範囲検索にも強いです
デメリット
・データの追加、更新時に索引も同時に更新するためオーバーヘッドが発生します。
索引を増やしすぎると悪影響
・データを削除してもその領域を解放しないため、頻繁に削除が発生する際には悪影響です。。
対策としてはREBUILDやDROP CREATEを使用すること
・null値を指定できないため、where句にis null is not nullを指定した際には索引が適用されません
・TO_CHAR、TO_NUMBER等の組み込み関数を使って値を変更すると索引が適用されなくなります。
ビットマップ索引
ある範囲内でユニークな値をグループ化し、それをキーとした開始ROWIDと終了ROWID、ビットマップで管理します。
同一の値で管理するため、データの種類が少ない時に有効です。
メリット
・特定の条件に一致するデータの取得時や、AND条件、OR条件を指定した時に有効です。
・NULL値を指定できます。
デメリット
・更新処理が多いテーブルに作成すると、ロック待ちによるオーバーヘッドが発生します。
ファンクション索引
あらかじめ組み込み関数を使用して値を変換してB*Tree索引のデータ構造を作ることで、
組み込み関数でも索引が適用されるようにしたものです。
ただし、作る時にちゃんと考慮しないと「ほぼ呼ばれない索引」が無駄に増えることになります。
逆キー索引
B*Tree索引では、その構造上連番のデータを作ると同一ブロックに処理が集中します。
逆キー索引ではリーフブロックに値を反転させてから格納することで均等に分散させられます。
ただし、この場合範囲検索で索引が効かなくなるので注意
チューニングするときに注意するところ
不要なフルテーブルスキャンの排除
フルテーブルスキャンは非常に多くのブロックにアクセスするので遅くなります。
これが発生する条件は
・WHERE句が指定されていない
・指定されているけど対象の列に索引が作成されていない
・索引が作成されているが使用できない。
のいずれか
なので対策としては
・頻繁に呼び出されるクエリに関して、インデックスを貼れるなら貼っていくべき。
ただし、むだなインデックスが増えていかないように注意
・索引が効かない条件に当てはまるかチェックし、当てはまるようなら効くようにSQLを書き換える
索引を使用できないケース
NULL値の検索
select
*
from
test_table_2
where
id is not null
B*TreeではNULL値の検索に索引が効きません。
カラムidはインデックスが貼られていますが、上記クエリが返ってくるまでに14.344 msかかりました。
ちなみにidに値を指定すると0.576 msだったため、パフォーマンスが大きく低下していると言えます。
対策として、上記の例であれば、idに値を入れたくないときは-1を入れ、
その値があったらデータが入ってないとしてしまうとか…
（ただし、この場合は当然いろんなところに影響してしまうため、十分注意するのが前提）
暗黙の型変換が行われる場合
where (char列)=1のように型が違う値を入れると、型変換が行われるためこの場合も索引がききません。
やるのであれば
where (char列)='1'
のようにちゃんと指定するべきです
ほかにも、
・LIKE句の中間一致と後方一致
・!=を使用した時
場合、索引がきかなくなるので注意
データの偏りに注意する
ほとんどのデータが同じ値の列にインデックスを作成するより、フルテーブルスキャンの方が早いことがあります。
これは、「抽出件数が全体レコード数の10%~15%以内にインデックスを作成するべき」という目安に通じます
あるテーブルtest_table_2において、値が二つしかない列is_humanにインデックスを貼った時と貼ってない時で下記クエリを投げてみました。
select
*
from
test_table_2
where
is_human = false;
結果、
インデックスあり：8.449 ms
インデックスなし：7.842 ms
とわずかにインデックスなしの方が早い結果となりました。
また、インデックスが貼られており、主キーのカラム（＝データが一件しかない）で下記を検索したところ、
select
*
from
test_table_2
where
id = 1;
実行時間は0.576 msとなりました。
まとめ
索引を使う場合、
・対象のテーブルでよく投げられるクエリは何か
・そのクエリに対し適切な索引が貼られているか
・SQL文は適切か
を注意する必要があります。