mysql批量插入亿级别的数据

原创 已于 2023-01-05 10:02:51 修改 · 899 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#java #mysql #elasticsearch

于 2023-01-05 00:00:00 首次发布
本文探讨了影响MySQL数据插入效率的因素，包括客户端与服务端通信、主键唯一索引校验、事务检查及单线程处理，并提出了批量操作、调整bulk_insert_buffer_size、关闭校验和采用java多线程master-worker模式的解决方案。

哪些情况会影响数据插入的效率

1.单次客户端与服务端之间的通信

2. mysql对主键、唯一索引、外键的校验

3. 事务检查

4. 单线程处理业务并封装sql数据（比较耗时）

针对以上情况解决方案

1.将单次插入数据操作修改为批量操作（load加载数据比批量新增数据效率更高）,可以减少client与server的通信消耗。

1.1 修改bulk_insert_buffer_size参数

# 修改insert缓存池长度，可以大批量的新增数据  
set bulk_insert_buffer_size = 53687091200;
# 需要超级权限
set GLOBAL concurrent_insert = 2;

2.关闭唯一索引、主键、外键校验

必须保证数据库主键的唯一情况下，才可使用此属性。及时关闭此属性mysql依然会检查主键唯一性
 set unique_checks = 0;
 set foreign_key_checks = 0;

3. 关闭事务检查

 set autocommit = 0;

4. 使用java多线程模型master-worker模式拆分任务，减少业务处理的耗时

4.1 Master类

这个是master类主要作用是从队列中获取数据并将数据放入临时集合中，集合中的数据大于阀值则使用线程池创建线程交于worker执行批量插入数据操作。
此类核心：1.拆分任务 2. 创建线程调用worker的run方法 3. 记录每个线程的结果集
package com.yuanda.erp9.syn.execule.thread;

import com.yuanda.erp9.syn.enums.ThreadPoolTypeEnum;
import com.yuanda.erp9.syn.execule.factory.SimpleThreadPoolFactory;
import com.yuanda.erp9.syn.service.erp9.ESService;
import com.yuanda.erp9.syn.service.erp9.GoodsService;
import com.yuanda.erp9.syn.service.erp9.impl.ESServiceImpl;
import com.yuanda.erp9.syn.service.erp9.impl.GoodsServiceImpl;
import com.yuanda.erp9.syn.util.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentLinkedDeque;

/**
 * @ClassName Master
 * @Description 主任务类，负责拆分任务，并调度worker执行.
 * @Date 2022/11/15
 * @Author myq
 */
@Slf4j
public class Master<T> {
    /**
     * 存放所有任务的容器
     */
    private ConcurrentLinkedDeque<T> deque = new ConcurrentLinkedDeque<>();

    /**
     * 存储返回值
     */
    private ConcurrentHashMap<String, Object> resMap = new ConcurrentHashMap<>();

    /**
     * 是否开始创建子任务执行
     */
    public volatile boolean isBegin = false;

    /**
     * 子任务最大接受数量（每个子任务执行的任务最大次数）
     */
    private final int workTaskMaxSize = 3000;

    public Master(Integer supplierId, String hisp) {
        this.isBegin = true;
        // 删除所有上次添加的数据
        deleteAllOfBefore(supplierId, hisp);
        // 设置属性
        setMysqlInsertProperty();
        // 创建线程并执行start方法之前，执行delayStart方法
        new Thread(this::distributionTask,"Master-Stared").start();
    }

    /**
     * @Description: 添加任务
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/11/1515:56
     */
    public void add(T task) {
        deque.add(task);
    }


