6-2 インデックス設計

インデックスが、SQLパフォーマンス改善の常套手段な理由は以下の3つ

1️⃣ アプリケーション透過的

インデックスを使うかどうかは、DBMSが自動的に判断する

DB設計者は、データベースにインデックスを作成するだけなので、導入ハードルが非常に低いのが利点

インデックスを使うときに、アプリケーションの変更は必要ない

アプリケーションから見ればインデックスは空気のような存在

2️⃣ データ透過的

インデックスを使ったとしても、DBに格納されているデータの中身は影響を受けない

テーブルの構造も変わらない

3️⃣ 大きな性能改善効果

インデックスと使ったときの機能改善は、デメリットよりもメリットが大きく上回りやすい

ただし、テーブルの全列にインデックスを使って良いわけでない

インデックス・ショットガンというアンチパターン

詳しくは、SQLアンチパターンの第12章で紹介されている

まずはB-treeインデックスから

インデックスにはいくつか種類がある

ただ、メジャーなB-treeインデックスを覚えておけば問題ない

どれくらいメジャーかというと、DBMSに対して、オプションなしでインデックスを使うと、暗黙的にB-treeインデックスになるくらい

B-treeインデックスの長所

B-treeインデックスの長所は、何でも平均以上にできる優等生であること

https://scrapbox.io/files/6879cfe2fdc9818f05acc30b.jpg

均一性：各キー値の間で検索速度のバラつきが少ない

持続性：データ量の増加に対してパフォーマンスの低下が少ない

処理汎用性：CRUDのいずれの処理もそこそこ速い

非等値性：等号に限らず、不等号を使ってもそこそこ速い

親ソート性：ソート（GROUP BYやCOUNTなどなど）が必要な処理を高速化できる

B-treeインデックスの構造

木構造でデータを保持している

https://scrapbox.io/files/6879d5d58f2adcacc73fbb18.jpg

最下層のノード（図の濃い青色の四角）のみが、実際のデータへのポインタを保持している

データベースは、最上位のノードから順に最下層のノードを辿る

1️⃣ 均一性

検索速度にバラつきが少ないのは、B-treeが平衡木だから

B-treeは最初に作られたときは平衡木だが、テーブルへの挿入・更新・削除を繰り返すと非平衡木になることもある

DBMSは自動で平衡木へ修復する機能も持っているが、データベースが長期間運用されるとどうしても非平衡木になりやすい

2️⃣ 持続性

B-treeの性能劣化は長期的に見ても緩やか

データベースのデータ量が増えても、B-treeの検索にかかる時間はほとんど増えない

https://scrapbox.io/files/68809e13292312dc5ce97860.png

3️⃣ 処理汎用性

B-treeは挿入・更新・削除のコストも、検索と同じ

4️⃣ 非等値性

不等号（<, <=）やBETWEENの範囲検索の条件でも、検索を高速化できる

高速化できる理由は、B-tree構築時にキー値がソートされているから

それにより、取得したい情報が、特定のノードより右か左かを絞り込める

ただし、否定条件（!=）だとインデックスが効かないので注意

5️⃣ 親ソート性

前提

ソート（集約関数・BRDER BY句・ウィンドウ関数など）は、コストの高い演算子

SQL文では、極力大きなソートを避けることでパフォーマンス低下を回避する

B-treeはキー値がソートされているので、インデックスを貼った列をORDER BY句に指定することで、ソート処理のスキップが可能になる