JDBC插入大量数据优化

[Click_Me]

这个就是做个练习.想看下JDBC的最大优化度.
我的要求就是插入到数据库里大量的数据 比如10W 或者 100W 而且要求内存稳定.
首先说下我的代码:
我的数据库MySQL

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我的数据库辅助类

package com.jdbc.batch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public final class DBUtils {
	private static String mysqlurl = "jdbc:mysql://localhost:3306/mytest";
	private static String accessurl = "jdbc:mysql://localhost:3306/mytest";
	private static String user = "root";
	private static String password = "root";

	// 获得连接
	public static Connection getAccessConn() throws SQLException {
		return DriverManager.getConnection(accessurl, user, password);
	}
	
	public static Connection getMySqlConn() throws SQLException {
		return DriverManager.getConnection(mysqlurl, user, password);
	}

	// 释放连接
	public static void free(ResultSet rs, PreparedStatement ps, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {

				try {
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			}

		}

	}

	// 加载驱动
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("驱动加载出错");
		}
	}

}

我的测试类

package com.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BatchExample {
	private static Connection mysqlConn = null;
	private static ResultSet rs = null;
	// 总条数
	private static int allCount = 10000;
	// 分批条数
	private static int preCount = 1000;
	// 计数器
	private static int count = 0;

	private static String insertSQL = "insert into users(firstname, lastname, age) values(?, ?, ?)";

	private static PreparedStatement mysqlPs = null;

	public static void main(String[] args) throws SQLException {
		try {
			mysqlConn = DBUtils.getMySqlConn();
			mysqlPs = mysqlConn.prepareStatement(insertSQL);
			mysqlConn.setAutoCommit(false);
			long start = System.currentTimeMillis();
			
			for (int i = 1; i <= allCount; i++) {
				mysqlPs.setString(1, "firstname" + i);
				mysqlPs.setString(2, "lastname" + i);
				mysqlPs.setInt(3, 23);
				mysqlPs.addBatch();		
				if ((i % preCount) == 0){
					mysqlPs.executeBatch();
					System.out.println("当前进行完毕===>" + (++count) * preCount + "条");
				}
			}		
			long end = System.currentTimeMillis();
			System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms");

		} catch (Exception e) {
			mysqlConn.rollback();
			System.out.println("数据出错,已进行回滚");
			throw new RuntimeException();
		} finally {
			mysqlConn.commit();
			DBUtils.free(rs, mysqlPs, mysqlConn);
		}
	}

}

我用的批处理.最后执行10000条的记录是

当前进行完毕===>1000条
当前进行完毕===>2000条
当前进行完毕===>3000条
当前进行完毕===>4000条
当前进行完毕===>5000条
当前进行完毕===>6000条
当前进行完毕===>7000条
当前进行完毕===>8000条
当前进行完毕===>9000条
当前进行完毕===>10000条
数据导入完毕,所用时间为: 8140 ms

[closewbq]

你使用批处理是执行了对数据库的N次访问，而使用StringBuilder，是执行了一次数据库的更新操作。时间的差异主要体现在了数据库的访问操作上。

[Click_Me]

StringBuilder最大可以存储多少 有什么办法可以让他无限使用 比如到一定量 让它释放

[zhangyong20081204]

可以尝试下jpa的方法,,看看源代码//

[hoof82]

学了。

[zhuyouyong]

顶

[Click_Me]

主要看下我的测试类
BatchExample 我用的批处理 addBatch每1000条打包发送一次.但结果还是不尽人意8140ms.

而我换了一种方案 用executeUpdate一次提交.SQL用StringBuilder串接 效率提升很快.
代码

