mysql 大表ddl_MySQL日常大表的DDL操作

﹏ヽ暗。殇╰゛Y 2022-11-05 03:06 220阅读 0赞

作为DBA，我们也常常会碰到这样的需求：需要在不影响线上业务的情况下给表添加一个字段或索引。如果是一张只有几百或几千条记录的小表，这样的需求是非

常容易解决的。但如果所管理的表数据量已经上亿、而且应用与数据库交互非常频繁，不允许停机窗口的出现，这样的需求又该如何满足？

大多数的alter table操作都会涉及lock-->copy to new table-->rename-->unlock的过程，锁表时间会很长,而且alter table 的process不可被kill,一旦执行就不可回退。

在MySQL5.5和之前版本，在运行的生产环境对大表(超过数百万纪录)执行Alter操作是一件很困难的事情。因为将重建表和锁表，影响用户者的使用。

从MySQL5.6开始，Online DDL特性被引进。他增强了很多种类的Alter

Table操作避免拷贝表和锁表，在运行Alter操作的同时允许运行select,insert,update,delete语句。因此在最新版本，我

们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。

但是即使在MySQL5.6，仍然有一些Alter操作(增加/删除列，增加/删除主键,改变数据类型等)需要重建表。

虽然mysql5.6对atler table操作有了很大改进，但仍有很多限制，并不建议直接在线上使用。如果线上有DDL的需求我们建议使用以下两种方式：

1、主从架构轮询修改

2、使用在线修改工具online-schema-change

对于第一种方式，使用的前提是，你的数据库架构是一个集群，如果不是，也就无所谓的轮询修改。修改的原理就是利用主从服务，在应用无感知的情况下，得到停机窗口，进行修改。

今天我们着重讲第二种方式，利用第三方工具实现在线大表的DDL操作。这种方式在alter操作更改表结构的时候不用锁定表，也就是说执行alter的时候不会阻塞写和读取操作。

工作原理：

创建一个和你要执行alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将

原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触

发器，触发器会将在原表上更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作了。

注意点：

1、操作的表必须有主键或唯一索引否则报错。

2、如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行。

3、当业务量较大时，修改操作会等待没有数据修改后，执行最后的rename操作。因此，在修改表结构时，应该尽量选择在业务相对空闲时，至少修改表上的数据操作较低时，执行较为妥当。

4、由于可能存在一定的风险，在操作之前，建议对数据表进行备份，可以使得操作更安全、可靠。

5、当是主从环境，不在乎从的延迟，则需要加--recursion-method=none参数。当需要尽可能的对服务产生小的影响，则需要加上--max-load参数。

环境搭建：

安装依赖环境

\#\#Install DBI

wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz

tar -zxvf DBI-1.625.tar.gz

cd DBI-1.625

perl Makefile.PL

make

make install

\#\#Install DBD::Mysql

wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.023.tar.gz

tar -zxvf DBD-mysql-4.023.tar.gz

cd DBD-mysql-4.023

perl Makefile.PL

make

make install

安装percona-toolkit：

\#\#Install percona-toolkit

wget percona.com/get/percona-toolkit.tar.gz

tar -zxvf percona-toolkit-2.2.16.tar.gz

cd percona-toolkit-2.2.16

perl Makefile.PL

make

make install

常用操作：

添加字段

\[root@rac1 bin\]\#  ./pt-online-schema-change -uroot  -pxxx --alter='add

column col1\_test int' --execute D=test,t=t\_xxx\_compensate

修改字段

\[root@rac1 bin\]\# ./pt-online-schema-change  -uroot  -pxxx

\--alter='MODIFY COLUMN col1\_test TINYINT NOT NULL DEFAULT 0 ' --execute

D=test,t=t\_xxx\_compensate

改字段名

\[root@rac1 bin\]\# ./pt-online-schema-change  -uroot  -pxxx

\--alter='CHANGE COLUMN col1\_test address varchar(30)' --execute

D=test,t=t\_xxx\_compensate

删除字段

\[root@rac1 bin\]\# ./pt-online-schema-change  -uroot  -pxxx

\--alter='drop  column  address ' --execute D=test,t=t\_xxx\_compensate

添加索引

\[root@rac1 bin\]\# ./pt-online-schema-change -uroot  -pxxx --alter='add

key indx\_test(col1\_test) ' --execute D=test,t=t\_xxx\_compensate

删除索引

\[root@rac1 bin\]\# ./pt-online-schema-change -uroot  -pxxx --alter='DROP INDEX indx\_test' --execute D=test,t=t\_xxx\_compensate

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126478/viewspace-1987040/，如需转载，请注明出处，否则将追究法律责任。

发表评论取消回复

表情：

评论列表（有 0 条评论，220人围观）

还没有评论，来说两句吧...

相关阅读

相关大数据从入门到实战 - Hive表DDL操作（二）

大数据从入门到实战 - Hive表DDL操作（二）一、关于此次实践 1、实战简介 2、全部任务二、实践详解 1、第1

我不是女神ヾ/ 2023年01月06日 05:30/ 0 赞/ 133 阅读

相关大数据从入门到实战 - Hive表DDL操作（一）

大数据从入门到实战 - Hive表DDL操作（一）一、关于此次实践 1、实战简介 2、全部任务二、实践详解 1、第1

电玩女神/ 2023年01月06日 05:30/ 0 赞/ 138 阅读

相关 mysql 大表ddl_MySQL日常大表的DDL操作

作为DBA，我们也常常会碰到这样的需求：需要在不影响线上业务的情况下给表添加一个字段或索引。如果是一张只有几百或几千条记录的小表，这样的需求是非常容易解决的。但如果所管理的

﹏ヽ暗。殇╰゛Y/ 2022年11月05日 03:06/ 0 赞/ 221 阅读

相关 MYSQL分表DDL操作（存储过程）

思路是获取数据的表名，然后拿到表名进行修改。 drop procedure if exists useCursor; CREATE PROCEDURE u

素颜马尾好姑娘i/ 2022年10月11日 14:56/ 0 赞/ 182 阅读

相关 MySql小表驱动大表

有的时候我们在操作数据库时会将两个或多个数据表关联起来通过一些条件筛选数据，在关联表时我们要遵循一些原则，这样会使我们编写的SQL 语句在效率上快很多。一、优化原则

怼烎@/ 2022年06月05日 11:08/ 0 赞/ 163 阅读

相关【MySQL】小表驱动大表

优化原则：小表驱动大表，即小的数据集驱动大的数据集。先来了解两个关键字：in和exists。建立两个表，员工表和部门表。分别用两个关键字来查询，一般员工表为大数据集，部门

﹏ヽ暗。殇╰゛Y/ 2022年05月20日 06:57/ 0 赞/ 211 阅读

相关 MySql 日常指导，及大表优化思路

> 当MySQL单表记录数过大时，增删改查性能都会急剧下降单表优化除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，

Bertha 。/ 2022年05月15日 01:13/ 0 赞/ 147 阅读

相关 MySql小表驱动大表

转载：[https://blog.csdn.net/dc2222333/article/details/78234649][https_blog.csdn.net_dc2222

柔情只为你懂/ 2022年01月27日 00:01/ 0 赞/ 194 阅读

相关 MySQL——小表驱动大表

小表驱动大表准备两站表： CREATE TABLE `student` ( `id` int(11) NOT NULL, `no` v

桃扇骨/ 2021年09月02日 09:20/ 0 赞/ 518 阅读

相关 MySQL--小表驱动大表

小表驱动大表 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9

逃离我推掉我的手/ 2021年08月13日 22:36/ 0 赞/ 360 阅读