Share
Embed
Transcript
19 slides
-
自己紹介 • yoshikai • 新卒2年目 • DBA 歴1年 • 昨年の今頃
MySQL 運用・管理[実践] 入門 を読み終えたくらい • 関連リンク • X:https://x.com/yo__tsu__o • ブログ:https://hiyochic.hatenablog.com/ -
今日の内容 • 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 -
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 -
今日の内容 • 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 -
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 -
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. -
今日の内容 • 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 -
CHANGE/MODIFY COLUMN • 意図しない変更が発生しないか注意 • SHOW CREATE TABLE などで現在のカラム定義を確認 •
変更する部分・変更しない部分全てを ALTER TABLE に含める • 変更内容によっては思わぬ事故につながる • いつの間にか NOT NULL が DEFAULT NULL に • INPLACE で実行できると思っていたら COPY だった -
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, -
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 -
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, -
今日の内容 • 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 -
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 -
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 -
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 -
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. -
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 テーブルサイズによる -
今日の内容まとめ • 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