Источник: blog.csdn.net/a18505947362/article/details/123667215.

В этой статье описывается личное использование MySQL для вставки больших файлов и обобщаются наиболее практичные решения.,4 способа обычно вставлять большие данные,Прямо сейчасforПетля одинарная、Объединение SQL、Пакетная вставкаsaveBatch()、цикл + Включите режим пакетной обработки и получите более практичные решения.

1. Предисловие

Недавно, в свободное время, я тестировал вставку данных в базу данных MySQL. Я понятия не имел, как быстро вставить данные. При объеме данных всего 1 Вт это заняло около 47 секунд. Это было действительно невыносимо! После постоянных исследований были найдены еще несколько практических решений.

2. Подготовительные работы

Тестовая среда: проект SpringBoot, платформа MyBatis-Plus, MySQL8.0.24, JDK13.

Предварительное условие: проект SpringBoot интегрирует MyBatis-Plus. В приведенной выше статье описан процесс настройки.,При этом для Пакетной реализован интерфейс IService. вставкаданныедействоватьsaveBatch()метод

1. Соответствующие зависимости, представленные файлом pom.xml в проекте Maven, следующие:

 <dependencies>
 
  <!-- SpringBoot Зависимости веб-модуля -->
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
 
  <!-- MyBatis-Plus полагаться -->
  <dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>mybatis-plus-boot-starter</artifactId>
   <version>3.3.1</version>
  </dependency>
 
  <!-- драйвер подключения библиотеки данных -->
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
  </dependency>
  
  <!-- Используйте аннотации,Упростите код-->
  <dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
  </dependency>
  
 </dependencies>

2. Содержимое файла свойств конфигурации Application.yml (ключевой момент: включить режим пакетной обработки)

server:
    # номер порта 
    port: 8080
 
#  Информация о конфигурации подключения MySQL (ниже представлена ​​лишь простая конфигурация, дополнительные настройки вы можете просмотреть самостоятельно)
spring:
    datasource:
        #  Адрес подключения (решение проблемы с искаженными китайскими символами UTF-8) + коррекция часового пояса)
        #         （rewriteBatchedStatements=true Включите пакетный режим)
        url: jdbc:mysql://127.0.0.1:3306/bjpowernode?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
        #  имя пользователя
        username: root
        #  пароль
        password: xxx
        #  Имя драйвера подключения
        driver-class-name: com.mysql.cj.jdbc.Driver

3. Класс сущности (тест)

/**
 *   Student Класс объекта
 *   
 *   @Dataаннотация：представлятьLombokполагаться,Можно опуститьSetter、Getterметод
 *   @author LBF
 *   @date 2022/3/18 16:06
 */
@Data
@TableName(value = "student")
public class Student {
    
    /**  первичный ключ  тип: приращение */
    @TableId(type = IdType.AUTO)
    private int id;
 
    /**  имя */
    private String name;
 
    /**  возраст */
    private int age;
 
    /**  адрес */
    private String addr;
 
    /**  адрес Число  @TableField: сопоставление с полями таблицы */
    @TableField(value = "addr_num")
    private String addrNum;
 
    public Student(String name, int age, String addr, String addrNum) {
        this.name = name;
        this.age = age;
        this.addr = addr;
        this.addrNum = addrNum;
    }
}

4. Структура таблицы учащихся базы данных (примечание: индекс отсутствует)

3. Тестовая работа

Кратко: После завершения подготовительных работ,Прямо сейчасверноforцикл、Объединение SQL-оператор、Пакетная вставкаsaveBatch()、циклвставлять+Включите пакетный режим,4 способа вставки тестовых данных для проверки производительности.

Примечание. Объем тестовых данных составляет 5 Вт, а таблица данных должна быть очищена после одного теста (чтобы гарантировать, что на нее не влияют старые данные).

На содержание следующего теста могут влиять многие факторы, такие как среда конфигурации теста, спецификации теста и объем данных. Читатели могут проводить тест на основе собственных справочных материалов.

1. Для вставки петли (одинарной) (общее время: 177 секунд)

