Mysql数据库批处理操作调优

1.实验目标

 探索在Java通过JDBC方式连接Mysql数据库的情况下，如何最快地进行批量插入、修改操作？

2.实验步骤

 这里主要记录探索批量插入的调优过程，批量删除或删除操作类似。本实验主要包含两大步骤：1）编写实验代码2）修改数据记录条数、单条插入语句包含的数据条数、每次批处理的提交量。本次实验中用到的People数据表，仅包含1个int数据项num,实验中插入的数据无具体物理意义。

 实验代码如下所示：

	public void batchInsert(Connection conn) {
		String str = "insert into People(num) values";
		int record = 100000,lenForEvery =15;// record是数据记录条数，lenForEvery是每条插入语句包含的记录条数
		int commitLen = 5000;// 每次批处理提交的记录条数
		for(int i=0;i<lenForEvery;i++) {// 形成一个insert的sql语句
			str+="(?)";
			if(i<lenForEvery-1) str+=",";
			else str+=";";
		}
		boolean autoCommit  = false;
		try {
			autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
			PreparedStatement ps =  conn.prepareStatement(str);
			int i = 0;
			int count = 0;
			int tempRecord = (record/lenForEvery)*lenForEvery;
			while(i<tempRecord) {
				int j = 1;
				while(j<=lenForEvery&&i<tempRecord) {
					ps.setInt(j, i);
					j++;
					i++;
					count++;
				}
				ps.addBatch();
				if(count>=commitLen||i==tempRecord) {
					count = 0;
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
				}
			}
			// 将剩余不足lenForEvery的记录进行存储
			ps.close();// 将之前的PreparedStatement进行关闭
			str = "insert into People(num) values(?)";
			ps  = conn.prepareStatement(str);
			while(i<record) {
				ps.setInt(1, i++);
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
			ps.clearBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();// 注意这里有可能存在一旦出错，无法全部回滚的问题
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally {
			try {
				conn.setAutoCommit(autoCommit);
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

3.实验结果

4.实验结论

   本文中采用的是PreparedStatement语句进行，主要是因为测试的是同一种类型的操作。从以上数据我们可以得出如下结论：

1）当存在大量的插入或修改操作需求时应尽量使用批处理操作

2）当待批量操作的SQL语句是同一种类型，如插入或删除时，建议使用PreparedStatement进行批量操作；若包含多种类型，建议使用Statement语句进行批量操作。

3）当待批量操作的SQL语句是insert语句时，可考虑在同一个SQL语句插入多条记录，一般以一次插入15~20条语句最为适合。