Zenn
Closed9

『達人に学ぶDB設計徹底指南書』を読んで学んだこと

DB
chima91chima91

データベースを制する者はシステムを制す

POAとDOA

  • POA（Process Oriented Approach）では、プロセス、つまり業務処理は短期間で大きく変わりがちで、プロセス単位でデータ設計を行うことになるため、複数のプロセスで同じデータを持つという冗長性が生じるなど、不都合が多い。
  • DOA（Data Oriented Approach）では、データの意味や形式が先に決まっていれば、複数のプログラムで共用することも容易で、業務要件の仕様変更にも柔軟に対応できるメリットが得られるため、システム開発ではプログラム設計に先立ってデータ設計が優先される。

3層スキーマモデル

  • 外部スキーマ（外部モデル）= ビューの世界 = ユーザーから見たDB
  • 概念スキーマ（論理データモデル）= テーブルの世界 = 開発者から見たDB
  • 内部スキーマ（物理データモデル）= ファイルの世界 = DBMSから見たDB

Web3層モデル

  • プレゼンテーション層 = UIを定義 = Webサーバ ≒ 上述の外部スキーマ
  • アプリケーション層 = アプリケーションを実行 = APサーバ
  • データ層 = データを一元管理 = DBサーバ = 上述の内部スキーマ
chima91chima91

論理設計と物理設計

論理設計のステップ

  1. エンティティの抽出
    エンティティはデータの集合体を指す言葉で、具体的には「顧客」や「社員」「店舗」「車」といった物理的な実体を伴ったものもあれば、「税」や「会社」「注文履歴」のように物理的実体を伴わない単なる概念としてしか存在しないものも含まれる。RDBでは、現実世界のエンティティをテーブルという物理的単位で格納していくことになる。
  2. エンティティの定義
  3. 正規化
  4. ER図の作成

物理設計のステップ

  1. テーブル定義
  2. インデックス定義
  3. ハードウェアのサイジング
    DBにおいては、データの整合性とパフォーマンスの間に強いトレードオフが存在する。
  4. ストレージの冗長構成決定
    システムにおいて最も性能的にボトルネックとなるディスクI/Oを分散することで、パフォーマンス向上を図る。
  5. ファイルの物理配置決定
  • データファイル
    テーブルのデータを格納するファイルで、アプリケーションがSQLを通じて参照および更新を行うファイルでもある。ただし、アプリケーションから見えるのはあくまでテーブルという論理的単位であり、ファイルが直接見えることはない。
  • インデックスファイル
    テーブルに作成されたインデックスが格納されるファイル。
  • システムファイル
    DBMSの内部管理用に使われるデータを格納するファイル。
  • 一時ファイル
    SQLのサブクエリを展開したデータやGROUP BYやDISTINCTを利用した時のソートデータなどを格納するファイル。
  • ログファイル
    テーブルのデータに対する変更を受け付け、一旦ここに変更をためた後、一括してデータファイルに変更を反映するためのファイル。差分バックアップはこのログファイル（トランザクションログ）をバックアップすることで実現する。

最近は上記5種類のファイルのほか、ストレージの大容量化に伴い、画像データなどのバイナリファイルをデータベースに格納することも増えている。

バックアップ設計

  1. リストア
    フルバックアップファイルをDBに戻す作業
  2. リカバリ
    そのファイルに対して差分（または増分）バックアップしていたログファイル（トランザクションログ）を適用して変更分を反映する作業
  3. ロールフォワード
    DBサーバーに残っているログファイル（トランザクションログ）を適用する作業
chima91chima91

論理設計と正規化

正規化を進めていくほどデータ整合性は高まるが、検索性能が劣化する。
正規化とは、テーブルのすべての列が関数従属性を満たすように整理していくこと。
第5正規形に至るまでの正規化は全てテーブル分割であり、常に1対多の関連を生むようにテーブルを分割する必要がある。正規化は情報を完全に保存したままテーブルを分割する無損失分解である。
正規形から非正規形に戻す操作は、常に結合SQLである。正規化の主な欠点は、テーブルの数が増えることで、結合を多用することになり、パフォーマンスが悪化することである。

第1正規形

