DBA 1年目で勉強になった DDL 3選

Transcript

1. DBA 1年目で勉強になった DDL 3選 MySQL アンカンファレンス #10, 2025/10/07 yoshikai
2. 自己紹介 • yoshikai • 新卒2年目 • DBA 歴1年 • 昨年の今頃 MySQL 運用・管理[実践] 入門 を読み終えたくらい • 関連リンク • X：https://x.com/yo__tsu__o • ブログ：https://hiyochic.hatenablog.com/
3. 今日の内容 • VARCHAR カラムサイズの拡張 • https://hiyochic.hatenablog.com/entry/2025/09/08/100620 • CHANGE/MODIFY COLUMN • https://hiyochic.hatenablog.com/entry/2025/09/16/122840 • CHARACTER SET utf8mb3 -> utf8mb4 • https://hiyochic.hatenablog.com/entry/2025/09/22/125349
4. DDL とその実行方式 • DDL (Data Definition Language) • データベースやテーブルなど、スキーマの構造を定義・変更する SQL • ALTER TABLE, CREATE TABLE, DROP TABLE, CREATE INDEX, … • ALTER TABLE 実行方式（ALGORITHM） • INSTANT：メタデータの変更のみで非常に高速（テーブルサイズに依らない） • INPLACE：多くの場合並列に DML を実行可能で テーブルサイズに応じた時間がかかることもある • COPY：並列に DML を実行できず、テーブルサイズに応じた時間がかかる • 参考 • https://zenn.dev/levtech/articles/226fcb30e99e70 • https://zenn.dev/levtech/articles/25462f51c9ffcb
5. 今日の内容 • VARCHAR カラムサイズの拡張 • https://hiyochic.hatenablog.com/entry/2025/09/08/100620 • CHANGE/MODIFY COLUMN • https://hiyochic.hatenablog.com/entry/2025/09/16/122840 • CHARACTER SET utf8mb3 -> utf8mb4 • https://hiyochic.hatenablog.com/entry/2025/09/22/125349
6. VARCHAR カラムサイズの拡張 • 255byte（文字数ではない）を跨ぐかどうか注意 • 255byte を跨ぐ：ALGORITHM=COPY • 255byte を跨がない：ALGORITHM=INPLACE, LOCK=NONE • VARCHAR カラムのデータ • prefix (文字列の byte 長) + 文字列 • 文字列 byte 長が 0 ~ 255 byte -> prefix が 1byte • 文字列 byte 長が 256 ~ 65535 byte -> prefix が 2byte • オンライン DDL のドキュメントの表（Extending VARCHAR column size） • In Place：Yes • Permits Concurrent DML： Yes • MySQL :: MySQL 8.4 Reference Manual :: 17.12.1 Online DDL Operations
7. VARCHAR カラムサイズの拡張 – 動作確認 • VARCHAR(10) -> VARCHAR(20) (40byte -> 80byte) • VARCHAR(63) -> VARCHAR(64) (252byte -> 256byte) >ALTER TABLE p MODIFY COLUMN name VARCHAR(20) COLLATE utf8mb4_bin NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 >ALTER TABLE p MODIFY COLUMN name VARCHAR(64) COLLATE utf8mb4_bin NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
8. 今日の内容 • VARCHAR カラムサイズの拡張 • https://hiyochic.hatenablog.com/entry/2025/09/08/100620 • CHANGE/MODIFY COLUMN • https://hiyochic.hatenablog.com/entry/2025/09/16/122840 • CHARACTER SET utf8mb3 -> utf8mb4 • https://hiyochic.hatenablog.com/entry/2025/09/22/125349
9. CHANGE/MODIFY COLUMN • 意図しない変更が発生しないか注意 • SHOW CREATE TABLE などで現在のカラム定義を確認 • 変更する部分・変更しない部分全てを ALTER TABLE に含める • 変更内容によっては思わぬ事故につながる • いつの間にか NOT NULL が DEFAULT NULL に • INPLACE で実行できると思っていたら COPY だった
10. CHANGE/MODIFY COLUMN – 動作確認 (1) • 文字数だけ 10 -> 20 に変えたい！ • -> NOT NULL が消える // ALTER TABLE 実行前 `col1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, // ALTER TABLE 実行 >ALTER TABLE t1 modify column col1 varchar(20), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: // ALTER TABLE 実行後 `col1` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
11. CHANGE/MODIFY COLUMN – 動作確認 (2) • 文字数だけ 10 -> 20 に変えたい • -> CHARACTER SET/COLLATION がデフォルトのものに変更 & ALGORITHM=COPY // ALTER TABLE 実行前 >show create table t1¥G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, … `col3` varchar(10) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
12. CHANGE/MODIFY COLUMN – 動作確認 (2) • 文字数だけ 10 -> 20 に変えたい • -> CHARACTER SET/COLLATION がデフォルトのものに変更 & ALGORITHM=COPY // ALTER TABLE 実行前 `col3` varchar(10) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, // ALTER TABLE 実行 >ALTER TABLE t1 modify column col3 varchar(20), ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. >ALTER TABLE t1 modify column col3 varchar(20), ALGORITHM=COPY; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 // ALTER TABLE 実行後 `col3` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
13. 今日の内容 • VARCHAR カラムサイズの拡張 • https://hiyochic.hatenablog.com/entry/2025/09/08/100620 • CHANGE/MODIFY COLUMN • https://hiyochic.hatenablog.com/entry/2025/09/16/122840 • CHARACTER SET utf8mb3 -> utf8mb4 • https://hiyochic.hatenablog.com/entry/2025/09/22/125349
14. CHARACTER SET utf8mb3 -> utf8mb4 • utf8mb3 と utf8mb4 • utf8mb3 は最大3byte、utf8mb4 は最大4byteで文字を表現 • utf8mb4 では や といった補助文字もサポート • Converting a character set • In Place: No, Permits Concurrent DML: No • 以下の条件を満たすとき CHARACTER SET の MODIFY を メタデータ変更のみの INPLACE 操作として実行できる • データ型が CHAR、VARCHAR、TEXT、ENUM • utf8mb3 から utf8mb4 への変更、または任意の CHARACTER SET から binary への変 更 • 変更対象のカラムにインデックスが貼られていない • 参考 • MySQL :: The MySQL 8.0.14 Maintenance Release is Generally Available • MySQL :: MySQL 8.4 Reference Manual :: 15.1.9 ALTER TABLE Statement
15. CHARACTER SET utf8mb3 -> utf8mb4 – 動作確認 (1) • utf8mb3 から utf8mb4 への CONVERT • テーブルサイズに応じた時間がかかるが INPLACE で実行可能 >ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (6.37 sec) Records: 0 Duplicates: 0 Warnings: 0
16. CHARACTER SET utf8mb3 -> utf8mb4 – 動作確認 (2) • utf8mb3 から utf8mb4 への MODIFY • INPLACE で実行可能 & メタデータの変更のみでほとんど時間がかからない • さらに、MODIFY 後の CONVERT もほとんど時間がかからない // まずは column 単位で character set を変更する >ALTER TABLE t1 MODIFY COLUMN col1 varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 // MODIFY 後に CONVERT する >ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
17. CHARACTER SET utf8mb3 -> utf8mb4 – 動作確認 (3) • VARCHAR で 255 byte 跨ぐことになる場合 • ALGORITHM=COPY • index が貼られている場合 • ALGORITHM=COPY // VARCHAR(80) (utf8mb3: 240 byte, utf8mb4: 320 byte) >ALTER TABLE t1 MODIFY COLUMN col1 varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. >ALTER TABLE t1 MODIFY COLUMN col1 varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
18. CHARACTER SET utf8mb3 -> utf8mb4 INPLACE 実行時間 CONVERT TO Yes テーブルサイズによる MODIFY Yes 一瞬 MODIFY 後 CONVERT Yes 一瞬 VARCHAR 255 byte 跨ぎ MODIFY No テーブルサイズによる VARCHAR 255 byte 跨ぎ MODIFY 後 CONVERT Yes 一瞬 index あり MODIFY No テーブルサイズによる index あり MODIFY 後 CONVERT No テーブルサイズによる
19. 今日の内容まとめ • VARCHAR カラムサイズの拡張 • 255byte（文字数ではない）を跨ぐかどうか注意 • 255byte を跨ぐ：ALGORITHM=COPY • 255byte を跨がない：ALGORITHM=INPLACE, LOCK=NONE • CHANGE/MODIFY COLUMN • 意図しない変更が発生しないか注意 • SHOW CREATE TABLE などで現在のカラム定義を確認 • 変更する部分・変更しない部分全てを ALTER TABLE に含める • CHARACTER SET utf8mb3 -> utf8mb4 • 意図した ALGORITHM を必ず指定する！ • ALGORITHM=INSTANT • ALGORITHM=INPLACE, LOCK=NONE