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) {
					// 构建完整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 向千万级数据量的表插入新列可以采用建中间表的方式；