Резюме: Среднее время теста составляет около 177 секунд, на что действительно невыносимо смотреть (закрытие лица), поскольку при использовании цикла for для вставки одной строки каждый раз необходимо получить соединение (Соединение), отпуская подключение и закрыть ресурс (если объем данных большой) это сильно потребляет ресурсы и занимает много времени.

@GetMapping("/for")
public void forSingle(){
    // время начала
    long startTime = System.currentTimeMillis();
    for (int i = 0; i < 50000; i++){
        Student student = new Студент("Ли Йи" + я, 24, «Город Чжанцзяцзе» + i,i + "Число");
        studentMapper.insert(student);
    }
    // время окончания
    long endTime = System.currentTimeMillis();
    System.out.println("Вставка данных требует времени:" + (endTime - startTime));
}

(1) Результат первого теста: 190155 примерно равен 190 секундам.

(2) Результат второго теста: 175926, что примерно равно 176 секундам (сервис не перезапускался)

(3) Результат третьего теста: 174726, что примерно равно 174 секундам (перезапуск службы)

2. Объединение операторов SQL (общее время: 2,9 секунды)

краткий：Формат сращивания：insert into student(xxxx) value(xxxx),(xxxx),(xxxxx).......

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

Однако при увеличении объема данных может произойти переполнение памяти и трудоемкий анализ операторов SQL. Однако по сравнению с первым пунктом производительность значительно повышается.

@GetMapping("/sql")
public void sql(){
    ArrayList<Student> arrayList = new ArrayList<>();
    long startTime = System.currentTimeMillis();
    for (int i = 0; i < 50000; i++){
        Student student = new Студент("Ли Йи" + я, 24, «Город Чжанцзяцзе» + i,i + "Число");
        arrayList.add(student);
    }
    studentMapper.insertSplice(arrayList);
    long endTime = System.currentTimeMillis();
    System.out.println("Вставка данных требует времени:" + (endTime - startTime));
}

// Вставка с использованием аннотации @Insert: это сделано для простоты, файл Mapper.xml не записывается.
@Insert("<script>" +
        "insert into student (name,age,addr,addr_num) values " +
        "<foreach collection='studentList' item='item' separator=','> " +
        "(#{item.name}, #{item.age}, #{item.addr}, #{item.addrNum}) " +
        "</foreach> " +
        "</script>")
int insertSplice(@Param("studentList") List<Student> studentList);

(1) Результат первого теста: 3218 примерно равно 3,2 секунды.

(2) Второй результат теста: 2592 примерно равно 2,6 секундам (служба не перезапускалась)

(3) Результат третьего теста: 3082 примерно равно 3,1 секунды (перезапуск службы)

3. Пакетная вставка saveBatch (общее время: 2,7 секунды)

краткий：использоватьMyBatis-PlusвыполнитьIServiceПакетная обработка в интерфейсеsaveBatch()метод,При просмотре исходного кода,Можно обнаружить, что на самом деле это вставка цикла for.,Но по сравнению с первым пунктом,Почему производительность улучшилась?？Из-за использования шардинга（batchSize = 1000） + Отправляйте операции транзакций в пакетном режиме, чтобы повысить производительность вместо снижения производительности Connection.

@GetMapping("/saveBatch1")
public void saveBatch1(){
    ArrayList<Student> arrayList = new ArrayList<>();
    long startTime = System.currentTimeMillis();
    // моделированиеданные
    for (int i = 0; i < 50000; i++){
        Student student = new Студент("Ли Йи" + я, 24, «Город Чжанцзяцзе» + i,i + "Число");
        arrayList.add(student);
    }
    // Пакетная вставка
    studentService.saveBatch(arrayList);
    long endTime = System.currentTimeMillis();
    System.out.println("Вставка данных требует времени:" + (endTime - startTime));
}

(1) Результат первого теста: 2864 примерно равно 2,9 секунды.

(2) Второй результат теста: 2302 примерно равно 2,3 секунды (служба не перезапускалась)

(3) Результат третьего теста: 2893, что примерно равно 2,9 секундам (перезапуск службы).

Важное примечание: MySQL JDBCДрайвер игнорирует по умолчаниюsaveBatch()методвexecuteBatch()заявление,Разделите набор операторов SQL, которые необходимо обработать в пакетах.,Во время выполнения каждое сообщение отправляется в библиотеку данных MySQL.,Результатом фактически является фрагментированная вставка,То есть по сравнению с методом одиночной вставки,Улучшенный,Однако существенно улучшить производительность не удалось.

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

