mysql中批量插入数据(1万、10万、100万、1000万、1亿级别) - 主机乐

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

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);
   }