mysql大批量插入数据的正确做法

MySQL插入已知的1000万条数据

如何快速的向数据库中插入已知的1000万条数据

​ 假设这1000W条数据在同一个集合里，那么将这个集合进行遍历，循环1000W次，结合数据库编程技术，如JDBC，就可以插入数据库，这么简单的操作，显然不是正确答案…

这样做的缺陷：

1.应用服务器（程序运行所在服务器）与MySQL数据库服务器并不是同一台，当需要执行数据库操作时，会由应用服务器将SQL语句发送到MySQL数据库服务器，发送过程中就需要建立网络连接，才可以发送SQL语句，则总共需要连接1000W次，效率极低。同时，每次执行SQL语句之前，MySQL服务器还会对SQL语句进行语法分析，语义分析，编译检查等才可以执行，假设最终有1000W条SQL语句，则这些过程需要耗费大量时间。
针对这个缺陷，可以使用【批处理】解决，减少连接数据库的次数，同时将单条插入语句改为一次插入多条数据以提高效率。如：

List<User> list = findAllUser();
Statement stat = conn.createStatement();
for (User u : list) {
	String sql = "INSERT INTO t_user (uname,age,email) VALUE ('"
                  +u.getUsername()+"',"+u.getAge()+",'"+u.getEmail()+"')";
	stat.addBatch(sql);
}

stat.executeBatch();
INSERT INTO t_user
      (uname,upwd,age,email)
VALUE 
      ('root1','123456',15,'root111@qq.com'),

      ('root2','123456',18,'root222@qq.com')，

      ('root3','123456',26,'root333@qq.com')，
      ....

​ 当两种方法配合使用，可以使用每条SQL语句插入100条数据，批处理每次发送1000条这样的数据，共循环100次即可。
​ 理论上来说，批处理操作不建议一次性超过【5000】，每条SQL语句根据具体需求调整，不建议超过【100】，可以调整循环次数来改善效率。

下面为测试代码：

1.测试普通插入

@Test

public void testInsert() {

	UserDAO dao = new UserDAO();
	long t1 = System.currentTimeMillis();

	for(int i=0;i<10000000;i++) {

	    dao.insert(


                "INSERT INTO t_user (uname,pwd,age,email) VALUES ('root','123',15,'root@qq.com')");

	}
	long t2 = System.currentTimeMillis();
	int time = (int) ((t2-t1)/1000);
	System.out.println("共耗时：" + time +"秒")
}

测试结果：

2.测试批处理配合拼接SQL语句插入

@Test



public void testBatch() throws SQLException {
	Connection conn = DBUtils.getConn();
	Statement stat = conn.createStatement();
long t1 = System.currentTimeMillis();

	for(int k=0;k<100;k++) {
	for (int i=0;i<1000;i++) {
			StringBuilder sql =new StringBuilder();
			sql.append("INSERT INTO t_user (uname,pwd,age,email) VALUES ");
			for (int j = 0; j < 100; j++) {				sql.append("('root','123',15,'root@qq.com'),");

			}
			stat.addBatch(sql.toString().substring(0,sql.lastIndexOf(",")))
		}

		stat.executeBatch();
	}

	long t2 = System.currentTimeMillis();
	int time = (int) ((t2-t1)/1000);
	System.out.println("共耗时：" + time +"秒");

}

测试结果：

结果显而易见了：第二种方案的执行效率远高于普通插入方法，不过，具体运行时间和电脑配置应该也有关系的，所以不用纠结时间长短，还是会用高效的方法就好了。

PS:【怎么就显而易见了，你压根没有贴图啊，想知道为什么吗，讲话儿了，我自认为I7的处理器也还拿得出手，可是第一种实在是太慢了，一晚上大概四个多小时吧，只插了50W+条，对，你没有看错，我掐指一算，这要真插完，要八百多个小时，显然是不成立的，肯定哪里算错了，实际上一两天也就没有问题了，存储引擎还是【InnoDB】的，以后有机会我测试下具体时间再上图，具体细节，以后慢慢更新，感觉有点心疼我电脑，都烫手了…】

​ 在尝试了几次，都被各种意外打断之后，我决定没有必要把数据全部插完，因为实在是太多了，不多说，上图吧

测试方式我调整了一下，每10W条数据显示一次用时，结果也算是情理之中，每10W条大概是一个小时多一点（68分钟），这样算下来，1000W条总共需要将近五天的时间，估计最慢也就这个样子了，但是不理解的是，这次测试的是i3的电脑，8G内存，200多万的时候，因为OOM，程序中断了，但和i7的运行时间感觉没有差多少，难道服务器的CPU性能不影响么…存疑。

