[入門]DB設計
今回は DBの設計からER図、トランザクションの対応まで幅広く概念学習ができればと思います。
対象読者
- DB設計を始める方
- DBの学習の全体像を把握したい方
データベースの関係
- 1 : 多
- 多 : 多
- 1 : 1
DB を設計するときに上記を 念頭に入れてどのタイプになるのかをまず考慮する必要があります。
ER図
ER図とは「E＝エンティティ(実体)」「R＝リレーションシップ(関連)」を用いたデータモデル図です。
どのテーブルとどのテーブルが どんな関係性にあるのかを可視化します。
覚える記号
| 記号 | 意味 |
|---|---|
| ○ | 0（ゼロ） |
| l | 1（イチ） |
| 鳥の足 | 多 |
上記のように、 記号を用いてDBの関係性を図示します。
多 : 多 の場合は注意
多 : 多の時は DB設計は複雑になりがちです。
そのため 中間テーブルを作成する必要があります。
例えば、 学生とコースを例に説明します。
前提は以下です。
- 一人の学生は複数のコースを受講できる。
- 一つのコースには複数の学生が参加できる。
この場合、学生とコースの間には多対多の関係があります。これを表現するために、中間テーブルを使用します。
Students テーブル
| StudentID | StudentName |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
Courses テーブル
| CourseID | CourseName |
|---|---|
| 101 | Mathematics |
| 102 | Physics |
| 103 | Computer Science |
中間テーブル: Enrollment テーブル
| EnrollmentID | StudentID | CourseID |
|---|---|---|
| 1 | 1 | 101 |
| 2 | 1 | 102 |
| 3 | 2 | 101 |
| 4 | 3 | 103 |
| 5 | 2 | 103 |
データの関係
- Alice は Mathematics (101) と Physics (102) を受講しています。
- Bob は Mathematics (101) と Computer Science (103) を受講しています。
- Charlie は Computer Science (103) を受講しています。
上記より、 StudentIDと CourseIDをEnrollmentID で管理する中間テーブルにより、複雑性が緩和されました。
多 : 多 のDB パターンにおける 定石です。
正規化
また、DB を設計するときに、テーブルのカラムが多くなってくると
情報が重複する部分がでて来ます。
例えば以下のような Order を例に考えます。
Orders テーブル
| OrderID | CustomerID | CustomerName | CustomerAddress | ProductID | ProductName | Quantity | Price |
|---|---|---|---|---|---|---|---|
| 1 | 101 | John Doe | 123 Elm St. | 201 | Widget A | 2 | 20 |
| 2 | 102 | Jane Smith | 456 Oak St. | 202 | Widget B | 1 | 15 |
| 3 | 101 | John Doe | 123 Elm St. | 203 | Widget C | 3 | 30 |
このテーブルには冗長なデータ（例えば、John Doeの名前と住所）が含まれています。これを正規化していきます。
第1正規形（1NF）
第1正規形では、各列が単一値であることを求めます。上記のテーブルは既に1NFに従っていますが、念のため確認します。
第2正規形（2NF）
第2正規形では、部分関数従属を排除します。これは、非キー属性がすべて主キー全体に完全に依存するようにします。ここでは、顧客情報と注文情報を分けます。
Customers テーブル
| CustomerID | CustomerName | CustomerAddress |
|---|---|---|
| 101 | John Doe | 123 Elm St. |
| 102 | Jane Smith | 456 Oak St. |
Orders テーブル
| OrderID | CustomerID | ProductID | Quantity | Price |
|---|---|---|---|---|
| 1 | 101 | 201 | 2 | 20 |
| 2 | 102 | 202 | 1 | 15 |
| 3 | 101 | 203 | 3 | 30 |
第3正規形（3NF）
第3正規形では、推移的関数従属を排除します。ここでは、製品情報を分けます。
Products テーブル
| ProductID | ProductName |
|---|---|
| 201 | Widget A |
| 202 | Widget B |
| 203 | Widget C |
Orders テーブル（修正後）
| OrderID | CustomerID | ProductID | Quantity | Price |
|---|---|---|---|---|
| 1 | 101 | 201 | 2 | 20 |
| 2 | 102 | 202 | 1 | 15 |
| 3 | 101 | 203 | 3 | 30 |
第4正規形（4NF）と第5正規形（5NF）
さらに突き詰める。
最終結果
最終的に、以下のようにデータが整理されます。
Customers テーブル
| CustomerID | CustomerName | CustomerAddress |
|---|---|---|
| 101 | John Doe | 123 Elm St. |
| 102 | Jane Smith | 456 Oak St. |
Products テーブル
| ProductID | ProductName |
|---|---|
| 201 | Widget A |
| 202 | Widget B |
| 203 | Widget C |
Orders テーブル
| OrderID | CustomerID | ProductID | Quantity | Price |
|---|---|---|---|---|
| 1 | 101 | 201 | 2 | 20 |
| 2 | 102 | 202 | 1 | 15 |
| 3 | 101 | 203 | 3 | 30 |
これにより、データの冗長性が削減され、一貫性が保たれます。
→ 縦と横でここはグループできるのではないかといった感じで全体を俯瞰してからやると効率的です。
テーブル設計
要件を確認する
システムの要件を明確にするために、次の質問に答えます。
- どのようなデータを保存する必要がありますか？
- そのデータはどのように使用されますか？
- 誰がデータにアクセスしますか？
テーブルの概要を設計する
要件に基づいて、データの概念モデルを作成します。この段階では、テーブル（例えば、顧客、注文、製品など）とそれらの関係を定義します。このモデルはER図として視覚化されます。
テーブルの詳細を設計する
-
各テーブルのカラムを追加する。
-
各テーブルの型を追加する。
-
キーを設定。
-
その後、実際に作成しマイグレーションなどを行う。
テーブルの型 とは？
データベーステーブルのカラムに型をつける方法について説明します。適切なデータ型を選択することは、データの効率的な保存と取得にとって非常に重要です。
1. データ型の選択
1.1 数値型
- INT: 整数を保存します。通常、4バイトのメモリを使用し、-2,147,483,648から2,147,483,647までの範囲を取ります。
- SMALLINT: 小さな整数を保存します。通常、2バイトのメモリを使用し、-32,768から32,767までの範囲を取ります。
- BIGINT: 大きな整数を保存します。通常、8バイトのメモリを使用し、-9,223,372,036,854,775,808から9,223,372,036,854,775,807までの範囲を取ります。
- FLOAT: 浮動小数点数を保存します。通常、4バイトのメモリを使用し、精度に制限があります。
- DOUBLE: 浮動小数点数を保存しますが、FLOATよりも高精度です。通常、8バイトのメモリを使用します。
- DECIMAL: 高精度の固定小数点数を保存します。精度と小数点以下の桁数を指定できます。
1.2 文字列型
-
CHAR(n): 固定長の文字列を保存します。nは文字の長さを指定し、最大255文字まで保存できます。スペースで埋められます。
-
VARCHAR(n): 可変長の文字列を保存します。nは最大文字数を指定し、最大255文字まで保存できます。実際の文字数に応じたメモリを使用します。 256になるとindex が張れない問題がでるため255 までとします。
-
TEXT: 長い文字列を保存します。通常、最大65,535文字まで保存できます。
1.3 日付・時刻型
-
DATE: 年、月、日を保存します。
-
TIME: 時、分、秒を保存します。
-
DATETIME: 年、月、日、時、分、秒を保存します。
-
TIMESTAMP: UNIXタイムスタンプを保存します。特定のタイムゾーンに依存しない形式です。
1.4 その他
-
BOOLEAN: 真偽値（TRUEまたはFALSE）を保存します。
-
BLOB: バイナリデータを保存します。画像やファイルなどの大きなデータを格納するのに使用します。
2. データ型の定義とカラム制約
2.1 主キー（PRIMARY KEY）
主キーはテーブル内の各行を一意に識別するためのカラムです。主キーにはNULL値を含めることができません。
2.2 外部キー（FOREIGN KEY）
外部キーは、他のテーブルの主キーに対応するカラムです。データベースの整合性を保つために使用します。
2.3 NULLとNOT NULL
- NULL: カラムに値が存在しない場合を示します。NULL値は、データがまだ設定されていない場合や不明な場合に使用されます。
- NOT NULL: カラムに必ず値が設定されていることを保証します。この制約を設定すると、NULL値を許可しません。
2.4 デフォルト値（DEFAULT）
デフォルト値は、INSERT文で値が指定されなかった場合にカラムに設定される値です。
※ 以下は可能な限りつける
- NOT NULL 制約 : メモなどで書いてもかかなくてもいいときは NULLを許容する。
- ユニークキー制約 : 店舗の名前とかかぶってはいけないもの。
- 外部キー制約 : リレーション先にレコードがあることを保障する。
トランザクションの詳細
1. トランザクションとは
トランザクションは、一連のデータベース操作を一つの論理的な単位として扱うための概念です。トランザクションは、すべての操作が成功するか、あるいはすべての操作が失敗するかのどちらかであることを保証します。これにより、データの整合性と一貫性が保たれます。
2. トランザクションの特性（ACID特性）
トランザクションは、次の4つの特性（ACID特性）を満たす必要があります。
2.1 原子性（Atomicity）
原子性は、トランザクション内のすべての操作がすべて実行されるか、あるいはまったく実行されないことを保証します。これは、「オールオアナッシング」の原則とも呼ばれます。例えば、銀行の口座間での資金移動では、送金元の口座からの引き落としと送金先の口座への入金が両方とも成功するか、どちらも失敗するかのいずれかです。
2.2 一貫性（Consistency）
一貫性は、トランザクションが終了した後にデータベースが一貫した状態にあることを保証します。これは、データの整合性制約が常に満たされることを意味します。例えば、銀行の口座の残高は負の値を取らないという制約がある場合、トランザクション後もこの制約が維持されます。
2.3 独立性（Isolation）
独立性は、複数のトランザクションが同時に実行されても、それらが相互に干渉しないことを保証します。これは、トランザクションが互いに独立して実行されることを意味します。例えば、2つのトランザクションが同じデータにアクセスしても、互いの影響を受けずに実行されます。
2.4 永続性（Durability）
永続性は、トランザクションが完了した後、その結果が永続的に保存されることを保証します。これは、システム障害が発生してもデータが失われないことを意味します。例えば、銀行のトランザクションが完了した後、システムがクラッシュしても取引の結果は失われません。
図示するなら こちらの画像がわかりやすいです。参考サイト
データベースロック
1. ロックとは
ロックは、データベースにおいて複数のトランザクションが同時にデータにアクセスする際にデータの整合性と一貫性を保つためのメカニズムです。ロックを使用することで、データの同時更新による競合や不整合を防ぐことができます。
2. ロックの種類
2.1 排他ロック（Exclusive Lock, X Lock）
排他ロックは、特定のリソース（例えば、テーブルや行）に対して排他アクセスを許可します。排他ロックがかけられたリソースには、他のトランザクションがアクセスできません。排他ロックはデータの更新時に使用されます。
2.2 共有ロック（Shared Lock, S Lock）
共有ロックは、特定のリソースに対して複数のトランザクションが同時に読み取りアクセスを許可します。共有ロックがかけられたリソースには、他のトランザクションが共有ロックをかけてアクセスできますが、排他ロックはかけられません。共有ロックはデータの読み取り時に使用されます。
2.3 意図ロック（Intent Lock）
意図ロックは、テーブル全体に対するロックの意思を示すために使用されます。意図共有ロック（ISロック）と意図排他ロック（IXロック）の2種類があります。
- 意図共有ロック（Intent Shared Lock, IS Lock）: テーブル内の特定の行に共有ロックをかける前にテーブル全体にかけるロック。
- 意図排他ロック（Intent Exclusive Lock, IX Lock）: テーブル内の特定の行に排他ロックをかける前にテーブル全体にかけるロック。
3. ロックの粒度
ロックの粒度は、ロックがかけられるデータの範囲を指します。ロックの粒度には、次のような種類があります。
3.1 行ロック（Row-level Lock）
行ロックは、テーブル内の特定の行に対してかけられるロックです。最も細かい粒度のロックであり、同時実行性が高くなりますが、ロック管理のオーバーヘッドが大きくなります。
3.2 ページロック（Page-level Lock）
ページロックは、データベースのページ（通常は8KBまたは16KBの固定サイズのブロック）に対してかけられるロックです。行ロックよりも粒度が粗く、テーブルロックよりも細かいです。
3.3 テーブルロック（Table-level Lock）
テーブルロックは、テーブル全体に対してかけられるロックです。最も粗い粒度のロックであり、同時実行性が低くなりますが、ロック管理のオーバーヘッドが小さくなります。
4. デッドロック
デッドロックは、2つ以上のトランザクションが互いに相手のロックを待ち続ける状態です。デッドロックが発生すると、トランザクションは無期限に待機状態になり、処理が進行しません。デッドロックを検出して解消するためには、次のような方法があります。
4.1 タイムアウト
トランザクションが一定時間以上ロックを待機している場合に、タイムアウトを設定してトランザクションを中止します。
4.2 ウェイトフォーグラフ（Wait-For Graph）
デッドロックを検出するために、トランザクション間のロック依存関係をグラフで表現し、循環が存在するかどうかをチェックします。循環が存在する場合、デッドロックが発生していると判断します。
N + 1 問題
N + 1問題は、データベースアクセスにおけるパフォーマンス問題の一つです。この問題は、あるエンティティを取得した後、そのエンティティに関連するN個のエンティティをそれぞれ個別に取得する場合に発生します。結果として、N+1回のデータベースクエリが実行され、パフォーマンスが大幅に低下します。
例えば、ブログ記事とそのコメントを取得する場合を考えます。1つのブログ記事を取得し、その記事に関連するコメントを取得するために、追加でN回のクエリが実行される場合、N+1問題が発生します。
また、 上記のように親子関係のある DB でよく発生している印象があります。
Blogs テーブル
| BlogID | Title | Content |
|---|---|---|
| 1 | Blog Title 1 | Blog Content 1 |
| 1 | Blog Title 2 | Blog Content 2 |
Comments テーブル
| BlogID | Title | Content |
|---|---|---|
| 1 | 1 | Comment Content 1 |
| 2 | 1 | Comment Content 2 |
| 3 | 2 | Comment Content 3 |
クエリの例
ブログ記事とそのコメントを取得する際の非効率なクエリ例を示します。
- ブログ記事を取得
SELECT * FROM Blogs WHERE BlogID = 1;
- 各ブログ記事に対してコメントを取得
SELECT * FROM Comments WHERE BlogID = 1;
これにより、ブログ記事がN個ある場合、ブログ記事を取得するための1回のクエリと、それぞれのブログ記事に対してコメントを取得するためのN回のクエリ、合計でN+1回のクエリが実行されます。
解決策
ジョインクエリで関連するデータを一度に取得してしまい、クエリの実行回数を抑えることが重要
SELECT b.*, c.*
FROM Blogs b
LEFT JOIN Comments c ON b.BlogID = c.BlogID
WHERE b.BlogID = 1;
Discussion