100 000 единиц данных вставляются пакетами, как это сделать быстрее всего?

задняя часть MySQL
100 000 единиц данных вставляются пакетами, как это сделать быстрее всего?

@[toc] На прошлой неделе Сонг Гэ перепечатал статью о пакетной вставке данных, в которой я рассказал вам о проблеме пакетной вставки данных и о том, как быстро выполнить пакетную вставку.

Маленький друг высказал разные мнения после прочтения статьи:

Song Ge серьезно поболтал с одноклассниками BUG и в основном понял значение этого маленького друга, поэтому я сам написал тестовый пример и реорганизовал сегодняшнюю статью. Я надеюсь обсудить этот вопрос с моими друзьями. Добро пожаловать, друзья, придумайте лучшие решения.

1. Анализ идей

Для задачи пакетной вставки мы используем JDBC для работы, на самом деле есть две идеи:

  1. Используйте цикл for для вставки данных по одному (для этого требуется пакетная обработка).
  2. Создайте вставку sql, похожую на этуinsert into user(username,address) values('aa','bb'),('cc','dd')....

Что быстрее?

Мы рассматриваем этот вопрос с двух сторон:

  1. Эффективность выполнения вставки SQL.
  2. Сетевой ввод-вывод.

Давайте сначала поговорим о первом решении, которое заключается в использовании цикла for для вставки:

  • Преимущество этого решения в том, что PreparedStatement в JDBC имеет функцию предварительной компиляции, которая будет кэшироваться после предварительной компиляции, последующее выполнение SQL будет быстрее, а JDBC может включать пакетную обработку, что очень мощно.
  • Недостатком является то, что во многих случаях наш SQL-сервер и сервер приложений могут не совпадать, поэтому необходимо учитывать сетевой ввод-вывод.Если сетевой ввод-вывод занимает много времени, это может снизить скорость выполнения SQL.

Давайте поговорим о втором решении, которое заключается в создании вставки SQL:

  • Преимущество этой схемы в том, что есть только один сетевой ввод-вывод, даже если процесс сегментирования состоит только из нескольких сетевых вводов-выводов, поэтому эта схема не будет тратить слишком много времени на сетевой ввод-вывод.
  • Конечно, у этого решения есть несколько недостатков: во-первых, SQL слишком длинный и даже может потребовать пакетной обработки после шардинга, во-вторых, нельзя полностью использовать преимущества предварительной компиляции PreparedStatement, и SQL необходимо перепарсить и нельзя использовать повторно; в-третьих, окончательный сгенерированный SQL слишком длинный, и менеджеру баз данных требуется время, чтобы разобрать такой длинный SQL.

Итак, что нам в конечном счете нужно учитывать, так это то, превышает ли время, которое мы тратим на сетевой ввод-вывод, время на вставки SQL? Это основной вопрос, который мы должны рассмотреть.

2. Тестирование данных

Далее давайте проведем простой тест и вставим 50 000 фрагментов данных пакетами.

Сначала подготовьте простую тестовую таблицу:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Затем создайте проект Spring Boot, введите зависимости MyBatis и драйверы MySQL, а затем настройте информацию о подключении к базе данных в application.properties:

spring.datasource.username=root
spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///batch_insert?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

Обратите внимание, что в URL-адресе подключения к базе данных есть еще один параметр.rewriteBatchedStatements, что является ядром.

Драйвер MySQL JDBC по умолчанию игнорируетexecuteBatch()оператор, поместите набор пакетов, которые мы ожидаем выполнитьsqlЗаявление разбивается и отправляется по одномуMySQLВ базе данных пакетные вставки на самом деле представляют собой одиночные вставки, что напрямую снижает производительность. будетrewriteBatchedStatementsпараметр установлен наtrue, драйвер базы данных поможет нам выполнить пакетное выполнениеSQL.

Итак, подготовительная работа завершена.

2.1 Тест по схеме 1

Для начала рассмотрим тест схемы 1, то есть вставки по одной (фактически пакетная обработка).

Сначала создайте соответствующий преобразователь следующим образом:

@Mapper
public interface UserMapper {
    Integer addUserOneByOne(User user);
}

Соответствующий файл XML выглядит следующим образом:

<insert id="addUserOneByOne">
    insert into user (username,address,password) values (#{username},#{address},#{password})
</insert>

Услуга заключается в следующем:

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {
    private static final Logger logger = LoggerFactory.getLogger(UserService.class);
    @Autowired
    UserMapper userMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Transactional(rollbackFor = Exception.class)
    public void addUserOneByOne(List<User> users) {
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        UserMapper um = session.getMapper(UserMapper.class);
        long startTime = System.currentTimeMillis();
        for (User user : users) {
            um.addUserOneByOne(user);
        }
        session.commit();
        long endTime = System.currentTimeMillis();
        logger.info("一条条插入 SQL 耗费时间 {}", (endTime - startTime));
    }
}

Здесь я скажу:

Хотя он вставляется один за другим, мы должны включить пакетный режим (BATCH), чтобы до и после использовался только этот SqlSession.Если пакетный режим не используется, потребуется много времени для повторного получения и отпустите соединение.Эффективность крайне низкая, и Сун Гэ не будет тестировать этот крайне неэффективный метод.

Затем напишите простой тестовый интерфейс, чтобы увидеть:

@RestController
public class HelloController {
    private static final Logger logger = getLogger(HelloController.class);
    @Autowired
    UserService userService;
    /**
     * 一条一条插入
     */
    @GetMapping("/user2")
    public void user2() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 50000; i++) {
            User u = new User();
            u.setAddress("广州：" + i);
            u.setUsername("张三：" + i);
            u.setPassword("123：" + i);
            users.add(u);
        }
        userService.addUserOneByOne(users);
    }
}

