MySQL线上大表DDL避免锁表的解决方案_Mysql

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锁表的资料请关注代码网其它相关文章！