SQLの実行とパフォーマンス
ユーザーがSQLを実行しテーブルからデータを取りに行くときにユーザーは取りに行くデータを指定するが、取りにいくルートを指定することはない。そのルートによってデータベースのパフォーマンスに大きく影響する。ルートを最適化することによってパフォーマンスを挙げることができる。そのルートを選択する方法にインデックス設計がある。
- SQLパフォーマンスを改善するにはインデックス設計をする
- アプリケーション透過性：インデックスに指定によってアプリケーションへ影響することはないため、優れた手段である。
- データ透過性：インデックスによってデータの中身や構造が変わることはないのでデータに対しても優れた改善手段である。
- 大きな改善効果
DBを使用したことのある人ならば誰しもがパフォーマンスを気にしたことがあると思います。各DBによってチューニング方法はありますが、インデックスのようにどのDBにも共通で存在しているチューニング方法を知っておくと、パフォーマンス改善を求められたときに対策できる一つのアイデアになるかもしれません。
B-treeインデックス
インデックスの種類は様々あるが、一番最初はB-treeインデックスを使用する。B-treeインデックスは他のインデックスに対して平均点の高さ（満遍なく性能が良い）が優れている。
- 均一性：各キー間で検索速度のばらつきが少ない
- 持続性：データ量の増加に対してパフォーマンスの低下が少ない
- 処理汎用性：検索などのいずれの処理に対してそこそこ早い
- 非等値性：不等号を使用してもそこそこ早い
- 親ソート性：GROUP BYなどのソートに対する処理に対してそこそこ早い
B-treeインデックスの設計方針
具体的にどのようなデータベースに対して設計するか
- 大規模なテーブルに対して作成する
- カーディナリティの高い列に作成する
- SQL文でWHERE句の選択条件、または結合条件に使用されている列に使用する
データ量が少ない場合はインデックスの効果はなくなる。
B-treeインデックスの作成の際はカーディナリティが多いテーブルに対して作成するのが効果的。カーディナリティとは行の数。インデックスは列に対して作成して、SELECT文などの実行速度を上げる。
- カーディナリティの注意点
- 複数列に対してインデックスを作成する際はカーディナリティは複数列が対象になるということを意識する。
- カーディナリティが高くても特定の値にデータが集中してるような列に向いていない
その他注意事項
- 主キーや一意制約の列には不要
内部的にすでにB-Treeインデックスが作成されている。 - インデックスは更新性能を劣化させる
インデックスを設定したデータを更新するとき、インデックスの更新も必要になるため更新性能が劣化してしまう。 - 定期的なメンテナンスが必要になる
データの更新などが長期的に続くと構造が崩れてしまうため、インデックスの再構築など性能を維持するためにメンテナンスが必要になる。
インデックスの作成しパフォーマンスを上げるには一定の条件はあるものの、試してみてパフォーマンスの変化を試してみるのも面白いかもしれません。テーブルやデータの内容に大きな影響を与えないということも試しやすい理由の一つです。自身の使用しているDBのインデックスの作成の仕方を調べて実装してみましょう！！
まとめ
インデックスを作成することでSQLのパフォーマンスを向上させ、アプリケーションの性能の向上にまでつながる。インデックスは列に対して作成することでSELECT文やJOIN句などの重い処理の速度を上げることができる。最もメジャーなインデックスとしてB-Treeインデックスがある。木構造を取ったインデックスであり、平衡木を作成することで処理を簡略化させる。インデックスを作成するのみ適したケースは大規模のテーブルに対しての作成、カーディナリティ（行）の高い列への作成、WHERE句などの選択条件・結合条件に使用される列に対して作成することである。インデックスはデータの更新や構造の変化などのより性能が劣化していくため、定期的なメンテナンスが必要である。以上を考慮し、インデックスを利用してデータベースの速度向上に寄与することができる。
残った疑問
- インデックスを使用すべきデータ量はどの程度のなるのか
- どのDBでもインデックスは使用可能？？DBによって適しているインデックスはあるか？？
参考資料
- 達人に学ぶDB設計徹底指南書/ミック
- B-treeインデックス入門：https://qiita.com/kiyodori/items/f66a545a47dc59dd8839