多线程创建百万条的sql性能测试数据

bilibilidicks

已于 2024-01-10 22:38:15 修改

阅读量371

点赞数 8

文章标签： sql 数据库

于 2024-01-10 19:13:50 首次发布

本文链接：https://blog.csdn.net/bilibilidicks/article/details/135510855

版权

表结构初始化

CREATE TABLE main_tb (
	main_tb_pkey int PRIMARY KEY AUTO_INCREMENT,
	f1key bigint,
	f2key bigint,
	f3key bigint
);
CREATE TABLE foreign_tb1 (
	fkey bigint PRIMARY KEY AUTO_INCREMENT,
	fkey_name varchar(32)
);
CREATE TABLE foreign_tb2 (
	fkey bigint PRIMARY KEY AUTO_INCREMENT,
	fkey_name varchar(32)
);
CREATE TABLE foreign_tb3 (
	fkey bigint PRIMARY KEY AUTO_INCREMENT,
	fkey_name varchar(32)
);

Entity包

@Data
@TableName("foreign_tb1")
public class ForeignTb1Entity implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId
    private Long fkey;

    private String fkeyName;
}

@Data
@TableName("foreign_tb2")
public class ForeignTb2Entity implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId
    private Long fkey;

    private String fkeyName;
}

@Data
@TableName("foreign_tb3")
public class ForeignTb3Entity implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId
    private Long fkey;

    private String fkeyName;
}


@Data
@TableName("main_tb")
public class MainTbEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableId
    private Long mainTbPkey;

    private Long f1key;

    private Long f2key;

    private Long f3key;
}

Dao包

import java.util.List;

public interface CommonDao {
    void batchInsert(List<String> list);

}

@Mapper
@Repository
public interface ForeignTb1Dao extends BaseMapper<ForeignTb1Entity>, CommonDao {
    void batchInsert(List<String> list);
}

@Mapper
@Repository
public interface ForeignTb2Dao extends BaseMapper<ForeignTb2Entity>, CommonDao {
    void batchInsert(List<String> foreignTb2EntityList);
}

@Mapper
@Repository
public interface ForeignTb3Dao extends BaseMapper<ForeignTb3Entity>, CommonDao {
    void batchInsert(List<String> foreignTb3EntityList);
}

@Mapper
@Repository
public interface MainTbDao extends BaseMapper<MainTbEntity>, CommonDao {
    void batchInsert(List<String> foreignTb3EntityList);
}

Config包

@Data
@Component
@ConfigurationProperties(prefix = "thread.pool")
public class ThreadPoolConfig {
    /**
     * 核心线程数
     */
    private Integer corePoolSize;

    /**
     * 设置最大线程数
     */
    private Integer maxPoolSize;

    /**
     * 设置线程活跃时间
     */
    private Integer keepAliveSeconds;

    /**
     * 设置队列容量
     */
    private Integer queueCapacity;

    /**
     * 线程名称前缀
     */
    private String prefixName;
}



@Configuration
@EnableAsync
@Slf4j
public class ThreadPoolExecutorConfig {
    private ThreadPoolConfig threadPoolConfig;

    public ThreadPoolExecutorConfig(ThreadPoolConfig threadPoolConfig) {
        this.threadPoolConfig = threadPoolConfig;
    }

    @Bean(name = "asyncServiceExecutor")
    public Executor asyncServiceExecutor() {
        log.info("start asyncServiceExecutor");
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(threadPoolConfig.getCorePoolSize());
        executor.setMaxPoolSize(threadPoolConfig.getMaxPoolSize());
        executor.setQueueCapacity(threadPoolConfig.getQueueCapacity());
        executor.setKeepAliveSeconds(threadPoolConfig.getKeepAliveSeconds());
        executor.setThreadNamePrefix(threadPoolConfig.getPrefixName());
        // 拒绝策略
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        // 初始化
        executor.initialize();
        return executor;
    }
}

Async包

public interface AsyncService {
    void executeAsync(CountDownLatch countDownLatch, List<String> stringList, CommonDao commonDao);
}


@Service
@Slf4j
public class AsyncServiceImpl implements AsyncService {
    @Override
    @Async("asyncServiceExecutor")
    public void executeAsync(CountDownLatch countDownLatch, List<String> stringList, CommonDao commonDao) {
        try {
            log.info("start executeAsync");
            // 异步线程需要做的事情
            commonDao.batchInsert(stringList);
            log.info("end executeAsync");
        } finally {
            // 无论上面程序是否异常必须执行 countDown,否则 await 无法释放
            countDownLatch.countDown();
        }
    }
}

