達人に学ぶDB設計 徹底指南書を読んで

はじめに

達人に学ぶDB設計の感想を記載いたします。

良かったところ

• 前回読んだSQL入門の書籍よりも専門的・実践的な内容でした。SQLの基礎知識がある前提で話が進むので、難易度が少し高めの書籍に感じました。
• 記載されている内容は少し難しかったですが、要所要所で図が使われて説明されるので頭に入りやすかったです。
• 章末の演習のシチュエーションが実践的・具体的で、実際に自分がDB設計に関わったようなイメージを持って演習に臨むことができました。
• 後半の章では実例を紹介しながらバッドノウハウ・グレーな設計ノウハウが紹介されていました。DB設計を行う際に「やってはいけないこと」を避けることができるような構成になっていました。
• 「DB設計で失敗すると開発全体が失敗しうる」という内容が随所に記載されており、論理設計・物理設計というタスクが非常に難易度が高いものであることを感じ取ることができました。

学んだこと

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

• 情報自体はデータのみから得られるものではない。データと文脈を合成して情報が生まれる。
• データベースのモデルが異なれば、フォーマットや設計方法も異なる。
  • 正規化はRDB以外のDBでは通常行わない。
• 一方で、モデルが異なっても「データの整合性保持」「冗長性排除」といった目的は変わらない。
• DBMSが異なってもDBのモデルが同じであれば、基本的に設計の方法は影響を受けない。
• DB設計が重要な理由2つ
  • ①DB設計＝データ設計でほぼ同義となるから
  • ②データ設計がシステム全体の品質を決めるから。ソフトウェアはあくまでデータの流通機構であり、プログラムの設計もデータ設計に左右される。
• DOAとPOA
  • POA(Process Oriented Approach)：プロセス（プログラム）を先に設計する方法論。現在では時代遅れ。
    • プロセス単位でデータ設計を行うため、同じデータをプロセスごとに別個に持つ必要がある。システム全体で見たときに冗長なデータ構造になりがち。処理ごとにデータが分散して管理が大変になる。
  • DOA(Data Oriented Approach)：プログラムより先にデータ設計を行う方法論。近年主流。
    • データを一元管理できるため、データがあまり変化せず永続的になる。複数プログラムで共用することが容易で、仕様変更にも柔軟に対応できる。
  • 「最初にデータありき」のスローガンで考えるべき。
• セオリーを踏む外したデータ設計を行うと、システムの機能/非機能の品質を致命的に損なうことにつながる。
• 3層スキーマモデル
  • 外部スキーマ：ユーザから見たDB
    • 画面のUIや入力データ等ユーザから見えるシステムの姿
  • 概念スキーマ：開発者から見たDB
    • 論理設計でこのスキーマを設計。データの独立性を保証するためにある。
    • 概念スキーマのない2層のスキーマで設計すると、変更に対する柔軟性がなくなる！
  • 内部スキーマ：DBMSから見たDB
    • 物理設計でこのスキーマを設計。DBMS内のファイルで表現される世界。
• Tips：概念の有用性がわからなくなったら、それがなかったらどうなるかを考えるようにする。長年の試行錯誤により生み出されたものがほとんど。

第2章　論理設計と物理設計

• システムの世界の「論理」：「物理層の制約に縛られない」という意味
  • 「物理層の制約」とは、DBサーバーのCPUパワーやDBMSのSQLの構文等実装面も含まれる。DB設計を最初に行う際は、こういう物理層の制約を一旦脇に置いて話を進める。
  • 論理設計→物理設計の順に行うということ
• 論理設計のステップ
  • ①エンティティの抽出
    • エンティティ（現実世界に存在するデータの集合体）を抽出する。要件定義の中で行われるもの。
  • ②エンティティの定義
    • 各エンティティの属性（どのようなデータを持つか）を定義する。
  • ③正規化
    • エンティティを抽出し属性を定義しただけでは、エンティティがシステムの利用に耐えられる状態になっていないため正規化を行う。
  • ④ER図の作成
    • エンティティの見取り図であるER図を作成する。エンティティ同士の関係を表現する。
