Запись больших xml в MYSQL?

Question

[arsenaljek]

Помогите с проблемой.
Есть xml-каталоги. 14 штук. Суммарный их объем составляет 1.4гб. Я оттуда парсю товары. Структура обычный YML.
Скрипт скачивает их на сайт, затем в цикле foreach обходит каждый файл.
И так-как в скрипте есть еще условия, что если текущая цена, которая парситься, отличаеться от цены за вчера, то идет запись, иначе запись не производим. То выполнения скрипта происходит нереально долго. Я даже не уверен, что он вообще успевает все пройти, т.к ограничения работы скрипта ограничены 10 часами.

$dir = "xml/";
$files = scandir($dir);
$files = array_diff(scandir($dir), array('..', '.')); 
 foreach ($files as $key => $value) { 
   $z->open('xml/'.$value);
  $doc = new DOMDocument;
  while ($z->read() && $z->name !== 'offer');
  while ($z->name === 'offer') {
    $node = simplexml_import_dom($doc->importNode($z->expand(), true));
    $id = mysqli_real_escape_string($link6,$node['id']);
    $url = mysqli_real_escape_string($link6,$node->url);
    $price = $node->price;
    $sql = 'INSERT INTO products (`id`,`url`,`date`) VALUES ("'.$id.'","'.htmlspecialchars(urldecode($url)).'","'.$today.'")';
  $result = mysqli_query($link6,$sql);
  $new_price = (int)$price;

  $sql = 'SELECT * FROM prices WHERE id = '.$id.'  ORDER BY date DESC ';
  $result = mysqli_query($link6,$sql);
  $row = mysqli_fetch_array($result);
  if ($row['id'] == $id AND $row['date'] == $today_new OR $row['price_product'] == $price) {
    // echo 'все совпало</br>';
    $articles1[] = $row['id'];
  } else {
    $articles2[] = $row['id'];
    // echo 'несовпало</br>';
    if ($row['price_product'] == $new_price) {
      // echo 'не пишем дата сегоднящная уже записана';
    } else {
      $sql = 'INSERT INTO prices (`id`, `price_product`, `date`) VALUES ("'.$id.'","'.(int)$price.'","'.$today.'")';
      $result = mysqli_query($link6,$sql);
    }
  }
  $z->next('offer');
  }
 }

Можете подсказать, как оптимизировать. Увеличив скорость записи в бд? И правильно ли делать так, как делаю я?

Comments

[Dmitry Bay]

Читали теорию своего вопроса по загрузке больших файлов?

https://php.ru/forum/threads/zagruzka-bolshix-csv-...
Парень там пишет.

я в свое время делал на php импорт реально больших файлов, так по моему опыту, никакой объектный интерфейс XML не годится. потому что файл сам по себе велИк, а в виде дерева объектов он еще сильнее распухает.

А теперь по существу, сколько там у вас позиций в файле? очень много, верно? И на эти очень много вы делаете max 2 запроса на то, чтобы поправиьт цену. Почему нельзя составить sql запрос с join, который выполнит задачу? Возможно даже с разделением определенным, если у вас там миллионы позиций. Т.е. вначале импортировали. Затем сделали update c join.

И еще вопрос - у вас получается составной индекс на products? id+date? зачем?

Answer 1

[Rsa97]

Жжжуть. Делать выборку в цикле для каждой строки, да ещё и генерируя каждый раз запрос - это, наверно, худший из возможных вариантов.
В таблицу `products` добавляете колонку `price`. Вешаете на эту таблицу триггеры на вставку и изменение строки, которые при добавлении или изменении цены заносят её в таблицу `prices`. Напрямую с таблицей `prices` из этого скрипта не работаете.
В цикле формируете и накапливаете наборы данных для вставки. Как только накопится некоторое количество, вставляете их одним запросом. Используете ON DUPLICATE KEY UPDATE для перезаписи изменённых значений. По окончании цикла выгружаете оставшиеся наборы.

Примерно так

