MySQL之Online DDL

[172.xx.xx.xxx:root@sz-pg-backup-zookeeper-003:/mysql/mysql3307/employees]# usr/bin/mysqlfrm --diagnostic mysql/mysql3307/employees/employee_test.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /mysql/mysql3307/employees/employee_test.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `employees`.`employee_test` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(42) NOT NULL,
  `last_name` varchar(48) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
PRIMARY KEY `PRIMARY` (`emp_no`),
KEY `idx_birth_date` (`birth_date`)
) ENGINE=InnoDB;

#...done.

[root@localhost][employees]> SELECT current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-08-16 11:48:45 |
+---------------------+
1 row in set (0.00 sec)

[root@localhost][employees]> ALTER TABLE `employee_test` COMMENT '雇员测试表';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

[172.xx.xx.xxx:root@sz-pg-backup-zookeeper-003:/mysql/mysql3307/employees]# usr/bin/mysqlfrm --diagnostic mysql/mysql3307/employees/employee_test.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /mysql/mysql3307/employees/employee_test.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `employees`.`employee_test` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(42) NOT NULL,
  `last_name` varchar(48) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
PRIMARY KEY `PRIMARY` (`emp_no`),
KEY `idx_birth_date` (`birth_date`)
) ENGINE=InnoDB COMMENT '雇员测试表';

#...done.

[root@localhost][employees]> SELECT current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-08-16 11:59:31 |
+---------------------+
1 row in set (0.00 sec)

[root@localhost][employees]> ALTER TABLE `employee_test` ENGINE=InnoDB;
Query OK, 0 rows affected (3.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

COPY: Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. Concurrent DML is not permitted.
# COPY：对原表的一个副本进行操作，将表数据从原表逐行复制到新表中。不允许并发DML。

[root@localhost][employees]> ALTER TABLE `employee_test` ENGINE=InnoDB, ALGORITHM=copy;
Query OK, 299962 rows affected (3.27 sec)
Records: 299962 Duplicates: 0 Warnings: 0

INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.
# INPLACE：操作避免复制表数据，但可能会就地重建表。在操作的准备和执行阶段可能会短暂地对表进行独占元数据锁定。通常，支持并发DML。

其实，支持Inplace的DDL也有有两种情况，一种情况是会重建表，即会rebuild，就是说，即使没有Server层创建临时表、转存数据、更换表名，删除旧表等一系列麻烦操作，但InnoDB存储引擎层也会保证B+树数据结构的顺序性而“原地”重建一次表数据。还有一种情况是不会重建表，比如，刚开始介绍的只修改元数据的DDL操作，即为no-rebuild。

1、建立一个临时文件，扫描源表主键的所有数据页；
2、用数据页中源表的记录生成B+树，存储到临时文件中；
3、生成临时文件的过程中，将所有对源表的操作记录在一个日志文件（row log）中；
4、临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与源表相同的数据文件；
5、用临时文件替换源表的数据文件。

* PREPARE阶段：
a. 创建新的临时.frm文件；
b. 持有MDL写锁（EXCLUSIVE-MDL），禁止读写；
c. 根据ALTER类型确定执行方式（copy、online-rebuild、online-norebuild）；
d. 更新数据字典的内存对象；
e. 分配row_log对象用来记录增量；
f. 生成新的临时ibd文件；

* DDL阶段：
g. 降级MDL写锁（EXCLUSIVE-MDL）为MDL读锁（SHARED_UPGRADABLE-MDL）；
h. 扫描old_table的聚簇索引每一条记录record，并遍历新表的索引进行处理；
i. 根据record构造对应的索引项，将构造索引项插入soft_buffer块排序；
j. 将sort_buffer块更新到新的索引上；
k. 记录DDL执行过程中产生的增量并在新表上重放；
l. 记录DDL执行过程中产生的增量（仅rebuild类型需要）；
m. 重放row_log间产生DML操作append到row_log最后一个block;

* COMMIT阶段：
n. 当前block为row_log最后一个时，禁止读写，升级到MDL写锁（EXCLUSIVE-MDL）；
o. 重做row_log中最后一部分增量；
p. 更新InnoDB的数据字典表；
q. 提交事务（刷事务的redo日志）；
r. 修改统计信息；
s. rename临时.ibd文件、.frm文件。

[root@localhost][(none)]> SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+
1 row in set (0.00 sec)

在使用Online DDL的时候，建议在业务低峰期执行，每次操作前，一定要去查看一下官档，因为DDL语句种类繁多，有些情况理论上的不重建，也许还是会重建，官档中也给出了详细说明：

* 表示含有不确定因素，所以使用对应语法前一定要做好测试，数据量不大的时候，可以直接使用，如果数据量很大，还是不建议使用Online DDL，推荐使用pt-osc或者gh-ost，这是两个不错的开源工具，后面会开辟单独的文章进行讲解。

※ Online和Inplace的关系：

• DDL过程如果是Online的，就一定是Inplace的；

• 反过来未必，也就是说Inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT INDEX）和空间索引 (SPATIAL INDEX) 就属于这种情况。
  

ALTER TABLE `t` ADD FULLTEXT(`field_name`);

这个过程是Inplace的，但会阻塞增删改操作，是非Online的。

※ 总结一些元数据锁信息：

• SHARED_UPGRADABLE:（ALTER TABLE语句）本身为读锁，但是有特殊，优先级为0；

• 并不受队列中的写锁等待而阻塞，只和当前持锁的SESSION比对，当前持锁SESSION为排他锁X，则等待，反之则获得锁；
  
• 为了保证一张表同时只有一个DDL操作进行，SHARED_UPGRADABLE之间是互斥的，一个时间只能有一个SHARED_UPGRADABLE的状态是GRANTED ，其余被阻塞；

• EXCLUSIVE、SHARED_NO_READ_WRITE级别相同，先进先出；
• SHARED_WRITE（DML）和SHARED_READ（SELECT）兼容，但SHARED_WRITE_ONLY优先级高于SHARED_READ（LOCK TABLE T READ）且不兼容；
• SHARED_WRITE_ONLY优先级最低，主要是因为被SHARED_WRITE互斥，但如果只有SHARED_READ则他们的优先级是兼容的。