[MyBatis] Сравнение эффективности нескольких пакетных вставок

задняя часть MyBatis

Существует три основных способа пакетной обработки данных:

  1. Повторно выполнять один оператор вставки
  2. foreachсоединениеsql
  3. пакетная обработка

1. Предварительная подготовка

на основеSpring Boot + Mysql, а чтобы опуститьget/set,использовалlombok, видетьpom.xml.

1.1 Структура таблицы

idИспользуйте автоинкремент базы данных.

DROP TABLE IF EXISTS `user_info_batch`;
CREATE TABLE `user_info_batch` (
                           `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
                           `user_name` varchar(100) NOT NULL COMMENT '账户名称',
                           `pass_word` varchar(100) NOT NULL COMMENT '登录密码',
                           `nick_name` varchar(30) NOT NULL COMMENT '昵称',
                           `mobile` varchar(30) NOT NULL COMMENT '手机号',
                           `email` varchar(100) DEFAULT NULL COMMENT '邮箱地址',
                           `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                           `gmt_update` timestamp NULL DEFAULT NULL COMMENT '更新时间',
                           PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT 'Mybatis Batch';

1.2 Конфигурационный файл проекта

Осторожно, вы могли обнаружить, что база данныхurlза которым следует абзацrewriteBatchedStatements=true, какая функция? Не волнуйтесь, я представлю его позже.

# 数据库配置
spring:
  datasource:
    url: jdbc:mysql://47.111.118.152:3306/mybatis?rewriteBatchedStatements=true
    username: mybatis
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
# mybatis
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: cn.van.mybatis.batch.entity

1.3 Класс сущности

@Data
@Accessors(chain = true)
public class UserInfoBatchDO implements Serializable {
    private Long id;

    private String userName;

    private String passWord;

    private String nickName;

    private String mobile;

    private String email;

    private LocalDateTime gmtCreate;

    private LocalDateTime gmtUpdate;
}

1.4 UserInfoBatchMapper 

public interface UserInfoBatchMapper {

    /** 单条插入
     * @param info
     * @return
     */
    int insert(UserInfoBatchDO info);

    /**
     * foreach 插入
     * @param list
     * @return
     */
    int batchInsert(List<UserInfoBatchDO> list);
}

1.5 UserInfoBatchMapper.xml 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.van.mybatis.batch.mapper.UserInfoBatchMapper">

  <insert id="insert" parameterType="cn.van.mybatis.batch.entity.UserInfoBatchDO">
    insert into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update)
    values (#{userName,jdbcType=VARCHAR}, #{passWord,jdbcType=VARCHAR},#{nickName,jdbcType=VARCHAR}, #{mobile,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{gmtCreate,jdbcType=TIMESTAMP}, #{gmtUpdate,jdbcType=TIMESTAMP})
  </insert>

  <insert id="batchInsert">
    insert into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.userName,jdbcType=VARCHAR}, #{item.passWord,jdbcType=VARCHAR}, #{item.nickName,jdbcType=VARCHAR}, #{item.mobile,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR}, #{item.gmtCreate,jdbcType=TIMESTAMP}, #{item.gmtUpdate,jdbcType=TIMESTAMP})
    </foreach>
  </insert>
</mapper>

1.6 Предварительные данные

Чтобы облегчить тестирование, несколько переменных извлекаются и загружаются заранее.

    private List<UserInfoBatchDO> list = new ArrayList<>();
    private List<UserInfoBatchDO> lessList = new ArrayList<>();
    private List<UserInfoBatchDO> lageList = new ArrayList<>();
    private List<UserInfoBatchDO> warmList = new ArrayList<>();
    // 计数工具
    private StopWatch sw = new StopWatch();
  • Для облегчения сборки данных извлекается общедоступный метод.
    private List<UserInfoBatchDO> assemblyData(int count){
        List<UserInfoBatchDO> list = new ArrayList<>();
        UserInfoBatchDO userInfoDO;
        for (int i = 0;i < count;i++){
            userInfoDO = new UserInfoBatchDO()
                    .setUserName("Van")
                    .setNickName("风尘博客")
                    .setMobile("17098705205")
                    .setPassWord("password")
                    .setGmtUpdate(LocalDateTime.now());
            list.add(userInfoDO);
        }
        return list;
    }
  • Прогрев данных
    @Before
    public void assemblyData() {
        list = assemblyData(200000);
        lessList = assemblyData(2000);
        lageList = assemblyData(1000000);
        warmList = assemblyData(5);
    }

2. Несколько раз выполнить один оператор вставки

Может "ленивые" программисты сделают это, очень просто, прямо в оригинальном синглеinsertвложенный в заявлениеforцикл.

2.1 Соответствующий интерфейс картографа

int insert(UserInfoBatchDO info);

2.2 Метод испытаний

Поскольку этот метод слишком медленный, данные сокращаются до2000полоска

@Test
public void insert() {
    log.info("【程序热身】");
    for (UserInfoBatchDO userInfoBatchDO : warmList) {
        userInfoBatchMapper.insert(userInfoBatchDO);
    }
    log.info("【热身结束】");
    sw.start("反复执行单条插入语句");
    // 这里插入 20w 条太慢了，所以我只插入了 2000 条
    for (UserInfoBatchDO userInfoBatchDO : lessList) {
        userInfoBatchMapper.insert(userInfoBatchDO);
    }
    sw.stop();
    log.info("all cost info:{}",sw.prettyPrint());
}

