TiDB 的数据加载性能调优方案

作者：heiheipp 、Gin

一、数据加载调优项

1. 调整数据加载程序的 jdbc 连接串：JDBC:mysql://{TiDBIP}:{TiDBPort}/{DBName}?characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSqlLimit=10000000&useConfigs=maxPerformance&rewriteBatchedStatements=true&defaultfetchsize=-2147483648
2. 将单行 insert 改为 batch insert 方式写入数据，形如 insert into table values(),(),(),()......,();
3. 增加配置参数用于灵活调整 batch insert 语句的 batch size，最佳实践的 batch size 为 100~300 之间，也就是每个 insert 语句写入 100~300 行记录，每个 insert 作为一个事务，自动提交。
4. batch insert 的并发可以调整到 64 或更高，同样需要增加配置参数用于灵活调整并发数。
5. 并发调高了，但在 TiDB 上观察连接数不够，可能是连接池设置问题，参考本文调整连接池最大连接数：专栏 - 使用 TiDB 时的连接池和负载均衡器配置策略 | TiDB 社区
6. 经常需要进行大量数据加载的表，需要设置为非聚簇索引表，并在表结构上增加 SHARD_ROW_ID_BITS 和 PRE_SPLIT_REGIONS 以避免写入热点，参考官方文档进行调整 TiDB 热点问题处理 | PingCAP 文档中心
7. 先创建索引，再加载数据。

二、MyBatis 的 batch insert 示例代码

1. 配置信息

程序基于 Sprint Boot 框架，在工程 resources 目录下的 application.yml 或 properties 文件中指定可被 spring context 获取的batch 提交参数，该参数用于程序中控制每一批提交的行数，如下图所示：

图1

2. 程序内容

2.1 获取配置

通过 spring 的 @Value或 @ConfigurationProperties 注解获取上述文件中的控制参数，本示例采用的是后者，即通过单独的参数实体类进行的数据封装，如下图所示：

图2

参考类文件 CtiqTransMainTableConfigModel.java：

package com.heiheipp.dataprepare.model;

import com.heiheipp.common.config.AbstractConfigModel;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @author heiheipp
 * @version 1.0
 * @className CtiqTransMainTableConfigModel
 * @desc TODO
 * @date 2022/3/16 17:05
 */
@Data
@Component("ctiqTransMainTableConfigModel")
@ConfigurationProperties(prefix = "ctiq.config.transmaintable")
public class CtiqTransMainTableConfigModel extends AbstractConfigModel {

    /**
     * 客户数量
     */
    private int custNum;

    /**
     * 每个客户的卡号数量
     */
    private int perCustCardNums;

    /**
     * 客户每日交易笔数
     */
    private int custTransNumEveryday;

    /**
     * 交易日期跨度
     */
    private int days;

    /**
     * 交易起始日期
     */
    private String startDay;

    /**
     * 交易终止日期
     */
    private String endDay;

    /**
     * 数据库单批次提交数量
     */
    private int batchNum;

    /**
     * 客户类型
     */
    private String custType;

    /**
     * 个人客户号前缀
     */
    private String personalCustIdPrefix;

    /**
     * 对公客户号前缀
     */
    private String companyCustIdPrefix;

    /**
     * 客户号长度
     */
    private int custIdLength;

    /**
     * 卡号前缀
     */
    private String cardBin;

    /**
     * 卡号长度
     */
    private int cardLength;

    /**
     * 账号前缀
     */
    private String accountPrefix;

    /**
     * 账号长度
     */
    private int accountLength;

    /**
     * 获取配置模型描述
     * @return
     */
    @Override
    public String getConfigModelDesc() {
        return "真实场景交易基础信息主表";
    }

    /**
     * 获取总处理数量
     * @return
     */
    @Override
    public int getTotalNums() {
        return getCustNum();
    }

    /**
     * 获取表头文件名
     * @return
     */
    @Override
    public String getFileHeaderName() {
        return "test_table_1_header.csv";
    }

    /**
     * 获取表文件名
     * @return
     */
    @Override
    public String getTargetFileName() {
        return "test_table_1.csv";
    }
}

2.2 程序控制

在另外的造数程序中，通过获取上述类的控制参数，通过循环控制的方式进行定制化批次大小的触发，即先循环构建每一条待插入的记录，并缓存到list对象中，再由数据库写入方法按照批次大小判断是否提交：

