springboot一次性定时任务插入1000万条数据

模拟 1000 万个用户，再去查询

导入数据

1. 用可视化界面：适合一次性导入、数据量可控

2. 写程序：for 循环，建议分批，不要一把梭哈（可以用接口来控制）要保证可控、幂等，注意线上环境和测试环境是有区别的

   导入 1000 万条，for i 1000w

3. 执行 SQL 语句：适用于小数据量

4. 一次性任务main方法

5. 定时任务

定时任务：

启动类上加注解

插入数据

@Component
public class InsertUser {
    @Resource
    private UserMapper userMapper;

    public void doInsertUser() {
        //计时工具spring的
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        final int NUM = 1000;
        for (int i = 0; i < NUM; i++) {
            User user = new User();
            user.setUsername("假用户");
            user.setUserAccount("fake");
            user.setTags("");
            user.setAvatarUrl("https://xingqiu-tuchuang-1256524210.cos.ap-shanghai.myqcloud.com/3925/202209101442967.jpg");
            user.setGender(0);
            user.setUserPassword("123123");
            user.setEmail("213213@qq.com");
            user.setUserStatus(0);
            user.setPhone("123123");
            user.setUserRole(0);
            user.setPlanetCode("111");
            userMapper.insert(user);
        }
        stopWatch.stop();
        stopWatch.getTotalTimeMillis();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

计时工具spring的

StopWatch

怎么运行，不能main方法，会报错

因为用到了mapper，springboot没启动，注入不进来

指定最大值, 相当于只执行一次

在方法上加注解

 @Scheduled(initialDelay = 5000,fixedRate = Long.MAX_VALUE)
1

就可以只执行一次

或者写在单元测试里，但是在打包时候会执行一次单元测试，每次打包就会插入数据

package com.bo.partner.service.impl;

import com.bo.partner.mapper.UserMapper;
import com.bo.partner.model.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.StopWatch;

import javax.annotation.Resource;

/**
* @author: bo
* @date: 2022/9/10
* @description:
*/
@SpringBootTest
public class InsertUserTest {
    @Resource
    private UserMapper userMapper;

    @Test
    public void doInsertUser() {
        //计时工具spring的
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        final int NUM = 1000;
        for (int i = 0; i < NUM; i++) {
            User user = new User();
            user.setUsername("假用户");
            user.setUserAccount("fake");
            user.setTags("");
            user.setAvatarUrl("https://xingqiu-tuchuang-1256524210.cos.ap-shanghai.myqcloud.com/3925/202209101442967.jpg");
            user.setGender(0);
            user.setUserPassword("123123");
            user.setEmail("213213@qq.com");
            user.setUserStatus(0);
            user.setPhone("123123");
            user.setUserRole(0);
            user.setPlanetCode("111");
            userMapper.insert(user);
        }
        stopWatch.stop();
        System.out.println(stopWatch.getTotalTimeMillis());
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

时间主要是花费在==建立和释放数据库连接（使用批量查询可以解决）==for循环是绝对线性的（并发）

批量查询

mybatisplus的saveBatch();

        final int NUM = 1000;
        List users = new ArrayList<>();

        for (int i = 0; i < NUM; i++) {
            User user = new User();
            user.setUsername("假用户");
            user.setUserAccount("fake");
            user.setTags("");
            user.setAvatarUrl("https://xingqiu-tuchuang-1256524210.cos.ap-shanghai.myqcloud.com/3925/202209101442967.jpg");
            user.setGender(0);
            user.setUserPassword("123123");
            user.setEmail("213213@qq.com");
            user.setUserStatus(0);
            user.setPhone("123123");
            user.setUserRole(0);
            user.setPlanetCode("111");
            users.add(user);
            /*userMapper.insert(user);*/

        }
        userService.saveBatch(users, 100);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

再次优化

并发要注意执行的先后顺序没有影响不要用到非并发类的集合

@Test
    public void doConcurrencyInsertUser() {
        //计时工具spring的
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        final int NUM = 100000;
        int j = 0;
        //10万分10组
        List> futureList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List users = Collections.synchronizedList(new ArrayList<>());
            while (true) {
                j++;
                User user = new User();
                user.setUsername("假用户");
                user.setUserAccount("fake");
                user.setTags("");
                user.setAvatarUrl("https://xingqiu-tuchuang-1256524210.cos.ap-shanghai.myqcloud.com/3925/202209101442967.jpg");
                user.setGender(0);
                user.setUserPassword("123123");
                user.setEmail("213213@qq.com");
                user.setUserStatus(0);
                user.setPhone("123123");
                user.setUserRole(0);
                user.setPlanetCode("111");
                users.add(user);
                if (j % 10000 == 0) {
                    break;
                }
            }
            CompletableFuture future = CompletableFuture.runAsync(() -> {
                userService.saveBatch(users, 10000);
            });
            futureList.add(future);
        }
        CompletableFuture.allOf(futureList.toArray(new CompletableFuture[]{})).join();
        stopWatch.stop();
        System.out.println(stopWatch.getTotalTimeMillis());
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

从自己的线程池取

private ExecutorService executorService = new ThreadPoolExecutor(60,1000,10000,TimeUnit.MINUTES,new ArrayBlockingQueue<>(10000));
1

1. CPU密集型:分配的核心线程数=CPU - 1
2. IO密集型:分配的核心线程数可以大于 CPU核数

---