MySQL线上环境单表1000w数据增加字段怎么做
向一个 1000w 数据的线上业务表里新加字段，怎么操作？
本地测试及正确解决方案：
1.准备测试环境
MySQL 测试环境
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) {
// 构建完整SQL
String sql = prefix + suffix.substring(0, suffix.length() - 1);
// 添加执行SQL
pst.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是阻塞的，严重影响线上业务，所以这个方案是不可取的。
7.总结
- MySQL 的 insert 语句后面带多个值可以加快插入速度，而且速度快到惊人；
- MySQL 向千万级数据量的表插入新列可以采用建中间表的方式；