图3

图4

参考类文件 CtiqTransMainTableFutureTask.java：

package com.heiheipp.dataprepare.executor;

import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.SystemClock;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import com.github.javafaker.Faker;
import com.heiheipp.common.constant.ConfigConstant;
import com.heiheipp.common.context.RuntimeContext;
import com.heiheipp.common.context.SpringContextUtil;
import com.heiheipp.common.executor.AbstractFutureTask;
import com.heiheipp.common.mbg.model.TestTable1;
import com.heiheipp.common.service.TaskLogService;
import com.heiheipp.common.service.TestTable1Service;
import com.heiheipp.common.util.DataBuildUtil;
import com.heiheipp.common.util.DateTimeUtil;
import com.heiheipp.common.constant.DataModelConstant;
import com.heiheipp.dataprepare.model.CtiqTransMainTableConfigModel;
import com.heiheipp.dataprepare.service.impl.CTIQDataPrepareServiceImpl;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicLong;

import lombok.extern.slf4j.Slf4j;

/**
 * @author heiheipp
 * @version 1.0
 * @className CtiqTransMainTableFutureTask
 * @desc TODO
 * @date 2022/3/16 16:58
 */
@Slf4j
public class CtiqTransMainTableFutureTask extends AbstractFutureTask<String> {

    private int targetType;

    private String fileLocation;

    private int threadCustNums;

    private long threadTotalNums;

    private int batchNums;

    private TaskLogService taskLogService;

    private TestTable1Service testTable1Service;

    private Map<String, Object> runtimeDatas = new ConcurrentHashMap<>();

    private String subThreadId;

    private AtomicLong processedNums = new AtomicLong(0L);

    private int committedRows = 0;

    private int startOffset;

    private CtiqTransMainTableConfigModel configModel;

    private Faker faker = new Faker(Locale.CHINA);

    private boolean isFirstLine;

    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

    private Date start;

    private Date end;

    private long dayBetween = 1L;

    private boolean isRegisterDBLog;

