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

はじめに

DB設計を、基礎からもう一度学んで
みたいと思い達人に学ぶDB設計徹底指南書を購入。

自分用メモで気づきメインで書くので、
詳しく知りたい人は購入することを推奨。
~~マークダウンは試しにやっているので統一性がなくちょっと汚く見えるのはユルシテ~~

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

データベース(DBMS)の代表的モデル

RDB(リレーショナルデータベース)
OODB(オブジェクト指向データベース)
XMLDB(XMLデータベース)
KVS(キーバリューストア型)
HD(階層型データベース)

ODAとPOA

DOA(data oriented approach) → データ中心アプローチ
POA(process oriented approach) → プロセス中心アプローチ
最初にデータがある。その次にプログラミングがある

3層スキーマ

外部スキーマ → ユーザーから見たデータベース
概念スキーマ → 開発者から見たデータベース
内部スキーマ → DBMSから見たデータベース

感想

データ設計で苦しむのは実務でも経験した。
実際にDBを作成したあとに追加依頼が入ることもあるので、
追加改修に応えられるようその都度ベストな設計をしていっても
現実そうならないこともあるよな。。

2章論理設計と物理設計

論理設計のステップ

エンティティの抽出(物理的実体を伴う必要はない)
エンティティの定義(列(attributes)を決める)
正規化
ER図の作成

物理設計のステップ

テーブル定義(概念スキーマ毎にテーブルを作成)
インデックス定義
ハードウェアのサイジング](該当システムで処理されうるデータサイズを見積もって、「十分な容量のストレージ（記憶装置）を決める」)
ストレージの冗長構成決定
ファイルの物理配置決定

RAID0→ 異なるディスクに「別々に」保存
RAID1→ 異なるディスクに「同じものを」保存
RAID5→ 3本で保存、1つまでなら壊れても可
RAID10 → RAID1とRAID0の組み合わせだが、コストが高い

感想

RAIDに関してはとりあえず、少なくてもRAID5。余裕があればRAID10

バックアップ設計

フルバックアップ

欠点1 → 時間が長い
欠点2 → ハードウェアリソースへの負荷が高い
欠点3 → サービス停止が必要

差分バックアップ

欠点 → 差分とフルバックアップの2つがあるので、その分手間
利点 → 早い。バックアップ量が減る

増分バックアップ

欠点 → リカバリの際に一番たいへん
利点 → 早い。バックアップ量が減る

感想

ハードウェアのサイジングは、必要十分なストレージサイズにする必要があるので、「ある程度余裕を持たせたストレージ容量にしておく」か「簡単に、ストレージの容量を追加できるような構成にしておく」のがよさそう。
スケーラビリティが高いというやつ。

関係ないけど、パズドラで以前ヘラ杯ロールバック事件合った時、あれは何バックアップだったんだろう。笑

バックアップ方式は、フルバックアップに増分or差分がヨシ

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

テーブルの構成要素

外部キーは人間の親子関係と同じ
外部キーがあれば、子供から消すのが吉(カスケード)
キーとなる列には、コードやIDなど表示形態の定まった可変長文字列を用いる
列には出来る限りNOT NULL制約をいれる

正規化とは

データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式

第1正規形

スカラ値 → 一つのセルに一つの値が含まれていること
関数従属性的に、一つのセルにデータが複数入ることはおかしい
「一つのセルに一つのデータしか含まない」

主キーにNULLを含んではならない。

第2正規形(部分関数従属)

第2正規形は、部分関数従属を解消することで得られる

親と子でテーブルをわけない設計だと、親のない子は登録できないみたいなことがおきたり、データの正確性が欠けてしまう。

無損失分解(テーブルを分割/結合可能)に出来るのでよい。

第3正規形(推移的関数従属)

テーブル内部に存在する段階的な従属関係のこと

ボイスーコットと第4、5は割愛

感想

json型にして列に履歴を保管したりしてたけど、あれはよくなさそうだったな。流石に第1正規形には違和感しかないが、第2正規形までは普通にできそう。推移的関数従属をすぐ見抜けるようになりたい

第5まであるが、基本的には第3正規形まで理解しておけばOK

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

IE表記法 → 鳥の足のやつ
IDEFIX

感想

既知だったが復習になった。

独立エンティティと従属エンティティの区別は主キーに外部キーが含まれているかでわかる

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

正規化 → データ整合性が担保される
非正規化 → SQLで結合を使わずにすむ

感想

正規化されていないデータは基本的には作らないようにする。
ただ、SQLのパフォーマンスが悪くなったりすることもあるので、
意識する。かといって、非正規化にしていいというわけでない。

正規化と非正規化はトレードオフ。原則として非正規化はNG。最後の手段である。

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

インデックス
統計情報

インデックス

アプリケーションのコードに影響を与えない
テーブルのデータに影響を与えない
それでいて性能改善の効果が大きい

B-treeインデックス

平均点が高い。
均一性、持続性、処理汎用性、非等値性、親ソート性
がどれもオールラウンダーで高い。 by クリス・デイト

B-treeインデックスをどこにつくればいいか？

大規模なテーブル
カーディナリティの高い列(特定の値の列が、どのくらいの種類の多さを持つか)
WHERE句の選択条件、または結合条件に使用されている列

しかし、カーディナリティが特定の列に偏っていると効果を発揮しづらい

統計情報

SQL実行
パーサーが構文チェック
最適なデータアクセス方法(実行計画)が決定。この処理がRDBMSにおけるコアとなる部分。オプティマイザは、複数のアクセス経路、インデックスの有無、データの分散や偏りの度合い、RDBMSの内部パラメータなどの条件を考慮して、コストを評価し選択可能な多くの実行計画を作成。

実際にエンジニアが実行計画立案に関与することはないが、

統計情報収集タイミング → データが大きく更新された後、なるべく早く
統計情報収集の対象(範囲) → 大きな変更のあったテーブル

については意識しないといけない。

感想

やみくもインデックスを貼るのではなく、検索されやすかったり、カーディナリティの高い列に作成するのがいいことを学んだ。
インデックスはデータ構造が変わる時にメンテナンスを適宜行う
実行計画については、もっと詳しく調べておきたい。
本番のSQLを叩くの未だに緊張する。笑

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

配列型による非スカラ値(第一正規形未満)
ダブルミーニング(レコードによって意味が違う)
単一参照テーブル
テーブル分割
ダブルマスタ

総評

物理設計や論理設計、エンティティやバックアップ、
正規化のトレードオフなど、普段意識しないところをさらっと復習できたのはよかった。実際に実務でDB設計する時に、辞書的な感じでまた読み返したいと思う。

8章と9章は割愛。