Insert InnoDB ускорение внесения большого объема данных
У меня имеется около 75683 данных, которые вносятся / изменяются раз в минуту, пытаюсь увеличить скорость выполнения данной операции, все пути которые пробовал привели к этому коду
$marketsToSet = array();
$marketsBase = array();
echo "foreach markets - ". microtime(true) . "<br>";
$time = time();
$i = 0;
$e = 0;
foreach ($marketsData as $key => $value) {
if(!isset($marketsToSet[$e])) $marketsToSet[$e] = array();
if(!isset($marketsBase[$e])) $marketsBase[$e] = array();
$marketsToSet[$e][] = $key;
$marketsToSet[$e][] = $value['e'];
$marketsToSet[$e][] = $value['f'];
$marketsToSet[$e][] = $value['i'];
$marketsToSet[$e][] = $value['v'];
$marketsToSet[$e][] = $value['b'];
$marketsToSet[$e][] = $time;
$marketsToSet[$e][] = 0;
$marketsBase[$e][] = "(?,?,?,?,?,?,?,?)";
$i++;
if($i == 8181) {
$i = 0;
$e++;
}
}
if(count($marketsBase) != 0) {
echo " set to bd - ". microtime(true) . "<br>";
for ($i = 0; $i <= $e; $i++) {
echo "insert #". $i . " count rows ". count($marketsBase[$i]) . " - " . microtime(true) . "<br>";
dbCon("INSERT INTO `markets` (`id`, `event_id`, `market_id`, `intext`, `val`, `text`, `version`,`del`) VALUES " . join(", ", $marketsBase[$i]) . " ON DUPLICATE KEY UPDATE `version` = IF(`text` <> VALUES(`text`) OR `val` <> VALUES(`val`) OR `del` <> VALUES(`del`), VALUES(`version`), `version`), `text` = IF(`text` <> VALUES(`text`), VALUES(`text`), `text`), `val` = IF(`val` <> VALUES(`val`), VALUES(`val`), `val`), `del` = IF(`del` <> VALUES(`del`), VALUES(`del`), `del`)", $marketsToSet[$i], hdRel());
}
echo "end set to bd - ". microtime(true) . "<br>";
}
Вот какую информацию по времени имею на выходе
foreach markets - 1587878994.2098
set to bd - 1587878994.251
insert #0 count rows 8181 - 1587878994.251
insert #1 count rows 8181 - 1587878995.9705
insert #2 count rows 8181 - 1587878997.5289
insert #3 count rows 8181 - 1587878999.1871
insert #4 count rows 8181 - 1587879000.8133
insert #5 count rows 8181 - 1587879002.8017
insert #6 count rows 8181 - 1587879004.5995
insert #7 count rows 8181 - 1587879006.3541
insert #8 count rows 8181 - 1587879008.4204
insert #9 count rows 8181 - 1587879010.039
insert #10 count rows 2054 - 1587879011.9794
end set to bd - 1587879012.9125
Лимит одного запроса insert в моем случае 8181 при большем кол-ве вылезает ошибка.
Доп инфа: hdRel() - функция подключения к мускулю (по настройкам хостера, покдлючение живет около 10 секунд, по этому приходится каждый раз переподключатся)
function dbCon($a, $b, $c) {
$d = $c->prepare($a);
$d->execute($b);
return $d;
}
Пример $marketsData:
$marketsData = array("a09c35efc909141cfe1ebad6d77b663c" => array("e"=> 931, "f"=> 21033819, "i"=> 4, "v" => "3.5", "b"=> "4:5"));
Какие еще есть методы, правки, которые мне нужно внести, что бы еще сильнее сжать скорость выполнения данной операции?
Ответы (2 шт):
Создайте дублирующую MyISAM таблицу и делайте пакетные вставки в неё - это должно работать быстрее.
Сначала truncate table, потом вставка примерно 75683 строк данных и потом перегонка данных из дублирующей таблицы в основную одним запросом:
INSERT INTO `markets` (`id`, `event_id`, `market_id`, `intext`, `val`, `text`, `version`,`del`)
SELECT `id`, `event_id`, `market_id`, `intext`, `val`, `text`, `version`,`del` FROM `markets_tmp`
ON DUPLICATE KEY UPDATE
`version` = IF(`text` <> VALUES(`text`) OR `val` <> VALUES(`val`) OR `del` <> VALUES(`del`), VALUES(`version`), `version`),
`text` = IF(`text` <> VALUES(`text`), VALUES(`text`), `text`),
`val` = IF(`val` <> VALUES(`val`), VALUES(`val`), `val`),
`del` = IF(`del` <> VALUES(`del`), VALUES(`del`), `del`)
Заключить весь цикл в транзакцию. На все вставки уйдет меньше секунды.
Еще можно использовать подготовленные выражения по человечески, 1 prepare - много execute(). Но прирост будет небольшой, процентов 5.