6-3 B-treeインデックスの設計方針
B-treeインデックスはどの列に作れば良いか？
指針は3つ
大規模なテーブル
カーディナリティが高い列
WHERE句の選択条件、または結合条件に使用されている列
以下で詳しく解説
B-treeインデックスとテーブルの規模
データ量が少ない場合、フルスキャンでもB-treeインデックスでも処理速度は変わらない
https://scrapbox.io/files/68809e13292312dc5ce97860.png
データ量が少ないと言える閾値は、目安としてレコード数が10万件
B-treeインデックスとカーディナリティ
例）
性別カラムに「男性」「女性」「不詳」の種類があるなら、カーディナリティは3
インデックスを使用する目安は、特定カラムの値を指定したときに、カーディナリティの5%程度に絞り込めるカーディナリティがあること
例1）
都道府県カラムで異なる値が47あるとする
このとき、1/47 = 約2%
カーディナリティは高いと言える
インデックスを使用する意味はある
例2）
性別カラムで異なる値が3つあるとする
このとき、1/3 = 約33.3%
カーディナリティは低いと言える
インデックスを使用する意味はない
カーディナリティの注意点
1️⃣
複合列に対してインデックスを作成する場合、カーディナリティは複合列の組み合わせで考える
例）
〇〇カラムのカーディナリティは2
△△カラムのカーディナリティは10
××カラムのカーディナリティは5
この時の絞り込み結果は、カラム単体だとそれぞれ5%より大きくなる
しかし、この3つのカラムを複合列にすると、絞り込み結果は1%
この場合は、インデックスを作成する必要がある
2️⃣
カーディナリティが高くても、特定の値に集中している列は、インデックス作成には向かない
例）
道府県カラムがある
値の99%が東京都
値の1%が東京都以外
この場合は、インデックスを作成する意味はない
フルスキャンしていることに近しいから
B-treeインデックスとSQL
SQLで検索条件や結合条件に使用する列にインデックスを作成する
ただし、検索条件や結合条件のSQL文でもインデックスが効かないパターンもあるので注意⬇️
1️⃣ インデックス列に演算を行なっている
code:sql（col_1カラムにインデックスを作成しているが効かない）
SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100;
インデックスを作成した列は裸で用いる
2️⃣ インデックス列に対してSQL関数を適用している
code:sql
SELECT *
FROM SomeTable
WHERE SUBSTR(col_1, 1, 1) = 'a';
3️⃣ IS NULL 述語を使っている
code:sql
SELECT *
FROM SomeTable
WHERE col_1 IS NULL;
4️⃣ 否定形を用いている
code:sql
SELECT *
FROM SomeTable
WHERE col1_1 !=（<>） 100;
5️⃣ 後方一致、または部分（中間）一致のLIKE述語を用いている
code:sql
SELECT *
FROM SomeTable
WHERE col_1 LIKE '%a';
code:sql
SELECT *
FROM SomeTable
WHERE col_1 LIKE '%a%';
6️⃣ 暗黙の型変換を行っている
code:col_1は文字列型.sql
SELECT *
FROM SomeTable
WHERE col_1 = 10;
B-treeインデックスに関するその他の注意事項
主キーおよび一意制約の列には作成不要
内部的にインデックスを作成しているので、二重にインデックスを作成する必要は無い
B-treeインデックスは更新性能を劣化させる
インデックス全般の欠点
インデックスが作成されている対象の列値が変更されると、インデックス内に保持している値も変更しないといけない
インデックスを作成すればするほど、テーブル対する更新性能が劣化していく
定期的なメンテナンスを行うことが望ましい
データベースの長期運用により、インデックスの性能は劣化するため。定期的にインデックスの再構築を行うことが望ましい
再構築の頻度は、DMBSごとに指標があるのでマニュアルを確認する