はじめに
業務でAWSのRDSを触る機会が増えてきたのですが、そもそもRDBについてイマイチ理解できていなかったので、そもそもRDBはどんな特徴があるかについてまとめていきたいと思います。
RDBとは
リレーショナル・データベースの略。
次に上げるACIDと呼ばれるいくつかの特性を持っている。
ACID特性
Atomicity（原子性）
これはよく銀行を例に例えられることも多いのですが、トランザクションの結果矛盾が起きないようにする仕組みです。
例えば口座A、B間で送金のやり取りが発生する場合、以下の２つの処理が行われる。
1.口座Aの残高を−１万円
2.口座Bの残高を＋１万円
上記の送金処理で、1,2両方実施するor1,2両方実施しないのどちらかで無いと実態と差異が発生してしまいます。
処理の結果を保証してくれる仕組みですね。
Consistency（独立性）
これはデータベースの整合性を保証する仕組みです。
RDBではテーブルを作成する際に、そのテーブルのレコードに入る値を予め定義します。
ここで定義した値と別の値が入っている場合、つまり整合性を満たしていないような場合は処理が中断されます。
きちんとルールを決めて、そのルールに沿った設計が必要になります。
Isolation（分離性）
複数のトランザクションが同時に発生した場合でも、それぞれのトランザクションは分離され、他のトランザクションからは処理の過程が隠匿される。
例えば、送金処理のトランザクションで、出金は完了したが入金はこれから実施するといった処理の過程は他のトランザクションからは見えない。
Durability（永続性）
これはトランザクションが完了したらその結果が保存され、システム障害が発生した場合でも失われないという性質。
データ操作のログをストレージに保存しておき、処理中に障害が発生しても記録を元に戻すことができる。
RDSの場合はマルチAZで冗長化されているケースが多く、可用性が高い。
ハンズオン
一旦AWSのRDSに触れてみます。
データベースを作成し、テーブル間のリレーションを手を動かして実施してみます。
今回はある会社のデータベースに、部署と従業員の情報を入れたテーブルをそれぞれ作成し、関連付けしていきます。
- データベースの作成
CREATE DATABASE CompanyDB;
- テーブルの作成
-- 部署テーブル
CREATE TABLE Departments (
DepartmentID INT AUTO_INCREMENT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL
);
-- 従業員テーブル
CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
HireDate DATE NOT NULL,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
上記の部分で、DepartmentsテーブルとEmployeesテーブルのDepartmentIDでリレーションを貼っている。
AUTO_INCREMENTでは、Departmentsの行が増えると自動で数字が加算されていくので、手動で部署にIDのを入れる必要がなくなる。
またRDBでは事前にどのようなデータが入るか定義する必要があるので、NOT NULL,VARCHAR(100)等で定義したデータ以外を入れようとするとエラーになる。
- データを挿入
-- 部署データの挿入
INSERT INTO Departments (DepartmentName)
VALUES ('Engineering'), ('Marketing');
上記でDepartmentsテーブルにデータを挿入する。以下で挿入したデータを確認する。
MySQL [CompanyDB]> SELECT * FROM Departments;
+--------------+----------------+
| DepartmentID | DepartmentName |
+--------------+----------------+
| 1 | Engineering |
| 2 | Marketing |
+--------------+----------------+
2 rows in set (0.00 sec)
ここで注目するのは、DepartmentIDには値を入れていないが、AUTO_INCREMENTによって値が増加しながら追加されている。手動で部署IDを設定するとミスが起こりやすいので、こうして自動でデータを入れてくれるのは頼もしい。。
以下で従業員テーブルにもデータを挿入する。
-- 従業員データの挿入
INSERT INTO Employees (FirstName, LastName, HireDate, DepartmentID)
VALUES
('John', 'Doe', '2023-05-01', 1),
('Jane', 'Smith', '2023-06-15', 2),
('Alice', 'Johnson', '2023-07-20', 2);
以下で確認する。
MySQL [CompanyDB]> select * from Employees;
+------------+-----------+----------+------------+--------------+
| EmployeeID | FirstName | LastName | HireDate | DepartmentID |
+------------+-----------+----------+------------+--------------+
| 4 | John | Doe | 2023-05-01 | 1 |
| 5 | Jane | Smith | 2023-06-15 | 2 |
| 6 | Alice | Johnson | 2023-07-20 | 2 |
+------------+-----------+----------+------------+--------------+
3 rows in set (0.01 sec)
実際にリレーションを通してデータが取得できるか確認してみる。
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
e.HireDate,
d.DepartmentName
FROM
Employees e
LEFT JOIN
Departments d
ON
e.DepartmentID = d.DepartmentID;
+------------+-----------+----------+------------+----------------+
| EmployeeID | FirstName | LastName | HireDate | DepartmentName |
+------------+-----------+----------+------------+----------------+
| 4 | John | Doe | 2023-05-01 | Engineering |
| 5 | Jane | Smith | 2023-06-15 | Marketing |
| 6 | Alice | Johnson | 2023-07-20 | Marketing |
+------------+-----------+----------+------------+----------------+
3 rows in set (0.00 sec)
LEFT JOINでEmployeesテーブルを基準にしていて、DepartmentsテーブルのDepartmentIDが一致している行を結合して表示している。
それによって、Employeesテーブルに部署IDに関連した部門名を結合することができた。
結合の方法は他にもいくつかあるので、どのような結果を得たいかで適宜使い分ける必要がありますね。
終わりに
やはり触れてみないとわからないことが多いですね。。。
今回やった範囲で言えば原理はわかりやすいですが、これがもっと大きなデータが保存されるDBの場合、テーブル設計やどういったSQLでデータを取り出すか等事前にしっかり設計する事が大切だと感じました。
誰かの参考になれば幸いです！