Почему ТРАНЗАКЦИЯ/ФИКСАЦИЯ так сильно повышает производительность с помощью 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.
2 answers
-
Пожалуйста, проверьте эту ссылку:
Https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html
InnoDB должен сбрасывать журнал на диск при каждой фиксации транзакции, если эта транзакция внесла изменения в базу данных. Когда за каждым изменением следует фиксация (как при настройке автоматической фиксации по умолчанию), пропускная способность ввода-вывода устройства хранения данных ограничивает количество потенциальных операций на второй.
Крупные транзакции могут повлиять на производительность во время фиксации (см. Выше)
Только в случае отката, однако он может быть оптимизирован с помощью некоторых настроек (проверьте ссылку)
Мой собственный небольшой тест в .Net (4 поля pr. записей):
ВСТАВИТЬ 1 запись, без транзакции:60 мс
ВСТАВИТЬ 1 запись, используя транзакцию:158 мс
ВСТАВЬТЕ 200 записей с использованием транзакций, зафиксируйте после каждой записи:17778 мс
ВСТАВИТЬ 200 записей без транзакций:4940 мс
ВСТАВИТЬ 200 записей с использованием транзакций, фиксировать только после последней записи:4552 мс
ВСТАВИТЬ 1000 записей с использованием транзакций, фиксируются только после последней записи:21795 мс
Клиент в Дании, сервер в Бельгии (Google cloud f1-micro).
Я хотел поместить это в комментарий, но форматирование не good....so заранее приношу свои извинения;-)