    /**
     * @Description: 拆分任务，并创建子线程去执行
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/12/279:43
     */
    public void distributionTask() {
        List<T> tempList = new ArrayList<>();
        //创建线程池
        ThreadPoolTaskExecutor executor = SimpleThreadPoolFactory.choose(ThreadPoolTypeEnum.SPECIAL_PURPOSE);
        // 延时启动后
        while (isBegin) {
            // 获取队列里面的数据
            T poll = deque.poll();
            if (poll != null) {
                tempList.add(poll);
                // 满足创建子任务条件
                if (tempList.size() >= workTaskMaxSize) {
                    working(tempList, executor);
                    tempList.clear();
                }
            }
        }

        // 结束标志
        if (!isBegin) {
            // 获取队列里面的数据
            while (deque.size() > 0) {
                T poll = deque.poll();
                tempList.add(poll);
                if (tempList.size() >= workTaskMaxSize) {
                    working(tempList, executor);
                    tempList.clear();
                }
            }
            working(tempList,executor);
            tempList.clear();
        }

        isCompleted(executor);
        // 重置属性
        resetMysqlInsertProperyt();
        System.gc();
    }



    /**
     * @Description: 判断线程池中的线程是否执行完毕
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/12/2810:35
     */
    public void isCompleted(ThreadPoolTaskExecutor executor) {
        while (true) {
            if (executor.getActiveCount() <= 0) {
//                executor.shutdown();
                log.debug(">>>>>>>>>>>>>>>>>>>>任务执行完毕<<<<<<<<<<<<<<<<<<<");
                break;
            }
        }
    }


    /**
     * @Description: 批量新增之后重置属性
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/12/2314:42
     */
    private void resetMysqlInsertProperyt() {
        GoodsService goodsService = getBean();
        goodsService.insertBatchBeforeSet();
    }

    /**
     * @Description: 批量新增之前设置属性
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/12/2314:41
     */
    private void setMysqlInsertProperty() {
        GoodsService goodsService = getBean();
        goodsService.insertBatchAfterReset();
    }


    /**
     * @Description: 子任务执行-并启动线程
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/11/1617:28
     */
    private void working(List<T> sonTaskList, ThreadPoolTaskExecutor executor) {
        ArrayList<T> mysqlList = new ArrayList<>(sonTaskList);
        AbstractWorker mysqlWorker = new MysqlWorker(mysqlList, resMap);
        executor.execute(mysqlWorker);

        ArrayList<T> esList = new ArrayList<>(sonTaskList);
        AbstractWorker esWorker = new ElasticSearchWorker(esList, resMap);
        executor.execute(esWorker);
    }


    /**
     * @Description: 删除上次数据
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/12/1915:20
     */
    private void deleteAllOfBefore(Integer supplierId, String hisp) {
        List<String> deleteIds = Collections.EMPTY_LIST;
        try {
            GoodsService goodsService = getBean();
            deleteIds = goodsService.queryDeleteIds(supplierId, hisp);
            if (CollectionUtils.isEmpty(deleteIds))
                return;
            List<String> mysqlIds = new ArrayList<>(deleteIds);
            goodsService.deleteAll(mysqlIds);
            log.info(">>>>>>>>>>>>>>>>>>>>>>>删除mysql数据成功>>>>>>>>>>>>>>>>>>>>>>>");

            ESService esService = SpringContextUtil.getBean("ESServiceImpl", ESServiceImpl.class);
            ArrayList<String> esIds = new ArrayList<>(deleteIds);
            deleteIds.clear();
            esService.deleteAllByIds(esIds);
            log.info(">>>>>>>>>>>>>>>>>>>>>>>删除ES数据成功>>>>>>>>>>>>>>>>>>>>>>>");
        } finally {
            deleteIds.clear();
        }
    }


    /**
     * @Description: 返回GoodService
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/12/2315:05
     */
    public GoodsService getBean() {
        return SpringContextUtil.getBean(GoodsService.class, "goodsServiceImpl", GoodsServiceImpl.class);
    }


}

4.2 Worker接口

package com.yuanda.erp9.syn.execule.thread;

/**
 * @ClassName Worker
 * @Description 子任务工作者
 * @Date 2022/11/15
 * @Author myq
 */

public interface Worker extends Runnable {



}
4.3 MysqlWorker
同步数据到三张表中
package com.yuanda.erp9.syn.execule.thread;

