亿级大表在线不锁表变更字段与索引

摘要:在业界中有一个比较成熟的工具,针对大表的场景,可以在线进行Alter变更,且不会出现锁表的风险。除此之外,它还有其他的一些优点,让我们开始探索吧。

背景

大家在日常工作中,往往需要对数据库的表结构做变更,一般涉及到增删字段,修改字段属性等ALTER的操作。然而,在大表场景下,特别是千万级、亿级的大表,如果处理不当。这些操作往往会引发锁表的巨大隐患,特别是在生产环境中,一旦在变更表结构过程中,出现了长时间锁表,会导致用户产生的数据长时间无法正常变更到表中,进而导致服务功能异常,结果将是灾难性的。

一般执行这种Alter类型的变更,我们可能有以下的想法:

1、停服,在停服期间做表结构的变更,自然就可以防止对用户产生影响。但是,很多场景是不允许停服的。并且如果表的数据量达到上亿,那么需要停服时间可能需要十几个小时,甚至更长,这是极不现实的;

2、凌晨执行,在用户较少的时间段内,做变更,尽量减少对用户产生影响。但是如果出现锁表的话,万一有用户使用服务,服务将不可用;

3、使用换表,但是缺点是复制数据到新表期间,如果用户在这期间做了update或delete操作,且数据发生在已经复制完成的部分,那么将无法感知到这部分数据,导致丢失掉用户的操作数据,风险太大;

4、使用存储过程,缺点是执行时间会很久,且有可能影响到用户的DDL操作。因为为了防止每次循环修改时,锁住太多数据行,我们需要控制每次更新数据的行数,粒度不能太大,否则很有可能会锁住用户正在操作的数据行。

那么针对以上实际的需求,就没有一个很好的工具,来解决我们的痛点吗?其实在业界中,就有一个比较成熟的工具,针对大表的场景,可以在线进行Alter变更,且不会出现锁表的风险。除此之外,它还有其他的一些优点,让我们开始探索吧。

一、pt-osc是什么

pt-online-schema-change是Percona-toolkit一员,通过改进原生ddl的方式,达到不锁表在线修改表结构的效果。在Percona的官网中,关于pt-osc工具,也特别提到了ALTER表不会出现锁表的特性。

针对上面谈到的避免锁表、感知用户更新删除动作等,ps-osc工具是怎么解决的呢?

pt-osc主要执行步骤如下:

1、创建一个跟原表一模一样的新表,命名方式为'_正式表名_new';

2、使用alter语句将要变更的内容在新创建的新表上做变更,避免了对原表的alter操作;

3、在原表中创建3个触发器,分别是insert、update和delete,主要是用于原表在往新表复制数据时,如果用户有DDL操作,触发器能够将在这期间出现的DDL操作数据也写入到新表中,确保新表的数据是最新的,不会丢失掉用户的新操作数据;

4、按块拷贝数据到新表,拷贝过程对数据行持有S锁;

5、重命名,将原表重命名为老表,命名为“_正式表名_old”,将新表重命名为正式表,可通过配置决定执行完成后是否删除掉老表;

6、删除3个触发器;

二、pt-osc的安装

在linux系统中安装步骤:
image

三、pt-osc的使用

pt-osc工具使用起来很简单,直接在linux命令行输入pt-osc格式的命令,即可直接执行。

以Mysql数据库增加一个名字是MARK的字段为例:

image

在上面的语句中:

1、user和password分别为数据库执行变更操作的用户名、密码,需要高权限;

2、host为数据库的IP地址;

3、port为数据库的端口号;

4、alter后面跟上具体的alter语句;

5、D为database名字;

6、t为要执行变更的表名;

7、no-drop-old-table就是不要删除

8、charset,字符集,使用utf8;

9、max-load,在复制数据时,工具会监控数据库中正在运行的线程数,如果大于配置的Threads_running值,那么会暂停复制,直到小于该值。以此防止对数据库造成较大压力,影响现网业务正常使用;

10、critical-load,默认为50,在每个块之后检查SHOW GLOBAL STATUS,与max-load不同的是,如果负载太高,,直接中止,而不是暂停。可根据自己数据库情况斟酌配置阈值;

注意:在--alter后面跟着的变更语句中,列名不可以加符号,否则会出现报错。如--alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';",MARK字段加了符号,就会出现错误,COMMENT后面有`符号无影响。

下面是使用pt-osc工具,实际执行一个作业时,打印出来的信息。为了安全起见,部分日志信息做了隐藏忽略。

image
image

四、性能对比

前面介绍了很多pt-osc的优点,以及良好的特性。那么实际使用效果到底怎么样呢?在测试环境中,专门做了一个测试,让大家有更加直观的感受。

在测试库中,准备了一张1600万数据的大表,目标为对大表添加一个字段,分别使用存储过程和pt-osc工具,进行测试。

4.1 使用存储过程

首先使用存储过程做测试,为防止锁表,每次只更新200行。整个变更从开始到完成,需要耗费90分钟。其实,存储过程在执行过程中,如果恰好用户也在DDL操作存储过程正在变更的数据行,还有可能会锁住用户的数据,导致用户不能变更成功。

4.2 使用pt-osc工具

pt-osc从开始执行到变更完成,耗时7分钟左右,速度非常快。在执行的过程中,测试环境的服务连接到该数据库,并执行多个会操作该表的任务,整个过程中,任务能够正常执行,未出现异常情况。

五、结语

ps-osc的上述优点,在现网环境的不停服等要求下,能够优雅地帮助我们实施变更,且保证在变更期间,数据库不会受到锁表、过载等的影响,进而保证了业务能够正常运转。

本文分享自华为云社区《千万级、亿级大表在线不锁表变更字段与索引》,原文作者:active_zhao 。

点击关注,第一时间了解华为云新鲜技术~


开发者之家
华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态...

生于云,长于云,让开发者成为决定性力量

1.3k 声望
1.7k 粉丝
0 条评论
推荐阅读
【贺】来自开发者的点赞,华为云开发者联盟入选 2022 中国技术品牌影响力企业榜
2023 年 1 月 4 日,中国技术先锋年度评选 | 2022 中国技术品牌影响力企业榜单正式发布。作为中国领先的新一代开发者社区,SegmentFault 思否依托数百万开发者用户数据分析,各科技企业在国内技术领域的行为及影...

华为云开发者联盟阅读 365

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山7阅读 1.8k评论 2

2022 年中国开源创新大赛在乌镇正式启动
11 月 10 日上午,在 2022 年世界互联网大会乌镇峰会数字经济分论坛上,2022 中国互联网发展创新与投资大赛暨 2022 年中国开源创新大赛在乌镇正式启动。

MissD2阅读 10.6k

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 727

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.3k

数据可视化分析之新技能——魔数图
大家在使用数据可视化工具进行数据设计的时候,有没有遇到过这些设计场景:部门人员的履历细信息、工厂各个产线的生产状态和生产信息、公司各个部门的KPI信息……这些数据都有一个共同的特点:数据结构相同,但是内...

葡萄城技术团队1阅读 7.8k

Mybatis源码解析之执行SQL语句
通过调用 session.getMapper (AccountMapper.class) 所得到的 AccountMapper 是一个动态代理对象,所以执行 accountMapper.selectByPrimaryKey (1) 方法前,都会被 invoke () 拦截,先执行 invoke () 中的逻辑。

京东云开发者3阅读 799评论 1

封面图

生于云,长于云,让开发者成为决定性力量

1.3k 声望
1.7k 粉丝
宣传栏