文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySql Online DDL操作问题怎么解决

2023-07-04 20:14

关注

本文小编为大家详细介绍“MySql Online DDL操作问题怎么解决”,内容详细,步骤清晰,细节处理妥当,希望这篇“MySql Online DDL操作问题怎么解决”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧。

一、环境

为支持用户账号删除功能,需要在 user 表上加一个字段 deleted。

数据库:Mysql5.6

被 操作表 user:数量级为100w,外键200多个

操作:alter table user add deleted boolean NOT NULL default false comment '用户注销标识' , algorithm=inplace, lock=none;

二、执行过程分析

在Mysql5.6之后,mysql支持 Online DDL 操作。

Online DDL Support for Column Operations

OperationIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
Adding a columnYesYesYes*No
Dropping a columnYesYesYesNo
Renaming a columnYesNoYes*Yes
Reordering columnsYesYesYesNo
Setting a column default valueYesNoYesYes
Changing the column data typeNoYesNoNo
Dropping the column default valueYesNoYesYes
Changing the auto-increment valueYesNoYesNo*
Making a column NULLYesYes*YesNo
Making a column NOT NULLYes*Yes*YesNo
Modifying the definition of an ENUM or SET columnYesNoYesYes

如图所示,所执行的添加列操作整个过程为:

分析后认为,整个过程只有在初始化和提交的极短过程内需要用到MDL排他锁(影响线上),故而就直接在线上进行操作尝试。

三、遇到的问题

在初始化和提交阶段需要用到MDL的排他锁,而如果DDL操作一直没获取MDL锁(默认获取MDL锁超时时间为一年),那么就会造成 Waiting for table metadata lock 状态,也会阻塞后面所有对 user 表的操作(包括select)。后面会看到连接占满,服务502:

MySql Online DDL操作问题怎么解决

在遇到这样的问题后,为不影响线上,于是后面进行了工具的尝试。

四、工具尝试

工具涉及到 pt-online-schema-change、gh-ost和阿里云无锁DDL。三个工具大同小异,均为使用临时表,将原表数据拷贝到临时表,最后将临时表重命名替换掉原表。区别是在增量同步方面,一个用的触发器、一个用的binlog日志。

但是在处理外键方面,pt-online-schema-change用的删除、重建外键,gh-ost和阿里云无锁DDL则是不支持主表外键的变更。eg:阿里云无锁DDL的失败尝试

MySql Online DDL操作问题怎么解决

故使用工具进行 DDL 操作也不适合。

五、Online DDL 尝试

在本地测试30w数据新增列只需440ms后,尝试选择了 online ddl 的操作:

设置 session 级别获取 MDL 锁等待时间时间,避免长时间阻塞其他线程

$ set lock_wait_timeout=10;# 在10s内获取不到MDL锁,直接退出 alter 操作ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

kill 掉对应的线程以及事务

$ select group_concat(stat separator ' ') from (select concat('kill query ',id,';') as stat from information_schema.processlist  where command != 'Sleep' and Time > 5 order by Time desc) as stats;+-------------------------------------+| group_concat(stat separator ' ')    |+-------------------------------------+| kill query 42510; kill query 42514; |+-------------------------------------+1 row in set (0.00 sec)$ select group_concat(stat separator ' ') from (select concat('kill ',trx_mysql_thread_id,';')   as stat from information_schema.innodb_trx order by trx_started desc) as stats;+-------------------------------------------------------------------------+| group_concat(stat separator ' ')                                        |+-------------------------------------------------------------------------+| kill 42436; kill 42435; kill 42521; kill 42511; kill 42510; kill 42483; |+-------------------------------------------------------------------------+1 row in set (0.01 sec)

执行 DDL 操作:

$ alter table user add deleted boolean NOT NULL default false comment '用户注销标识' , algorithm=inplace, lock=none;

结果:

MySql Online DDL操作问题怎么解决

以上是在停服后操作的结果,其中 2 操作在停服后,没有需要 kill 的 ID。

读到这里,这篇“MySql Online DDL操作问题怎么解决”文章已经介绍完毕,想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会,如果想了解更多相关内容的文章,欢迎关注编程网行业资讯频道。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