记一次通过java多线程sql执行优化

接近2万的员工
搞所有员工密码重置为工号后6位的功能时。
我一开始直接串行执行，耗时11分钟
后面优化用多线程+mybatis的序列化批量处理，如下图代码：

    public int resetAllPwd2() {
        List<SysUser> sysUsers = userMapper.selectAllNormalStaff();
        ExecutorService es = Executors.newFixedThreadPool(9, new ThreadFactoryBuilder().setNameFormat("SysUserServiceImpl-%d").build());
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);

        List<SysUserMapper> mappers = new ArrayList<>();
        for (int i = 0; i < 15; i++) {
            mappers.add(sqlSession.getMapper(SysUserMapper.class));
        }
        String currentUserName = "blank";
        try {
            currentUserName = SecurityUtils.getUsername();
        } catch (Exception e) {
            e.printStackTrace();
        }
        String finalCurrentUserName = currentUserName;
        es.execute(() -> {
            for (int i = 0; i < sysUsers.size(); i++) {
                SysUser sysUser = sysUsers.get(i);
                String userName = sysUser.getUserName();
                if (StringUtils.isNotBlank(userName) && userName.length() >= 6) {
                    sysUser.setPassword(SecurityUtils.encryptPassword(userName.substring(userName.length() - 6)));
                }
                int finalI = i;
                es.execute(() -> {
                    mappers.get(finalI % 15).resetUserPwd(userName, sysUser.getPassword(), finalCurrentUserName);
                });
                // 每一千笔提交一次，防止往后数据量大内存溢出
                if (i % 1000 == 999) {
                    sqlSession.commit();
                    sqlSession.flushStatements();
                    sqlSession.clearCache();
                }
            }
        });
        return 1;
    }

但仍然要6分钟左右。

总感觉这么点数据这么久不对劲，加上对mybatis的批处理不太熟悉，干脆自已考虑用空间换时间，多线程分页直接干降到了1分钟左右。下图代码

    @Override
    public int resetAllPwd() {
        List<SysUser> sysUsers = userMapper.selectAllNormalStaff();
        ExecutorService es = Executors.newFixedThreadPool(16, new ThreadFactoryBuilder().setNameFormat("SysUserServiceImpl-%d").build());

        String currentUserName = "blank";
        try {
            currentUserName = SecurityUtils.getUsername();
        } catch (Exception e) {
            e.printStackTrace();
        }
        String finalCurrentUserName = currentUserName;
        es.execute(() -> {
            int size = sysUsers.size();
            int total = size / 499 + (size % 499 == 0 ? 0 : 1);
            for (int i = 0; i < total; i++) {
                int finalI = i;
                if (i != total - 1) {
                    es.execute(() -> {
                        List<SysUser> subList = sysUsers.subList(finalI * 499, (finalI + 1) * 499);
                        setNewPwd(sysUsers, finalCurrentUserName, subList);
                    });
                } else {
                    es.execute(() -> {
                        List<SysUser> subList = sysUsers.subList(finalI * 499, size);
                        setNewPwd(sysUsers, finalCurrentUserName, subList);
                    });
                }
            }
        });

        return 1;
    }

    private void setNewPwd(List<SysUser> sysUsers, String finalCurrentUserName, List<SysUser> subList) {
        for (int idx = 0; idx < subList.size(); idx++) {
            SysUser sysUser = sysUsers.get(idx);
            String userName = sysUser.getUserName();
            if (StringUtils.isNotBlank(userName) && userName.length() >= 6) {
                sysUser.setPassword(SecurityUtils.encryptPassword(userName.substring(userName.length() - 6)));
            }
        }
        userMapper.resetUserPwdBatch(subList, finalCurrentUserName);
    }

后续可能再优化的方向应该是mybatis批处理加多线程加分页空间换时间。

但我这暂时1分钟左右的响应时间够用了，这个功能被用的机率不高，暂且就这样吧。

----------------2022.10.13有个代码简化的更新-------------------

代码如下：

 @Override
    public int resetAllPwd() {
        List<SysUser> sysUsers = userMapper.selectAllNormalStaff();
        ExecutorService es = Executors.newFixedThreadPool(16, new ThreadFactoryBuilder().setNameFormat("SysUserServiceImpl-%d").build());

        String currentUserName = "blank";
        try {
            currentUserName = SecurityUtils.getUsername();
        } catch (Exception e) {
            e.printStackTrace();
        }
        String finalCurrentUserName = currentUserName;
        List<List<SysUser>> partition = ListUtils.partition(sysUsers, 500);
        for (List<SysUser> users : partition) {
            es.execute(() -> setNewPwd(finalCurrentUserName, users));
        }
        return 1;
    }

    private void setNewPwd(String finalCurrentUserName, List<SysUser> subList) {
        for (int idx = 0; idx < subList.size(); idx++) {
            SysUser sysUser = subList.get(idx);
            String userName = sysUser.getUserName();
            if (StringUtils.isNotBlank(userName) && userName.length() >= 6) {
                sysUser.setPassword(SecurityUtils.encryptPassword(userName.substring(userName.length() - 6)));
            }
        }
        userMapper.resetUserPwdBatch(subList, finalCurrentUserName);
    }

用上了ListUtils工具类替换了手写的分页拆分集合。清爽很多。这段在线上用了一年多吧，唯一的问题是加密时工具类提供的加密算法比较耗时，导致程序跑完了有时要2-3分钟才出效果。