MySql数据库大表添加字段的方法

链接：https://cloud.tencent.com/developer/article/1524019

　　

MySql数据库大表添加字段的方法

2019-10-212019-10-21 17:09:08阅读 1.9K0

 

第一 基础方法

增加字段基本方法，该方法适合十几万的数据量，可以直接进行加字段操作。

ALTER TABLE tbl_tpl ADD title(255) DEFAULT '' COMMENT '标题' AFTER id;

但是，线上的一张表如果数据量很大，执行加字段操作就会锁表，这个过程可能需要很长时间甚至导致服务崩溃，那么这样操作就有风险。

第二 临时表方法

思路如下：

① 创建一个临时的新表，首先复制旧表的结构（包含索引）

create table new_table like old_table;

② 给新表加上新增的字段，注意，此时新表是空表，加字段很快；

③ 把旧表的数据复制过来

insert into new_table(filed1,filed2) select filed1,filed2 from old_table;

④ 删除旧表，重命名新表的名字为旧表的名字

不过这里需要注意，执行第三步的时候，可能这个过程也需要时间，这个时候有新的数据进来，所以原来的表如果有字段记录了数据的写入时间就最好了，可以找到执行这一步操作之后的数据，并重复导入到新表，直到数据差异很小。不过还是会可能损失极少量的数据。

所以，如果表的数据特别大，同时又要保证数据完整，最好停机操作。

第三 使用pt-online-schema-change

介绍

pt-online-schema-change是percona公司开发的一个工具，在percona-toolkit包里面可以找到这个功能，它可以在线修改表结构

原理:

• 首先它会新建一张一模一样的表，表名一般是_为前缀_new后缀,例如原表为t_user 临时表就是_t_user_new
• 然后在这个新表执行更改字段操作
• 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
• 最后将原表的数据拷贝到新表中，然后替换掉原表

SQL语句:

ALTER TABLE tmp_task_user ADD support tinyint(1) unsigned NOT NULL DEFAULT '1';

工具命令：

sh pt.sh tmp_task_user "ADD COLUMN support tinyint(1) unsigned NOT NULL DEFAULT '1'"

好处:

• 降低主从延时的风险
• 可以限速、限资源，避免操作时MySQL负载过高

建议:

• 在业务低峰期做，将影响降到最低

安装

1.去官网下载对应的版本，官网下载地址:下载网址

查询mysql版本 根据版本下载对应的软件

select version();

2.下载

 

或者

百度云下载

• 链接:https://pan.baidu.com/s/1EE_aZmLtGe-jAT3sUWtZow
• 密码:yxqz

安装

rpm  -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

安装后位置

/bin/pt-online-schema-change

3.该工具需要一些依赖包，直接执行不成功时一般会有提示，这里可以提前yum安装

yum list |grep Digest |grep MD5
yum install -y perl-Digest-MD5

yum list |grep Term |grep ReadKey
yum install -y perl-TermReadKey.x86_64

yum list |grep DBI
yum install -y perl-DBI

yum list |grep DBD
yum install -y perl-DBD-MySQL

yum list |grep Time |HiRes
yum install -y perl-Time-HiRes

yum list |grep Socket |grep SSL
yum install -y perl-IO-Socket-SSL

使用

1.参数

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"
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

添加执行权限

chmod +x pt.sh

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)"

实际示例

ALTER TABLE t_question_bak DROP COLUMN docBase64,DROP COLUMN typeTextids,DROP COLUMN typeTextNames,DROP COLUMN kaofaid,DROP COLUMN kaofaname,DROP COLUMN topicNumber,DROP COLUMN docorder,DROP COLUMN corepointcode,DROP COLUMN corepointids,DROP COLUMN corepointcodes,DROP COLUMN pointnames,DROP COLUMN corepointnum,DROP COLUMN directorids,DROP COLUMN directornames,DROP COLUMN directorcodes;
ALTER TABLE t_question_bak MODIFY COLUMN typeTextId INT(11) DEFAULT 0 COMMENT '题型ID(对应表t_subject_questiontype)';

pt-online-schema-change工具:

sh pt.sh t_question_bak "DROP COLUMN docBase64,DROP COLUMN typeTextids,DROP COLUMN typeTextNames,DROP COLUMN kaofaid,DROP COLUMN kaofaname,DROP COLUMN topicNumber,DROP COLUMN docorder,DROP COLUMN corepointcode,DROP COLUMN corepointids,DROP COLUMN corepointcodes,DROP COLUMN pointnames,DROP COLUMN corepointnum,DROP COLUMN directorids,DROP COLUMN directornames,DROP COLUMN directorcodes,MODIFY COLUMN typeTextId INT(11) DEFAULT 0 COMMENT '题型ID(对应表t_subject_questiontype)',ADD COLUMN belongtype int(2) DEFAULT 0"

删除索引

sh pt.sh t_question_bak "DROP COLUMN docBase64,DROP COLUMN typeTextids,DROP COLUMN typeTextNames,DROP COLUMN kaofaid,DROP COLUMN kaofaname,DROP COLUMN topicNumber,DROP COLUMN docorder,DROP COLUMN corepointcode,DROP COLUMN corepointids,DROP COLUMN corepointcodes,DROP COLUMN pointnames,DROP COLUMN corepointnum,DROP COLUMN directorids,DROP COLUMN directornames,DROP COLUMN directorcodes,MODIFY COLUMN typeTextId INT(11) DEFAULT 0 COMMENT '题型ID(对应表t_subject_questiontype)',ADD COLUMN belongtype int(2) DEFAULT 0,DROP INDEX 上传时间,DROP INDEX 试题科目,DROP INDEX 组题次数,DROP INDEX 删除人员,DROP INDEX 审核时间,DROP INDEX 主键,DROP INDEX 题型ID,DROP INDEX 难易度,DROP INDEX 上传人ID,DROP INDEX 所属试卷类型ID,DROP INDEX 删除人ID,DROP INDEX 试题来源类型,DROP INDEX 所属学校ID,DROP INDEX 试卷ID,DROP INDEX 核心知识点ID,DROP INDEX question_testid,DROP INDEX 试题状态,DROP INDEX ctyp值,DROP INDEX docHtml2,DROP INDEX q_docHtml2"

实际测试删除索引后再修改表结构，效率将大大提升。 100W数据在不删除索引的情况下50多分钟才处理了50%，删除索引后只用了2分钟。

删除表索引

生成删除索引的SQL

# 拼接删除索引的语法(排除主键索引)
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,';') 
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923' AND TABLE_NAME='t_question' AND i.INDEX_NAME <> 'PRIMARY';

复制运行即可