测试表结构

CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `dept` tinyint(4) NOT NULL COMMENT '部门id',
  `name` varchar(30) DEFAULT NULL COMMENT '用户名称',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=2097165 DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

开两个 session

创建全文索引时，仅支持读，会阻塞写

mysql-1>alter table t3 add fulltext index idx_1(name);
#执行中.......

mysql-2>insert into t3 values(2097164,4,'user_2097164',now(),now());
#等待中.......

optimize table或者alter table ... engine=innodb，不会阻塞写

mysql-1> alter table t3 engine=innodb;
#执行中.......
Query OK, 0 rows affected (9.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-2> insert into t3 values(2097166,6,'user_2097166',now(),now());
Query OK, 1 row affected (0.01 sec)

optimize table或者alter table ... engine=innodb 存在全文索引时，会阻塞写

mysql-1>alter table t3 add fulltext index idx_1(name);

mysql-1> alter table t3 engine=innodb;
#执行中.......
Query OK, 2097165 rows affected (28.14 sec)     # 有数据受到影响
Records: 2097165  Duplicates: 0  Warnings: 0

# 有数据受到影响 查看
mysql-1> show profile;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             | 0.000047  |
| checking permissions | 0.000005  |
| checking permissions | 0.000003  |
| init                 | 0.000004  |
| Opening tables       | 0.000272  |
| setup                | 0.000030  |
| creating table       | 0.003757  |
| After create         | 0.000138  |
| System lock          | 0.056069  |
| copy to tmp table    | 27.775030 |  #copy to tmp table
| rename result table  | 0.302859  |
| end                  | 0.000010  |
| query end            | 0.000003  |
| closing tables       | 0.000010  |
| freeing items        | 0.000059  |
| cleaning up          | 0.000006  |
+----------------------+-----------+
16 rows in set (0.07 sec)
# 说明有全文索引时 optimize table 或者 alter table 进行了 copy tablecopy table

# 执行 alter table 同时 执行插入,会阻塞写
mysql-2> insert into t3 values(2097167,7,'user_2097167',now(),now());
#等待中.......
Query OK, 1 row affected (27.50 sec)

添加列 表结构

CREATE TABLE `t3` (
  `id` int(11) NOT NULL COMMENT '',
  `dept` tinyint(4) NOT NULL COMMENT '部门id',
  `name` varchar(30) DEFAULT NULL COMMENT '用户名称',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
) ENGINE=InnoDB AUTO_INCREMENT=2097165 DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

用例1：添加 auto_increment 列要锁表，会阻塞写

mysql-1> alter table t3 add column tid int not null primary key auto_increment;
#执行中.......
Query OK, 0 rows affected (14.26 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> insert into t3 values(2097168,8,'user_2097168',now(),now(),2097168);
#等待中.......
Query OK, 1 row affected (13.58 sec)

用例2：添加普通列是 online 的,不会阻塞写

mysql> alter table t3 add column content1 text;
#执行中.......
Query OK, 0 rows affected (8.78 sec)
Records: 0  Duplicates: 0  Warnings: 0  

mysql-2> insert into t3 values(2097170,7,'user_2097170',now(),now());
Query OK, 1 row affected (0.00 sec)

用例3：修改列类型，会阻塞写

mysql-1> alter table t3 change content1 content1 longtext;  
#执行中.......
Query OK, 2097169 rows affected (9.99 sec)      #有受影响行数
Records: 2097169  Duplicates: 0  Warnings: 0

mysql-2> insert into t3 values(2097171,1,'user_2097171',now(),now(),'a');
#等待中.......
Query OK, 1 row affected (9.50 sec)

# 有数据受到影响 查看
mysql-1> show profile;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             | 0.000107  |
| checking permissions | 0.000006  |
| checking permissions | 0.000006  |
| init                 | 0.000013  |
| Opening tables       | 0.000330  |
| setup                | 0.000058  |
| creating table       | 0.004067  |
| After create         | 0.000233  |
| System lock          | 0.011582  |
| copy to tmp table    | 10.594625 |    #copy to tmp table
| rename result table  | 0.019796  |
| end                  | 0.000010  |
| query end            | 0.000004  |
| closing tables       | 0.000009  |
| freeing items        | 0.000049  |
| cleaning up          | 0.000037  |
+----------------------+-----------+
16 rows in set (0.07 sec)

#注：修改列类型 DDL 采用 copy table 方式并且阻塞写入，线上操作须谨慎

常用DDL执行方式总结

<怕>

参考：https://blog.csdn.net/huiyunfei/article/details/120483509