$data = [];
while ($z->name === 'offer') {
  ...
  $data[] = '("'.$id.'","'.htmlspecialchars(urldecode($url)).'","'.$today.'",'.(int)$price.')';
  if (count($data) > 99) {
    $sql = 'INSERT INTO `products` (`id`,`url`,`date`,`price`) VALUES ' 
         . implode(',', $data)
         . 'ON DUPLICATE KEY UPDATE `url` = VALUES(`url`), `price` = VALUES(`price`)';
    mysqli_query($link6,$sql);
    $data = [];
  }
  ...
}
if (count($data) > 0) {
  $sql = 'INSERT INTO `products` (`id`,`url`,`date`,`price`) VALUES ' 
       . implode(',', $data)
       . 'ON DUPLICATE KEY UPDATE `url` = VALUES(`url`), `price` = VALUES(`price`)';
  mysqli_query($link6,$sql);
}

Comments

[arsenaljek]

Спасибо, что пытаетесь мне помочь. У меня есть некоторые недопонимания.

В таблицу `products` добавляете колонку `price`. Вешаете на эту таблицу триггеры на вставку и изменение строки, которые при добавлении или изменении цены заносят её в таблицу `prices`.

Что значит

Вешаете на эту таблицу триггеры на вставку и изменение строки

?

[Rsa97]

arsenaljek, CREATE TRIGGER

[arsenaljek]

Rsa97, Первый раз с этим сталкиваюсь...
Пытаюсь выполнить

CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
  INSERT INTO prices Set id_products = OLD.id_products , price_product = OLD.price;
END;

Говорит, что синтаксическая ошибка.
Поле id_products находится в обоих таблицах (products и prices) и в нем содержится артикул товара

[Rsa97]

arsenaljek, Точка с запятой после INSERT рассматривается как окончание команды CREATE TRIGGER. Надо менять символ завершения команды

DELIMITER $$
CREATE TRIGGER ...
  ...
END
$$
DELIMITER ;

[arsenaljek]

Rsa97, Запустил скрипт. В таблицу products было добавлено около 600 000 товаров. В таблицу цены 60 000...
Встроил Ваш код в свой, получилось так

while ($z->read() && $z->name !== 'offer');
  $data = [];
  while ($z->name === 'offer') {
    //$node = new SimpleXMLElement($z->readOuterXML());
    $node = simplexml_import_dom($doc->importNode($z->expand(), true));
    $id_product = mysqli_real_escape_string($link6,$node['id']);
    $url = mysqli_real_escape_string($link6,$node->url);
    $price = $node->price;
    $group = $node->categoryId;
    $image = mysqli_real_escape_string($link6,$node->picture);
    $name = mysqli_real_escape_string($link6,$node->name);
    $vendor = mysqli_real_escape_string($link6,$node->vendor);
    $vendorCode = mysqli_real_escape_string($link6,$node->vendorCode);
    $description = mysqli_real_escape_string($link6,$node->description);
    $barcode = mysqli_real_escape_string($link6,$node->barcode);
    $data[] = '("'.$id_product.'","'.$vendorCode.'","'.$vendor.'","'.$barcode.'","'.$name.'","'.$description.'","'.$image.'","'.htmlspecialchars(urldecode($url)).'","'.$group.'","'.$today.'",'.(int)$price.')';
    if (count($data) > 99) {
      $sql = 'INSERT INTO `products` (`id_product`,`vendorCode`,`vendor`,`barcode`,`name`,`description`,`image_product`,`link_product`,`group`,`date`,`price`) VALUES ' 
         . implode(',', $data)
         . 'ON DUPLICATE KEY UPDATE `link_product` = VALUES(`link_product`), `price` = VALUES(`price`)';
      echo $sql.'</br>';
      mysqli_query($link6,$sql);
      $data = [];
    }
    $z->next('offer');
  }
  if (count($data) > 0) {
    $sql = 'INSERT INTO `products` (`id_product`,`vendorCode`,`vendor`,`barcode`,`name`,`description`,`image_product`,`link_product`,`group`,`date`,`price`) VALUES ' 
       . implode(',', $data)
       . 'ON DUPLICATE KEY UPDATE `link_product` = VALUES(`link_product`), `price` = VALUES(`price`)';
    mysqli_query($link6,$sql);
  }
}

Триггер добавил так

DELIMITER $$
CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
  INSERT INTO prices Set id_products = OLD.id_products , price_product = OLD.price, `date` = OLD.`date`;
