DDL(CREATE・ALTER・DROP) — ALTER TABLEは取り消せない

CREATE・ALTER・DROP。組み替えられる表のカードと設計図、工具。

表をどう分けるか(設計・正規化)はテーブル設計と正規化|更新・挿入・削除の異常を実際に起こすで済ませました。この記事は設計の話をしません。「決まった形を、実際に作る・変える・消す操作」だけを扱います。

前回: MySQLのデータ型選び — FLOATで金額は1円ずれる これは【深掘り】です。本線の順路ではありません。 本線のMySQLの導入とSQLの基本|テーブル作成とSELECT・INSERTで CREATE TABLE を1回打ったところの、その1行だけを1本かけて掘り直すものです。読まなくても本線は先に進めます。

作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて appdb がある状態から始めます。練習用のテーブルはこの記事の中で作ります。

SELECT を間違えても、誰も困りません。変な数字が出るだけで、打ち直せば済みます。

ALTER TABLE は違いました。

本線で tasks に列を1つ足したとき、こう打ちました。

ALTER TABLE tasks ADD COLUMN due_date DATE;

一瞬で終わりました。 数行しか入っていない表だったからです。

これを、行が何十万も入っている表に打つと、話がまるで変わります。 私は検証用のVMで同じことをして、こうなりました。

  • ALTER TABLE が10秒以上返ってこない
  • その間、別の画面から打った INSERT が、1行なのに一緒に固まっている
  • SELECT まで固まっている

打ったのは「列を1つ足すだけ」です。 それでサービス全体が止まりました。

そして、もっと悪いことに気づきました。

START TRANSACTION;
ALTER TABLE ...;
ROLLBACK;

この ROLLBACK は、効きません。 トランザクションの回で「全部やるか、1つもやらないか」を覚えた直後に、それが通用しない世界があると知りました。

この記事は、その2つを実際に手元で起こします。

対象レベル: 中級〜上級 / 区分: 発展

目次

この記事でわかること

  • DDL(表を作る・変える・消す文)は、打った瞬間に暗黙でコミットされること。ROLLBACK は効かない。しかもその前に打った INSERT まで巻き込んで確定させる
  • ALTER TABLE には「一瞬で終わるもの」と「表を丸ごと作り直すもの」があること。見た目のSQLはほとんど同じで、区別がつかない。だから自分で調べる打ち方が要る
  • 表を作り直す ALTER が走っている間、その表への書き込みは全部待たされること。待ち時間は行数にほぼ比例する
  • 一瞬で終わるはずの ALTER でも、サービスは止まること。原因は処理時間ではなくメタデータロックで、開きっぱなしのトランザクションが1つあるだけで起きる
  • DELETE と TRUNCATE と DROP は「消す」が全部違うこと。TRUNCATE は ROLLBACK できず、AUTO_INCREMENT も戻す
  • CREATE TABLE ... SELECT は、索引も AUTO_INCREMENT も引き継がないこと。コピーしたつもりが別物になっている

この記事でやること

  1. ROLLBACK が効かないところを実演する(先に効く例を見てから、DDLで壊す)
  2. CREATE TABLE を1行ずつ読み、SHOW CREATE TABLE で「実際に何が作られたか」を確かめる
  3. ALTER TABLE に ALGORITHM=INSTANT を明示して、わざと失敗させる。どれで走るかを自分で調べられるようにする
  4. 80万行の表を作って、COPY が走る ALTER の時間を実測する。 同時に別の画面から INSERT して、待たされるのを見る
  5. DELETE / TRUNCATE / DROP / RENAME を全部打って、違いを目で確かめる
  6. 後片づけをして、練習用のテーブルを全部消す

先に知る言葉

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

言葉意味
DDLData Definition Language。表そのものを作る・変える・消す文。 CREATE ALTER DROP TRUNCATE RENAME
DMLData Manipulation Language。表の中身をいじる文。 INSERT UPDATE DELETE SELECT
暗黙のコミットその文を打った瞬間に、進行中のトランザクションが勝手に確定されること。 自分で COMMIT と打っていなくても起きる
ストレージエンジン表のデータを実際にディスクへ置く担当。 MySQL では既定で InnoDB
照合順序(コレーション)文字を比べるときの規則。 大文字小文字を区別するか、などが決まる
リビルド(表の作り直し)新しい形の空の表を裏で作り、全行をコピーして入れ替えること。 行数の分だけ時間がかかる
オンラインDDL表を変えている間も、読み書きを止めないやり方。できる操作とできない操作がある
メタデータロック(MDL)「この表の形はいま変えないでね」という予約。 SELECT 1本でも、トランザクションを開いている間ずっと持ち続ける
一時テーブルいま繋いでいる接続の中だけに存在する表。 接続を切ると消える
生成列他の列から計算して作られる列。 自分で値を入れない。索引を張れる
マイグレーション表の変更を「コード」としてファイルに残し、順に適用していくやり方。 ORMの回で扱ったもの

① 練習用の場所を作る(d_ で始める)

本線で作った tasks には一切触りません。

深掘り用のテーブルは、すべて d_ で始めることにします(d は deep のつもりです)。前回までの深掘りと同じ規則です。

DDLの練習では、この規則がとくに効きます。 この記事では DROP TABLE を何度も打つからです。d_ で始まっていない表は打たない、と決めておけば、指が滑っても tasks は消えません。

VMに入って、MySQL に接続します。

bash
$ sudo mysql
USE appdb;
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.39-0ubuntu0.22.04.1 |
+-----------+

8.0 より前だと、この記事の半分は動きません。 ALGORITHM=INSTANT も、生成列への索引も、8.0 の機能です。数字が 5.7 で始まっていたら、ここで止まってください。

作業を始める前に、いま何があるかを見ておきます。

SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| tasks           |
| users           |
+-----------------+

この一覧を覚えておいてください。 最後の後片づけで、ここに戻ることを確認します。

② 一番大事なところ — DDLは ROLLBACK できない

先にこれをやります。 他のどの話より先に、これを体で分かっておかないと、このあとの実験でうっかり本番相当のデータを壊したときに戻せないからです。

まず、ROLLBACK が効く例を見る

練習用の表を1つ作ります。