セルに複数の値を許せば、主キーが各列の値を一意に決定できないため、テーブルはスカラ値だけで構成する。

第2正規形

テーブル内で部分関数従属を解消し、完全関数従属のみのテーブルを作る。それぞれに異なるレベルの実体（エンティティ）を、きちんとテーブルとしても分離する作業。

第3正規形

テーブル内で推移的関数従属を解消する。

ボイス-コッド正規形

非キーからキーへの関数従属を解消する。

第4正規形

第4正規形および第5正規形は、関連エンティティ（主キーだけで構成されるのが特徴）に対して発生する。第3正規形までの具体的な実体を伴ったエンティティの正規化では発生しない。
キーと集合との対応を多値従属性と呼び、→→という記号を表す。
独立な多値従属性が複数存在するテーブルを分割することで第４正規形となる。

第5正規形

関連と関連エンティティを一対一で対応させる。

chima91chima91

ER図

相手のエンティティと対応するレコード数をカーディナリティと呼ぶ。

IE（Information Engineering）表記法

多（2以上）のカーディナリティを表す記号から鳥の足とも呼ばれている。

IDEF1X（Integration DEFinition, 1XはIDEFファミリの中の1つを表す）表記法

黒丸が鳥の足にほぼ近い意味を持つ。

  • 独立エンティティ：主キーに外部キーを含んでいない = 角の尖った四角でエンティティを表現

  • 従属（依存）エンティティ：主キーに外部キーを含んでいる = 角の丸い四角でエンティティを表現

  • 非依存リレーションシップ：外部キーにNULLを許す = 点線で関連を表現

  • 依存リレーションシップ：外部キーにNULLを許さない = 実線で関連を表現

chima91chima91

論理設計とパフォーマンス

正規化が、ある意味で、情報を複数のテーブルに分散させる行為であるため、正規化されたテーブルは、単独のテーブルでは必ずしもユーザが欲する全ての情報をカバーできない。が、SQLにおける結合は非常にコストの高い操作であるため、非正規化による解決も視野に入れる必要がある。

正規化とSQL（更新）

更新処理に関しては、正規化のほうに軍配が上がる。

非正規化とパフォーマンス

サマリデータ（集計データ）を冗長に保持すると正規形に違反するが、検索を高速化できる。
選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる。

冗長性とパフォーマンスのトレードオフ

  • 非正規化は、検索のパフォーマンスは向上させるが、更新のパフォーマンスを低下させる。
  • 非正規化は、データのリアルタイム性（鮮度）を低下させる。
  • 非正規化は、後続の工程で設計変更すると、手戻りが大きい。
chima91chima91

データベースとパフォーマンス

インデックス

インデックスとは、（x, α）という形式の配列で、xはキー値、αはそれに結び付く情報（実データorそれへのポインタ）を意味する。実際には、DBにおいて、αはデータへのポインタであることが多い。

  • アプリケーション透過的：アプリケーションコードに影響を与えない。同じチューニング手段でも、非正規化はアプリケーションを大きく改修しなければならない。
  • データ透過的：テーブルのデータに影響を与えない。インデックスを作成する際に、論理設計を修正するような手戻りを心配する必要はない。

注意点として、インデックスが作成されている対象の列値が変更されると、インデックス内に保持している値も変更しなければならないため、インデックスを作成すればするほど、当該テーブルに対する更新性能が劣化していく。

B-tree

