MySQL学习笔记
文章目录
- 1 库定义
- —1.1 创建数据库
- —1.2 查询库的定义(不是DDL)
- —1.3 修改库定义
- —1.4 删除库（不代表生产操作）
- —1.5 库定义规范
- 2 表定义
- —2.1 创建表定义
- —2.2 建表规范审核建表语句
- —2.3 查表
- —2.4 改表
- —2.5 删除表
- 3 Online DDL
- —3.1 介绍
- —3.2 Copy 算法原理
- —3.3 INPLACE 算法原理
- —3.4 INSTANT
- —3.5 在Online DDL中的rebuild和no-rebuild
- —3.6 关于Online DDL的误区问题
- ——3.6.1 Online DDL 会不会锁表
- ——3.6.2 支持 INPLACE 算法的 DDL 一定是 Online 的吗？
- ——3.6.3 INPLACE DDL 需不需要额外的数据空间
- —3.7 Online DDL 补充-RowLOG
- ——3.7.1 row log空间
- ——3.7.2 索引排序空间
- ——3.7.3 中间表空间
- —3.8 Online DDL 补充(官方文档)
1 库定义
—1.1 创建数据库
create database abc;
create schema xyz;
create database if not exists oldguo;
create database bbb charset utf8mb4 collate utf8mb4_0900_ai_ci;
8.0默认字符集utf8mb4，校对规则默认为utf8mb4_0900_ai_ci
—1.2 查询库的定义(不是DDL)
show databases;
show create database bbb;
—1.3 修改库定义
alter database aaa charset utf8mb4;
—1.4 删除库（不代表生产操作）
drop database if exists aaa;
—1.5 库定义规范
1.必须设置字符集
2.库名不要大写字母，不要数字开头，不要超过18字符，不要用内置字符串，和业务有关。
3.生产禁用 DROP database（只有管理员可以做）
4.显式的设置字符集
2 表定义
—2.1 创建表定义
use test;
create table if not exists stu(
sid int unsigned not null primary key auto_increment comment '学号',
sname varchar(64) not null comment '姓名',
sage tinyint unsigned not null default 18 comment '年龄',
sgender char(1) not null default 'M' comment '性别:M|F',
saddr enum('bj','tj','sh','cq','xk','am','tw') not null comment '省份',
stel char(11) not null unique key comment '手机',
sqq char(11) not null unique key comment 'QQ号',
intime datetime not null default now() comment '入学时间'
)engine=innodb charset=utf8mb4 comment '学生表';
建表官方示例：
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`sid` CHAR(10) NOT NULL COMMENT '学号',
`sname` VARCHAR(128) NOT NULL COMMENT '姓名',
`sage` TINYINT(3) UNSIGNED NOT NULL DEFAULT '18' COMMENT '年龄',
`sgender` TINYINT(3) UNSIGNED NOT NULL DEFAULT '2' COMMENT '性别',
`scardid` CHAR(18) NOT NULL COMMENT '身份证号',
`saddr` ENUM('北京市','上海市','天津市','重庆市') NOT NULL COMMENT '省份',
`shobby` SET('足球','篮球','羽毛','排球','保龄','乒乓') DEFAULT NULL COMMENT '爱好',
`smoney` DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '学费',
`sdate` DATETIME NOT NULL COMMENT '入学时间',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
create table t1 like stu; 创建与原表表结构一样的表
—2.2 建表规范审核建表语句
1.表名：不要大写字母，不要数字开头，不要超过18字符，不要用内置字符串，和业务有关。
2.列名：业务有关，不要内置字符，不要超过18字符。
3.数据类型：合适的，精简的，完整的。
4.每个表要有且只有1个主键。每个列尽量Not null。尽量不要使用外键
5.每列有注释
6.存储引擎InnoDB，字符集utf8mb4
7.每个表建议在30个字段以内。
8.需要存储emoji字符的，则选择utf8mb4字符集。
9.机密数据，加密后存储。
10.整型数据，默认加上UNSIGNED。
11.存储IPV4地址建议用INT UNSIGNE，查询时再利用INET_ATON()、INET_NTOA()函数转换。
12.如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储。
13.选择尽可能小的数据类型，用于节省磁盘和内存空间。
14.存储浮点数，可以放大倍数存储。
15.每个表必须有主键，INT/BIGINT并且自增做为主键，分布式架构使用sequence序列生成器保存。
16.每个列使用not null，或增加默认值。
—2.3 查表
mysql> show tables;
mysql> desc student;
mysql> show create table student;
—2.4 改表
#生产需求1：在stu表中添加状态列，表示行是否被删除(1代表删除，0代表没删除)
alter table stu add is_deleted tinyint not null default 0 comment '状态列：1代表删除，0代表没删除';
#生产需求2：改数据类型的值范围,在业务低峰期更改，修改属性一定要把与原表列重叠的的属性都加上
alter table stu modify sname varchar(100) not null comment '姓名';
注意：modify只能改字段数据类型完整约束，不能改字段名，但是change可以。
change修改的范围和力度要比modify大。
修改数据类型，修改字段位置用modify，改名字就用change
#生产需求3：在表的某个列加个索引
alter table stu add index i_sname(sname);
修改表名
rename table stu to stu_1;
修改表的存储引擎
alter table stu engine=myisam;
增加一列
alter table stu add a int after sname;
删除一列
alter table stu drop a ;
—2.5 删除表
drop table stu;（不代表生产操作）
清空表
truncate table stu_1;
truncate删除更快，直接清理idb文件；drop需要清除表结构元数据。
3 Online DDL
—3.1 介绍
在5.6版本之后，对于部分alter table ，加入新的执行算法，可以进行DDL时，在有业务（DML操作） 时，可以并行进行表结构修改。可以通过ALter table 时添加 ALGORITHM参数控制使用算法。
ALter table t1 add a int ALGORITHM=?;
目前可以支持的算法有三种：
COPY
INPLACE
INSTANT DDL操作，在执行时，不管何种算法，都会经历三个阶段：准备阶段、执行阶段、提交阶段。 不同之处是在三个阶段中分别作了不同的处理。
—3.2 Copy 算法原理
DDL时，会生成临时新表，将原表数据逐行拷贝到新表中，在此期间会阻塞DML
准备阶段：
对表加元数据共享锁S，读取frm元数据（此时DDL不能并行）
在Server层通过Create like语句，创建临时表，Engine层也生成对应ibd、frm文件执行，有共享锁S，影响原表的DDL操作
执行、提交阶段：
1、修改临时表元数据，加新列，有共享锁S，影响原表的DDL操作
2、拷贝原表数据到临时表，有共享锁S和排他锁X，影响原表的DDL,DML操作,直到复制完成 3、重命名临时表及文件
4、删除原表及文件
5、提交事务，释放锁
—3.3 INPLACE 算法原理
无需拷贝全表数据到新表，但可能还是需要INPLACE方式（原地，无需生成新的临时表）重建整表。 这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁）， 除此外，DDL期间不会阻塞原表的DML操作。
准备阶段：
1、对表加元数据共享锁，并升级为排他锁；（此时DML不能并行，阻塞DML、DDL操作）
2、判断语句rebuild table,no-rebuild，no-rebuild，在原表所在的路径下创建.frm和.ibd临时中转文件； no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属 于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指 定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中
3、申请row log空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）
执行阶段：（online）
1、释放排他锁，保留元数据共享升级锁；（此时DML可以并行，阻塞DDL）
2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；
3、将所有对原表的DML操作记录在日志文件row log中。
提交阶段：
1、升级元数据共享升级锁，产生排他锁锁表；（此时DML不能并行）
2、重做row log中的内容；（no-rebuild不需要）
3、重命名原表文件，将临时文件改名为原表文件名，删除原表文件；
4、提交事务，变更完成。
在DDL期间产生的数据，会按照正常操作一样，写入原表，记redolog、undolog、binlog， 并同步到从库去执行，只是额外会记录在row log中，并且写入row log的操作本身也会记录redolog， 而在提交阶段才进行row log重做，此阶段会锁表，此时主库（新表空间+row log）和从库（表空间）数据是一致的
在主库DDL操作执行完成并提交，这个DDL才会写入binlog传到从库执行，在从库执行该DDL时，这个DDL对 于从库本地来讲仍然是online的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产 生row log。但是对于主库同步过来DML，此时会被阻塞，是offline的，DDL是排他锁的在复制线程中也是一样， 所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。 所以大表的DDL操作，会造成同步延迟。
—2.1 创建表定义
use test;
create table if not exists stu(
sid int unsigned not null primary key auto_increment comment '学号',
sname varchar(64) not null comment '姓名',
sage tinyint unsigned not null default 18 comment '年龄',
sgender char(1) not null default 'M' comment '性别:M|F',
saddr enum('bj','tj','sh','cq','xk','am','tw') not null comment '省份',
stel char(11) not null unique key comment '手机',
sqq char(11) not null unique key comment 'QQ号',
intime datetime not null default now() comment '入学时间'
)engine=innodb charset=utf8mb4 comment '学生表';
建表官方示例：
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`sid` CHAR(10) NOT NULL COMMENT '学号',
`sname` VARCHAR(128) NOT NULL COMMENT '姓名',
`sage` TINYINT(3) UNSIGNED NOT NULL DEFAULT '18' COMMENT '年龄',
`sgender` TINYINT(3) UNSIGNED NOT NULL DEFAULT '2' COMMENT '性别',
`scardid` CHAR(18) NOT NULL COMMENT '身份证号',
`saddr` ENUM('北京市','上海市','天津市','重庆市') NOT NULL COMMENT '省份',
`shobby` SET('足球','篮球','羽毛','排球','保龄','乒乓') DEFAULT NULL COMMENT '爱好',
`smoney` DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '学费',
`sdate` DATETIME NOT NULL COMMENT '入学时间',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
create table t1 like stu; 创建与原表表结构一样的表
—2.2 建表规范审核建表语句
1.表名：不要大写字母，不要数字开头，不要超过18字符，不要用内置字符串，和业务有关。 2.列名：业务有关，不要内置字符，不要超过18字符。 3.数据类型：合适的，精简的，完整的。 4.每个表要有且只有1个主键。每个列尽量Not null。尽量不要使用外键 5.每列有注释 6.存储引擎InnoDB，字符集utf8mb4 7.每个表建议在30个字段以内。 8.需要存储emoji字符的，则选择utf8mb4字符集。 9.机密数据，加密后存储。 10.整型数据，默认加上UNSIGNED。 11.存储IPV4地址建议用INT UNSIGNE，查询时再利用INET_ATON()、INET_NTOA()函数转换。 12.如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储。 13.选择尽可能小的数据类型，用于节省磁盘和内存空间。 14.存储浮点数，可以放大倍数存储。 15.每个表必须有主键，INT/BIGINT并且自增做为主键，分布式架构使用sequence序列生成器保存。 16.每个列使用not null，或增加默认值。
—2.3 查表
mysql> show tables; mysql> desc student; mysql> show create table student;
—2.4 改表
#生产需求1：在stu表中添加状态列，表示行是否被删除(1代表删除，0代表没删除) alter table stu add is_deleted tinyint not null default 0 comment '状态列：1代表删除，0代表没删除'; #生产需求2：改数据类型的值范围,在业务低峰期更改，修改属性一定要把与原表列重叠的的属性都加上 alter table stu modify sname varchar(100) not null comment '姓名'; 注意：modify只能改字段数据类型完整约束，不能改字段名，但是change可以。 change修改的范围和力度要比modify大。 修改数据类型，修改字段位置用modify，改名字就用change #生产需求3：在表的某个列加个索引 alter table stu add index i_sname(sname); 修改表名 rename table stu to stu_1; 修改表的存储引擎 alter table stu engine=myisam; 增加一列 alter table stu add a int after sname; 删除一列 alter table stu drop a ;
—2.5 删除表
drop table stu;（不代表生产操作） 清空表 truncate table stu_1; truncate删除更快，直接清理idb文件；drop需要清除表结构元数据。
3 Online DDL
—3.1 介绍
在5.6版本之后，对于部分alter table ，加入新的执行算法，可以进行DDL时，在有业务（DML操作） 时，可以并行进行表结构修改。可以通过ALter table 时添加 ALGORITHM参数控制使用算法。
ALter table t1 add a int ALGORITHM=?;
目前可以支持的算法有三种：
COPY
INPLACE
INSTANT DDL操作，在执行时，不管何种算法，都会经历三个阶段：准备阶段、执行阶段、提交阶段。 不同之处是在三个阶段中分别作了不同的处理。
—3.2 Copy 算法原理
DDL时，会生成临时新表，将原表数据逐行拷贝到新表中，在此期间会阻塞DML
准备阶段：
对表加元数据共享锁S，读取frm元数据（此时DDL不能并行）
在Server层通过Create like语句，创建临时表，Engine层也生成对应ibd、frm文件执行，有共享锁S，影响原表的DDL操作
执行、提交阶段：
1、修改临时表元数据，加新列，有共享锁S，影响原表的DDL操作
2、拷贝原表数据到临时表，有共享锁S和排他锁X，影响原表的DDL,DML操作,直到复制完成 3、重命名临时表及文件
4、删除原表及文件
5、提交事务，释放锁
—3.3 INPLACE 算法原理
无需拷贝全表数据到新表，但可能还是需要INPLACE方式（原地，无需生成新的临时表）重建整表。 这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁）， 除此外，DDL期间不会阻塞原表的DML操作。
准备阶段：
1、对表加元数据共享锁，并升级为排他锁；（此时DML不能并行，阻塞DML、DDL操作）
2、判断语句rebuild table,no-rebuild，no-rebuild，在原表所在的路径下创建.frm和.ibd临时中转文件； no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属 于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指 定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中
3、申请row log空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）
执行阶段：（online）
1、释放排他锁，保留元数据共享升级锁；（此时DML可以并行，阻塞DDL）
2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；
3、将所有对原表的DML操作记录在日志文件row log中。
提交阶段：
1、升级元数据共享升级锁，产生排他锁锁表；（此时DML不能并行）
2、重做row log中的内容；（no-rebuild不需要）
3、重命名原表文件，将临时文件改名为原表文件名，删除原表文件；
4、提交事务，变更完成。
在DDL期间产生的数据，会按照正常操作一样，写入原表，记redolog、undolog、binlog， 并同步到从库去执行，只是额外会记录在row log中，并且写入row log的操作本身也会记录redolog， 而在提交阶段才进行row log重做，此阶段会锁表，此时主库（新表空间+row log）和从库（表空间）数据是一致的
在主库DDL操作执行完成并提交，这个DDL才会写入binlog传到从库执行，在从库执行该DDL时，这个DDL对 于从库本地来讲仍然是online的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产 生row log。但是对于主库同步过来DML，此时会被阻塞，是offline的，DDL是排他锁的在复制线程中也是一样， 所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。 所以大表的DDL操作，会造成同步延迟。
表格说明几种常见操作的模式
—3.4 INSTANT
只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他MDL锁， 原表数据也不受影响。整个DDL过程几乎是瞬间完成的，也不会阻塞DML。 这个新特性是8.0.12引入的，再次感谢腾讯互娱DBA团队的贡献。执行DDL操作时，ALGORITHM选项可以 不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指 定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。 当采用COPY模式时，这时表里任何的修改数据操作，DDL都会被阻塞。COPY模式下会生成临时新 表，操作完成后原表会被删除，新表被重命名为原表名。当DDL开始后，原表上仅能只读，其他 的DML操作也都会被阻塞。COPY过程中，唯一会阻塞只读的时机是在清理旧表结构和表定义缓存时。 https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html 当前，InnoDB支持用于这些操作的INSTANT算法： 添加列（追加） 更改索引选项 重命名表（以ALTER方式） 设置/删除默认值 修改栏 添加/删除虚拟列 您可以使用ALGORITHM = INSTANT在单个语句中指定多个操作。
个人建议： 一般DDL操作最好都采用pt-osc或gh-ost这样的工具来实施，并且实施之前务必要先检查当前目标表上是否有事务或大查询未结束，避免严重的MDL锁等待 除了8.0以上版本，除了追加式新增列、表改名、新增虚拟列这三种支持INSTANT的操作可以直接跑DDL，其余的都统统采用pt-osc/gh-osc工具，相对更不容易出问题 执行ALTER TABLE DDL时，不要指定ALGORITHM=?, LOCK=?选项，因为MySQL会自行判断该采用哪种方式。本来可以INPLACE的，可能不小心给指定成COPY就悲剧了 - Instant：此变更可以"立刻"完成； - In Place：此变更由InnoDB引擎独立完成，不需要使用Redo log等，可以节省开销，否则使用copy； - Rebuild Table：此变更会重建聚簇索引，一般情况下，涉及到数据变更时才需要重建聚簇索引； - Permits Concurrent DML：此变更进行时，是否允许其他DML变更同一张表。此特性关系到变更是否会长时间阻塞业务； - Only Modifies：此变更是否只变更元信息，不涉及数据变更；
—3.5 在Online DDL中的rebuild和no-rebuild
指的是否rebuild表，rebuild table是Server层的行为，no-rebuild代表不需要 在Server层,通过类似Create table创建表，但不代表不生成临时文件。例如上面例 子，inplace 添加列，在Server层确实不需要进行create like行为创建临时表，但 依然会在engine层创建临时转储ibd，frm文件，所以也是需要占用额外空间的。
—3.6 关于Online DDL的误区问题
——3.6.1 Online DDL 会不会锁表
要回答这个问题，首先要明确“锁表”的含义。很多 MySQL 用户经常在表无法正常的进行DML时 就觉得是锁表了，这种说法其实过于宽泛，实际上能够影响DML操作的锁至少包 括以下几种（默认为 InnoDB表）：
MDL锁
表锁
行锁
GAP锁
其中除了MDL锁是在Server层加的之外，其它三种都是在 InnoDB 层加的。具体的加锁逻辑不在此进行展开，但是需要明确一点：所有的操作（不管是 DDL还是DML还是查询语句）都需 要先拿 Server层的MDL锁，然后再去拿InnoDB层的某个需要的锁。
一个 DDL 的基本过程是这样的：
首先，在开始进行 DDL 时，需要拿到对应表的 MDL X 锁，然后进行一系列的准备工作；
然后将 MDL X 锁降级为 MDL S 锁，进行真正的 Dml操作；
最后再次将 MDL S 锁升级为 MDL X 锁，完成 DDL 操作，释放 MDL 锁；
所以在真正执行 DDL 操作期间，确实是不会“锁表”的，但是如果在第一阶段拿 MDL X锁时 无法正常获取，那就可能真的会“锁表了”。
——3.6.2 支持 INPLACE 算法的 DDL 一定是 Online 的吗？
从概念上来说，INPLACE和Online是两个不同维度的事情。COPY和INPLACE指的是DDL内部 的执行算法，可以简单的理解成：COPY是在Server层的操作，INPLACE是在InnoDB层的操作。
而用户更加关心 Online 与否，通常只与一个问题有关：是否允许并发 DML。
COPY 算法执行的 DDL肯定不是 Online 的；
INPLACE 算法执行的 DDL 不一定是 Online 的；
——3.6.3 INPLACE DDL 需不需要额外的数据空间
前面我们提到过，MySQL内部对于DDL的ALGORITHM 有两种选择：INPLACE 和 COPY（8.0 新增了INSTANT，但是使用范围较小）。
COPY 算法理解起来相对简单一点：创建一张临时表，然后将原表的数据拷贝到临时表中，最后再用临时表替换原表。对于上面的步骤，由于需要将原表的数据拷贝到临时表中，所以肯定需要消耗额外的数据空间。
那么对于支持 INPLACE 算法的 DDL，是不是不需要额外的数据空间？
答案是：需要。其实之所以会问这个问题，还是因为对 INPLACE 本身的理解出现了偏差。
简单来说：INPLACE 描述的是表(no rebuild)，而不是数据文件。只要不创建临时表(rebuild table)，那么都是INPLACE的。
实际上，很多 INPLACE DDL 都会会创建临时数据文件，所以都会需要额外的数据空间，例如：
增加主键
重建主键
新增列（8.0 支持 INSTANT DDL，不需要）
删除列
调整列顺序
删除列默认值
增加列默认值
修改表的 ROW_FORMAT
OPTIMIZE table
—3.7 Online DDL 补充-RowLOG
——3.7.1 row log空间
row log空间每次申请的大小由 innodb_sort_buffer_size决定， 最大值由innodb_online_alter_log_max_size，该值默认为128M，支持动态修改。 对于更新频繁的表来讲，如果预计在DDL期间对表的更新操作存储可能超过128M时， 需要为本次操作增大该值。当然如果不涉及rebuild操作时，不需要考虑该值。 如果提示DB_ONLINE_LOG_TOO_BIG错误，则是由innodb_online_alter_log_max_size空间不足造成的。
——3.7.2 索引排序空间
如果DDL操作涉及二级索引的创建，会在MySQL临时目录产生临时排序文件，将中间的 排序结果写入文件，最终将内容合并到最终表或索引中，然后自动删除临时排序文件。 这个路径默认为mysql全局参数tmpdir指定（默认值为/tmp，如果手动指定了innodb_tmpdir 参数的路径，则tmpdir会被覆盖），且不会在原始表的目录中创建临时排序文件。 tmpdir需要保证能够容纳要创建的二级索引，临时排序文件最大可能需要的空间等于表中的 数据量加上索引，否则执行将报错。
——3.7.3 中间表空间
如果DDL操作涉及rebuild表，则会在原表所在目录创建临时表空间文件（以#sql开头），临时表 空间大小需要等于原表大小，重建完成后会自动重命名临时表空间，删除原表空间。所以执行 rebuild操作时需要保证原表所在路径下有足够空间。
—3.8 Online DDL 补充(官方文档)
在instant(8.0.12)添加列后元数据发生更改后，如何解析页面上的物理记录？ 额外的信息与数据字典中的一些元数据一起保留在物理记录中。我们认为，将元数据 存储在适当的数据字典表中并使其在事务上保持一致将使其更健壮且更自然。此新的 元数据存储在物理记录中。这个新的元数据包括一个存储在info_bits中的标志。 info_bits中的此新信息用于跟踪是否在第一个即时ADD COLUMN之后创建记录。我们 还使用info_bits跟踪物理记录中的字段/列数。当表经历第一个即时ADD COLUMN 时的列数以及新添加的列的所有默认值都存储在数据字典中。这两条 信息存储在数据字典表的se_private_data列中。
有了这些额外的信息，现在就可以立即执行ADD COLUMN操作，而无需修改表中的任何行。 如果没有即时的ADD COLUMN，则表中的所有行将采用与以前相同的格式。即时发 出ADD COLUMN后，对该表的任何更新都将以新格式写入行。 从数据字典中查找默认值（如果有）。
在每个即时ADD COLUMN中，都会分别跟踪新添加的列的默认值。这些列的默认值可以随时 更改。因此，在重建或截断表之后，可以丢弃即时列数和默认值，此外，可以像以前 一样将表中的行更改为旧格式。如果表是分区表，则不同的分区可能具有不同数量的 即时列，并且需要不同数量的默认值。如果某些分区被重建，截断或重新创建，则 分区中的行也可以像以前一样更改为旧格式。
以下是一些可以立即完成的操作的简单示例：
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b));
Query OK, 0 rows affected (0.70 sec)
mysql> # Modify the index can be instant if it's a trivial change
mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Rename the table through ALTER TABLE can be instant
mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.26 sec)
mysql> # SET DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # DROP DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # MODIFY COLUMN can be instant
mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'),
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # ADD/DROP virtual column can be instant
mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL),
ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Do two operations instantly in the same statement
mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT
200, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP TABLE t2;
Query OK, 0 rows affected (0.36 sec)
如何观察： 用户可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：用户 可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：
mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 0 |
+----------+---------+--------------+
1 row in set (0.22 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
+----------+------+-------------+---------------+
2 rows in set (0.38 sec)
如我们所见，在innodb_tables中引入了一个名为instant_cols的新列，该列代表即时列的数量，而在
innodb_columns中引入了两个有关默认值的新列，分别名为'has_default'和'default_value'。
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
请注意，table_id不变。它不再是表的重建！正如我们所看到的，'instant_cols'现在设置为2，这意味
着在第一个即时ADD COLUMN发生时表中有a列和b列。在innodb_columns中记住c和d列的默认值。现在，
如果has_default为1，则用户可以知道是否立即添加了列。此外，如果“ has_default”为1，则此列的默
认值存储在“ default_value”字段中。d的default_value 设置为值1000的内部二进制格式。
mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+--------------------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+--------------------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
| 1065 | e | 1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)
在另一个即时添加列之后，table_id再次保持不变。'instant_cols'不会更改，并且还会记住列e的默认值。
从概念上来说，INPLACE和Online是两个不同维度的事情。COPY和INPLACE指的是DDL内部 的执行算法，可以简单的理解成：COPY是在Server层的操作，INPLACE是在InnoDB层的操作。 而用户更加关心 Online 与否，通常只与一个问题有关：是否允许并发 DML。 COPY 算法执行的 DDL肯定不是 Online 的； INPLACE 算法执行的 DDL 不一定是 Online 的；
——3.6.3 INPLACE DDL 需不需要额外的数据空间
前面我们提到过，MySQL内部对于DDL的ALGORITHM 有两种选择：INPLACE 和 COPY（8.0 新增了INSTANT，但是使用范围较小）。
COPY 算法理解起来相对简单一点：创建一张临时表，然后将原表的数据拷贝到临时表中，最后再用临时表替换原表。对于上面的步骤，由于需要将原表的数据拷贝到临时表中，所以肯定需要消耗额外的数据空间。
那么对于支持 INPLACE 算法的 DDL，是不是不需要额外的数据空间？
答案是：需要。其实之所以会问这个问题，还是因为对 INPLACE 本身的理解出现了偏差。
简单来说：INPLACE 描述的是表(no rebuild)，而不是数据文件。只要不创建临时表(rebuild table)，那么都是INPLACE的。
实际上，很多 INPLACE DDL 都会会创建临时数据文件，所以都会需要额外的数据空间，例如：
增加主键
重建主键
新增列（8.0 支持 INSTANT DDL，不需要）
删除列
调整列顺序
删除列默认值
增加列默认值
修改表的 ROW_FORMAT
OPTIMIZE table
—3.7 Online DDL 补充-RowLOG
——3.7.1 row log空间
row log空间每次申请的大小由 innodb_sort_buffer_size决定， 最大值由innodb_online_alter_log_max_size，该值默认为128M，支持动态修改。 对于更新频繁的表来讲，如果预计在DDL期间对表的更新操作存储可能超过128M时， 需要为本次操作增大该值。当然如果不涉及rebuild操作时，不需要考虑该值。 如果提示DB_ONLINE_LOG_TOO_BIG错误，则是由innodb_online_alter_log_max_size空间不足造成的。
——3.7.2 索引排序空间
如果DDL操作涉及二级索引的创建，会在MySQL临时目录产生临时排序文件，将中间的 排序结果写入文件，最终将内容合并到最终表或索引中，然后自动删除临时排序文件。 这个路径默认为mysql全局参数tmpdir指定（默认值为/tmp，如果手动指定了innodb_tmpdir 参数的路径，则tmpdir会被覆盖），且不会在原始表的目录中创建临时排序文件。 tmpdir需要保证能够容纳要创建的二级索引，临时排序文件最大可能需要的空间等于表中的 数据量加上索引，否则执行将报错。
——3.7.3 中间表空间
如果DDL操作涉及rebuild表，则会在原表所在目录创建临时表空间文件（以#sql开头），临时表 空间大小需要等于原表大小，重建完成后会自动重命名临时表空间，删除原表空间。所以执行 rebuild操作时需要保证原表所在路径下有足够空间。
—3.8 Online DDL 补充(官方文档)
在instant(8.0.12)添加列后元数据发生更改后，如何解析页面上的物理记录？ 额外的信息与数据字典中的一些元数据一起保留在物理记录中。我们认为，将元数据 存储在适当的数据字典表中并使其在事务上保持一致将使其更健壮且更自然。此新的 元数据存储在物理记录中。这个新的元数据包括一个存储在info_bits中的标志。 info_bits中的此新信息用于跟踪是否在第一个即时ADD COLUMN之后创建记录。我们 还使用info_bits跟踪物理记录中的字段/列数。当表经历第一个即时ADD COLUMN 时的列数以及新添加的列的所有默认值都存储在数据字典中。这两条 信息存储在数据字典表的se_private_data列中。
有了这些额外的信息，现在就可以立即执行ADD COLUMN操作，而无需修改表中的任何行。 如果没有即时的ADD COLUMN，则表中的所有行将采用与以前相同的格式。即时发 出ADD COLUMN后，对该表的任何更新都将以新格式写入行。 从数据字典中查找默认值（如果有）。
在每个即时ADD COLUMN中，都会分别跟踪新添加的列的默认值。这些列的默认值可以随时 更改。因此，在重建或截断表之后，可以丢弃即时列数和默认值，此外，可以像以前 一样将表中的行更改为旧格式。如果表是分区表，则不同的分区可能具有不同数量的 即时列，并且需要不同数量的默认值。如果某些分区被重建，截断或重新创建，则 分区中的行也可以像以前一样更改为旧格式。
以下是一些可以立即完成的操作的简单示例：
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b));
Query OK, 0 rows affected (0.70 sec)
mysql> # Modify the index can be instant if it's a trivial change
mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Rename the table through ALTER TABLE can be instant
mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.26 sec)
mysql> # SET DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # DROP DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # MODIFY COLUMN can be instant
mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'),
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # ADD/DROP virtual column can be instant
mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL),
ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Do two operations instantly in the same statement
mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT
200, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP TABLE t2;
Query OK, 0 rows affected (0.36 sec)
如何观察： 用户可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：用户 可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：
mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 0 |
+----------+---------+--------------+
1 row in set (0.22 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
+----------+------+-------------+---------------+
2 rows in set (0.38 sec)
如我们所见，在innodb_tables中引入了一个名为instant_cols的新列，该列代表即时列的数量，而在
innodb_columns中引入了两个有关默认值的新列，分别名为'has_default'和'default_value'。
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
请注意，table_id不变。它不再是表的重建！正如我们所看到的，'instant_cols'现在设置为2，这意味
着在第一个即时ADD COLUMN发生时表中有a列和b列。在innodb_columns中记住c和d列的默认值。现在，
如果has_default为1，则用户可以知道是否立即添加了列。此外，如果“ has_default”为1，则此列的默
认值存储在“ default_value”字段中。d的default_value 设置为值1000的内部二进制格式。
mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+--------------------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+--------------------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
| 1065 | e | 1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)
在另一个即时添加列之后，table_id再次保持不变。'instant_cols'不会更改，并且还会记住列e的默认值。
row log空间每次申请的大小由 innodb_sort_buffer_size决定， 最大值由innodb_online_alter_log_max_size，该值默认为128M，支持动态修改。 对于更新频繁的表来讲，如果预计在DDL期间对表的更新操作存储可能超过128M时， 需要为本次操作增大该值。当然如果不涉及rebuild操作时，不需要考虑该值。 如果提示DB_ONLINE_LOG_TOO_BIG错误，则是由innodb_online_alter_log_max_size空间不足造成的。
——3.7.2 索引排序空间
如果DDL操作涉及二级索引的创建，会在MySQL临时目录产生临时排序文件，将中间的 排序结果写入文件，最终将内容合并到最终表或索引中，然后自动删除临时排序文件。 这个路径默认为mysql全局参数tmpdir指定（默认值为/tmp，如果手动指定了innodb_tmpdir 参数的路径，则tmpdir会被覆盖），且不会在原始表的目录中创建临时排序文件。 tmpdir需要保证能够容纳要创建的二级索引，临时排序文件最大可能需要的空间等于表中的 数据量加上索引，否则执行将报错。
——3.7.3 中间表空间
如果DDL操作涉及rebuild表，则会在原表所在目录创建临时表空间文件（以#sql开头），临时表 空间大小需要等于原表大小，重建完成后会自动重命名临时表空间，删除原表空间。所以执行 rebuild操作时需要保证原表所在路径下有足够空间。
—3.8 Online DDL 补充(官方文档)
在instant(8.0.12)添加列后元数据发生更改后，如何解析页面上的物理记录？ 额外的信息与数据字典中的一些元数据一起保留在物理记录中。我们认为，将元数据 存储在适当的数据字典表中并使其在事务上保持一致将使其更健壮且更自然。此新的 元数据存储在物理记录中。这个新的元数据包括一个存储在info_bits中的标志。 info_bits中的此新信息用于跟踪是否在第一个即时ADD COLUMN之后创建记录。我们 还使用info_bits跟踪物理记录中的字段/列数。当表经历第一个即时ADD COLUMN 时的列数以及新添加的列的所有默认值都存储在数据字典中。这两条 信息存储在数据字典表的se_private_data列中。
有了这些额外的信息，现在就可以立即执行ADD COLUMN操作，而无需修改表中的任何行。 如果没有即时的ADD COLUMN，则表中的所有行将采用与以前相同的格式。即时发 出ADD COLUMN后，对该表的任何更新都将以新格式写入行。 从数据字典中查找默认值（如果有）。
在每个即时ADD COLUMN中，都会分别跟踪新添加的列的默认值。这些列的默认值可以随时 更改。因此，在重建或截断表之后，可以丢弃即时列数和默认值，此外，可以像以前 一样将表中的行更改为旧格式。如果表是分区表，则不同的分区可能具有不同数量的 即时列，并且需要不同数量的默认值。如果某些分区被重建，截断或重新创建，则 分区中的行也可以像以前一样更改为旧格式。
以下是一些可以立即完成的操作的简单示例：
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b));
Query OK, 0 rows affected (0.70 sec)
mysql> # Modify the index can be instant if it's a trivial change
mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Rename the table through ALTER TABLE can be instant
mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.26 sec)
mysql> # SET DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # DROP DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # MODIFY COLUMN can be instant
mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'),
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # ADD/DROP virtual column can be instant
mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL),
ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Do two operations instantly in the same statement
mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT
200, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP TABLE t2;
Query OK, 0 rows affected (0.36 sec)
如何观察： 用户可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：用户 可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：
mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 0 |
+----------+---------+--------------+
1 row in set (0.22 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
+----------+------+-------------+---------------+
2 rows in set (0.38 sec)
如我们所见，在innodb_tables中引入了一个名为instant_cols的新列，该列代表即时列的数量，而在
innodb_columns中引入了两个有关默认值的新列，分别名为'has_default'和'default_value'。
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
请注意，table_id不变。它不再是表的重建！正如我们所看到的，'instant_cols'现在设置为2，这意味
着在第一个即时ADD COLUMN发生时表中有a列和b列。在innodb_columns中记住c和d列的默认值。现在，
如果has_default为1，则用户可以知道是否立即添加了列。此外，如果“ has_default”为1，则此列的默
认值存储在“ default_value”字段中。d的default_value 设置为值1000的内部二进制格式。
mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+--------------------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+--------------------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
| 1065 | e | 1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)
在另一个即时添加列之后，table_id再次保持不变。'instant_cols'不会更改，并且还会记住列e的默认值。
如果DDL操作涉及rebuild表，则会在原表所在目录创建临时表空间文件（以#sql开头），临时表 空间大小需要等于原表大小，重建完成后会自动重命名临时表空间，删除原表空间。所以执行 rebuild操作时需要保证原表所在路径下有足够空间。
—3.8 Online DDL 补充(官方文档)
在instant(8.0.12)添加列后元数据发生更改后，如何解析页面上的物理记录？ 额外的信息与数据字典中的一些元数据一起保留在物理记录中。我们认为，将元数据 存储在适当的数据字典表中并使其在事务上保持一致将使其更健壮且更自然。此新的 元数据存储在物理记录中。这个新的元数据包括一个存储在info_bits中的标志。 info_bits中的此新信息用于跟踪是否在第一个即时ADD COLUMN之后创建记录。我们 还使用info_bits跟踪物理记录中的字段/列数。当表经历第一个即时ADD COLUMN 时的列数以及新添加的列的所有默认值都存储在数据字典中。这两条 信息存储在数据字典表的se_private_data列中。
有了这些额外的信息，现在就可以立即执行ADD COLUMN操作，而无需修改表中的任何行。 如果没有即时的ADD COLUMN，则表中的所有行将采用与以前相同的格式。即时发 出ADD COLUMN后，对该表的任何更新都将以新格式写入行。 从数据字典中查找默认值（如果有）。
在每个即时ADD COLUMN中，都会分别跟踪新添加的列的默认值。这些列的默认值可以随时 更改。因此，在重建或截断表之后，可以丢弃即时列数和默认值，此外，可以像以前 一样将表中的行更改为旧格式。如果表是分区表，则不同的分区可能具有不同数量的 即时列，并且需要不同数量的默认值。如果某些分区被重建，截断或重新创建，则 分区中的行也可以像以前一样更改为旧格式。
以下是一些可以立即完成的操作的简单示例：
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b));
Query OK, 0 rows affected (0.70 sec)
mysql> # Modify the index can be instant if it's a trivial change
mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Rename the table through ALTER TABLE can be instant
mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.26 sec)
mysql> # SET DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # DROP DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # MODIFY COLUMN can be instant
mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'),
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # ADD/DROP virtual column can be instant
mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL),
ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> # Do two operations instantly in the same statement
mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT
200, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP TABLE t2;
Query OK, 0 rows affected (0.36 sec)
如何观察： 用户可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：用户 可以通过information_schema中的视图观察即时添加列的结果。更具体地说， 一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于 可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：
mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 0 |
+----------+---------+--------------+
1 row in set (0.22 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
+----------+------+-------------+---------------+
2 rows in set (0.38 sec)
如我们所见，在innodb_tables中引入了一个名为instant_cols的新列，该列代表即时列的数量，而在
innodb_columns中引入了两个有关默认值的新列，分别名为'has_default'和'default_value'。
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000,
ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
请注意，table_id不变。它不再是表的重建！正如我们所看到的，'instant_cols'现在设置为2，这意味
着在第一个即时ADD COLUMN发生时表中有a列和b列。在innodb_columns中记住c和d列的默认值。现在，
如果has_default为1，则用户可以知道是否立即添加了列。此外，如果“ has_default”为1，则此列的默
认值存储在“ default_value”字段中。d的default_value 设置为值1000的内部二进制格式。
mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables
WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name | instant_cols |
+----------+---------+--------------+
| 1065 | test/t1 | 2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
mysql> SELECT table_id, name, has_default, default_value FROM
information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+--------------------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+--------------------------+
| 1065 | a | 0 | NULL |
| 1065 | b | 0 | NULL |
| 1065 | c | 1 | NULL |
| 1065 | d | 1 | 800003e8 |
| 1065 | e | 1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)
在另一个即时添加列之后，table_id再次保持不变。'instant_cols'不会更改，并且还会记住列e的默认值。