bulk update через temporаry table без lock. И не будет блокировать селект. Добавлять новые, удалять старые - тут больше вопрос к алгоритму скрипта.
Я еще раз пишу, update медленнее insert в два раза, потому что при update = delete+insert. Создание temporary table, даже если и в памяти, после от туда заливать в основную, и удалять temporart table это все мутарно и тоже не нужные замедления.
Задача, к примеру вот такая:
Цель, есть таблица с большим кол-во товаров, и приходит с импортом большое кол-во товаров. То, что приходит, приходит всегда полностью, не частично, частичное обновление/добавление не предусмотрено. Нужно удалить то что не пришло, пропустить схожие и добавить новые. Как бы Вы это сделали?
Вот мое решение:
У меня есть таблица products, я каждый раз при импорте дропаю и вновь создаю таблицу products_ и далее запускаю хранимую процедуру с транзакцией:
Код:
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sync_fl`()
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
ROLLBACK;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
ROLLBACK;
END;
START TRANSACTION;
UPDATE `products` SET `updated` = 0;
INSERT INTO `products` (`prod`,`type`,`pict`)
SELECT `prod`,`type`,`pict`
FROM `products_`
ON DUPLICATE KEY UPDATE `updated` = 1;
DELETE FROM `products` WHERE `updated` = 0;
COMMIT;
END$$
DELIMITER ;
В данной процедуре, я сначала сбрасываю у всех товаров признак обновления `updated` = 0, у таблицы products значение по умолчанию у этой колонки 1. Далее делаю insert из таблицы с пришедшими данными, в существующей таблице устанавливаю 1 если найдено сходство и добавляю новые товары если те есть. Те, товары которых не было в импорте удаляются. Обновление происходит мгновенно.
Хочу понять, является ли мой пример лучшим решением выхода из ситуации или можно сделать лучше?