CREATE TABLE d_tx (
  id INT NOT NULL PRIMARY KEY,
  memo VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT して、ROLLBACK します。

START TRANSACTION;
INSERT INTO d_tx VALUES (1, '取り消すつもり');
SELECT COUNT(*) FROM d_tx;
ROLLBACK;
SELECT COUNT(*) FROM d_tx;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

1行入って、ROLLBACK で消えました。 これがトランザクションの回でやった、期待どおりの動きです。

同じことを、DDLを1文挟んでやる

今度は、INSERT と ROLLBACK のあいだに CREATE TABLE を1つ挟むだけです。

START TRANSACTION;
INSERT INTO d_tx VALUES (2, 'これも取り消すつもり');
CREATE TABLE d_tx_side (id INT);
ROLLBACK;
SELECT * FROM d_tx;
+----+----------------------+
| id | memo                 |
+----+----------------------+
|  2 | これも取り消すつもり |
+----+----------------------+

残っています。

ROLLBACK と打ったのに、INSERT が生きています。しかも、エラーも警告も出ていません。

挟んだ表のほうも見ます。

SHOW TABLES LIKE 'd_tx%';
+------------------------+
| Tables_in_appdb (d_tx%) |
+------------------------+
| d_tx                   |
| d_tx_side              |
+------------------------+

d_tx_side も残っています。 ROLLBACK は、どちらにも効きませんでした。

何が起きたのか

CREATE TABLE を打った瞬間に、MySQL が勝手に COMMIT を打ちました。

これを暗黙のコミットと言います。順番はこうです。

  1. START TRANSACTION — トランザクション開始
  2. INSERT — まだ確定していない
  3. CREATE TABLE — ここで MySQL が「先に COMMIT しますね」と勝手に確定する。 そのあと表を作る
  4. ROLLBACK — もう何も残っていない。 取り消すものが無いので、何も起きない

つまり ROLLBACK が「効かなかった」のではなく、ROLLBACK する前に全部終わっていたのです。

これは CREATE TABLE だけの話ではありません。 ALTER TABLE でも同じです。

START TRANSACTION;
ALTER TABLE d_tx ADD COLUMN dummy INT NULL;
ROLLBACK;
SHOW COLUMNS FROM d_tx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| memo  | varchar(30) | NO   |     | NULL    |       |
| dummy | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

dummy 列が残っています。

「アトミックDDL」は「ロールバックできる」ではない

MySQL 8.0 の説明を読むと「アトミックDDLに対応した」と書いてあります。これを「トランザクションで囲めるようになった」と読むと、間違えます。

アトミックDDLが保証しているのはこれだけです。

ALTER TABLE の途中でサーバーが落ちても、「列が半分だけ足された表」は残らない。 その1文が丸ごと成功しているか、丸ごと無かったことになっているかの、どちらかになる。

「あなたが ROLLBACK と打てば戻せる」とは、一言も書いていません。 落ちたときに壊れない、という話です。

PostgreSQL は違います。 PostgreSQLの回で触れたとおり、あちらは DDL もトランザクションに入り、ROLLBACK で戻ります。 「マイグレーションを1つのトランザクションで囲む」という話が通じるのは、そちらの世界です。MySQL では通じません。

だから、この記事の実験には戻し方が要る

ROLLBACK が使えないということは、やり直す手段を自分で用意しないといけないということです。

この記事では、次の2つでそれをやります。

  • すべての CREATE TABLE の前に DROP TABLE IF EXISTS を置く。 何度でも同じ状態から始め直せる
  • 触るのは d_ で始まる表だけにする

この2つは、覚え書きではなく安全装置です。 練習中に表が変な形になったら、DROP して作り直せば終わります。

後片づけしておきます。

DROP TABLE IF EXISTS d_tx, d_tx_side;

③ CREATE TABLE を1行ずつ読む

本線では、こう打ちました。

CREATE TABLE tasks (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100));

動きます。 ただし、このとき MySQL が実際に何を作ったかは、この文には書かれていません。 書かなかった部分は、全部サーバーの既定値で埋まっています。

書ける形を、全部書いてみます。

DROP TABLE IF EXISTS d_item;

