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

はじめに

1章　データベースを制するものはシステムを制す

データベースの代表的なモデル

• リレーショナルデータベース（RDB)
  関係データベースとも呼ばれる。データを二次元表の形式で管理するため人間が理解しやすい

• オブジェクト指向データベース(OODB)
  データをオブジェクトとして管理

• XMLデータベース
  XML形式のデータを扱うデータベース。階層構造のデータの扱いが得意

• キー・バリュー型ストア
  データをキーとバリューの組み合わせだけの単純なデータ型で表現するデータベース

• 階層型データベース
  データを階層構造（木構造）で表現するデータベース

リレーショナルデータベースで扱う重要な設計技法の「正規化」は他のモデルのデータベースでは通常行わない。などデータベース毎に違いがある

主なDBMS

• Oracle Databese
  オラクル社の製品
• SQL Server
  マイクロソフト社の製品
• DB2
  IBM社の製品
• PostgreSQL
  米バークレイ大学で開発されたオープンソースのDBMS
• MySQL
  オラクル社の所有する、オープンソースのDBMS

システム開発の工程

1.要検定義
2.設計
3.開発（実装）
4.テスト

要件定義...システムが満たすべき機能やサービスの水準、要件を決める

設計...定義された要件を満たすために必要なシステムを作るための設計を行う

開発(実装)...設計書に従ってシステムを実際に作る

テスト...実装によって組み上がったシステムが、本当に実用に耐える品質か試験する

開発方法

• ウォーターフォールモデル
  要件定義、設計、開発、テストと一つずつ工程を踏んで、段階的にシステムを作っていく

• プロトタイピングモデル
  小さな試験品を作って顧客やユーザーに見せてフィードバックをもらい、取り入れて改良品を出す循環的な開発方法

プロトタイピングを応用したモデルに「アジャイル」がある

データベース設計が重要な理由

1.システムにおける大半のデータはデータベースに保存されるため

2.データ設計がシステムの品質を最も大きく左右する

近年のソフトウェア開発では「データ中心アプローチ（DOA)」という考えが主流

データ中心アプローチ（DOA)...システムを作る際、プログラムよりも前にデータ設計から始める方法論

プロセス中心アプローチ（POA)...「プロセス（処理）」、プログラム中心の開発方法

POAでは処理毎にデータが分散して管理が大変、DOAならばデータを一元管理できる

3層スキーマ