package com.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BufferQuery {
	private static Connection mysqlConn = null;
	private static ResultSet rs = null;
	// 总条数
	private static int allCount = 10000;
	// 分批条数
	private static int preCount = 1000;
	// 计数器
	private static int count = 0;

	private static String insertSQL = "insert into users(firstname, lastname, age) values(?, ?, ?)";

	private static PreparedStatement mysqlPs = null;

	public static void main(String[] args) throws SQLException {
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("insert into users(firstname, lastname, age) values");
			mysqlConn = DBUtils.getMySqlConn();
			mysqlPs = mysqlConn.prepareStatement(insertSQL);
			mysqlConn.setAutoCommit(false);
			long start = System.currentTimeMillis();
			
			for (int i = 1; i <= allCount; i++) {
				if(i > 1) sb.append(",");
				sb.append("('aa"+ i +"','bb',23)");	

				if(i % preCount == 0){
					System.out.println("导入进行===>" + (++count * preCount) + "条");				
				}				
			}
			mysqlPs.executeUpdate(sb.toString());
			
			
			long end = System.currentTimeMillis();
			System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms");

		} catch (Exception e) {
			mysqlConn.rollback();
			System.out.println("数据出错,已进行回滚");
			throw new RuntimeException();
		} finally {
			mysqlConn.commit();
			DBUtils.free(rs, mysqlPs, mysqlConn);
		}
	}

}

[Click_Me]

// 用的StringBuilder串接
           long start = System.currentTimeMillis();
            
            for (int i = 1; i <= allCount; i++) {
                if(i > 1) sb.append(",");
                sb.append("('aa"+ i +"','bb',23)");    

                if(i % preCount == 0){
                    System.out.println("导入进行===>" + (++count * preCount) + "条");                
                }                
            }
            mysqlPs.executeUpdate(sb.toString());

运行结果为:

导入进行===>1000条
导入进行===>2000条
导入进行===>3000条
导入进行===>4000条
导入进行===>5000条
导入进行===>6000条
导入进行===>7000条
导入进行===>8000条
导入进行===>9000条
导入进行===>10000条
数据导入完毕,所用时间为: 219 ms

1W条才129ms为什么会比批处理快这么多.但是还有问题就是 如果数据量更大 如20W 那么StringBuilder就装不下 堆栈溢出....
==========================================================
大家谁有更好的意见 说下 尤其是内存上面 我的代码 内存会上升 大家谁有经验讨论下

[Click_Me]

有兴趣的同学看下. 不求代码 只需要说下 有没有更好的方案 对于大量数据循环插入

[lz12366007]

帮你顶下。。。。。。。。。

[healer_kx]

Java处理大数据量的工作，真的很不爽，不如这部分用Python写，然后被Java调、

[swandragon]

用存储过程处理大数据量

[xiaozejun]

存储过程 处理 是一种不错的选择
在还有一次的测试虽然相差比较大
但是楼主可以多试几次 这个东西 有时候 不仅仅是
方法的原因 很有可能还有其他原因

[AldisZhan]

[Quote=引用 8 楼 closewbq 的回复:]
你使用批处理是执行了对数据库的N次访问，而使用StringBuilder，是执行了一次数据库的更新操作。时间的差异主要体现在了数据库的访问操作上。
[/Quote]
说的有道理 你用批处理自连接了 10 次，而用StringBuilder才一次
而且批处理的构造SQL语句应该没有StringBuilder高效
你可以分两组在测试一下
1.都用一次提交
2.用StringBuilder分10次

其实我觉得更好的是去看源代码，这样可以知道他们的根本区别在哪

[老黄忠]

我测试的jdbc批处理操作，插入112万条数据的对比
批量向数据表插入一条数据（包含一个float值）
建立表个数 每个表插入数据条数 单个数据表耗时（毫秒） 数据库连接耗时（毫秒） 带数据库连接总耗时（毫秒） 不带数据库连接总耗时（毫秒） 备注
800 1440 480-600 312 437703 437391 调用Statement.execute批量插入
800 1440 100-200 328 118985 118657 调用Statement.addBatch批量插入
800 1440 40-110 313 56922 56609 调用PreparedStatement.addBatch批量插入

[Click_Me]

[Quote=引用 14 楼 tiney 的回复:]
我测试的jdbc批处理操作，插入112万条数据的对比

[/Quote]
谢谢你的测试 排版乱了.. 没太看懂 但反应 也是addBatch最快的吧 那样也就是跟我的方法一样吧?