一、写在开头
我们在上一篇文章中提到了通过EasyExcel处理Mysql百万数据的导入功能，当时我们经过测试数据的反复测验，100万条放在excel中的数据，4个字段的情况下，导入数据库，平均耗时500秒，这对于我们来说肯定难以接受，今天我们就来做一次性能优化。
二、性能瓶颈分析
一般的大数据量excel入库的场景中，耗时大概在如下几点里：
- 耗时1： 百万数据读取，字段数量，sheet页个数，文件体积；针对这种情况，我们要选择分片读取，选择合适的集合存储。
- 耗时2： 百万数据的校验，逐行分字段校验；这种情况的耗时会随着字段个数逐渐增加，目前我们的案例中不设计，暂不展开。
- 耗时3： 百万数据的写入；选择合适的写入方式，如Mybatis-plus的分批插入，采用多线程处理等。
三、针对耗时1进行优化
耗时2的场景我们在案例中并未用到，耗时1中针对百万级数据的读取，我们必然要选择分片读取，分片处理，这在我们上一篇文章中就已经采用了该方案，这里通过实现EasyExcel的ReadListener页面读取监听器，实现其invoke方法，在方法中我们增加BATCH_COUNT（单次读取条数）配置，来进行分片读取。读取完后，我们一定要选择合适的集合容器存放临时数据，不同集合之间的增加数据性能存在差异这里我们选择ArrayList。
【优化前代码片段】@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
/*成功数据*/
private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
/*单次处理条数*/
private final static int BATCH_COUNT = 20000;
@Resource
private ThreadPoolExecutor threadPoolExecutor;
@Resource
private UserMapper userMapper;
@Override
public void invoke(User user, AnalysisContext analysisContext) {
if(StringUtils.isNotBlank(user.getName())){
successList.add(user);
return;
}
if(successList.size() >= BATCH_COUNT){
log.info("读取数据：{}", successList.size());
saveData();
}
}
///
///
}
AI 代码解读
@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
/*成功数据*/
// private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
private final List<User> successList = new ArrayList<>();
/*单次处理条数，有原来2万变为10万*/
private final static int BATCH_COUNT = 100000;
@Resource
private ThreadPoolExecutor threadPoolExecutor;
@Resource
private UserMapper userMapper;
@Override
public void invoke(User user, AnalysisContext analysisContext) {
if (StringUtils.isNotBlank(user.getName())) {
successList.add(user);
return;
}
//size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入
if (successList.size() >= BATCH_COUNT) {
log.info("读取数据：{}", successList.size());
saveData();
//清理集合便于GC回收
successList.clear();
}
}
///
///
}
AI 代码解读
这里面我们主要做了2点优化，1）将原来的线程安全的CopyOnWriteArrayList换为ArrayList，前者虽然可保线程安全，但存储数据性能很差；2）将原来单批次2000调整为100000，这个参数是因电脑而异的，并没有最佳数值。
【注】：本文中的代码仅针对优化点贴出，完整代码参考文首中的上一篇文章连接哈！
四、针对耗时3进行优化
针对耗时3的处理方案，我们这里准备了2个：JDBC分批插入+手动事务控制、多线程+Mybatis-Plus批量插入。
4.1 JDBC分批插入+手动事务控制
很多博文中都说mybatis批量插入性能低，有人建议使用原生的JDBC进行处理，那咱们就采用这种方案来测试一下。
首先我们既然要通过jdbc连接数据库进行操作，那就先准备一个连接工具类吧
public class JdbcConnectUtil {
private static String driver;
private static String url;
private static String name;
private static String password;
/**
* 创建数据Properties集合对象加载加载配置文件
*/
static {
Properties properties = new Properties();
try {
properties.load(JdbcConnectUtil.class.getClassLoader().getResourceAsStream("generator.properties"));
driver = properties.getProperty("jdbc.driverClass");
url = properties.getProperty("jdbc.connectionURL");
name = properties.getProperty("jdbc.userId");
password = properties.getProperty("jdbc.password");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接对象
* @return
* @throws Exception
*/
public static Connection getConnect() throws Exception {
return DriverManager.getConnection(url, name, password);
}
/**
* 关闭数据库相关资源
* @param conn
* @param ps
* @param rs
*/
public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if (conn != null) conn.close();
if (ps != null) ps.close();
if (rs != null) rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void close(Connection conn, PreparedStatement ps) {
close(conn, ps, null);
}
public static void close(Connection conn, ResultSet rs) {
close(conn, null, rs);
}
}
AI 代码解读
有了工具类后，我们就可以在EasyExcelImportHandler类中进行JDBC导入逻辑的实现啦。
/**
* jdbc+事务处理
*/
public void import4Jdbc(){
//分批读取+JDBC分批插入+手动事务控制
Connection conn = null;
//JDBC存储过程
PreparedStatement ps = null;
try {
//建立jdbc数据库连接
conn = JdbcConnectUtil.getConnect();
//关闭事务默认提交
conn.setAutoCommit(false);
String sql = "insert into user (id,name, phone_num, address) values";
sql += "(?,?,?,?)";
ps = conn.prepareStatement(sql);
for (int i = 0; i < successList.size(); i++) {
User user = new User();
ps.setInt(1,successList.get(i).getId());
ps.setString(2,successList.get(i).getName());
ps.setString(3,successList.get(i).getPhoneNum());
ps.setString(4,successList.get(i).getAddress());
//将一组参数添加到此 PreparedStatement 对象的批处理命令中。
ps.addBatch();
}
//执行批处理
ps.executeBatch();
//手动提交事务
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
//记得关闭连接
JdbcConnectUtil.close(conn,ps);
}
}
AI 代码解读
这里我们通过PreparedStatement的addBatch()和executeBatch()实现JDBC的分批插入，然后用import4Jdbc()替换原来的savaData()即可。
经过多次导入测试，这种方案的平均耗时为140秒。相比之前的500秒确实有了大幅度提升，但是2分多钟仍然感觉有点慢。
4.2 多线程+Mybatis-Plus批量插入
我们知道Mybatis-Plus的IService中提供了saveBatch的批量插入方法，但经过查看日志发现Mybatis-Plus的saveBatch在最后还是循环调用的INSERT INTO语句！
这种情况下，测试多线程速度和单线程相差不大，所以需要实现真正的批量插入语句，两种方式，一种是通过给Mybatis-Plus注入器，增强批量插入，一种是在xml文件中自己拼接SQL语句，我们在这里选用后一种，因为我们只做一个表，直接手写xml很方便，如果是在企业开发时建议使用sql注入器实现（自定义SQL注入器实现DefaultSqlInjector，添加InsertBatchSomeColumn方法，通过使用InsertBatchSomeColumn方法批量插入。）。
【XML中手动批量插入】
<insert id="insertSelective" parameterType="java.util.List">
insert into user
(id,name, phone_num, address
)
values
<foreach collection="list" item="item" separator=",">
(#{item.id},#{item.name},#{item.phoneNum},#{item.address})
</foreach>
</insert>
AI 代码解读
在在EasyExcelImportHandler类中的saveData()方法中实现多线程批量插入。
/**
* 采用多线程读取数据
*/
private void saveData() {
List<List<User>> lists = ListUtil.split(successList, 1000);
CountDownLatch countDownLatch = new CountDownLatch(lists.size());
for (List<User> list : lists) {
threadPoolExecutor.execute(() -> {
try {
userMapper.insertSelective(list.stream().map(o -> {
User user = new User();
user.setName(o.getName());
user.setId(o.getId());
user.setPhoneNum(o.getPhoneNum());
user.setAddress(o.getAddress());
return user;
}).collect(Collectors.toList()));
} catch (Exception e) {
log.error("启动线程失败,e:{}", e.getMessage(), e);
} finally {
//执行完一个线程减1,直到执行完
countDownLatch.countDown();
}
});
}
// 等待所有线程执行完
try {
countDownLatch.await();
} catch (Exception e) {
log.error("等待所有线程执行完异常,e:{}", e.getMessage(), e);
}
// 提前将不再使用的集合清空，释放资源
successList.clear();
lists.clear();
}
AI 代码解读
经过多次导入测试，100万数据量导入耗时平均在20秒，这就是一个很客观且友好用户的导入功能啦，毕竟100万的xlsx文件，打开都需要七八秒呢！
五、总结
OK！以上就是SpringBoot项目下，通过阿里开源的EasyExcel技术进行百万级数据的导入功能的优化步骤啦，由原来的500秒优化到20秒！
六、结尾彩蛋
如果本篇博客对您有一定的帮助，大家记得留言+点赞+收藏呀。原创不易，转载请联系Build哥！
