MySQL之com.mysql.jdbc.PreparedStatement 源码分析-jdbc批量插入性能提升利器

核心代码： com.mysql.jdbc.PreparedStatement   

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.41</version>
    <scope>runtime</scope>
</dependency>

  方法：executeBatchInternal boolean

             getRewriteBatchedStatements();

             int batchTimeout = this.timeoutInMillis;
                this.timeoutInMillis = 0;
                this.resetCancelledState();

                try {
                    this.statementBegins();
                    this.clearWarnings();
                    long[] var3;
                    if (!this.batchHasPlainStatements && this.connection.getRewriteBatchedStatements()) {
                        if (this.canRewriteAsMultiValueInsertAtSqlLevel()) {
                            var3 = this.executeBatchedInserts(batchTimeout);
                            return var3;
                        }

                        if (this.connection.versionMeetsMinimum(4, 1, 0) && !this.batchHasPlainStatements && this.batchedArgs != null && this.batchedArgs.size() > 3) {
                            var3 = this.executePreparedBatchAsMultiStatement(batchTimeout);
                            return var3;
                        }
                    }

如上图源码所示：
url参数指定rewriteBatchedStatements参数置为true, mysql驱动才会批量执行SQL (如：#jdbc.url=jdbc:mysql://localhost:3306/dtree?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true
需要注意的是：如果批量插入条数<3也会一条一条的插入，只有批量条数大于3以后才会真正的批量插入

jndi方式
<Resource auth="Container" 
driverClassName="com.mysql.jdbc.Driver"
   factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
   maxActive="20" 
   maxIdel="10" 
   maxWait="1000" 
   name="jdbc/dtree"
   username="root" 
   password="root" 
   type="javax.sql.DataSource"
   url="jdbc:mysql://localhost:3306/dtree?rewriteBatchedStatements=true" />
   <ResourceLink global="jdbc/dtree" 
   name="jdbc/dtree"
   type="javax.sql.DataSource" />

rewriteBatchedStatements设置为true后，发送一次请求，内容为”insert into t (…) values (…) , (…), (…)” 反之，则一条一条插入。以下给出一组测试数据：    
300000条数据以后    
设置true后5000条耗时1010ms   
设置true后10000条耗时1069ms
设置true后30000条耗时2610ms
设置true后40000条耗时3579ms
默认关闭时100条耗时11617ms\7155ms\11951ms\8037ms\4219ms
   

结论：通过设置rewriteBatchedStatements为true，来标识是否启用批量操作。该设置对批量插入、更新、删除都有效，如果不配置，则无效。