MySql 插入(insert)性能测试 以及优化

http://blog.csdn.net/lgh1117/article/details/8619486

测试环境： 笔记本电脑

CPU：I5 

系统：MAC OS 10.7

内存：8G

硬盘：5400转，笔记本硬盘

 

MySql 版本：Oracle官网下载的mysql-5.5.24-osx10.6-x86_64， 默认安装

MySql JDBC驱动版本：mysql-connector-java-5.1.20

 

 

MySql建表语句：

CREATE  TABLE `dev`.`test_insert` (

 `id` INT NOT NULL ,

 `uname` VARCHAR(10) NULL ,

 PRIMARY KEY (`id`) )

ENGINE = InnoDB;

 

测试结果 ：

 

 

创建连接用时355 ms

执行清理操作：成功

每执行100000次sql提交一次事务

MySql非批量插入10万条记录，用时12128 ms,平均每秒执行8245条

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

创建连接用时13 ms

执行清理操作：成功

每执行10000次sql提交一次事务

MySql非批量插入10万条记录，用时11667 ms,平均每秒执行8571条

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

创建连接用时13 ms

执行清理操作：成功

每执行1000次sql提交一次事务

MySql非批量插入10万条记录，用时11744 ms,平均每秒执行8514条

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

创建连接用时12 ms

执行清理操作：成功

每执行100次sql提交一次事务

MySql非批量插入10万条记录，用时14796 ms,平均每秒执行6758条

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

创建连接用时15 ms

执行清理操作：成功

每执行10次sql提交一次事务

MySql非批量插入10万条记录，用时15917 ms,平均每秒执行6282条

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

创建连接用时14 ms

执行清理操作：成功

每执行1次sql提交一次事务

MySql非批量插入10万条记录，用时89030 ms,平均每秒执行1123条

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

创建连接用时13 ms

执行清理操作：成功

每执行100000次sql提交一次事务

MySql批量插入10万条记录，用时1298 ms,平均每秒执行77041条

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

创建连接用时16 ms

执行清理操作：成功

每执行10000次sql提交一次事务

MySql批量插入10万条记录，用时1221 ms,平均每秒执行81900条

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

创建连接用时12 ms

执行清理操作：成功

每执行1000次sql提交一次事务

MySql批量插入10万条记录，用时1418 ms,平均每秒执行70521条

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

创建连接用时15 ms

执行清理操作：成功

每执行100次sql提交一次事务

MySql批量插入10万条记录，用时2252 ms,平均每秒执行44404条

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

创建连接用时13 ms

执行清理操作：成功

每执行10次sql提交一次事务

MySql批量插入10万条记录，用时9139 ms,平均每秒执行10942条

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

创建连接用时10 ms

执行清理操作：成功

每执行1次sql提交一次事务

MySql批量插入10万条记录，用时80250 ms,平均每秒执行1246条

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

测试代码：

Java代码  

