※まだ勉強し始めたばかりなので基礎的な内容になります。
インデックスについて
パフォーマンスを改善したい！そんな時、何でもかんでもインデックスを設定すればよいわけではありません。
インデックスを設定すべき項目、設定すべきではない項目があるのでまとめます。
また、インデックスを設定していても働かないパターンがあるので併せて記載します。
前提としてレコード数は十分に多く、統計情報は更新されているものとします。
インデックスを設定すべき項目
カーディナリティの高い項目
カーディナリティはデータのばらつきを表す指標です。
これが高いほど一意性が高いことを表しており、インデックスが有効に働きます。
選択率の低い項目
選択率とはwhere句に使用した際に、どのくらいデータが絞られるかを表す指標です。
これが低いほど有効に働きます。
大体5~10%が目安になってくるようです。
結合条件としての使用頻度が高い項目
上記2条件を満たす使用頻度の高い項目にインデックスを張ると大きなパフォーマンス改善が見込めます。
Json内項目
便利なJsonですが、SQLで使用するとなると大きなコストがかかるそうです。
Json内項目にインデックスを設定することでパフォーマンスの改善が見込めるようです。
※Json関してはそもそも使用しない方が良いと思っています。
インデックスを設定すべきではない項目
SQL実行時のパラメータによって選択率が変動する項目
パラメータAでは選択率が低く有効に働くものの、パラメータBではほとんど絞ることができない場合に逆にパフォーマンスを悪化させる可能性があります。
例えば日付項目(date)に、各レコードで十分にバラバラな日付が設定されていたとします。
これだけを聞くとインデックスを設定すべき項目だと思いますが、
where句で以下のような条件での使用頻度が高く、多くのデータがヒットしてしまう場合は設定すべきではないと思います。
select *
from test t
where t.date < :paramDate
更新が頻繁に行われる項目
検索のパフォーマンスは向上するかもしれませんが、更新時のパフォーマンス低下に繋がる
恐れがあります。
そもそも効かないインデックス
中間一致、後方一致のlike検索
インデックスが効くのは前方一致のときです。
左辺での演算・関数
左辺はインデックスを使用する部分なので、基本的に右辺を使いましょう。
どうしても左辺で関数をする必要のある場合は、関数インデックスを使用しましょう。
is null
nullにインデックスは適用できません。
否定
<>やnot in、not existsを使用するとインデックスが効かなくなります。
おわりに
不要なインデックスは削除して整理しましょう。
複合インデックスについてはまたの機会に勉強してまとめます。
参考
SQL実践入門──高速でわかりやすいクエリの書き方
JSONデータの索引
[Oracle Cloud] Oracle Database で JSON データに Index を設定する