NutzCN Logo
精华 druid 数据源 批量插入 fastInsert 慢
发布于 3018天前 作者 Rekoe 8698 次浏览 复制 上一个帖子 下一个帖子
标签: dao

pojo

@Table("test_insert")
public class TestPojo {

	@Name
	private String id;

	@Column
	private String name;

	@Column
	private String nickName;

	@Column
	private String uname;

	public TestPojo() {
		super();
	}

	public TestPojo(String id) {
		super();
		this.id = id;
		this.name = id;
		this.nickName = id;
		this.uname = id;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getNickName() {
		return nickName;
	}

	public void setNickName(String nickName) {
		this.nickName = nickName;
	}

	public String getUname() {
		return uname;
	}

	public void setUname(String uname) {
		this.uname = uname;
	}

}

测试代码

		dao.create(TestPojo.class, false);
		for (int i = 0; i < 2000; i++) {
			List<TestPojo> add = new ArrayList<TestPojo>();
			for (int j = 0; j < 500; j++) {
				add.add(new TestPojo(R.UU32()));
			}
			long time = System.currentTimeMillis();
			dao.fastInsert(add);
			System.out.println(i + " time = " + (System.currentTimeMillis() - time));
			add.clear();
		}
9 回复

我的测试结果, 批量插入时,大部分时间在等数据库

untitled1_png

debug 了一下
主要的时间消耗在执行

int[] counts = pstat.executeBatch();

基本上认定是auto commit导致的问题

jdbc autocommit=true 慢
nutz 无事务fastInsert 慢
jdbc autocommit=false 快
nutz 有事务fastInsert 快

连接加入 rewriteBatchedStatements=TRUE 有没更快点!!

嗯, 坐等帖主验证效果

rewriteBatchedStatements 好像只影响发送数据的性能吗?

关于rewriteBatchedStatements参数,Mysql官方的说明:

Should the driver use multiqueries (irregardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called? Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly. Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don't specify stream lengths when using PreparedStatement.set*Stream(), the driver won't be able to determine the optimum number of parameters per batch and you might receive an error from the driver that the resultant packet is too large. Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements.

查到的资料是 useServerPrepStmts=false&rewriteBatchedStatements=true 这配置是最高的,,但是一直没数据,没有测试过!

添加回复
请先登陆
回到顶部