1.Online DDL简介
在MySQL的早期版本中,DDL操作因为锁表会和DML操作发生锁冲突,大大降低并发性。在早期版本中,大部分DDL操作的执行原理就是通过重建表的方式,因为要复制原表数据,所以会长时间锁表,只能读不能写,DDL操作和DML操作有很严重的冲突。从MySQL5.6开始,很多DDL操作过程都进行了改进,出现了Online DDL,用于支持DDL执行期间DML语句的并行操作,提高数据库的吞吐量。
MySQL 在线DDL分为 INPLACE
和 COPY
两种方式,通过在ALTER语句的ALGORITHM参数指定。
ALGORITHM=INPLACE
,可以避免重建表带来的IO和CPU消耗,保证ddl期间依然有良好的性能和并发。ALGORITHM=COPY
,需要拷贝原始表,所以不允许并发DML写操作,可读。这种copy方式的效率还是不如 inplace ,因为前者需要记录undo和redo log,而且因为临时占用buffer pool引起短时间内性能受影响。
上面只是 Online DDL 内部的实现方式,此外还有 LOCK 选项控制是否锁表,根据不同的DDL操作类型有不同的表现:默认MySQL尽可能不去锁表,但是像修改主键这样的昂贵操作不得不选择锁表。
LOCK=NONE
,即DDL期间允许并发读写涉及的表,比如为了保证 ALTER TABLE 时不影响用户注册或支付,可以明确指定,好处是如果不幸该 alter语句不支持对该表的继续写入,则会提示失败,而不会直接发到库上执行。LOCK=SHARED
,即DDL期间表上的写操作会被阻塞,但不影响读取。LOCK=DEFAULT
,让mysql自己去判断lock的模式,原则是mysql尽可能不去锁表LOCK=EXCLUSIVE
,即DDL期间该表不可用,堵塞任何读写请求。如果你想alter操作在最短的时间内完成,或者表短时间内不可用能接受,可以手动指定。
但是有一点需要说明,无论任何模式下,Online DDL开始之前都需要一个短时间排它锁(exclusive)来准备环境,所以alter命令发出后,会首先等待该表上的其它操作完成,在alter命令之后的请求会出现等待waiting meta data lock
。同样在DDL结束之前,也要等待alter期间所有的事务完成,也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行,否则一样出现连环锁表。
2.不同类DDL操作详情
不同种类DDL语句具体的执行情况是不同的,下表列举出常见DDL语句具体的执行详情,包括是否允许读写及是否锁表。这个表格希望大家可以详细对比看下,特别要关注下需要copy table的DDL操作。
操作 | 支持方式 | Allow R/W | 说明 |
add/create index | online | 允许读写 | 当表上有FULLTEXT索引除外,需要锁表,阻塞写 |
drop index | online | 允许读写 | 操作元数据,不涉及表数据。所以很快,可以放心操作 |
optimize table | online | 允许读写 | 当带有fulltext index的表用copy table方式并且阻塞写 |
alter table...engine=innodb | online | 允许读写 | 当带有fulltext index的表用copy table方式并且阻塞写 |
add column | online | 允许读写(增加自增列除外) | 1、添加auto_increment列要锁表,阻塞写;2、虽采用online方式,但是表数据需要重新组织,所以增加列依然是昂贵的操作 |
drop column | online | 允许读写(增加自增列除外) | 同add column,重新组织表数据,,昂贵的操作 |
Rename a column | online | 允许读写 | 操作元数据;不能改列的类型,否则就锁表 |
Reorder columns | online | 允许读写 | 重新组织表数据,昂贵的操作 |
Make column NOT NULL | online | 允许读写 | 重新组织表数据,昂贵的操作 |
Change data type of column | copy table | 仅支持读,阻塞写 | 创建临时表,复制表数据,昂贵的操作 |
Set default value for a column | online | 允许读写 | 操作元数据,因为default value存储在frm文件中,不涉及表数据。所以很快,可以放心操作 |
alter table xxx auto_increment=xx | online | 允许读写 | 操作元数据,不涉及表数据。所以很快,可以放心操作 |
Add primary key | online | 允许读写 | 昂贵的操作 |
Convert character set | copy table | 仅支持读,阻塞写 | 如果新字符集不同,需要重建表,昂贵的操作 |
3.DDL最佳实践
虽然MySQL 5.6和5.7版本提供了Online DDL操作,但Online DDL仍存在以下问题:
- 主从复制延迟,只有主库上DDL执行成功才会写入到binlog中,而DDL操作在从库上不能并发执行,因此即使主库执行DDL时允许并发DML操作,对于大表操作,仍会引发严重的复制延迟。
- 主库执行Online DDL时,不能根据负载暂停DDL操作。
- 使用Inplace方式执行的DDL,发生错误或被KILL时,需要一定时间的回滚期,执行时间越长,回滚时间越长。
- 使用Copy方式执行的DDL,需要记录过程中的undo和redo日志,同时会消耗buffer pool的资源,效率较低,优点是可以快速停止。
- Online DDL并不是所有时间段的Online,在特定时间段需要加元数据锁或其他锁。
- 允许并发DML的DDL,可能会导致Duplicate entry问题。
针对DDL,下面整理下几点干货建议,之后执行DDL语句时可以参考下:
- 执行DDL前查看下该表有没有被事务占用,防止出现MDL锁。
- 执行DDL前确保datadir,tmpdir磁盘空间足够。
- 能业务低峰期操作的DDL,都尽量安排在业务低峰期进行。
- 对于大表和较大表,如果对复制延迟和主库性能敏感,建议改为gh-ost或pt-osc工具。
- 对于并发操作较高的表,无论表数据量多少,不能在业务高峰期操作。
- 同个表的多个DDL语句可以合并在一起进行,避免多次table rebuild带来的消耗。但是也要注意分组,比如需要copy table和只需inplace就能完成的,应该分两个alter语句。
参考: