学步园

--------------------------------结论---------------------------------

MySql   非批量10万条记录，                5700条/秒

MySql   批量(batch)10万条记录，       62500条/秒

oracle   非批量插入10万条记录，            4464条/秒

oracle   批量(batch)插入10万条记录，   27778条/秒

注：以上测试都是在插入10万条数据完成之后，一次性提交事务(对性能影响很大，占了很大便宜)。

另有一篇文章，说明提交事务的次数对insert性能的影响：《MySql 插入(insert)性能测试》 

MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。

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

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

Mysql JDBC驱动，各个版本测试结果：

关于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.

请参见：http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

-------------------------事情的起因---------------------------------

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

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

我在这里重复的提醒大家：

MySql的JDBC驱动，不是真正支持批量操作的，就算你在代码中调用了批量操作的方法，MySql的JDBC驱动也是按照一般操作来处理的。
这不是什么重大发现，也不是什么新消息，是老调重弹，如果你使用Mysql数据库不要寄希望于通过JDBC批量操作来提高大量插入数据的性能，起码目前的MySql的JDBC驱动是这样的。

---------------------------------测试环境---------------------------------

测试机：笔记本电脑ThinkPad T400

操作系统：Windows XP 

CPU:P8600 2.4G

内存:2G 

IP:192.168.10.124

数据库服务器：笔记本ThinkPad T400

操作系统：虚拟机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万条也没有问题)

表只有两个字段：

id int

uname varchar(10)

innoDB引擎

---------------------------------测试结果：---------------------------------

这里通过一个点也可以看出来Mysql批量与非批量性能是一样。

oracle的JDBC实现的批量操作的性能十分优秀。

---------------------------------测试方法：---------------------------------

下面是测试代码：

Java代码  