import com.yuanda.erp9.syn.pojo.CmsGoodsPojo;
import com.yuanda.erp9.syn.service.erp9.GoodsService;
import com.yuanda.erp9.syn.util.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @ClassName Worker
 * @Description 子任务工作者
 * @Date 2022/11/15
 * @Author myq
 */
@Slf4j
public class MysqlWorker<T> extends AbstractWorker<T> {

    // 数据集合
    private List<T> task;
    // 子任务执行情况结果集
    private ConcurrentHashMap<String, Object> resultMap;


    public MysqlWorker(List<T> task, ConcurrentHashMap<String, Object> resultMap) {
        // 初始化子任务
        this.task = task;
        this.resultMap = resultMap;
    }

    /**
     * @Description: 由父类线程池调用
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/11/2815:24
     */
    @Override
    void task() {
        String workerThreadName = Thread.currentThread().getName();
        try {
            log.info("**************开始同步mysql数据**************");
            GoodsService goodsService = SpringContextUtil.getBean("goodsServiceImpl");
            goodsService.insertBatch((List<CmsGoodsPojo>) task);
            log.info("**************结束同步mysql数据**************");
        } catch (Exception e) {
            e.printStackTrace();
            log.error(workerThreadName + ":{} ", e);
            resultMap.put("errMsg", "worker run error : {" + e + "}");
            resultMap.put("errThreadName", workerThreadName);
            resultMap.put("errCode", 500);
            throw new RuntimeException("Thread-Name" + workerThreadName + "worker run error :{}");
        } finally {
            task.clear();
        }
    }


}
4.4 ElasticSearchWorker
同步数据到es中
package com.yuanda.erp9.syn.execule.thread;

import com.yuanda.erp9.syn.pojo.CmsGoodsPojo;
import com.yuanda.erp9.syn.service.erp9.ESService;
import com.yuanda.erp9.syn.service.erp9.impl.ESServiceImpl;
import com.yuanda.erp9.syn.util.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CountDownLatch;

/**
 * @ClassName Worker
 * @Description 子任务工作者
 * @Date 2022/11/15
 * @Author myq
 */
@Slf4j
public class ElasticSearchWorker<T> extends AbstractWorker<T> {

    // 数据集合
    private List<T> task;
    // 子任务执行情况结果集
    private ConcurrentHashMap<String, Object> resultMap;

    public ElasticSearchWorker(List<T> task, ConcurrentHashMap<String, Object> resultMap) {
        this.task = task;
        this.resultMap = resultMap;
    }


    /**
     * @Description: 将数据分批执行添加到数据库中
     * @Params:
     * @Return:
     * @Author: Mr.myq
     * @Date: 2022/11/1517:09
     */
    @Override
    void task() {
        String workerThreadName = Thread.currentThread().getName();
        try {
            log.info("**************开始同步es数据**************");
            ESService esService = SpringContextUtil.getBean("ESServiceImpl", ESServiceImpl.class);
            esService.addESData((List<CmsGoodsPojo>) task);
            log.info("***************结束同步es数据*************");
        } catch (Exception e) {
            e.printStackTrace();
            log.error("线程名称：{},同步es数据异常： {}", workerThreadName, e.toString());

            resultMap.put("errMsg", "worker run error : {" + e + "}");
            resultMap.put("errThreadName", workerThreadName);
            resultMap.put("type", "elasticsearch");
            throw new RuntimeException("Thread-Name: " + workerThreadName + "worker run error :{}");
        } finally {
            task.clear();
        }
    }

}

以上都是代码片段无法直接使用。最重要的核心思想