2.3 Время выполнения

  • первый раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
59887  100%  反复执行单条插入语句
  • второй раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
64853  100%  反复执行单条插入语句
  • в третий раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
58235  100%  反复执行单条插入语句

Вставьте так2000данные, среднее время трех исполнений:60991 ms.

три,foreachсоединениеSQL

3.1 Соответствующий интерфейс картографа

int batchInsert(List<UserInfoBatchDO> list);

3.2 Метод испытаний

И этот метод, и следующий метод используют20wтест данных.

@Test
public void batchInsert() {
    log.info("【程序热身】");
    for (UserInfoBatchDO userInfoBatchDO : warmList) {
        userInfoBatchMapper.insert(userInfoBatchDO);
    }
    log.info("【热身结束】");
    sw.start("foreach 拼接 sql");
    userInfoBatchMapper.batchInsert(list);
    sw.stop();
    log.info("all cost info:{}",sw.prettyPrint());
}

3.3 Время выполнения

  • первый раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
18835  100%  foreach 拼接 sql
  • второй раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
17895  100%  foreach 拼接 sql
  • в третий раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
19827  100%  foreach 拼接 sql

Вставьте так20wданные, среднее время трех исполнений:18852 ms.

4. Пакетная обработка

путьmapperа такжеxmlповторно использованный2.1.

4.1 rewriteBatchedStatementsпараметр

В начале теста я обнаружил, что он был изменен наMybatis BatchСпособ подачи не работает.По сути при вставке все равно вставляет записи по одной,и скорость намного меньше оригинала.foreachсоединениеSQLметод, который является очень ненаучным.

Позже я узнал, что если вы хотите выполнять его в пакетном режиме, подключитеURLВ строку необходимо добавить новый параметр:rewriteBatchedStatements=true

  • rewriteBatchedStatementsВведение параметра

MySqlизJDBCсвязанныйurlдобавитьrewriteBatchedStatementsпараметры и гарантии5.1.13Вышеупомянутая версия драйвера обеспечивает высокопроизводительную пакетную вставку.MySql JDBCДрайверы игнорируют по умолчаниюexecuteBatch()оператор, поместите набор пакетов, которые мы ожидаем выполнитьsqlЗаявление разбивается и отправляется по одномуMySqlВ базе данных пакетные вставки на самом деле представляют собой одиночные вставки, что напрямую снижает производительность. только поставитьrewriteBatchedStatementsпараметр установлен наtrue, драйвер поможет вам пакетное выполнениеSQL. Этот вариантINSERT/UPDATE/DELETEдействительны.

4.2 Подготовка партии

  • Ручной впрыскSqlSessionFactory 
    @Resource
    private SqlSessionFactory sqlSessionFactory;
  • тестовый код
@Test
public void processInsert() {
    log.info("【程序热身】");
    for (UserInfoBatchDO userInfoBatchDO : warmList) {
        userInfoBatchMapper.insert(userInfoBatchDO);
    }
    log.info("【热身结束】");
    sw.start("批处理执行 插入");
    // 打开批处理
    SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
    UserInfoBatchMapper mapper = session.getMapper(UserInfoBatchMapper.class);
    for (int i = 0,length = list.size(); i < length; i++) {
        mapper.insert(list.get(i));
        //每20000条提交一次防止内存溢出
        if(i%20000==19999){
            session.commit();
            session.clearCache();
        }
    }
    session.commit();
    session.clearCache();
    sw.stop();
    log.info("all cost info:{}",sw.prettyPrint());
}

4.3 Время выполнения

  • первый раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
09346  100%  批处理执行 插入
  • второй раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
08890  100%  批处理执行 插入
  • в третий раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
09042  100%  批处理执行 插入

Вставьте так20wданные, среднее время трех исполнений:9092 ms.

4.4 Если данных больше

Когда я расширяю данные до100wчас,foreachсоединениеsqlТо, как вставка не может быть выполнена, поэтому я могу проверить только время вставки партии.

При тестировании вам нужно только добавить [4.2] в тестовый код, чтобыlistНарезатьlageListПросто протестируйте.

  • первый раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
32419  100%  批处理执行 插入
  • второй раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
31935  100%  批处理执行 插入
  • в третий раз
-----------------------------------------
ms     %     Task name
-----------------------------------------
33048  100%  批处理执行 插入

Вставьте так100wданные, среднее время трех исполнений:32467 ms.

V. Резюме

пакетная вставка Объем данных Среднее время для трех исполнений
Вставьте один фрагмент данных в цикл 2000 60991 ms
foreachсоединениеsql 20w 18852 ms
пакетная обработка 20w 9092 ms
пакетная обработка 100w 32467 ms
  1. Хотя эффективность циклической вставки одного фрагмента данных чрезвычайно низка, объем кода очень мал, и его можно использовать при небольшом объеме данных, но запрещается использовать при большом объеме данных, и эффективность слишком низкая;
  2. foreachсоединениеsqlСпособ его использования, есть большие разделы операторов xml и sql для написания, в которых легко ошибиться.Хотя эффективность приемлема, он по-прежнему непригоден при работе с большим объемом данных, поэтому не рекомендуется используй это;
  3. Пакетное выполнение является рекомендуемой практикой, когда вставляется большой объем данных, и это более удобно в использовании.

【Пример кода для этой статьи】

Категории