読んだ: 達人に学ぶDB設計 徹底指南書
Created at Wed, Mar 18, 2015この記事には僕の主観, 解釈が入っています. 本のまとめというよりかは, 感想的なものです.
データベースを制する者はシステムを制す
システムを構築する時に, 要件定義をし, どのようにデータが必要, 保持するかを決定することはとても大切です. 拡張性が高く, 正しいデータ設計をすると, 要件修正があった時にも, 大きな変更をすることはありません. データ中心のアプローチは現在の主流です.
view(外部スキーマ)とmodel(論理スキーマ)を分離しておくことも大切です. 論理スキーマを正規化などの技術を使い, リレーショナル・データベースの基本に沿った設計をすることで, 外部スキーマで柔軟に組み合わせて使用することが出来ます.
論理設計と物理設計
論理設計は物理設計に先立って行われる必要があり, 論理設計は物理設計とは独立して設計することが出来ます.
論理設計では, エンティティ抽出が行われます. エンティティは要件定義に基づいて行われます. 例えば, 要件「ユーザがブログを投稿できる」があったとしたら，ユーザとブログがエンティティとして抽出できます. そして, 各エンティティがどのような属性(列)を持っているかを定義して, どのような関係(1対多になっているかなど)になっているかをER図などで示します.
物理設計では, パフォーマンス, キャパシティの2つの要素が仕様を満たすようにすることが大切です. キャパシティは, データファイル, インデックスファイル, システムファイル, 一時ファイル, ログファイル(トランザクション履歴的なもの)の5つのファイルがあり, それぞれのファイルがどれくらいのサイズになるかを見積もります. パフォーマンスは, TPS(Transaction Per Second)のスループットがどれくらい出れば良いかを設定し, CPU, メインメモリを見積もります.
冗長設計(RAID, レプリケーション), バックアップ設計(フル, 差分, 増分バックアップ)をし, 不測の事態でもデータがなくならないようにすることも大切です.
論理設計と正規化 ～なぜテーブルは分割する必要があるのか？
正規化は, テーブルをどのように定義すればいいかのプラクティスです. 正規化をすることでデータの重複を防ぎ, データサイズを小さくし, データ不整合を防ぐことが出来ます. 正規化はRDSにおける, リファクタリングのような感じです.
正規化には, 第5まであります. テーブルを分割することで, データの重複を除去していきます. 詳しくはこちらが分かりやすいです.
まとめると, 正規化メリットは
- データの重複を防ぐ
- データサイズを小さく保つことが出来る
- 正規化のノウハウは広く共有されているので, 他の人にも分かりやすいテーブル設計になりやすい
ER図 ～複数のテーブルの関係を表現する
正規化をすると, 必然的にテーブルが増加していきます. その結果, テーブル同士の依存関係が複雑になり管理が困難になります. ER(Entity Relationship)図で図示することで, 理解を助ける事ができます. Entityにはテーブルの属性, プライマリーキーなどを記述し, Entityの関係(Relationship)を線のようなものでつないでいきます. 詳しくはこちらが分かりやすいです.
ER図を作成するメリットは, DB管理者以外の, アプリケーションエンジニアやマネージャーなどにも理解しやすいことだと思います. (メモ: Google Spread SheetでDB設計を書くと, 自動でER図に変換してくれたりすると嬉しい)
論理設計とパフォーマンス ～正規化の欠点と非正規化
正規化をすると, データ整合性が保ちやすくなります. しかし, テーブルの数が増えるため, 必要なデータを取得するときに, joinを多用することになります. joinはコストが高いオペレーションなので, パフォーマンスに影響が出る可能性があります. 正規化は往々にして 検索のコストを上げる ことになります.
なので, パフォーマンスがシビアに要求される箇所は, 非正規化を行い解決します. 非正規化には, 「集約データを属性に持たせる」 or 「単純に正規化と逆のことをやる」. の2種類があります. 「集約データを属性に持たせる」とは, 例えば, ショッピングサイトでお買い物をしたとすると, オーダーテーブル, オーダーと注文した商品を関係づけるテーブル, 商品テーブルの3つのテーブルが必要だとします.
オーダーテーブル
- オーダーID, ユーザID, 注文時間
オーダーと注文した商品を関係づけるテーブル
- オーダーID, 商品ID, 個数
商品テーブル
- 商品ID, 名前, 値段
ここで, 注文したオーダーの値段が知りたいとき, オーダーと注文した商品を関係づけるテーブルと商品テーブルをjoinする必要が有ります. これは非常にコストが掛かりパフォーマンスに影響が出そうです. そこで, オーダーテーブルに合計金額を保存することにします. そうすれば, あるオーダーの値段が単純なSQLで取得することが出来ます. 多少冗長でも, よく使うであろうデータを持たせておくことで, パフォーマンスを改善できます. その代わり, 「データサイズが増える」. 「データ不整合になりやすい」. といったデメリットがあります.
データベースとパフォーマンス
パフォーマンスを上げるためには, インデックスの設計をする必要があります. インデックスには, 「アプリケーションコードを修正する必要がない」. 「データに何も影響を与えずにパフォーマンスを上げることが出来る」といったメリットがあります. しかし, 「インデックスファイル分, ストレージを余計に必要とする」.「データ更新時にインデックスを再構築するため, データ更新の速度が遅くなる」といったデメリットがあります.
インデックスを作る列には下記の点に留意する必要が有ります.
- カーディナリティの高い列に作成. また値に偏りがない.
- WHERE, JOIN条件に使用されている列に作成
- LIKE述語は前方一致のみインデックスが適用される(LIKE ‘abc%’ はインデックスが効くが, LIKE ‘%abc’ はインデックスが効かない)
論理設計のバッドノウハウ
箇条書きで説明します.
ダブルミーニング
- カラムの表している意味が行(列)ごとに違う. 例えば, 体重かと思ったら, BMI値が入っている人もいるなど
- 無駄に混乱を生むので使わないほうが良い
単一参照モデル
- 1つのテーブルに区別カラムを持たせ, いろいろな種類のデータを挿入する
- アプリケーション側で, 区別カラムを特定する必要があり, それを忘れるとバグが混入する. 無駄に混乱を生むので使わないほうが良い
ダブルマスタ
- システムの統合で, 同じ用な意味を持つテーブルが複数ある状態
- システムのクレンジングをする必要がある
論理設計のグレーノウハウ
代理キーは極力使わないようにする. 代理キーではなくて, タイムスタンプや, 日付, インターバル(開始, 終了年度)といったものを組み合わせると, 代理キーの作成を防ぐことが出来る.
- 基本, 自然キー以外を主キーに使うのはナンセンス
- 履歴などを保持したい場合は, インターバルやタイムスタンプ列を導入して解決出来ることがある
列持ちテーブルではなくて, 行持ちテーブルを使う.
- 列持ちテーブル: id, name, 子1, 子2, 子3 のように列を保持
- 行持ちテーブル: id, nameというテーブルと, ID, 子(行持ち)というテーブルの2つで保持する
集計キー
- 地方コードを列として追加 => テーブルのサイズが大きくなり, パフォーマンスが悪化する恐れあり
- (県コード,地方コード)のrelationを表したテーブルを作成する. しかし結合が必要. Viewを使う, case when + group byを使う方法もある
感想, メモ
広くデータベースのことが説明されている名著でした. 次は, MySQLとか, Postgreなどの具体的なDBの本を読もうと思います.