#  Информация о конфигурации соединения MySQL
spring:
    datasource:
        #  соединятьадрес（еще нет Включите пакетный режим)
        url: jdbc:mysql://127.0.0.1:3306/bjpowernode?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        #  имя пользователя
        username: root
        #  пароль
        password: xxx
        #  Имя драйвера подключения
        driver-class-name: com.mysql.cj.jdbc.Driver

Результат теста: 10541, что примерно равно 10,5 секундам (пакетный режим не включен)

4. Вставка цикла + включение пакетного режима обработки (общее затраченное время: 1,7 секунды) (акцент: однократная подача)

Краткое описание: после включения пакетной обработки, отключения автоматической отправки транзакций и совместного использования одного и того же сеанса SqlSession производительность одиночной вставки в цикл for существенно повышается, поскольку один и тот же сеанс SqlSession экономит потребление энергии при операциях, связанных с ресурсами, и сокращает время обработки транзакций; и т. д., тем самым значительно повышая эффективность выполнения. (На данный момент лично я считаю лучшим решением)

@GetMapping("/forSaveBatch")
public void forSaveBatch(){
    //  Включить режим пакетной обработки BATCH , отключить автоматическое представление транзакций false
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
    //  Приобретение отражения, приобретение Mapper
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    long startTime = System.currentTimeMillis();
    for (int i = 0 ; i < 50000 ; i++){
        Student student = new Студент("Ли Йи" + я, 24, «Город Чжанцзяцзе» + i,i + "Число");
        studentMapper.insertStudent(student);
    }
    // Отправьте транзакцию один раз
    sqlSession.commit();
    // закрыть ресурс
    sqlSession.close();
    long endTime = System.currentTimeMillis();
    System.out.println("Общее затраченное время: " + (endTime - startTime));
}

(1) Результат первого теста: 1831 примерно равно 1,8 секунды.

(2) Второй результат теста: 1382 примерно равно 1,4 секунды (служба не перезапускалась)

(3) Результат третьего теста: 1883, что примерно равно 1,9 секунды (перезапуск службы).

4. Резюме

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

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

------

Мы создали качественную группу технического обмена,быть с замечательными людьми,Вы также станете лучше сами,ТоропитьсяНажмите, чтобы присоединиться к группе,Наслаждайтесь радостью совместного роста. кроме того,Если вы недавно хотели сменить работу,Несколько лет назад я потратил 2 недели, собирая кучу опыта от крупных производителей.,Те, кто готов сменить работу после праздников, могутНажмите здесь, чтобы получить это！

Рекомендуем к прочтению

• 1024 День программиста: давайте поприветствуем новую эру вычислительных мощностей и новое будущее открытого исходного кода!
• Выпущен Git 2.38, представляющий гигантский инструмент управления складом «Scalar».
• Бонус в размере 70 000, байтовое предложение, iPhone 14 Pro и другие неожиданные призы.

··································

Здравствуйте, я программист DD, ветеран разработки с 10-летним стажем, Alibaba Cloud MVP, Tencent Cloud TVP, опубликовал книгу и начал бизнес, 4 года на госпредприятиях и 6 лет в Интернете. От рядовых девелоперов до архитекторов, а затем и партнеров. В то же время мое глубочайшее чувство заключается в том, что мы должны продолжать учиться и уделять внимание передовым технологиям. Если вы будете настойчивы, больше думаете, меньше жалуетесь и усерднее работаете, вас будет легко обгонять в поворотах! Так что не спрашивайте меня, не поздно ли что-то делать сейчас. Если вы настроены в чем-то с оптимизмом, вы должны упорствовать, чтобы увидеть надежду, а не упорствовать только тогда, когда вы видите надежду. Поверьте, если вы будете упорствовать, вы станете лучше, чем сейчас! Если у вас еще нет направления, вы можете сначала следовать за мной. Я часто буду делиться здесь новейшей информацией, которая поможет вам накопить капитал для обгонов в поворотах.

Нажмите, чтобы подать заявку2022до настоящего времени10000Tучебные материалы