    /**
     * 构造函数
     *
     * @param parentThreadId
     * @param threadCustNums
     * @param threadOrder
     * @param configModel
     */
    public CtiqTransMainTableFutureTask(long parentThreadId, int threadCustNums, int threadOrder,
                                        CtiqTransMainTableConfigModel configModel) {
        this.parentThreadId = String.valueOf(parentThreadId);
        this.threadCustNums = threadCustNums;
        this.taskLogService = SpringContextUtil.getBean(TaskLogService.class);
        this.testTable1Service = SpringContextUtil.getBean(TestTable1Service.class);
        this.configModel = configModel;
        this.batchNums = this.configModel.getBatchNum();

        // 参数计算
        this.startOffset = this.threadCustNums * configModel.getPerCustCardNums() * threadOrder + 1;
        this.targetType = SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).getTargetType();
        this.fileLocation = SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).getFileLocation();
        this.isFirstLine = !SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).isFileMerge();
        this.isRegisterDBLog = SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).isRegisterDBLog();

        // 以startDay和endDay计算要循环的天数
        if (!StrUtil.isEmpty(configModel.getStartDay()) && !StrUtil.isEmpty(configModel.getEndDay())) {
            try {
                this.start = this.dateFormat.parse(configModel.getStartDay());
                this.end = this.dateFormat.parse(configModel.getEndDay());

                // 日期校验
                if (this.start.compareTo(this.end) > 0) {
                    log.error("起始日期不能大于终止日期");
                    throw new RuntimeException("起始日期不能大于终止日期");
                }

                // 计算时间差
                this.dayBetween = DateUtil.between(this.start, this.end, DateUnit.DAY) + 1L;
            } catch (ParseException e) {
                log.error("子线程[{}]解析起始、终止时间异常", this.subThreadId);
                e.printStackTrace();
                throw new RuntimeException("解析起始、终止时间异常");
            }
        } else if (configModel.getDays() > 0) {
            this.dayBetween = configModel.getDays();
        }

        // 计算当前子线程要处理的总记录数
        this.threadTotalNums = (threadCustNums * configModel.getCustTransNumEveryday()) * this.dayBetween;
    }

    /**
     * 任务执行方法
     *
     * @return
     */
    @Override
    protected String submit(long subThreadId) {
        String result = "";
        this.subThreadId = String.valueOf(subThreadId);
        long startTime = System.currentTimeMillis();

        log.info("Sub thread[{}] start, threadTotalNums is {}, custNums is {}, duration is {} days, batchNums is {}, startOffset is {}, isRegisterDBLog is {}.",
                this.subThreadId, this.threadTotalNums, this.threadCustNums, this.dayBetween,
                this.batchNums, this.startOffset, this.isRegisterDBLog);

        // 计算公共变量
        String fileName = null;
        if (this.targetType == 2) {
            fileName = "boc_poc.test_table_1.0" + this.subThreadId + ".csv";
            this.fileLocation = this.fileLocation.equalsIgnoreCase("default") ?
                    new File(".").getAbsolutePath() : this.fileLocation;
            this.fileLocation += File.separator + fileName;
            log.info("Sub thread[{}] write data to file[{}].", this.subThreadId, this.fileLocation);

            // 将文件名写入主线程
            SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).getTmpFileNameList().add(this.fileLocation);
        }

        // 处理业务公共信息
        int custIdFillLength;
        String custIdPrefix = null;
        switch (this.configModel.getCustType()) {
            case "Personal":
                custIdFillLength = this.configModel.getCustIdLength() -
                        this.configModel.getPersonalCustIdPrefix().length() - DataModelConstant.PROVINCE_LENGTH;
                custIdPrefix = this.configModel.getPersonalCustIdPrefix();
                break;
            case "Company":
                custIdFillLength = this.configModel.getCustIdLength() -
                        this.configModel.getCompanyCustIdPrefix().length() - DataModelConstant.PROVINCE_LENGTH;
                custIdPrefix = this.configModel.getCompanyCustIdPrefix();
                break;
            default:
                custIdFillLength = 0;
                custIdPrefix = "";
                break;
        }

        int cardNoFillLength = this.configModel.getCardLength() - this.configModel.getCardBin().length();
        String cardNoPrefix = this.configModel.getCardBin();
        int accountFillLength = this.configModel.getAccountLength() - this.configModel.getAccountPrefix().length();
        String accountPrefix = this.configModel.getAccountPrefix();

        // 登记任务开始日志
        if (isRegisterDBLog) {
            recordTask(ConfigConstant.TaskStatusEnum.START);
        }

        // 制造数据
        List<TestTable1> testTable1s = new ArrayList<>();
        TestTable1 testTable1 = null;
        int currentPercentage = 0, prevPercentage = 0;
        try {
            for (int i = 0; i < this.threadCustNums; i++) {
                // 按单客户每次交易笔数制造数据
                buildMultiTestTable1(testTable1s, testTable1, custIdPrefix, cardNoPrefix, accountPrefix, i,
                        custIdFillLength, cardNoFillLength, accountFillLength);

                // 数据写入操作
                if (this.processedNums.get() < this.threadTotalNums) {
                    writeData(testTable1s, false);
                } else {
                    writeData(testTable1s, true);
                }

                // 登记任务处理中日志
                prevPercentage = currentPercentage;
                currentPercentage = DataBuildUtil.getPercentge(this.processedNums.get(), this.threadTotalNums);
                if (DataBuildUtil.getPercentageWtihTens(this.processedNums.get(), this.threadTotalNums) &&
                        prevPercentage < currentPercentage) {
                    if (isRegisterDBLog) {
                        recordTask(ConfigConstant.TaskStatusEnum.PROCESSING);
                    }
                    log.info("Sub thread {} has been processing {}%, processedNums is {}, totalNums is {}, execution time is {}.",
                            this.subThreadId, currentPercentage, this.processedNums.get(), this.threadTotalNums,
                            SystemClock.now() - startTime);
                }
            }

            // 补提交剩余部分
            writeData(testTable1s, true);
        } catch (Exception e) {
            log.error("Sub thread {} error, and execution time is {}ms.", this.subThreadId,
                    System.currentTimeMillis() - startTime);
            e.printStackTrace();

            // 登记任务失败信息
            if (isRegisterDBLog) {
                recordTask(ConfigConstant.TaskStatusEnum.ERROR);
            }
        } finally {
            log.info("Sub thread {} finish, and execution time is {}ms.", this.subThreadId,
                    System.currentTimeMillis() - startTime);

            // 登记任务结束信息
            if (isRegisterDBLog) {
                recordTask(ConfigConstant.TaskStatusEnum.SUCCESS);
            }

            // 清理线程上下文
            RuntimeContext.clearRuntimeData();

            // 计数器操作
            CTIQDataPrepareServiceImpl.getCountDownLatch().countDown();
        }
        return result;
    }

    /**
     * 构建任务日志数据
     */
    private void buildRuntimeDatas(ConfigConstant.TaskStatusEnum taskStatusEnum, Object... args) {
        switch (taskStatusEnum) {
            case START:
                // 登记开始任务
                runtimeDatas.put(ConfigConstant.PARENT_THREAD_ID_KEY, this.parentThreadId);
                runtimeDatas.put(ConfigConstant.SUB_THREAD_ID_KEY, subThreadId);
                runtimeDatas.put(ConfigConstant.TASK_TYPE_KEY, ConfigConstant.TaskTypeEnum.DATA_PREPARE.getTypeDesc());
                runtimeDatas.put(ConfigConstant.TASK_CONTENT_KEY, getTaskContent());
                runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.START.getStatus());
                runtimeDatas.put(ConfigConstant.TASK_TOTAL_NUMS_KEY, this.threadTotalNums);
                runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, 0L);
                runtimeDatas.put(ConfigConstant.TASK_CREATE_TIME_KEY, SystemClock.now());
                runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now());
                runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.START);
                break;
            case PROCESSING:
                // 更新处理中任务
                runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.PROCESSING.getStatus());
                runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, processedNums.get());
                runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.PROCESSING);
                runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now());
                break;
            case ERROR:
                // 任务异常
                runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.ERROR.getStatus());
                runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, processedNums.get());
                runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.ERROR);
                runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now());
                break;
            case SUCCESS:
                // 任务成功结束
                runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.SUCCESS.getStatus());
                runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, processedNums.get());
                runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.SUCCESS);
                runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now());
                break;
            default:
                break;
        }
    }

    /**
     * 获取任务类型
     *
     * @return
     */
    private String getTaskContent() {
        return "TEST_TABLE_1表数据准备";
    }

    /**
     * 登记任务日志
     *
     * @param taskStatusEnum
     */
    private void recordTask(ConfigConstant.TaskStatusEnum taskStatusEnum) {
        switch (taskStatusEnum) {
            case START:
                // 登记任务开始日志
                buildRuntimeDatas(ConfigConstant.TaskStatusEnum.START);
                break;
            case PROCESSING:
                // 登记任务处理中日志
                buildRuntimeDatas(ConfigConstant.TaskStatusEnum.PROCESSING);
                break;
            case ERROR:
                // 登记任务失败信息
                buildRuntimeDatas(ConfigConstant.TaskStatusEnum.ERROR);
                break;
            case SUCCESS:
                // 登记任务结束信息
                buildRuntimeDatas(ConfigConstant.TaskStatusEnum.SUCCESS);
                break;
            default:
                break;
        }

        RuntimeContext.setRuntimeDatas(runtimeDatas);

        try {
            taskLogService.recordTaskLog();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 构建TestTable1对象
     * @param testTable1
     * @param custId
     * @param cardNoArray
     * @param accountArray
     * @param custName
     * @param startLongTime
     * @param endLongTime
     */
    private void buildTestTable1(TestTable1 testTable1, String custId, String[] cardNoArray, String[] accountArray,
                                 String custName, long startLongTime, long endLongTime) {
        int num = DataBuildUtil.getIntegerRandom(this.configModel.getPerCustCardNums());

        testTable1.setColumn1(new BigDecimal(IdUtil.getSnowflakeNextIdStr()));
        testTable1.setColumn2(DataBuildUtil.getRandomWithLength(3));
        //testTable1.setColumn3(DataBuildUtil.getRandomWithLength(5));
        testTable1.setColumn3(String.valueOf(RandomUtil.randomInt(1, 10000)));
        testTable1.setColumn4(accountArray[num]);
        testTable1.setColumn5(new Timestamp(DateTimeUtil.randomLongTime(startLongTime, endLongTime)));
        testTable1.setColumn6(DataBuildUtil.getRandomWithLength(8));
        testTable1.setColumn7(DataBuildUtil.getRandomWithLength(8));
        testTable1.setColumn8(DataBuildUtil.getRandomWithLength(5));
        testTable1.setColumn9(testTable1.getColumn4());
        testTable1.setColumn10("0001");
        //testTable1.setColumn11(Integer.valueOf(DataBuildUtil.getRandomWithLength(3)));
        testTable1.setColumn11(RandomUtil.randomInt(1, 999));
        testTable1.setColumn12(Integer.valueOf(DataBuildUtil.getRandomWithLength(2)));
        testTable1.setColumn13(DataBuildUtil.getRandomWithLength(12));
        testTable1.setColumn14(UUID.randomUUID().toString().replaceAll("-", ""));
        testTable1.setColumn15(DataBuildUtil.getRandomWithLength(4));
        testTable1.setColumn16(DataBuildUtil.getRandomWithLength(4));
        testTable1.setColumn17(DataBuildUtil.generateMsg("某", 12, "产品"));
        testTable1.setColumn18(DataBuildUtil.generateMsg("某", 16, "文书合同"));
        testTable1.setColumn19("1");
        testTable1.setColumn20("01");
        testTable1.setColumn21("001");
        testTable1.setColumn22(cardNoArray[num]);
        testTable1.setColumn23("01");
        testTable1.setColumn24("1");
        testTable1.setColumn25(DataBuildUtil.getTranType());
        testTable1.setColumn26(DataBuildUtil.getTranTypeDesc(testTable1.getColumn25()));
        testTable1.setColumn27(DateUtil.date(testTable1.getColumn5().getTime()).toTimeStr());
        testTable1.setColumn28(DataBuildUtil.getRandomWithLength(7));
        testTable1.setColumn29(DataModelConstant.DEFAULT_BRANCH_NO);
        testTable1.setColumn30(DataModelConstant.DEFAULT_BRANCH_NAME);
        testTable1.setColumn31(DateUtil.parse("2021-05-01", "yyyy-MM-dd"));
        testTable1.setColumn32("0");
        testTable1.setColumn33("112233");
        testTable1.setColumn34("112233");
        testTable1.setColumn35("01");
        testTable1.setColumn36("提示码");
        testTable1.setColumn37(DataBuildUtil.getChannel());
        testTable1.setColumn38(DateUtil.date(testTable1.getColumn5().getTime()).toSqlDate());
        testTable1.setColumn39(testTable1.getColumn13());
        testTable1.setColumn40("156");
        testTable1.setColumn41("CNY");
        testTable1.setColumn42(NumberUtil.round("1.00", 2));
        testTable1.setColumn43(DataBuildUtil.getDebitOrCreditFlag());
        testTable1.setColumn44(DataBuildUtil.getChongzhengFlag(testTable1.getColumn43()));
        testTable1.setColumn45(DataBuildUtil.getRandomBigDecimal(10000, 2));
        testTable1.setColumn46(testTable1.getColumn45().add(new BigDecimal(100)));
        testTable1.setColumn47(testTable1.getColumn46());
        testTable1.setColumn48(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn49(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn50(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn51(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn52(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn53(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn54(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn55(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn56(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn57(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn58(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn59(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn60(DataBuildUtil.generateMsg("某", 30, "备注"));
        testTable1.setColumn61(DataBuildUtil.generateMsg("某", 15, "用途"));
        testTable1.setColumn62(DataBuildUtil.generateMsg("某", 30, "附言"));
        testTable1.setColumn63(DataBuildUtil.generateMsg("某", 30, "摘要"));
        testTable1.setColumn64("1");
        testTable1.setColumn65("现钞");
        testTable1.setColumn66(DataModelConstant.DEFAULT_PAYER_NAME);
        testTable1.setColumn67("1234567890123456789");
        testTable1.setColumn68("1234567890123456789");
        testTable1.setColumn69("1111");
        testTable1.setColumn70(Integer.valueOf(DataBuildUtil.getRandomWithLength(3)));
        testTable1.setColumn71(Integer.valueOf(DataBuildUtil.getRandomWithLength(2)));
        testTable1.setColumn72(DataModelConstant.DEFAULT_BRANCH_NAME);
        testTable1.setColumn73(DataModelConstant.DEFAULT_BRANCH_NO);
        testTable1.setColumn74(DataModelConstant.DEFAULT_PROVINCE_LH_NO);
        // 付款人姓名
        if ("D".equalsIgnoreCase(testTable1.getColumn43())) {
            testTable1.setColumn75(custName);
        } else {
            testTable1.setColumn75(DataModelConstant.DEFAULT_PAYER_NAME);
        }

        testTable1.setColumn76("1234567890123456789");
        testTable1.setColumn77("12345678901234567890001");
        testTable1.setColumn78("1111");
        testTable1.setColumn79(Integer.valueOf(DataBuildUtil.getRandomWithLength(3)));
        testTable1.setColumn80(Integer.valueOf(DataBuildUtil.getRandomWithLength(2)));
        testTable1.setColumn81(DataModelConstant.DEFAULT_BRANCH_NAME);
        testTable1.setColumn82(DataModelConstant.DEFAULT_BRANCH_NO);
        testTable1.setColumn83(DataModelConstant.DEFAULT_PROVINCE_LH_NO);
        testTable1.setColumn84(DataModelConstant.DEFAULT_RECEIVER_NAME);
        testTable1.setColumn85("9876543210987654321");
        testTable1.setColumn86("12345678901234567890001");
        testTable1.setColumn87("1111");
        testTable1.setColumn88(Integer.valueOf(DataBuildUtil.getRandomWithLength(3)));
        testTable1.setColumn89(Integer.valueOf(DataBuildUtil.getRandomWithLength(2)));
        testTable1.setColumn90(DataModelConstant.DEFAULT_BRANCH_NAME);
        testTable1.setColumn91(DataModelConstant.DEFAULT_BRANCH_NO);
        testTable1.setColumn92(DataModelConstant.DEFAULT_PROVINCE_LH_NO);
        // 收款人姓名
        if ("C".equalsIgnoreCase(testTable1.getColumn43())) {
            testTable1.setColumn93(custName);
        } else {
            testTable1.setColumn93(DataModelConstant.DEFAULT_RECEIVER_NAME);
        }

        testTable1.setColumn94("9876543210987654321");
        testTable1.setColumn95("98765432109876543210001");
        testTable1.setColumn96("1111");
        testTable1.setColumn97(Integer.valueOf(DataBuildUtil.getRandomWithLength(3)));
        testTable1.setColumn98(Integer.valueOf(DataBuildUtil.getRandomWithLength(2)));
        testTable1.setColumn99(DataModelConstant.DEFAULT_BRANCH_NAME);
        testTable1.setColumn100(DataModelConstant.DEFAULT_BRANCH_NO);
        testTable1.setColumn101(DataModelConstant.DEFAULT_PROVINCE_LH_NO);
        testTable1.setColumn102("2222");
        testTable1.setColumn103(DataBuildUtil.generateMsg("某", 2, "凭证"));
        testTable1.setColumn104("112233445566778899");
        testTable1.setColumn105("2222");
        testTable1.setColumn106(DataBuildUtil.generateMsg("某", 2, "产生凭证"));
        testTable1.setColumn107("112233445566778899");
        testTable1.setColumn108("998877665544332211");
        testTable1.setColumn109("1111");
        testTable1.setColumn110(Integer.valueOf(4));
        testTable1.setColumn111(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn112(DataModelConstant.ZERO_AMOUNT);
        testTable1.setColumn113(DataModelConstant.DEFAULT_ADDRESS_PHONE);
        testTable1.setColumn114(DataModelConstant.DEFAULT_ADDRESS_PHONE);
        testTable1.setColumn115("1122334455");
        testTable1.setColumn116("1122334455");
        testTable1.setColumn117("1122334455");
        testTable1.setColumn118("1122334455");
        testTable1.setColumn119("1122334455");
        testTable1.setColumn120("1122334455");
        testTable1.setColumn121(DataBuildUtil.generateMsg("某", 1, "系统"));
        testTable1.setColumn122("11111");
        testTable1.setColumn123(DateUtil.date(testTable1.getColumn5().getTime()).toSqlDate());
        testTable1.setColumn124("1111");
        testTable1.setColumn125("11111");
        testTable1.setColumn126("1");
        testTable1.setColumn127("1");
        testTable1.setColumn128("11112222");
        testTable1.setColumn129(DataModelConstant.DEFAULT_BRANCH_NO);
        testTable1.setColumn130("1");
        testTable1.setColumn131("1");
        testTable1.setColumn132("1");
        testTable1.setColumn133("1");
        testTable1.setColumn134(DateUtil.date(testTable1.getColumn5().getTime()).toSqlDate());
        testTable1.setColumn135("1");
        testTable1.setColumn136("1");
        testTable1.setColumn137("1");
        testTable1.setColumn138("1");
        testTable1.setColumn139("1");
        testTable1.setColumn140("1");
        testTable1.setColumn141("1");
        testTable1.setColumn142("1");
        testTable1.setColumn143("1");
        testTable1.setColumn144(custId);
        testTable1.setColumn145("1");
        testTable1.setColumn146("1");

        // 生成json字符串
        testTable1.setColumn151(DataModelConstant.DEFAULT_JSON_STRING);
    }

    /**
     * 构建TestTable1列表
     * @param testTable1s
     * @param testTable1
     * @param custIdPrefix
     * @param cardNoPrefix
     * @param accountPrefix
     * @param currentOffset
     * @param custIdFillLength
     * @param cardNoFillLength
     * @param accountFillLength
     */
    private void buildMultiTestTable1(List<TestTable1> testTable1s, TestTable1 testTable1, String custIdPrefix,
                                      String cardNoPrefix, String accountPrefix, int currentOffset,
                                      int custIdFillLength, int cardNoFillLength, int accountFillLength) {
        // 公共信息
        int offset = currentOffset * this.configModel.getPerCustCardNums();
        String custId = DataBuildUtil.generateCustId(custIdPrefix, this.startOffset, offset, custIdFillLength);
        String custName = this.faker.name().fullName();
        String[] cardNoArray = new String[this.configModel.getPerCustCardNums()];
        String[] accountArray = new String[this.configModel.getPerCustCardNums()];
        for (int k = 0; k < this.configModel.getPerCustCardNums(); k++) {
            cardNoArray[k] = DataBuildUtil.generateCardNo(cardNoPrefix, this.startOffset,
                    offset + k, cardNoFillLength);
            accountArray[k] = DataBuildUtil.generateAccount(accountPrefix, this.startOffset,
                    offset + k, accountFillLength);
        }

        // 外层按照日期循环
        for (int i = 0; i < (new Long(this.dayBetween)).intValue(); i++) {
            long startLongTime = DateUtil.offsetDay(this.start, i).getTime();
            long endLongTime = DateUtil.offsetDay(this.start, i + 1).getTime() - 1L;

            // 内层按照每日交易数循环
            for (int j = 0; j < this.configModel.getCustTransNumEveryday(); j++) {
                testTable1 = new TestTable1();
                buildTestTable1(testTable1, custId, cardNoArray, accountArray, custName, startLongTime, endLongTime);

                testTable1s.add(testTable1);

                // 递增总生成数量和待提交数量
                this.processedNums.incrementAndGet();
                this.committedRows++;
            }
        }
    }

    /**
     * 写文件操作
     * @param testTable1s
     * @param flag
     */
    private void writeData(List<TestTable1> testTable1s, boolean flag) {
        if (testTable1s.size() > 0) {
            if (this.targetType == 1) {
                //写入数据库
                if (this.committedRows >= this.batchNums) {
                    this.testTable1Service.batchInsert(testTable1s);
                    testTable1s.clear();
                    this.committedRows = 0;
                } else if (flag) {
                    this.testTable1Service.batchInsert(testTable1s);
                    testTable1s.clear();
                    this.committedRows = 0;
                }
            } else if (this.targetType == 2) {
                //写入csv文件
                this.testTable1Service.writeCsv(this.fileLocation, testTable1s, this.isFirstLine);
                testTable1s.clear();
                this.isFirstLine = false;

                //末尾追加空行
                if (flag) {
                    this.testTable1Service.writeCsv();
                }
            }
        }
    }
}

2.3 DAO 相关操作

本工程使用的是 MyBatis ORM 框架，在 mapper xml 文件中增加了 batchInsert sql，通过 foreach 方式进行的批量 insert，如下图：

图5

图6

对应的 mapper interface 类如下，红线部分主要是为了 mapper xml 中的参数定位：

图7

对应的 service 类通过 spring 事务的方式、以 PROPAGATION_REQUIRES_NEW 的事务传播方式进行的事务控制：

图8