1.外部スキーマ（外部モデル）...ビューの世界
2.概念スキーマ（論理データモデル）...テーブルの世界
3.内部スキーマ(物理データモデル）...ファイルの世界

• 外部スキーマ
  システムの利用者であるユーザーから見て、データベースがどのような機能とインターフェースを持っているか定義する、「ユーザーから見たデータベース」

• 概念スキーマ
  データベースに保持するデータの要素および、データ同士の関係を記述するスキーマ、「開発者から見たデータベース」である。このスキーマの設計を「論理設計」と呼ぶ。

• 内部スキーマ
  概念スキーマで定義された論理データモデルを、具体的にどのようにDBMS内部に格納するかを定義するスキーマ。「DBMSから見たデータベース」

2章 論理設計と物理設計

概念スキーマを定義する設計を、論理設計

論理設計のステップ

1.エンティティの抽出
2.エンティティの定義
3.正規化
4.ER図の作成

エンティティの抽出...「エンティティ」は「実体」と訳すが、物理的実体を伴う必要はない。最終的に「テーブル」という物理単位で格納していく、要件定義に近い

エンティティの定義...エンティティを抽出した後は、各エンティティがどのようなデータを保持するか決める必要があ理、データを「属性(attribute)」という形で保持する。

正規化...エンティティ（テーブル）について、システムでの利用がスムーズに行えるよう整理する作業、フォーマットを整理する。

ER図の作成...ER図、エンティティ同士の関係を表現する

内部スキーマと物理設計

物理設計のステップ

1.テーブル定義
2.インデックス定義
3.ハードウェアのサイジング
4.ストレージの冗長構成決定
5.ファイルの物理配置決定

テーブル設計

論理設計で定義された概念スキーマをもとに、DBMS内部に格納するための「テーブル」の単位に変換する作業

インデックス定義

インデックスはパフォーマンス向上のため。本の索引のようなもの

ハードウェアのサイジング

システム開発では２種類の意味で使う

1.システムで利用するデータサイズを見積り、それに十分な容量の記憶装置（ストレージ）を選定する

2.システムが十分な性能を発揮できるだけのスペックのCPUやメモリを持ったサーバを選定する

• クラウドによって提供されているハードウェアの上にシステムを構築していくことを、HaaS（Hardware as a service）またはIaaS(Infrastructure as a service)と呼び

• DBMSなど上位層のレイヤーまでクラウドで提供するのをPaaS(Platform as a service)またはSaaS(Software as a Service)と呼ぶ

ストレージの冗長構成

可能な限り高い耐障害性を持つようにシステムを構築する技術、「RAID」
RAID...Redundant Array of Independent Disks（独立したディスクの冗長配列）複数のディスクを束ねて仮想的に一つのストレージとする技術。基本的な考え方は、複数のディスクに同じデータを書き込んで冗長化することで、そのうちの一本が壊れても残りのディスクが生きていればデータを保全できるようにする、というもの。
「冗長」とは「同じものを複数の場所にもつ」という意味

• RAID0
  別名ストライピング
  データを異なるディスクに分散して保持すること。ディスクが一本でも故障したらデータが失われるため冗長化は全くない
  ただディスク別にデータの保存するものを分けているだけ

• RAID1
  別名ミラーリング
  二本のディスクに全く同じデータを持つ。一本だけの場合に比べて冗長性は２倍

• RAID5
  パリティ分散と呼ばれる方式
  最低三本で構成し、データとともに「パリティ」と呼ばれる誤り訂正符号を分散して格納する。
  ディスクが壊れてもパリティから実データを復元可能。
  一本までならどのディスクが壊れてもデータ保全可能
  データを分散できるためI/O性能（読み出し）の向上も期待できる。
  パリティの計算を行うため書き込み性能は高くないが、ディスクの本数が増えるほど読み出し性能は向上する

• RAID10
  別名「RAID1+0」とも呼ぶ
  RAID1とRAID0を合わせたもの。ディスクが裁定４本なのでコストが高いが高信頼性と高速性を両立させる。

ファイルの物理配置

データベースに格納されるファイルは、用途別に以下の５種類に大別できる。

1.データファイル
2.インデックスファイル
3.システムファイル
4.一時ファイル
5.ログファイル

開発者が意識するのは1と2だけ
残りの３つはいずれも、DBMSの内部処理で使用asれるファイルのためDBAと呼ばれるデータベース管理者以外は意識しない

• データファイル
  ユーザーがデータベースに格納するデータを保持するためのファイル。SQLを通じて参照および更新を行うファイル

• インデックスファイル
  テーブルに作成されたインデックスが格納されるファイル

• システムファイル
  DBMSの内部管理用に使われるデータを格納する

• 一時ファイル
  名前の通りDBMS内部での一時的なデータを格納するため使う

• ログファイル
  データに対する変更を溜め込み、一括してデータファイルに変更を反映させるため
  「トランザクションログ」や「バイナリログ」などとも呼ぶ

バックアップ設計

バックアップの基本分類

• フルバックアップ（完全バックアップ）
  バックアップをとった地点までのすべてのデータをバックアップとる
  バックアップの時間がかかる、ハードウェアリソースへの負荷が高い、サービス停止が必要

• 差分バックアップ
  ログファイルを使用してバックアップをする。トランザクションログにはデータに対するあらゆる変更操作の履歴が残っているのでこれをバックアップを取ると再現可能
  月曜だけフルバックアップし、それ以降の曜日は月曜日との差分みたいなやり方できる
  利点はバックアップのデータ量が減る
  欠点はリカバリの時に、フルバックアップのファイルだけでなく差分ログの適用も必要で、リカバリの手順と時間が増える

• 増幅バックアップ
  差分と考えは同じだが効率化したもの。常に必要なログしかバックアップしない
  利点は、バックアップデータが３つのうち最小になる、時間も最短、容量も最小
  欠点は、リカバリ手順が最も複雑になる、復旧に必要なファイルも増える

バックアップの選ぶ内容
1.バックアップしない
2.フルバックアップのみ
3.フルバックアップ+差分バックアップ
4.フルバックアップ+増分バックアップ

3か4が一般的

リカバリ設計

バックアップファイルを戻す作業を、「リストア」
そのファイルに対して、トランザクションログを適用して変更分を反映させる作業を「リカバリ」

リストアおよびリカバリの手順は以下の三段階になる
1.フルバックアップのファイルをデータベースに戻す（リストア）
2.差分（または増分）バックアップしていたトランザクションログを適用する（リカバリ）
3.データベースサーバーに残っているトランザクションログを適用する（ロールフォワード）

3章 論理設計と正規化

テーブルとは、共通点を持ったレコードの集合

テーブルにおいて、横と縦のデータの組みを「行」と「列」あるいは「レコード」と「カラム」という呼び方をする

「主キー」または「プライマリキー」はテーブルに置いて必ず一つ存在しなければならず、かつ一つしか存在できない。その値を指定すれば、必ず１行のレコードを特定できるような列の組み合わせのこと

主キーがテーブルに必ず存在し、常に一意なのでテーブルには重複行は存在できない。

主キーが作れない場合は、複数列を組み合わせて作るキーの複合キーを使う

候補キーとスーパーキーがある
候補キー...主キーとして利用可能なキーが複数存在した場合、それら「候補」となるキー
スーパーキー...主キーに、非キー列を付加した場合のキーの組み合わせ
外部キー...二つのテーブルの間の列同士で設定するもの。テーブルに対して制約を課し、その制約を「参照整合性制約」という

カスケード...親がいない子のデータを合わせて変更するか、更新SQLをエラーにするか選択する

制約

• NOT NULL制約
  リレーショナルデータベースでは、NULLの値を「空欄」にする
  NULLを許容しないのをNOT NULL制約で、制約されている値をNULLに更新しようとするとエラーになる。

• 一意制約
  列の組について一意性を求める制約

• CHECK制約
  列の取りうる値の範囲を制限する制約

テーブルと列の名前

ルール

名前に使える文字集合

• 半角のアルファベット
• 半角の数字
• アンダーバー(_)

最初はアルファベット
名前は重複してはならない

正規化

第1正規形

一つのセルの中には一つの値しか含まない

この値のことを、「スカラ値」と呼ぶ

理由：セルに複数の値を許せば、主キーが各列の値を一意に決定できないから

第2正規形

主キーの一部の列に対して従属する列がある場合、この関係を部分関数従属と呼ぶ
主キーを構成するすべての列に従属性がある場合を、完全関数従属と呼ぶ

部分関数従属の関係にあるキー列と従属列だけ独立のテーブルにすればいい

第二正規化は加虐的な操作で、正規化によって失われる情報がない。このように情報を完全に保存したままテーブルを分割する操作のことを無損失分解と呼ぶ。

第3正規形

テーブル内部に存在する段階的な従属関係のことを、推移的関数従属と呼ぶ。

ボイス-コッド正規形

分解時に可逆的な分解が可能なこと、非可逆的な分解、結合になってはいけない

第４正規形

多値従属性...関数従属性の値が複数ある場合

独立な多値従属性が複数存在するテーブルを分解すればいい

第５正規形

正規化についてまとめ

• 正規化とは更新時の不都合/不整合を排除する
• 正規化は従属性を見抜くことで可能になる
• 正規形はいつでも非正規形に戻せる

正規化は常にするべきか

• 第３正規形までは、原則として行う
• 関連エンティティが存在する場合は関連とエンティティが1対１に対応するように注意する

4章 ER図

テーブル同士がお互いにどういう関係にあるのか、ということを明示するために作る図をER図と呼ぶ。

テーブル同士の間では、一般的に３パターンある

• パターン1 1対1
• パターン2 1対多
• パターン3 多対多

多対多は中間テーブル使って実装すればいい（関連実体）

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

正規化するとSQLのパフォーマンスが落ちる、しかし絶対に正規化をするべき

冗長性とパフォーマンスのトレードオフ
非正規かが更新不整合のリスクを増やすリスク

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

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

インデックスはSQLのパフォーマンス改善のために非常にポピュラー

• アプリケーションのコードに影響を与えない
  インデックスを使うかはDBMSが自動的に判断するので、単純にデータベース側にインデックスを作成すればいいだけなのでアプリケーションプログラムの変更が必要ない

• テーブルのデータに影響を与えない
  インデックスを作成することでテーブルに格納されているデータの中身が影響を受けることはない

• それでいて性能改善の効果が大きい
  インデックスの性能が、データ量に対して線形よりも緩くしか劣化しないため

頻繁に使用するインデックス B-treeインデックス
B-treeの長所は平均点の高さ
平衡木（へいこうぎ）であるため、どんなキー値を使っても、常にリーフまでの距離が一定になるため、探索を同じ計算量で行える。
持続性、処理汎用性はデータ量nに対してO(logn)のため早い

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

• 大規模なテーブルに対して作成する
  レコード数が1万件以下の場合はほぼ効果がないが、それ以上なら効果はある

• カーディナリティの高い列に作成する
  特定の列の値が、どのくらいの種類の多さを持つか表す概念
  特定の値にデータが集中している列には向かない

• SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する

B-treeインデックスを使えないSQL
1.インデックス列に演算を行なっている
2.索引列に対してSQL関数を適用している
3.IS NULL 述語を使っている
4.否定形を用いている
5.ORを用いている
6.後方一致、または中間一致のLIKE述語を用いている
7.暗黙の型変換を行なっている

注意点
主キーおよび一意制約の列に作成不要
B-treeインデックスは更新性能を劣化させる
定期的なメンテナンスを行うことが望ましい

7章 アンチパターン

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

配列もあんまり使わないようにして、第１正規形を遵守する方がいい

ダブルミーニング

同一の列が二つの意味を持つ状態

単一参照テーブル

利点

• マスタテーブルの数が減るため、ER図やスキーマがシンプルになる
• コード検索のSQLを共通化できる

欠点

• 「コードタイプ、値、内容」の各列とも、必要とされる列長はコード体系によって異なるため、余裕を見て大きめな可変超文字列型で宣言する必要がある
• 一つのテーブルにレコードを集約するため、コード体系の種類と数の多さによっては、レコード数が多くなり、検索のパフォーマンスを悪化させる
• コード検索のSQL内でコードタイプやコード値を間違えて指定してもエラーになることがないため、バグに気付きにくい
• ER図がスッキリすると言っても、ERモデルとしては正確さを欠いており、かえってER図の可読性を下げることになる

テーブル分割

大きく分けて2種類ある

水平分割

原則リレーショナルデータベースでは原則禁止とされる。

欠点：分割する意味的な理由がない、拡張性に乏しい、他の代替手段

垂直分割

集約

• 列の絞り込み
• サマリテーブル

不適切なキー

ダブルマスタ

同じ役割を果たすはずのマスタテーブルが二つ存在するケース
システム統廃合で起きることが多い

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

代理キー 主キーが役に立たない時

1.そもそも入力データに主キーにできるような一意キーが存在しない

2.一意キーはあるが、サイクリックに使いまわされる
レコード削除したあと、削除したレコードの一意キーを再利用する

3.一意キーはあるが、途中で指す対象が変わる
レコードを統廃合で一意キーの対象が変更される

上記のような問題を解決する手段が代理キー

自然キーによる解決

1は自然キーに主キーが存在しないケースなので、アプリケーションでデータが一意になるよう整形する対策のみ
2,3は基本的に自然キーによる解決が可能で、タイムスタンプかインターバルの２種類ある

オートナンバリングの是非

1.重複値が生じないこと（一意性の保証）
2.歯抜けが生じないこと（連続性の保証）

シーケンスオブジェクト
ID列

列持ちテーブル

利点

• シンプルな設計
• 入出力のフォーマットと合わせやすい

欠点

• 列の増減が難しい
• 無用のNULLを使わなくてはならない

9章

終わりに

DB設計のパターン、アンチパターンからインデックスの箇所など今まで具体的に理解していなかった箇所を網羅的に理解できて良かった内容でした。

参考

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