来补一下之前的窟窿，据说【i3】CPU的单核性能是非常强劲的，那也就意味着，单线程的情况下，【i3】和【i7】确实没多大区别，毕竟我也没有用线程搞插入，如果真有这种操作，可能会体现出多核CPU的强大之处吧，对硬件不太了解，大佬们，轻点喷！

MySQL

如何快速的向数据库中插入已知的1000万条数据

​ 假设这1000W条数据在同一个集合里，那么将这个集合进行遍历，循环1000W次，结合数据库编程技术，如JDBC，就可以插入数据库，这么简单的操作，显然不是正确答案…

这样做的缺陷：

1.应用服务器（程序运行所在服务器）与MySQL数据库服务器并不是同一台，当需要执行数据库操作时，会由应用服务器将SQL语句发送到MySQL数据库服务器，发送过程中就需要建立网络连接，才可以发送SQL语句，则总共需要连接1000W次，效率极低。同时，每次执行SQL语句之前，MySQL服务器还会对SQL语句进行语法分析，语义分析，编译检查等才可以执行，假设最终有1000W条SQL语句，则这些过程需要耗费大量时间。
针对这个缺陷，可以使用【批处理】解决，减少连接数据库的次数，同时将单条插入语句改为一次插入多条数据以提高效率。如：

List<User> list = findAllUser();
Statement stat = conn.createStatement();
for (User u : list) {
	String sql = "INSERT INTO t_user (uname,age,email) VALUE ('"

                    +u.getUsername()+"',"+u.getAge()+",'"+u.getEmail()+"')";
	stat.addBatch(sql);
}



stat.executeBatch();
INSERT INTO t_user
      (uname,upwd,age,email)
VALUE 
      ('root1','123456',15,'root111@qq.com'),
      ('root2','123456',18,'root222@qq.com')，
      ('root3','123456',26,'root333@qq.com')，
      ..

​ 当两种方法配合使用，可以使用每条SQL语句插入100条数据，批处理每次发送1000条这样的数据，共循环100次即可。
​ 理论上来说，批处理操作不建议一次性超过【5000】，每条SQL语句根据具体需求调整，不建议超过【100】，可以调整循环次数来改善效率。

下面为测试代码：

1.测试普通插入

@Test

public void testInsert() {

	UserDAO dao = new UserDAO();
	long t1 = System.currentTimeMillis();
	for(int i=0;i<10000000;i++) {
	    dao.insert(
                "INSERT INTO t_user (uname,pwd,age,email) VALUES ('root','123',15,'root@qq.com')");
	}
	long t2 = System.currentTimeMillis();
	int time = (int) ((t2-t1)/1000);
	System.out.println("共耗时：" + time +"秒");
}

测试结果：

2.测试批处理配合拼接SQL语句插入

@Test



public void testBatch() throws SQLException {

	Connection conn = DBUtils.getConn();
	Statement stat = conn.createStatement();
	long t1 = System.currentTimeMillis();
	for(int k=0;k<100;k++) {
		for (int i=0;i<1000;i++) {

			StringBuilder sql =new StringBuilder();

			sql.append("INSERT INTO t_user (uname,pwd,age,email) VALUES ");

			for (int j = 0; j < 100; j++) {
				sql.append("('root','123',15,'root@qq.com'),");


			}
			stat.addBatch(sql.toString().substring(0,sql.lastIndexOf(",")));
		}
		stat.executeBatch();
	}
	long t2 = System.currentTimeMillis();
	int time = (int) ((t2-t1)/1000);
	System.out.println("共耗时：" + time +"秒");
}

测试结果：

结果显而易见了：第二种方案的执行效率远高于普通插入方法，不过，具体运行时间和电脑配置应该也有关系的，所以不用纠结时间长短，还是会用高效的方法就好了。

PS:【怎么就显而易见了，你压根没有贴图啊，想知道为什么吗，讲话儿了，我自认为I7的处理器也还拿得出手，可是第一种实在是太慢了，一晚上大概四个多小时吧，只插了50W+条，对，你没有看错，我掐指一算，这要真插完，要八百多个小时，显然是不成立的，肯定哪里算错了，实际上一两天也就没有问题了，存储引擎还是【InnoDB】的，以后有机会我测试下具体时间再上图，具体细节，以后慢慢更新，感觉有点心疼我电脑，都烫手了…】

​ 在尝试了几次，都被各种意外打断之后，我决定没有必要把数据全部插完，因为实在是太多了，不多说，上图吧

测试方式我调整了一下，每10W条数据显示一次用时，结果也算是情理之中，每10W条大概是一个小时多一点（68分钟），这样算下来，1000W条总共需要将近五天的时间，估计最慢也就这个样子了，但是不理解的是，这次测试的是i3的电脑，8G内存，200多万的时候，因为OOM，程序中断了，但和i7的运行时间感觉没有差多少，难道服务器的CPU性能不影响么…存疑。