Является ли пакетная вставка базы данных такой особенной?

задняя часть база данных

В последнее время о новых проектах написано много различныхinsertBatchВсегда говорили, что пакетная вставка намного эффективнее циклической, так что давайте поэкспериментируем в этой статье, правда ли это?

тестовая среда:

  • SpringBoot 2.5
  • Mysql 8
  • JDK 8
  • Docker

Во-первых, множество данных вставки, альтернативные решения:

  • foreachвставка петли
  • соединениеsql, после выполнения
  • Вставить с помощью пакетной функции

Создайте тестовую среду

sqlдокумент:

drop database IF EXISTS test;
CREATE DATABASE test;
use test;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT "",
  `age` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Применяемый файл конфигурации:

server:
  port: 8081
spring:
  #数据库连接配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true
    username: root
    password: 123456
#mybatis的相关配置
mybatis:
  #mapper配置文件
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.aphysia.spingbootdemo.model
  #开启驼峰命名
  configuration:
    map-underscore-to-camel-case: true
logging:
  level:
    root: error

файл запуска, настроенныйMapperПуть к сканируемому файлу:

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.aphysia.springdemo.mapper")
public class SpringdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringdemoApplication.class, args);
    }

}

MapperВсего в файле подготовлено несколько методов: вставка одного объекта, удаление всех объектов, а также объединение и вставка нескольких объектов:

import com.aphysia.springdemo.model.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {

    int insertUser(User user);

    int deleteAllUsers();


    int insertBatch(@Param("users") List<User>users);
}

