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

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

1. 使用PreparedStatement batch operation

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

• 没有使用批处理方法
• 在语句执行之前应关闭事务自动提交，语句执行完之后再提交

	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将每列数据隔开）

	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语句对这一列数据的引用。