DDL执行方式
- mysql 5.6 之前:进行添加索引、修改字段之类修改表结构的操作会锁表,期间会阻塞DML、不能写入数据,对大表而言会造成较长的写入中断时间、生产环境难以容忍。
- mysql 5.6 起:开始支持Online DDL,在执行DDL时会尽量少使用锁,但还是可能会锁表。
- pt-online-schema-change:简称pt-osc,是 percona-toolkit 一员,支持在不锁表的情况下在线修改表结构。
建议:小表可以直接执行DDL,几百万、几千万、上亿记录数的大表通过 pt-osc 修改表结构。
mysql online DDL
可参考:https://blog.csdn.net/weixin_45238761/article/details/125343029
percona-toolkit 介绍
percona-toolkit 简称pt,是一组数据库高级命令行工具的集合,用于执行复杂、麻烦的mysql任务。
常用的pt工具比如
- pt-table-checksum:检测主从数据一致性
- pt-heartbeat:监测主从同步延迟
- pt-table-sync:同步表数据,常用于主从数据不一致时进行数据修复
- pt-online-schema-change:在线修改表结构
pt-online-schema-change 修改表结构
pt-osc工作流程如下
-
创建一个和原表结构相同的临时表,表名后缀默认是new
-
在临时表执行DDL修改表结构,因为是空表,执行速度很快
-
在原表中创建 insert、update、delete 操作的触发器
-
从原表拷贝数据块到临时表,拷贝过程中在原表进行的写操作都会通过触发器更新到临时表
-
替换表名,将原表的表名修改为后缀 old,将临时表表名修改为原表表名
-
如果该表设置了外键,会根据 alter-foreign-keys-method 参数,检测外键相关的表,做相应处理。
-
最后默认会删除原表、触发器。
注意点
- 原表必须有主键或唯一索引,否则会报错
Cannot chunk the original table
mall.
t_user: There is no good index and the table is oversized. at /usr/local/bin/pt-online-schema-change line 5486
- 原表不能已经存在触发器,否则会报错
The table
mall.
t_userhas triggers. This tool needs to create its own triggers, so the table cannot already have triggers.
- pt-osc 异常终止时不会自动删除原表上的触发器,如果要删除新表,需要先删除原表上的触发器,否则向原表执行DML时会因为找不到新表而报错。
- 对于主从架构,应该在主库上执行pt-osc 操作。