END;
$$
DELIMITER ;

[Rsa97]

arsenaljek, По уму, надо записывать только изменения цен, то есть добавить в триггер IF NEW.`price` != OLD.`price` .... И удобнее сохранять именно новую цену, тогда проще потом смотреть историю.

[arsenaljek]

Rsa97, Я правильно понимаю?

DELIMITER $$
CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
IF NEW.`price` != OLD.`price` THEN
  INSERT INTO prices Set id_products = OLD.id_products  , price_product = OLD.price, `date` = OLD.`date`;
END IF;
END;
$$
DELIMITER ;

[Rsa97]

arsenaljek, Да, как-то так.

[arsenaljek]

Rsa97, Всё работает. Спасибо за подсказку и направление в изучении темы триггеров!)
Единственно, что если обе таблицы пустые, то при первом срабатывании скрипта, он не пишет цены в таблицу prices. С этим можно что-то придумать?

[Rsa97]

arsenaljek, Так вы пишете старую цену, а её ещё нету.
Тогда надо писать новую цену, и добавлять триггер на вставку (after insert)
Этот триггер, кстати, тоже лучше переделать на after update.

[arsenaljek]

Rsa97, Мммм..
Т.е теперь у нас становиться 2 триггера.
Триггер Update

DELIMITER $$
CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
IF NEW.`price` != OLD.`price` THEN
  INSERT INTO prices Set id_products = OLD.id_products  , price_product = OLD.price, `date` = OLD.`date`;
END IF;
END;
$$
DELIMITER ;

И добавляем триггер insert

DELIMITER $$
CREATE TRIGGER `insert_price` AFTER INSERT ON `products`
FOR EACH ROW BEGIN
IF NEW.`price` != NEW.`price` THEN
  INSERT INTO prices Set id_products = NEW.id_products  , price_product = NEW.price, `date` = NEW.`date`;
END IF;
END;
$$
DELIMITER ;

[Rsa97]

arsenaljek, В триггере на вставку IF не нужен, там точно данные новые :-)

[arsenaljek]

Rsa97, Спасибо, Вам за помощь. Всё работает!

[arsenaljek]

Rsa97, Можете подсказать.
Ситуация. Сейчас все товары из xml пишутся в бд как нужно. Т.е из xml пишется в базу.1 таблица товары.2 таблица цен. Цена пишется только в том случае, если прошлая цена отличается от той, которая текущая. Здесь всё ок. Но как сделать так, чтобы тот, товар, который временно или навсегда исчез и xml, тоже можно было бы , как то зафиксировать этот момент. Т.е например :
07.10.2019 в xml был товар

<offer id="1">
	<name>Эспандер</name>
	<price>999.00</price>
</offer>

Сегодня 08.10.2019 этот товар в xml отсутствует и надо бы пользователю вывести "Товар закончился"
А если завтра он опять появиться в xml c ценой, то опять записать цену.(Хорошо было бы, если бы цена записывалась только уникальной от той, что была за прошлый день).

[Rsa97]

arsenaljek, Перед импортом XML отмечайте все товары как отсутствующие, во время импорта снимайте эту отметку. Тогда после импорта на всех товарах, которых в XML не было, останется отметка.
Естественно, делать всё надо в одной транзакции.
BEGIN TRANSACTION;
UPDATE `products` SET `inStock` = 0;
INSERT (..., `inStock`) VALUES (..., 1) ON DUPLICATE UPDATE ..., `inStock` = 1;
...
COMMIT;

[arsenaljek]

Rsa97, Добавил в самое начала скрипта xml импорта строку
$sql = "UPDATE `products` SET `stock`= 0";
В таблице теперь все как надо. Есть товар 1, нету товара 0. Но теперь я не понимаю, как сделать так, чтобы когда товар нету и мы не пишем цену в таблицу prices, он и расценивал это, как товара нету, а не "цена такая-же как вчера и поэтому цену не пишем". Т.е проблема с выводом на страницу. Сейчас вывод ведь устроен таким образом, что в таблицу с ценами пишем, только если цена изменилась по сравнению с прошлой записью и вывожу я запросом все цены

$sql = "SELECT `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` WHERE `id_product` = ".$row_product['id_product']." ORDER BY `date` DESC";