[眼会手也要会]对MySQL数据大的表进行DDL的一些方式

如果DDL前没有分析到位,比如对一张在线使用的大表进行了修改数据类型操作,则线上用户的一些修改等操作会产生超时无法响应等问题,影响线上业务;

网上分享的一些方法:

1.停机,进行DDL,简单粗暴不容易出问题;

2.新增表,关联的方式新增或修改字段;可能造成数据冗余,需要修改程序;

3.主从复制等集群方式,对从库进行DDL,然后切成主库;

4.新增相同表结构进行双写追加数据方式,切换程序表名或rename表名;

5.与4相同,使用触发器的方式同步新数据,有现成的的工具可用,如pt-online-schema-change;

6.评估执行语句,使用在线DDL,需要数据库版本支持;

一.在线DDL

1.MySQL 5.6及以后的版本.具体来说就是可以支持 Online DDL，可以在执行 DDL 操作的同时，不影响 DML 的正常执行，线上直接执行 DDL 操作对用户基本无感知（部分操作对性能有影响）,下图中红色部分操作支持online DDL;

2.根据执行结果,评估 Online DDL 操作的性能: Online DDL 操作的性能取决于是否发生了表的重建。在对大表执行 DDL 操作之前，为了避免影响正常业务操作，最好是先评估一下 DDL 语句的性能再选择如何操作。

(1)复制表结构，创建一个新的表 (2)在新创建的表中插入少量数据 (3)在新表上面执行 DDL 操作 (4)检查执行操作后返回的 rows affected 是否是 0。如果该值非 0，则意味着需要拷贝表数据，此时对 DDL 的上线需要慎重考虑;

示例:

修改某一列的默认值（快速，不会影响到表数据）
```
Query OK, 0 rows affected (0.04 sec)
```
添加索引（需要花费一些时间，但是 0 rows affected 说明没有发生表拷贝）
```
Query OK, 0 rows affected (11.11 sec)
```
修改列的数据类型（需要花费很长时间，并且重建表）
```
Query OK, 10000 rows affected (1 min 10.11 sec)
```

由于在执行 Online DDL 过程中需要记录并发执行的 DML 操作发生的变更，然后在执行完 DDL 操作之后再应用这些变更，因此使用 Online DDL 操作花费的时间比不使用 Online 模式执行要更长一些。

二.使用pt-online-schema-change

注意,该工具使用条件:具有主键或唯一键,不能存在外键及触发器等;

1.下载安装等准备工作,percona下载地址安装依赖包

yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum install perl-Digest-MD5

2.使用(以下为网上收集的信息)

#基本参数
--user=        连接mysql的用户名
--password=    连接mysql的密码
--host=        连接mysql的地址
P=             连接mysql的端口号
D=             连接mysql的库名
t=             连接mysql的表名
--alter        修改表结构的语句
--execute      执行修改表结构
--charset=utf8       使用utf8编码，避免中文乱码
--no-version-check   不检查版本，在阿里云服务器中一般加入此参数，否则会报错
--nodrop-old-table   保留原始表
--lock-wait-timeout  等待锁的时间(可适当大于数据库innodb_lock_wait_timeout)

为了方便复用,写个执行脚本:

#!/bin/bash
table=$1
alter_conment=$2

cnn_host='xxx'
cnn_user='xxx'
cnn_pwd='xxx'
cnn_db='wf_play_record'

echo "$table"
echo "$alter_conment"
/opt/pt-osc/percona-toolkit-3.3.1/bin/pt-online-schema-change --nodrop-old-table --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host}  P=3306,D=${cnn_db},t=$table --alter "${alter_conment}" --execute

使用脚本执行命令:

#修改表字段数据类型,正常语句
ALTER TABLE `wf_play_record`.`wf_chapter_play_record` MODIFY COLUMN `courseId` int(11) NULL`;
#使用脚本(去掉前面部分,并且去掉符号 `):
./pt-osc.sh wf_chapter_play_record "MODIFY COLUMN courseId int(11) NULL DEFAULT NULL"

执行效果: (1)执行中的时候,可以看到一个修改了字段数据类型的新表: alt 并且在原表中新增了三个触发器: alt 内容为:

#INSERT
BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `wf_play_record`.`_wf_chapter_play_record_new` (`id`, `userid`, `courseid`, `chapterid`, `playtime`, `test`) VALUES (NEW.`id`, NEW.`userid`, NEW.`courseid`, NEW.`chapterid`, NEW.`playtime`, NEW.`test`);END

#UPDATE
BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `wf_play_record`.`_wf_chapter_play_record_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `wf_play_record`.`_wf_chapter_play_record_new`.`id` <=> OLD.`id`; REPLACE INTO `wf_play_record`.`_wf_chapter_play_record_new` (`id`, `userid`, `courseid`, `chapterid`, `playtime`, `test`) VALUES (NEW.`id`, NEW.`userid`, NEW.`courseid`, NEW.`chapterid`, NEW.`playtime`, NEW.`test`); END

#DELETE
BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `wf_play_record`.`_wf_chapter_play_record_new` WHERE `wf_play_record`.`_wf_chapter_play_record_new`.`id` <=> OLD.`id`; END

(2)此时无论对原数据表做什么操作(增删改查)都是可以的,没有锁表;

(3)观察脚本执行进度: alt

(4)执行完成后,原表变为_old,新表已rename: alt 此处的行数在navicat中显示不一样(暂未发现原因),但执行count后的结果,两个表数据一致;

本文由 GY 创作，采用知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: 2021/12/08 10:07