Mapper.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="com.aphysia.springdemo.mapper.UserMapper">
    <insert id="insertUser" parameterType="com.aphysia.springdemo.model.User">
        insert  into user(id,age) values(#{id},#{age})
    </insert>

    <delete id="deleteAllUsers">
        delete from user where id>0;
    </delete>

    <insert id="insertBatch" parameterType="java.util.List">
        insert into user(id,age) VALUES
        <foreach collection="users" item="model" index="index" separator=",">
            (#{model.id}, #{model.age})
        </foreach>
    </insert>
</mapper>

При тестировании мы удаляем все данные каждый раз, когда мы работаем, чтобы гарантировать, что тест является объективным и не зависит от предыдущих данных.

разные тесты

1. для каждой вставки

Сначала получите список, а затем выполните операцию с базой данных для каждой части данных, чтобы вставить данные:

@SpringBootTest
@MapperScan("com.aphysia.springdemo.mapper")
class SpringdemoApplicationTests {

    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Resource
    UserMapper userMapper;

    static int num = 100000;

    static int id = 1;

    @Test
    void insertForEachTest() {
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        for (int i = 0; i < users.size(); i++) {
            userMapper.insertUser(users.get(i));
        }
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }
}

2. сращивание sql вставки

На самом деле данные вставляются следующим образом:

INSERT INTO `user` (`id`, `age`) 
VALUES (1, 11),
(2, 12),
(3, 13),
(4, 14),
(5, 15);

    @Test
    void insertSplicingTest() {
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        userMapper.insertBatch(users);
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }

3. Пакетная вставка с помощью Batch

БудуMyBatis sessionизexecutor typeустановить какBatch ,использоватьsqlSessionFactoryУстановите режим выполнения на пакетный и установите автоматическую отправку наfalse, После того, как все вставит, отправить снова за один раз:

    @Test
    public void insertBatch(){
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        for(int i=0;i<users.size();i++){
            mapper.insertUser(users.get(i));
        }
        sqlSession.commit();
        sqlSession.close();
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }

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

На основе пакетной обработки каждые 1000 единиц данных должны быть представлены в первую очередь, то есть представлены пакетами.

    @Test
    public void insertBatchForEachTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = getRandomUsers();
        long start = System.currentTimeMillis();
        for(int i=0;i<users.size();i++){
            mapper.insertUser(users.get(i));
            if (i % 1000 == 0 || i == num - 1) {
                sqlSession.commit();
                sqlSession.clearCache();
            }
        }
        sqlSession.close();
        long end = System.currentTimeMillis();
        System.out.println("time:" + (end - start));
    }

Первые результаты явно ошибочны?

Запустив приведенный выше код, мы можем получить следующий результат:forЭффективность цикла вставки действительно очень низкая, мозаичнаяsqlЭффективность относительно высокая.Я видел некоторые данные, которые говорят о сращиванииsqlможет бытьmysqlограничение, но я выполняю1000wТолько когда происходит переполнение кучи памяти.

Ниже неправильный результат! ! !

Метод вставки 10 100 1000 1w 10w 100w 1000w
для вставки петли 387 1150 7907 70026 635984 слишком долго... слишком долго...
сплайсинг sql вставка 308 320 392 838 3156 24948 OutOfMemoryError: не хватает памяти
пакетная обработка 392 917 5442 51647 470666 слишком долго... слишком долго...
пакет + пакетная фиксация 359 893 5275 50270 472462 слишком долго... слишком долго...

Сплайсинг sql не превышает памяти

Давайте взглянемmysqlпределы:

mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_allowed_packet        | 67108864   |
| mysqlx_max_allowed_packet | 67108864   |
| slave_max_allowed_packet  | 1073741824 |
+---------------------------+------------+
3 rows in set (0.12 sec)

Этот67108864полон600Multi-M, тоже не задайсь никакой ошибки, то мы изменяем его, чтобы изменить его сейчас, изменить полный повторный тест:

  1. первый стартmysqlВ случае входа в контейнер вы также можете напрямуюDockerНастольная версия напрямуюCliзначок для входа:
docker exec -it mysql bash
  1. Войти/etc/mysqlКаталог, изменитьmy.cnfдокумент:
cd /etc/mysql
  1. первым подписатьсяvim, или вы не можете редактировать файл:
apt-get update
apt-get install vim
  1. Исправлятьmy.cnf 
vim my.cnf
  1. добавить в последнюю строкуmax_allowed_packet=20M(согласно сiРедактировать, после редактирования нажатьesc,войти:wqпокидать)
 
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# Custom config should go here
!includedir /etc/mysql/conf.d/
max_allowed_packet=2M
  1. выйти из контейнера
# exit
  1. Проверитьmysqlконтейнерid 
docker ps -a

image-20211130005909539

  1. перезагружатьmysql 
docker restart c178e8998e68

После успешного перезапуска просмотрите самый большойmax_allowed_pactet, обнаружил, что он был успешно изменен:

mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_allowed_packet        | 2097152    |
| mysqlx_max_allowed_packet | 67108864   |
| slave_max_allowed_packet  | 1073741824 |
+---------------------------+------------+

Выполняем сшивание сноваsql,Обнаружить100wкогда,sqlдостиг3.6Mили около того, больше, чем мы установили2M, успешная демонстрация выдает ошибку:

org.springframework.dao.TransientDataAccessResourceException: 
### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.
; Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.

Почему эта пакетная обработка такая медленная?

Однако, если вы присмотритесь, то обнаружите, что вышеуказанный метод не показывает никаких преимуществ при пакетной обработке, иforПетля имеет какое-то значение? Это правильно?

Это точно неправильно.Из официальной документации видно, что он будет обновляться пачками и не будет каждый раз создавать подготовленные операторы.Теория быстрее.

image-20211130011820487

Затем я обнаружил одну из своих самых важных проблем: подключение к базе данных.URL адрес отсутствуетrewriteBatchedStatements=true

Если мы не напишем,MySQL JDBCДиск игнорируется по умолчаниюexecuteBatch()операторы, набор операторов, которые мы ожидаем выполнять в пакетахsqlОператор разделен, но при выполнении он отправляется один за другимMySQLБаза данных, по сути, представляет собой единую вставку, что напрямую приводит к снижению производительности. Я сказал, что производительность похожа на зацикливание для вставки данных.

толькоrewriteBatchedStatementsпараметр установлен наtrue, драйвер базы данных поможет нам выполнить пакетное выполнениеSQL.

Правильное подключение к базе данных:

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true&&&rewriteBatchedStatements=true

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

Метод вставки 10 100 1000 1w 10w 100w 1000w
для вставки петли 387 1150 7907 70026 635984 слишком долго... слишком долго...
сплайсинг sql вставка 308 320 392 838 3156 24948 (вероятно, превышает предел длины SQL) OutOfMemoryError: не хватает памяти
партия (выделено) 333 323 362 636 1638 8978 OutOfMemoryError: не хватает памяти
пакет + пакетная фиксация 359 313 394 630 2907 18631 OutOfMemoryError: не хватает памяти

Из приведенных выше результатов следует, что пакетная обработка выполняется намного быстрее. Когда порядок величины слишком велик, это фактически превышает переполнение памяти. Пакетная обработка и пакетная отправка не становятся быстрее. Это похоже на пакетную обработку, но становится медленнее.да,подавал слишком много раз,склейкаsqlКогда количество решений относительно невелико, это мало чем отличается от пакетной обработки.forЦиклическая вставка данных, это правда особенная.100уже нужен, когда1s, этот вариант не вариант.

Сначала, когда я обнаружил, что пакетная обработка идет медленно, я очень сомневался в себе. Позже я узнал, что есть параметр, и было ощущение очистки облаков. Чем больше я знал, тем больше я не знал. .

【Об авторе】:
Цинь Хуай, общественное числоЦинь Хуай продуктовый магазин】Автор, дорога технологий не в одно время, горы высоки, а реки длинны, даже если она медленная, она продолжается и продолжается.

Sword Point предлагает решение всех проблем PDF

Что я написал в 2020 году?

Заметки по программированию с открытым исходным кодом

Категории