MySQL数据库---ONLINE DDL

数据库知多少 2021-04-26

1294

生产业务系统中的表结构调整是业务需求扩展和系统设计变更的常见方式，但对生产服务而言，生产库中表的变更可能对线上服务产生影响。但有的时候这种调整是不可避免的，如何最大化的降低表结构变更对线上业务的影响呢？Percona开发了一个非常好的工具，称为 pt-online-schema-change，在线执行此类操作，而不会阻塞或影响应用程序，且允许对正在更改的表进行读/写操作；github开源的gh-ost，宣称是不依赖于触发器，通过模拟从库，在row binlog中获取增量变更，再异步应用到影子表来实现DDL操作；官方的MySQL也加入了越来越多的功能来满足在线业务表结构调整的功能，尽量降低对线上业务的影响。今天我们就来看看MySQL官方的ONLINE DDL功能吧。

数据库信息

Current user: root@localhost

SSL: Not in use

Server version: 5.7.22 MySQL Community Server - GPL

Protocol version: 10

Connection: Localhost via UNIX socket

MySQL官方的ONLINE DDL介绍

在5.7以前的MySQL版本中在添加或者删除二级索引的时候，可以不用复制原表，对于索引的添加删除这类DDL操作，MySQL数据库的操作过程为如下：

新建一个单独的临时表，表结构同原表定义的结构；
然后把原表中数据导入到临时表中，为了保持数据的一致性，中间复制数据（Copy Table）全程锁表只读；
drop掉原表；
把临时表重命名为原来的表名；

在这个操作过程中，如果有写请求进来将无法提供服务，会出现由于连接堵塞导致连接数大量增加的情况。如果是创建二级索引，会对原表加上一个S锁，创建过程不需要重建表（no-REBUILD）；删除二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义。这个过程只允许读操作，不能写入，但是会大大加快索引修改的速度（不包括主键索引）。

在MySQL 5.6中，ONLINE DDL把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等，实际效果要看所使用的选项和操作类别来定。

而在5.7版本数据库中，对ONLINE DDL功能又做了提高。

语法：

ALTER TABLE tbl\_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALTER 语句中可以指定参数ALGORITHM和LOCK分别指定DDL执行的方式和DDL期间DML的并发控制；

ONLINE DDL选项

MySQL在线DDL分为INPLACE和COPY两种方式，在ALTER语句的ALGORITHM参数指定。

ALGORITHM=INPLACE，可以避免重建表带来的IO和CPU消耗，保证DDL期间依然有良好的性能和并发。
ALGORITHM=COPY，需要拷贝原始表，所以不允许并发DML写操作，可读。这种copy方式的效率还是不如inplace，因为前者需要记录undo和REDO log，而且因为临时占用buffer pool引起短时间内性能受影响。

LOCK 选项：

选项控制是否锁表，根据不同的DDL操作类型有不同的表现：默认MySQL尽可能不去锁表。

LOCK=NONE，即DDL期间允许并发读写涉及的表，为了保证 ALTER TABLE 时不影响在线业务，可以明确指定，如果该 alter语句不支持对该表的继续写入，则会提示失败，而不会直接发到库上执行。在ALGORITHM=COPY下默认LOCK=NONE级别;LOCK=SHARED，即DDL期间表上的写操作会被阻塞，但不影响读取；LOCK=DEFAULT，让MySQL去判断lock模式，原则MySQL尽可能不去锁表；
LOCK=EXCLUSIVE，即DDL期间该表不可用，堵塞任何读写请求。如果你想alter操作在最短的时间内完成，或者表短时间内不可用能接受，可以手动指定。

注意：无论任何模式下，ONLIE DDL开始之前都需要一个短时间排它锁(exclusive)来准备环境，所以alter命令发出后，会首先等待该表上的其它操作完成，在alter命令之后的请求会出现等待waiting meta data lock。同样在DDL结束之前，也要等待ALTER期间所有的事务完成，也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行，否则一样出现连环锁表。

不同的DDL操作类别

不是MySQL数据库支持在线DDL就可以随心所欲的alter table，锁不锁表要看情况：

根据官方 Summary of ONLINE Status for DDL Operations 整理挑选的常用操作。

从表看出，In-Place为No，DML一定是No，说明 ALGORITHM=COPY 一定会发生拷贝表，只读。但ALGORITHM=INPLACEE也有可能发生拷贝表，但可以并发DML；

不允许并发DML的情况有：修改列数据类型、删除主键、变更表字符集，即这些类型操作DDL是不能ONLINE的。

注意：

更改主键索引与普通索引处理方式是不一样的，主键即聚集索引，体现了表数据在物理磁盘上的排列，包含了数据行本身，需要拷贝表；而普通索引通过包含主键列来定位数据，所以普通索引的创建只需要一次扫描主键即可，而且是在已有数据的表上建立二级索引，更紧凑，将来查询效率更高。修改主键也就意味着要重建所有的普通索引。删除二级索引更简单，修改INNODB系统表信息和数据字典，标记该所以不存在，标记所占用的表空间可以被新索引或数据行重新利用。