Resource文件夹下的Mapper文件夹

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="lego.wing.wingframework.dao.ForeignTb1Dao">

    <insert id="batchInsert" parameterType="java.lang.String">
        insert into foreign_tb1(
        fkey_name
        )values
        <foreach collection="list" item="item" separator=",">
            (#{item})
        </foreach>
    </insert>
</mapper>


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="lego.wing.wingframework.dao.ForeignTb2Dao">

    <insert id="batchInsert" parameterType="java.lang.String">
        insert into foreign_tb2(
        fkey_name
        )values
        <foreach collection="list" item="item" separator=",">
            (#{item})
        </foreach>
    </insert>
</mapper>


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="lego.wing.wingframework.dao.ForeignTb3Dao">

    <insert id="batchInsert" parameterType="java.lang.String">
        insert into foreign_tb3(
        fkey_name
        )values
        <foreach collection="list" item="item" separator=",">
            (#{item})
        </foreach>
    </insert>
</mapper>


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="lego.wing.wingframework.dao.MainTbDao">

    <insert id="batchInsert" parameterType="java.lang.String">
        insert into main_tb(
        f1key,
        f2key,
        f3key
        )values
        <foreach collection="list" item="item" separator=",">
            (#{item}, #{item}, #{item})
        </foreach>
    </insert>
</mapper>

Yml文件配置

# 异步线程池配置
thread:
  pool:
    corePoolSize: 8 # 核心线程数
    maxPoolSize: 20 # 设置最大线程数
    keepAliveSeconds: 300 # 设置线程活跃时间
    queueCapacity: 100 # 设置队列容量
    prefixName: async-service- # 线程名称前缀

测试用例

package lego.wing.wingframework;

import com.google.common.collect.Lists;
import lego.wing.wingframework.async.AsyncService;
import lego.wing.wingframework.dao.ForeignTb1Dao;
import lego.wing.wingframework.dao.ForeignTb2Dao;
import lego.wing.wingframework.dao.ForeignTb3Dao;
import lego.wing.wingframework.dao.MainTbDao;
import lego.wing.wingframework.vo.MainTbVo;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Random;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Slf4j
@SpringBootTest
class WingFrameworkApplicationTests {

    static String getRandomString(int length) {
        String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Random random = new Random();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < length; i++) {
            int number = random.nextInt(62);
            sb.append(str.charAt(number));
        }
        return sb.toString();
    }

    @Autowired
    MainTbDao mainTbDao;

    @Autowired
    ForeignTb1Dao foreignTb1Dao;

    @Autowired
    ForeignTb2Dao foreignTb2Dao;

    @Autowired
    ForeignTb3Dao foreignTb3Dao;

    @Autowired
    AsyncService asyncService;

    ExecutorService executor = Executors.newFixedThreadPool(32);


    @Test
    public void initForeignTb2Data() {
        int size = 500000;
        int batchSize = 10;
        List<String> foreignTb2EntityList = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            foreignTb2EntityList.add(getRandomString(15));
        }
//        for (int i = 0; i < size; i++) {
//            foreignTb2EntityList.add(String.valueOf(i + 1));
//        }
        // 每 100000 条数据插入开一个线程
        List<List<String>> lists = Lists.partition(foreignTb2EntityList, 10000);
        for (int i = 0; i < batchSize; i++) {
            CountDownLatch countDownLatch = new CountDownLatch(lists.size());
            long startTime = System.currentTimeMillis();
            // lists.forEach(listSub -> asyncService.executeAsync(countDownLatch, listSub, mainTbDao));
            lists.forEach(listSub -> asyncService.executeAsync(countDownLatch, listSub, foreignTb1Dao));
            // lists.forEach(listSub -> asyncService.executeAsync(countDownLatch, listSub, foreignTb2Dao));
            // lists.forEach(listSub -> asyncService.executeAsync(countDownLatch, listSub, foreignTb3Dao));
            try {
                // 保证之前的所有的线程都执行完成，才会走下面的
                countDownLatch.await();
            } catch (InterruptedException e) {
                log.error("阻塞异常：" + e.getMessage());
            }
            long endTime = System.currentTimeMillis();
            log.info("共耗时：{} 秒", (endTime - startTime) / 1000);
        }
    }

    @Test
    public void testSelectData() {
        long startTime = System.currentTimeMillis();
        List<MainTbVo> mainTbVos = mainTbDao.selectMainTbByCondition(new HashMap<>());
        long endTime = System.currentTimeMillis();
        log.info("共耗时：{} 秒", (endTime - startTime) / 1000);
    }

}

编写连表查询Sql进行测试

SELECT *
FROM main_tb mt 
LEFT JOIN foreign_tb1 ft 
ON mt.f1key = ft.fkey
LEFT JOIN foreign_tb2 ft2 
ON mt.f2key = ft2.fkey 
LEFT JOIN foreign_tb3 ft3 
ON mt.f3key = ft3.fkey;