Java中大数据多线程导入MySQL性能测试

业务

多线程可以有效地提高业务效率，下面模拟向MySQL分批次写入100万条记录，看看使用多线程和不使用多线程，耗费时间的差距

注：本次实验是往本地MySQL数据库写入数据，如果是服务器的数据库，一般来说网络速度会成为瓶颈

配置

CPU 4核8线程

内存 16G 2400MHz

测试代码

使用MyBatis向MySQL数据插入数据，线程池初始大小20，分10轮写入，每轮写入10万条数据，总计100万，由于MyBatis无法承载太多数据，所以每轮10万条数据分200批次写入MySQL数据库，每批次500条记录

package com.example.mysql.controller;

import com.example.mysql.entity.User;
import com.example.mysql.mapper.UserMapper;
import com.example.mysql.util.DateUtil;
import com.example.mysql.util.NumberUtil;
import com.example.mysql.util.UUIDUtil;
import com.example.mysql.util.id.IdGenerator;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.ListUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;

@RestController
@Slf4j
public class MySqlIndexControllerThreadPool {

    @Resource
    private UserMapper userMapper;

    private final ExecutorService executorService = Executors.newFixedThreadPool(20);

    @GetMapping("/mysql/thread/pool")
    public String mysql() throws ParseException {

        long st = System.currentTimeMillis();

        int total = 0;

        for (int i = 0; i < 10; i++) {

            List<User> users = new ArrayList<>();

            for (int j = 0; j < 100000; j++) {
                User user = new User();
                user.setId(IdGenerator.nextCommonId());
                user.setUsername(UUIDUtil.nextUUID().substring(0,10));
                user.setPassword(UUIDUtil.nextUUID().substring(0,10));
                user.setAddress(UUIDUtil.nextUUID().substring(0,10));
                user.setEmail(UUIDUtil.nextUUID().substring(0,10));
                user.setProfile(UUIDUtil.nextUUID().substring(0,10));
                user.setBirthday(DateUtil.randomDate());
                user.setRegisterDay(DateUtil.randomDate());
                user.setLoginDay(DateUtil.randomDate());
                user.setStatus(NumberUtil.getStatusInt());
                user.setAccount(NumberUtil.accountDecimal());
                user.setBalance(NumberUtil.balanceDecimal());
                user.setAge(NumberUtil.getAgeInt());
                user.setSex(NumberUtil.getSexInt());
                user.setAvatar(UUIDUtil.nextUUID().substring(0,10));
                user.setLevel(NumberUtil.getLevelInt());

                users.add(user);
            }

            total += users.size();

            List<List<User>> partition = ListUtils.partition(users, 500);

            CountDownLatch cdl = new CountDownLatch(users.size());

            log.info("正在写入数据库，第 {} 轮", (i + 1));

            AtomicInteger count = new AtomicInteger();
            int round = i + 1;
            partition.parallelStream().forEach(p -> {
                executorService.execute(() -> {
                    userMapper.insertBatch(p);
                    cdl.countDown();
                    log.info("{} -> 写完第 {} 批，批次 {}/{}，第 {} 轮", Thread.currentThread().getName(), 
                    count.getAndIncrement() + 1, count.get(), partition.size(), round);
                });
            });

            log.info("第 {} 轮写入数据库结束", (i + 1));

        }

        log.info("{} 条数据插入数据库，耗时：{} s", total, (System.currentTimeMillis() - st) / 1000);

        return "batch insert success, " + total + " 条数据插入数据库，耗时：" + 
        (System.currentTimeMillis() - st) / 1000 + " s";
    }

}

100万数据性能测试

多线程

耗时74s

非多线程

耗时204s

总结

使用多线程性能几乎提升3倍