文章介绍了在MySQL中修改线上大表结构的方法和注意事项。首先，文章分析了直接修改大表结构可能带来的性能问题和风险。接着，提出了几种优化方案，如使用pt-online-schema-change工具、分批次修改数据、创建新表并迁移数据等。文章详细解释了每种方法的操作步骤和适用场景，帮助读者在不影响线上业务的情况下安全高效地修改大表结构。
场景
在线数据库的维护中，总会涉及到研发修改表结构的情况，修改一些小表影响很小，而修改大表时，往往影响业务的正常运转，如表数据量超过500W，1000W，甚至过亿时
影响：
- 在线修改大表的表结构执行时间往往不可预估，一般时间较长
- 由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作
- 如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入
- 修改大表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低
- 在线修改大表结构容易导致主从延时，从而影响业务读取
工具说明
pt-online-schema-change
Github 开源地址：
https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-online-schema-change
pt-online-schema-change是percona公司开发的一个开源数据库工具，在percona-toolkit包里面可以找到这个功能，它可以在不锁表的情况下，在线修改表结构
原理:
- 首先它会新建一张一模一样的表，表名一般是_new后缀
- 然后在这个新表执行更改字段操作
- 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
- 最后将原表的数据拷贝到新表中，然后替换掉原表
好处:
- 降低主从延时的风险
- 可以限速、限资源，避免操作时MySQL负载过高
建议:
- 在业务低峰期做，将影响降到最低
安装
安装 Percona Toolkit
Mac下使用 homebrew安装
brew install percona-toolkit
pt-online-schema-change --version # 可以看到版本输出说明安装成功CentOS 下安装
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm # 添加Percona仓库
sudo yum install percona-toolkit # 安装Percona Toolkit
使用Percona官方二进制包安装
- 下载Percona Toolkit：访问 Percona Toolkit下载页面 并下载适用于你系统的二进制包。
- 解压下载的包：
tar -xzf percona-toolkit-*.tar.gz - 进入解压后的目录：
cd percona-toolkit-* - 安装Percona Toolkit：
sudo ./install
使用
1.参数
./bin/pt-online-schema-change –help 可以查看参数的使用，我们只是要修改个表结构，只需要知道几个简单的参数就可以了–user= 连接mysql的用户名 –password= 连接mysql的密码 –host= 连接mysql的地址 P=3306 连接mysql的端口号 D= 连接mysql的库名 t= 连接mysql的表名 –alter 修改表结构的语句 –execute 执行修改表结构 –charset=utf8 使用utf8编码，避免中文乱码 –no-version-check 不检查版本，在阿里云服务器中一般加入此参数，否则会报错
2.为避免每次都要输入一堆参数，写个脚本复用一下，pt.sh#!/bin/bash table=$1 alter_conment=$2 cnn_host=’127.0.0.1′ cnn_user=’user’ cnn_pwd=’password’ cnn_db=’database_name’ echo “$table” echo “$alter_conment” /root/percona-toolkit-2.2.19/bin/pt-online-schema-change –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
3.添加表字段
如添加表字段SQL语句为:
ALTER TABLE tb_test ADD COLUMN column1 tinyint(4) DEFAULT NULL;
那么使用pt-online-schema-change则可以这样写
sh pt.sh tb_test “ADD COLUMN column1 tinyint(4) DEFAULT NULL”
4.修改表字段
SQL语句：
ALTER TABLE tb_test MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT ‘0’;
pt-online-schema-change工具:
sh pt.sh tb_test “MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT ‘0’”
5.修改表字段名
SQL语句:
ALTER TABLE tb_test CHANGE COLUMN age adress varchar(30);
pt-online-schema-change工具:
sh pt.sh tb_test “CHANGE COLUMN age address varchar(30)”
6.添加索引
SQL语句:
ALTER TABLE tb_test ADD INDEX idx_address(address);
pt-online-schema-change工具:
sh pt.sh tb_test “ADD INDEX idx_address(address)”
注意事项
- pt-online-schema-change工具还有很多其他的参数，可以有很多限制，比如限制CPU、线程数量、从库状态等等
- 一定要在业务低峰期做，这样才能确保万无一失
- 备份数据：在执行任何表结构变更之前，确保你已经备份了数据。
- 测试环境：在生产环境中执行变更之前，建议先在测试环境中进行测试。
- 权限：确保你有足够的权限来执行表结构变更。
官方文档：
https://docs.percona.com/percona-toolkit/pt-online-schema-change.html