MySQL线上环境单表1000w数据增加字段怎么玩

http://www.gameboys.cn/article/163

向一个 1000w 数据的线上业务表里新加字段，怎么操作？

本地测试及正确解决方案：

1.准备测试环境

MySQL 测试环境

系统：Linux centos 6.8
内存：2G 内存
CPU:2 核 CPU
硬盘：200G 硬盘
MySQL 版本：5.1
测试程序：本地连内网测试

2.准备测试程序 JAVA 代码

package cn.gameboys.mysql;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;/*** Description:** @author gameboys(www.gameboys.cn)* @date 2019年11月28日*/
public class InsertTest {public static void main(String[] args) throws ClassNotFoundException, SQLException {final String url = "jdbc:mysql://192.168.6.66/test3?useUnicode=true&characterEncoding=utf8";final String name = "com.mysql.jdbc.Driver";final String user = "gameboys";final String password = "gameboys1234";Connection conn = null;Class.forName(name); // 指定连接类型conn = DriverManager.getConnection(url, user, password); // 获取连接if (conn != null) {System.out.println("获取连接成功");insert(conn);} else {System.out.println("获取连接失败");}}public static void insert(Connection conn) {// 一共插入数据int totalCount = 10000000;// 每次sql插入数据int perTimeCount = 100000;// 开始时间Long begin = new Date().getTime();//这里换表，test_insert索引为MyISAM ，test_insert2索引为InnoDB//String prefix = "INSERT INTO test_insert (id,sex,name,company,department,position) VALUES ";String prefix = "INSERT INTO test_insert2 (id,sex,name,company,department,position) VALUES ";try {// 保存sql后缀StringBuffer suffix = new StringBuffer();// 设置事务为非自动提交conn.setAutoCommit(false);// 比起st，pst会更好些PreparedStatement pst = (PreparedStatement) conn.prepareStatement(""); // 准备执行语句// 外层循环，总提交事务次数for (int i = 1; i <= totalCount; i++) {//suffix = new StringBuffer();// 第j次提交步长// 构建SQL后缀suffix.append("('" + i + "','1'" + ",'我是名字" + i + "'" + ",'np公司名'" + ",'np部门'" + ",'np职位'),");if (i % perTimeCount == 0) {// 构建完整SQLString sql = prefix + suffix.substring(0, suffix.length() - 1);// 添加执行SQLpst.addBatch(sql);// 执行操作pst.executeBatch();// 提交事务conn.commit();// 清空上一次添加的数据suffix = new StringBuffer();}}// 头等连接pst.close();conn.close();} catch (SQLException e) {e.printStackTrace();}// 结束时间Long end = new Date().getTime();// 耗时System.out.println(totalCount + "条数据插入花费时间 : " + (end - begin) / 1000 + " s");System.out.println("插入完成");}
}

3.新建数据库，添加测试数据表：

MyISAM索引的表CREATE TABLE `test_insert` (`id` int(11) NOT NULL,`sex` varchar(1) DEFAULT NULL COMMENT '性别',`name` varchar(20) DEFAULT NULL COMMENT '名字',`company` varchar(20) DEFAULT NULL COMMENT '公司',`department` varchar(20) DEFAULT NULL COMMENT '部门',`position` varchar(20) DEFAULT NULL COMMENT '职位',PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;InnoDB索引的表
CREATE TABLE `test_insert2` (`id` int(11) NOT NULL,`sex` varchar(1) DEFAULT NULL COMMENT '性别',`name` varchar(20) DEFAULT NULL COMMENT '名字',`company` varchar(20) DEFAULT NULL COMMENT '公司',`department` varchar(20) DEFAULT NULL COMMENT '部门',`position` varchar(20) DEFAULT NULL COMMENT '职位',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4.解决运行程序报错

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (6977867 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
设置插入sql的大小为100m
set global max_allowed_packet = 100*1024*1024设置好后重新登录数据库才能看的设置后的值
mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 104857600  |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

5.执行代码得到两种引擎插入 1000w 条数据需要的时间

5.1插入表索引为MyISAM的结论：
10000000条数据插入花费时间 : 105 s
5.2插入表索引为InnoDB的结论，两次：
10000000条数据插入花费时间 : 194 s
10000000条数据插入花费时间 : 215 s

6.正确插入 1000w 数据表新加字段方法

1.新加中间表 sql

CREATE TABLE `test_insert2_new` (`id` int(11) NOT NULL,`sex` varchar(1) DEFAULT NULL COMMENT '性别',`name` varchar(20) DEFAULT NULL COMMENT '名字',`company` varchar(20) DEFAULT NULL COMMENT '公司',`department` varchar(20) DEFAULT NULL COMMENT '部门',`position` varchar(20) DEFAULT NULL COMMENT '职位',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter tabletest_insert2_new add `testCol` bigint(11) NOT NULL DEFAULT '0' COMMENT '测试新加字段';

2.将老表数据插入新表

insert into test_insert2_new(id, sex, name, company, department, position) select id, sex, name, company, department, position from test_insert2;

3.交换名字，添加字段成功

alter table test_insert2 rename to test_insert2_old; alter table test_insert2_new rename to test_insert2;

4.执行结果

[SQL]
insert into test_insert2_new(id, sex, name, company, department, position) select id, sex, name, company, department, position from test_insert2;
受影响的行: 10000000
时间: 66.029ms
[SQL]
alter table test_insert2 rename to test_insert2_old;
受影响的行: 0
时间: 0.058ms
[SQL]
alter table test_insert2_new rename to test_insert2;
受影响的行: 0
时间: 0.013ms
耗时为66s，期间是可以查询test_insert2表，对业务影响最小，这个方案比较可取。

8.删库跑路的操作方法：

8.1 执行 sql

alter table test_insert2_old add 'testCol' bigint(11) NOT NULL DEFAULT '0' COMMENT '测试新加字段';

8.2 结论

[SQL]
alter table test_insert2_old add `testCol` bigint(11) NOT NULL DEFAULT '0' COMMENT '测试新加字段';
受影响的行: 10000000
时间: 86.621ms
期间查询请求是可以成功的，但是update和insert是阻塞的，严重影响线上业务，所以这个方案是不可取的。