達人に学ぶDB設計

達人に学ぶDB設計 徹底指南書

達人に学ぶDB設計 徹底指南書

第7章 まで。うなずけるところ多いなぁ。 これ読んでからDB設計すべきだよね、と思った。

第5章 論理設計とパフォーマンス 正規化の欠点と非正規化

  • 正規化最大の欠点はSQLのパフォーマンス問題
  • 結合が高コスト
  • 非正規化なら結合を使わずにすみ、パフォーマンスもよい
  • 正規化と非正規化、どちらが正解なのか？
    • トレードオフ
      • パフォーマンスを取るか、データ整合性を取るか
      • 正規化の次数が低いほど検索SQLのパフォーマンスは良く、データ整合性は低い
  • 原則非正規化は許さない
  • 非正規化は最後の手段
  • 冗長性排除によって引き起こされる性能問題
    • サマリーデータの冗長性排除のよるパターン
    • 選択条件の冗長性排除によるパターン
  • サマリデータを冗長に保持すると正規形に違反するが、検索を高速化できる
  • 選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる
  • 正規化は可能な限り高次にすることが大原則で、トレードオフで考える
  • 非正規化のリスク
    1. 検索のパフォーマンスは向上させるが、更新のパフォーマンスを低下させる
    2. データのリアルタイム性（鮮度）を低下させる
    3. 後続の工程で設計変更すると、手戻りが大きい
  • 論理設計には物理設計の知識が必要

第6章 データベースとパフォーマンス

  • データベースのパフォーマンスを決める主な要因
    • ディスク I/O の分散（RAID）
    • SQL における結合（正規化）
    • インデックスと統計情報
  • インデックス設計
    • アプリケーションのコードに影響を与えない（アプリケーション透過的）
      • 存在を意識しなくても良い ・・・ 透過的
    • テーブルのデータに影響を与えない（データ透過的）
      • データに影響を与えない
    • それでいて性能改善の効果が大きい
  • B-tree インデックス
    • 平均点の高さ
      • 均一性、持続性、処理汎用性、非等値性、親ソート性
    • 平衡木
    • O(log n)
      • データ量が増えても性能劣化の度合いが緩やか
    • 非等値性
      • =, <, >, <=, >=, between
      • <>, != といった否定条件には効果がない
    • ソートはコストの高い演算
      • 大量データのソートが必要な場合、メモリに載りきらないため溢れることがある
  • B-tree インデックスはどの列に作れば良いか？
    1. 大規模なテーブルに対して作成する
    2. カーディなりティの高い列に作成する
    3. 選択条件、結合条件に使用されている列に作成する
  • データ量が少ない場合はインデックスの効果がない
  • カーディナリティ
    • 特定の列の値がどのくらいの種類の多さを持つか、を表す概念
    • 複合列に対してインデックスを作成する場合、カーディナリティは組み合わせで考える
    • カーディナリティが高い列ほどインデックスの効果が高いが、値が平均的に分散しているのがベスト
  • インデックスを利用できない例
    • インデックス列に演算を行っている
    • 索引列に対して SQL関数 を適用している
    • IS NULL 述語を使っている
    • 否定形を用いている
    • OR を用いている
      • IN を使うなどで回避
    • 後方、中間一致の LIKE述語 を用いている
    • 暗黙の型変換
  • その他注意事項
    • 主キーおよび一意制約の列には作成不要
    • 更新性能を劣化させる
    • 定期的なメンテナンスを行うことが望ましい
      • 定期的なインデックスの再構築
      • 断片化率や木の高さを指標
  • 統計情報
    • SQLをパーサが受け取り、オプティマイザが実行計画を決める
    • オプティマイザが実行計画を立てる際に必要になるのが統計情報
    • オプティマイザはカタログマネージャに統計情報の照会をかける
  • 統計情報収集のタイミング
    • データが大きく更新された後、なるべく早く
    • 原則夜間帯に実施する
      • リソース消費、長時間のため
  • 統計情報収集の対象（範囲）
    • 大きな更新のあったテーブル、およびインデックス
    • 一時テーブルについては別項
  • 統計情報の凍結
    • 現状のものから実行計画を変化させたくない場合
    • オプティマイザを完全には信じない悲観的な考えに基づいた選択
      • 実施するのはかなり大変

第7章 論理設計のバッドノウハウ

  • 戦略の失敗を戦術で取り返すことはできない
  • ダメ設計が生まれる理由は「何も考えていない」ことによるもの
  • 非スカラ値（第1正規形未満）
    • 配列型による非スカラ値
    • 配列型を利用する場合はミドルウェア、アプリケーションとの整合性を考慮
    • 原則配列型は使用せず、第1正規形を守る
    • スカラ値の基準は？
      • 意味的に分割できる限りなるべく分割して保持する
        • 氏名は姓名、Email は @ で分割
        • 結合はあとからでもできるが、分割はできないため
    • 情報は可能な限り分割して保存するのが良いが、意味を壊してはいけない
    • 分解した列と結合した列の両方を保存する「二正面作戦」はデータ容量のムダ、 冗長データ保持による更新コストの増加のため採用すべきではない
  • ダブルミーニング
    • テーブルの列は変数ではなく、一度意味を決めたら変更不可
  • 単一参照テーブル
  • テーブル分割
    • 水平分割
      • 分割する意味的な理由がない
      • 拡張性に乏しい
      • 他の代替手段がある
    • 垂直分割
      • 集約で代替可能
    • 集約
      • 列の絞り込み
        • データマートの作成
        • データ容量、データ同期の問題
      • サマリテーブル
        • 集約関数によってレコードを集約した状態で保持する
    • sharding
      • 論理的にも物理的にも異なるテーブルとして扱われる
      • シェアードナッシング型のアーキテクチャ
    • カラムベースデータベース
      • 対になるのはローベースデータベース
      • 選択する列がすくない場合、I/O量を削減できる
  • 不適切なキー
    • 可変長文字列は明らかに使ってはいけないキー型
      • 不燃性を備えていない
      • 固定長文字列との混同
      • キーには固定長文字列のコード列が望ましい
  • ダブルマスタ
    • ダブルマスタはシステム統廃合によって起きることが多い
  • バッドノウハウがバッドである理由
    • 可読性
    • 設計変更の難しさ
    • データ構造がコードを決めるのであて、その逆ではない