1. package jdbc2;  
2. import java.sql.Connection;  
3. import java.sql.DriverManager;  
4. import java.sql.PreparedStatement;  
5. import java.sql.SQLException;  
6.   
7. public class Mysql {  
8.     public static void main(String[] args) {  
9.         test_mysql();  
10.         //test_mysql_batch();  
11.         //test_oracle();  
12.         //test_oracle_batch();  
13.     }  
14.     /** 
15.      * mysql非批量插入10万条记录 
16.      * 第1次：17437 ms 
17.      * 第2次：17422 ms 
18.      * 第3次：17046 ms 
19.      */  
20.     public static void test_mysql(){  
21.         String url="jdbc:mysql://192.168.10.139:3306/test";  
22.         String userName="root";  
23.         String password="1234";  
24.         Connection conn=null;  
25.         try {        
26.               Class.forName("com.mysql.jdbc.Driver");        
27.               conn =  DriverManager.getConnection(url, userName, password);        
28.               conn.setAutoCommit(false);        
29.               String sql = "insert into t_user(id,uname) values(?,?)";        
30.               PreparedStatement prest = conn.prepareStatement(sql);        
31.               long a=System.currentTimeMillis();  
32.               for(int x = 0; x < 100000; x++){        
33.                  prest.setInt(1, x);        
34.                  prest.setString(2, "张三");        
35.                  prest.execute();  
36.               }        
37.               conn.commit();        
38.               long b=System.currentTimeMillis();  
39.               System.out.println("MySql非批量插入10万条记录用时"+ (b-a)+" ms");  
40.         } catch (Exception ex) {  
41.             ex.printStackTrace();  
42.         }finally{  
43.             try {  
44.                 if(conn!=null)conn.close();  
45.             } catch (SQLException e) {  
46.                 e.printStackTrace();  
47.             }     
48.         }  
49.     }  
50.     /** 
51.      * mysql批量插入10万条记录(未加rewriteBatchedStatements参数)   ，加了参数后是1600ms左右 
52.      * 第1次：17437 ms 
53.      * 第2次：17562 ms 
54.      * 第3次：17140 ms 
55.      */  
56.     public static void test_mysql_batch(){  
57.         //String url="jdbc:mysql://192.168.10.139:3306/test";  
58. <p style="margin: 0.0px 0.0px 0.0px 0.0px; color: #3b3df5;"><span style="font-family: Verdana,Arial,Helvetica,sans-serif; color: #000000;">     String url="jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true";</span>  
59.   
60.   
61. </p>  
62.   
63.   
64.         String userName="root";  
65.         String password="1234";  
66.         Connection conn=null;  
67.         try {        
68.             Class.forName("com.mysql.jdbc.Driver");        
69.             conn =  DriverManager.getConnection(url, userName, password);        
70.             conn.setAutoCommit(false);        
71.             String sql = "insert into t_user(id,uname) values(?,?)";        
72.             PreparedStatement prest = conn.prepareStatement(sql);        
73.             long a=System.currentTimeMillis();  
74.             for(int x = 0; x < 100000; x++){        
75.                 prest.setInt(1, x);        
76.                 prest.setString(2, "张三");        
77.                 prest.addBatch();      
78.             }        
79.             prest.executeBatch();        
80.             conn.commit();        
81.             long b=System.currentTimeMillis();  
82.             System.out.println("MySql批量插入10万条记录用时"+ (b-a)+" ms");  
83.         } catch (Exception ex) {  
84.             ex.printStackTrace();  
85.         }finally{  
86.             try {  
87.                 if(conn!=null)conn.close();  
88.             } catch (SQLException e) {  
89.                 e.printStackTrace();  
90.             }     
91.         }  
92.     }  
93.     /** 
94.      * oracle非批量插入10万条记录 
95.      * 第1次：22391 ms 
96.      * 第2次：22297 ms 
97.      * 第3次：22703 ms 
98.      */  
99.     public static void test_oracle(){  
100.         String url="jdbc:oracle:thin:@192.168.10.139:1521:orcl";  
101.         String userName="scott";  
102.         String password="tiger";      
103.         Connection conn=null;  
104.         try {        
105.             Class.forName("oracle.jdbc.OracleDriver");        
106.             conn =  DriverManager.getConnection(url, userName, password);        
107.             conn.setAutoCommit(false);        
108.             String sql = "insert into t_user(id,uname) values(?,?)";        
109.             PreparedStatement prest = conn.prepareStatement(sql);        
110.             long a=System.currentTimeMillis();  
111.             for(int x = 0; x < 100000; x++){        
112.                 prest.setInt(1, x);        
113.                 prest.setString(2, "张三");        
114.                 prest.execute();  
115.             }  
116.             conn.commit();        
117.             long b=System.currentTimeMillis();  
118.             System.out.println("Oracle非批量插入10万记录用时"+ (b-a)+" ms");  
119.             conn.close();      
120.         } catch (Exception ex) {  
121.             ex.printStackTrace();  
122.         }finally{  
123.             try {  
124.                 if(conn!=null)conn.close();  
125.             } catch (SQLException e) {  
126.                 e.printStackTrace();  
127.             }     
128.         }  
129.     }  
130.     /** 
131.      * oracle批量插入10万条记录 
132.      * 第1次：360 ms 
133.      * 第2次：328 ms 
134.      * 第3次：359 ms 
135.      */  
136.     public static void test_oracle_batch(){  
137.         String url="jdbc:oracle:thin:@192.168.10.139:1521:orcl";  
138.         String userName="scott";  
139.         String password="tiger";      
140.         Connection conn=null;  
141.         try {        
142.             Class.forName("oracle.jdbc.OracleDriver");        
143.             conn =  DriverManager.getConnection(url, userName, password);        
144.             conn.setAutoCommit(false);        
145.             String sql = "insert into t_user(id,uname) values(?,?)";        
146.             PreparedStatement prest = conn.prepareStatement(sql);        
147.             long a=System.currentTimeMillis();  
148.             for(int x = 0; x < 100000; x++){        
149.                 prest.setInt(1, x);        
150.                 prest.setString(2, "张三");        
151.                 prest.addBatch();  
152.             }  
153.             prest.executeBatch();        
154.             conn.commit();        
155.             long b=System.currentTimeMillis();  
156.             System.out.println("Oracle批量插入10万记录用时"+ (b-a)+" ms");  
157.             conn.close();      
158.         } catch (Exception ex) {  
159.             ex.printStackTrace();  
160.         }finally{  
161.             try {  
162.                 if(conn!=null)conn.close();  
163.             } catch (SQLException e) {  
164.                 e.printStackTrace();  
165.             }     
166.         }  
167.     }  
168. }