批量插入假数据效率对比

1. 每条数据都执行建立连接，插入数据，关闭连接

@Resource
private UserMapper userMapper;

public void insertUsers() {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    final int INSERT_NUM = 100000;
    for (int i = 0; i < INSERT_NUM; i++) {
        User user = new User();
        user.setUserName("faker" + i);
        user.setUserAccount("" + i);
        user.setAvatarUrl("https://123213.com/123.png");
        user.setGender(0);
        user.setUserPassword("dc8701a0f05e92481c536d911c5c6fa9");
        user.setPhone("123");
        user.setEmail("123@qq.com");
        user.setTags("[]");
        user.setUserStatus(0);
        user.setUserRole(0);
        user.setStudentId("11111111");
        userMapper.insert(user);
    }
    stopWatch.stop();
    System.out.println(stopWatch.getTotalTimeMillis());
}

时间：470100

2. 建立连接，批量插入数据，关闭连接

@Resource
private UserService userService;

public void insertUsers() {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    final int INSERT_NUM = 100000;
    List<User> userList = new ArrayList<>();
    int batchSize = 10000;
    for (int i = 0; i < INSERT_NUM; i++) {
        User user = new User();
        user.setUserName("faker" + i);
        user.setUserAccount("" + i);
        user.setAvatarUrl("https://123213.com/123.png");
        user.setGender(0);
        user.setUserPassword("dc8701a0f05e92481c536d911c5c6fa9");
        user.setPhone("123");
        user.setEmail("123@qq.com");
        user.setTags("[]");
        user.setUserStatus(0);
        user.setUserRole(0);
        user.setStudentId("11111111");
        userList.add(user);
    }
    userService.saveBatch(userList, batchSize);
    stopWatch.stop();
    System.out.println(stopWatch.getTotalTimeMillis());
}

时间：20533

3. 并发建立连接，批量插入数据，关闭连接

@Resource
private UserService userService;

public void doConcurrencyInsertUsers() {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    int batchSize = 10000;
    int j = 0;
    List<CompletableFuture<Void>> futureList = new ArrayList<>();
    // 分十组
    for (int i = 0; i < 10; i++) {
        List<User> userList = new ArrayList<>();
        while (true) {
            j++;
            User user = new User();
            user.setUserName("faker" + i);
            user.setUserAccount("" + i);
            user.setAvatarUrl("https://123213.com/123.png");
            user.setGender(0);
            user.setUserPassword("dc8701a0f05e92481c536d911c5c6fa9");
            user.setPhone("123");
            user.setEmail("123@qq.com");
            user.setTags("[]");
            user.setUserStatus(0);
            user.setUserRole(0);
            user.setStudentId("11111111");
            userList.add(user);
            userList.add(user);
            if (j % batchSize == 0) {
                break;
            }
        }
        // 异步执行
        CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
            System.out.println("threadName: " + Thread.currentThread().getName());
            userService.saveBatch(userList, batchSize);
        });
        futureList.add(future);
    }
    CompletableFuture.allOf(futureList.toArray(new CompletableFuture[]{})).join();
    stopWatch.stop();
    System.out.println(stopWatch.getTotalTimeMillis());
}

分10组：5862

分100组：6378（默认线程数不够，一个线程需要干几次）

自定义线程数，开100个线程：

@Resource
private UserService userService;

private ExecutorService executorService = new ThreadPoolExecutor(100, 1000, 10000, TimeUnit.MINUTES, new ArrayBlockingQueue<>(10000));
    public void doConcurrencyInsertUsers() {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        int batchSize = 1000;
        int j = 0;
        List<CompletableFuture<Void>> futureList = new ArrayList<>();
        // 分一百组
        for (int i = 0; i < 100; i++) {
            List<User> userList = new ArrayList<>();
            while (true) {
                j++;
                User user = new User();
                user.setUserName("faker" + i);
                user.setUserAccount("" + i);
                user.setAvatarUrl("https://123213.com/123.png");
                user.setGender(0);
                user.setUserPassword("dc8701a0f05e92481c536d911c5c6fa9");
                user.setPhone("123");
                user.setEmail("123@qq.com");
                user.setTags("[]");
                user.setUserStatus(0);
                user.setUserRole(0);
                user.setStudentId("11111111");
                userList.add(user);
                userList.add(user);
                if (j % batchSize == 0) {
                    break;
                }
            }
            // 异步执行
            CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
                System.out.println("threadName: " + Thread.currentThread().getName());
                userService.saveBatch(userList, batchSize);
            }, executorService);
            futureList.add(future);
        }
        CompletableFuture.allOf(futureList.toArray(new CompletableFuture[]{})).join();
        stopWatch.stop();
        System.out.println(stopWatch.getTotalTimeMillis());
    }
}

时间：6288

I/O时间为关键因素，再多线程意义不大

ps: 上面的代码这样写j++是被多个线程递增，线程不安全