Как оптимизировать хранимую процедуру?

Question

[Kerm]

При обновлении интернет магазина, создается временная таблица, после занесения данных в которую, выполняется хранимая процедура:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sync_fl`()
BEGIN
  DROP TABLE IF EXISTS `sh_prod_im__`;
  ALTER TABLE `sh_prod_im` RENAME TO `sh_prod_im__`;
  ALTER TABLE `sh_prod_im_` RENAME TO `sh_prod_im`;
END$$
DELIMITER ;

Интернет-магазин имеет много посещений в день и видать кто то ловит момент переименования таблицы и возникает ошибка (Table 'sh_prod_im' doesn't exist.), как можно оптимизировать данный процесс чтобы подобных проблем не возникало? Читал что процесс изменения названия таблицы занимает 0.10 sec и это не подходит получается.

Answer 1

[Rsa97]

Транзакции тут не помогут, так как DROP TABLE/ALTER TABLE вызывают немедленный COMMIT.
Остаётся только менять саму процедуту обновления таблицы. Например так:

BEGIN TRANSACTION;
UPDATE `sh_prod_im` SET `updated` = 0;

Затем вставляем/обновляем строки:

INSERT 
  INTO `sh_prod_im` (`article`, ..., `updated`) 
  VALUES (:article, ..., 1) 
  ON DUPLICATE KEY UPDATE ..., `updated` = 1;

И удаляем необновлённые:

DELETE FROM `sh_prod_im` WHERE `updated` = 0;
COMMIT;

Comments

[Kerm]

Изучаю Ваше решение, спасибо.

Посмотрите вот так правильно?

CREATE TABLE `sh_prod_im` (
  `prod` int(10) unsigned NOT NULL,
  `type` int(10) unsigned NOT NULL,
  `pict` int(10) unsigned NOT NULL,
  `updated` int(1) unsigned default '1',
  PRIMARY KEY (`prod`,`type`,`pict`)
) ENGINE=InnoDB;

У updated указал по умолчанию значение 1 для новых, добавленных записей, далее изменил процедуру:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sync_fl`()
BEGIN
  UPDATE `sh_prod_im` SET `updated` = 0;

  INSERT INTO `sh_prod_im` (`prod`,`type`,`pict`) 
    SELECT `prod`,`type`, `pict`
    FROM `sh_prod_im_`
    ON DUPLICATE KEY UPDATE `updated` = 1;

  DELETE FROM `sh_prod_im` WHERE `updated` = 0;
END$$
DELIMITER;

[Rsa97]

Kerm, Обычно нет смысла оформлять такой код в хранимой процедуре, его можно использовать прямо в скрипте, вместо создания таблицы `sh_prod_im_`.
Действительно ли первичным ключом должна быть комбинация (`prod`,`type`,`pict`)?
И используйте транзакцию, тогда для остальных процессов обновление таблицы будет выглядеть как мнгновенный процесс.

[Kerm]

Действительно ли первичным ключом должна быть комбинация (`prod`,`type`,`pict`)?

prod - это не уникальное значение
type - это тип фоток, в пределах одного товара это уникальное значение, бывает от 0 до 50
pict - это id фотографии, не уникальное значение

Уникальные значения только prod+type или prod+type+pict

Селект вот такой используется из этой таблицы:

SELECT * FROM `sh_prod_im` WHERE `prod` IN ('.implode(',',$prod_k).') ORDER BY `type`

[Kerm]

И используйте транзакцию, тогда для остальных процессов обновление таблицы будет выглядеть как мнгновенный процесс.

Посмотрите, вот так?

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sync_fl2`()

BEGIN

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

START TRANSACTION;

  UPDATE `sh_prod_im` SET `updated` = 0;

  INSERT INTO `sh_prod_im` (`prod`,`type`,`pict`) 
    SELECT `prod`,`type`,`pict`
    FROM `sh_prod_im_`
    FOR UPDATE
    ON DUPLICATE KEY UPDATE `updated` = 1;

  DELETE FROM `sh_prod_im` WHERE `updated` = 0;

COMMIT;
END$$
DELIMITER ;

[Rsa97]

Kerm, В принципе, так.

[Kerm]

И прошу еще разрешить сомнение еще по одному двум вопросам, если в INSERT INTO `sh_prod_im` (`prod`,`type`,`pict`) у меня в конце не указана колонка updated, но в конце on duplucate key я прописан updated = 1 это не чего страшного? я проверил ошибок нет, не уверен в правильности отработки скрипта.

И еще стоит ли в SELECT в конце дописывать FOR UPDATE?

[Rsa97]

Kerm, FOR UPDATE дописывать не надо, вы не меняете таблицу `sh_prod_im_`.
`updated` в основной части INSERT'а можно не указывать, вы задали для этого поля 1 по умолчанию, новые строки будут создаваться с 1.

[Kerm]

Спасибо Вам, буду тестировать. А вот еще, а нужно ли где указывать SET AUTOCOMMIT=0 или нет? Мне надо только в этой процедуре попробовать транзакцию сделать и все.

[Rsa97]

Kerm, Не нужно. При явном старте транзакции через BEGIN автокоммит отключается до конца транзакции.

[Kerm]

А использование транзакции в самом первом моём примере реализации процедуры:

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;
CREATE DEFINER=`root`@`%` PROCEDURE `sync_fl`()
BEGIN
  DROP TABLE IF EXISTS `sh_prod_im__`;
  ALTER TABLE `sh_prod_im` RENAME TO `sh_prod_im__`;
  ALTER TABLE `sh_prod_im_` RENAME TO `sh_prod_im`;
COMMIT;
END$$
DELIMITER ;

Не решило бы ошибку с недоступностью таблицы при переименовании, как я понял из Ваших слов выше, транзакция бы применялась мгновенно.

У меня из-за решения что выше я написал по вашим советам, увеличилось время импорта товаров.

[Rsa97]

Kerm, Да. DROP и ALTER принудительно завершают транзакцию.