Как сравнить две огромные таблицы (3 и 2 млн строк) и обновить одну из них?

Question

[Александр]

Доброго дня! Есть основная таблица с продукцией на 4 млн строк (6Гб) и вторая таблица с обновленным ассортиментом (~3 млн строк, 4 Гб). Тип таблиц - InnoDB. Набор полей таблиц одинаковый.

Основная таблица продукции:

CREATE TABLE `items` (
 `aid` int(11) AUTO_INCREMENT,
 `id` varchar(100),
 `id_shop` tinyint(4),
 `name` text,
 `description` text,
 `enabled` tinyint(1),
... еще 20 полей ...
 PRIMARY KEY (`aid`),
 KEY `se` (`id_shop`,`enabled`),
... еще много индексов ...
) ENGINE=InnoDB AUTO_INCREMENT=43657573 DEFAULT CHARSET=utf8

Нужно сравнить их по совпадению значений 2х полей (id и id_shop):
1. Если такого товара нет - добавить его в основную таблицу.
2. Если товар есть - обновить некоторые его поля в основной таблице.

Сначала решил попробовать "плохое" решение: на пхп циклом пройти по второй таблице, во время каждой итерации искать совпадения со старой таблицей, и потом делать INSERT или UPDATE. Это решение занимает огромное кол-во времени ~10 часов.

После этого наткнулся на "INSERT ON DUPLICATE KEY UPDATE".

INSERT INTO items (id, id_shop, name) VALUES ('1', '2', '3') 
ON DUPLICATE KEY 
UPDATE name = 'new_name', description = 'new description'

Но запутался, какой PRIMARY KEY нужно использовать в таком запросе.

1. Нужно удалить PRIMARY KEY у 'aid', и вместо него создать ключ: PRIMARY KEY (`id`, `id_shop`)?
2. Можно ли в одним этим запросом решить мою задачу? Или придется всё равно проходить циклом по каждой строке обновленной таблицы и выполнять "INSERT ON DUPLICATE KEY UPDATE"?

Подскажите, пожалуйста, как правильно использовать эту конструкцию в моем случае?
Спасибо!

Answer 1

[Rsa97]

Нужен UNIQUE KEY (`id`, `id_shop`)
Запрос будет примерно такой:

INSERT INTO `items` (`id`, `id_shop`, `name`, `description`, ...)
  SELECT `id`, `id_shop`, `name` AS `new_name`, `description` AS `new_description`, ...
    FROM `new_items` 
  ON DUPLICATE KEY UPDATE `name` = `new_name`, `description` = `new_description`, ...

Перед таким большим запросом есть смысл удалить остальные ключи, оставить только PRIMARY KEY и UNIQUE KEY, после запроса пересоздать их.

Comments

[Александр]

Попробовал ваш запрос, ругается на последнюю строку: ON DUPLICATE KEY UPDATE `name` = `new_name`.... Говорит new_name не определен в списке полей. Как бы этот вложенный SELECT вынести наружу?

[Александр]

Разобрался, такое решение работает: INSERT INTO items (id, id_shop, name, description) SELECT id, id_shop, name, description FROM new_items ON DUPLICATE KEY UPDATE name = new_items.name, description = new_items.description. Спасибо большое за помощь!

Answer 2

[Roman Fov]

A обновление можно так:

UPDATE `items`, `items_new` SET `items`.`name` = `items_new`.`name` WHERE `items`.`aid` = `items_new`.`aid` AND `items`.`shop_id` = `items_new`.`shop_id`