我要评论1、锁表问题
在系统研发过程中，随着业务需求千变万化，避免不了调整线上mysql ddl数据表的操作，也就是alter table操作，例如：加个索引、加个字段等…
但是如果这张线上表是个大表，也就是说该表可能存在百万、千万、甚至上亿条数据，这时候ddl操作这个过程耗时非常久，并且这个执行阶段存在一个极端现象：锁表，锁表会带来很大的问题，那就是直接导致线上大表读写阻塞。这对大部分系统来说，是无法接受的。
现在有很多避免线上锁表的方案，例如：
- 停机执行（直接系统停机维护…不推荐 ）
- online ddl（mysql 5.6版本以上支持，不推荐 ）
- pt-online-schema-change（推荐 ）
本次主要介绍pt-online-schema-change，因为online ddl这个方案争议还是比较多的，并不建议使用，具体原因参考其他文章，本文重点不在这里。
2、pt-online-schema-change 原理
参考文档：pt-online-schema-change — percona toolkit documentation
- 首先，根据
原表创建新表，但是并不会拷贝原表中的数据，_new结尾 - 在
新表执行 ddl 语句，因为是空表，执行速度很快 - 给
原表加3个触发器，捕获变更（insert/update/delete），避免迁移过程中，新表数据不实时同步原表 - 批量拷贝
原表数据到新表 - 数据一致后，会删除
原表，留下新表作为生产表。这个过程通常是瞬时的，新表此时已经包含了所有的最新数据
3、pt-online-schema-change 实战
3.1、准备数据
本次我使用阿里云的 ubuntu 22.04 服务器，上面安装了一台mysql数据库，mysql最好设置为innodb_autoinc_lock_mode=2，否则在高并发的写入情况下，很容易产生锁等待以及死锁，我先通过下述 sql 脚本新增 700w 测试数据：
delimiter $$
create procedure generate_data()
begin
declare i int default 0;
while i < 7000000 do
insert into user (user_name, pass_word, create_time)
values
(concat('user_', uuid()),
concat('password_', floor(rand() * 10000)),
curdate());
set i = i + 1;
end while;
end $$
delimiter ;
call generate_data();
3.2、安装工具
percona toolkit 是一个集合了多个实用工具的工具包，专门用于 mysql 数据库的管理和优化，而 pt-online-schema-change 是其中一个非常有用的工具，所以这里安装 percona toolkit：
apt install percona-toolkit
3.3、模拟锁表
先通过 navicat 依次执行下述几个命令，模拟锁表现象发生，ddl操作前的事务没有提交，就会一直锁住：
# 1.耗时查询sql（耗时几分钟） select * from user # 2.给大表加字段 alter table user add column email30 varchar (255) comment '用户邮箱' # 3.分页查询 select * from user limit 1,10
接着就通过命令查看，出现锁表现象：
show full processlist
ddl后续所有操作，都会被阻塞，没办法正常执行，会导致生产环境sql直接卡死。
3.4、解决锁表
使用 pt-online-schema-change 进行 ddl 模版大概如下：
pt-online-schema-change --host=主机ip --user=mysql账号 --password=mysql密码 --alter "ddl语句" d=数据库名,t=表名 --print --execute
重新模拟锁表现象:
# 1.耗时查询sql（耗时几分钟） select * from user # 2.给大表加字段（注意⚠️：这个在服务器上执行，不要在navicat上） pt-online-schema-change --host=172.16.0.217 --user=root --password=root --alter "add column address varchar(255) comment '家庭住址'" d=pt-online-test,t=user --print --execute # 3.分页查询 select * from user limit 1,10
最后会发现，select * from user limit 1,10查询操作不会被阻塞（但是执行会变慢一些），当然pt-online-schema-change官方提供了许多参数细节，有兴趣可以访问官网自行查看。
以上就是mysql线上大表ddl避免锁表的解决方案的详细内容，更多关于mysql大表ddl锁表的资料请关注代码网其它相关文章！
发表评论