几种Mysql数据传输方法效率的比较

;
//创建文件传输类 Statement stmt = null;
//创建文件回收类 ResultSet rs = null; try{ //调用Class类的方法创建驱动 Class.forName("com.mysql.jdbc.Driver"); //连接地址包括：IP,MySQL的账号，密码 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Newdatabase", "1234", "1234"); stmt = conn.createStatement(); String sql = "select * from account1";
//执行语言命令 rs = stmt.executeQuery(sql); }catch(Exception ex){ ex.printStackTrace(); }finally{ if(rs!=null){ try{ rs.close(); }catch(Exception ex){ ex.printStackTrace(); } } if(stmt!=null){ try{ stmt.close(); }catch(Exception ex){ ex.printStackTrace(); } } if(conn!=null){ try{ conn.close(); }catch(Exception ex){ ex.printStackTrace(); } } }

下面比较几种传输数据的方法，通过测定传输10000条数据所需的时间，来比较效率。

1. 直接用for循环10000次，该方法耗时634592毫秒

Date d1 = new Date();
...
for(int i =1; i<=10000; i++){
    sql = "insert into test1(others,list) value(null,999999)";
    stmt.executeUpdate(sql);
}
Date d2 = new Date();
System.out.println(d2.getTime()-d1.getTime());

2. 取消自动传输语言命令，调用Connection的commit（）方法，即手动提交事务。该方法耗时5537毫秒

Date d1 = new Date();
...
conn.setAutoCommit(false);
...
for(int i =1; i<=10000; i++){
       sql = "insert into test1(others,list) value(null,999999)";
       stmt.executeUpdate(sql);
}
conn.commit();
Date d2 = new Date();
System.out.println(d2.getTime()-d1.getTime());

3. 通过创建多个Statement，同时传输。该方法耗时646904毫秒

        //创建5个Statement

        Statement stmt1=null;
        Statement stmt2=null;
        Statement stmt3=null;
        Statement stmt4=null;
        Statement stmt5=null;
        try{
            Date d1 = new Date();
            ...
            stmt1 = conn.createStatement();
            stmt2 = conn.createStatement();
            stmt3 = conn.createStatement();
            stmt4 = conn.createStatement();
            stmt5 = conn.createStatement();
            String sql = null;
            for(int i =1; i<=2000; i++){
                sql = "insert into test1(others,list) value(null,999999)";
                stmt1.executeUpdate(sql);
                stmt2.executeUpdate(sql);
                stmt3.executeUpdate(sql);
                stmt4.executeUpdate(sql);
                stmt5.executeUpdate(sql);
            }
            Date d2 = new Date();
            System.out.println(d2.getTime()-d1.getTime());
        }

4. 通过创建多线程传输数据。该方法耗时239143毫秒

   public void method4(){
        Date d1 = new Date();
        Megadata M1 = new Megadata();
        Megadata M2 = new Megadata();
        Megadata M3 = new Megadata();
        Megadata M4 = new Megadata();
        Megadata M5 = new Megadata();
        M1.start();
        M2.start();
        M3.start();
        M4.start();
        M5.start();
        try{
            M1.join();
            M2.join();
            M3.join();
            M4.join();
            M5.join();
        }catch(Exception e){
            e.printStackTrace();
        }
        Date d2 = new Date();
        System.out.println(d2.getTime()-d1.getTime());
    }
    
    public void run(){
        try{
            ...

            for(int i=0;i<2000;i++){
                sql = "insert into test1(others,list) value(null,999999)";
                stmt.executeUpdate(sql);
            }
            
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            if(stmt!=null){
                try{
                    stmt.close();
                }catch(Exception ex){
                    ex.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception ex){
                    ex.printStackTrace();
                }
            }
        }
        
    }

当然方法还有其他，从这四种可以看出手动提交事务更高效，然而这却是一个问题。。。

几种Mysql数据传输方法效率的比较

标签：com   acl   drive   count   final   resultset   文件   数据库   循环