python java 速度

使用者两种语言的比较理由

由于工作关系需要压力测试往数据库中加入10000000（1000w）条的数据，使用Python 优化后的代码在插入10000（1w）条 数据要30分钟 ，后来实在没有办法想用java来提高效率。最后的结果让我太惊讶。java只需要6分钟（没有优化的） 优化后只需要不到30秒

注意地方

1 做大量插入的时候要使用批量插入，如果循环一次插入一次效率是非常地下的
2 有共享数据的时候，使用多线程真是不好处理，Java中使用锁 同步锁后效率会和 单线程的熟读差不多，只有一个执行完了才轮到其他的线程。

下面是java 和python的主要代码

package ohho;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import ohho.dao.ExtensionDao;
import ohho.dao.TokenDao;
import ohho.dao.UserDao;
import ohho.servlet.AddUser;
import ohho.servlet.AddToken;
import ohho.servlet.AddExtension;
import ohho.dao.UserDao;
public class ATestnew {
    public static void main(String[] args) throws SQLException {
        UserDao user_dao = new UserDao();
        TokenDao token_dao = new TokenDao();
        ExtensionDao extension_dao = new ExtensionDao();        
        long t1=System.currentTimeMillis();

        long number= 500000;

        List<Integer> list = new ArrayList<Integer>();
        list = user_dao.insert_user_new(number);
        System.out.println("list" + list.size());
        token_dao.insert_token_new(list);
        extension_dao.add_Extension_new(list);
        long t2=System.currentTimeMillis();
        long t3 = t2 - t1;
        double t4 = t3 / 1000 /60;
        System.out.println("直接执行时间(毫秒)" + t3);
//      System.out.println("花费时间（分钟）："+ t4);
    }
}

jdbc 连接数据库

package ohho.tools;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;





public class JdbcUntil {
//
//  public static final String url = "jdbc:mysql://localhost:3306/ohho?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true";  
//    public static final String name = "com.mysql.jdbc.Driver";  
//    public static final String user = "root";  
//    public static final String password = "111111";  



    public static Connection conn = null;  


    public  static Connection getConnection(){
     try {  
            Class.forName(name);//指定连接类型  
            conn = DriverManager.getConnection(url, user, password);//获取连接
//            System.out.println("连接了");
          return conn;
        } catch (Exception e) {  
         throw new RuntimeException("链接错误");
           // e.printStackTrace();  
        }  
   }

