Альтернатива/оптимизация курсора Mysql - обновления каждой строки слишком медленные

Я хочу оптимизировать обновление на основе курсора или фактически заменить его...

Ситуация

Мы проводим рекламные кампании, и я хотел бы отслеживать активность пользователей для каждой кампании.

Логика

Каждая кампания помещается в определенный пакет — сегмент нашей клиентской базы.

CREATE TABLE `segments` (
  `campaign_id` int(6) DEFAULT NULL,
  `customer_id` varchar(20) DEFAULT NULL,
  `tracking_start_date` date DEFAULT NULL,
  `tracking_end_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

tracking_start_date — это дата кампании, а tracking_end_date — дата окончания отслеживания.

Каждая кампания имеет свой собственный «Призыв к действию (CTA)», который является типом транзакции, которую мы продвигаем и надеемся, что клиенты начнут использовать ее после кампании.

CREATE TABLE `cta` (
  `campaign_id` int(11) DEFAULT NULL,
  `Date` date DEFAULT NULL,
  `segment` varchar(100) DEFAULT NULL,
  `message` varchar(320) DEFAULT NULL,
  `Size` int(11) DEFAULT NULL,
  `cta` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

По умолчанию tracking_end_date в таблице сегментов установлен на последний день месяца, но я создал процедуру для проверки и обновления этого поля. (Идентификатор кампании выдается последовательно в соответствии с датой кампании, поэтому самая ранняя кампания имеет наименьшее значение идентификатора кампании, и наоборот). Отслеживание выполняется по календарному месяцу за месяцем.

Сценарий обновления

Для каждой записи в таблице сегментов проверьте, появляется ли тот же самый customer_id в будущей кампании и имеет ли эта кампания с большей tracking_start_date тот же CTA.

Если TRUE: измените tracking_end_date для этой записи на день перед новой кампанией.

Если FALSE: сохранить последний день месяца tracking_start_date в качестве tracking_end_date.

Если обновление не будет выполнено, то мы будем иметь двойной/тройной... учет транзакций для клиентов, которые появляются в нескольких кампаниях и имеют один и тот же CTA.

Ниже приведена процедура, которую я сейчас использую, но проблема в том, что она слишком медленная.

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

    CREATE DEFINER=`root`@`localhost` PROCEDURE `set_campaign_end_date_child`(IN var_campaign_id INT)
BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE var_customer_id VARCHAR(20);
 DECLARE var_tracking_start_date DATE;
 DECLARE cur1 CURSOR FOR SELECT DISTINCT customer_id FROM segments WHERE campaign_id =var_campaign_id; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 -- perform cursur update loop now
OPEN cur1;
read_loop: LOOP
 IF done THEN
 LEAVE read_loop;
 END IF;
 FETCH cur1 INTO var_customer_id;
 SELECT DISTINCT DATE INTO var_tracking_start_date FROM cta WHERE campaign_id = var_campaign_id;
 UPDATE segments SET tracking_end_date = 
 (SELECT  IFNULL(DATE_SUB(MIN(tracking_start_date),INTERVAL 1 DAY),LAST_DAY(var_tracking_start_date)) FROM segments_temp 
 WHERE customer_id = var_customer_id 
 AND campaign_id 
 IN(SELECT campaign_id FROM cta WHERE cta IN (SELECT cta FROM cta WHERE campaign_id = var_campaign_id)
 AND campaign_id > var_campaign_id))
 WHERE customer_id = var_customer_id AND campaign_id =var_campaign_id ;
 END LOOP read_loop;
CLOSE cur1;
 END$$

DELIMITER ;

PS: перед началом процедуры я делаю копию таблицы сегментов в другой таблице с именем segments_temp и выполняю сравнение оттуда (это связано с тем, что MySQL не может выполнять обновления из самоссылающегося запроса)

Надеюсь, я ясно и заранее спасибо за ваши идеи


mysql stored-procedures query-performance cursors
person zamentali    schedule 27.03.2015    source источник
comment
Вы можете ссылаться на ту же таблицу в UPDATE, используя самосоединение вместо подзапроса.   -  person Barmar    schedule 27.03.2015
comment
Спасибо Бармар. Хотите поделиться, как бы вы написали это тогда?   -  person zamentali    schedule 27.03.2015

Ответы (1)


arrow_upward
1
arrow_downward

Используйте самосоединение, чтобы дважды ссылаться на таблицу segments. Если я правильно понимаю ваш код, я думаю, что это эквивалентное обновление:

UPDATE segments AS s1
LEFT JOIN (SELECT customer_id, DATE_SUB(MIN(tracking_start_date),INTERVAL 1 DAY) AS new_tracking_start_date
           FROM segments AS s2
           WHERE campaign_id IN (
                SELECT campaign_id 
                FROM cta 
                WHERE cta IN (
                    SELECT cta 
                    FROM cta 
                    WHERE campaign_id = var_campaign_id)
                AND campaign_id > var_campaign_id)
           GROUP BY customer_id) AS s3 
ON s1.customer_id = s3.customer_id
SET tracking_end_date = IFNULL(new_tracking_start_date, LAST_DAY(tracking_start_date))
WHERE campaign_id = var_campaign_id
person Barmar    schedule 27.03.2015
comment
спасибо, Бармар. Выглядит определенно лучше, чем цикл курсора. Позвольте мне попробовать это - person zamentali; 27.03.2015
comment
Спасибо, Бармар... Работает как шарм!!. Просто нужно было добавить недостающую скобку рядом с последним предложением where - person zamentali; 27.03.2015
comment
На самом деле это после IFNULL, исправил. - person Barmar; 27.03.2015