インデックスについてゆるくまとめる
アプリケーションエンジニアとしてデータベースと仲良くなろうということで、
このあたりの本を読みました。
そこで、インデックスについて何も知らんな、ということでゆるくまとめることにしました。
- インデックスについて
- RDBで使われるインデックス
- インデックスの有効活用
- インデックスによる性能向上が難しいケース
- インデックスによる悪影響
- インデックスが使えない場合の対処
- テーブルスキャンを選択した方がよい場合
- （最後に）本読んだ感想
インデックスについて
検索を高速化するために用いられるもの。 索引のイメージに近くて、キーワードを検索すると、そのキーワードの場所がわかる感じ。
RDBで使われるインデックス
RDBで使われているインデックスだいたい以下のとおり。
- B-tree(B+tree)インデックス
- ビットマップインデックス
- ハッシュインデックス
ここでは、B+treeインデックスについて少し取りあげる。
B+treeインデックスについて
多くのデータベース製品で採用されている検索アルゴリズム。
B-treeの修正バージョン。Oracle、PostgreSQL、MySQLなどで採用されている。
B+treeの検索性能が優れているところ
- ルートとリーフの距離を一定に保つようにされているため、検索性能が安定
- 木の深さが約3～4レベルくらいで一定していて、データもソートして保持しているため、2分探索によって検索コストを小さく抑えることができる
- データがソートされていることから、集約関数などで必要になるソートをスキップできることがある。
インデックスの有効活用
どのような列に対してインデックスを作成するべかの基準となるのが、列のカーディナリティと選択率。
カーディナリティとは値のばらつき具合を示す概念。
選択率とは特定の列の値を指定したときに行をテーブル全体の母集合からどの程度絞り込めるかを示す概念。
基準となる2つの指標
インデックスが使用できる構文
インデックスを使える主なSQLの構文は以下の通り。
- WHERE句
- JOIN
- 相関サブクエリ
- ソート
インデックスによる性能向上が難しいケース
大規模なデータベースになればなるほど、インデックス設計が重要になってくる。
注意として、インデックス設計はテーブル定義とSQLだけをみれば完結するものではないこと。
絞り込み条件を見極める必要があり、SQL文・検索キー列のカーディナリティを知る必要がある。
例えば以下のケースでは性能向上が難しい。
- SQL文に絞り込み条件が存在しない
- ほとんどレコードを絞り込めない
- インデックスが使えない検索条件
- 中間一致または後方一致のLIKE述語
- 索引列で演算を行っている
- (例) where col_1 + 1 > 100
- IS NULL述語を使っている
- 牽引列に対して関数を使用している
- 否定形を用いている
インデックスによる悪影響
インデックスが増えるほど、以下の悪影響を及ぼす。
- テーブル更新時にオーバヘッドが大きくなる
- 必要なディスクスペースも大きくなる
- データサイズが増えることでバッファプールのヒット率も悪化
インデックスが使えない場合の対処
大きく分けて3通り。
- 外部設計による対処
- データマートによる対処
- インデックスオンリースキャン
外部設計による対処
外部設計レベルでパフォーマンスを意識した調整を行うこと。
例えばUI設計でクエリが実行されないようにアプリケーション側で制御する。
とくにアプリケーションとデータベースの設計がそれぞれ専門で実施されていると、 コミュニケーションの断絶が起きがち。 システムで重要機能要件と性能のためによるトレードオフについて妥協点を探す議論が必要。
データマートによる対処
データマートとは、 特定のクエリで必要とされるデータだけを保持する、相対的な小さなサイズのテーブルこと。
サマリテーブルと呼んだりする場合も。
目的はアクセス対象のテーブルサイズを小さくすることでI/O量を減らすこと。
データマート採用の注意観点
データの鮮度
- データ同期のタイミングが短いほど鮮度は新しい。
- 頻繁に更新処理実行されればパフォーマンス劣化の危険性がある
- 伝統的には夜間バッチ実行で鮮度は最低1日前
- データ同期のタイミングが短いほど鮮度は新しい。
データマートのサイズ
- オリジナルテーブルのサイズと同じであれば意味がない
- データマートの数
- 機能要件に即したエンティティではないため管理が比較的難しい
- ストレージ容量が逼迫
- パックアップをストレージのスナップショット機能などで取得すると無駄にバックアップウィンドウを圧迫
- バッチウィンドウ
- データマート作成に時間がかかるため、バッチウィンドウを圧迫
- 些細な差分更新でない限り統計情報も収集する必要がある
- こうした処理を余裕を持って収めるためのパッチウィンドウとジョブネットの考慮が必要
インデックスオンリースキャンによる対処
インデックスオンリースキャンとは、 クエリ実行に必要なカラムがインデックスに含まれてあれば、テーブル全体にアクセスせず、インデックスだけにアクセスするクエリのこと。 このようなインデックスをカヴァリングインデックスという。
利点はI/Oを削減すること。
インデックスはテーブル列のサブセットしか保持していないため、そのサイズはテーブルに比べるとかなり小さい。
また、データマートにはアプリケーション側の改修が必要だが、インデックスの場合はそうした改修が不要。
インデックスオンリースキャン採用の注意観点
- DBMS(とくに古いバージョン)によっては使えないことがある
- 1つのインデックスに含められる列数には限度がある
- 更新のオーバヘッドを増やす
- 定期的なインデックスのリビルドが必要
- 検索性能はインデックスのサイズに依存するので、サイズのモニタリングやリビルドが必要
- SQL文に新たな列が追加されたら使えない
テーブルスキャンを選択した方がよい場合
インデックスを使わずにテーブルスキャンを選択した方がいいケース
- あるインデックスを使うクエリの実行頻度がきわめて低い1日に1回など）
- テーブルのサイズが非常に小さい
- インデックスの対象は数万～数十万が目安
- 検索結果が非常に多くの行にヒットする
- インデックスには10%未満を指標とした方がよい
（最後に）本読んだ感想
３冊読み通してそれぞれの難易度をいうと、 SQL実践入門と理論から学ぶデータベースの本が同じぐらいで、 失敗から学ぶRDB本はやや易しい、という感じ。
違いをあげるとすると、SQL実践入門ではインデックスの対処法について比較的幅広く紹介されてました。 理論から学ぶデータベース実践入門はB+tree以外のインデックスについても説明がありましたね。 失敗から学ぶRDB本はわかりやすく端的に説明されてある印象をうけました。
大規模なデータベースを扱ったことがない経験がないので、どこまで通用するのかなー、実際やってみてどうなるんだろうか、というのは気になりましたね。
まあとりあえず基本的な常識（会話できるレベルの知識）は抑えておければいいかな。