    public  static void close() {  
        try {  

            conn.close();  
//           System.out.println("关闭了吗");
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

    public static void main(String[] args) {
        // TODO Auto-generated method stub
       Connection c=JdbcUntil.getConnection();//接受返回的对象，connection相当于一个接口
       if(c!=null){
           System.out.println("链接数据库成功");
       }
    }

}

userdao

package ohho.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import ohho.tools.JdbcUntil;

import java.util.UUID;

import com.mysql.jdbc.Statement;
public class UserDao {
    public  int insert_user(String username,String password,String cellphone, int country_code_id){
        // 这个方法是循环一次提交一次的典型代表
        Connection conn=JdbcUntil.getConnection();
        String sql="insert into ohho_user (username,password,cellphone,country_code_id,created_at,changed_at,timestamp,state) values(?,?,?,?,?,?,?,?)";
        ResultSet rs = null; 
        int id = 0;
        try {
            PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, username);
            ps.setString(2, password);
            ps.setString(3, cellphone);
            ps.setInt(4, country_code_id);
            ps.setTimestamp(5,new Timestamp(new Date().getTime()));
            ps.setTimestamp(6,new Timestamp(new Date().getTime()));
            ps.setLong(7, new Date().getTime());
            ps.setInt(8, 1);

            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1); 
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        JdbcUntil.close();
//      System.out.println(id);
        return id;
    }

public  List<Integer> insert_user_new(long number) throws SQLException{

        UserDao user_dao = new UserDao();
        List<Integer> user_id_list = new ArrayList<Integer>();

        String password = "1411678a0b9e25ee2f7c8b2f7ac92b6a74b3f9c5";   
        int country_code_id = 159;      
        String cellphone = "12345678912";
        long cellphone_long = 1234567891 ;
        cellphone = user_dao.get_cellphone();
        if (cellphone != null){
            cellphone_long = Long.parseLong(cellphone);
        }


        Connection conn=JdbcUntil.getConnection();
        String sql="insert into ohho_user (username,password,cellphone,country_code_id,created_at,changed_at,timestamp,state) values(?,?,?,?,?,?,?,?)";
        int id = 1;
        conn.setAutoCommit(false);
        try {
            PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

            for (int i = 0; i < number; i++) {
                String uuid = UUID.randomUUID().toString().replaceAll("-", "");     
                String username = uuid;
                cellphone_long = cellphone_long + 1;

                ps.setString(1, username);
                ps.setString(2, password);
                ps.setString(3, String.valueOf(cellphone_long));
                ps.setInt(4, country_code_id);
                ps.setTimestamp(5,new Timestamp(new Date().getTime()));
                ps.setTimestamp(6,new Timestamp(new Date().getTime()));
                ps.setLong(7, new Date().getTime());
                ps.setInt(8, 1);
                ps.addBatch();
//              if (i % 1000 == 0){
//                  ps.executeBatch();
//              }

            }       
            ps.executeBatch();
            conn.commit();          
            ResultSet rs = ps.getGeneratedKeys() ;
            while (rs.next()) {
                id = rs.getInt(1);
                user_id_list.add(id);
            }

            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JdbcUntil.close();
        return user_id_list;
    }


    public  String get_cellphone(){
        Connection conn=JdbcUntil.getConnection();
        String sql="SELECT cellphone FROM ohho_user where id in (SELECT MAX(id) from ohho_user)";
        String cellphone = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                cellphone = rs.getString("cellphone");
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        JdbcUntil.close();
//      System.out.println(cellphone);
        return cellphone;

    }

    public static void main(String[] args) throws Exception {

        UserDao user = new UserDao();
        String a = user.get_cellphone();
        System.out.println(a);
    }

}

tokendao

package ohho.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import ohho.tools.JdbcUntil;

import com.mysql.jdbc.Statement;

public class TokenDao {
public  void insert_token(int user_id,String token){

        Connection conn=JdbcUntil.getConnection();
        String sql="insert into ohho_user_token (user_id,token,created_at,changed_at,timestamp) values(?,?,?,?,?)";

        try {
            PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, user_id);
            ps.setString(2, token);
            ps.setTimestamp(3,new Timestamp(new Date().getTime()));
            ps.setTimestamp(4,new Timestamp(new Date().getTime()));
            ps.setLong(5, new Date().getTime());
            ps.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        JdbcUntil.close();


    }
public void insert_token_new(List<Integer> list_user_id) throws SQLException{

    Connection conn=JdbcUntil.getConnection();
    String sql="insert into ohho_user_token (user_id,token,created_at,changed_at,timestamp) values(?,?,?,?,?)";

    conn.setAutoCommit(false);
    try {
        PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

//      System.out.println("进来大小"+list_user_id.size());
        for (int i = 0; i < list_user_id.size(); i++) {
            String token = UUID.randomUUID().toString().replaceAll("-", "");
            ps.setInt(1, list_user_id.get(i));
            ps.setString(2, token);
            ps.setTimestamp(3,new Timestamp(new Date().getTime()));
            ps.setTimestamp(4,new Timestamp(new Date().getTime()));
            ps.setLong(5, new Date().getTime());
            ps.addBatch();
//          if (i % 1000 == 0){
//              ps.executeBatch();
//          }
//          System.out.println("进来了多少次");
        }       
        ps.executeBatch();
        conn.commit();
        ps.close();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    JdbcUntil.close();

}
}

使用批量插入比每次插入的效率有60倍的差异

python

python 实现效率比较高的也是用批量插入。tornado 我使用的是orm sqlalchemy 框架

最基本的核心代码

    def add_user_commit_all(self, number=10):
        cellphone = 10000000000
        # identity_card = 100000000000000000
        user_query = self.user.get_query()
        user_query = self.user.order_by_id_desc(user_query)
        user_obj = Operation.first(user_query)
        if user_obj:
            if user_obj.cellphone:
                cellphone = int(user_obj.cellphone)
        i = 1
        dic_list_token = list()
        dic_list_extension = list()
        while i <= number:
            # OHHOLog.print_log(i)
            print(i)
            i += 1
            cellphone += 1
            data = dict()
            while self.user.get_by_cellphone(str(cellphone)):
                cellphone = cellphone + 1
            user_id = self.add_user_table(str(cellphone))
            token_dic = self.add_user_token_table_dic(user_id)
            extension_dic = self.add_user_extension_dic(user_id)
            dic_list_token.append(token_dic)
            dic_list_extension.append(extension_dic)

        self.token.token.bulk_add(dic_list_token)
        self.user_extension.bulk_add(dic_list_extension)
        print("for end")
        print("end")

 def bulk_add(self, dict_list):
        return Operation.bulk_add(self.model, dict_list)

批量实现插入

@staticmethod
    def bulk_add(model, dict_list):
        try:
            session.execute(model.__table__.insert(), dict_list)
            session.commit()
            return True
        except Exception as ex:
            OHHOLog.print_log(ex)
            return False