如何提升MySQL批量插入的效率

转载 2015年07月09日 14:30:20

需要将大量数据(大概5W条)插入MySQL数据库,用普通的SQL Statement执行,时间大概是几分钟。于是想到用PreparedStatement,但是改了之后发现效率并没有很大的提升。不成,想到了load data local infile...命令,以前一直认为这条命令仅限MySQL终端上使用而不是标准的SQL语句,今天看了几篇文章之后有了很大的收获。

1. 使用PreparedStatement batch operation

以前使用PreparedStatement性能没有很大提升的原因在于:

  • 没有使用批处理方法
  • 在语句执行之前应关闭事务自动提交,语句执行完之后再提交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
public void batchLoad(Connection connection)
{
try {
connection.setAutoCommit(false);
BufferedReader reader = new BufferedReader(new FileReader("tfacts_result"));
String sqlString = "insert into test(node1, node2, weight) values(?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sqlString);
String line = null;
while(true)
{
line = reader.readLine();
if(line == null)
{
break;
}
String[] columns = line.split("\t");
for(int i = 1; i <= columns.length; i++)
{
pstmt.setString(i, columns[i-1]);
}
pstmt.addBatch();
}
pstmt.executeBatch();
connection.commit();
pstmt.close();
reader.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}catch (IOException e){
e.printStackTrace();
}

2.使用load data local infile into tabel XXX(注意在文件中用\t将每列数据隔开)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public void loadData(Connection connection)
{
long starTime = System.currentTimeMillis();
String sqlString = "load data local infile ? into table test";
PreparedStatement pstmt;
try {
pstmt = connection.prepareStatement(sqlString);
pstmt.setString(1, "tfacts_result");
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
long endTime = System.currentTimeMillis();
System.out.println("program runs " + (endTime - starTime) + "ms");
}

测试了5W条数据,PreparedStatement耗时10s,而load data infile耗时3s。

REF:

http://www.iteye.com/topic/320942

http://www.blogjava.net/jicheng687/archive/2010/12/23/341418.html

PS:使用load data local infile 命令的时候貌似最后一列后面还需要加“\t”,不然会会把换行符导入到表中最后一列的数据中,影响到其他SQL语句对这一列数据的引用。

相关文章推荐

jdbc连接Oracle/MySQL数据库进行批量导入操作,如何提高效率???

使用JDBC连接Oracle数据库时行

mysql批量insert数据的几种方法的效率比较

最近做shopsping爬虫优化时,得出几点关于批量insert数据及各自的效率的心得 语言:java 效率衡量参数:多次执行,比较各自的执行时间 测试数据量:1W条 mysql驱动版本:5....

MongoDB批量操作及与MySQL效率对比

本文主要通过批量与非批量对比操作的方式介绍MongoDB的bulkWrite()方法的使用。顺带与关系型数据库MySQL进行对比,比较这两种不同类型数据库的效率。如果只是想学习bulkWrite()的...

如何利用Spark提高批量插入Solr的效率

有时候我们会碰到这样的场景:利用Spark批量插入数据。因为Spark相比MR编程更方便,更容易上手。因此接下来讲讲利用Spark批量插入数据时候的注意点。假设批量往SolrCloud里面插入数据。 ...

Android数据流操作之--提高数据插入效率(批量插入方法)

android 向数据库中,批量插入数据方法效率的比较

从MySQL到Redis,提升数据迁移的效率

题外话:看到一篇不错的从mysql迁移数据到redis的解决方案,特此记录一下。 做开发的同学都知道,一旦设计到底层存储优化,数据结构甚至数据库的变更,通常都会进行数据迁移的工作。如果系统运行时间...

mysql从innodb转到MyIsam的count查询效率极大提升

mysql从innodb转到MyIsam的count查询效率极大提升   近日,公司有个业务日志的表超过500万后,count(*)需要4分55秒,将该表的存储引擎从innodb转换到MyIsam...

MySQL提升效率之limit

分页查询在我们的实际工作中经常会遇到,但是对于不同的数据库,分页查询的实现方式也不一样。而在MySQL中的分页就是靠limit实现的,可是limit的效率怎么样呢,我们来看看。 我们在写代码的...

c#对mysql的批量插入

一、背景介绍         前段时间,由于工作需要,实现了C#对mysql进行大批量记录进行插入,也包括单条插入。最先,在网上搜了很久,网上的例子非常少,难道是mysql不够普及,还是mysql作为...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)