mysql中关于批量插入数据（1万、10万、100万、1000万、1亿级别的数据）

亦凉 2022-03-29 01:43 725阅读 0赞

硬件：windows7+8G内存+i3-4170处理器+4核CPU  
首先贴上数据库的操作类BaseDao：  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.util.List;  
   
import com.lk.entity.TUser;  
   
public class BaseDao \{  
    private static ConfigManager cm = ConfigManager.getInstance();  
   
    private static String Driver = null;  
    private static String URL = null;  
    private static String USER = null;  
    private static String PWD = null;  
   
    private static Connection conn = null;  
    private static PreparedStatement psmt = null;  
    public ResultSet rs = null;  
    public int row = 0;  
   
    static \{  
        Driver = cm.getString("DRIVER");  
        URL = cm.getString("URL");  
        USER = cm.getString("USER");  
        PWD = cm.getString("PWD");  
        try \{  
            Class.forName(Driver);  
        \} catch (ClassNotFoundException e) \{  
            e.printStackTrace();  
        \}  
        getConnection();  
        try \{  
            conn.setAutoCommit(false);  
            psmt = conn.prepareStatement("");  
        \} catch (SQLException e) \{  
            e.printStackTrace();  
        \}  
    \}  
   
    private static Connection getConnection() \{  
        try \{  
            conn = DriverManager.getConnection(URL, USER, PWD);  
        \} catch (SQLException e) \{  
            e.printStackTrace();  
        \}  
        return conn;  
    \}  
   
    /\*\*  
     \* 多条记录插入操作  
     \* flag是为了判断是否是插入的最后一个记录  
     \*/  
    public boolean affectRowMore(String sql, List<TUser> list, long flag) \{  
        try \{  
            psmt = conn.prepareStatement(sql);  
            for (TUser tUser : list) \{  
                psmt.setLong(1, tUser.getId());  
                psmt.setString(2, tUser.getName());  
                psmt.setInt(3, tUser.getSex());  
                psmt.setString(4, tUser.getPhone());  
                psmt.setString(5, tUser.getPassword());  
                // 添加执行sql  
                psmt.addBatch();  
            \}  
            // 执行操作  
            int\[\] counts = psmt.executeBatch(); // 执行Batch中的全部语句  
            conn.commit(); // 提交到数据库  
            for (int i : counts) \{  
                if (i == 0) \{  
                    conn.rollback();  
                \}  
            \}  
            closeAll(flag);  
        \} catch (SQLException e) \{  
            e.printStackTrace();  
            return false;  
        \}  
        return true;  
    \}  
   
    /\*\*  
     \* 多条记录插入操作  
     \*/  
    public boolean affectRowMore1(String sql, long flag) \{  
        try \{  
            psmt.addBatch(sql);  
            // 执行操作  
            int\[\] counts = psmt.executeBatch(); // 执行Batch中的全部语句  
            conn.commit(); // 提交到数据库  
            for (int i : counts) \{  
                if (i == 0) \{  
                    conn.rollback();  
                    return false;  
                \}  
            \}  
            closeAll(flag);  
        \} catch (SQLException e) \{  
            e.printStackTrace();  
            return false;  
        \}  
        return true;  
    \}  
   
    public void closeAll(long flag) \{  
        try \{  
            if (conn != null && flag == -1) \{  
                // 在完成批量操作后恢复默认的自动提交方式，提高程序的可扩展性  
                conn.setAutoCommit(true);  
                conn.close();  
            \}  
            if (psmt != null && flag == -1) \{  
                psmt.close();  
            \}  
            if (rs != null) \{  
                rs.close();  
            \}  
        \} catch (SQLException e) \{  
            e.printStackTrace();  
        \}  
    \}  
\}  
方法一:  
通过BaseDao中的affectRowMore方法进行插入，插入的速度如下所示：

\* 一万条数据(通过多条添加)  
     \* 生成1万条数据共花费978毫秒  
     \* 生成10万条数据共花费5826毫秒  
     \* 生成100万条数据共花费54929毫秒  
     \* 生成1000万条数据共花费548640毫秒  
     \* 生成1亿条数据(因为数字过大，没有计算)

public void insertBenchMark() \{  
        long start = System.currentTimeMillis();  
        List<TUser> list = new ArrayList<>();  
        long row = 1;  
        for (int j = 0; j < 1000; j++) \{  
            for (int i = 0; i < 10000; i++) \{  
                String uuid = UUID.randomUUID().toString();  
                String name = uuid.substring(0, 4);  
                int sex = -1;  
                if(Math.random() < 0.51) \{  
                    sex = 1;  
                \}else \{  
                    sex = 0;  
                \}  
                String phone = (String) RandomValue.getAddress().get("tel");  
                list.add(new TUser(row,name, sex, phone, uuid));  
                row++;  
            \}  
            int flag = 1;  
            if(j==999) \{  
                flag = -1;  
            \}  
            //封装好的  
            boolean b = userDao.insertMore(list,flag);  
            if(!b) \{  
                System.out.println("出错了----");  
                System.exit(0);  
            \}else \{  
                list.clear();  
            \}  
        \}  
        long end = System.currentTimeMillis();  
        System.out.println("生成1000万条数据共花费"+(end-start)+"毫秒");  
    \}  
public boolean insertMore(List<TUser> list,long flag) \{  
        String sql = "insert into tuser(id,name,sex,phone,password) values(?,?,?,?,?)";  
        return affectRowMore(sql,list,flag);  
    \}  
方法二：  
通过BaseDao中的affectRowMore1方法进行数据的插入操作，插入的速度如下：

\* 通过拼接语句实现多条添加  
     \* 生成1万条数据共花费225毫秒  
     \* 生成10万条数据共花费1586毫秒  
     \* 生成100万条数据共花费14017毫秒  
     \* 生成1000万条数据共花费152127毫秒  
     \* 生成1亿条数据(因为数字过大，没有计算)

public void insertBenchMark1() \{  
        long start = System.currentTimeMillis();  
        StringBuffer suffix = new StringBuffer();  
        long row = 1;  
        for (int j = 0; j < 1000; j++) \{  
            for (int i = 0; i < 10000; i++) \{  
                String uuid = UUID.randomUUID().toString();  
                String name = uuid.substring(0, 4);  
                int sex = -1;  
                if(Math.random() < 0.51) \{  
                    sex = 1;  
                \}else \{  
                    sex = 0;  
                \}  
                String phone = (String) RandomValue.getAddress().get("tel");  
                suffix.append("(" + row + ",'" + name + "'," + sex + ",'" + phone + "','" + uuid + "'),");  
                row++;  
            \}  
            boolean b = userDao.insertMore1(suffix.substring(0, suffix.length()-1),j);  
            if(!b) \{  
                System.out.println("出错了----");  
                System.exit(0);  
            \}else \{  
                // 清空上一次添加的数据  
                suffix = new StringBuffer();  
            \}  
        \}  
        long end = System.currentTimeMillis();  
        System.out.println("生成1000万条数据共花费"+(end-start)+"毫秒");  
    \}  
public boolean insertMore1(String sql\_suffix,long flag) \{  
        String sql\_prefix = "insert into tuser(id,name,sex,phone,password) values ";  
        return affectRowMore1(sql\_prefix + sql\_suffix,flag);  
    \}  
总结：  
方法一和方法二很类同，唯一不同的是方法一采用的是“insert into tb (...) values (...);insert into tb (...) values (...);...”的方式执行插入操作，方法二则是“insert into tb (...) values(...),(...)...;”的方式。