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

第一 基础方法

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

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.下载

或者

百度云下载

安装

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';

复制运行即可

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序源代码

微信商城信息管理系统(java后台+小程序)

前一段时间在公众号中发布了一套微信商城信息管理系统(java后台+小程序),也许大家可能都还记得。

83950
来自专栏23号杂货铺

Springboot之多数据源的配置使用

现在的企业服务逐渐地呈现出数据的指数级增长趋势,无论从数据库的选型还是搭建,大多数的团队都开始考虑多样化的数据库来支撑存储服务。例如分布式数据库、Nosql数据...

32030
来自专栏程序猿讲故事

制作mysql大数据表并验证覆盖索引的查询效率

昨天跟同事聊起数据表性能的问题,能不能仅用覆盖索引实现数据的汇总统计。找了一个开发环境已有的数据表进行测试,通过explain命令,能看到mysql通过覆盖索引...

11320
来自专栏程序猿讲故事

CentOS上搭建MariaDB复制

一直没有自己配置过mysql复制,这两天空闲一些,在电脑上装了免费的VMWare Workstation Player,然后下载CentOS的最新版,开始配置一...

8520
来自专栏程序猿讲故事

灵活使用Maven Profile

项目中一直应用Maven的profile特性解决不同环境的部署问题。最近在尝试解决本地调试环境的时候碰到一些问题,顺便仔细研究了一下。因为项目仍然在用普通Spr...

10930
来自专栏直播、短视频那些事儿

在直播app制作过程中,服务器是如何配置的?

不论是一对多直播还是一对一直播app制作,关于服务器的配置和成本是大多数运营商比较关心和头疼的问题。一般来说,在直播app运营的每个阶段,所安排的服务器台数和负...

16630
来自专栏程序猿讲故事

Mysql使用ReplicationDriver驱动实现读写分离

数据库的主从复制环境已经配好,该要解决系统如何实现读写分离功能了。Mysql的jdbc驱动提供了一种实现ReplicationDriver。

10330
来自专栏快速搭建 Discuz! 论坛

如何快速搭建 Discuz! 论坛

Discuz!全称:Crossday Discuz! Board,是一套免费使用的社区论坛软件系统,由北京康盛新创科技有限责任公司推出,目前最新版本是Discu...

37640
来自专栏程序源代码

你认识的“索引”那些事儿

其实提到索引这个名词,有些抽象我们不太好理解这个名词。你转换下思路和学习方法。你会发现其实生活中的索引无处不在。

9420
来自专栏杨建荣的学习笔记

计算MySQL表碎片的SQL整理

当然整理的过程不光是知识梳理的过程,也是转化为实践场景的一个过程,通过这样一个体系,对于整个MySQL对象生命周期管理有了较为深入的认识,这里我来抛砖引玉,来作...

11910

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励