MYSQL JDBC的批处理操作三种方式

 

SQL批处理是JDBC性能优化的重要武器，经本人研究总结，批处理的用法有三种。

 

package lavasoft.jdbctest; 

import lavasoft.common.DBToolkit; 

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

/** 
* JDBC的批量操作三种方式 
* 
* @author leizhimin 2009-12-4 14:42:11 
*/ 
public class BatchExeSQLTest { 

        public static void main(String[] args) { 
                exeBatchStaticSQL(); 
        } 

        /** 
         * 批量执行预定义模式的SQL 
         */ 
        public static void exeBatchParparedSQL() { 
                Connection conn = null; 
                try { 
                        conn = DBToolkit.getConnection(); 
                        String sql = "insert into testdb.book (kind, name) values (?,?)"; 
                        PreparedStatement pstmt = conn.prepareStatement(sql); 
                        pstmt.setString(1, "java"); 
                        pstmt.setString(2, "jjjj"); 
                        pstmt.addBatch();                     //添加一次预定义参数 
                        pstmt.setString(1, "ccc"); 
                        pstmt.setString(2, "dddd"); 
                        pstmt.addBatch();                     //再添加一次预定义参数 
                        //批量执行预定义SQL 
                        pstmt.executeBatch(); 
                } catch (SQLException e) { 
                        e.printStackTrace(); 
                } finally { 
                        DBToolkit.closeConnection(conn); 
                } 
        } 

        /** 
         * 批量执行混合模式的SQL、有预定义的，还有静态的 
         */ 
        public static void exeBatchMixedSQL() { 
                Connection conn = null; 
                try { 
                        conn = DBToolkit.getConnection(); 
                        String sql = "insert into testdb.book (kind, name) values (?,?)"; 
                        PreparedStatement pstmt = conn.prepareStatement(sql); 
                        pstmt.setString(1, "java"); 
                        pstmt.setString(2, "jjjj"); 
                        pstmt.addBatch();    //添加一次预定义参数 
                        pstmt.setString(1, "ccc"); 
                        pstmt.setString(2, "dddd"); 
                        pstmt.addBatch();    //再添加一次预定义参数 
                        //添加一次静态SQL 
                        pstmt.addBatch("update testdb.book set kind = 'JAVA' where kind='java'"); 
                        //批量执行预定义SQL 
                        pstmt.executeBatch(); 
                } catch (SQLException e) { 
                        e.printStackTrace(); 
                } finally { 
                        DBToolkit.closeConnection(conn); 
                } 
        } 

        /** 
         * 执行批量静态的SQL 
         */ 
        public static void exeBatchStaticSQL() { 
                Connection conn = null; 
                try { 
                        conn = DBToolkit.getConnection(); 
                        Statement stmt = conn.createStatement(); 
                        //连续添加多条静态SQL 
                        stmt.addBatch("insert into testdb.book (kind, name) values ('java', 'java in aciton')"); 
                        stmt.addBatch("insert into testdb.book (kind, name) values ('c', 'c in aciton')"); 
                        stmt.addBatch("delete from testdb.book where kind ='C#'"); 
                        stmt.addBatch("update testdb.book set kind = 'JAVA' where kind='java'"); 
//                        stmt.addBatch("select count(*) from testdb.book");                //批量执行不支持Select语句 
                        //执行批量执行 
                        stmt.executeBatch(); 
                } catch (SQLException e) { 
                        e.printStackTrace(); 
                } finally { 
                        DBToolkit.closeConnection(conn); 
                } 
        } 
}

 

Mysql批量插入executeBatch的性能问题  

2011-09-14 14:54:47|  分类： 技术相关|举报|字号 订阅

 

 

移动MAS短信平台用的是Mysql 4.0.20版本。虽提供有各种接口，但DB接口相信是效率最高的。开发接口程序，使用JDBC连接，prepareStatement和executeBatch批量插入数据，然而当每批量设为200条时，耗时约7秒左右。问题出在哪里？

     SmsSent.connMysql.setAutoCommit(false);
     SmsSent.logger.debug("Mysql批量执行开始");
     SmsSent.insMysql.executeBatch();
     SmsSent.logger.debug("Mysql批量插入");
     SmsSent.connSybase.setAutoCommit(false);
     SmsSent.updSybase.executeBatch();
     SmsSent.logger.debug("Sybase批量更新");
     SmsSent.connSybase.commit();
     SmsSent.logger.debug("Sybase批量更新成功。");
     SmsSent.connMysql.commit(); //

     SmsSent.logger.debug("Mysql批量更新成功。");
     SmsSent.connMysql.setAutoCommit(true);
     SmsSent.connSybase.setAutoCommit(true);
查看日志，会发现耗时主要出现在“mysql批量执行开始”和“Mysql批量插入”之间，其余操作包括Sybase的200条语句更新都是毫秒级的。查找文档，得出以下结论：

老版Mysql的JDBC驱动中对批量更新executeBatch仍是以逐条方式进行的，这一点有网友捕捉通讯报文得以证实。虽然已经使用了预编译语句，仍会与Mysql产生200次通讯交互，由于该移动服务器不在本省，ping之有30多毫秒的延迟，200X30就是将近6秒的时间，当然如此计算并不科学，但仍能反映出问题所在。

所以结论就是200条更新的时间花费在通讯开销上。

若要解决问题需使用Mysql的JDBC驱动mysql-connector-java-5.1.13以上，此时可在连接URL中加入rewriteBatchedStatements=true 来使其缓冲后批量更新以提高性能。从网友的测试结果上来看性能提高将近10倍以上。Mysql库本身在3.1.13以上支持此参数。

悲剧的是，之前自己的测试证实Mysql 4.0.20是不支持5甚至4以上版本的JDBC驱动的（官方文档中也有注明）。本人使用的是3.1.14版驱动，加入以上参数后，并未报错，然而性能未有任何提高。

Mas升级mysql版本可能性不大，那么将程序放在Mas库所在机器上倒可以解决问题。