• 物理設計のステップ
  • ①テーブル定義
    • 論理設計で定義された概念スキーマをもとにテーブルに変換する作業
  • ②インデックス定義
    • 非機能、パフォーマンスに大きく影響するインデックスを定義する作業
  • ③ハードウェアのサイジング
    • キャパシティとパフォーマンスの2つの観点からサイジングを行う。
    • パフォーマンス問題の8割はディスクI/Oによって決まる。
    • DBではデータの整合性とパフォーマンスの間に強いトレードオフが存在する。
    • キャパシティのサイジング
      • この段階ではサービス終了時のデータ量を見積もる必要があるが、なかなか難しい。見積もるためには以下2つのアプローチがある。
      • 1.安全率を大きく取って、余裕を持たせたサイジングをする。
      • 2.容量不足に備えて、簡単に記憶装置を追加できるような構成にしておく。
    • パフォーマンスのサイジング
      • 処理時間（どれだけ速いか）、スループット（どれだけ多いか）の2つが性能要件の指標となる。
      • リソース使用量については、「類似の稼働システムのデータを流用する」「プロトタイプを構築して、性能検証をする」ことにより基礎数値を測る。
    • サイジングについては精度高く行うことは難しい。そのため「実施時に必ず安全率をかける」「スケーラビリティの高い構成を組む」ことが大切になる。
    • サイジングは物理設計の中でも難易度が高いタスク。サイジングができればDBエンジニアとしてはエース級
  • ④ストレージの冗長構成決定
    • RAIDという技術を使ってデータの物理的な持ち方を冗長化する。DBに保管されるデータは業務の基幹データなので失うことは許されない。そのため、RAIDという技術を使って高い耐障害性を持つように構築する。RAIDを使うことで、システムの信頼性と性能を共に改善できる。
    • RAIDのパターンは何個か存在するが、DBのRAIDは少なくともRAID5(最低3本で構成、パリティを使用する)で構成する。
    • コストに余裕があれば、RAID10(RAID1とRAID0を組みあわせた構成)で構成する。
    • RAID0(データを異なるディスクに分散するのみの構成)は論外。
  • ⑤ファイルの物理配置決定
    • ファイルの配置については自動化が進んでいるのである程度はDBMSが自動的に配置してくれる。しかし、基本的な考え方を抑えなければトラブルシューティングが困難になる。
• バックアップ設計
  • データ損失の事件を避ける設計には2通りの方針がある。一つは極力データを失わないようRAID設計等を使うこと。2つ目は、データが失われた際に復旧できるようにしておくこと。
  • 主要なバックアップ方式は以下3つ。「フルバックアップ＋差分バックアップ」、「フルバックアップ＋増分バックアップ」が一般的
    • 1.フルバックアップ
    • 2.差分バックアップ
    • 3.増分バックアップ
• リカバリ設計
  • バックアップファイルを戻す作業が「リストア」、トランザクションログを適用して変更分を反映する作業を「リカバリ」という。
  • データ復旧時には、バックアップされていないDBMS内のトランザクションログまで適用する（ロールフォワード）ことで、データを障害直前の状態に復旧できる。

第3章　論理設計と正規化 ～なぜテーブルは分割する必要があるのか？

• 外部キーの役割はあるテーブルに対して参照整合性制約を課すこと。
• 親レコードがない子レコードを合わせた削除することをカスケードという。
• 常に子テーブルを先に削除・変更して後から親テーブルを更新すればカスケードするかいなかの考慮はそもそも不要になる。
• キーに使用する列はコードやID等の固定長文字列のデータ型にするのがRDBの鉄則。
• テーブル定義において、列には可能な限りNOT NULL制約を付与するべし。

正規化について

• 正規化とはDBのデータの冗長性を排除し、一貫性と効率性をもったデータ形式にすること。正規形は第5レベルまであるが、普通は第3世紀形まで理解すれば十分。
  • 第1正規形：一つのセルの中に一つの値しか含まないようにする。
  • 第2正規形：部分関数従属（主キーの一部に従属する列が存在する）を解消する。
  • 第3正規形：推移的関数従属（何段階かで従属する列が存在する）を解消する。
  • ボイスーコッド正規形：非キーからキーへの関数従属をなくした状態にする。分解時に気をつけないと不可逆な操作となることがある。
  • 第4正規形：多値従属性が複数存在するテーブルを分割する。
  • 第5正規形：関連と関連エンティティを1対1になるようにテーブルを分割する。
• 正規化の逆操作は結合。正規化は基本的に可逆的な操作。
• 正規化によりデータの冗長性を排除するとテーブル数が自ずと増える。そのため、SQL文で結合を多用することになりパフォーマンスが悪化する。

第4章　ER図 ～複数のテーブルの関係を表現する

• ER図の代表的なフォーマットは、「IE表記法」と「IDEFIX」の2つ。
  • IE表記のほうが簡素で概略が把握しやすい。IDEFIXはかなり細かく記述できる。
• RDBのお約束として多対多の関連は作ってはならない。こういう場合は、関連実体を使用して解消する。
• 複数のエンティティと対応するレコード数をカーディナリティという。
• 他のテーブルに依存しない独立エンティティと依存する従属エンティティの区別は主キーに外部キーが含まれるかでわかる。

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

• 正規化されたテーブルへのSQLは結合を多用することになるので、非常にパフォーマンスが悪くなる。
• 正規化の次数が低いほど検索SQLのパフォーマンスは良いが、データ整合性は低くなる。
• 意図的に非正規化することで、SQLで結合を使わずに済む。更新時には、非正規化するとレコード数が増えてパフォーマンスが悪くなることも多い。
• 非正規化は、切羽詰まった時にとる最後の手段（しかも劇薬）と考えるべき。
• その他、正規化にによって結合が必要になるためパフォーマンス悪化するパターン。
  • サマリデータの冗長性排除：サマリデータを冗長に保持すると非正規化になるが、検索を高速化できる。
  • 選択条件の冗長性排除：選択条件を冗長に保持すると非正規化になるが、検索を高速化できる。
