初めに
こんにちは。
頼れるエンジニアになるため勉強を頑張っている4年目エンジニアです。
技術書を読むだけではもったいないと思い、アウトプットとしてQiitaで発信しています。
少しでもこの本を読むきっかけになればなと思います。
今回の書籍
今回の書籍ですが、前回のDBの達人にSQL学んでみたの設計編となっています。
前回の書籍を読んでいたおかげで、文中に出てくるSQLに対しての抵抗が無くて良かったです。
学んだこと
ここから下は章読む→要約するを繰り返した作った部分です。
分からなかったところは分かるまでChatGPTに聞くをしています。
また業務で使えないと意味が無いので、ChatGPTに演習を作ってもらったりどういう場面で使うのかというレベル感で理解を深めました。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
1章
三層スキーマモデル
| スキーマ層 | 視点 | 説明 |
|---|---|---|
| 外部スキーマ | ユーザー | 特定ユーザーやアプリケーションに対して見せる部分（ビューなど） |
| 概念スキーマ | 開発者・設計者 | データベース全体の論理構造。エンティティやリレーションの定義など、論理設計の対象 |
| 内部スキーマ | DBMS | データの物理的な格納方法やインデックスなど、記憶装置上の構造、物理設計 |
2章
論理設計
論理：物理層の制約にとらわれない（CPUパワーやストレージなど）
∴概念スキーマ（論理設計）→内部スキーマ（物理設計）の順番で設計を行う
論理設計のステップ
エンティティの抽出
↓
エンティティの定義
↓
正規化
↓
ER図の作成
エンティティの抽出
要件定義書、仕様書などを読み情報として管理すべき“対象”や“実体”をリスト化する
エンティティの定義
抽出したエンティティの属性を決定する。キーもここで設定する
正規化
エンティティを整理する作業
ER図の作成
正規化によって増えたテーブルの関係を示す図の作成
物理設計
論理設計の内容を、実際に動くデータベースで効率よく扱うために落とし込む設計
物理設計のステップ
テーブル定義
↓
インデックス定義
↓
ハードウェアのサイジング
↓
ストレージの冗長構成決定
↓
ファイルの物理配置決定
テーブル定義
抽出したエンティティの定義からDBMS内に格納するためのテーブルを定義する
インデックス定義
検索や並び替えを速くするための「目次」を作る工程。
ハードウェアのサイジング
- 必要な性能や容量のサーバーを見積もること
- 性能要件の指標である「どれだけ多いか」、「どれだけ速いか」を意識する
- 精度を上げるためには安全率を考慮し、スケーラビリティの高い構成を意識
ストレージの冗長構成決定
| RAIDレベル | 特徴 | メリット | デメリット |
|---|---|---|---|
| RAID 0 | データを分散（ストライピング） | 高速 | 故障に弱い（1台壊れると全損） |
| RAID 1 | 同じデータを複製（ミラーリング） | 高信頼性 | 容量が半分になる |
| RAID 5 | データとパリティを分散 | バランス良し | 書き込みはやや遅い |
| RAID 6 | RAID5 + パリティ2つ | より高い冗長性 | 書き込みはさらに遅い |
| RAID 10 | RAID0 + RAID1（ミラー＋分散） | 高速＆高信頼 | 容量効率は低め |
ファイルの物理配置決定
- データファイル
- インデックスファイル
- システムファイル
- 一時ファイル
- ログファイル
多いRAID構成は1と2~5で分ける
バックアップ
バックアップの種類
- フルバックアップ
- 差分バックアップ
- 増分バックアップ
| 種類 | 保存する内容 | 特徴 | メリット | デメリット |
|---|---|---|---|---|
| フルバックアップ | 全てのデータ | 一番わかりやすい・完全コピー | ・復元が簡単で高速 ・単独で復元可能 |
・保存に時間と容量がかかる |
| 差分バックアップ | 最後のフルバックアップ以降に変更があったデータ | 復元が早い・容量が中くらい | ・変更分だけ保存で効率的 ・復元はフル＋差分だけでOK |
・時間が経つと差分が増えて容量も増大 、差分ファイルのどれか故障してたら復旧不可 |
| 増分バックアップ | 最後のバックアップ（フル or 増分）以降の変更分 | 保存が速い・復元が遅め | ・毎日の保存が速く軽量 ・ストレージ容量を節約できる |
・復元に複数ファイルが必要で複雑 ・1つでも欠損すると復元困難 |
復元手順
- バックアップファイルをデータベースに戻す→リストア
- 差分バックアップをしていたトランザクションログを適用する→リカバリ
- データベースサーバに残っているトランザクションログを適用する→ロールフォワード
3章
テーブル
概要
- テーブルとは、共通点をもったレコードの集まり
- テーブル名は英語で書くと複数形 or 集合名詞であらわせる
- テーブルに重複行は存在できない
テーブルの制約
- NOT NULL制約
- 一意制約
- CHECK制約
キー
- 主キー：一意に識別できるキー
- 複合キー：複数列組み合わせて作る主キー
- 外部キー：2つのテーブル間で使うキー
キーはコードやIDなどの固定長文字列を用いる
正規化
| 正規形 | 主なルール | 解決する問題 |
|---|---|---|
| 第1正規形 | 1列に1つの値（繰り返し排除） | データの一貫性と検索性 |
| 第2正規形 | 主キーの一部にだけ依存する列を分離（部分従属の排除） | データの冗長性削減 |
| 第3正規形 | 主キー以外に依存する列を分離（推移的従属の排除） | 更新時の整合性と効率化 |
4章
ER図書いてみた
ChatGPTに聞いたところ、draw.io使うとER図書けるということで、例題も作ってもらいER図を書いてみました。
1.Users
| カラム名 | 型 | キー | 備考 |
|---|---|---|---|
| user_id | INT | PK | ユーザーID |
| username | VARCHAR | ユーザー名 | |
| VARCHAR | メールアドレス |
2.Posts
| カラム名 | 型 | キー | 備考 |
|---|---|---|---|
| post_id | INT | PK | 投稿ID |
| user_id | INT | FK | 投稿者のユーザーID |
| title | VARCHAR | 投稿タイトル | |
| content | TEXT | 投稿内容 |
3.Comments
| カラム名 | 型 | キー | 備考 |
|---|---|---|---|
| comment_id | INT | PK | コメントID |
| post_id | INT | FK | コメント対象の投稿ID |
| user_id | INT | FK | コメントしたユーザーID |
| comment | TEXT | コメント内容 |
実際に書いてみた図
ChatGPTに採点してもらったところ正解でした。
実際書いてみると実感わきます。
5章
正規化の欠点はSQLで結合が発生するためパフォーマンスとトレードオフだということが書かれていました。
6章
インデックス
インデックスの特徴
- アプリケーション透過性
- データ透過性
- 性能改善大
Betreeインデックスの設計法
- 大規模なテーブルについて作成する
- カーディナリティ（種類の多さ）の高い列に作成する
- SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
パーティション
巨大なテーブルを論理的に分割して高速化・管理しやすくする仕組み
パーティションの種類
- レンジパーティション
- リストパーティション
- ハッシュパーティション
その他のパフォーマンスチューニング
- ヒント句
- パラレルクエリ
- オンメモリ
7章
アンチパターン
非スカラ値
第一正規形守っていれば起こらない
ダブルミーニング
列に2つの意味を持たせない。当たり前
単一参照テーブル
同じ構造だからと言って二つのテーブルをまとめない
テーブル分割
水平分割、垂直分割どっちも使用しない
不適切なキー
可変長文字列は不偏性があるから向いていない
外部キーに設定したキーを設定先で違う型定義したりしない
ゾンビマート、多段マート
むやみやたらにデータマートを作らないこと
8章
グレーゾーンな設計
代理キー
主キーが決められない場合（市町村コードなどで採番を使い果たしてしまう場合）に人工的に列を追加してキーを追加する方法
自然キー
DBに標準搭載されているタイムスタンプとインターバルを用いたキー
9章
隣接リストモデルと閉包テーブルモデル
| モデル名 | 強み | 使うべきとき | 例 |
|---|---|---|---|
| 隣接リストモデル | 実装が簡単、直感的、軽量 | 階層が浅い／構造があまり変わらない／部分的な親子関係だけ見ればいい | 商品カテゴリ（家電 > テレビ） シンプルなコメント返信機能 |
| 閉包テーブルモデル | ツリー探索が高速／再帰なしで柔軟に扱える | 階層が深い／すべての子孫・祖先をよく検索する／構造の変更が頻繁 | 組織図（社員 → 部長 → 本部長） ナレッジツリーやスレッド構造など |
まとめ
今回設計編を読んでみて、漠然としてやり方の分からなかったDB設計の解像度が格段に上がった。
そのおかげでまた１つステップアップした感覚が得られた。
エンティティの抽出から正規化をしてER図を書く流れは業務としてもよく使うだろうなと思った。
この書籍を通してDB設計する上で足りないのは物理層のデータベースサーバやストレージの選定するための知識だ。
書籍にはDBのドキュメントを嫁と書いていたのでそれが一番確実なんだろうなと思った。
ちょっと気になったので各DBの特徴の表をChatGPTに出してもらった
| データベース | タイプ | 特徴 | メリット | デメリット | 代表的な用途 |
|---|---|---|---|---|---|
| PostgreSQL | RDBMS（OSS） | 高機能で拡張性あり。JSON, GIS, 再帰クエリなど現代的機能に強い。 | - 標準SQLに忠実 - 高機能（CTE, ウィンドウ関数） - OSSで無料 - 拡張性◎ |
- 設定やチューニングは初心者には少し難しい - ドキュメントがやや上級者向け |
Webアプリ、分析、業務システム全般 |
| MySQL | RDBMS（OSS） | 世界中で使われてる定番。商用利用も多い。 | - シンプルで軽量 - ドキュメント豊富 - MySQL 8で再帰CTEも対応 - OSSで無料 |
- 過去は厳密なSQLチェックが弱かった - PostgreSQLより機能面では劣る部分もある |
中小規模のWebアプリ、WordPressなど |
| SQLite | 組み込みDB | ファイルベースで軽量。インストール不要。 | - 超軽量 - 依存なし（1ファイル） - モバイル・IoTにも◎ |
- 複数ユーザーの同時書き込みに弱い - パフォーマンスや機能は限定的 |
モバイルアプリ、組み込み系、ローカル検証 |
| Oracle Database | RDBMS（商用） | エンタープライズ向け。信頼性と実績は最強クラス。 | - 信頼性・可用性◎ - 商用サポートあり - 大規模システムに強い |
- ライセンス費用が超高額 - 学習コスト高い |
大手金融、官公庁、大規模企業システム |
| SQL Server | RDBMS（商用） | Microsoft製。Windowsとの相性が抜群。 | - BI（分析）機能が強い - MS製品との統合が簡単 |
- 商用ライセンス必要（ただしExpressは無料） - Linux対応は限定的 |
企業の業務アプリ、ERP、Windows環境全般 |
| MongoDB | NoSQL（ドキュメント型） | JSONライクな柔軟な構造をそのまま保存できる | - スキーマレスで柔軟 - 開発スピードが早い - スケールアウトしやすい |
- 複雑なトランザクションは苦手 - 厳密な整合性が必要な場面には不向き |
モダンなWebアプリ、プロトタイプ開発 |
| Redis | NoSQL（キー・バリュー型） | インメモリで爆速。キャッシュ用途がメイン。 | - 処理が超高速 - Pub/Subやセッション管理に便利 |
- 永続化はオマケ程度 - 複雑なクエリ不可 |
キャッシュ、キュー、セッション管理 |
今自分にできることは、これから使うDBの仕様をしっかり定着させて、幅を広げていくことだと思う。
（自分で使ったことのないDB検証出来たら一番）