经过测试插入700W数据（三张表需要2分45秒的时间）
如果需要源码的小伙伴，请评论、私信联系我。如果对您有帮助，希望您三连。
MySQL高级技巧：批量插入百万级数据量
master_chenchen的博客
06-10 2454
正确的操作方法不仅能大幅提高数据插入效率，还能有效保障数据的安全性和完整性。批量插入是数据库操作中的常见需求，掌握高效安全的批量插入技巧对于数据库管理员和开发者来说至关重要。即每次只插入一定数量的记录，然后提交事务，接着进行下一批的插入。批量插入是指在一次数据库操作中，插入多行数据的过程。相对于单条插入，批量插入可以减少数据库事务的次数，提高数据处理的效率。在执行批量插入操作时，应密切监控系统的CPU、内存以及磁盘IO等资源使用情况，确保操作不会引起系统过载。对于需要插入百万级别数据量的情况，推荐使用。
c mysql 批量插入_c#之mysql四种带事务批量插入
weixin_32256355的博客
01-19 1184
前言对于像我这样的业务程序员开发一些表单内容是家常便饭的事情，说道表单 我们都避免不了多行内容的提交，多行内容保存，自然要用到数据库，如果循环打扰我数据库，数据库也会觉得很累，从而增加数据库服务器压力。所以小子不才，根据平时经验总结了一下用到的批量插入的方法代码。本文是针对c#语言开发，数据库是mysql数据库。当然我这是单片机的本地电脑运行，跟服务器上有很大区别，到了服务器上可能跑的比这快的多，...
MySQL插入一亿条数据
weixin_goodDreamLd的博客
12-31 2235
1、创建数据表 use test; CREATE TABLE `large_user` ( `id` bigint(20) DEFAULT NULL, `name` varchar(64) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE= 2、创建用户表 use test; CREATE TABLE `large_user_log`...
MySQL快速插入亿级数据
downdawn
10-18 2960
MySQL快速插入亿级数据 前言 通常我们会有这样的需求：构造数据，大批量插入数据库，以供后续处理。如果是几万几十万的数据那还好说，但如果是上千万上亿的数据，那么我们对速度的追求就更加迫切。 这里我们利用Python来操作MySQL（或者PostgreSQL），采取多进程多协程的方式，实现大批量数据快速插入。 数据库配置 # 查看bulk_insert_buffer_size大小，默认是8M SH...
MySQL怎么快速插入1亿条数据
m0_59236433的博客
12-10 1008
综上，提高MySQL插入效率主要可通过调整数据库配置、选择适合的存储引擎以及运用批量插入策略等方式实现。在实际应用中，尤其是在使用ORM框架进行数据操作时，应合理选择并充分利用批量插入功能，以最大程度提升插入效率。省流连接数据时，数据源配置文件url加上数据量很大时，使用mybatisPlus或MybatisFlex提供的saveBatch即可。
1亿条数据批量插入 MySQL，哪种方式最快？
2301_77463738的博客
07-11 1764
能够看到，在开启事务下 JDBC直接处理 和 JDBC批处理 均耗时更短。Mybatis 轻量级框架插入 , mybatis在我这次实验被黑的可惨了，哈哈。实际开启事务以后，差距不会这么大（差距10倍）。大家有兴趣的可以接着去测试JDBC直接处理，在本次实验，开启事务和关闭事务，耗时差距5倍左右，并且这个倍数会随着数据量的增大而增大。因为在未开启事务时，更新10000条数据，就得访问数据库10000次。导致每次操作都需要操作一次数据库。
1亿条数据批量插入 MySQL，哪种方式最快
热门推荐
终码一生
06-01 1万+
这几天研究mysql优化中查询效率时，发现测试的数据太少（10万级别），利用 EXPLAIN 比较不同的 SQL 语句，不能够得到比较有效的测评数据，大多模棱两可，不敢通过这些数据下定论。所以通过随机生成人的姓名、年龄、性别、电话、email、地址 ，向mysql数据库大量插入数据，便于用大量的数据测试 SQL 语句优化效率。、在生成过程中发现使用不同的方法，效率天差万别。下图一共三千三百万数据：在数据量在亿级别时，别点下面按钮，会导致Navicat持续加载这亿级别的数据，导致电脑死机。~觉着自己电脑配置不
mysql 10w级别的mysql数据插入
09-11
综上所述，对于“mysql 10w级别的mysql数据插入”，通过批量插入、优化PHP处理、合理配置数据库参数以及考虑事务和索引管理等多种方法，可以显著提高数据插入的效率，从而在处理大规模数据时保持高效运行。
深入浅出mysql海量数据批量更新插入、批量查询
小伍的程序之路
06-05 3104
这个参数后的抓包截图，jdbc客户端重写了sql语句，它把多个sql语句用分号分隔连接在一起，形成一个大sql脚本， 然后将这个sql脚本一次性发送到server端，最后接收到了大量的response响应。mysql提供了批量写入的方法，将大批量的sql脚本一批次发送到服务端，减少IO次数，然后统一一次执行sql。理论上，上面的sql是批量提交到mysql server统一执行的，但在默认情况下实际上它还是一条条执行命令，要真正的批量执行sql，需要在jdbc连接url加上。，重启项目再执行接口。
MySQL死锁套路之唯一索引下批量插入顺序不一致
12-16
【MySQL死锁套路之唯一索引下批量插入顺序不一致】 死锁是数据库管理系统中常见的问题，它发生在两个或多个事务在执行过程中，因争夺资源而造成的一种相互等待的现象，若无外力干涉它们将无法继续执行。在MySQL中，...
mysql read_buffer_size 设置多少合适
09-10
很多朋友都会问mysql read_buffer_size 设置多少合适，其实这个都是根据自己的内存大小等来设置的
mysql技巧：提高插入数据(添加记录)的速度
12-15
问题描述： 普通台式机，采集数据，表中已经有>1000万数据量。 采集回来的数据插入表中的时候很慢，每条约100毫秒。 解决方法： 1、加大mysql配置中的bulk_insert_buffer_size，这个参数默认为8M bulk_insert_buffer_size=100M 2、改写所有insert语句为insert delayed 这个insert delayed不同之处在于：立即返回结果，后台进行处理插入。 还有一个技巧是在一跳insert中插入多条数据，类似insert into tablename values(‘xxx’,’xxx’),(‘yyy’,’yyy’),(‘zzz’
Mysql数据库快速插入亿级数据
ma_anjun的博客
03-14 8446
Mysql数据库快速插入亿级数据 接手一个项目，该项目运行了两三年了。接手的时候，只有一个部署文档和全部代码，再没有其他文档了，也没有其他任何人了解这个项目。好吧，试着深入了解吧。代码在测试环境跑来了，整个项目算是看得七七八八了。去线网看看，我靠，mysql数据库数据已经好几十个G了。定位到其中一张表t_send_message_send，发送短信的记录表，已经一亿多条数据了，占用空间四十多个G，...
一亿条数据的更新方法
06-27 1157
现在有一事实表,表上按时间分区,总共有1亿多条,有几个分区较大,4000多万数据.现在需要update这个表中的其中4个列,当这几个列为空时更新为-1.我实在是担心这个更新的速度,各位有没有什么好的办法啊select count...
10秒内插入亿量级数据，我滴妈，真牛逼...
You’re a wanderer just like me
03-25 6415
一、前言 相信很多小伙伴都遇到过大数据量导入DB，或者说数据库分表带来的数据批量插入，这时候会遇到个问题就是插入效率低的问题，例如博主前段时间在导入几千万条万条数据时，过程耗时***秒，具体多少？反正没有继续等，直接崩溃。 于是乎对代码做了点优化，最后做到了10s内！！！ 开始表演，mock下过程 原始写法，用的正常mybatisplus插入： public void insertMore() { long beginTime = System.currentTimeMillis();.
mysql批量插入大量数据
最新发布
05-27
### 关于 MySQL 批量插入大量数据的最佳实践 在实际生产环境中，当需要向 MySQL 数据库中插入大量的数据时，采用高效的批量插入方法能够显著提升性能并减少资源消耗。以下是针对此场景的一些最佳实践： #### 1. ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

心有城府，腹有良谋

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值