关于数据库:亿级大表在线不锁表变更字段与索引

36次阅读

共计 2414 个字符,预计需要花费 7 分钟才能阅读完成。

摘要:在业界中有一个比拟成熟的工具,针对大表的场景,能够在线进行 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 零碎中装置步骤:

三、pt-osc 的应用

pt-osc 工具应用起来很简略,间接在 linux 命令行输出 pt-osc 格局的命令,即可间接执行。

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

在下面的语句中:

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 工具,理论执行一个作业时,打印进去的信息。为了平安起见,局部日志信息做了暗藏疏忽。


四、性能比照

后面介绍了很多 pt-osc 的长处,以及良好的个性。那么理论应用成果到底怎么样呢?在测试环境中,专门做了一个测试,让大家有更加直观的感触。

在测试库中,筹备了一张 1600 万数据的大表,指标为对大表增加一个字段,别离应用存储过程和 pt-osc 工具,进行测试。

4.1 应用存储过程

首先应用存储过程做测试,为避免锁表,每次只更新 200 行。整个变更从开始到实现,须要消耗 90 分钟。其实,存储过程在执行过程中,如果恰好用户也在 DDL 操作存储过程正在变更的数据行,还有可能会锁住用户的数据,导致用户不能变更胜利。

4.2 应用 pt-osc 工具

pt-osc 从开始执行到变更实现,耗时 7 分钟左右,速度十分快。在执行的过程中,测试环境的服务连贯到该数据库,并执行多个会操作该表的工作,整个过程中,工作可能失常执行,未出现异常状况。

五、结语

ps-osc 的上述长处,在现网环境的不停服等要求下,可能优雅地帮忙咱们施行变更,且保障在变更期间,数据库不会受到锁表、过载等的影响,进而保障了业务可能失常运行。

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

点击关注,第一工夫理解华为云陈腐技术~

正文完
 0