• 非正規化は慎重に実施するべきで難しい作業。それゆえ、エンジニアの本務である。
• 非正規化のリスク3つ
  • 1.検索パフォーマンスは向上するが更新パフォーマンスは低下する。
  • 2.データのリアルタイム性が低下する。更新タイミングに注意。
  • 3.後続の工程で設計変更すると、手戻りが大きい。
• 論理設計の際には、「システムの品質・開発が成功するかは今ここで決まる！」という気概で臨む必要がある。
• 論理設計をする際には物理設計の知識も必要になる。

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

• インデックスはSQLパフォーマンス改善にポピュラーな手段。その理由3つ。
  • 1.アプリケーションのコードに影響を与えない。
  • 2.テーブルのデータに影響を与えない。
  • 3.透過的であるのに、性能改善効果が大きい。
• 頻繁に利用するインデックスはB-treeインデックス。B-treeインデックスは各特色の平均点が高いため秀才的なインデックス手法。
• B-treeインデックスを作るタイミング
  • 1.大規模なテーブルに対して作成する。目安として1万件以上のレコード数だと効果がある。
  • 2.カーディナリティの高い列に作成する。目安としては、一つのキーでレコードの5％に絞れるくらい。加えて値が分散しているとベスト。
  • 3.SQLでWHERE句の条件や結合条件に使用されている列に適用する。
    • インデックスを作成した列はSQL上で裸で操作しないと意味がないので注意。演算してはいけない。
• B-treeインデックスに関するその他の注意事項
  • 主キーや一意制約の列には内部的に自動でインデックスが作られるので、B-treeインデックスは不要。
  • B-treeインデックスは更新性能を劣化させる
  • メンテナンスを定期的に行うことが望ましい。
• 統計情報収集はデータが大きく更新された後になるべく早く行う。

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

• DBMSで配列型に対応していても、配列型は使用しないで第1正規形を守ること。
• カラムの分割については、意味的に分割できる限りなるべく分割して保持するべき。名前を「性＋名」に、メアドを「アカウント名＋ドメイン名」に分割する等。
• カラムは変数ではないため、ダブルミーニングのカラムはNG。
• ポリモフィックな単一参照テーブルは作るべきでない。
• テーブルの水平分割。垂直分割はNG。垂直分割したいなら、「集約」で代替する。
• オリジナルのテーブルから一部のカラムを抽出したテーブルをデータマート、あるいはマートという。
• 可変長文字列は不変性がないのでキーには向かない。固定長文字列が望ましい。
• ダブルマスタはシステムの統廃合で起きることが多い。

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

• 主キーが決められない・不十分なケース3つ。解決策は、代理キーを使用すること。
  • 1.入力データに一意なキーがそもそも存在しない
  • 2.一意キーはあるが、サイクリックで使いまわされている。
  • 3.一意キーはあるが、途中で指す対象が変化する。
• ただし一般的には極力代理キーは使わず、自然キーでの解決を図るべき。（代理キーにより論理モデルがわかりにくくなるため）
• 自然キーにより解決するためには、「タイムスタンプ」「インターバル」を使って複合キーにするものがある。
• 自動採番について
  • シーケンスとID列ではシーケンスの方が柔軟で拡張性が高い。
  • 自動採番をアプリケーション側で実装するのは「車輪の再発明」なのでNG。
• ビューを使用する際には、ビューの背後にデーブルが存在することを意識すること。この意識が薄いと、多段ビューによりパフォーマンス悪化につながる。
• 多段ビューのような過度に複雑な作りはシステムをダメにする。「KISSの原則」に従うべき。
  • KISSの原則；Keep It Simple, Stupid.(単純にしておけ、馬鹿者)
• データクレンジングの重要性
  • 一意キーの存在しないデータは不適切なキーを生むので、データクレンジングが必要。
  • 名寄せをサボると、ダブルマスタを産むのでデータクレンジングが必要
• 上記のようなグレーノウハウは軽い気持ちで使うものではない。グレーな解決策であることを意識して使用すべき。

第9章　一歩進んだ論理設計 ～SQLで木構造を扱う

• RDBでは木構造のデータを表現するのが苦手。
• 伝統的な手段として、隣接リストモデルで表現できるがSQLが複雑になる。
• 近年では、「入れ子集合モデル」「入れ子区間モデル」「経路列挙モデル」等で木構造を表現している。

難しかったこと

• 最終章の木構造の話は記載内容が難しかったです。木構造の論理設計を行う際には再度読み直そうと思いました。