基礎編では、「データ操作言語」と「データ定義言語」を
応用編では、「データ制御言語」をまとめました
SQL以外のDBに関する理論とかはまた別にしました
- SQL基礎編はこちら
- SQL応用編はこちら
はじめに
DB試験対策やDB設計についての知識をできる限りまとめました
DB設計ってプロジェクトやコンテンツによって別物だし、code-and-fix開発方式じゃないことは確かだけど、なんやかんやじゃないかと思ったので、ChatGPTに質問しました。
実務でのDB設計の分野の話です。ER図・スキーマ・モデル・正規化は、どのくらい定型化されていますか
実務でのデータベース設計におけるER図、スキーマ、モデル、正規化は、一般的に標準化されたアプローチやベストプラクティスに基づいていますが、柔軟性も持っています。以下は、それぞれの要素がどの程度定型化されているかを説明します：
- ER図（Entity-Relationship Diagram）:
- ER図は、データモデリングの初期段階で、ビジネス要件やデータの関係性を視覚化するために使用されます。
- ER図は、一般的にエンティティ、属性、およびエンティティ間の関係を示します。
- 標準的なER図の記法やツールが存在しますが、組織やプロジェクトによっては独自の記法やツールが使われることもあります。
- スキーマ（Schema）:
- データベースのスキーマは、データベース内のテーブル、ビュー、インデックスなどの構造を定義します。
- 一般的に、スキーマはデータベース管理システムに依存しますが、標準的な構造や設計原則があります。
- 例えば、リレーショナルデータベースでは、主キーや外部キーを定義するなどの標準的な手法があります。
- モデル（Model）:
- データベースモデルは、データベースの構造を記述するための形式化された記述です。代表的なものには論理モデルと物理モデルがあります。
- 論理モデルは、ビジネス要件に基づいてデータベースの構造を定義します。ER図がその一例です。
- 物理モデルは、実際のデータベース管理システムでのデータの格納方法やインデックスの構造などを定義します。
- 正規化（Normalization）:
- 正規化は、リレーショナルデータベース設計において、データの冗長性を減らし、データの一貫性を保つためのプロセスです。
- 正規化の手法は、一般的に第1正規形（1NF）から第5正規形（5NF）までがあります。
- 正規化のレベルは、データの性質やアクセスパターン、パフォーマンス要件などに基づいて柔軟に適用されます。
これらの要素は一般的に定型化されたアプローチやベストプラクティスに基づいていますが、個々のプロジェクトや組織によって要件が異なるため、柔軟性があります。組織やプロジェクトは、これらの要素を適切にカスタマイズして、特定の要件や制約に対応することが重要です。
やっぱり柔軟性が大きいですよね（知らんけど）
モデルとスキーマ
モデルースキーマ
概念ー概念
物理ー内部
論理ー外部
スタースキーマ
正規化理論
-
第１正規形
べき集合や直積集合の排除 -
第２正規形
部分関数従属の排除 -
第３正規形
推移関数従属の排除 -
ボイスコッド正規形
すべて（候補キーへ）の部分・推移的関数従属属性の排除 -
第４正規形
多値従属の排除 -
第５正規形
結合従属の排除
トランザクション管理
ACID特性
トランザクションにおいて重要な4つの特性
-
Atomicity(原子性)
すべて実行 OR すべて非実行 -
Consistency(一貫性)
データの矛盾なし -
Isolation(独立性)
複数のトランザクションの順序関係ない -
Durability(永続性)
正常終了したトランザクションの結果は失われない
トランザクションの流れ
コミット：トランザクション結果を確定する処理
アボート：中断した処理をロールバックする処理
トランザクション分離レベル
-
READ UNCOMMITED（読み取り未確定）
コミットしてないデータも取得 -
READ COMMITED（読み取り確定）
コミットした最新のデータを取得 -
REPEATABLE READ（繰り返し読み取り）
読み取り対象のデータが他トランザクションで更新されない -
SERIALIZABLE（直列化可能）
複数のトランザクションの順序関係ない
トランザクションの不整合性（リード現象）
トランザクション１（T1）のせいで、トランザクション２（T2）の読取がおかしくなる
-
ダーティリード
T1[更新] → T2[読取] → T1[ロールバック]
あったかもしれない未来を見た -
ノンリピータブルリード
T2[読取] → T1[更新] → T2[読取]
知らん間に変わっとる
ファジーリードっていう人もいる -
ファントムリード
T2[読取] → T1[挿入または削除] → T2[読取]
存在しないデータを見た
許可と抑止
〇：許可、×：抑止
| RU | RC | RR | S | |
|---|---|---|---|---|
| ダーティ | 〇 | × | × | × |
| ノンリピート | 〇 | 〇 | × | × |
| ファントム | 〇 | 〇 | 〇 | × |
例（RCに設定してたらダーティは抑止できるけど、他は起きる）
バックアップと復元
DBサーバーは、トランザクションやログの管理を行っていないと、ディスク容量が満杯になるまで肥大化していく。
-
オフラインバックアップ
サーバーを停止して、データファイルをコピペする
データクライアントでエクスポート・インポートしたっていい
フルバックアップになる -
オンラインバックアップ
DBサーバーを停止せず、連続的な運用中にデータを保護する
差分または増分バックアップできる -
SQLダンプ
データベースの状態を完全にバックアップする
データベースのスキーマ（テーブル、ビュー、インデックスなど）の定義と、それに含まれるデータをエクスポート
# export
mysqldump -u ユーザ名 -p データベース名 > バックアップファイル.sql
# import
mysql -u ユーザ名 -p データベース名 < バックアップファイル.sql
-
トランザクションログ
DBに加えられた変更を順番に記録したもの
ロールバックとロールフォワード
チェックポイントを用いた障害回復機能
- あらかじめ設定した復旧間隔でチェックポイント（DBのデータを保存）を行う
- 障害発生前にコミットしたトランザクションは、更新後ログを用いてフォワードリカバリーする
- 障害発生にコミットできていないトランザクションは、更新前ログを用いてバックワードリカバリーする
データ分析基盤
-
整形：
データクレンジング：データの重複や誤記などを削除・修正・正規化して、データの品質を高める -
加工：
データマイニング：ビジネスに有益な情報を抽出したり、データの可視化を行ったりし、特定の利用者や用途に応じた形に加工する
ロック
概要
複数のユーザーが同時に利用するDBには欠かせない機能。
同じデータが同時に更新された際の矛盾を回避する機能。
種類
-
排他ロック（占有ロック）（Exclusive Lock）
更新系ステートメント（UPDATE, INSERT, DELETE）を実行する際に、他トランザクションから邪魔されたくない -
共有ロック（Shared Lock）
検索ステートメント（SELECT）を実行する際に、他トランザクションが更新しないでほしい（SELECTは許す） -
更新ロック（Update Lock）
今検索してるけど、この後更新するから、一切邪魔しないでね -
意図ロック（Intention Lock）
複数の粒度レベルでロックするために用いる
トランザクションが後でテーブルの行に必要とするロックのタイプを示すテーブルレベルのロック -
カバレージロック（Covering Lock）
インデックスの範囲をロックする
ロックの粒度
- 行レベルロック：default : トランザクション
- 表レベルロック：ALTER TABLEなど
- データベースレベルロック：DBMSによるロック : バックアップなど
ロックエスカレーション
あるテーブルに多数行のロックがかけられると、自動的に表レベルロックに切り替えること。
- メリット
DBMSの負荷を下げ、メモリも余裕ができる - デメリット
同時に実行できるトランザクション数が減る
デッドロックの原因となる
デッドロック
２つのトランザクションがお互いにロック待ちをしている状態
対策
- トランザクションの時間を短くする
- 同じ順番でロックするようにする
楽観的VS悲観的
楽観/悲観的同時実行 | 楽観/悲観的排他制御 | 楽観/悲観ロック
楽観的：だろう運転：他に更新するヤツおらんやろ、後でチェックしたらいいや
悲観的：かもしれない運転：他に更新するヤツおるかも、念のためブロックしとこ
利用者からしたら絶対悲観的のほうがいいと思うやろ？
悲観的は実装と運用のコスト高いねん
その他キーワード
２フェーズコミット（２相コミット）
- 複数のDBに分けて情報を格納している場合、通常のトランザクションだと整合性を保てない
- 各DBに「確定準備」と「確定」の２段階の指示を出すことで解決
dbt（data build tool）
- データアナリストやアナリティクスエンジニアが（ほぼ）SQLだけで、データを変換しデータウエアハウス、データマートを構築していくことができるツール
代表的な監査機能
- SQL Server Audit
- MySQL Enterprise Audit Plugin
- MySQL General Query Log
- MySQL Binary Log (Binlog)
透過的な暗号化
（Transparent Data Encryption, TDE）
- データベースに保存されるデータを自動的に暗号化する機能
- データベース内のデータが物理的に保存される際に自動的に暗号化され、データがディスクに書き込まれる前やディスクから読み取られる直前に自動的に復号される
- アプリケーション側での変更を最小限に抑えつつ、データの保護を強化でき、データベース管理者は、データベースエンジンの設定を調整することで、データの安全性を確保できる