Mysql游标替代/优化-每行更新太慢

Question

我正在寻求优化基于光标的更新或实际替换它...

情况

我们正在开展促销事件，我想跟踪每个事件的用户事件。

逻辑

每个营销事件都会推送到特定批处理 - 我们的客户群分割

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

默认情况下，段表中的跟踪结束日期设置为该月的最后一天，但我创建了一个过程来检查和更新此字段。(Campaign_id 根据事件日期顺序发布，因此最早的事件具有最小的campaign_id 值，反之亦然)跟踪是按日历月进行的。

更新场景

对于分段表中的每条记录，检查相同的 customer_id 是否出现在未来的广告事件中，以及具有较大跟踪开始日期的广告事件是否具有相同的 CTA。

如果为 TRUE:将该记录的tracking_end_date 更改为新广告系列的前一天。

如果为 FALSE:保留tracking_start_date 月份的最后一天作为tracking_end_date。

如果更新未完成，那么我们将对出现在多个广告系列中且具有相同 CTA 的客户的交易进行双倍/三倍计数。

下面是我当前正在使用的程序，但问题是它太慢了。

这些过程位于另一个过程中，该过程循环遍历当月的 Campaign_id，然后在提供相关的 Campaign_id 时调用此过程

    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无法通过自引用查询进行更新)

希望我清楚并提前感谢您的想法

Answer 1

使用自连接可以让您两次引用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