MySql的JDBC驱动不支持批量操作(已结)

---------作者2010-9-25日补充-----------------------------------------------------------------

首先感谢javaeye的朋友，积极的参与讨论交流本帖子。

 

交流的结果：

MySql连接的url中要加rewriteBatchedStatements参数，例如

String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true"; 

还要保证mysql JDBC驱的版本。MySql的JDBC驱动的批量插入操作性能是很优秀的。

 

MySql JDBC 驱动版本	结果
5.0.8	没有提高 18秒
5.1.7	没有提高 18秒
5.1.13	有提高 1.6秒

 

具体的分析见后面srdrm 的回复。

 

 

-------------------------------------------------------------------------------------------------

事情的起因：
原贴是《使用DBCP 数据库连接池遇到的两个比较怀疑的问题》 http://www.iteye.com/topic/768416

帖子是问连接池问题的，主要是使用连接池向MySql插入大量数据的事儿，有很多javaEye的朋友让作者使用JDBC的批量操作来提高大量插入数据的性能。

我在这里重复的提醒大家：
MySql的JDBC驱动，不是真正支持批量操作的，就算你在代码中调用了批量操作的方法，MySql的JDBC驱动也是按照一般操作来处理的。


这不是什么重大发现，也不是什么新消息，是老调重弹，如果你使用Mysql数据库不要寄希望于通过JDBC批量操作来提高大量插入数据的性能，起码目前的MySql的JDBC驱动是这样的。


为什么要重新开一个帖子？因为作者的帖子是讨论连接池问题的，为了不跑题，不干扰作者，所以重新开一个帖子来说明这个问题。


----批量插入测试---

测试环境：

测试机：笔记本 Windows XP
CPU:P8600 2.4G 内存:2G
IP:192.168.10.124

数据库服务器：笔记本中运行的虚拟机Windows 2003
内存：分配了500M
IP:192.168.10.139

数据库服务器上安装有MySql5.0.18 ,Oracle10G,都是默认安装。

JDBC驱动版本：
MySql : mysql-connector-java-5.0.8
Oracle : ojdbc6.jar  ，之前使用ojdbc14.jar批量插入10万条，实际只插入了3万多条，其它的丢失了，换ojdbc6.jar后，一次commit插入100万条也OK了。

表只有两个字段：

id int

uname varchar(10)
测试方法：
见下面的代码

测试结果：

 

	mysql非批量插入10万条记录	mysql批量插入10万条记录	oracle非批量插入10万条记录	oracle批量插入10万条记录
第1次	17437 ms	17437 ms	22391 ms	360 ms
第2次	17422 ms	17562 ms	22297 ms	328 ms
第3次	17046 ms	17140 ms	22703 ms	359 ms

这里通过一个点也可以看出来oracle的完善与强大了，oracle的JDBC实现的批量操作的性能十分优秀。

 

 

下面是测试代码：

