What is this?
達人に学ぶDB設計徹底指南書を参考にインデックス、Explainによる実行計画の調査方法などをメモ的にまとめたもの
assumption
インデックスとは？
まず、DBのパフォーマンスを決める要素は主に以下の二つが存在する。
- インデックス
- 統計情報
その中でも、DBのパフォーマンス改善の手法としてインデックスが用いられることが最もポピュラー
インデックスは * 探すレコードを識別するデータの項目
- 対象レコードの格納位置を示すポインタ
で構成されており、これを利用してデータの格納位置を特定し、その位置を直接アクセスする事で、表の検索速度を上げることが出来るもの。
インデックスがない場合は、検索を先頭の行から順に行うのでデータ量が多くなるほど検索に時間がかかる事がある。
【理由】
アプリケーションのコードに影響を与えない
テーブルのデータに影響を与えない
性能改善の効果が高くコスパが良い
さらに、その中でもB-treeインデックスが最も基本(この他にもビットマップインデックス、ハッシュインデックスなどがある)なので、今回はB-treeインデックスを深堀する。
B-treeインデックスについて
特徴
下記の項目において、バランスが良く汎用性が高い
- 均一性：各キー値の間で検索速度にバラツキが少ない。
- 持続性：データ量の増加に比してパフォーマンス低下が少ない
- 処理汎用性：検索、挿入、更新、削除のいずれの処理もそこそこ速い。
- 非等値性：等号（=）に限らず、不等号（<、>、<=、>=）を使ってもそこそこ速い。
- 親ソート性：GROUPBY、ORDERBY、COUNT／MAX／MINなどなどソートが必要な処理を高速化できる。
引用：達人に学ぶDB設計徹底指南書
均一性
- B-treeインデックスはどのリーフからもルートへの距離が均一の平衡木構造のため、検索を同じ計算量で行える
- 更新削除などで平衡木構造が崩れることがあるので、常に検索の速度が一定となる訳ではないので注意が必要
持続性
- 平衡木の高さは平均的に３〜４と平たい構造のため、データ量が増えても更新や検索にかかる時間や負荷は上がりにくいと言う性質
引用：達人に学ぶDB設計徹底指南書
処理汎用性
- 検索、挿入、更新、削除いずれの処理もデータ量が増えても処理コストの差が少ないと言う性質
非等値性
- =のみでなく>=やBETWEENのような範囲検索においても高速に行える性質
- B-treeインデックスではキー値が必ずソートされるため、範囲検索では特定のノードよりも左というように探索範囲を絞り込むことが可能のため
- <>などの否定条件による絞り込みは、特定のリーフ以外というような場合があるので非等値性の恩恵は受けられないので注意が必要
親ソート性
- B-treeインデックスのキーを使ってソートを行うことでソート処理をスキップすることが出来る性質
- RDBMSにおいてソート処理は別メモリが確保されるほどコストの高い処理という前提
B-treeインデックスの使いところ
- 規模の大きなテーブルに付与する
- カーディナリティ（列の値がどの程度の種類を持つか。例えば営業日など）の高い列に付与する：基準は特定のキーを指定して、5%程度に絞り込めること
- SQL文でWHERE句の選択条件、結合条件に使用されている列に作成する
ユニークインデックスについて
表内のデータの 2 つの行が同一のキー値を持たないようにすることによりデータ整合性の維持に貢献する索引
主キーとユニークインデックスの違い
- 主キー(primary key)
- テーブルのレコードを一意（ユニーク）に決定するための列名の組み合わせ
- テーブルに一つしか持てない
- 主キーは内部的にユニークインデックスが貼られる
- ユニークインデックス(UNIQUE INDEX)
- テーブルのレコードを一意（ユニーク）に決定するための列名の組み合わせ
- テーブルの列（の組み合わせ）何箇所も持てる
EXPLAINでの調査について
SQLの実行計画（どのインデックスを使ってクエリを取得するかなど）を取得するためのステートメント
インデックスが正しく使われているか、クエリを効率的に処理出来ているかを確認する際に用いる
＜見方＞
pending....
