DB設計時のポイントについて、個人的におすすめな「達人に学ぶDB設計 徹底指南書」に記載されている内容を参考にさせてもらいつつまとめます。
Contents
DB設計に関する理解の重要性
私は、某製造メーカーにて生産管理関連のシステムの設計・開発・導入をしています。社内SEという立ち位置のため、開発自体は外注することが多いのですが、データの分析や活用の面でDBを扱うことが多いです。主にOracle、後はソフトなどの前提によりSQL ServerやPostgreSQL、MySQL等です。
私はDBエンジニアというわけではないのでDBの細かいところは得意というわけではないのですが、社内SEとして開発ベンダーと議論する際や、BI等によるデータ分析や活用を考えたときにDB設計についてしっかりと理解しておくことは非常に重要です。
DB設計に関する学習の際には「達人に学ぶDB設計 徹底指南書」が特に役に立ちました。
この書籍は、DB設計の基本的な内容と共に、バッドノウハウやグレーノウハウといったやってはいけないノウハウについても色々と紹介されています。
本記事では、上記書籍にかかれているDB設計のポイントや自分の経験やその他調べて学んだことを加えて整理します。DB設計について気になる方の参考に少しでもなればよいかなと思います。（私自身としてもDB設計について思い出すときや書籍を見直すときのための覚え書きとなればなと思っていたりします）
本記事では、特に各種設計の考え方を中心に整理します。書籍を参考にさせていただいていますが、あくまで私が理解した内容を整理するという位置づけですので、細部興味がある方は、上記書籍を読んでみてもらえればなと思います。
DB設計時のポイント
データベース設計の基本
DBとDBMS
「データベース（DB）」とは、データの集まりを指すために使われる言葉です。一方で「データベースマネジメントシステム（DBMS）」とはデータベースを管理するためのシステムのことを言います。
私は製造業の社内SEとして働いていますが、製造現場の人と話していたりすると現場の人はExcelで整理された表データをDBと呼んだりします。これまで開発等を経験してきた私からするとDBというのはOracle等のDBMSで管理された正規化されたデータを思いだすので、何となく違和感を感じたりします。ただ、これもデータの集まりということでデータベースといえばデータベースです。
データベースの種類
データベースと一言で言っても色々な種類があります。
- リレーショナルデータベース（Relational Database: RDB）
- オブジェクト指向データベース（Object Oriented Database: OODB）
- XMLデータベース（XML Database: XMLDB）
- 階層型データベース（Hierarchical Database）
- キー・バリュー型ストア（Key-Value Store: KVS）
- ドキュメント指向データベース（Document Oriented Database）
- 列指向型データベース（Columnar Database）
- グラフデータベース（Graph Database）
この中で企業などでいまだに中心的であるのはリレーショナルデータベース（RDB）でしょう。以降で整理する内容はRDBに関連するものが中心になります。
キー・バリュー型ストアより下のデータベースは近年のビッグデータや特定領域を対象としたようなデータベースです。本記事では扱いませんが、興味がある方は調べてみてもらえるといいかと思います。
主なDBMS
DBMSについてもいくつかの種類があり、RDBに関して代表的なものは以下のようなものがあります。
- Oracle Database
- SQL Server
- DB2
- PostgreSQL
- MySQL
私の場合、今現在働いしている職場だと最も使われているのはOracleです。他にも、DB2以外は多少触ったことがあります。
Oracle等のメーカー提供の有償DBMSはサポート含めて安心感があるという点があるかなと個人的には思います。一方で、PostgreSQLやMySQLはOSS（Open Source Software）のため無償で使用することができるので便利ですが、問題発生時に自力で解決できるぐらいのノウハウがないと若干不安が残ります。最近はOSSのサポートを提供している企業などもありますので利用を考える必要もあるかもしれません。
本サイトで取り上げている中心であるPythonでもデータベースを扱うためのモジュールが各種用意されていて、以下記事で一部データベースに関連する方法をまとめていますので興味があれば参考にしてください。
DOAとPOA
システム開発時の考え方として、データ中心アプローチ（DOA: Data Oriented Approach）とプロセス中心アプローチ（POA: Process Oriented Approach）といった考え方があります。
その名の通り、データの構造を決めてからプログラム開発を進める方法がDOAで、プロセス（処理＝プログラム）を先に作ってデータを決めていく開発方法がPOAです。POAは時代遅れとみなされており、DOAのデータ中心に進めていくのが近年の主流です。
少し脱線しますが、UI（ユーザーインタフェース）についてもオブジェクト指向UIデザインという領域でも、オブジェクト（データ）が決まってUIが決まっていくので、厳密には異なる領域ですが似ているかなという印象を持っていたりします。
3層スキーマ
DB設計で意識して区別しておかなければいけないことがスキーマの概念です。以下の三つのスキーマに基づくモデルを「3層スキーマモデル」と言います。
- 外部スキーマ（外部モデル）：ビュー
- 概念スキーマ（論理データモデル）：テーブル
- 内部スキーマ（物理データモデル）：ファイル
外部スキーマは、システムの利用者やシステムの画面から見たデータベースの姿で、いくつかのテーブルを必要に応じてつなぎ合わせて利用者に見せているものです。DBMS等で扱うViewがまさに該当するでしょう。
概念スキーマは、具体的に一つ一つのテーブル定義のことで、主に開発者が意識する領域で、論理設計と呼ばれる設計領域に該当します。テーブル間の関係性を表すER図もこの部分に該当します。
内部スキーマは、よりハードウェアに近い世界で、DBMSから見たデータベースです。プログラマでもDB構築をしたことがない人だとよく知らないかもしれませんが、DBの実態はテーブルやインデックスのDBファイルとして格納されています。このDBファイルをどう設計して、どのようにディスク配置するか等の物理設計と呼ばれる設計領域に該当します。
概念スキーマは、3層の中で間に挟まっているスキーマで、利用者よりの世界（外部スキーマ）とハードウェアよりの世界（内部スキーマ）の間をうまくつなぐための緩衝材のような役割をしています。
この3層がうまく分離されていないと画面側の変更が物理設計に影響したり、物理設計の変更が画面に大きく影響したりしてしまい、変更に対する柔軟性がないシステムになってしまいます。このようなことにならないように各スキーマを分離して独立させることをスキーマのデータ独立性と言い、概念スキーマはデータ独立性を保証するための役割を担っています。
DBの論理設計
DBの論理設計は、以下の手順で行われます。
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
エンティティの抽出
エンティティというのは、データベースで扱う実態です。例えば、「顧客」「社員」「会社」「注文履歴」のようなものがあります。これらがテーブルになっていくわけです。
エンティティの定義
エンティティの定義では、それらのテーブルのキーが何で、属性（列）としてどういったものがあるかを決めていきます。
正規化
エンティティ（テーブル）の整合性を確保するためにデータを整理する作業を正規化（normalization）と言います。
正規化は第１～５までの正規化がありますが第３正規化までは正規化するべきと言われています。
ER図の作成
正規化まで行うとテーブルが複数出来上がってくるのですが、正規化するとテーブルが分割されるので各テーブルの関係性がよく見えなくなってきます。このテーブル間の関係性を表現するのがER図（Entity-Relationship Diagram）になります。
DBの論理設計は上記のような手順で行われていきます。正規化やER図については書き出すと長くなるので別で整理してみようかなと思っています。
DBの物理設計
DBの物理設計は、以下の手順で行われます。
- テーブル定義
- インデックス定義
- ハードウェアのサイジング
- ストレージの冗長性構成
- ファイルの物理配置
テーブル定義
テーブル定義は、論理設計のエンティティ定義に内容に近いと思うかもしれませんが、物理設計では具体的に各テーブルの属性（列）がどういった型で、サイズはどうするかといった具体的な物理定義をしていきます。
インデックス定義
インデックス（索引）はデータベースの検索の応答性に非常に重要な役割を果たします。
その名の通り書籍などの索引と同じで、一部の列または複数の列に対してインデックスを作成すると、検索の性能が大幅に向上します。インデックスはなくても動くのですが、私の過去の経験からも適切にインデックスが張られていないDBを扱うとかなり遅くてストレスを感じますので、非常に重要な設計要素になります。
ハードウェアのサイジング
ハードウェアのサイジングは「データの容量」と「パフォーマンス」という2つの観点があります。
DBはDBサーバーを用意して運用することがほとんどですが、サービス開始後のデータ容量増加を見越してストレージのサイズを決める必要があります。また、速度を考慮して、CPUやメモリ、ストレージのI/O速度も意識する必要があります。
データベースの性能問題の8割はディスクI/Oに起因するともいわれています。HDDのディスク回転数をどうするかや必要に応じてSSDを選択するといったことも検討が必要です。
ストレージの冗長構成
ストレージの冗長性は、要はRAID（Redundant Array of Independent Disks）の事です。RAIDは、複数のディスクをまとめて仮想的に一つのストレージとする技術で、まとまりをRAIDグループと言います。
RAIDには主要なもので以下のようなものがあります。
| レベル | 概要 |
|---|---|
| RAID0（ストライピング） | データを異なるディスクに分散することでI/O性能を向上させる。ただし、1本でもディスクが故障するとデータは失われるため冗長性は全くない。 |
| RAID1（ミラーリング） | 2本のディスクに全く同じデータを書き込むことで冗長性を確保する。信頼性は向上するが、I/O性能には寄与しない。 |
| RAID5 | 最低3本で構成し、パリティという誤り訂正符号を含めて分散してデータを格納する。分散するのでI/O性能の向上も期待でき、1本までなら故障に耐えられるが、2本同時に故障すると復旧できない。 |
| RAID6 | RAID5の上位版ともいう方式で、最低4本で構成し、2重に冗長データを生成する。二重に冗長データを生成するのでRAID5より性能が悪くなるが、同時に2本までの故障まで対応できる。 |
| RAID10（RAID1+0） | RAID1+0ともいうこともあるRAID0とRAID1を組み合わせた方法。I/O性能と冗長性を両方確保できるいいとこどりの方法だが、コストが高い（最低でも4本HDDが必要） |
どのRAIDレベルを採用するかは、どの程度費用がかけられるかとどの程度の信頼性を求めるかによるというのが正直なところです。
私の所属している企業では、RAID1+ホットスペアということでRAID1の構成に加えて1台待機HDDを用意して1本故障時には自動で再構成して1本のみにずっとなってしまうことを防ぐような構成がよく使われています。また、より信頼性が求められるシステムではRAID5やRAID6等も使われています。
ファイルの物理配置
DBMSが管理するファイルとして設計時に考量するべきファイルとしては、以下のような項目があります。
- データファイル
- インデックスファイル
- システムファイル
- 一時ファイル
- ログファイル
開発者が中心的に意識するのはデータファイルとインデックスファイルぐらいかと思います。システムファイル、一時ファイル、ログファイルはDBMSが使用するファイルです。
システムファイルはその名の通りシステム管理のための情報です。一時ファイルもその名の通り、データを一時的に格納する領域です。SQLでGROUP BYやDISTINCT等を利用するときなどに一時的にデータを展開する領域になります。
プログラマでもあまり理解していない人もいますが、DBMSはデータのコミット時に即座にデータファイルを更新しているわけではありません。一旦ログファイルに変更をため込んで、あるタイミングで一括してデータファイルへ変更を反映しています。Oracleでは「REDOログ」、PostgreSQLやSQLServer、DB2では「トランザクションログ」、MySQLでは「バイナリログ」等、DBMSによって呼び方が異なります。
ファイルの物理配置では、これらのファイルをどのようにハードウェアに配置するのかを決めます。性能的にはすべての別ディスクに配置した方がI/O性能はよくなります。しかし、ハードウェアコストは非常に高くなります。I/O性能とコストはトレードオフの関係にあります。
それほど負荷が高くないシステムであれば全て同じディスクでも問題はないでしょうが、大規模なシステムになったときは費用と相談しながらちょうどよいポイントを探すのが重要になります。
バックアップ設計
バックアップについては、企業のシステムでは非常に重要な設計項目です。ハードウェア故障などの際にデータが永久に失われるようなことがあれば大惨事になるでしょう。
バックアップの主要な方式は以下の３つになります。
- フルバックアップ（完全バックアップ）
- 差分バックアップ
- 増分バックアップ
それぞれ簡単に内容を整理してみましょう。以降の例では毎日、夜間の0:00にバックアップを取っている例を想定して記載します。
フルバックアップ（完全バックアップ）
フルバックアップ（完全バックアップ）は非常にシンプルで、以下のように毎日全データをバックアップします。
フルバックアップは、リカバリする際には、前日のバックアップデータだけで済みます。仮に前日のバックアップが破損していても、その１日前までなら戻せる可能性もあります。
フルバックの欠点としては、全データをとる特性上バックアップの時間が長くなる事です。ハードウェアのリソース負荷が高くなりますし、サービス停止時間が長くなる点も問題になる可能性があります。
差分バックアップ
差分バックアップは、ある日にフルバックアップを取得して、その後はフルバックアップ時点からの変更分のみのバックアップを取得します。
差分バックアップは、リカバリする際には、フルバックアップ＋前日のバックアップが必要になります。
差分バックアップの利点はデータ容量が減ることです。欠点は、リカバリの時に複数ファイルが必要になるので手順が増えて時間がかかる事です。どちらかが破損していた場合に復旧できないという点も欠点になります。
増分バックアップ
増分バックアップは、考え方については差分バックアップと同じです。ある日にフルバックアップを取得して、その後は前日からの変更分のみのバックアップを取得します。
増分バックアップは、リカバリする際には、フルバックアップ＋前日までの全てのバックアップが必要になります。
増分バックアップでは、バックアップのデータ容量が全ての方式で最小になりますのでディスク容量が限られる場合等に便利です。一方で、リカバリ手順が最も複雑になりますし、一部のデータが破損していた場合に完全に復元できないという点が欠点になります。
どのバックアップ方式を使うべきか
上記で紹介してきたように、フルバックアップ、差分バックアップ、増分バックアップにはそれぞれ利点／欠点がありました。
これらの方式では、バックアップコスト（ハードディスク容量・時間など）とリカバリコスト（復旧時間・手順の複雑さ）がトレードオフの関係にあるという特徴があります。
方式の検討ポイントとしては以下の点があります。
- いつの時点の状態に復旧させるか
- バックアップ時間はどれぐらいか
- リカバリにかけられる時間はどれくらいか
- 何世代までデータを残すか
企業などではどのぐらいのダウンタイムを許容するかという観点でサーバーの投資費用の判断をしたりしますので、上記のような検討が必要です。一方で、小規模なシステムの場合や、システム停止が製造などの実運用に直結しないようなシステムの場合はバックアップは取らないという選択肢もあります。よく検討してちょうどよいポイントを探す必要があります。
リカバリ設計
バックアップ設計とリカバリ設計はセットで実施することが一般的です。
リストアとリカバリとロールフォワード
「リストア」と「リカバリ」と「ロールフォワード」という言葉は厳密に分けて使う必要があります。
| リストア | フルバックアップのファイルをデータベースに戻す。 |
| リカバリ | 差分（または増分）バックアップしたトランザクションログを適用する。 |
| ロールフォワード | データベースサーバー内の未バックアップのトランザクションログを適用する。 |
トランザクションログは、DBMS内部にも残っていて最後のバックアップ後に実施されたユーザーの変更分（未バックアップ）が残っています。この未バックアップのトランザクションログを障害直前まで適用することをロールフォワードと言います。
リストア→リカバリ→ロールフォワードといった作業をすることで障害発生直前までのデータを復旧できるということになります。
まとめ
DB設計時のポイントについて、個人的におすすめな「達人に学ぶDB設計 徹底指南書」に記載されている内容を参考にさせてもらいつつまとめました。
私はDBエンジニアというわけではないのでDBの細かいところは得意というわけではないのですが、社内SEとして開発ベンダーと議論する際や、BI等によるデータ分析や活用を考えたときにDBエンジニアと対等に会話できるようにということでDB知識は学んできました。
上記書籍にかかれているDB設計のポイントや自分の経験やその他調べて学んだことを加えて整理しています。本記事では、特に各種設計の考え方を中心に整理しましたが、正規化やER図、書籍で紹介されているバッドノウハウやグレーノウハウについては、また別途整理してみると知識の定着になるかなと思っていたりします。
なお、より詳細な内容については今回参考にさせていただいた以下の書籍を手に取っていただいて読んでみてもらえるとよいかなと思います。
