mysql批量插入亿级别的数据

原创

已于 2023-01-05 10:02:51 修改 · 843 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java #mysql #elasticsearch

于 2023-01-05 00:00:00 首次发布

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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 Concurren