mysql函数大批量插入数据+java多线程并发案例

当前需要做个测试，mysql数据库需要5000万数据来完成性能测试，于是我们写了个函数来完成这个功能。

函数定义如下：

#批量插入数据库的函数
BEGIN
    #定义需要插入数据库表中的字段；
 
    DECLARE id BIGINT(20);
    DECLARE phone_number varchar(11);
 
    #定义变量控制插入数据库表的行数
    DECLARE sid bigint(20) DEFAULT 1;
    DECLARE count bigint(20);
    declare total bigint(20) DEFAULT 50000000;

    #开启循环判断
    while sid < total do
        SET count = sid + 100000; //每次插入数据库条数，防止条数太多提交失败；

    #开始事务
    start transaction;
    WHILE (sid < count) DO //循环插入数据库数据
    SET id = sid;
    SET phone_number = CONCAT(1, ROUND(ROUND(RAND(),10)*10000000000));
    INSERT INTO t_contact_phone(sid,phone_number)
    VALUES(id,phone_number);
    #累计行数
    SET sid = sid+1;
    END WHILE;
    commit;  //提交事务
  end while; //结束循环
END

执行上述函数即可创建5000万条数据在测试库用于测试。

当我们需要同时执行大批量操作，那如果依然顺序单线程执行，会比较耗时，为了提高效率，我们需要通过多线程异步方式来提高效率；

比方说大批量数据的同步方案，只能通过批量查询+多线程+异步的方式来完成。才能更短的时间内更高效的完成任务。

public class SynchronizationData {

private static final Logger logger = LoggerFactory.getLogger(SynchronizationData.class);@Autowiredprivate ApplicationSumaryMapper applicationSumaryMapper;//需要同步的数据表（mybatis完成）@Autowiredprivate ContactPhoneMapper contactPhoneMapper;//需要插入的数据表一（mybatis完成）@Autowiredprivate MiddleFieldMapper middleFieldMapper; //需要插入的数据表二（mybatis完成）private static ExecutorService executor = Executors.newFixedThreadPool(2); //定义线程池用于多线程执行。//需要同步总条数private long totalNumber = 0;//需要同步的最大进件id;private long maxSid = 0;//当前已经同步的sidprivate long currentSid = 0;private long currentPage = 0;private long maxPage = 0;public void startSynData() {    //获取当前系统中最大的sid    maxSid = applicationSumaryMapper.getMaxSid();    logger.info("需要同步的最大进件id(SID)为:", maxSid);    //根据sid获取总条数    totalNumber = applicationSumaryMapper.getTotalNumber(maxSid);    logger.info("需要同步的进件总数为:", totalNumber);    maxPage = totalNumber / 1000 + 1;    logger.info("最大页数为:", maxPage);    //判断能否开始执行，执行时间为晚上12点到早上8点    boolean synStart = isSynStart(currentSid, maxSid);    if (synStart) {        //开始分页查询        for (long i = 0; i <= maxPage;) {            List<ContactPhone> contactList = new ArrayList<ContactPhone>();            List<MiddleField> middleList = new ArrayList<MiddleField>();            currentPage = i;