Напишите простой модульный тест:

/**
 * 
 * 单元测试加事务的目的是为了插入之后自动回滚，避免影响下一次测试结果
 * 一条一条插入
 */
@Test
@Transactional
void addUserOneByOne() {
    List<User> users = new ArrayList<>();
    for (int i = 0; i < 50000; i++) {
        User u = new User();
        u.setAddress("广州：" + i);
        u.setUsername("张三：" + i);
        u.setPassword("123：" + i);
        users.add(u);
    }
    userService.addUserOneByOne(users);
}

Как видите, это заняло 901 миллисекунду, а 5 Вт данных были вставлены менее чем за 1 секунду.

2.2 Вариант 2 теста

Второй вариант — сгенерировать SQL, а затем вставить его.

Маппер выглядит следующим образом:

@Mapper
public interface UserMapper {
    void addByOneSQL(@Param("users") List<User> users);
}

Соответствующий SQL выглядит следующим образом:

<insert id="addByOneSQL">
    insert into user (username,address,password) values
    <foreach collection="users" item="user" separator=",">
        (#{user.username},#{user.address},#{user.password})
    </foreach>
</insert>

Услуга заключается в следующем:

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {
    private static final Logger logger = LoggerFactory.getLogger(UserService.class);
    @Autowired
    UserMapper userMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;
    @Transactional(rollbackFor = Exception.class)
    public void addByOneSQL(List<User> users) {
        long startTime = System.currentTimeMillis();
        userMapper.addByOneSQL(users);
        long endTime = System.currentTimeMillis();
        logger.info("合并成一条 SQL 插入耗费时间 {}", (endTime - startTime));
    }
}

Затем вызовите этот метод в модульном тесте:

/**
 * 合并成一条 SQL 插入
 */
@Test
@Transactional
void addByOneSQL() {
    List<User> users = new ArrayList<>();
    for (int i = 0; i < 50000; i++) {
        User u = new User();
        u.setAddress("广州：" + i);
        u.setUsername("张三：" + i);
        u.setPassword("123：" + i);
        users.add(u);
    }
    userService.addByOneSQL(users);
}

Вы можете видеть, что для вставки 50 000 фрагментов данных потребовалось 1805 миллисекунд.

Видно, что эффективность выполнения генерации SQL все же немного хуже.

Кроме того, следует отметить, что второе решение также имеет проблему, то есть, когда количество данных велико, сгенерированный SQL будет очень длинным, и MySQL может быть не в состоянии обработать такой большой SQL за один раз. , В это время необходимо изменить конфигурацию MySQL или вставляемые данные фрагментированы, и эти операции приведут к более длительному времени вставки.

2.3 Сравнительный анализ

Очевидно, что вариант 1 имеет больше преимуществ. При пакетной вставке 100 000 или 200 000 данных преимущества первого решения будут более очевидными (второе решение требует изменения конфигурации MySQL или сегментирования вставляемых данных).

3. Как депутат сделал это?

Друзья знаете, на самом деле в MyBatis Plus тоже есть метод пакетной вставки saveBatch, давайте взглянем на исходный код его реализации:

@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {
    String sqlStatement = getSqlStatement(SqlMethod.INSERT_ONE);
    return executeBatch(entityList, batchSize, (sqlSession, entity) -> sqlSession.insert(sqlStatement, entity));
}

Как видите, полученный здесь sqlStatement представляет собойINSERT_ONE, то есть вставлять по одному.

Давайте посмотрим на метод executeBatch следующим образом:

public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
    Assert.isFalse(batchSize < 1, "batchSize must not be less than one");
    return !CollectionUtils.isEmpty(list) && executeBatch(entityClass, log, sqlSession -> {
        int size = list.size();
        int i = 1;
        for (E element : list) {
            consumer.accept(sqlSession, element);
            if ((i % batchSize == 0) || i == size) {
                sqlSession.flushStatements();
            }
            i++;
        }
    });
}

Обратите внимание, что третий возвращаемый параметр — это лямбда-выражение, которое также является основной логикой пакетной вставки в MP.Видно, что MP сначала разбивает данные (размер сегмента по умолчанию — 1000), а после завершения сегментирования который также вставляется один за другим. Продолжайте смотреть на метод executeBatch, и вы обнаружите, что sqlSession здесь на самом деле является пакетным sqlSession, а не обычным sqlSession.

Подводя итог, можно сказать, что схема пакетной вставки в MP фактически такая же, как наша идея пакетной вставки в разделе 2.1.

4. Резюме

Ну что, после вышеописанного разбора, теперь друзья умеют делать пакетную вставку?

Сун Гэ предоставил тестовый пример, и официальный аккаунт ответил в фоновом режиме.пакетный вставной тестПолучите адрес кейса, в кейсе есть три метода модульного тестирования, запустите его напрямую, вы можете увидеть разницу во времени пакетной вставки (скрипт базы данных находится в каталоге ресурсов).

Заинтересованные друзья могут попробовать ~

Наконец, еще раз спасибо за комментарии от детской обуви BUG~

Категории