代表的なインデックスである、B-treeは、木構造でデータを保持しており、**最下層のリーフ（葉）と呼ばれるノードだけが、実データに対するポインタを保持している。**最上位のルートノードから順にノードを辿って、リーフノードから実データを見つけにいく。

  • 均一性：各キー値の間で検索速度にバラツキが少ない。
    • B-treeは平衡木（Balanced-tree）であるため、どんなキー値を使っても、常にリーフまでの距離が一定になるため、探索を同じ計算量で行える。
  • 持続性：データ量の増加に対してパフォーマンス低下が少ない。
    • B-treeの木の高さは平均的には3~4程度で、これはデータ量が膨大に増えても変わらない特性であるため、テーブルへの挿入・更新・削除などが繰り返されることによるインデックスの性能低下が緩やかである。
  • 処理汎用性：検索・挿入・更新・削除のいずれの処理もそこそこ速い。
    • ビットマップインデックスだと検索性能はB-treeを凌駕することもあるが、更新に多大な時間を要する。その点、B-treeは挿入・更新・削除のコストが検索と同じくデータ量nに対してO(log n)である。
  • 非等値性：統合に限らず、不統合を使ってもそこそこ速い。
    • B-treeは構築される時、必ずキー値をソートするため、たとえリーフノードを一つに絞れないとしても、特定のノードよりも左とか右のノードだけに探索範囲を絞ることが可能になる。そのため、等号による検索のみならず、不等号やBETWEENといった範囲探索の条件に対しても高速化を可能としている。一方、否定条件による検索には、特定のノード以外の全てのノードが該当するため、B-treeによる絞り込みが効かない。
  • 親ソート性：GROUP BY, ORDER BY, COUNT, MAX, MINなど、ソートが必要な処理を高速化できる。
    • SQLは一切の手続きを記述しないため、SELECT文やUPDATE文の中でも明示的にソートを記述することがない。しかし、集約関数やORDER BY句、集合演算、OLAP関数を記述したときには、暗黙にDBMS内部でソートが行われている。ソートはDBMS内部で専用のメモリ領域が割り当てられており、その内部に一時的にデータを保持して実施されるが、大量データのソートが必要な場合、メモリに載りきらないために溢れてしまうことがある。その場合、DBMSは一時的にディスクへデータを書き出すため、I/Oコストが非常に大きなものになる。
    • B-treeインデックスは、構築時にキー値をソートして保持するため、B-treeインデックスが存在する列をORDER BY句のキーとして指定した場合、ソート処理をスキップすることが可能になる。

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

  • 大規模なテーブルに対して作成する。
    • データ量が少ない場合、インデックスの効果はない。
  • カーディナリティの高い列に作成する。
    • 特定のキー値を指定した時に、全体のレコード数の5%程度に絞り込めるだけのカーディナリティがある列に作成する。
  • SQL文でWHERE句の選択条件または結合条件に使用されている列に作成する。
    • B-treeは一般的にNULLについてはデータの値とは見做さず保持していない。
    • ORを用いた場合はインデックスが利用されないため、INで書き換えることで回避できる。
    • DBMSは主キー制約や一意制約を作成する際、内部的にはB-treeインデックスを作成している。B-treeがデータをソートして保持するため、重複値チェックにこれを利用している。

統計情報

統計情報とは、SQLの最適なアクセスパスを見つけるために必要な地図情報のこと。

  • ルールベース：かつては、ある程度エンジニアがデータアクセスの経路を選択する方法が主流であった。
  • コードベース：最近では、DBMSに経路選択を一任するアーキテクチャが主流になっている。