package jdbc2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Mysql {
	public static void main(String[] args) {
		test_mysql();
		//test_mysql_batch();
		//test_oracle();
		//test_oracle_batch();
	}
	/**
	 * mysql非批量插入10万条记录
	 * 第1次：17437 ms
	 * 第2次：17422 ms
	 * 第3次：17046 ms
	 */
	public static void test_mysql(){
		String url="jdbc:mysql://192.168.10.139:3306/test";
		String userName="root";
		String password="1234";
		Connection conn=null;
		try {      
		      Class.forName("com.mysql.jdbc.Driver");      
		      conn =  DriverManager.getConnection(url, userName, password);      
		      conn.setAutoCommit(false);      
		      String sql = "insert into t_user(id,uname) values(?,?)";      
		      PreparedStatement prest = conn.prepareStatement(sql);      
		      long a=System.currentTimeMillis();
		      for(int x = 0; x < 100000; x++){      
		         prest.setInt(1, x);      
		         prest.setString(2, "张三");      
		         prest.execute();
		      }      
		      conn.commit();      
		      long b=System.currentTimeMillis();
		      System.out.println("MySql非批量插入10万条记录用时"+ (b-a)+" ms");
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
	/**
	 * mysql批量插入10万条记录
	 * 第1次：17437 ms
	 * 第2次：17562 ms
	 * 第3次：17140 ms
	 */
	public static void test_mysql_batch(){
		String url="jdbc:mysql://192.168.10.139:3306/test";
		String userName="root";
		String password="1234";
		Connection conn=null;
		try {      
			Class.forName("com.mysql.jdbc.Driver");      
			conn =  DriverManager.getConnection(url, userName, password);      
			conn.setAutoCommit(false);      
			String sql = "insert into t_user(id,uname) values(?,?)";      
			PreparedStatement prest = conn.prepareStatement(sql);      
			long a=System.currentTimeMillis();
			for(int x = 0; x < 100000; x++){      
				prest.setInt(1, x);      
				prest.setString(2, "张三");      
				prest.addBatch();    
			}      
			prest.executeBatch();      
			conn.commit();      
			long b=System.currentTimeMillis();
			System.out.println("MySql批量插入10万条记录用时"+ (b-a)+" ms");
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
	/**
	 * oracle非批量插入10万条记录
	 * 第1次：22391 ms
	 * 第2次：22297 ms
	 * 第3次：22703 ms
	 */
	public static void test_oracle(){
		String url="jdbc:oracle:thin:@192.168.10.139:1521:orcl";
		String userName="scott";
		String password="tiger";	
		Connection conn=null;
		try {      
			Class.forName("oracle.jdbc.OracleDriver");      
			conn =  DriverManager.getConnection(url, userName, password);      
			conn.setAutoCommit(false);      
			String sql = "insert into t_user(id,uname) values(?,?)";      
			PreparedStatement prest = conn.prepareStatement(sql);      
			long a=System.currentTimeMillis();
			for(int x = 0; x < 100000; x++){      
				prest.setInt(1, x);      
				prest.setString(2, "张三");      
				prest.execute();
			}
			conn.commit();      
			long b=System.currentTimeMillis();
			System.out.println("Oracle非批量插入10万记录用时"+ (b-a)+" ms");
			conn.close();    
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
	/**
	 * oracle批量插入10万条记录
	 * 第1次：360 ms
	 * 第2次：328 ms
	 * 第3次：359 ms
	 */
	public static void test_oracle_batch(){
		String url="jdbc:oracle:thin:@192.168.10.139:1521:orcl";
		String userName="scott";
		String password="tiger";	
		Connection conn=null;
		try {      
			Class.forName("oracle.jdbc.OracleDriver");      
			conn =  DriverManager.getConnection(url, userName, password);      
			conn.setAutoCommit(false);      
			String sql = "insert into t_user(id,uname) values(?,?)";      
			PreparedStatement prest = conn.prepareStatement(sql);      
			long a=System.currentTimeMillis();
			for(int x = 0; x < 100000; x++){      
				prest.setInt(1, x);      
				prest.setString(2, "张三");      
				prest.addBatch();
			}
			prest.executeBatch();      
			conn.commit();      
			long b=System.currentTimeMillis();
			System.out.println("Oracle批量插入10万记录用时"+ (b-a)+" ms");
			conn.close();    
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
}

 

说一下我的测试情况，代码就是照搬LZ，只有mysql，没测oracle

台式机windows xp sp3
e8400
4g内存
完全本机测试，127.0.0.1

mysql版本5.1.50
jdbc版本-5.1.13
jdk版本1_5_0_22

每插一次前都truncate table
非批量8800-9200左右，大量出现88xx 92xx 90xx这种
批量时间会多50-100
LZ代码是10W后才executeBatch，我试过每1000或1W条executeBatch，也就是if(x%1000==0)executeBatch一下，还是没区别

http://www.iteye.com/topic/49129
\

咳咳，这个问题07年就讨论过了。~

07年那个讨论的是PreparedStatement Statement哪个快
楼主想证明的只是mysql jdbc不支持批量操作而已

翻翻后面的帖子，有各种测试。有mysql官方文档的引用和解释。

 

 

我给你举个例子，MySQL-5.1.7 JDBC驱动，一般获取的底层Connection实现为com.mysql.jdbc.JDBC4Connection，它是com.mysql.jdbc.ConnectionImpl的父类。

 

ConnectionImpl 4109行代码:

 

 

public java.sql.PreparedStatement prepareStatement(String sql,
			int resultSetType, int resultSetConcurrency) throws SQLException {
		checkClosed();

	        .....
		} else {
			pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);  // 不加设置，默认PreparedStatement实例在这里返回。
		}
		
		return pStmt;
	}

 

 其实现类为：

 

 

public class JDBC4PreparedStatement extends PreparedStatement

 

 

其Batch操作的实现：

 

 

/**
	 * JDBC 2.0 Add a set of parameters to the batch.
	 * 
	 * @exception SQLException
	 *                if a database-access error occurs.
	 * 
	 * @see StatementImpl#addBatch
	 */
	public void addBatch() throws SQLException {
		if (this.batchedArgs == null) {
			this.batchedArgs = new ArrayList();
		}

		this.batchedArgs.add(new BatchParams(this.parameterValues,
				this.parameterStreams, this.isStream, this.streamLengths,
				this.isNull));
	}

 

前面我说过，

 

 

 

 

好，接下来看一下实现BatchParams：

 

 

 

 

BatchParams(byte[][] strings, InputStream[] streams,
				boolean[] isStreamFlags, int[] lengths, boolean[] isNullFlags) {
			//
			// Make copies
			//
			this.parameterStrings = new byte[strings.length][];
			this.parameterStreams = new InputStream[streams.length];
			this.isStream = new boolean[isStreamFlags.length];
			this.streamLengths = new int[lengths.length];
			this.isNull = new boolean[isNullFlags.length];

 

 

 

然后executeBatch操作：

 

public int[] executeBatch() throws SQLException {
		checkClosed();
		
		if (this.connection.isReadOnly()) {
			throw new SQLException(Messages.getString("PreparedStatement.25") //$NON-NLS-1$
					+ Messages.getString("PreparedStatement.26"), //$NON-NLS-1$
					SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
		}

		synchronized (this.connection.getMutex()) {
			if (this.batchedArgs == null || this.batchedArgs.size() == 0) {
                return new int[0];
            }

			// we timeout the entire batch, not individual statements
			int batchTimeout = this.timeoutInMillis;
			this.timeoutInMillis = 0;
		
			resetCancelledState();
			
			try {
				clearWarnings();

				if (!this.batchHasPlainStatements
						&& this.connection.getRewriteBatchedStatements()) {
					
					
					if (canRewriteAsMultivalueInsertStatement()) {
						return executeBatchedInserts(batchTimeout);
					}
					
					if (this.connection.versionMeetsMinimum(4, 1, 0)  // MySQL > 4.1.0
							&& !this.batchHasPlainStatements
							&& this.batchedArgs != null 
							&& this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
						return executePreparedBatchAsMultiStatement(batchTimeout);
					}
				}

				return executeBatchSerially(batchTimeout);  
			} finally {
				clearBatch();
			}
		}
	}

 

 

如果你有兴趣的话，方法会调用到protected int[] executeBatchSerially(int batchTimeout) throws SQLException 方法

 

－> protected int executeUpdate(byte[][] batchedParameterStrings,

InputStream[] batchedParameterStreams, boolean[] batchedIsStream,

int[] batchedStreamLengths, boolean[] batchedIsNull, boolean isReallyBatch)

throws SQLException {

 

->  protected ResultSetInternalMethods executeInternal(int maxRowsToRetrieve,

Buffer sendPacket, boolean createStreamingResultSet,

boolean queryIsSelectOnly, Field[] metadataFromCache,

boolean isBatch)

 

- > Connectionimpl.execSQL(StatementImpl callingStatement, String sql, int maxRows,

Buffer packet, int resultSetType, int resultSetConcurrency,

boolean streamResults, String catalog,

Field[] cachedMetadata,

boolean isBatch) throws SQLException {

 

-> com.mysql.jdbc.MysqlIO.sqlQueryDirect(StatementImpl callingStatement, String query, String characterEncoding, Buffer queryPacket, int maxRows, int resultSetType, int resultSetConcurrency, boolean streamResults, String catalog, Field[] cachedMetadata) throws Exception

 

 

-> MyqlIO.private final void send(Buffer packet, int packetLen)  throws SQLException 

 

 

Buffer 会保存传出的byte数组，最终commit的时候，把数据通过IO传输。当然如果你的Buffer的长度，超过了max_allowed_packet的话，会报出一场。

 

 

你的结论中，没有错，MySQL在处理Batch的方法不是很好，包括PreparedStatement处理传输的都是整句，而不是仅为参数部分，所以结果不理想，不过Batch处理会提高IO的效率。

 

 

最后面补充一句，楼主还可以通过

DataBaseMetaData的方法来判断

boolean java.sql.DatabaseMetaData.supportsBatchUpdates() throws SQLException

你可以给基金会建议下给mysql加个批量处理数据的方法，或者做下优化。

kimmking也来了，我真是荣幸啊，呵呵。

我去看了你说的文章《PreparedStatement真的比Statement快吗？》  。

有好多朋友都是2007年以后加入javaeye的，我也是， 呵呵。