勉強日記

チラ裏

達人に学ぶDB設計 徹底指南書 ch6 データベースとパフォーマンス

RDB 勉強メモ

www.shoeisha.co.jp

データベースのパフォーマンスを決める要因

インデックス

  • 非常にポピュラーなSQLチューニング手段
  • インデックスってなに
  • (x, a)という形式の配列
    • x: キー値
    • a: 情報
      • 実データ
      • ポインタ
  • RDBMS内にテーブルとは独立に保持される

統計情報

  • SQL: ユーザはデータを「how」ではなく「what」で指定する
  • かつて: 「ルールベース」
    • エンジニアがある程度「how」を指定
  • 現在: 「コストベース」
  • 統計情報
    • 最短経路を知るための「地図」にあたる

インデックス設計

  • 特徴
    • アプリケーション透過的
      • インデックスを使用するかどうかはDBMSが自動的に判断
      • アプリケーションプログラムの変更がない
      • cf. 非正規化はアプリケーション大改修不可避
    • データ透過的
      • データが影響を受けることはない
      • 論理設計に撥ねることもない
    • 性能改善の効果が大きい
      • 性能がデータ量に対して線形よりも緩やかにしか劣化しない
      • とはいえ闇雲に作っても駄目

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

  • インデックスにもいろいろ
    • B-treeインデックス
    • ビットマップインデックス
    • ハッシュインデックス
  • オールラウンダーB-tree

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

B-treeインデックスはオール4の秀才

  • 均一性
    • 各キー値の間で検索速度にバラツキが少ない
  • 持続性
    • データ量の増加に対してパフォーマンス低下が少ない
  • 処理汎用性
    • 検索、挿入、更新、削除のいずれの処理そこそこ速い
  • 非等値性
    • 等号(=)に限らず、不等号(< > <= >=)を使ってもそこそこ速い
  • 親ソート性
    • 暗黙のソートが発生する処理を高速化できる
      • GROUP BY
      • ORDER BY
      • COUNT/MAX/MIN

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

  • 均一性
    • B-treeが平衡木だから
      • 挿入、更新、削除等が繰り返されると非平衡木になっていくことがある
  • 持続性
    • O(log n)
    • cf. フルスキャンはO(n)
  • 処理汎用性
    • 挿入、更新、削除もO(log n)
    • cf. ビットマップインデックスは検索は速いが更新が遅い
  • 非等値性
    • 検索木だから「右」とか「左」とかに絞れる
    • <>とかは速くならない
  • 親ソート性
    • 木構造自体がソート済のようなもの
    • ソートをスキップできる
      • 特にファイルソートのI/Oコストは甚大なので効果大

B-treeインデックスの設計方針

B-treeインデックスはどの列に作れば良いか？

  • 大規模テーブル
  • カーディナリティの高い列
  • WHERE句の選択条件、結合条件に使用されている列

B-treeインデックスとテーブルの規模

データ量が少ない場合はインデックスの効果はない

  • 前述の通り、B-treeインデックスの性能劣化のオーダーはCRUDすべてO(log n)
  • nが小さいとO(n)のフルスキャンのほうが早かったりする
  • どれくらい
    • 1万件が目安

B-treeインデックスとカーディナリティ

カーディナリティが高い列ほどインデックスの効果が高い。ただし、値が平均的に分散しているのがベスト

  • カーディナリティ
    • 特定の列の値がどれくらいの種類の多さをもつか
  • カーディナリティが低い例
    • 性別(カーディナリティ3)
      • 男性
      • 女性
      • 不詳
    • 1/3=33%程度にしか絞れない
  • カーディナリティが高い例
    • 営業日(カーディナリティ200くらい)
    • 1/200=0.5%にまで絞れる
  • 5%程度には絞れること

カーディナリティの注意点

  • 順番
    • 複合列(a,b,c)
    • a,b,cそれぞれカーディナリティ2,5,10だとする
    • 組み合わせると1/(2*5*10)=1%程度に絞りこめる
  • データの分布
    • 1-100の値をとるが、値の99%は100であるようなケース
    • 検索性能は安定しない

B-treeインデックスとSQL

  • 効かないケースに気をつける
    • 演算NG
    • NULLは可搬性失う
    • 否定形NG
    • ORはNG
      • INはOK
    • 前方一致以外のLIKE述語
    • 暗黙の型変換

B-treeインデックスに関するその他の注意事項

  • 自動で作成されるからわざわざ作成しなくていいケース
    • 主キー
    • 一意制約
  • B-treeインデックスは更新性能を劣化させる
    • 極力無駄に作らない
  • 定期的なメンテナンスをすることが望ましい
    • 木が崩れていく
    • 崩れ具合の指標値(断片化率、木の高さ等)と調査方法はDBMSによる
      • マニュアルで調べよ

統計情報

  • 統計情報
    • メタデータ
      • テーブルやインデックス等のデータに関するデータ
    • DBMSはこれを頼りにSQLのアクセスパスを決定する

オプティマイザと実行計画

  • パーサ
  • オプティマイザ
    • カタログマネージャに統計情報照会
    • SQLの実行計画を決定する
      • 最短(と思われる)手続きの手順
  • カタログマネージャ
    • 統計情報返却

統計情報の設計指針

統計情報収集のタイミング

データが大きく更新された後、なるべく早く

統計情報収集は原則、夜間帯に実施する

  • 更新: INSERT/UPDATE/DELETE
    • レコード件数増減
    • データ分布・偏りの変化
  • 大規模更新の後は「地図が古い」状態になる
  • 地図の更新が必要
    • リソース大量消費
    • 長時間かかる
  • 日中はオンライン処理の性能を劣化させるので避けよ

統計情報収集の対象(範囲)

  • 統計情報収集は高負荷
  • なので不要なテーブルは含めない
    • CRUD表などで整理する
  • 一時テーブルに注意
    • INSERT後に統計情報収集しないといけない

統計情報の凍結について

統計情報の凍結は、オプティマイザを信じない悲観的設計。
しかし現実に実施するのはかなり大変

  • データ量の増加に伴って実行計画は変化する
  • これによりパフォーマンスが劣化することは珍しくない
  • 実行計画を現状のものから実行計画を変化させたくない場合に統計情報を凍結する
  • システムのサービス終了時のデータを想定した状態での統計情報を用意
    • 「サービス終了時のデータ」を想定して用意するのは大変なので断念することも多い