ROLLBACKしたのに戻らないことがあります。トランザクションとACIDを、実際に落として確かめます。
これは【深掘り】の記事です。本線の順路ではありません。 このシリーズは言葉を覚える回にしません。 全部、手元で立てて、打って、出力を見ます。 飛ばしても本線は進めます。 本線側の「トランザクションと ACID」は 全部やるか、1つもやらないか — トランザクションとACID にあります。
この記事のコマンドは、VMの中(Ubuntu Server)で打ちます。 Windows の PowerShell ではありません。VM の立て方は VMを29台溜めた話 — Ubuntu Serverの立て方 にあります。
この記事では、既にあるデータベースを一切触りません。 練習用の MySQL を Docker Compose で1つ立てて、その中だけで壊します。 作業場所は
~/cslab/dbthの1フォルダだけで、最後にdocker compose down -vで中身ごと全部消します。 本番のDBや、自分のサイトのDBには絶対に繋がないでください。 この記事は「わざと壊す」ことが目的です。
「ROLLBACK すれば戻る」と思っていました。
移行スクリプトを1本書いていました。やることは3つです。
- 新しい列を足す(
ALTER TABLE) - 既存の行を新しい列に埋める(
UPDATE) - 古い行のうち要らないものを消す(
DELETE)
心配だったので、全体をトランザクションで囲みました。 START TRANSACTION で始めて、 途中でおかしくなったら ROLLBACK する。そう書いたつもりでした。
途中でおかしくなりました。ROLLBACK を打ちました。戻りませんでした。
正確に言うと、一部だけ戻りました。 ALTER TABLE より後にやった DELETE は戻りました。 ALTER TABLE より前にやった UPDATE は、そのまま確定していました。
MySQL では、
ALTER TABLEを実行した瞬間に、それまでのトランザクションが勝手にコミットされます。 エラーも警告も出ません。「暗黙のコミット」 といいます。 だから「囲んだつもりのトランザクション」は、ALTER TABLEの行で2つに切れていました。
これは私の書き方が悪かった、で終わる話ではありません。 SQL が「DDL」「DML」という種類に分かれているのは、名前を分けるためではなく、こういう挙動が違うからです。 分類を「試験に出る言葉」として覚えていたので、実害があるということを知りませんでした。
この記事では、それを実際に再現します。 そのあと、 ACID を A・C・I・D と1文字ずつ、順番に壊して確かめます。壊れ方を見ないと、何を守ってもらっていたのか分かりません。
そして「じゃあ NoSQL はなぜ速いのか」を BASE と PACELC で対比し、 正規化した3テーブルの JOIN と、非正規化した1テーブルの SELECT を、同じデータで実測します。
似たタイトルの記事があります。役割が違います。 この記事は理屈と、戻らない場合を扱います。全部やるか、1つもやらないか — トランザクションとACID はアプリに組み込んで使う手順を扱います。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
- SQL は4種類(DDL / DML / DQL / DCL)に分かれている。 これは分類の話ではなく、戻せるかどうかが違うという実害の話
- DDL と DCL は暗黙のコミットを起こす。
ALTER TABLEを1行挟むと、その前のINSERTまで確定する - ACID の4文字は、それぞれ別の壊れ方を防いでいる。 A は途中で止まること、C は変な値、I は他人の途中、D は電源断
- 「D(永続性)が効いている」ことは、コンテナを落として上げ直すと確かめられる。 ただし
down -vは別物で、こちらは本当に消える - BASE は ACID の反対語ではなく、「ACID の一部を捨てた」という宣言。NoSQL が速いのは、捨てているから
- CAP は「分断が起きたとき」の話しかしていない。 普段(分断が無いとき)の選択まで含めたのが PACELC
- 正規化は「同じ事実を1箇所にする」こと。 目的は容量削減ではなく、更新時に矛盾しないこと
- 非正規化は「速さのために正しさを一部捨てる」判断。 だから測ってからやる
- ストアドプロシージャは往復を減らせるが、Git で管理しにくい。 今の個人開発では、まず使わない
- クラスタリングとフェデレーションは別物。 同じデータを複数台で持つのがクラスタ、機能ごとにDBを分けるのがフェデレーション
この記事でやること
- Docker Compose で練習用の MySQL を1つ立てる(既存のDBには一切触らない)
- DDL / DML / DQL / DCL を、代表的な文を1つずつ実際に打って区別する
ALTER TABLEで暗黙のコミットを再現し、ROLLBACKが効かないところを出力で見る- ACID を A・C・I・D と1つずつ壊す(送金の途中でエラー / 制約違反 / 2接続 / コンテナ再起動)
- BASE と PACELC を表で対比し、どちらを選ぶかの基準を決める
- 崩れた表を第1〜第3正規形まで直し、そのあと非正規化して JOIN と SELECT を実測する
- ストアドプロシージャを1つ作って呼び、使わない理由まで書く
- クラスタリングとフェデレーションの違いを、判断表にして締める
docker compose down -vで全部消す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| DDL | Data Definition Language。入れ物を作る・変える SQL。CREATE ALTER DROP TRUNCATE |
| DML | Data Manipulation Language。中身を入れる・直す・消す SQL。INSERT UPDATE DELETE |
| DQL | Data Query Language。読む SQL。SELECT だけ。DML に含める流儀もある |
| DCL | Data Control Language。権限を与える・取り上げる SQL。GRANT REVOKE |
| トランザクション | 「ここからここまでを、全部やるか1つもやらないか」で扱う区切り |
| 暗黙のコミット | こちらが COMMIT と書いていないのに、DBが勝手に確定すること |
| ACID | トランザクションが守るべき4つの性質。原子性・一貫性・分離性・永続性 |
| BASE | ACID の一部を捨てた側の考え方。だいたい使える・状態は変わる・そのうち揃う |
| PACELC | 分断時(P)の A か C に加えて、平常時(E)の L か C まで決める考え方 |
| 正規化 | 同じ事実を1箇所にしか置かないようにテーブルを分けること |
| 非正規化 | 速さのために、わざと同じ事実を2箇所に持つこと |
① 練習用の MySQL を1つ立てる
既にあるDBを使いません。 壊すからです。新しく立てて、最後に消します。
作業フォルダを作ります。
Docker がまだ入っていない場合は、先に入れておいてください。Compose の書き方そのものは Composeなら1コマンド — 複数コンテナをまとめる にあります。
compose.yml を1つ書きます。
services:
db:
image: mysql:8.0
container_name: dbth-mysql
environment:
MYSQL_ROOT_PASSWORD: dbth_root_pw
MYSQL_DATABASE: dbth
ports:
- "127.0.0.1:3307:3306"
volumes:
- dbdata:/var/lib/mysql
volumes:
dbdata:
3行だけ意味を書いておきます。
| 行 | なぜそう書いたか |
|---|---|
image: mysql:8.0 | latest を使わない。 バージョンで挙動が変わる話をするので、固定する。なお MySQL 8.0 は2026年4月30日にコミュニティ版のサポートが終了しています。 この記事の出力は 8.0.39 で取ったものなので練習ではこのまま固定しますが、練習以外で新しく作るなら 8.4(LTS)を選びます |
"127.0.0.1:3307:3306" | VM の外から繋がらないようにする。 127.0.0.1: を書かないと、VMのIPで誰でも来られる |
- dbdata:/var/lib/mysql | データの置き場所。永続性(D)を確かめるときにここが効く |
ポートを 3307 にしているのは、VM に既に MySQL が入っていた場合にぶつからないようにするためです。 この記事では使いませんが、外から繋ぐときは 3306 ではなく 3307 です。
立てます。
[+] Running 3/3
✔ Network dbth_default Created 0.1s
✔ Volume "dbth_dbdata" Created 0.0s
✔ Container dbth-mysql Started 0.4s
すぐには繋がりません。 MySQL は初回起動でデータ領域を作るので、20秒ほどかかります。 「上がったか」はログで確かめます。
dbth-mysql | 2026-10-04T02:11:47.882031Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.39' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
ready for connections が出るまで待ちます。 ここを待たずに繋ぐと ERROR 2002 (HY000): Can't connect to local MySQL server through socket が出ます。DBが壊れたわけではありません。
繋ぎます。
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.39 MySQL Community Server - GPL
mysql>
[Warning] Using a password on the command line interface can be insecure. は毎回出ます。 コマンドの履歴やプロセス一覧にパスワードが残るからです。練習用なので今回は無視します。 本番では、コマンドラインにパスワードを書かず、-p だけ付けて対話で入力します。
1行だけ流したいときのために、シェルの関数を作っておきます。
VERSION() @@transaction_isolation
8.0.39 REPEATABLE-READ
2>&1 | grep -v にしているのは、パスワードの警告だけを消すためです。 ここを 2>/dev/null にするとSQLのエラーまで消えます。 私は最初それをやって、 「エラーも出ないのに結果が変わらない」で20分溶かしました。 2>&1 は「標準エラーを標準出力に合流させる」指定で、書く位置で意味が変わります。
📸 スクリーンショット1:
docker compose psとq "SELECT VERSION();"の出力。dbth-mysqlがrunningで、バージョンが返ってきているところを残す
② SQL は4種類に分かれている — DDL / DML / DQL / DCL
「SQL は4種類」と本には書いてあります。 覚えるだけだと意味がないので、1種類ずつ実際に打ちます。
対話クライアントに入ります。以下、mysql> が付いている行が入力です。
DDL — 入れ物を作る・変える
mysql> CREATE TABLE accounts (
-> id INT PRIMARY KEY,
-> owner VARCHAR(20) NOT NULL,
-> balance INT NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESCRIBE accounts;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| owner | varchar(20) | NO | | NULL | |
| balance | int | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected の「0 rows」に注目してください。 DDL は行を触っていません。 触ったのは入れ物の形(スキーマ)です。ここが DML との一番の違いです。
DDL の代表は4つです。
| 文 | 何をするか | 注意 |
|---|---|---|
CREATE | 作る | — |
ALTER | 形を変える | これが後で問題になります |
DROP | 入れ物ごと消す | 中身も一緒に消える |
TRUNCATE | 中身だけ全部消す | DELETE に見えるが DDL 扱い。戻せない |
TRUNCATE TABLE は「全行削除」なので DML に見えます。 MySQL では DDL 扱いで、 ROLLBACK できません。 名前ではなく挙動で分類されているということです。
DML — 中身を入れる・直す・消す
mysql> INSERT INTO accounts VALUES (1, 'sato', 1000), (2, 'suzuki', 1000);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> UPDATE accounts SET balance = balance + 500 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> DELETE FROM accounts WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 の2つは別の数字です。 WHERE に当たった行が matched、実際に値が変わった行が Changed です。 同じ値で UPDATE すると matched: 1 Changed: 0 になります。「更新したのに変わっていない」の判定に使えます。
戻しておきます。
mysql> INSERT INTO accounts VALUES (2, 'suzuki', 1000);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE accounts SET balance = 1000 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
DQL — 読む
mysql> SELECT * FROM accounts ORDER BY id;
+----+--------+---------+
| id | owner | balance |
+----+--------+---------+
| 1 | sato | 1000 |
| 2 | suzuki | 1000 |
+----+--------+---------+
2 rows in set (0.00 sec)
DQL は SELECT しかありません。 そして 「DQL という分類を使わず、SELECT も DML に入れる」流儀もあります。 どちらでも構いませんが、この記事では分けます。 理由は次の1点です。
SELECTだけは、暗黙のコミットを起こしません。INSERT/UPDATE/DELETEとSELECTは、戻せるかどうかでは同じ側ですが、 「読むだけ」は行にロックを掛けない(既定の分離レベルでは)という違いがあります。
DCL — 権限を与える・取り上げる
権限用のユーザーを作ります。ユーザーを作る CREATE USER は DCL 側です。
mysql> CREATE USER 'appuser'@'%' IDENTIFIED BY 'app_pw';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT SELECT, INSERT, UPDATE ON dbth.* TO 'appuser'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'appuser'@'%';
+-------------------------------------------------------------------+
| Grants for appuser@% |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`%` |
| GRANT SELECT, INSERT, UPDATE ON `dbth`.* TO `appuser`@`%` |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
このユーザーで繋いで、DROP を試します。 別のシェルから入ります。
mysql> SELECT COUNT(*) FROM accounts;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> DROP TABLE accounts;
ERROR 1142 (42000): DROP command denied to user 'appuser'@'172.18.0.1' for table 'accounts'
ERROR 1142 は「文法が間違っている」ではなく「権限が無い」です。 そして GRANT USAGE ON *.* は「権限ゼロ」を表す特別な行で、何も許していません。 「USAGE が付いているから使える」ではありません。ここは読み間違えやすいところです。
exit で戻ります。
4種類の表 — この分類には実害がある
ここが本題です。 4種類を並べると、分類が見た目の話ではないことが分かります。
| 種類 | 代表 | 何を触るか | ROLLBACK で戻せるか |
|---|---|---|---|
| DDL | CREATE ALTER DROP TRUNCATE | 入れ物の形 | 戻せない。しかも暗黙のコミットを起こす |
| DML | INSERT UPDATE DELETE | 中身の行 | 戻せる(InnoDB の場合) |
| DQL | SELECT | 読むだけ | 変えていないので関係なし |
| DCL | GRANT REVOKE CREATE USER | 権限 | 戻せない。こちらも暗黙のコミット |
実害はこれです。 「DDL と DCL は、トランザクションの中に置いても囲われない」。 移行スクリプトが
ALTER TABLEとUPDATEを混ぜていると、失敗したとき中途半端な状態で止まります。
次の節で、それを実際に起こします。
③ DDL は ROLLBACK できない(暗黙のコミット)
まず、DML だけなら本当に戻ることを確認します。 対話クライアントに入り直します。
まず、正しく戻る例
mysql> SELECT COUNT(*) FROM accounts;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts VALUES (91, 'temp_a', 100);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO accounts VALUES (92, 'temp_b', 200);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(*) FROM accounts;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM accounts;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
2件に戻りました。 これが「トランザクションが効いている」状態です。 自分の接続の中では4件に見えていて、ROLLBACK で無かったことになりました。
次に、途中に ALTER TABLE を1行入れる
やることは同じです。 INSERT を2回して ROLLBACK します。 違いは、2回目の INSERT の前に ALTER TABLE を1行入れただけです。
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts VALUES (91, 'temp_a', 100);
Query OK, 1 row affected (0.00 sec)
mysql> ALTER TABLE accounts ADD COLUMN memo VARCHAR(50) NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO accounts VALUES (92, 'temp_b', 200, NULL);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
ROLLBACK は成功しています。 エラーも警告も出ていません。数えます。
mysql> SELECT * FROM accounts ORDER BY id;
+----+--------+---------+------+
| id | owner | balance | memo |
+----+--------+---------+------+
| 1 | sato | 1000 | NULL |
| 2 | suzuki | 1000 | NULL |
| 91 | temp_a | 100 | NULL |
+----+--------+---------+------+
3 rows in set (0.00 sec)
id = 91が残っています。ROLLBACKしたのに戻っていません。id = 92だけ消えました。
何が起きたか、時系列で書きます。
| 打った文 | DBの中で起きたこと |
|---|---|
START TRANSACTION | トランザクション開始 |
INSERT (91) | 未確定のまま追加 |
ALTER TABLE ... | ここで暗黙の COMMIT が走り、91 が確定。そのあと列を追加し、新しいトランザクションが始まる |
INSERT (92) | 新しいトランザクションの中で追加 |
ROLLBACK | 新しいほうだけ戻る。だから 92 だけ消えた |
ALTER TABLE の行で、トランザクションが2つに切れていました。 そして ALTER TABLE そのものも ROLLBACK で戻っていません。memo 列は残ったままです。
📸 スクリーンショット2:
ROLLBACK;の直後のSELECT * FROM accounts ORDER BY id;の出力。id=91が残り、memo列も残っているところを残す
なぜ MySQL はこうなっているのか
MySQL の DDL は、データ辞書(テーブル定義そのもの)を書き換えます。 昔の MySQL ではこの書き換えが、行のトランザクションとは別の仕組みで管理されていました。 そのため「DDL をトランザクションで囲む」ことが原理的にできず、暗黙のコミットで区切る設計になっています。
MySQL 8.0 でアトミック DDL(DDL 自体が途中で落ちても半端に残らない)は入りましたが、 これは「DDL 1文が半端に終わらない」という話であって、「アプリ側の ROLLBACK で取り消せる」という話ではありません。 混同しやすいところです。
本番の移行スクリプトで、これが一番怖い
怖いのは、エラーが出ないことです。エラーが出れば気づけます。 「ROLLBACK を打って、Query OK と出て、でも戻っていない」が一番危険です。
なので、移行スクリプトは次の形にします。
| やり方 | 説明 |
|---|---|
| DDL と DML を同じスクリプトに混ぜない | ファイルを分ける。先に DDL、後で DML |
| DDL は「足す」だけにする | 列を足す・テーブルを作るは戻しやすい。消すのは別の日に |
| 戻す手順(down)を先に書く | 「ALTER TABLE ... DROP COLUMN memo で戻る」と書いてから流す |
| 必ず練習用のコピーで通してから本番へ | 本番で初めて流すのは、この記事の趣旨と正反対 |
PostgreSQL では DDL もトランザクションで囲めます。(CREATE TABLE を ROLLBACK で消せます) 同じ SQL を書いていても、DB が違うと戻せるかどうかが変わるということです。 ALTER TABLE そのものの話は ALTER TABLEは取り消せない — SQL深掘り に別途あります。
片づけて次に行きます。
mysql> ALTER TABLE accounts DROP COLUMN memo;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DELETE FROM accounts WHERE id >= 90;
Query OK, 1 row affected (0.00 sec)
④ ACID を1つずつ壊して確かめる
ACID は4つの別々の性質です。 一気に説明されると全部同じに聞こえるので、1つずつ壊します。
| 文字 | 名前 | 守らないと何が起きるか |
|---|---|---|
| A | 原子性(Atomicity) | 送金の「引いた」だけが残る。お金が消える |
| C | 一貫性(Consistency) | 残高がマイナスの行、存在しないユーザーの注文が入る |
| I | 分離性(Isolation) | 他人の途中の値を読んで計算してしまう |
| D | 永続性(Durability) | 「完了しました」と返した直後の再起動で消える |
A — 原子性:送金の途中で落として、両方戻す
残高チェックを入れた口座テーブルを作り直します。
mysql> DROP TABLE accounts;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE accounts (
-> id INT PRIMARY KEY,
-> owner VARCHAR(20) NOT NULL,
-> balance INT NOT NULL,
-> CONSTRAINT chk_balance CHECK (balance >= 0)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO accounts VALUES (1, 'sato', 1000), (2, 'suzuki', 1000);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
sato から suzuki へ 300 送ります。 送金は必ず2つの UPDATE です。
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE accounts SET balance = balance - 300 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ここで「アプリが落ちた」ことにします。 2つ目の UPDATE を打つ前に、途中経過を見ます。
mysql> SELECT * FROM accounts;
+----+--------+---------+
| id | owner | balance |
+----+--------+---------+
| 1 | sato | 700 |
| 2 | suzuki | 1000 |
+----+--------+---------+
2 rows in set (0.00 sec)
合計が 1700 です。300 円が消えています。 この状態が外から見えたらアウトです。分離性(I)が守っているのは、まさにこの瞬間です。
戻します。
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT *, (SELECT SUM(balance) FROM accounts) AS total FROM accounts;
+----+--------+---------+-------+
| id | owner | balance | total |
+----+--------+---------+-------+
| 1 | sato | 1000 | 2000 |
| 2 | suzuki | 1000 | 2000 |
+----+--------+---------+-------+
2 rows in set (0.00 sec)
両方戻りました。 これが A(原子性) です。
A は「成功させる」性質ではありません。「半分だけ成功した状態を残さない」性質です。 失敗してよい。中途半端が残らなければよい。
C — 一貫性:制約が変な値を弾く
C は「DBが勝手に正しくしてくれる」ことではありません。 こちらが宣言したルール(制約)を、DBが必ず守らせるということです。
まず CHECK を破ってみます。1000 円しかない口座から 5000 円引きます。
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
ERROR 3819 (HY000): Check constraint 'chk_balance' is violated.
mysql> SELECT * FROM accounts;
+----+--------+---------+
| id | owner | balance |
+----+--------+---------+
| 1 | sato | 1000 |
| 2 | suzuki | 1000 |
+----+--------+---------+
2 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
CHECK 制約が実際に効くのは MySQL 8.0.16 以降です。 それ以前は書けるけれど黙って無視されました。 「制約を書いたつもりで守られていない」は 実際に起きた事故です。効いているかは SHOW CREATE TABLE で確かめられます。
mysql> SHOW CREATE TABLE accounts\G
*************************** 1. row ***************************
Table: accounts
Create Table: CREATE TABLE `accounts` (
`id` int NOT NULL,
`owner` varchar(20) NOT NULL,
`balance` int NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `chk_balance` CHECK ((`balance` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
\G は「縦に表示」です。 横に長い結果はこちらのほうが読めます。
次に外部キーを破ります。注文テーブルを足します。
mysql> CREATE TABLE orders (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> user_id INT NOT NULL,
-> amount INT NOT NULL,
-> CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES accounts(id)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO orders (user_id, amount) VALUES (1, 500);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO orders (user_id, amount) VALUES (999, 500);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbth`.`orders`, CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `accounts` (`id`))
user_id = 999 のユーザーは存在しません。 アプリ側でチェックし忘れても、DBが止めました。
外部キーは InnoDB でしか効きません。 MyISAM では書けるけれど無視されます。 SHOW CREATE TABLE の ENGINE=InnoDB を必ず確認してください。
C(一貫性)の実体は「制約」です。 アプリのバリデーションは C ではありません。 アプリを通さない経路(管理画面の直接SQL、バッチ、他のサービス)が 必ず1つはあるからです。最後の砦はDB側に置きます。
I — 分離性:もう片方から、途中の値は見えない
ここは接続を2つ使います。 ターミナルを2枚開いてください。
ターミナルA:
ターミナルB: 同じコマンドで、別の接続として入ります。
接続IDが違うことを両方で確認します。
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 14 |
+-----------------+
1 row in set (0.00 sec)
ターミナルAで、コミットせずに書き換えます。
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE accounts SET balance = 999999 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT balance FROM accounts WHERE id = 1;
+---------+
| balance |
+---------+
| 999999 |
+---------+
1 row in set (0.00 sec)
ターミナルBで、同じ行を読みます。
mysql> SELECT balance FROM accounts WHERE id = 1;
+---------+
| balance |
+---------+
| 1000 |
+---------+
1 row in set (0.00 sec)
Bからは 1000 のままです。 Aの中では 999999 に見えているのに、Bには一切漏れていません。 これが I(分離性) です。
ターミナルAでコミットします。
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
ターミナルBでもう一度読みます。
mysql> SELECT balance FROM accounts WHERE id = 1;
+---------+
| balance |
+---------+
| 999999 |
+---------+
1 row in set (0.00 sec)
見えるようになりました。
分離性は「有る / 無い」ではなく段階がある
わざと弱くしてみます。ターミナルBで分離レベルを下げます。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
ターミナルAで、またコミットせずに書き換えます。
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE accounts SET balance = 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ターミナルBで読みます。
mysql> SELECT balance FROM accounts WHERE id = 1;
+---------+
| balance |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
見えてしまいました。 Aはまだ
COMMITしていません。 この後AがROLLBACKしたら、Bは「存在しなかった値」を読んで計算したことになります。 これを ダーティリード といいます。
ターミナルAで戻します。
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
ターミナルBを元に戻します。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
MySQL の既定は REPEATABLE READ です。 PostgreSQL や SQL Server の既定は READ COMMITTED です。 同じアプリを別のDBに移すと、見え方が変わります。 4段階の分離レベルと、それぞれで何が起きるかは 同じSELECTが2回目で変わる — SQL深掘り に分けてあります。
📸 スクリーンショット3: ターミナルAとBを並べた画面。 Aで
999999に更新している最中に、Bでは1000が返っているところを1枚に収める
D — 永続性:コミットしたら、電源が落ちても残る
COMMIT が返ってきた時点で、ディスクに書かれているという約束です。確かめます。
mysql> UPDATE accounts SET balance = 1000 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts VALUES (3, 'takahashi', 7777);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
MySQL のコンテナを、そのまま落として上げ直します。
[+] Restarting 1/1
✔ Container dbth-mysql Started 10.6s
上がるのを待ってから読みます。
id owner balance
1 sato 1000
2 suzuki 1000
3 takahashi 7777
残っています。 これが D(永続性) です。
なぜ残ったのかを1つ確かめておきます。
@@innodb_flush_log_at_trx_commit @@sync_binlog
1 1
innodb_flush_log_at_trx_commit = 1が「COMMIT のたびにログをディスクに書いて待つ」設定です。 これを0や2にすると速くなりますが、電源断で「COMMIT 済みの数秒分」が消えます。 D は無料ではありません。 「速くするために D を少し捨てる」という設定が、実際に存在します。
docker compose restart で残るのは、当たり前ではありません。 コンテナを作り直す(down してから up)場合、volumes: を書いていなければ消えます。 そこは D の話ではなく、Docker のボリュームの話です。
そして docker compose down -v は、ボリュームごと消します。 これは後片づけで使います。
⑤ BASE と対比する — NoSQL が速い理由
「NoSQL は速い」と言われます。何を捨てて速いのかを見ます。
BASE は3つの言葉の頭文字です。
| 文字 | 元の言葉 | 意味 |
|---|---|---|
| BA | Basically Available | 基本的に利用可能。一部が壊れても、全体は応答を返し続ける |
| S | Soft state | 状態は変わりうる。誰も書いていないのに、読むたび値が変わることがある(伝播の途中だから) |
| E | Eventually consistent | 結果的に整合。いつかは揃う。いつとは言わない |
ACID と BASE の対比
| 観点 | ACID | BASE |
|---|---|---|
| 書いた直後に読むと | 必ず新しい値 | 古い値が返ることがある |
| 一部のノードが落ちたら | 止める(整合性を優先) | 返し続ける(可用性を優先) |
| 書き込みの速さ | 遅い。全員の合意を待つ | 速い。1台に書けたら返す |
| 横に台数を増やすと | 合意のコストが増えて、頭打ちになる | ほぼ線形に伸びる |
| アプリ側の仕事 | 少ない。DBが守る | 多い。「古い値が来る」前提でコードを書く |
| 代表 | MySQL / PostgreSQL / Oracle | Cassandra / DynamoDB / Riak |
NoSQL が速いのは、賢いアルゴリズムを使っているからではありません。 待つのをやめたからです。 ACID の「全員の合意を待つ」部分を捨てています。
どちらを選ぶかは、業務で決める
技術の好みではありません。「間違ったときに何が起きるか」で決めます。
| データ | 選ぶ側 | 理由 |
|---|---|---|
| 口座残高・在庫数 | ACID | 1円ずれたら業務が止まる。 数え直せない |
| 注文の確定 | ACID | 「注文できたのに商品が無い」は謝って済まない |
| いいねの数 | BASE | 3秒古くても誰も困らない。 秒間何万回も更新される |
| 閲覧数・ランキング | BASE | そもそも概算でよい |
| ログ・イベント | BASE | 1件落ちても全体の傾向は変わらない |
| ユーザーのプロフィール | 場合による | メールアドレス変更直後に古いのが出るのは許容範囲か、で決める |
1つのサービスの中で両方使うのが普通です。 「うちは MySQL です」ではなく、 「残高は MySQL、いいね数は Redis」のようにデータごとに決めます。
そして重要なのは、ACID の DB を使っていても、レプリカを増やした瞬間に BASE 側に片足が入ることです。 書き込みを1台、読み取りを3台に散らすと、書いた直後に別のレプリカを読むと古い値が返ります。 そこは 書いた直後に読むと無い — レプリケーションとCAP にあります。
⑥ PACELC — CAP の続き
CAP 定理はよく引かれます。 ただし、CAP は「分断が起きたとき」の話しかしていません。
CAP: ネットワークが分断された(Partition)とき、 可用性(A)か整合性(C)のどちらかを諦めるしかない。
問題は、分断は滅多に起きないことです。 普段(分断が無いとき)にどう振る舞うかを、CAP は何も言っていません。 そこを足したのが PACELC です。
PACELC: Partition が起きているとき(P)は、A か C の二択。 Else(分断が起きていないとき)は、Latency(速さ)か Consistency(整合性)の二択。
読み方
PA/EL のように書きます。スラッシュの左が分断時、右が平常時です。
| 記法 | 意味 |
|---|---|
| PA/EL | 分断時は返し続ける。平常時も速さ優先(古い値が返りうる) |
| PC/EC | 分断時は止める。平常時も整合性優先(遅くても正しい) |
| PA/EC | 分断時は返し続けるが、平常時は整合性優先 |
実例
| システム | 分断時(P) | 平常時(E) | 記法 |
|---|---|---|---|
| MySQL 単体 | 分断のしようがない | C(正しさ優先) | PC/EC |
| MySQL 準同期レプリケーション | C。レプリカの応答を待って止まりうる | C。待つぶん遅い | PC/EC |
| MySQL 非同期レプリケーション | A。プライマリは返し続ける | L。レプリカは遅れる | PA/EL |
| Cassandra(既定) | A | L。整合性レベルを上げれば C 寄りにできる | PA/EL |
| DynamoDB(既定の読み) | A | L。結果整合の読み | PA/EL |
| DynamoDB(強い整合の読み) | A | C。読み取りコストが2倍かかる | PA/EC |
| MongoDB(既定) | A | C。プライマリから読む | PA/EC |
「普段どうするか」まで決めるのが PACELC です。 CAP だけで議論すると、年に1回あるかどうかの分断の話をしているのに、 毎日効いているレイテンシの話が抜けます。
同じ製品でも、設定で記法が変わります。 上の表の DynamoDB は、 同じDBなのに読み方を変えるだけで PA/EL にも PA/EC にもなります。 「この製品は CP だ」という覚え方は、ほぼ役に立ちません。設定を見ます。
⑦ 正規化と非正規化 — 崩れた表を直して、そのあと壊す
正規化は「テーブルを分けること」ではありません。 同じ事実を1箇所にしか置かないことです。目的は容量ではなく、更新したときに矛盾しないことです。
崩れた表を作る
わざと悪い表を作ります。
mysql> CREATE TABLE orders_bad (
-> order_id INT,
-> user_name VARCHAR(20),
-> user_city VARCHAR(20),
-> user_pref VARCHAR(20),
-> products VARCHAR(100),
-> amount INT
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO orders_bad VALUES
-> (1, 'sato', 'yokohama', 'kanagawa', 'pen,note', 300),
-> (2, 'suzuki', 'kawasaki', 'kanagawa', 'pen', 100),
-> (3, 'sato', 'yokohama', 'kanagawa', 'eraser', 50);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM orders_bad;
+----------+-----------+-----------+-----------+----------+--------+
| order_id | user_name | user_city | user_pref | products | amount |
+----------+-----------+-----------+-----------+----------+--------+
| 1 | sato | yokohama | kanagawa | pen,note | 300 |
| 2 | suzuki | kawasaki | kanagawa | pen | 100 |
| 3 | sato | yokohama | kanagawa | eraser | 50 |
+----------+-----------+-----------+-----------+----------+--------+
3 rows in set (0.00 sec)
この表には、3種類の問題があります。順番に直します。
第1正規形 — 1つのマスに1つの値
products に pen,note と2つ入っています。 これが第1正規形違反です。
何が困るか、実際に困ります。「pen を含む注文」を数えてみます。
mysql> SELECT COUNT(*) FROM orders_bad WHERE products LIKE '%pen%';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
一見合っています。では note を notepen に変えたらどうなるか、を考えてください。 LIKE '%pen%' は notepen にも当たります。 そして 部分一致は索引が効かないので、行数が増えるほど線形に遅くなります。
行に割ります。
mysql> CREATE TABLE order_items (
-> order_id INT,
-> product VARCHAR(20)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO order_items VALUES
-> (1,'pen'), (1,'note'), (2,'pen'), (3,'eraser');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM order_items WHERE product = 'pen';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
LIKE が = になりました。 これが第1正規形の効果です。索引が効く形になりました。
第2正規形 — 主キーの一部だけで決まる列を追い出す
order_items の主キーは (order_id, product) です。 ここに「商品の単価」を足したくなったとします。
mysql> ALTER TABLE order_items ADD COLUMN unit_price INT NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> UPDATE order_items SET unit_price = 100 WHERE product = 'pen';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
unit_price は product だけで決まります。order_id は関係ありません。 主キーの一部だけで決まる列がある=第2正規形違反です。
何が困るか。 ペンの単価を 120 に上げるとき、 order_items の中のペンの行を全部更新しないといけません。 1行でも漏れたら、同じペンに2つの単価が存在します。
mysql> ALTER TABLE order_items DROP COLUMN unit_price;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE products (
-> product VARCHAR(20) PRIMARY KEY,
-> unit_price INT NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO products VALUES ('pen',100), ('note',200), ('eraser',50);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
単価は1箇所になりました。
第3正規形 — 主キー以外から決まる列を追い出す
orders_bad に戻ります。user_pref は user_city から決まります。 (yokohama なら必ず kanagawa)
主キー(order_id)ではなく、他の列(user_city)から決まる列がある=第3正規形違反です。
何が困るか、実際に起こします。 市の名前を直してみます。
mysql> UPDATE orders_bad SET user_city = 'yokohama-shi' WHERE order_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT order_id, user_name, user_city FROM orders_bad;
+----------+-----------+--------------+
| order_id | user_name | user_city |
+----------+-----------+--------------+
| 1 | sato | yokohama-shi |
| 2 | suzuki | kawasaki |
| 3 | sato | yokohama |
+----------+-----------+--------------+
3 rows in set (0.00 sec)
同じ sato の市が、行によって違います。 これが更新時異常です。 1つの事実(sato の住む市)が3行に散らばっているので、更新が漏れます。
分けます。
mysql> DROP TABLE orders_bad;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE prefs (
-> pref_id INT PRIMARY KEY,
-> pref_name VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE cities (
-> city_id INT PRIMARY KEY,
-> city_name VARCHAR(20) NOT NULL,
-> pref_id INT NOT NULL,
-> CONSTRAINT fk_cities_pref FOREIGN KEY (pref_id) REFERENCES prefs(pref_id)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE users (
-> user_id INT PRIMARY KEY,
-> user_name VARCHAR(20) NOT NULL,
-> city_id INT NOT NULL,
-> CONSTRAINT fk_users_city FOREIGN KEY (city_id) REFERENCES cities(city_id)
-> );
Query OK, 0 rows affected (0.05 sec)
3つに分かれました。 表にすると、崩れていた1枚がこうなります。
| 直したもの | 何が1箇所になったか |
|---|---|
| 第1正規形 | 1つのマスに1つの値。 カンマ区切りをやめた |
| 第2正規形 | 商品の単価が products の1行だけに |
| 第3正規形 | 市と県の対応が cities の1行だけに |
正規化の目的は、容量削減ではありません。 「1つの事実を1箇所にする」ことで、更新の漏れを構造的に起こせなくすることです。 テーブル設計そのものは 1つの表に全部入れると壊れる — テーブル設計と正規化 にあります。
そして、非正規化する
正規化すると JOIN が増えます。 本当に遅くなるのか、測ります。
20万行入れます。 再帰CTEを使います。
mysql> SET SESSION cte_max_recursion_depth = 1000000;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO prefs VALUES (1,'kanagawa'),(2,'tokyo'),(3,'chiba'),(4,'saitama');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO cities
-> WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM n WHERE x < 200)
-> SELECT x, CONCAT('city_', x), (x % 4) + 1 FROM n;
Query OK, 200 rows affected (0.02 sec)
Records: 200 Duplicates: 0 Warnings: 0
mysql> INSERT INTO users
-> WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM n WHERE x < 20000)
-> SELECT x, CONCAT('user_', x), (x % 200) + 1 FROM n;
Query OK, 20000 rows affected (0.31 sec)
Records: 20000 Duplicates: 0 Warnings: 0
ここで一度失敗しました。
mysql> INSERT INTO users
-> WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM n WHERE x < 20000)
-> SELECT x, CONCAT('user_', x), (x % 200) + 1 FROM n;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
再帰CTEは既定で 1000 回で止まります。 無限ループを防ぐための安全弁です。 先ほどの SET SESSION cte_max_recursion_depth を打っていないと、ここで落ちます。
注文を20万行入れます。
mysql> DROP TABLE orders;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE orders (
-> order_id INT PRIMARY KEY,
-> user_id INT NOT NULL,
-> amount INT NOT NULL,
-> KEY idx_orders_user (user_id)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO orders
-> WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM n WHERE x < 200000)
-> SELECT x, (x % 20000) + 1, (x % 900) + 100 FROM n;
Query OK, 200000 rows affected (3.12 sec)
Records: 200000 Duplicates: 0 Warnings: 0
同じデータを、非正規化した1テーブルにも入れます。
mysql> CREATE TABLE orders_flat (
-> order_id INT PRIMARY KEY,
-> user_id INT NOT NULL,
-> pref_name VARCHAR(20) NOT NULL,
-> amount INT NOT NULL
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO orders_flat
-> SELECT o.order_id, o.user_id, p.pref_name, o.amount
-> FROM orders o
-> JOIN users u ON u.user_id = o.user_id
-> JOIN cities c ON c.city_id = u.city_id
-> JOIN prefs p ON p.pref_id = c.pref_id;
Query OK, 200000 rows affected (1.94 sec)
Records: 200000 Duplicates: 0 Warnings: 0
この時点で、県の名前が20万行にコピーされました。 これが非正規化です。
測る
同じ集計を、両方で3回ずつ流します。 1回目はディスクから読むので遅く出ます。3回目を見ます。
正規化した3テーブルの JOIN:
mysql> SELECT p.pref_name, COUNT(*) AS cnt, SUM(o.amount) AS total
-> FROM orders o
-> JOIN users u ON u.user_id = o.user_id
-> JOIN cities c ON c.city_id = u.city_id
-> JOIN prefs p ON p.pref_id = c.pref_id
-> GROUP BY p.pref_name;
+-----------+-------+----------+
| pref_name | cnt | total |
+-----------+-------+----------+
| chiba | 50000 | 27476300 |
| kanagawa | 50000 | 27475950 |
| saitama | 50000 | 27474350 |
| tokyo | 50000 | 27473400 |
+-----------+-------+----------+
4 rows in set (0.41 sec)
非正規化した1テーブルの SELECT:
mysql> SELECT pref_name, COUNT(*) AS cnt, SUM(amount) AS total
-> FROM orders_flat
-> GROUP BY pref_name;
+-----------+-------+----------+
| pref_name | cnt | total |
+-----------+-------+----------+
| chiba | 50000 | 27476300 |
| kanagawa | 50000 | 27475950 |
| saitama | 50000 | 27474350 |
| tokyo | 50000 | 27473400 |
+-----------+-------+----------+
4 rows in set (0.09 sec)
3回ずつ流した結果です。
| 回 | 正規化(3JOIN) | 非正規化(1テーブル) |
|---|---|---|
| 1回目 | 0.58 sec | 0.14 sec |
| 2回目 | 0.43 sec | 0.09 sec |
| 3回目 | 0.41 sec | 0.09 sec |
| 比 | — | 約 4.5 倍速い |
MySQL 8.0 にはクエリキャッシュがありません。(5.7 で非推奨、8.0 で削除) それでも1回目が遅いのは、バッファプールにデータが載っていないからです。 測るときは必ず数回流して、安定した値を見ます。
どこに時間が行ったのかも見ます。
mysql> EXPLAIN FORMAT=TREE
-> SELECT p.pref_name, COUNT(*) FROM orders o
-> JOIN users u ON u.user_id = o.user_id
-> JOIN cities c ON c.city_id = u.city_id
-> JOIN prefs p ON p.pref_id = c.pref_id
-> GROUP BY p.pref_name\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=245832.15 rows=200000)
-> Nested loop inner join (cost=175832.15 rows=200000)
-> Nested loop inner join (cost=105832.15 rows=200000)
-> Table scan on o (cost=20832.15 rows=200000)
-> Single-row index lookup on u using PRIMARY (user_id=o.user_id)
-> Single-row index lookup on c using PRIMARY (city_id=u.city_id)
-> Single-row index lookup on p using PRIMARY (pref_id=c.pref_id)
1 row in set (0.01 sec)
Nested loop inner join が3段あります。 20万行それぞれについて、 索引を3回引いています。 1回あたりは速くても、20万×3回の合計が 0.3 秒です。
非正規化は「速さのために正しさを一部捨てる」判断
捨てているものを、はっきりさせます。
| 項目 | 正規化 | 非正規化 |
|---|---|---|
| 同じ事実の置き場所 | 1箇所 | 20万箇所 |
| 県名を直すとき | prefs の1行 | 20万行の UPDATE。途中で落ちたら混在する |
| 読み取り | JOIN が要る。遅い | 速い |
| 容量 | 小さい | 大きい |
| 矛盾 | 起きない(構造的に) | 起きうる。防ぐのはアプリの責任 |
やってみます。県名を1つ変えます。
mysql> UPDATE prefs SET pref_name = 'KANAGAWA' WHERE pref_id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT pref_name, COUNT(*) FROM orders_flat GROUP BY pref_name;
+-----------+----------+
| pref_name | COUNT(*) |
+-----------+----------+
| chiba | 50000 |
| kanagawa | 50000 |
| saitama | 50000 |
| tokyo | 50000 |
+-----------+----------+
4 rows in set (0.09 sec)
orders_flatは古いままです。 JOIN 側は直っているのに、非正規化側だけ嘘になりました。 これが非正規化のコストです。 直すには20万行を更新する必要があります。
なので順番は、必ずこうです。
- まず正規化する。 正しさを構造で守る
- 遅いことを測る。 「遅そう」ではなく
EXPLAINと秒数で - 索引で解決できないか先に試す。 非正規化より先に索引
- それでも足りないときだけ非正規化し、「どこが嘘になりうるか」をコードのコメントに書く
⑧ ストアドプロシージャを1つ作って呼ぶ
「SQL を DB の中に保存しておいて、名前で呼ぶ」のがストアドプロシージャです。 1つ作ります。
mysql> DELIMITER //
mysql> CREATE PROCEDURE transfer(IN p_from INT, IN p_to INT, IN p_amount INT)
-> BEGIN
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION
-> BEGIN
-> ROLLBACK;
-> RESIGNAL;
-> END;
-> START TRANSACTION;
-> UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
-> UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
-> COMMIT;
-> END //
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
DELIMITER // は SQL ではありません。 mysql クライアントへの指示です。 プロシージャの中に ; が入るので、「文の終わりの記号を一時的に // に変える」必要があります。 mysql -e "..." や一部の GUI ではこれが効かず、ERROR 1064 になります。 私はそれで詰まりました。
呼びます。
mysql> SELECT * FROM accounts ORDER BY id;
+----+-----------+---------+
| id | owner | balance |
+----+-----------+---------+
| 1 | sato | 1000 |
| 2 | suzuki | 1000 |
| 3 | takahashi | 7777 |
+----+-----------+---------+
3 rows in set (0.00 sec)
mysql> CALL transfer(1, 2, 300);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM accounts ORDER BY id;
+----+-----------+---------+
| id | owner | balance |
+----+-----------+---------+
| 1 | sato | 700 |
| 2 | suzuki | 1300 |
| 3 | takahashi | 7777 |
+----+-----------+---------+
3 rows in set (0.00 sec)
失敗するほうも試します。 残高より多く送ります。
mysql> CALL transfer(1, 2, 99999);
ERROR 3819 (HY000): Check constraint 'chk_balance' is violated.
mysql> SELECT * FROM accounts ORDER BY id;
+----+-----------+---------+
| id | owner | balance |
+----+-----------+---------+
| 1 | sato | 700 |
| 2 | suzuki | 1300 |
| 3 | takahashi | 7777 |
+----+-----------+---------+
3 rows in set (0.00 sec)
変わっていません。 EXIT HANDLER の中の ROLLBACK が効きました。
RESIGNAL を書かないと、エラーが握り潰されて呼び出し側に届きません。 「ロールバックはされたが、アプリは成功だと思っている」という最悪の形になります。
保存されているものは一覧で見られます。
mysql> SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED
-> FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'dbth';
+--------------+--------------+---------------------+
| ROUTINE_NAME | ROUTINE_TYPE | CREATED |
+--------------+--------------+---------------------+
| transfer | PROCEDURE | 2026-10-04 03:22:41 |
+--------------+--------------+---------------------+
1 row in set (0.01 sec)
メリットとデメリットを、正直に
| 内容 | |
|---|---|
| 利点 | 往復が減る。 5回のSQLを1回の CALL にできる。ネットワークの往復が4回消える |
| 利点 | 権限を絞れる。 アプリユーザーに EXECUTE だけ与え、テーブルへの直接の UPDATE を禁止できる |
| 利点 | 複数のアプリから同じロジックを呼べる。 言語が違っても同じ結果になる |
| 欠点 | Git で管理しにくい。 「今DBに入っている版」と「リポジトリの版」がずれる |
| 欠点 | テストしにくい。 DBを立てないと1行もテストできない |
| 欠点 | 業務ロジックがDBに散る。 「この計算どこ?」がアプリとDBの2箇所になる |
| 欠点 | デバッグ手段が貧弱。 ブレークポイントが置けない。ログは自分で書く |
| 欠点 | DB を移すと全部書き直し。 MySQL と PostgreSQL で文法が違う |
今の個人開発では、まず使いません。 理由は1つで、Git に乗らないからです。コードレビューできないものは、後で必ず分からなくなります。 往復を減らしたいなら、まずSQLの本数を減らす(N+1 をやめる)ほうが先です。
使うのが妥当な場面もあります。
- アプリを触れない(既存の業務システムに後から機能を足す)
- アプリユーザーにテーブルを直接触らせたくない(権限を
EXECUTEだけに絞る) - 複数の言語のアプリが同じDBを触っていて、ロジックを1箇所にしたい
ビューとプロシージャの使い分けは 列を消したらビューが壊れた — SQL深掘り にあります。
⑨ クラスタリングとフェデレーション
この2つは混同されます。 名前が似ているからではなく、どちらも「DBを複数台にする」からです。
クラスタリング = 同じデータを複数台で持つ。 目的は可用性(1台落ちても続く) フェデレーション = 機能ごとにDBを分ける。 目的は負荷分散(1台あたりの仕事を減らす)
図で持つイメージ
クラスタリング:
[アプリ]
|
+---------+---------+
| | |
[DB #1] [DB #2] [DB #3]
users users users ← 全部同じ中身
orders orders orders
フェデレーション:
[アプリ]
|
+---------+---------+
| | |
[DB #1] [DB #2] [DB #3]
users orders logs ← 中身が違う
表で比べる
| 観点 | クラスタリング | フェデレーション |
|---|---|---|
| 各ノードの中身 | 全部同じ(または一部が同じ) | 全部違う |
| 主な目的 | 可用性。1台落ちても止めない | 負荷分散。書き込みも分散できる |
| 1台落ちたら | 他が代わる。サービスは続く | その機能だけ止まる。他は動く |
| 読み取り性能 | 台数分伸びる | 機能ごとにしか伸びない |
| 書き込み性能 | 伸びにくい。全台に伝えるため | 伸びる。別のDBだから |
| 難しくなること | 整合性。どこまで待つか、遅れをどう扱うか | 跨いだ JOIN とトランザクション |
| 例 | MySQL レプリケーション / Galera / Aurora | ユーザーDB・注文DB・ログDBを分ける |
それぞれ、何が難しくなるか
クラスタリングで難しくなるのは、整合性です。
- 書き込みを何台に伝えてから「完了」と返すか。 1台なら速いが、落ちたら消える
- レプリカが遅れているとき、そこから読んでよいか。 「書いた直後に読むと無い」が起きる
- フェイルオーバーの瞬間、どちらが正なのか。 両方が正だと思うとスプリットブレインになる
フェデレーションで難しくなるのは、跨いだ操作です。
JOINが書けなくなります。 ユーザーDBと注文DBが別サーバーなら、アプリ側で結合するしかありません- トランザクションが効きません。 「注文DBに入れて、在庫DBを減らす」をまとめて
ROLLBACKできません - 外部キーが張れません。 存在しないユーザーの注文を、DBが止めてくれなくなります
フェデレーションは、④で確かめた ACID の C(外部キー)と A(原子性)を、境界をまたぐ部分で失います。 失った分は、アプリが自分で書くことになります。(Saga・補償トランザクション・結果整合)
判断表 — 今の自分は何をすべきか
| 状況 | やること | やらないこと |
|---|---|---|
| 1台で捌けている | 何もしない。 監視だけ入れる | 先回りしてクラスタを組まない |
| 読み取りが重い | 索引を見直す → 読み取りレプリカを足す | いきなり分割 |
| 書き込みが重い | バッチ化・非同期化 → シャーディング | クラスタを増やしても書き込みは速くならない |
| 落ちると困る | クラスタリング(レプリカ+自動切替) | フェデレーション(可用性は上がらない) |
| 機能ごとに負荷が全然違う | フェデレーション(ログだけ別DBへ) | JOIN が要る2つを分けない |
| 判断がつかない | まず測る。 遅いのがCPUかIOかロックか | 構成図から入る |
一番多い間違いは「可用性が欲しいのにフェデレーションする」と、その逆です。 分けたい理由が「落ちても困らないようにしたい」なら、答えはクラスタリングです。 「1台が重すぎる」なら、答えはフェデレーションかシャーディングです。
つまずいたところ
1. ROLLBACK が Query OK を返すのに、戻っていなかった
エラーが出ないのが一番厄介でした。ALTER TABLE を1行挟んだだけで、 その前の INSERT が確定していました。 ROLLBACK は「暗黙のコミットの後に始まった新しいトランザクション」を 戻しただけです。ROLLBACK が成功したことは、戻ったことの証明になりません。
2. 2>/dev/null でSQLのエラーまで消していた
パスワードの警告がうるさかったので 2>/dev/null を付けました。 MySQL のエラーも標準エラー出力に出ます。 制約違反を起こしているのに何も出ず、 「制約が効いていない」と勘違いしました。2>&1 | grep -v "Using a password" に直しました。
3. CHECK 制約を書いたのに効かなかった(別の環境で)
この記事の MySQL 8.0.39 では効きます。 8.0.16 より前の MySQL では、CHECK は構文として受け付けられて、黙って無視されます。 「書いたから守られている」は成立しません。SHOW CREATE TABLE で、実際に残っているか確認します。
4. 再帰CTEが 1001 回で止まった
ERROR 3636 です。cte_max_recursion_depth の既定は 1000 です。 無限ループを防ぐための安全弁で、バグではありません。 SET SESSION cte_max_recursion_depth = 1000000; を先に打ちます。
5. DELIMITER が効かなくて ERROR 1064 が出た
mysql -e "CREATE PROCEDURE ..." で作ろうとして落ちました。 DELIMITER は SQL ではなく mysql クライアントのコマンドです。 -e では処理されません。対話クライアントに入るか、ファイルを source します。
6. docker compose restart で残ったので「永続性が効いている」と思い込んだ
半分正しくて、半分間違いでした。 コンテナを再起動しただけなら、volumes: を書いていなくても書き込み層に残ります。 本当に永続性を確かめるなら、docker compose down(-v なし)してから up -d します。 ここで消えるなら、それはボリュームを書いていないという Docker 側の問題です。
7. orders_flat だけ古い県名のままになった
非正規化した表を作ったあと、prefs を更新しました。 JOIN 側は直り、非正規化側は嘘のままでした。 これは「更新を忘れた」ではなく、 非正規化を選んだ時点で確定していたコストです。入れるときに、どう同期するかを一緒に決めます。
8. 1回目の測定が遅くて、2回目と3倍違った
MySQL 8.0 にクエリキャッシュはありません。 遅かったのはバッファプールが空だったからです。 「キャッシュが効いた」ではなく 「ディスクから読むのをやめた」が正しい説明です。測るときは3回流して、安定した値を採ります。
後片づけ
必ず消してください。 MySQL は起動しているだけでメモリを数百MB使います。
[+] Running 3/3
✔ Container dbth-mysql Removed 1.9s
✔ Volume dbth_dbdata Removed 0.1s
✔ Network dbth_default Removed 0.2s
-v を付けると、ボリューム(データ本体)まで消えます。 付けないとデータだけ残り続けます。 消えたことを確認します。
何も出なければ消えています。
フォルダごと消す場合はこうです。
rm -rf は戻せません。 パスを打つ前に ls ~/cslab/dbth で中身を確認してください。
まとめ — 理解できたこと
- SQL の4分類(DDL / DML / DQL / DCL)は、暗記用の言葉ではない。 戻せるかどうかが違うという実害の分類だった
ALTER TABLEを1行挟むと、その前のINSERTが確定する。ROLLBACKはQuery OKを返すのに戻らないROLLBACKが成功したことは、戻ったことの証明にならない。 必ずSELECTで数えるTRUNCATEは DELETE に見えて DDL。GRANTも暗黙のコミットを起こす。名前ではなく挙動で分類されている- A(原子性)は「成功させる」性質ではない。「半分だけ成功した状態を残さない」性質。送金の途中で合計が 1700 になった
- C(一貫性)の実体は制約。
CHECKはERROR 3819、外部キーはERROR 1452で止めた。アプリのバリデーションは C ではない - I(分離性)は段階がある。 既定の
REPEATABLE READでは未コミットが見えず、READ UNCOMMITTEDに下げたら見えてしまった - D(永続性)はタダではない。
innodb_flush_log_at_trx_commitを0にすれば速くなるが、COMMIT 済みが消えうる - BASE は ACID の反対語ではなく「一部を捨てた」という宣言。 NoSQL が速いのは、待つのをやめたから
- 残高は ACID、いいね数は BASE。 1つのサービスで両方使う。製品ではなくデータごとに決める
- CAP は分断時の話しかしていない。 平常時の L と C まで決めるのが PACELC。同じ製品でも設定で記法が変わる
- 正規化の目的は容量ではなく、更新時に矛盾しないこと。 同じ事実を1箇所にする
- 非正規化は 4.5 倍速かった(0.41秒 → 0.09秒)が、
prefsを直したら 20万行が嘘になった。 速さのために正しさを一部捨てる判断 - ストアドプロシージャは往復と権限には効くが、Git に乗らない。 個人開発ではまず使わない
- クラスタリングは可用性、フェデレーションは負荷分散。 クラスタは整合性が、フェデレーションは跨いだ JOIN とトランザクションが難しくなる
- 困ったら「まず測る」。
EXPLAINと秒数を出す前に構成図を書かない
参考リンク
- MySQL 8.0 リファレンス: 暗黙のコミットを起こす文の一覧
- MySQL 8.0 リファレンス: InnoDB のトランザクション分離レベル
- MySQL 8.0 リファレンス: CHECK 制約
- MySQL 8.0 リファレンス: 外部キー制約
- MySQL 8.0 リファレンス: GRANT 構文
- MySQL 8.0 リファレンス: CREATE PROCEDURE
- MySQL 8.0 リファレンス: WITH(共通テーブル式・再帰CTE)
- MySQL 8.0 リファレンス: InnoDB の起動オプションとパラメータ
- Docker 公式: Compose の概要
- roadmap.sh: Computer Science
次に読む
この記事は 【深掘り】コンピュータ科学 の 21 / 25 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | UML図とMermaid記法 — 図はすぐ嘘になる |
| → 次の記事 | SSEとWebSocket — 1秒ごとに聞きに行くのをやめる |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- DDL は ROLLBACK できない
- ACID を1文字ずつ壊して確かめる
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント