第13章 テーブル設計
正規化
正規化には主に以下の5つの段階、または「正規形」があります。
- 第一正規形 (1NF): 各セルが単一の値を持ち、各レコードが一意のキーによって識別できる状態。
- 第二正規形 (2NF): 1NFを満たし、さらに部分関数従属（一部のキーに依存する属性）が存在しない状態。
- 第三正規形 (3NF): 2NFを満たし、さらに推移的関数従属（他のカラムを経由してキーに依存する属性）が存在しない状態。
- ボイス・コッド正規形 (BCNF): 3NFを満たし、さらにすべての決定従属が候補キーからのものだけである状態。
- 第四正規形 (4NF): BCNFを満たし、さらにマルチ値依存（あるカラムの値が他のカラムの値のセットに依存する状態）が存在しない状態。
一般的に、データベース設計においては最低でも第三正規形まで達成することが推奨されています。ただし、パフォーマンス向上や特定の要件を満たすために、意図的に正規化を緩和（非正規化）することもあります。
第一正規形 (1NF)
第一正規形（1NF）は、データベースの正規化における基本的なレベルで、以下の2つの基準を満たす必要があります。
1. 各セル（フィールド）は単一の値を持つ
2. 各レコードは一意のキーによって識別できる
たとえば、以下のような「Employee」テーブルがあるとします。
| ID | Name | |
|---|---|---|
| 1 | John Doe | john.doe@example.com |
| 2 | Jane Doe | jane.doe@example.com, jane.doe@company.com |
上記のテーブルでは、Jane Doeのメールアドレスフィールドが2つの値を持っています。これは1NFの基準を満たしていません。
この問題を解決するために、各セルに単一の値を持たせるようにテーブルを改善します。
| ID | Name | |
|---|---|---|
| 1 | John Doe | john.doe@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Jane Doe | jane.doe@company.com |
それぞれのレコードは一意のIDにより識別でき、各セルは単一の値を持つようになりました。したがって、このテーブルは第一正規形（1NF）を満たしています。
第二正規形 (2NF)
データベースの正規化における第二正規形（2NF）は、以下の2つの基準を満たす必要があります。
1. テーブルは第一正規形（1NF）を満たす
2. 非キー列は完全にキー（主キーまたは複合キー）に依存する
たとえば、以下のようなOrdersテーブルがあるとします。
| OrderID | ProductID | ProductName | CustomerID |
|---|---|---|---|
| 1 | 200 | Apples | 123 |
| 2 | 201 | Bananas | 124 |
| 3 | 202 | Grapes | 125 |
上記のテーブルでは、ProductName（非キー列）がProductID（キー）に部分的に依存しています。これは2NFの基準を満たしていません。
この問題を解決するために、部分的依存を排除するためにテーブルを分割します。
Ordersテーブル
| OrderID | ProductID | CustomerID |
|---|---|---|
| 1 | 200 | 123 |
| 2 | 201 | 124 |
| 3 | 202 | 125 |
Productsテーブル
| ProductID | ProductName |
|---|---|
| 200 | Apples |
| 201 | Bananas |
| 202 | Grapes |
こうすることで、全ての非キー列が完全にキーに依存するようになり、テーブルは第二正規形（2NF）を満たすようになります。
第三正規形（3NF）
データベースの正規化における第三正規形（3NF）は、以下の基準を満たす必要があります。
1. テーブルは第二正規形（2NF）を満たす
2. 非キー列が他の非キー列に依存しない
たとえば、以下のようなCustomersテーブルがあるとします。
| CustomerID | CustomerName | CustomerAddress | City | CityPostalCode |
|---|---|---|---|---|
| 200 | John | 123 Apple St. | London | 10000 |
| 201 | Jane | 456 Banana Rd. | York | 20000 |
| 202 | Bob | 789 Grape Ave. | Leeds | 30000 |
上記のテーブルでは、CityPostalCode（非キー列）がCity（もう一つの非キー列）に依存しています。これは3NFの基準を満たしていません。
この問題を解決するために、推移的依存を排除するためにテーブルを分割します。
Customersテーブル
| CustomerID | CustomerName | CustomerAddress | City |
|---|---|---|---|
| 200 | John | 123 Apple St. | London |
| 201 | Jane | 456 Banana Rd. | York |
| 202 | Bob | 789 Grape Ave. | Leeds |
Citiesテーブル
| City | CityPostalCode |
|---|---|
| London | 10000 |
| York | 20000 |
| Leeds | 30000 |
こうすることで、全ての非キー列が他の非キー列に依存しないようになり、テーブルは第三正規形（3NF）を満たすようになります。
ボイス・コッド正規形 (BCNF)
ボイス・コッド正規形 (BCNF: Boyce-Codd Normal Form) は、第三正規形 (3NF) の一種で、より厳格な制約が加えられています。BCNF を満たすためには以下の条件を満たす必要があります。
1. テーブルは第三正規形 (3NF) を満たしている。
2. すべての決定従属（つまり、ある列の値が他の列の値を一意に決定する）は、スーパーキー（テーブルの行を一意に特定するためのキーまたはキーの組み合わせ）によるものである。
以下に具体的な例を示します。
Coursesテーブル
| CourseID | Professor | DeptCode |
|---|---|---|
| 1 | Smith | CS |
| 2 | Johnson | EE |
| 3 | Smith | MA |
このテーブルでは、教授（Professor）と学科コード（DeptCode）の組み合わせが一意なコース（CourseID）を決定します。しかし、教授（Professor）と学科コード（DeptCode）の組み合わせ全体はスーパーキーではないため、このテーブルは BCNF を満たしていません。
この問題を解決するためには、テーブルを分割して以下のようにすることが一つの方法です。
Coursesテーブル
| CourseID | ProfessorID |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
Professorsテーブル
| ProfessorID | Professor | DeptCode |
|---|---|---|
| 1 | Smith | CS |
| 1 | Smith | MA |
| 2 | Johnson | EE |
このようにテーブルを分割することで、すべての決定従属がスーパーキーによるものとなり、テーブルは BCNF を満たすようになります。ただし、BCNF を適用することで、テーブルの冗長性は減少しますが、同時にテーブル間の関係が複雑になることもありますので、実際の適用には注意が必要です。
第四正規形（4NF）
第四正規形（4NF）は、マルチ値従属性（複数の値が他の値に依存すること）を取り扱うための正規形です。第四正規形は、以下の条件を満たす場合に達成されます。
1. テーブルはボイス・コッド正規形(BCNF)を満たしている。
2. テーブルにマルチ値依存が存在しない。つまり、ある列のセットが別の列のセットに依存している場合、その依存が完全に機能的であること。
以下に具体的な例を示します。以下のテーブルを考えてみましょう。
Studentsテーブル
| StudentID | Course | Hobby |
|---|---|---|
| 1 | Math | Painting |
| 1 | Science | Painting |
| 1 | Math | Photography |
| 2 | Math | Stamp collecting |
このテーブルでは、生徒(StudentID)ごとに、取っている科目(Course)と趣味(Hobby)があります。しかし、科目と趣味は互いに関連性がなく、独立しています。ここでマルチ値従属性が生じています。つまり、生徒のIDに対して、取っている科目と趣味が依存していますが、科目と趣味が互いに依存しているわけではありません。
このような場合、テーブルは第四正規形を満たしていないと言えます。このテーブルを第四正規形にするためには、以下のようにテーブルを2つに分割することが一つの解決策となります。
Student_Coursesテーブル
| StudentID | Course |
|---|---|
| 1 | Math |
| 1 | Science |
| 2 | Math |
Student_Hobbiesテーブル
| StudentID | Hobby |
|---|---|
| 1 | Painting |
| 1 | Photography |
| 2 | Stamp collecting |
これにより、各テーブルは第四正規形を満たすようになります。
インデックス
インデックスはデータベースにおいてデータの検索速度を向上させるための重要なツールであり、本の目次のようなものです。ある特定のカラム（または複数のカラム）の値を基にして、それが存在する行の位置を効率的に見つけ出すことができます。
インデックスには主にB-TreeインデックスとHashインデックスがあります。
B-Treeインデックスは最も一般的であり、等価検索と範囲検索に効果的で、Hashインデックスは等価検索には非常に効果的ですが、範囲検索には使用できません。
Hashインデックスは一部のストレージエンジン(MEMORYとNDB)でのみ使用する事ができ、B-Treeインデックスはその他のストレージエンジン(InnoDBやMyISAMなど)で使用する事ができます。
インデックスの作成方法
B-Treeインデックス
CREATE INDEX idx_lastname ON employees(last_name);
HASHインデックス
CREATE HASH INDEX idx_lastname ON employees_memory(last_name);
インデックスを使用すると、データの検索とソートが速くなります。大量のデータがあるテーブルにおいて、特定のカラムで頻繁に検索を行う場合、インデックスは大きなパフォーマンス向上をもたらすことがあります。
インデックスの副作用
- インデックス自体もディスクスペースを消費します。大きなテーブルでは、インデックスのサイズも非常に大きくなる可能性があります。
- データの追加、削除、更新時にはインデックスも更新する必要があります。これにより、データの変更が遅くなる可能性があります。
- 適切にインデックスを選択しないと、意図しない性能低下を引き起こすこともあります。たとえば、カーディナリティ（値のユニークさ）が低いカラムをインデックス化すると、パフォーマンスが低下する可能性があります。
したがって、インデックスは注意深く設計し、必要に応じて適切な性能テストを行うことが重要です。
データ型
MySQLでは、様々なデータ型が用意されており、それぞれのデータ型は特定の種類のデータを格納するために最適化されています。データ型はテーブルの各列に対して定義され、それぞれの列は定義されたデータ型に従ってデータを格納します。
いくつかの基本的なデータ型を下記に示します
| 種別 | データ型 | 備考 | |
|---|---|---|---|
| 整数型 | TINYINT | 符号付き | -128～127 |
| 符号なし | 0～255 | ||
| 整数型 | SMALLINT | 符号付き | -32,768～32,768 |
| 符号なし | 0～65,535 | ||
| 整数型 | MEDIUMINT | 符号付き | -8,388,608～8,388,607 |
| 符号なし | 0～16,777,215 | ||
| 整数型 | INT | 符号付き | -2,147,483,648～2,147,483,647 |
| 符号なし | 0～4,294,967,295 | ||
| 整数型 | BIGINT | 符号付き | -9,223,372,036,854,775,808～9,223,372,036,854,775,807 |
| 符号なし | 0～18,446,744,073,709,551,615 | ||
| 固定小数点数型 | DECIMAL | 符号付き | DECIMAL(M, D)として精度M、小数点以下桁数(D)。Mの最大値は65、Dの最大値は30 |
| 符号なし | 負の値は使用不可 | ||
| 固定小数点数型 | DECIMAL | 符号付き | DECIMALと同義 |
| 符号なし | DECIMALと同義 | ||
| 浮動小数点数型 | FLOAT | 符号付き | -3.402823466e+38～-1.175494351e-38、0、1.175494351e-38～3.402823466e+38 |
| 符号なし | 負の値は使用不可 | ||
| 浮動小数点数型 | DOUBLE | 符号付き | -1.7976931348623157e+308～-2.2250738585072014e-308、0、2.2250738585072014e-308～1.7976931348623157e+308 |
| 符号なし | 負の値は使用不可 | ||
| ビット値型 | BIT | BIT(M)としてビット値M。Mの最大値は64 | |
| 日付型 | DATE | "1000-01-01"～"9999-12-31" | |
| 日時型 | DATETIME | "1000-01-01 00:00:00.000000"～"9999-12-31 23:59:59.999999" | |
| タイムスタンプ型 | TIMESTAMP | "1970-01-01 00:00:01.000000"～"2038-01-19 03:14:07.999999" | |
| 時間型 | TIME | "-838:59:59.000000"～"838:59:59.000000" | |
| 文字列型 | CHAR | CHAR(M)でMは文字数。Mの最大値は255。固定長文字列 | |
| 文字列型 | VARCHAR | VARCHAR(M)でMは文字数。Mの最大値は65,535。可変長文字列 | |
| 文字列型 | BINARY | BINARY(M)でMはバイト数。Mの最大値は255。固定長バイナリバイト文字列 | |
| 文字列型 | TEXT | TEXT(M)でMは文字数。Mの最大値は65,535。 | |
| バイナリデータ型 | BLOB | BLOB(M)でMはバイト数。Mの最大値は65,535。 | |
| ENUM型 | ENUM | 最大65,535個の要素 | |
| SET型 | SET | 最大64個の要素 | |
| JSON型 | JSON | 4,294,967,295バイト(実効値は約1GB) | |
| 地理空間データ型 | GEOMETRY | ポイント、ラインストリング、ポリゴン、マルチポイント、マルチラインストリング、マルチポリゴンなど | |
主キーと外部キー
MySQLでは、「主キー（Primary Key）」と「外部キー（Foreign Key）」は、テーブル間のリレーションを制御し、データの一貫性と整合性を維持するために使用されます。
主キー（Primary Key）
主キーはテーブルの各レコードを一意に識別するために使用されます。主キーはテーブルの中で一意でなければならず、NULL値を持つことはできません。1つのテーブルには1つの主キーしか存在できませんが、その主キーは複数のカラム（複合主キー）から構成されることがあります。
例えば、次のステートメントでテーブルを作成し、'id'カラムを主キーとして指定できます。
CREATE TABLE Employees (
id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (id)
);
外部キー（Foreign Key）
外部キーはあるテーブルのカラムが、他のテーブルの主キーを参照していることを示します。これにより、リレーショナルデータベースの2つのテーブル間のリレーションを作成します。外部キー制約は、参照整合性を維持するために使用され、参照するテーブルの主キーに存在しない値を外部キーに挿入することを防ぎます。
例えば、次のステートメントでテーブルを作成し、department_idカラムを外部キーとして指定し、Departmentsテーブルのidカラムを参照しています。
CREATE TABLE Employees (
id INT AUTO_INCREMENT,
name VARCHAR(100),
department_id INT,
PRIMARY KEY (id),
FOREIGN KEY (department_id) REFERENCES Departments(id)
);
この例では、Employeesテーブルのdepartment_idカラムはDepartmentsテーブルのidカラムを参照しています。これにより、Employeesテーブルのdepartment_idカラムには、Departmentsテーブルのidカラムに存在する値のみが挿入されます。
パーティショニング
MySQLのパーティショニングは、テーブルを複数の部分（パーティション）に分割し、それぞれを独立したテーブルのように扱う機能です。パーティショニングは、大量のデータを効率的に管理するための手段であり、クエリパフォーマンスの向上やデータ管理の容易性を提供します。
- RANGEパーティショニング：この種類は、指定した範囲に基づいてパーティションを分割します。範囲は連続する必要があり、重複することはできません。
- LISTパーティショニング：この種類は、リストで指定した値に基づいてパーティションを分割します。値は任意の値を指定でき、重複することはできません。
- HASHパーティション：この種類は、指定した列のハッシュ値に基づいてパーティションを分割します。この結果、データは均等に分散されます。
- KEYパーティション：この種類は、MySQLの内部ハッシュ関数に基づいてパーティションを分割します。この結果、データは均等に分散されます。
例えば、日付に基づいてデータをパーティショニングする場合のサンプルは以下のようになります。ここでは RANGE パーティショニングを使用しています。
CREATE TABLE orders (
order_id INT NOT NULL,
product_id INT NOT NULL,
order_date DATE NOT NULL,
quantity INT NOT NULL
)
PARTITION BY RANGE( YEAR(order_date) ) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN (2022),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
この例では、ordersテーブルを作成し、order_dateカラムの年に基づいてテーブルをパーティション化しています。各パーティションは、特定の年の注文を格納します。例えば、パーティションp1は2021年より前のすべての注文（つまり2020年の注文）を格納します。
パーティションを指定した取得
MySQLでは、特定のパーティションからデータを取得するために、SELECTクエリのFROM句で直接パーティションを指定することができます。これは PARTITION (partition_name) の形式で使用します。
たとえば、次のようなクエリは orders テーブルの p3 パーティションからすべての行を取得します。
SELECT * FROM orders PARTITION (p3);
パーティションの追加
既存のパーティションテーブルにパーティションを追加するには、ALTER TABLEステートメントのADD PARTITIONを使用します。以下に具体例を示します。
ALTER TABLE orders ADD PARTITION (PARTITION p4 VALUES LESS THAN (2023));
この例では、2023年より前の注文を格納する新たなパーティションp4をordersテーブルに追加しています。
パーティションの削除
既存のパーティションテーブルからパーティションを削除するにはALTER TABLEステートメントのDROP PARTITIONを使用します。以下に具体例を示します。
ALTER TABLE orders DROP PARTITION p0;
この例では、パーティションp0をordersテーブルから削除しています。パーティションを削除すると、そのパーティションに格納されている全てのデータも削除されますので、操作は注意が必要です。
パーティショニングを使用すると、特定のパーティションのみに対してクエリを実行することができ、全体のテーブルスキャンの必要性を回避することが可能になります。これにより、特に大量のデータを扱う場合にパフォーマンスが向上します。ただし、パーティショニングの設計と管理は注意が必要です。パーティションの数が多すぎると、その管理が困難になる可能性があります。また、適切なパーティションキーを選択しないと、パフォーマンスが低下する可能性があります。