            //查询需要同步的数据集            List<ApplicationSumary> synDataList = applicationSumaryMapper.getSynData(currentPage, currentSid, maxSid);            //遍历集合组装数据            prepareDataForBatchInsert(contactList, middleList, synDataList);            i = batchAddData(i, contactList, middleList, synDataList);        }    } else {        //不符合同步条件，啥也不做    }}@Transactional //通过事务控制同成功，同失败。private long batchAddData(long i, List<ContactPhone> contactList, List<MiddleField> middleList, List<ApplicationSumary> synDataList) {    //异步批量插入contactList    Future<Integer> contactNumber = executor.submit(new contactPhoneCallable(contactList));    //异步批量插入middleList    Future<Integer> middleNumber = executor.submit(new middleFiledCallable(middleList));    try {        Integer number1 = contactNumber.get();        Integer number2 = middleNumber.get();        //如果都成功则继续        if (number1 != -1 && number2 != -1) {         i++;        } else {            //失败则继续当前页执行        }    } catch (Exception e) {        e.printStackTrace();        //异常并记录当前的sid        logger.info("异常最小的sid为:"+synDataList.get(0).getSid());        logger.info("异常最大的sid为:"+synDataList.get(synDataList.size()-1).getSid());    }    return i;}//遍历要同步的数据集合组装要批量插入的数据集合private void prepareDataForBatchInsert(List<ContactPhone> contactList, List<MiddleField> middleList, List<ApplicationSumary> synDataList) {    for (ApplicationSumary application : synDataList            ) {        MiddleField middleField = new MiddleField(application.getSid(), application.getUnitAddressCode(), application.getCustunitAddress(), application.getRegistAddressCode(), application.getRegistAddress()                , application.getLiveAddressCode(), application.getLiveAddress(), application.getCustQq(), application.getCustWechatcode(), application.getAppStatus(), application.getAppStatusCode());        middleList.add(middleField);        //客户电话号        if (application.getPhoneNubmer() != null) {            ContactPhone phoneForSelf = new ContactPhone();            contactList.add(phoneForSelf);        };        //销售人员电话        if (application.getSalesPhone() != null) {            ContactPhone phoneForSales = new ContactPhone();            contactList.add(phoneForSales);        };        //家庭联系人电话        if (application.getFamilyPhone() != null) {            ContactPhone phoneForFamily = new ContactPhone();            contactList.add(phoneForFamily);        };        //紧急联系人电话        if (application.getOtherContactphone() != null) {            ContactPhone phoneForOther = new ContactPhone();            contactList.add(phoneForOther);        };        //工作证明人电话        if (application.getFalyPhone() != null) {            ContactPhone phoneForFaly = new ContactPhone();            contactList.add(phoneForFaly);        };    }}//判断是否可以开始同步，这里只允许晚上0点到早上八点允许执行private boolean isSynStart(long currentSid, long maxSid) {    Date date = new Date();    int hours = date.getHours();    if ((hours >= 0 || hours <= 7) && currentSid < maxSid) {        return true;    } else {        return false;    }}

//多线程执行任务一：给联系人表中批量插入数据class contactPhoneCallable implements Callable<Integer>{     private List<ContactPhone> runList;    public contactPhoneCallable(List<ContactPhone> runList) {        this.runList = runList;    }        @Override    public Integer call() {        try {            int addNumber = contactPhoneMapper.batchAdd(runList);            return addNumber;        } catch (Exception e) {            return -1;        }    }}//多线程执行的任务二；给中间信息表中批量插入数据class middleFiledCallable implements Callable<Integer>{    private List<MiddleField> runList;    public middleFiledCallable(List<MiddleField> runList) {        this.runList = runList;    }    @Override    public Integer call() {        try {            int middleNumber = middleFieldMapper.batchAdd(runList);            return middleNumber;        } catch (Exception e) {            return -1;        }    }}

}

最后给出分页查询和批量插入的sql语句：

mybatis定义的方法：

List<ApplicationSumary> getSynData(@Param("currentPage") long currentPage, @Param("currentSid") long currentSid, @Param("maxSid") long maxSid);

分页查询sql:

<select id="getSynData" resultMap="BaseResultMap">  select  <include refid="Base_Column_List" />  from t_application_sumary  where sid<![CDATA[ <= ]]>#{maxSid} and sid> #{currentSid} limit #{currentPage},#{currentPage+1000}</select>

mybatis定义的方法：

int batchAdd(List<ContactPhone> contactList);

批量插入sql:

<insert id="batchAdd" parameterType="java.util.List">  insert into t_contact_phone (sid,phone_type,phone_number,phone_name)  values  <foreach collection="list" item="item" index="index" separator=",">    (#{item.sid,jdbcType=BIGINT}, #{item.phoneType,jdbcType=VARCHAR}, #{item.phoneNumber,jdbcType=char},    #{item.phoneName,jdbcType=VARCHAR})  </foreach></insert>