CREATE TABLE IF NOT EXISTS d_item (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code       VARCHAR(20)  NOT NULL COMMENT '商品コード',
  name       VARCHAR(60)  NOT NULL COMMENT '表示名',
  price      INT          NOT NULL DEFAULT 0,
  note       VARCHAR(200) NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_d_item_code (code)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci
  COMMENT='DDL練習用の商品表';

上から順に、何を言っているか。

書いたもの意味
IF NOT EXISTS同じ名前の表があったら、何もせず成功したことにする(後述。これが曲者)
BIGINT UNSIGNED符号なしの大きい整数。 マイナスを使わないなら、同じバイト数で倍の範囲が使える
AUTO_INCREMENT入れなければ自動で次の番号が入る列。表に1つだけ
NOT NULL空を許さない。 SELECTの深掘りでやったとおり、NULL は事故のもとなので、要らないなら禁止しておく
DEFAULT 0省略したときに入る値
COMMENT '...'列や表に付ける説明。 消えずに残り、SHOW CREATE TABLE で読める
PRIMARY KEY (id)行を1つに決める列。 InnoDB では、これがデータの並び順そのものになる
UNIQUE KEY重複を禁止する。 同時に索引にもなる
ENGINE=InnoDBストレージエンジン。 トランザクションと外部キーが使えるのは InnoDB
DEFAULT CHARSET=utf8mb4文字コード。 文字コードの回でやったとおり、utf8 ではなく utf8mb4。絵文字が入る
COLLATE=...文字の比べ方。 ai はアクセント無視、ci は大文字小文字無視

SHOW CREATE TABLE で、実際に作られたものを見る

ここが型です。 CREATE TABLE を打ったら、必ず打ち返して確認します。

SHOW CREATE TABLE d_item\G
*************************** 1. row ***************************
       Table: d_item
Create Table: CREATE TABLE `d_item` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '商品コード',
  `name` varchar(60) NOT NULL COMMENT '表示名',
  `price` int NOT NULL DEFAULT '0',
  `note` varchar(200) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_d_item_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='DDL練習用の商品表'

末尾の \G は「縦に並べて表示して」という意味です。; の代わりに打ちます。横に長い結果はこちらのほうが読めます。

打った文と見比べてください。 大事なのは、違っているところです。

  • note の NULL が DEFAULT NULL に化けている。 「空を許す列」は、書かなくても既定値が NULL になる
  • DEFAULT 0 が DEFAULT '0' になっている。 表示上の差で、中身は数値のまま
  • bigint(20) ではなく bigint。 8.0 から、整数の後ろの表示幅は出なくなった

SHOW CREATE TABLE は「自分が書いたもの」ではなく「サーバーが理解したもの」を返します。 差があったら、サーバーのほうが正しいです。

IF NOT EXISTS は「同じ形になる」を保証しない

ここは事故ります。

IF NOT EXISTS は、同名の表が既にあったら、中身を一切見ずに何もしません。 形が全然違っていても、です。

CREATE TABLE IF NOT EXISTS d_item (id INT);
Query OK, 0 rows affected, 1 warning (0.00 sec)

「成功」と出ました。 列1つの表を作ったつもりです。警告を読みます。

SHOW WARNINGS;
+-------+------+-----------------------------------+
| Level | Code | Message                           |
+-------+------+-----------------------------------+
| Note  | 1050 | Table 'd_item' already exists     |
+-------+------+-----------------------------------+

何も作られていません。 さっきの列6つの表がそのままです。

「エラーが出なかったから、思ったとおりの表がある」は成り立ちません。 IF NOT EXISTS を使うなら、そのあと必ず SHOW CREATE TABLE で形を見るところまでを1組にしてください。

④ 表をコピーする2つの方法 — 片方は別物ができる

「いまの表と同じものを、もう1つ作りたい」は、よくあります。書き方が2つあって、結果が全然違います。

LIKE — 形だけをそっくりコピーする

DROP TABLE IF EXISTS d_item_like;
CREATE TABLE d_item_like LIKE d_item;

中身は入りません。入れたいなら別途 INSERT します。

INSERT INTO d_item (code, name, price, note)
VALUES ('A-001', 'ねじ', 120, NULL),
       ('A-002', 'ボルト', 300, '在庫少'),
       ('A-003', 'ナット', 80, NULL);

INSERT INTO d_item_like SELECT * FROM d_item;
SELECT COUNT(*) FROM d_item_like;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

SELECT — 中身ごとコピーする(形は保証されない)

DROP TABLE IF EXISTS d_item_sel;
CREATE TABLE d_item_sel AS SELECT * FROM d_item;
SELECT COUNT(*) FROM d_item_sel;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

行数は同じです。 「コピーできた」と思うところです。形を見ます。

SHOW CREATE TABLE d_item_like\G
SHOW CREATE TABLE d_item_sel\G
*************************** 1. row ***************************
       Table: d_item_like
Create Table: CREATE TABLE `d_item_like` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '商品コード',
  `name` varchar(60) NOT NULL COMMENT '表示名',
  `price` int NOT NULL DEFAULT '0',
  `note` varchar(200) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_d_item_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='DDL練習用の商品表'

*************************** 1. row ***************************
       Table: d_item_sel
Create Table: CREATE TABLE `d_item_sel` (
  `id` bigint unsigned NOT NULL,
  `code` varchar(20) NOT NULL COMMENT '商品コード',
  `name` varchar(60) NOT NULL COMMENT '表示名',
  `price` int NOT NULL,
  `note` varchar(200) DEFAULT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

d_item_sel から消えているものを数えてください。

引き継がれたもの引き継がれなかったもの
列の名前・型・NOT NULLPRIMARY KEY
列の COMMENTUNIQUE KEY(=索引が全部消えた)
文字コードと照合順序AUTO_INCREMENT
DEFAULT 0 と DEFAULT CURRENT_TIMESTAMP
表の COMMENT
外部キー(LIKE でも引き継がれません)

索引が無いということは、索引の回でやった全表走査になるということです。行が増えたときに、コピー元と同じ速さは出ません。

もっと直接的に壊れます。 使ってみます。

INSERT INTO d_item_sel (code, name) VALUES ('A-001', '重複できてしまう');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

AUTO_INCREMENT が無いので、id を自分で書かないと入りません。 書いてみます。

INSERT INTO d_item_sel (id, code, name, price, created_at)
VALUES (99, 'A-001', '重複できてしまう', 0, NOW());
SELECT id, code, name FROM d_item_sel WHERE code = 'A-001';
+----+-------+------------------+
| id | code  | name             |
+----+-------+------------------+
|  1 | A-001 | ねじ             |
| 99 | A-001 | 重複できてしまう |
+----+-------+------------------+

code が重複しました。 コピー元では UNIQUE KEY が禁じていたものです。エラーは出ません。静かに壊れます。

使い分けの結論 – 形をそのまま増やしたい → CREATE TABLE ... LIKE。 そのあと INSERT ... SELECT で中身を入れる – 「いまの中身を一時的に取っておきたい」だけ → CREATE TABLE ... SELECT でよい。 ただしそれは表の複製ではなく、結果のメモだと思うこと – どちらを使っても、SHOW CREATE TABLE で見比べるまで終わりではない

⑤ ALTER TABLE は、実は4種類ある

ALTER TABLE の書き方は、まずこれだけ押さえます。

ALTER TABLE d_item ADD COLUMN stock INT NOT NULL DEFAULT 0;
ALTER TABLE d_item MODIFY COLUMN note VARCHAR(400) NULL;
ALTER TABLE d_item CHANGE COLUMN note memo VARCHAR(400) NULL;
ALTER TABLE d_item RENAME COLUMN memo TO note;
ALTER TABLE d_item DROP COLUMN stock;
書き方やること
ADD COLUMN列を足す
MODIFY COLUMN 列名 新しい定義名前はそのまま、定義だけ変える
CHANGE COLUMN 旧名 新名 新しい定義名前と定義を同時に変える。定義を丸ごと書き直す必要がある
RENAME COLUMN 旧名 TO 新名名前だけ変える(8.0から)
DROP COLUMN列を消す

MODIFY と CHANGE は、書かなかった属性を消す

ここが ALTER TABLE で一番よくやる事故です。

MODIFY は「一部を変える」ではありません。「その列の定義を、これで全部置き換える」です。

いまの price はこうでした。

  `price` int NOT NULL DEFAULT '0',

「もっと大きい数を入れたいので BIGINT にする」つもりで、こう打ちます。

ALTER TABLE d_item MODIFY COLUMN price BIGINT;
SHOW CREATE TABLE d_item\G
  `price` bigint DEFAULT NULL,

NOT NULL が消えました。DEFAULT 0 も消えました。

エラーは出ていません。この表は、いまから price に NULL を受け付けます。 アプリ側は「price は必ず数値」のつもりで書かれているので、どこか遠くで落ちます。

同じことが COMMENT でも起きます。

ALTER TABLE d_item MODIFY COLUMN code VARCHAR(20) NOT NULL;
SHOW CREATE TABLE d_item\G
  `code` varchar(20) NOT NULL,

COMMENT '商品コード' が消えました。

正しい打ち方は、「変えたいところ以外も、いまの定義を全部書く」です。

ALTER TABLE d_item
  MODIFY COLUMN price BIGINT NOT NULL DEFAULT 0,
  MODIFY COLUMN code VARCHAR(20) NOT NULL COMMENT '商品コード';

そのために、ALTER TABLE を打つ前に SHOW CREATE TABLE を打ちます。 いまの定義を目で見て、それをコピーして直す。思い出して書かない。

名前だけ変えたいなら RENAME COLUMN を使ってください。 CHANGE は定義を書き直させるので、名前を変えるだけのつもりで属性を消す、という事故に直結します。

複数の変更は、1文にまとめる

ALTER TABLE は、カンマで区切っていくつでも並べられます。

ALTER TABLE d_item
  ADD COLUMN stock INT NOT NULL DEFAULT 0,
  ADD COLUMN maker VARCHAR(40) NULL,
  ADD INDEX idx_d_item_name (name);

バラバラに3回打つより、まとめて1回のほうが速いです。表を作り直す種類の変更なら、作り直しが3回から1回に減るからです。

⑥ どれで走っているのか — ALGORITHM を明示して調べる

ここが、この記事の中心です。

ALTER TABLE は、内部では3つのやり方のどれかで走ります。SQLの見た目からは区別がつきません。

やり方何をするかかかる時間書き込みは
INSTANT表の「形の説明書」だけを書き換える。 データには触らない行数に関係なく一瞬止まらない(ただし後述のMDLは要る)
INPLACEいまの表を、その場で作り替える。索引を作り直したりする行数なりにかかる多くの場合は止まらない
COPY新しい形の空の表を裏に作り、全行をコピーして入れ替える行数に比例。長い全部待たされる

問題は、「今回どれで走ったか」を MySQL が教えてくれないことです。 打ったら返ってきた、それだけです。小さい表で試すと、COPY でも一瞬で終わるので気づきません。

調べ方 — 期待するやり方を書いて、わざと失敗させる

ALTER TABLE の末尾に , ALGORITHM=INSTANT と書けます。

これは「INSTANT でできるならやって。できないならエラーにして。」という意味です。勝手に重いやり方に落ちません。

まず、成功する例。

ALTER TABLE d_item ADD COLUMN memo2 VARCHAR(50) NULL, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Records: 0 に注目してください。 1行も触っていません。列を足したのに、です。 これが INSTANT です。

次に、失敗する例。

ALTER TABLE d_item MODIFY COLUMN code VARCHAR(30) NOT NULL COMMENT '商品コード', ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

「その操作は一瞬ではできません」と、はっきり言ってくれました。 エラーメッセージが次に試すべきものまで書いています。落とします。

ALTER TABLE d_item MODIFY COLUMN code VARCHAR(30) NOT NULL COMMENT '商品コード', ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.03 sec)

INPLACE では通りました。 VARCHAR(20) を VARCHAR(30) に伸ばすのは、表を作り直さずにできます。

同じ「文字列を伸ばす」でも、作り直しになる境目がある

ここが面白いところです。 今度は name を VARCHAR(60) から VARCHAR(100) に伸ばします。さっきと同じ種類の変更です。

ALTER TABLE d_item MODIFY COLUMN name VARCHAR(100) NOT NULL COMMENT '表示名', ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

同じ操作なのに、今度は断られました。

理由はバイト数です。utf8mb4 は1文字あたり最大4バイトなので、

  • VARCHAR(20) → 最大 80 バイト、VARCHAR(30) → 最大 120 バイト。どちらも 255 以下
  • VARCHAR(60) → 最大 240 バイト、VARCHAR(100) → 最大 400 バイト。255 をまたいだ

InnoDB は、255バイト以下の文字列なら「長さ」を1バイトで記録し、それを超えると2バイトで記録します。 つまり 255 をまたぐと、ディスク上の1行の形そのものが変わります。 だから全行を書き直すしかありません。

この境目は、SQLを見ても分かりません。 分かるのは、明示して打って、断られるかどうかです。

LOCK=NONE も一緒に書く

ALGORITHM と同じ場所に、LOCK も書けます。

ALTER TABLE d_item MODIFY COLUMN name VARCHAR(100) NOT NULL COMMENT '表示名', ALGORITHM=COPY, LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

「書き込みを止めずにやることはできません」と断られました。

これが安全装置です。

本番に打つ ALTER TABLE には、必ず , ALGORITHM=INSTANT か , LOCK=NONE を書き足す。 通れば安全、エラーが出たら「これは重い変更だ」と分かる。

何も書かないと、MySQL は黙って一番重いやり方に落ちて、そのまま走り出します。 止められるのは打つ前だけです。

バージョンで変わるので、暗記しない

どの操作が INSTANT になるかは、MySQL のバージョンで増えています。 たとえば「列を末尾以外に足す」「列を消す」は、途中のバージョンから INSTANT でできるようになりました。

だから覚えるのではなく、毎回打って確かめてください。 手元の 8.0 で試すのが一番速く、そして確実です。

INSTANT は無限には使えません。 同じ表に INSTANT の列変更を重ねていくと、ある回数で「もう作り直しが要る」と言われます。「ずっと一瞬で済む」ではないことは頭に入れておいてください。

⑦ 実測 — COPY が走ると、書き込みは何秒待つのか

言葉で「遅い」と書いても身に付かないので、測ります。

大きい表を作る

DROP TABLE IF EXISTS d_big;
CREATE TABLE d_big (
  id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  payload VARCHAR(200) NOT NULL,
  n       INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5万行を一気に作ります。

SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO d_big (payload, n)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 50000
)
SELECT CONCAT('payload-', n), n FROM seq;

WITH RECURSIVE は「自分自身を呼び出す SELECT」です。1 から始めて、前の値に1を足す行を作り続けます。行が無いところから行を作るときに使えます。 cte_max_recursion_depth を先に上げているのは、既定が 1000 回で打ち切りだからです。上げないと ERROR 3636 で止まります。

ここから倍々で増やします。

INSERT INTO d_big (payload, n) SELECT payload, n FROM d_big;

これを打つたびに行数が倍になります。4回打って 80万行にします。

SELECT COUNT(*) FROM d_big;
+----------+
| COUNT(*) |
+----------+
|   800000 |
+----------+

COPY の ALTER を打って、時間を見る

payload を VARCHAR(200) から VARCHAR(300) に伸ばします。さっきの 255 バイトの境目を越えているので、COPY になります。まず、それを確かめます。

ALTER TABLE d_big MODIFY COLUMN payload VARCHAR(300) NOT NULL, ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

COPY 確定です。 覚悟して打ちます。

ALTER TABLE d_big MODIFY COLUMN payload VARCHAR(300) NOT NULL, ALGORITHM=COPY;
Query OK, 800000 rows affected (10.62 sec)
Records: 800000  Duplicates: 0  Warnings: 0

Records: 800000。 さっきの INSTANT が Records: 0 だったのと比べてください。80万行を、全部読んで、全部書き直しています。

行数を変えて同じことを繰り返した結果です。このVMでの実測値なので、数字そのものは環境で変わります。

行数ALGORITHM=COPY の ALTER
10万行約 1.3 秒
20万行約 2.6 秒
40万行約 5.2 秒
80万行約 10.6 秒

見るのは秒数ではなく、形です。行数が倍になると、時間も倍になっています。

この比例を、そのまま先に伸ばしてください。

  • 800万行 → 100秒
  • 8000万行 → 1000秒(17分)

17分のあいだ、その表への書き込みが全部止まります。

別の画面から書き込んで、待たされるのを見る

「止まる」を、実際に見ます。SSHの接続をもう1つ開いてください。

VS Code のターミナルでも、別の端末でも構いません。2つの画面それぞれで sudo mysql に入って、USE appdb; しておきます。

画面Aで、重い ALTER を打ちます。

ALTER TABLE d_big MODIFY COLUMN payload VARCHAR(200) NOT NULL, ALGORITHM=COPY;

返ってこないうちに、画面Bで1行だけ入れます。

INSERT INTO d_big (payload, n) VALUES ('割り込みたい', 0);
Query OK, 1 row affected (8.91 sec)

1行の INSERT に 8.91 秒かかりました。

この INSERT 自体は一瞬で終わる仕事です。 待っていただけです。アプリから見ると、これは「保存ボタンを押したまま9秒固まった」ということです。

待っているあいだに、画面C(もう1つ開くか、画面Bで ALTER の前に)でこう打つと、待ち行列が見えます。

SHOW PROCESSLIST;
+----+------+-----------+-------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host      | db    | Command | Time | State                           | Info                               |
+----+------+-----------+-------+---------+------+---------------------------------+------------------------------------+
|  9 | root | localhost | appdb | Query   |    6 | copy to tmp table               | ALTER TABLE d_big MODIFY COLUMN... |
| 10 | root | localhost | appdb | Query   |    4 | Waiting for table metadata lock | INSERT INTO d_big (payload, n) ... |
+----+------+-----------+-------+---------+------+---------------------------------+------------------------------------+

copy to tmp table — 裏で一時的な表にコピーしている最中だ、という意味です。名前のとおりのことをしています。

Waiting for table metadata lock — 「表の形が変わりそうなので待っています」。これがサービスが止まっているときに出る文字列です。覚えておいてください。

⑧ INSTANT でも止まる — メタデータロックという本当の犯人

ここまで読むと「INSTANT で打てば安全」と思います。私はそう思いました。違いました。

ALTER TABLE は、どんなに一瞬でも、その一瞬だけ表を独占します。 「いま形を変えるので、誰も触らないでください」という予約が要ります。これがメタデータロック(MDL)です。

そして、この予約は「誰も表を使っていない状態」でしか取れません。

実演 — SELECT 1本で ALTER が止まる

画面Bで、トランザクションを開いて、SELECT を1本打ちます。COMMIT しません。そのまま放置します。

START TRANSACTION;
SELECT COUNT(*) FROM d_item;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

打ち終わっています。何も動いていません。 ですが、この接続は d_item を掴んだままです。

画面Aで、一瞬で終わるはずの ALTER を打ちます。

ALTER TABLE d_item ADD COLUMN mdl_test INT NULL, ALGORITHM=INSTANT;

返ってきません。

INSTANT です。データには1バイトも触りません。それでも、返ってきません。

さらに悪いのはここからです。画面Cで、ただの SELECT を打ちます。

SELECT * FROM d_item LIMIT 1;

これも返ってきません。

画面Dがあれば SHOW PROCESSLIST; を打つと、こうなっています。

| 10 | root | localhost | appdb | Sleep |  120 |                                 | NULL                            |
|  9 | root | localhost | appdb | Query |   45 | Waiting for table metadata lock | ALTER TABLE d_item ADD COLUMN...|
| 11 | root | localhost | appdb | Query |   30 | Waiting for table metadata lock | SELECT * FROM d_item LIMIT 1    |

上から順に読んでください。これが障害の全体像です。

  1. 接続10は Sleep。 何もしていません。ただトランザクションを閉じていないだけです
  2. ALTER は、その接続10を待っています
  3. 後から来た SELECT は、ALTER の後ろに並びました

「読むだけ」の SELECT まで止まっています。 待っている ALTER の後ろには、あとから来たものが全部積み上がります。 アプリの接続数はすぐ上限に達し、表1つの問題がサイト全体の停止になります。

画面Bで COMMIT を打つと、全部いっぺんに動き出します。

COMMIT;

これが「一瞬で終わる ALTER を打ってサービスが止まった」の正体でした。 犯人は ALTER の処理時間ではなく、開きっぱなしのトランザクション1本です。

対策 — 諦めさせる

待ち続けさせないことが一番大事です。

SET SESSION lock_wait_timeout = 10;
ALTER TABLE d_item ADD COLUMN mdl_test2 INT NULL, ALGORITHM=INSTANT;

10秒で予約が取れなければ、こうなります。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

失敗してくれました。これが正解です。

ALTER が失敗すれば、後ろに並んでいたものは並ばずに済みます。「サイトが止まる」が「自分の ALTER が1回失敗する」に変わりました。

この1行を、ALTER TABLE の前に必ず置いてください。

⑨ 安全に ALTER TABLE を打つ手順

ここまでを、順番にまとめます。本番の表に打つときは、上から全部やります。

  1. バックアップを取る。 その表だけでよければ、これで足ります
bash
$     mysqldump -u root -p appdb d_item > ~/d_item_backup.sql

戻せることまで確認してください。 ファイルができただけでは、バックアップではありません

  1. SHOW CREATE TABLE で、いまの定義をコピーしておく。 MODIFY で属性を消さないため
  2. 同じ行数の検証環境で先に打つ。 ここで秒数を測ります。 3行の表で試しても何も分かりません
    SELECT COUNT(*) FROM d_item;
  1. ALGORITHM=INSTANT を明示して打ってみる。 通れば、その時点でほぼ終わりです
  2. 通らなければ、LOCK=NONE を明示する。 それも通らなければ「書き込みが止まる変更」だと確定します
  3. SET SESSION lock_wait_timeout = 10; を必ず先に打つ
  4. 止まっていい時間帯に打つ。 測った秒数の3倍は見ておきます
  5. 打ったあと、SHOW CREATE TABLE で結果を確認する

何時間もかかる表には、専用の道具がある

行数が多すぎて、どう頑張っても止められる時間に収まらないことがあります。そのための道具が、外部にあります。名前だけ覚えておいてください。

  • pt-online-schema-change(Percona Toolkit)
  • gh-ost(GitHub製)

どちらも、やっていることは⑩でやる「新しい表を作って、詰めて、名前を入れ替える」です。 それを、コピー中に入った変更を取りこぼさないように自動でやってくれます。

この記事の範囲では使いません。 「手で ALTER を打つのが無理な規模になったら、こういう名前のものを調べる」とだけ知っていれば十分です。

⑩ 消し方は3つある — DELETE / TRUNCATE / DROP

「消す」と一口に言いますが、3つあって全部違います。

DELETE FROM tTRUNCATE TABLE tDROP TABLE t
何が残るか表は残る。行が消える表は残る。行が消える表ごと無くなる
種類DMLDDLDDL
速さ行数に比例(遅い)一瞬(作り直すだけ)一瞬
ROLLBACKできるできないできない
WHERE書ける書けない(全部消える)—
AUTO_INCREMENT戻らない(続きから)1に戻る—
外部キーで参照されている表参照している行があればエラー常にエラー(行が無くても)エラー(先に子を消す)
トリガ動く動かない—

実際に打って確かめます。

DROP TABLE IF EXISTS d_del;
CREATE TABLE d_del (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  v  VARCHAR(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO d_del (v) VALUES ('a'), ('b'), ('c');

ROLLBACK できるか

START TRANSACTION;
DELETE FROM d_del;
ROLLBACK;
SELECT COUNT(*) FROM d_del;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

DELETE は戻りました。 次。

START TRANSACTION;
TRUNCATE TABLE d_del;
ROLLBACK;
SELECT COUNT(*) FROM d_del;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

TRUNCATE は戻りません。 ②でやったとおり、TRUNCATE は DDL なので、打った瞬間に暗黙のコミットが起きています。

DELETE FROM t; を WHERE 無しで打ったときは、まだ助かる可能性があります(ROLLBACK すれば戻る)。 TRUNCATE TABLE t; を打ってしまったら、バックアップからしか戻せません。 「速いから TRUNCATE」で選んではいけない理由がこれです。

AUTO_INCREMENT はどうなるか

INSERT INTO d_del (v) VALUES ('a'), ('b'), ('c');
DELETE FROM d_del;
INSERT INTO d_del (v) VALUES ('あとから');
SELECT * FROM d_del;
+----+----------+
| id | v        |
+----+----------+
|  4 | あとから |
+----+----------+

id が 4 から始まりました。 DELETE は番号を戻しません。

TRUNCATE TABLE d_del;
INSERT INTO d_del (v) VALUES ('truncate後');
SELECT * FROM d_del;
+----+------------+
| id | v          |
+----+------------+
|  1 | truncate後 |
+----+------------+

TRUNCATE は 1 に戻しました。

これは、どちらが正しいという話ではありません。

  • DELETE が番号を戻さないのは、消した行の id を他の表が覚えているかもしれないからです。番号を使い回すと、別人の行に繋がります
  • TRUNCATE は「この表を作り直す」なので、まっさらから始まります

本番の表に TRUNCATE を打つと、番号が振り直されます。 これは、テーブル設計の回でやった外部キーの繋がりを、静かに壊します。

外部キーがあると、3つとも別々に怒られる

DROP TABLE IF EXISTS d_child, d_parent;

CREATE TABLE d_parent (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE d_child (
  id INT NOT NULL PRIMARY KEY,
  parent_id INT NOT NULL,
  CONSTRAINT fk_d_child_parent FOREIGN KEY (parent_id) REFERENCES d_parent(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO d_parent VALUES (1, '親');
INSERT INTO d_child  VALUES (1, 1);

3つとも打ってみます。

DELETE FROM d_parent;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
TRUNCATE TABLE d_parent;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
DROP TABLE d_parent;
ERROR 3730 (HY000): Cannot drop table 'd_parent' referenced by a foreign key constraint 'fk_d_child_parent' on table 'd_child'

3つとも止まりました。エラー番号も文面も別々です。

そして、TRUNCATE の断り方だけが違います。 子の行を全部消しても、TRUNCATE は断ります。

DELETE FROM d_child;
TRUNCATE TABLE d_parent;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

d_child は空なのに、まだ断られます。 「参照している行があるか」ではなく「参照する約束がされているか」で見ているからです。

外部キーは、消し方まで縛ってくれます。 これは邪魔ではなく、守ってもらっているということです。

⑪ RENAME TABLE は一瞬 — 入れ替えの定石

名前を変えるのは、行数に関係なく一瞬です。 データを1バイトも動かさないからです。

RENAME TABLE d_del TO d_del_old;
Query OK, 0 rows affected (0.01 sec)

80万行の d_big でも同じです。 試してみてください。

そして、RENAME TABLE は複数の名前を同時に、途中経過なしで入れ替えられます。

RENAME TABLE d_item TO d_item_tmp,
             d_item_like TO d_item,
             d_item_tmp TO d_item_like;

この途中で「d_item という名前が存在しない一瞬」はありません。 だから、アプリが動いたまま入れ替えられます。

定石 — 新しい表を作って、詰めて、名前を交換する

⑦でやったように、ALTER TABLE が10分かかるなら、その10分は書き込みが止まります。その代わりに、こうします。

CREATE TABLE d_big_new LIKE d_big;
ALTER TABLE d_big_new MODIFY COLUMN payload VARCHAR(300) NOT NULL;
INSERT INTO d_big_new SELECT * FROM d_big;
RENAME TABLE d_big TO d_big_old, d_big_new TO d_big;
  1. 新しい形の空の表を作る — 元の表は無傷
  2. その空の表に ALTER を打つ — 0行なので一瞬
  3. 中身を流し込む — この間、元の表は普通に読み書きできる
  4. 名前を交換する — 一瞬

⑧でやった pt-online-schema-change も gh-ost も、やっていることはこれです。

ただし、このままでは使えません。 3番の INSERT ... SELECT をしている最中に元の表へ入った行は、新しい表に入りません。消えます。 道具を使う理由がここです。 pt-online-schema-change はトリガで、gh-ost は更新ログを読んで、コピー中の変更を新しい表へ追いかけさせます。 手で書くものではありません。

もう1つ。外部キーは名前を追いかけます。 d_big を参照している外部キーがあると、RENAME したあとはd_big_old を指したままになります。外部キーのある表では、この定石を安易に使わないでください。

⑫ 一時テーブル — 接続を切ると消える

計算の途中結果を置いておきたいことがあります。CREATE TEMPORARY TABLE を使います。

CREATE TEMPORARY TABLE d_tmp_calc AS
SELECT n % 10 AS bucket, COUNT(*) AS cnt
FROM d_big
GROUP BY n % 10;

SELECT * FROM d_tmp_calc ORDER BY bucket LIMIT 3;
+--------+-------+
| bucket | cnt   |
+--------+-------+
|      0 | 80001 |
|      1 | 80000 |
|      2 | 80000 |
+--------+-------+

一覧には出ません。

SHOW TABLES LIKE 'd_tmp%';
Empty set (0.00 sec)

SHOW TABLES は一時テーブルを出しません。 存在するのに、見えません。

そして、接続を切ると消えます。

EXIT;
bash
$ sudo mysql
USE appdb;
SELECT * FROM d_tmp_calc;
ERROR 1146 (42S02): Table 'appdb.d_tmp_calc' doesn't exist

片づけ忘れが起きないのが利点です。 逆に言えば、接続が切れたら中身は取り戻せません。

本物の表と同じ名前が付けられる

ここは危ないので、一度見ておいてください。

CREATE TEMPORARY TABLE d_item (id INT);
SELECT COUNT(*) FROM d_item;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

d_item には行が入っているはずなのに、0 です。

同じ名前の一時テーブルは、本物の表を隠します。 この接続の中では、d_item と書くと一時テーブルのほうが当たります。

DROP TABLE d_item; と打ったら、消えるのは一時テーブルのほうです。 「消したのに残っている」「消えるはずのないものが消えた」の両方が起きます。

DROP TEMPORARY TABLE d_item;
SELECT COUNT(*) FROM d_item;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

戻りました。

一時テーブルにも d_tmp_ のような接頭辞を付けて、本物と絶対にぶつからない名前にしてください。 消すときは DROP TEMPORARY TABLE と書く。 こう書けば、本物の表を掴んだ場合はエラーになります。

⑬ 生成列 — JSONに索引を張る

列は、他の列から計算して作ることもできます。 これを生成列と言います。

使いどころがはっきりしているのは、JSONです。 NoSQLの回で見たように、MySQL にも JSON 型があります。ただし JSONの中身は、そのままでは索引が張れません。

DROP TABLE IF EXISTS d_json;
CREATE TABLE d_json (
  id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  doc JSON NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO d_json (doc) VALUES
  ('{"email":"a@example.com","age":30}'),
  ('{"email":"b@example.com","age":41}');

ALTER TABLE d_json
  ADD COLUMN email VARCHAR(191)
    GENERATED ALWAYS AS (doc->>'$.email') VIRTUAL,
  ADD INDEX idx_d_json_email (email);

SELECT id, email FROM d_json WHERE email = 'b@example.com';
+----+---------------+
| id | email         |
+----+---------------+
|  2 | b@example.com |
+----+---------------+

email に値を入れたことは一度もありません。 JSONから自動で出てきています。そして索引が張ってあるので、行が増えても速いままです。

VIRTUAL は保存しません(読むたびに計算する)。STORED は保存します。VIRTUAL を足すのは形の変更だけなので速く、STORED を足すと全行に値を書くので表の作り直しになります。 ⑥の ALGORITHM の話が、ここでも効いてきます。

⑭ 結論 — この記事のSQLを、本番で手打ちしない

ここまで全部やったうえで、最後にこれを書きます。

ALTER TABLE を、本番のサーバーに手で打たないでください。

理由は、ここまでで全部出ました。

  • ROLLBACK が効かない(②)。打ち間違いは、打ち間違いのまま残る
  • MODIFY は書かなかった属性を消す(⑤)。手で打つと必ずいつか消す
  • どのやり方で走るかは、打ってみないと分からない(⑥)
  • INSTANT でも、タイミングが悪ければサービスが止まる(⑧)
  • そして、手で打った変更は「どこにも記録が残りません」

最後のが一番効きます。検証環境で打った ALTER を、本番でもう一度打つとき、あなたはそれを思い出して打ち直します。 1文字違えば、2つの環境の表は別物になります。そして、それに気づくのは何ヶ月も先です。

だから、ORMとマイグレーションの回で、ALTER TABLE をファイルに書いてコードとして残しました。

この記事は、そのファイルの中身を読めるようにするためのものです。 マイグレーションツールは、結局この記事で打ったSQLを組み立てて投げています。 中で何が起きているか分かっていれば、

  • そのマイグレーションが INSTANT で終わるのか、10分かかるのかが、流す前に分かる
  • 失敗して途中で止まったとき、ROLLBACK されていないと分かる(②。MySQL では、途中まで進んだ状態がそのまま残ります)
  • 重い変更だと分かったときに、⑪の入れ替えに切り替えられる

道具に任せるのと、道具に丸投げするのは違います。 前者にするために、ここまで手で打ちました。

つまずいたところ

ROLLBACK を信じて、検証中に表を壊した。 ②の実験をする前に、練習用の表で「まあ ROLLBACK すればいいや」と DROP TABLE を打ちました。戻りませんでした。この記事を②から始めているのは、私がその順番で失敗したからです。

ALTER が「一瞬で終わった」ので安全だと思った。 3行の練習表で ALTER TABLE を何度打っても、全部一瞬です。COPY で走っていても一瞬です。 80万行の表を作るまで、自分が何をしていたのか分かっていませんでした。 ⑦で行数を増やしたのは、そのためです。

MODIFY で NOT NULL を消した。 price を BIGINT にするだけのつもりでした。SHOW CREATE TABLE を打ち返す習慣が無かったので、気づいたのは、あとで NULL が入ったときです。ALTER TABLE の前に現在の定義をコピーする、という手順は、この失敗から来ています。

ALGORITHM を書かずに調べようとして、遠回りした。 最初は SHOW PROCESSLIST や information_schema を眺めて「これは COPY なのか」を判定しようとしました。打ちながら覗きに行くので、間に合いません。「明示して失敗させる」に気づくまで、しばらくかかりました。調べるのではなく、宣言して断らせる。

INSTANT なのに ALTER が返ってこなくて、MySQL を疑った。 ⑧そのものです。別の画面で START TRANSACTION; して SELECT を打ったまま放置していたのを、完全に忘れていました。「サーバーが重い」ではなく「自分がロックを掴んでいた」でした。SHOW PROCESSLIST で Sleep の行を見るまで分かりませんでした。

TRUNCATE が外部キーで断られた理由が分からなかった。 子の表を空にしたのに、まだ断られます。「行が無いのになぜ」と5分悩みました。行を見ているのではなく、制約があるかどうかを見ていると分かって納得しました。

WITH RECURSIVE が 1000 行で止まった。 ERROR 3636 が出ます。cte_max_recursion_depth の既定が 1000 なので、それ以上は打ち切られます。⑦で先に SET SESSION しているのはこのためです。

一時テーブルを SHOW TABLES で探して、無いと思った。 作ったはずの d_tmp_calc が一覧に出ません。壊れたのかと思って作り直しました。 出ないのが仕様です。

後片づけ

この記事で作った表を全部消します。 d_ で始まるものだけです。

DROP TABLE IF EXISTS d_child, d_parent;
DROP TABLE IF EXISTS d_item, d_item_like, d_item_sel, d_item_tmp;
DROP TABLE IF EXISTS d_big, d_big_new, d_big_old, d_del, d_del_old;
DROP TABLE IF EXISTS d_json, d_tx, d_tx_side;

d_child を先に消しています。 外部キーで d_parent を参照しているので、親から消そうとすると⑩のエラー3730で止まります。

tasks を巻き込んでいないことを、必ず確かめてください。

SHOW TABLES;
SELECT COUNT(*) FROM tasks;

①で見た一覧に戻っていれば成功です。

一時テーブルは、接続を切れば消えます。

EXIT;

DROP DATABASE appdb; は打たないでください。 本線のアプリのデータが全部消えます。 消すのは表だけです。

この記事をやり直したくなったら、DROP TABLE IF EXISTS から始まる各手順をそのまま打てば、何度でも同じ状態に戻せます。 そのために全部の CREATE TABLE の前に置いてありました。

まとめ — 理解できたこと

  • DDL(CREATE / ALTER / DROP / TRUNCATE / RENAME)は、打った瞬間に暗黙でコミットされる。ROLLBACK は効かない
  • しかも、その前に打った INSERT まで確定させる。 エラーも警告も出ない。MySQL でマイグレーションをトランザクションで囲むことはできない
  • 8.0 の「アトミックDDL」は「途中で落ちても半端な表が残らない」という意味。 「あとから戻せる」という意味ではない
  • SHOW CREATE TABLE は、自分が書いたものではなく、サーバーが理解したものを返す。 CREATE を打ったら必ず打ち返す
  • IF NOT EXISTS は、形が違う同名の表があっても黙って何もしない。 「成功した」は「思ったとおりの表がある」ではない
  • CREATE TABLE ... LIKE は形をコピーする。CREATE TABLE ... SELECT は索引も AUTO_INCREMENT も既定値も引き継がない
  • MODIFY と CHANGE は、書かなかった属性を消す。 NOT NULL も DEFAULT も COMMENT も。名前だけ変えるなら RENAME COLUMN
  • ALTER TABLE には INSTANT / INPLACE / COPY の3つのやり方がある。SQLの見た目では区別できない
  • 調べ方は「明示して失敗させる」。 , ALGORITHM=INSTANT を書き足せば、できないときはエラーで止まる
  • utf8mb4 の VARCHAR は 255 バイトが境目。 またぐと同じ「文字数を伸ばす」でも表の作り直しになる
  • COPY の所要時間は行数にほぼ比例する。 80万行で約10秒なら、8000万行で約17分。その間ずっと書き込みが待たされる
  • INSTANT でもサービスは止まる。 犯人はメタデータロックで、開きっぱなしのトランザクション1本で起きる
  • ALTER が待たされると、後から来た SELECT まで後ろに並ぶ。 SHOW PROCESSLIST の Waiting for table metadata lock がその印
  • 本番の ALTER の前に SET SESSION lock_wait_timeout = 10; を打つ。 待たせるより、失敗させる
  • DELETE は戻せる/番号を戻さない。TRUNCATE は戻せない/番号を1に戻す/外部キーに参照されていれば行が無くても断られる
  • RENAME TABLE は行数に関係なく一瞬で、複数を同時に交換できる。 「新しい表を作って詰めて交換」が無停止変更の定石
  • ただしコピー中に入った変更は落ちる。 それを追いかけるのが pt-online-schema-change と gh-ost
  • 一時テーブルは接続を切ると消え、SHOW TABLES に出ず、同名の本物の表を隠す
  • 生成列は他の列から計算される列。JSONの中身に索引を張るときに使う。VIRTUAL は速く、STORED は表を作り直す
  • 結論:本番でDDLを手打ちしない。 マイグレーションに書く。この記事は、そのファイルの中身を読めるようにするためのもの

参考リンク


次に読む

この記事は 【深掘り】SQL の 3 / 17 本目です。

記事
← 前の記事MySQLのデータ型選び — FLOATで金額は1円ずれる
→ 次の記事NOT NULL・UNIQUE・CHECK制約 — アプリで弾いた値が入る

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

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

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

Backend›SQL — DDL
  • CREATE / ALTER / DROP / TRUNCATE
  • ALTER TABLE のアルゴリズムとロック

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

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

コメント

コメントする

目次