はじめに
データベース設計・構築、そしてクエリ実行までを基礎から一通り学び直しました。プログラミングとはまた異なる「データの整合性」という概念に触れ、多くの発見がありました。
データベース設計は、アプリケーションの品質を決める鍵と言っても過言でなく、ここを疎かにすると、後からデータ矛盾やパフォーマンス低下という大きな代償を払うことになると感じました。
本記事では、私が1から設計・構築して学んだ流れを、備忘録としてまとめました。
前提
- OS: macOS
- DB: MySQL 8.0系
- 対象読者：DB/SQLの基本を学び始めた方
データベース設計・構築の流れ
設計から構築までは、「論理設計」→「物理設計」→「DB構築」というフェーズで進めました。
【論理設計】
1. エンティティ抽出
2. エンティティ定義（属性の洗い出し）
3. 正規化
4. ER図作成
【物理設計】
5. テーブル定義
6. インデックス定義
【データベース構築】
実際にSQLを実行して環境を作る
論理設計
論理設計は、オブジェクト指向のクラスのように全体の設計書を作るイメージで、使うデータ、ルールや条件についてデータベースのテーブルを作ると考えています。
エンティティ抽出
「ユーザー」「商品」「注文」など、システムが扱うデータの「実体（ひとかたまり）」を洗い出します。 これは「部屋の片付け」に似ていて、散らかった情報を箱や棚（エンティティ）に分けて整理整頓します。
エンティティ定義
抽出したエンティティに対し、どのような情報（属性）を持たせるかを決めます。「ユーザー」なら「氏名・メールアドレス」といった具合です。
正規化
データの重複をなくし、整合性を保つためのステップです。いきなり完璧な形は目指せないので、まずは1つの大きな表を作り、そこから段階的に分割していきました。
- 第一正規形：1つのセルに1つの値のみが入っている状態
- 第二正規形：主キー（IDなど）の一部にしか依存していない項目を別テーブルに切り出し、重複を排除
- 第三正規形：主キー以外の項目に依存している項目をさらに切り出す
ER図
正規化したテーブル同士の「つながり」を可視化します。1対1、1対多、多対多（中間テーブルが必要）の関係を明確にすることで、データの構造を明らかにします。
mermaidというツールを使用しました。
物理設計
論理設計で作った図面をもとに、物理的にデータベースへのデータの格納方法や領域をどう実現するか決めます。
テーブル定義
各カラムのデータ型（INT, VARCHARなど）、制約（NOT NULL, UNIQUE）、主キー、オートインクリメントなどを細かく設定します。
インデックス定義
「索引」を作成し、検索を高速化するための設計です。インデックスがないと、DBは数百万行あっても先頭から1行ずつ探す「フルスキャン」を行うため、非常に重くなります。
インデックスを作成すると、あらかじめ並べられた「索引」を使って該当する行の場所を直接特定できるため、調べる量が大幅に減り、高速に検索できます。
インデックスを運用するなら下記のようにすると良いと考えました。
- 対象： 頻繁に検索（WHERE, JOIN）されるカラム、値のバリエーションが多い（カーディナリティが高い）カラム
- 注意： レコードが少ない場合は不要。また、インデックスを貼りすぎると更新処理（INSERT/UPDATE）が遅くなるため、バランスが重要
インデックス自体は、データベース構築後でも設定できるので、必要になったら貼るというのも良いと思いました。
データベース構築
以下の手順で環境を作りました。
1. ユーザー作成
2. データベース作成
3. テーブル作成
4. データ導入
5. クエリ実行
ユーザー作成
セキュリティの観点から、rootユーザーではなく専用ユーザーを作成します。
# rootユーザーログイン
mysql -uroot
# ユーザー作成
CREATE USER user1@localhost IDENTIFIED BY 'password';
# 作成したユーザー確認
SELECT user, host FROM mysql.user;
# 作成したユーザーに権限を付与（学習用なので一旦ALL）
GRANT ALL PRIVILEGES ON *.* TO user1@localhost WITH GRANT OPTION;
# 権限リロード
FLUSH PRIVILEGES;
# rootユーザーログアウト
EXIT;
# 作成したユーザーでログイン
mysql -u user1 -p
password
※ ユーザーへの権限は、本来必要なデータベース・権限のみに絞るべきですが、今回は学習目的のため簡略化しています。
データベース・テーブル作成
テーブル定義書に従い、実際にテーブルを構築します。
# データベースを作成
CREATE DATABASE データベース名;
# 作成したデータベースを確認
SHOW DATABASES;
# 使用するデータベース指定
USE データベース名;
# テーブルを作成(※下記はイメージです)
CREATE TABLE users (
id BIGINT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
PRIMARY KEY (id)
);
# 作ったテーブルを確認
SHOW TABLES;
データ導入
必要となるデータを投入します。
# 下記は例となるクエリです。
INSERT INTO users VALUES
(1, 'taro', 'taro@email.com'),
(2, 'kota', 'kota@email.com'),
(3, 'natsumi', 'natsumi@email.com'),
(4, 'shingo', 'shingo@email.com'),
(5, 'hikaru', 'hikaru@email.com');
# 導入したデータを確認
SELECT * FROM テーブル名;
クエリ実行
テーブル結合するなどして必要なテーブルからデータを取れるか確認すると良さそうです。
下記はイメージです。
SELECT
t1.title "テーブル1タイトル",
t2.name "テーブル2名前"
FROM table1 t1
INNER JOIN table2 t2
ON t1.table2_id = t2.id
LIMIT 5;
学んで印象に残ったこと
実際に手を動かしてみて、特に印象に残った点をいくつか挙げます。
- 実行計画：EXPLAIN
- SQLスタイルガイド
- ロックとトランザクション
実行計画：EXPLAIN
EXPLAINを使うと、自分の書いたクエリが「どう実行されるか」を見ることができます。全件スキャン（type: ALL）になっているのを見つけ、インデックスを貼ることでインデックスを利用した検索に改善される様子を確認できた時は、パフォーマンス改善の面白さを実感しました。
イメージ例：test_db 通常クエリ
EXPLAIN ANALYZE SELECT * FROM salaries WHERE salary = 70575;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (salaries.salary = 70575) (cost=283348 rows=281835) (actual time=14.4..6662 rows=56 loops=1)
-> Table scan on salaries (cost=283348 rows=2.82e+6) (actual time=13.4..5746 rows=2.84e+6 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (6.66 sec)
イメージ例：test_db 爆速クエリ INDEX設定
EXPLAIN ANALYZE SELECT * FROM salaries WHERE emp_no = 10100;
+-------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on salaries using PRIMARY (emp_no=10100) (cost=1.75 rows=15) (actual time=6.82..8.88 rows=15 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
SQLスタイルガイド
クエリが長くなると可読性が落ちます。インデントや改行のルールを統一するSQLスタイルガイドの存在を知り、読みやすいクエリを書くことを意識したいと思いました。
ロックとトランザクション
これは実際に体験すると理解が早かったです。 「ターミナルを2つ開き、同時に同じデータを更新しようとすると、一方が待たされる（ロックがかかる）」という挙動を試し、データの一貫性が守られる仕組みを肌で感じることができました。
イメージ例：どちらもトランザクションを開始してるため実行されない
USE employees;
BEGIN;
UPDATE salaries
SET salary = 70000
WHERE emp_no = 10001
AND from_date = '1986-06-26';
USE employees;
BEGIN;
UPDATE salaries
SET salary = salary * 2
WHERE emp_no = 10001
AND from_date = '1986-06-26';
おわりに
1からサービスを作る際、データベース設計は決して避けて通れない最重要課題だと痛感しました。
特に論理設計（正規化）を適当に済ませてしまうと、後から正しいデータが取れず、開発の終盤で痛い目に遭うと思いました。
設計に迷ったときは、自分の考えが正しいかどうかAIと「壁打ち」しながら納得のいくテーブル構造を追求することが大切だと学びました。また、設計の段階からどんなクエリを試すか想定しておくと設計の質の向上も見込めると感じました。
今回の学びを活かし、パフォーマンスと整合性を意識してデータベース設計をしていきたいです。
参考