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性能不影响么…存疑。