插入操作数据库优化

单条数据的插入

@Test
public void insertOne(){
    ProfilerUtil.start("insertOne");
    BlogPO blog = this.init();
    blogMapper.insert(blog);
    ProfilerUtil.end("insertOne");
}

使用时间:

【insertOne】 use time:【590】ms

循环插入10000条

@Test
public void insertLoop(){
    ProfilerUtil.start("insertLoop");
    for(int i = 0; i< 1000; i++){
        BlogPO blog = this.init();
        blogMapper.insert(blog);
    }
    ProfilerUtil.end("insertLoop");
}

使用时间:

【insertLoop】 use time:【44939】ms

10000条批量插入

@Test
public void batchInsert(){
    ProfilerUtil.start("batchInsert");
    List<BlogPO> lists = new ArrayList<>();
    for(int i = 0; i< 1000; i++){
        BlogPO blog = this.init();
        lists.add(blog);
    }
    blogService.saveBatch(lists);
    ProfilerUtil.end("batchInsert");
}

使用时间:

【batchInsert】 use time:【4279】ms

使用多线程异步插入10000数据(每次插入2500,开启4个线程)

@Test
public void batchInsertExecutor(){
    ProfilerUtil.start("batchInsertExecutor");
    List<BlogPO> lists = new ArrayList<>();
    for(int i = 0; i< 10000; i++){
        BlogPO blog = this.init();
        lists.add(blog);
    }
    saveBatch(lists);
    ProfilerUtil.end("batchInsertExecutor");
}

/**
     * 批量保存
     * @param lists
     */
private void saveBatch(List<BlogPO> lists){
    if (CollectionUtils.isEmpty(lists)) {
        return;
    }
    int size = lists.size();
    int batchSize = 2500;
    int batchCount = size % batchSize == 0 ? size / batchSize : size / batchSize + 1;

    AtomicInteger commitCount = new AtomicInteger();
    AtomicBoolean rollBack = new AtomicBoolean();
    CountDownLatch countDownLatch = new CountDownLatch(1);

    List<BlogPO> blogList = new ArrayList<>(batchSize);
    int scStockRealPOSSize = 0;

    //async to db operation
    for (int i = 0; i < size; i++) {
        blogList.add(lists.get(i));
        scStockRealPOSSize++;

        if (batchSize != scStockRealPOSSize && size - 1 != i) {
            continue;
        }
        //async to batch insert
        List<BlogPO> batchWithTxScStockRealPOS = blogList;
        commonThreadPool.execute(() -> batchWithTx(batchWithTxScStockRealPOS, batchSize, commitCount, rollBack, countDownLatch));
        blogList = new ArrayList<>(batchSize);
        scStockRealPOSSize = 0;
    }

    //check all threads result
    while (commitCount.get() != batchCount && !rollBack.get()) {
    }
    //tell other all threads to continue
    countDownLatch.countDown();
}

/**
     * 手动控制事务
     * @param scStockRealPOS
     * @param batchSize
     * @param commitCount
     * @param rollBack
     * @param countDownLatch
     */
private void batchWithTx(List<BlogPO> scStockRealPOS, int batchSize, AtomicInteger commitCount, AtomicBoolean rollBack, 
                        CountDownLatch countDownLatch) {
    tx.execute(new TransactionCallbackWithoutResult() {
        @Override
        protected void doInTransactionWithoutResult(TransactionStatus status) {
            //batch db operate
            batchInsert(scStockRealPOS, batchSize, commitCount, rollBack);
            //wait all end
            try {
                countDownLatch.await();
            } catch (InterruptedException e) {
                rollBack.set(true);
                log.error("saveBatch countDownLatch.await() error", e);
                throw new RuntimeException("aveBatch countDownLatch.await() error", e);
            }
            //tx rollback for when need
            if (rollBack.get()) {
                status.setRollbackOnly();
            }
        }
    });
}

/**
     * 插入数据库
     * @param batchSize
     * @param commitedNum
     * @param rollBack
     */
private void batchInsert(List<BlogPO> blogList, int batchSize, AtomicInteger commitedNum, AtomicBoolean rollBack) {
    try {
        blogService.saveBatch(blogList, batchSize);
        commitedNum.incrementAndGet();
    } catch (Throwable e) {
        rollBack.set(true);
        log.error("asyncBatchInsert error", e);
    }
}

使用时间(经测试2500每条为最佳条数,使用时间最少):

【batchInsertExecutor】 use time:【3795】ms
github地址:https://github.com/gao-tao/performance
posted @   snail灬  阅读(257)  评论(0编辑  收藏  举报
编辑推荐:
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· .NET 进程 stackoverflow异常后,还可以接收 TCP 连接请求吗?
阅读排行:
· 本地部署 DeepSeek:小白也能轻松搞定!
· 基于DeepSeek R1 满血版大模型的个人知识库,回答都源自对你专属文件的深度学习。
· 在缓慢中沉淀,在挑战中重生!2024个人总结!
· 大人,时代变了! 赶快把自有业务的本地AI“模型”训练起来!
· Tinyfox 简易教程-1:Hello World!
点击右上角即可分享
微信分享提示