ONLINE DDL实现过程

ONLINE DDL主要包括3个阶段：PREPARE阶段；DDL执行阶段；COMMIT阶段。

REBUILD方式比NO-REBUILD方式实质多了一个DDL执行阶段，PREPARE阶段和COMMIT阶段类似。

DDL执行过程中三个阶段的流程：

PREPARE阶段:

创建新的临时frm文件；
持有EXCLUSIVE-MDL锁，禁止读写；
根据alter类型，确定执行方式(copy,ONLINE-REBUILD,ONLINE-NOREBUILD)；
假如是Add Index，则选择ONLINE-NOREBUILD即INPLACE方式；
更新数据字典的内存对象；
分配row_log对象记录增量(仅REBUILD类型需要)；
生成新的临时ibd文件(仅REBUILD类型需要)；

DDL执行阶段:

降级EXCLUSIVE-MDL锁，允许读写；
扫描原表上的聚集索引中每一条记录；
遍历新表的聚集索引和二级索引，逐一处理；
根据记录构造对应的索引项；将构造索引项插入sort_buffer块排序；
将sort_buffer块更新到新的索引上；
记录DDL执行过程中产生的增量数据(仅REBUILD类型需要)；
重放row_log中的操作到新索引上(no-REBUILD数据是在原表上更新的)；
重放row_log间产生dml操作append到row_log最后一个数据块上；

COMMIT阶段:

当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁；
重做row_log中最后一部分增量操作；
更新INNODB的数据字典表；
提交事务(刷事务的REDO日志)；
修改统计信息；
重命名临时idb文件，frm文件；
操作完成；

即：

ONLINE DDL注意事项

磁盘空间，REBUILD 的时候，会拷贝ibd文件，所以至少需要原表一倍的空间，因为拷贝表的的操作是直接在数据目录下进行的；
REBUILD 时，INNODB_ONLINE_ALTER_LOG_MAX_SIZE参数大小要设置的足够大；
REBUILD时，产生的DML涉及到行记录变更日志，要有足够空间存储；
inplace的时候，考虑临时目录空间是否足够；
DDL对SLAVE库延迟的影响是否可以接受；
主库ONLINE DDL过程中，由于未COMMIT，所以其它并发操作可以正常同步到从库；
主库COMMIT后，DDL同步到从库，由于从库是单线程执行SQL_THREAD，如果DDL执行需要1个小时，那么从库DDL重演至少滞后1小时；
考虑允许从库的滞后时间，如果不允许较大延迟，可以通过并行复制来优化处理；
row-log会根据主键及唯一约束来检查重复值或者修改冲突；
在执行一个允许并发DML在线 ALTER TABLE时，结束之前这个线程会应用 ONLINE LOG 记录增量修改，而这些修改是其它thread产生的，所以可能会遇到重复键值错误 (ERROR 1062 (23000): Duplicate entry)，涉及到table copy时，目前还没有机制限制暂停DDL，或者限制IO阀值；
一般来说，建议把多个alter语句合并执行，避免多次table REBUILD带来的消耗；
执行完ALTER TABLE之后，最好ANALYZE TABLE table_name去更新索引统计信息。

如何暂停DDL操作

1. KILL ID

copy table/inplace下通过kill id的方式暂停DDL操作，kill完后，仍然可以再次正常执行DDL，不会存在冲突，其创建的临时idb及frm文件会自动删除。

how full processlist;
kill id; #( DDL SQL的id号)

2. KILL -9 MYSQLD进程

在copy table ，inplace的情况下直接kill掉mysqld进程，或主机宕机，没有完成的DDL语句不会继续执行，但是，其生成的frm跟idb临时文件不会被删除，建议启动mysql服务后，手工删除无用的临时文件。

ONLINE DDL的进度监控

5.7 的版本ONLINE DDL 可以通过P_S 查看进度

1. 打开P_S功能

mysql> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/INNODB/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0
mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

2. 执行改表操作

mysql> ALTER TABLE employees.employees ADD COLUMN miDDLe_name varchar(14) AFTER first_name;
Query OK, 0 rows affected (9.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 单独开会话查看DDL进度

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/INNODB/alter table (read PK and internal sort) |            230 |           854 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.01 sec)

总结

随着MySQL数据库的发展，越来越多的功能加入到数据库中，也更方便DBA进行管理，我们也要切实了解这些功能背后的原理，才能在真正操作线上数据库的时候做到心中有底，知晓操作所带来的一切风险和后果。谨记：DDL永远不要在业务高峰期间执行。

完

=end=

mysql

文章转载自数据库知多少，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL数据库---ONLINE DDL

完

评论