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

测试环境： 笔记本电脑

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. }    

package com.devplatform.module.core.dao.jdbc;
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.SQLException;  
import java.sql.Statement;

/**
   MySql 插入(insert)性能测试
   Oracle 插入(insert)性能测试

	MySql建表语句：
	CREATE  TABLE `dev`.`test_insert` (
	  `id` INT NOT NULL ,
	  `uname` VARCHAR(10) NULL ,
	  PRIMARY KEY (`id`) )
	ENGINE = InnoDB;
 */
public class JdbcInsterTest {  
	
	static int  count=100000;//总次数
	
	//一定要写rewriteBatchedStatements参数，Mysql批量插入才性能才理想
	static String mySqlUrl="jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true";
	static String mySqlUserName="root";  
	static String mySqlPassword="1234";  
	
	static String oracleurl="jdbc:oracle:thin:@192.168.10.139:1521:orcl";  
	static String oracleuserName="scott";  
	static String oraclepassword="tiger"; 
	
	static String sql = "insert into test_insert(id,uname) values(?,?)"; 
	
	//每执行几次提交一次
	static int[] commitPoint={count,10000,1000,100,10,1};
	
    public static void main(String[] args) {  
    	for(int point:commitPoint){
            test_mysql(point);  
    	}
    	for(int point:commitPoint){
            test_mysql_batch(point);  
    	}
//    	for(int point:commitPoint){
//            test_oracle(point);  
//    	}
//    	for(int point:commitPoint){
//            test_oracle_batch(point);  
//    	}
    }  
    
    /**
     * 创建连接
     * @return
     */
    public static Connection getConn(String flag){
    	long a=System.currentTimeMillis();
        try {        
        	if("mysql".equals(flag)){
                Class.forName("com.mysql.jdbc.Driver");        
                Connection conn =  DriverManager.getConnection(mySqlUrl, mySqlUserName, mySqlPassword);     
                conn.setAutoCommit(false);  
                return conn;
        	}else if("oracle".equals(flag)){
                Class.forName("oracle.jdbc.OracleDriver");        
                Connection conn =  DriverManager.getConnection(oracleurl, oracleuserName, oraclepassword); 
                conn.setAutoCommit(false);  
                return conn;
        	}else{
        		System.out.println();
        		throw new RuntimeException("flag参数不正确,flag="+flag);
        	}
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }finally{  
        	long b=System.currentTimeMillis();  
            System.out.println("创建连接用时"+ (b-a)+" ms"); 
        }
        return null;
    }
    /**
     * 关闭连接
     * @return
     */
    public static void close(Connection conn){
    	 try {  
             if(conn!=null){
            	 conn.close();  
             }
         } catch (SQLException e) {  
             e.printStackTrace();  
         }
    }
    /**
     * 删除旧数据
     * @return
     */
    public static void clear(Connection conn){
    	try{
            Statement st=conn.createStatement();
            boolean bl=st.execute("delete FROM test_insert");
            conn.commit();
            st.close();
            System.out.println("执行清理操作："+(bl==false?"成功":"失败"));
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    }
    /**
     * 打印信息
     * @return
     */
    public static void print(String key,long startTime,long endTime,int point){
    	System.out.println("每执行"+point+"次sql提交一次事务");
    	System.out.println(key+"，用时"+ (endTime-startTime)+" ms,平均每秒执行"+(count*1000/(endTime-startTime))+"条");
    	System.out.println("----------------------------------");
    }
    /** 
     * mysql非批量插入10万条记录 
     */  
    public static void test_mysql(int point){  
        Connection conn=getConn("mysql");  
        clear(conn);
        try {        
              PreparedStatement prest = conn.prepareStatement(sql);        
              long a=System.currentTimeMillis();  
              for(int x = 1; x <= count; x++){        
                 prest.setInt(1, x);        
                 prest.setString(2, "张三");        
                 prest.execute();  
                 if(x%point==0){
                	 conn.commit();
                 }
              }        
              long b=System.currentTimeMillis();  
              print("MySql非批量插入10万条记录",a,b,point);
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }finally{  
            close(conn);    
        }  
    }  
    
    /** 
     * mysql批量插入10万条记录 
     */  
    public static void test_mysql_batch(int point){  
        Connection conn=getConn("mysql");  
        clear(conn);
        try {        
            PreparedStatement prest = conn.prepareStatement(sql);        
            long a=System.currentTimeMillis();  
            for(int x = 1; x <= count; x++){        
                prest.setInt(1, x);        
                prest.setString(2, "张三");        
                prest.addBatch();    
                if(x%point==0){
                	prest.executeBatch();      
                	conn.commit();
                }
            }        
            long b=System.currentTimeMillis();  
            print("MySql批量插入10万条记录",a,b,point);
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }finally{  
            close(conn);    
        }  
    }  
    
    /** 
     * oracle非批量插入10万条记录 
     */  
    public static void test_oracle(int point){  
        Connection conn=getConn("oracle");  
        clear(conn);
        try {        
            PreparedStatement prest = conn.prepareStatement(sql);        
            long a=System.currentTimeMillis();  
            for(int x = 1; x <= count; x++){        
                prest.setInt(1, x);        
                prest.setString(2, "张三");        
                prest.execute();  
                if(x%point==0){
                	conn.commit();
                }
            }  
            long b=System.currentTimeMillis();  
            print("Oracle非批量插入10万记录",a,b,point);
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }finally{  
            close(conn);    
        }  
    }  
    /** 
     * oracle批量插入10万条记录 
     */  
    public static void test_oracle_batch(int point){  
        Connection conn=getConn("oracle");   
        clear(conn);
        try {        
            PreparedStatement prest = conn.prepareStatement(sql);        
            long a=System.currentTimeMillis();  
            for(int x = 1; x <= count; x++){        
                prest.setInt(1, x);        
                prest.setString(2, "张三");        
                prest.addBatch();  
                if(x%point==0){
                	prest.executeBatch();      
                	conn.commit();
                }
            }  
            long b=System.currentTimeMillis();  
            print("Oracle批量插入10万记录",a,b,point);
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }finally{ 
        	close(conn); 
        }  
    }  
}