1. package com.devplatform.module.core.dao.jdbc;   
2. import java.sql.Connection;     
3. import java.sql.DriverManager;     
4. import java.sql.PreparedStatement;     
5. import java.sql.SQLException;     
6. import java.sql.Statement;   
7.   
8. /**  
9.    MySql 插入(insert)性能测试  
10.    Oracle 插入(insert)性能测试  
11.  
12.     MySql建表语句：  
13.     CREATE  TABLE `dev`.`test_insert` (  
14.       `id` INT NOT NULL ,  
15.       `uname` VARCHAR(10) NULL ,  
16.       PRIMARY KEY (`id`) )  
17.     ENGINE = InnoDB;  
18.  */  
19. public class JdbcInsterTest {     
20.        
21.     static int  count=100000;//总次数   
22.        
23.     //一定要写rewriteBatchedStatements参数，Mysql批量插入才性能才理想   
24.     static String mySqlUrl="jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true";   
25.     static String mySqlUserName="root";     
26.     static String mySqlPassword="1234";     
27.        
28.     static String oracleurl="jdbc:oracle:thin:@192.168.10.139:1521:orcl";     
29.     static String oracleuserName="scott";     
30.     static String oraclepassword="tiger";    
31.        
32.     static String sql = "insert into test_insert(id,uname) values(?,?)";    
33.        
34.     //每执行几次提交一次   
35.     static int[] commitPoint={count,10000,1000,100,10,1};   
36.        
37.     public static void main(String[] args) {     
38.         for(int point:commitPoint){   
39.             test_mysql(point);     
40.         }   
41.         for(int point:commitPoint){   
42.             test_mysql_batch(point);     
43.         }   
44. //      for(int point:commitPoint){   
45. //            test_oracle(point);     
46. //      }   
47. //      for(int point:commitPoint){   
48. //            test_oracle_batch(point);     
49. //      }   
50.     }     
51.        
52.     /**  
53.      * 创建连接  
54.      * @return  
55.      */  
56.     public static Connection getConn(String flag){   
57.         long a=System.currentTimeMillis();   
58.         try {           
59.             if("mysql".equals(flag)){   
60.                 Class.forName("com.mysql.jdbc.Driver");           
61.                 Connection conn =  DriverManager.getConnection(mySqlUrl, mySqlUserName, mySqlPassword);        
62.                 conn.setAutoCommit(false);     
63.                 return conn;   
64.             }else if("oracle".equals(flag)){   
65.                 Class.forName("oracle.jdbc.OracleDriver");           
66.                 Connection conn =  DriverManager.getConnection(oracleurl, oracleuserName, oraclepassword);    
67.                 conn.setAutoCommit(false);     
68.                 return conn;   
69.             }else{   
70.                 System.out.println();   
71.                 throw new RuntimeException("flag参数不正确,flag="+flag);   
72.             }   
73.         } catch (Exception ex) {     
74.             ex.printStackTrace();     
75.         }finally{     
76.             long b=System.currentTimeMillis();     
77.             System.out.println("创建连接用时"+ (b-a)+" ms");    
78.         }   
79.         return null;   
80.     }   
81.     /**  
82.      * 关闭连接  
83.      * @return  
84.      */  
85.     public static void close(Connection conn){   
86.          try {     
87.              if(conn!=null){   
88.                  conn.close();     
89.              }   
90.          } catch (SQLException e) {     
91.              e.printStackTrace();     
92.          }   
93.     }   
94.     /**  
95.      * 删除旧数据  
96.      * @return  
97.      */  
98.     public static void clear(Connection conn){   
99.         try{   
100.             Statement st=conn.createStatement();   
101.             boolean bl=st.execute("delete FROM test_insert");   
102.             conn.commit();   
103.             st.close();   
104.             System.out.println("执行清理操作："+(bl==false?"成功":"失败"));   
105.         }catch(Exception e){   
106.             e.printStackTrace();   
107.         }   
108.     }   
109.     /**  
110.      * 打印信息  
111.      * @return  
112.      */  
113.     public static void print(String key,long startTime,long endTime,int point){   
114.         System.out.println("每执行"+point+"次sql提交一次事务");   
115.         System.out.println(key+"，用时"+ (endTime-startTime)+" ms,平均每秒执行"+(count*1000/(endTime-startTime))+"条");   
116.         System.out.println("----------------------------------");   
117.     }   
118.     /**   
119.      * mysql非批量插入10万条记录   
120.      */     
121.     public static void test_mysql(int point){     
122.         Connection conn=getConn("mysql");     
123.         clear(conn);   
124.         try {           
125.               PreparedStatement prest = conn.prepareStatement(sql);           
126.               long a=System.currentTimeMillis();     
127.               for(int x = 1; x <= count; x++){           
128.                  prest.setInt(1, x);           
129.                  prest.setString(2, "张三");           
130.                  prest.execute();     
131.                  if(x%point==0){   
132.                      conn.commit();   
133.                  }   
134.               }           
135.               long b=System.currentTimeMillis();     
136.               print("MySql非批量插入10万条记录",a,b,point);   
137.         } catch (Exception ex) {     
138.             ex.printStackTrace();     
139.         }finally{     
140.             close(conn);       
141.         }     
142.     }     
143.        
144.     /**   
145.      * mysql批量插入10万条记录   
146.      */     
147.     public static void test_mysql_batch(int point){     
148.         Connection conn=getConn("mysql");     
149.         clear(conn);   
150.         try {           
151.             PreparedStatement prest = conn.prepareStatement(sql);           
152.             long a=System.currentTimeMillis();     
153.             for(int x = 1; x <= count; x++){           
154.                 prest.setInt(1, x);           
155.                 prest.setString(2, "张三");           
156.                 prest.addBatch();       
157.                 if(x%point==0){   
158.                     prest.executeBatch();         
159.                     conn.commit();   
160.                 }   
161.             }           
162.             long b=System.currentTimeMillis();     
163.             print("MySql批量插入10万条记录",a,b,point);   
164.         } catch (Exception ex) {     
165.             ex.printStackTrace();     
166.         }finally{     
167.             close(conn);       
168.         }     
169.     }     
170.        
171.     /**   
172.      * oracle非批量插入10万条记录   
173.      */     
174.     public static void test_oracle(int point){     
175.         Connection conn=getConn("oracle");     
176.         clear(conn);   
177.         try {           
178.             PreparedStatement prest = conn.prepareStatement(sql);           
179.             long a=System.currentTimeMillis();     
180.             for(int x = 1; x <= count; x++){           
181.                 prest.setInt(1, x);           
182.                 prest.setString(2, "张三");           
183.                 prest.execute();     
184.                 if(x%point==0){   
185.                     conn.commit();   
186.                 }   
187.             }     
188.             long b=System.currentTimeMillis();     
189.             print("Oracle非批量插入10万记录",a,b,point);   
190.         } catch (Exception ex) {     
191.             ex.printStackTrace();     
192.         }finally{     
193.             close(conn);       
194.         }     
195.     }     
196.     /**   
197.      * oracle批量插入10万条记录   
198.      */     
199.     public static void test_oracle_batch(int point){     
200.         Connection conn=getConn("oracle");      
201.         clear(conn);   
202.         try {           
203.             PreparedStatement prest = conn.prepareStatement(sql);           
204.             long a=System.currentTimeMillis();     
205.             for(int x = 1; x <= count; x++){           
206.                 prest.setInt(1, x);           
207.                 prest.setString(2, "张三");           
208.                 prest.addBatch();     
209.                 if(x%point==0){   
210.                     prest.executeBatch();         
211.                     conn.commit();   
212.                 }   
213.             }     
214.             long b=System.currentTimeMillis();     
215.             print("Oracle批量插入10万记录",a,b,point);   
216.         } catch (Exception ex) {     
217.             ex.printStackTrace();     
218.         }finally{    
219.             close(conn);    
220.         }     
221.     }     
222. }    