DBMSがSQL文を受け取ってテーブルにアクセスするまでの流れ

  1. DBMS内のパーサ（parser）と呼ばれるモジュールが、受け取ったSQLの構文をチェックする。
  2. チェックが済むと、SQLはオプティマイザ（optimizer）と呼ばれるモジュールへ送られる。オプティマイザは`カタログマネージャ'と呼ばれるモジュールに統計情報の照会をかける。
  3. オプティマイザは統計情報を受け取ると、たくさんの経路の中から最短（と思われる）経路を選択し、SQLを手続きに変換する。ここで得られた手続きの手順が、実行計画（実行プラン, アクセスプラン）であり、ようやく実データであるテーブルへとアクセスを行う。

統計情報の設計指針

一般的にエンジニアがSQLの実行計画立案に直接的に関与することはなく、統計情報を通した間接的な関わり方になる。

  • 統計情報収集のタイミング
    • データが大きく更新された後、なるべく早く、原則、夜間帯に実施する。
  • 統計情報収集の対象（範囲）
    • 大きな更新のあったテーブル（およびインデックス）
chima91chima91

論理設計のバッドノウハウ

非スカラ値（第1正規形未満）

SQLには世界共通の標準規格があり、数年に一度改訂が行われている。その1999年の改訂で標準に盛り込まれた機能の一つが配列型である。これはつまり、非スカラ値を持つテーブルを作れるようになったことを意味する。だが、DB側だけが配列型に対応したとしても、DBと接続するアプリケーションやミドルウェアも対応しなければ意味がないため、まだ実用化へのハードルは高い。スカラ値の基準として、分割したものを後で結合するのは簡単だが、結合された状態のものを後で分割するのは比較的難しいため、情報は可能な限り分割して保存するのが良い。

ダブルミーニング

RDBにおけるテーブルは、意味的には現実の世界にあるいろいろな実体（エンティティ）の写像、つまり物を写し取ったものであるため、変数よりもずっと静的で固定的な存在であるため、テーブルや列の意味は一度決めたら容易に変更してはならない。

単一参照テーブル

ER図がスッキリするとは言っても、ERモデルとしては正確さを欠いており、かえってER図の可読性を下げることになるため、テーブルにポリモーフィズムはいらない。

テーブル分割

  • 水平分割
    • 水平分割（レコード単位でテーブルを分割）の代替として、多くのDBMSがパーティションという機能を持っており、これを用いることで、テーブルを分割することなくパーティションキーを軸として物理的に格納領域を分離することが可能となる。一般的には、パーティションはインデックスよりもカーディナリティが小さく、かつ値の変更があまり起きない列をキーにして利用する。
    • sharding
      • パーティションによって分割されたテーブルは、論理的にはあくまで単一のテーブルとして扱うことができるのに対し、shardingによって分割されたテーブルは、論理的にも物理的にも異なるテーブルとして扱われる。
  • 垂直分割
    • 垂直分割（列単位でテーブルを分割）の代替として、集約がある、
      • 列の絞り込み
        • 頻繁に参照される列だけを持ったテーブルを新たに作成する。オリジナルのテーブルは残すため、テーブル分割ではない。このようなオリジナルのテーブルと比較して小規模なテーブルをデータマート、あるいは単にマートと呼ぶ。気をつけるべきはマートとのデータ同期であり、多くの場合、マートの更新は1日1回〜数回程度の頻度で一括更新（バッチ更新）されている。
      • サマリテーブル
        • サマリテーブルとは、集約関数によってレコードを集約した状態で保持したもの。これも、データ同期に注意する必要がある。
    • column-base database
      • row-base databaseだと、一つのSQLで利用する列がたとえ1列だけであってもレコード全体を読み出す必要があるのに対して、カラムベースだと、その特定の列だけを読み出すことで、I/O量を削減できる。

不適切なキー

可変長文字列（VARCHAR）は不変性がないためキーには不向き。
キーには固定長文字列（CHAR）の「コード」列が望ましい。

ダブルマスタ（著者の造語）

システム統廃合で、同じような役割を持っているテーブルが複数存在してしまうのを避けるために、データクレンジングを行なってエンティティの統廃合をする必要がある。

chima91chima91

論理設計のグレーノウハウ

バッドノウハウ以外にも、違法スレスレのグレーゾーンの設計がある。

代理キー ~ 主キーが役に立たないとき

代理キー

代理キーを使えば、入力データの仕様と独立してDB側で主キーを設定できるため、面倒な業務上の仕様調整を省略できる。また、主キーが複合キーの場合に、SQLのWHERE句における記述方法をシンプルにできる。
しかし、代理キーはそもそも論理的には不要なキーのため、論理モデルを分かりにくくしてしまう。

自然キー

連番という無味乾燥な代理キーと違って、自然キーは業務的に意味を持つ列である。タイムスタンプやインターバルといった時間の情報をもった列を追加することで、代理キーの使用を避けることができる。

オートナンバリング

  • DBの機能を利用する
    • 一意な連番を払い出すシーケンスオブジェクトを利用する。開始値、最大値、増分、サイクリックにするかどうかといった様々な条件をオプションで指定できる。
    • 一意な連番を払い出すデータ型であるID列を利用する。シーケンスオブジェクトに比べると、指定できるオプションが少なく、また、DBMSごとに実装が統一されておらず移植性が低い。
  • アプリケーション側で実装する
  • 1行1列の整数型のデータを持つ採番テーブルを用意し、他のテーブルにデータを登録するたびにインクリメントしていく手法。ただし排他制御の問題がある。シーケンスオブジェクトでは最初からロックメカニズムが実装されている。

ロックが起きることで、同時アクセスが多数集中した時に、この連番の払い出し処理がボトルネックとなり、性能遅延が発生する可能性がある。ロックは連番の一意性を保証するために必要なロジックであるため、原理的にこの現象を防ぐことはできず、オートナンバリングは便利に見えて少なからぬ危険を孕む機能であると言える。

列持ちテーブル

配列をテーブルの列を複数使って模したのが列持ちテーブルである。

  • 利点：シンプルで、配列を表現しているのが分かりやすい。また、入出力のフォーマットと合わせやすい。
  • 欠点：列の増減が難しい。また、無用のNULLを使うことになる。

行持ちテーブルは列持ちテーブルと比べて欠点が少ない。行持ちと列持ちの変換は保証されている上にSQL一発で可能。

アドホックな集計キー

アドホックな（場当たり的な）キーは、コード体系が短いスパンで変わったり、別のコード体系が必要になったりする。解決策としては、

  • アドホックなキーを別テーブルに分離する
  • ビューを使う
  • GROUP BY句の中でアドホックキーを作る

多段ビュー

ビューは、SELECT文を保存して一種のテーブルとして扱うことができるため、正規化されたテーブルの結合など、複雑なSELECT文をビューにしておくことで、上位レイヤーのアプリケーションのプログラミングを簡潔に行える利点がある。テーブルを隠蔽してユーザーに必要なデータだけを必要な形で見せられることから、外部スキーマに属する。
ただし、ビューは実データを保持しないため、結局のところ、ビュー定義のSELECT文を実行してオリジナルのテーブル（基底テーブル）にアクセスしている。そのため、ビューへのSQL文によるアクセスにおいては、常に背後に存在する基底テーブルの存在を意識しておく必要がある。

データクレンジングの重要性

データクレンジングはDB設計に先立って行う必要がある。
人名や企業名、住所、電話番号などの表記揺れを解消して統一する名寄せを行う。名寄せが発生する根本的な原因は、標準的なフォーマットを決めず、フリーハンドでの入力を許していることである。

chima91chima91

一歩進んだ論理設計

RDBのアキレス腱とも言える木構造を表現するモデルをまとめる。

隣接リストモデル

このモデルは、SQLで木構造を表現する最も古く、かつポピュラーな方法である。
ノードのレコードに親ノードの情報（ポインタ）を保持するもの。ベンダー独自の拡張を使わないと、更新や検索のクエリが極めて複雑になり、パフォーマンスも悪いという欠点がある。

入れ子集合モデル

このモデルは、ノードを点ではなく、面積をもった円として捉え、ノード間の階層関係を円の包含関係によって表す。

  • 利点：木構造を操作するための検索SQL文が隣接リストモデルに比べて非常にシンプルになることである。
  • 欠点：更新対象と無関係な円の座標も連動して更新しなければならない。

入れ子区間モデル

このモデルは、円の左端/右端の座標として取れる範囲を整数から実数まで広げた入れ子集合モデルの拡張版である。挿入対象としたい区間の左端座標をpleft、右端座標をprightとすると、

pleft * 3 < pleft * 2 + pright < pleft + pright * 2 < pright * 3

すなわち、

pleft < (pleft * 2 + pright) / 3 < (pleft + pright * 2) / 3 < pright

が成り立つため、

  • 追加ノードの左端座標 = (pleft * 2 + pright) / 3
  • 追加ノードの右端座標 = (pleft + pright * 2) / 3

というように追加ノードの座標の計算ができる。

経路列挙モデル

このモデルは、ノードをディレクトリと見做し、各ノードまでのpathを記述するモデルである。

  • 利点
    • ノード自身のレコードに親子関係が含まれているため、他のモデルに比べて経路探索のSQL文が圧倒的に簡単になる。
    • pathもテーブル上で一意になるため、ユニークインデックスによる高速検索が可能。
  • 欠点
    • 経路に主キーを使うと、経路の文字列が非常に長大になる危険がある。
    • pathに番号を使うと、ノードの削除・追加などの更新が複雑になる。
このスクラップは2022/09/14にクローズされました