Почему TRANSACTION/COMMIT так сильно улучшает производительность с помощью PHP/MySQL (InnoDB)?

Я работал с импортом больших CSV файлов данных; обычно менее 100 000 записей. Я работаю с PHP и MySQL (таблицы InnoDB). Мне нужно было использовать PHP для преобразования некоторых полей и выполнять некоторую текстовую обработку до MySQL INSERT (часть process_note_data() в коде ниже). MySQL LOAD DATA был невозможен, поэтому, пожалуйста, не предлагайте его.

Недавно я попытался улучшить скорость этого процесса, используя транзакции MySQL, используя START TRANSACTION и COMMIT. Увеличение производительности было неожиданным. Время (и) обработки уменьшилось в 20 раз. Таким образом, 20-минутный процесс занял всего около 1 минуты.

ВОПРОСЫ.

1.) Кто-нибудь понимает, почему такое увеличение производительности (от 20 минут до 1 минуты)?

2.) Должен ли я беспокоиться о том, насколько большой транзакция может получить 100 000 записей?

3.) Должен ли я беспокоиться о большом количестве вставок и/или обновлений в транзакции?

/*
 * Customer Notes Data:
 * Rows are either a meeting, call or note!
 */
$row = 1;
$data = array();
$fields = array();
$line = '';

$db->query('SET autocommit=0;');
$db->query('START TRANSACTION;');

if (($handle = fopen("modules/".$currentModule."/Data/customernote.csv", "r")) !== FALSE) {
  while (($data = fgetcsv($handle, 4096, ',', '"')) !== FALSE && $row < 999000) {
    //Row 1 - CSV header row with field names
    if ($row == 1) {
      $csv_fields = $data;
    } elseif ($row > 1) {
      $fields = $this->process_note_data($data, $csv_fields, $row);
    }
    $row++;
  } // end while
  fclose($handle);
}

$db->query('COMMIT;');
$db->query('SET autocommit=1;');

Примечание. Обработка текста/поля выполняется при вызове $this->process_note_data(), который затем вызывает другой вспомогательный класс с кодом оператора INSERT. У меня не было достаточно места, чтобы включить весь код. $db->query() - типичный объект базы данных для запросов MySQL.

Ответы

Ответ 1

  • Пожалуйста, проверьте эту ссылку:

    https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html

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

  • Большие транзакции могут повлиять на производительность во время фиксации (см. выше)

  • Только в случае откат, однако его можно оптимизировать с помощью некоторых настроек (проверьте ссылку)

Ответ 2

Мой собственный небольшой тест в .Net(4 поля пр. записей):

Запись INSERT 1, транзакция: 60 ms

Запись INSERT 1, используя транзакцию: 158 ms

INSERT 200 записей с использованием транзакций, фиксация после каждой записи: 17778мс

INSERT 200 записей без транзакций: 4940 ms

INSERT 200 записей с использованием транзакций, только фиксация после последнего запись: 4552 ms

INSERT 1000 записи с использованием транзакций, только фиксация после последней записи: 21795 ms

Клиент в Дании, сервер в Бельгии (Google cloud f1-micro).

Я хотел помещать это в комментарий, но форматирование не очень хорошо... так вот, извиняюсь заранее, -)