トランザクションとACID — ROLLBACKしたのに戻らない

トランザクションとACID。データベースとひとまとめの処理カード、確定のチェックと取り消しの矢印。

ROLLBACKしたのに戻らないことがあります。トランザクションとACIDを、実際に落として確かめます。

前回: UML図とMermaid記法 — 図はすぐ嘘になる

これは【深掘り】の記事です。本線の順路ではありません。 このシリーズは言葉を覚える回にしません。 全部、手元で立てて、打って、出力を見ます。 飛ばしても本線は進めます。 本線側の「トランザクションと 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つです。

  1. 新しい列を足す(ALTER TABLE)
  2. 既存の行を新しい列に埋める(UPDATE)
  3. 古い行のうち要らないものを消す(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を分けるのがフェデレーション

この記事でやること

  1. Docker Compose で練習用の MySQL を1つ立てる(既存のDBには一切触らない)
  2. DDL / DML / DQL / DCL を、代表的な文を1つずつ実際に打って区別する
  3. ALTER TABLE で暗黙のコミットを再現し、ROLLBACK が効かないところを出力で見る
  4. ACID を A・C・I・D と1つずつ壊す(送金の途中でエラー / 制約違反 / 2接続 / コンテナ再起動)
  5. BASE と PACELC を表で対比し、どちらを選ぶかの基準を決める
  6. 崩れた表を第1〜第3正規形まで直し、そのあと非正規化して JOIN と SELECT を実測する
  7. ストアドプロシージャを1つ作って呼び、使わない理由まで書く
  8. クラスタリングとフェデレーションの違いを、判断表にして締める
  9. docker compose down -v で全部消す

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
DDLData Definition Language。入れ物を作る・変える SQL。CREATE ALTER DROP TRUNCATE
DMLData Manipulation Language。中身を入れる・直す・消す SQL。INSERT UPDATE DELETE
DQLData Query Language。読む SQL。SELECT だけ。DML に含める流儀もある
DCLData Control Language。権限を与える・取り上げる SQL。GRANT REVOKE
トランザクション「ここからここまでを、全部やるか1つもやらないか」で扱う区切り
暗黙のコミットこちらが COMMIT と書いていないのに、DBが勝手に確定すること
ACIDトランザクションが守るべき4つの性質。原子性・一貫性・分離性・永続性
BASEACID の一部を捨てた側の考え方。だいたい使える・状態は変わる・そのうち揃う
PACELC分断時(P)の A か C に加えて、平常時(E)の L か C まで決める考え方
正規化同じ事実を1箇所にしか置かないようにテーブルを分けること
非正規化速さのために、わざと同じ事実を2箇所に持つこと

① 練習用の MySQL を1つ立てる

既にあるDBを使いません。 壊すからです。新しく立てて、最後に消します。

作業フォルダを作ります。

bash
$ mkdir -p ~/cslab/dbth
$ cd ~/cslab/dbth

Docker がまだ入っていない場合は、先に入れておいてください。Compose の書き方そのものは Composeなら1コマンド — 複数コンテナをまとめる にあります。

compose.yml を1つ書きます。

bash
$ nano compose.yml
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.0latest を使わない。 バージョンで挙動が変わる話をするので、固定する。なお 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 です。

立てます。

bash
$ docker compose up -d
[+] Running 3/3
 ✔ Network dbth_default    Created                                        0.1s
 ✔ Volume "dbth_dbdata"    Created                                        0.0s
 ✔ Container dbth-mysql    Started                                        0.4s

すぐには繋がりません。 MySQL は初回起動でデータ領域を作るので、20秒ほどかかります。 「上がったか」はログで確かめます。

bash
$ docker compose logs db | tail -3
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が壊れたわけではありません。

繋ぎます。

bash
$ docker compose exec db mysql -uroot -pdbth_root_pw dbth
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行だけ流したいときのために、シェルの関数を作っておきます。

bash
$ q() { docker compose exec -T db mysql -uroot -pdbth_root_pw dbth -e "$1" 2>&1 | grep -v "Using a password"; }
bash
$ q "SELECT VERSION(), @@transaction_isolation;"
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> が付いている行が入力です。

bash
$ docker compose exec db mysql -uroot -pdbth_root_pw dbth

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 を試します。 別のシェルから入ります。

bash
$ docker compose exec db mysql -uappuser -papp_pw dbth
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 で戻せるか
DDLCREATE ALTER DROP TRUNCATE入れ物の形戻せない。しかも暗黙のコミットを起こす
DMLINSERT UPDATE DELETE中身の行戻せる(InnoDB の場合)
DQLSELECT読むだけ変えていないので関係なし
DCLGRANT REVOKE CREATE USER権限戻せない。こちらも暗黙のコミット

実害はこれです。 「DDL と DCL は、トランザクションの中に置いても囲われない」。 移行スクリプトが ALTER TABLE と UPDATE を混ぜていると、失敗したとき中途半端な状態で止まります。

次の節で、それを実際に起こします。


③ DDL は ROLLBACK できない(暗黙のコミット)

まず、DML だけなら本当に戻ることを確認します。 対話クライアントに入り直します。

bash
$ docker compose exec db mysql -uroot -pdbth_root_pw dbth

まず、正しく戻る例

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:

bash
$ cd ~/cslab/dbth
$ docker compose exec db mysql -uroot -pdbth_root_pw dbth

ターミナルB: 同じコマンドで、別の接続として入ります。

bash
$ cd ~/cslab/dbth
$ docker compose exec db mysql -uroot -pdbth_root_pw dbth

接続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 のコンテナを、そのまま落として上げ直します。

bash
$ docker compose restart db
[+] Restarting 1/1
 ✔ Container dbth-mysql  Started                                          10.6s

上がるのを待ってから読みます。

bash
$ q "SELECT * FROM accounts ORDER BY id;"
id	owner	balance
1	sato	1000
2	suzuki	1000
3	takahashi	7777

残っています。 これが D(永続性) です。

なぜ残ったのかを1つ確かめておきます。

bash
$ q "SELECT @@innodb_flush_log_at_trx_commit, @@sync_binlog;"
@@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つの言葉の頭文字です。

文字元の言葉意味
BABasically Available基本的に利用可能。一部が壊れても、全体は応答を返し続ける
SSoft state状態は変わりうる。誰も書いていないのに、読むたび値が変わることがある(伝播の途中だから)
EEventually consistent結果的に整合。いつかは揃う。いつとは言わない

ACID と BASE の対比

観点ACIDBASE
書いた直後に読むと必ず新しい値古い値が返ることがある
一部のノードが落ちたら止める(整合性を優先)返し続ける(可用性を優先)
書き込みの速さ遅い。全員の合意を待つ速い。1台に書けたら返す
横に台数を増やすと合意のコストが増えて、頭打ちになるほぼ線形に伸びる
アプリ側の仕事少ない。DBが守る多い。「古い値が来る」前提でコードを書く
代表MySQL / PostgreSQL / OracleCassandra / DynamoDB / Riak

NoSQL が速いのは、賢いアルゴリズムを使っているからではありません。 待つのをやめたからです。 ACID の「全員の合意を待つ」部分を捨てています。

どちらを選ぶかは、業務で決める

技術の好みではありません。「間違ったときに何が起きるか」で決めます。

データ選ぶ側理由
口座残高・在庫数ACID1円ずれたら業務が止まる。 数え直せない
注文の確定ACID「注文できたのに商品が無い」は謝って済まない
いいねの数BASE3秒古くても誰も困らない。 秒間何万回も更新される
閲覧数・ランキングBASEそもそも概算でよい
ログ・イベントBASE1件落ちても全体の傾向は変わらない
ユーザーのプロフィール場合によるメールアドレス変更直後に古いのが出るのは許容範囲か、で決める

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(既定)AL。整合性レベルを上げれば C 寄りにできるPA/EL
DynamoDB(既定の読み)AL。結果整合の読みPA/EL
DynamoDB(強い整合の読み)AC。読み取りコストが2倍かかるPA/EC
MongoDB(既定)AC。プライマリから読む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 sec0.14 sec
2回目0.43 sec0.09 sec
3回目0.41 sec0.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万行を更新する必要があります。

なので順番は、必ずこうです。

  1. まず正規化する。 正しさを構造で守る
  2. 遅いことを測る。 「遅そう」ではなく EXPLAIN と秒数で
  3. 索引で解決できないか先に試す。 非正規化より先に索引
  4. それでも足りないときだけ非正規化し、「どこが嘘になりうるか」をコードのコメントに書く

⑧ ストアドプロシージャを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使います。

bash
$ cd ~/cslab/dbth
$ docker compose down -v
[+] Running 3/3
 ✔ Container dbth-mysql    Removed                                        1.9s
 ✔ Volume dbth_dbdata      Removed                                        0.1s
 ✔ Network dbth_default    Removed                                        0.2s

-v を付けると、ボリューム(データ本体)まで消えます。 付けないとデータだけ残り続けます。 消えたことを確認します。

bash
$ docker volume ls | grep dbth

何も出なければ消えています。

bash
$ docker ps -a | grep dbth

フォルダごと消す場合はこうです。

bash
$ cd ~
$ rm -rf ~/cslab/dbth

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 と秒数を出す前に構成図を書かない

参考リンク


次に読む

この記事は 【深掘り】コンピュータ科学 の 21 / 25 本目です。

記事
← 前の記事UML図とMermaid記法 — 図はすぐ嘘になる
→ 次の記事SSEとWebSocket — 1秒ごとに聞きに行くのをやめる

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

›CS — Database Theory
  • DDL は ROLLBACK できない
  • ACID を1文字ずつ壊して確かめる

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次