[java] view plain copy

1. package com.devplatform.module.core.dao.jdbc;  
2. import java.sql.Connection;    
3. import java.sql.DriverManager;    
4. import java.sql.PreparedStatement;    
5. import java.sql.SQLException;    
6. import java.sql.Statement;  
7.   
8. /** 
9.    MySql 插入(insert)性能测试 
10.    Oracle 插入(insert)性能测试 
11.  
12.     MySql建表语句： 
13.     CREATE  TABLE `dev`.`test_insert` ( 
14.       `id` INT NOT NULL , 
15.       `uname` VARCHAR(10) NULL , 
16.       PRIMARY KEY (`id`) ) 
17.     ENGINE = InnoDB; 
18.  */  
19. public class JdbcInsterTest {    
20.       
21.     static int  count=100000;//总次数  
22.       
23.     //一定要写rewriteBatchedStatements参数，Mysql批量插入才性能才理想  
24.     static String mySqlUrl="jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true";  
25.     static String mySqlUserName="root";    
26.     static String mySqlPassword="1234";    
27.       
28.     static String oracleurl="jdbc:oracle:thin:@192.168.10.139:1521:orcl";    
29.     static String oracleuserName="scott";    
30.     static String oraclepassword="tiger";   
31.       
32.     static String sql = "insert into test_insert(id,uname) values(?,?)";   
33.       
34.     //每执行几次提交一次  
35.     static int[] commitPoint={count,10000,1000,100,10,1};  
36.       
37.     public static void main(String[] args) {    
38.         for(int point:commitPoint){  
39.             test_mysql(point);    
40.         }  
41.         for(int point:commitPoint){  
42.             test_mysql_batch(point);    
43.         }  
44. //      for(int point:commitPoint){  
45. //            test_oracle(point);    
46. //      }  
47. //      for(int point:commitPoint){  
48. //            test_oracle_batch(point);    
49. //      }  
50.     }    
51.       
52.     /** 
53.      * 创建连接 
54.      * @return 
55.      */  
56.     public static Connection getConn(String flag){  
57.         long a=System.currentTimeMillis();  
58.         try {          
59.             if("mysql".equals(flag)){  
60.                 Class.forName("com.mysql.jdbc.Driver");          
61.                 Connection conn =  DriverManager.getConnection(mySqlUrl, mySqlUserName, mySqlPassword);       
62.                 conn.setAutoCommit(false);    
63.                 return conn;  
64.             }else if("oracle".equals(flag)){  
65.                 Class.forName("oracle.jdbc.OracleDriver");          
66.                 Connection conn =  DriverManager.getConnection(oracleurl, oracleuserName, oraclepassword);   
67.                 conn.setAutoCommit(false);    
68.                 return conn;  
69.             }else{  
70.                 System.out.println();  
71.                 throw new RuntimeException("flag参数不正确,flag="+flag);  
72.             }  
73.         } catch (Exception ex) {    
74.             ex.printStackTrace();    
75.         }finally{    
76.             long b=System.currentTimeMillis();    
77.             System.out.println("创建连接用时"+ (b-a)+" ms");   
78.         }  
79.         return null;  
80.     }  
81.     /** 
82.      * 关闭连接 
83.      * @return 
84.      */  
85.     public static void close(Connection conn){  
86.          try {    
87.              if(conn!=null){  
88.                  conn.close();    
89.              }  
90.          } catch (SQLException e) {    
91.              e.printStackTrace();    
92.          }  
93.     }  
94.     /** 
95.      * 删除旧数据 
96.      * @return 
97.      */  
98.     public static void clear(Connection conn){  
99.         try{  
100.             Statement st=conn.createStatement();  
101.             boolean bl=st.execute("delete FROM test_insert");  
102.             conn.commit();  
103.             st.close();  
104.             System.out.println("执行清理操作："+(bl==false?"成功":"失败"));  
105.         }catch(Exception e){  
106.             e.printStackTrace();  
107.         }  
108.     }  
109.     /** 
110.      * 打印信息 
111.      * @return 
112.      */  
113.     public static void print(String key,long startTime,long endTime,int point){  
114.         System.out.println("每执行"+point+"次sql提交一次事务");  
115.         System.out.println(key+"，用时"+ (endTime-startTime)+" ms,平均每秒执行"+(count*1000/(endTime-startTime))+"条");  
116.         System.out.println("----------------------------------");  
117.     }  
118.     /**  
119.      * mysql非批量插入10万条记录  
120.      */    
121.     public static void test_mysql(int point){    
122.         Connection conn=getConn("mysql");    
123.         clear(conn);  
124.         try {          
125.               PreparedStatement prest = conn.prepareStatement(sql);          
126.               long a=System.currentTimeMillis();    
127.               for(int x = 1; x <= count; x++){          
128.                  prest.setInt(1, x);          
129.                  prest.setString(2, "张三");          
130.                  prest.execute();    
131.                  if(x%point==0){  
132.                      conn.commit();  
133.                  }  
134.               }          
135.               long b=System.currentTimeMillis();    
136.               print("MySql非批量插入10万条记录",a,b,point);  
137.         } catch (Exception ex) {    
138.             ex.printStackTrace();    
139.         }finally{    
140.             close(conn);      
141.         }    
142.     }    
143.       
144.     /**  
145.      * mysql批量插入10万条记录  
146.      */    
147.     public static void test_mysql_batch(int point){    
148.         Connection conn=getConn("mysql");    
149.         clear(conn);  
150.         try {          
151.             PreparedStatement prest = conn.prepareStatement(sql);          
152.             long a=System.currentTimeMillis();    
153.             for(int x = 1; x <= count; x++){          
154.                 prest.setInt(1, x);          
155.                 prest.setString(2, "张三");          
156.                 prest.addBatch();      
157.                 if(x%point==0){  
158.                     prest.executeBatch();        
159.                     conn.commit();  
160.                 }  
161.             }          
162.             long b=System.currentTimeMillis();    
163.             print("MySql批量插入10万条记录",a,b,point);  
164.         } catch (Exception ex) {    
165.             ex.printStackTrace();    
166.         }finally{    
167.             close(conn);      
168.         }    
169.     }    
170.       
171.     /**  
172.      * oracle非批量插入10万条记录  
173.      */    
174.     public static void test_oracle(int point){    
175.         Connection conn=getConn("oracle");    
176.         clear(conn);  
177.         try {          
178.             PreparedStatement prest = conn.prepareStatement(sql);          
179.             long a=System.currentTimeMillis();    
180.             for(int x = 1; x <= count; x++){          
181.                 prest.setInt(1, x);          
182.                 prest.setString(2, "张三");          
183.                 prest.execute();    
184.                 if(x%point==0){  
185.                     conn.commit();  
186.                 }  
187.             }    
188.             long b=System.currentTimeMillis();    
189.             print("Oracle非批量插入10万记录",a,b,point);  
190.         } catch (Exception ex) {    
191.             ex.printStackTrace();    
192.         }finally{    
193.             close(conn);      
194.         }    
195.     }    
196.     /**  
197.      * oracle批量插入10万条记录  
198.      */    
199.     public static void test_oracle_batch(int point){    
200.         Connection conn=getConn("oracle");     
201.         clear(conn);  
202.         try {          
203.             PreparedStatement prest = conn.prepareStatement(sql);          
204.             long a=System.currentTimeMillis();    
205.             for(int x = 1; x <= count; x++){          
206.                 prest.setInt(1, x);          
207.                 prest.setString(2, "张三");          
208.                 prest.addBatch();    
209.                 if(x%point==0){  
210.                     prest.executeBatch();        
211.                     conn.commit();  
212.                 }  
213.             }    
214.             long b=System.currentTimeMillis();    
215.             print("Oracle批量插入10万记录",a,b,point);  
216.         } catch (Exception ex) {    
217.             ex.printStackTrace();    
218.         }finally{   
219.             close(conn);   
220.         }    
221.     }    
222. }