使用觸發器時避免死鎖

我有一個帶有名為subscribers. 引擎是 InnoDB。該表大約有 100,000 行。此表包含多個列：

CREATE TABLE `subscribers` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `userID` int(11) DEFAULT NULL,
 `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `custom_fields` longtext COLLATE utf8mb4_unicode_ci,
 `list` int(11) DEFAULT NULL,
 `unsubscribed` int(11) DEFAULT '0',
 `bounced` int(11) DEFAULT '0',
 `bounce_soft` int(11) DEFAULT '0',
 `complaint` int(11) DEFAULT '0',
 `last_campaign` int(11) DEFAULT NULL,
 `last_ares` int(11) DEFAULT NULL,
 `timestamp` int(100) DEFAULT NULL,
 `join_date` int(100) DEFAULT NULL,
 `confirmed` int(11) DEFAULT '1',
 `messageID` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `ip` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `referrer` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `method` int(1) DEFAULT NULL,
 `added_via` int(1) DEFAULT NULL,
 `gdpr` int(1) DEFAULT '0',
 `notes` text COLLATE utf8mb4_unicode_ci,
 PRIMARY KEY (`id`),
 KEY `s_list` (`list`),
 KEY `s_unsubscribed` (`unsubscribed`),
 KEY `s_bounced` (`bounced`),
 KEY `s_bounce_soft` (`bounce_soft`),
 KEY `s_complaint` (`complaint`),
 KEY `s_confirmed` (`confirmed`),
 KEY `s_timestamp` (`timestamp`),
 KEY `s_email` (`email`),
 KEY `s_last_campaign` (`last_campaign`),
 KEY `s_messageid` (`messageID`),
 KEY `s_country` (`country`),
 KEY `s_referrer` (`referrer`),
 KEY `s_method` (`method`),
 KEY `s_added_via` (`added_via`),
 KEY `s_gdpr` (`gdpr`)
) ENGINE=InnoDB AUTO_INCREMENT=446314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

這些列跟踪訂閱者的電子郵件、訂閱狀態等。我想跟踪對此表的任何更改，以使另一個數據庫、不同的伺服器和不同的結構保持最新的更改。

所以我創建了另一個表，subscribers_update.

CREATE TABLE IF NOT EXISTS subscribers_update (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `subscriber_id` int(11) DEFAULT NULL,
   `email` varchar(100) DEFAULT NULL,
   `list` int(11) DEFAULT NULL,
   `action` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

然後分別為UPDATE、INSERT和設置 3 個不同的觸發器。DELETE

# when user updates his or her information
DELIMITER $$

CREATE TRIGGER after_subscribers_update
   AFTER UPDATE ON subscribers
   FOR EACH ROW
BEGIN
   IF (SELECT COUNT(*) FROM subscribers_update
           WHERE subscriber_id = NEW.id
             AND email = NEW.email
             AND list = NEW.list
             AND action = 'update') = 0
   THEN
       INSERT INTO subscribers_update
       SET subscriber_id = NEW.id,
           email = NEW.email,
           list = NEW.list,
           action = 'update';
   END IF;
END;

$$
DELIMITER ;

# when user is added through a sign-up form
DELIMITER $$

CREATE TRIGGER after_subscribers_insert
   AFTER INSERT ON subscribers
   FOR EACH ROW
BEGIN
   IF (SELECT COUNT(*) FROM subscribers_update WHERE subscriber_id = NEW.id AND email = NEW.email AND list = NEW.list
              AND action = 'insert') = 0
   THEN
       INSERT INTO subscribers_update
       SET subscriber_id = NEW.id,
           email = NEW.email,
           list = NEW.list,
           action = 'insert';
   END IF;
END;

$$
DELIMITER ;

# when user is deleted
DELIMITER $$

CREATE TRIGGER after_subscribers_delete
   AFTER DELETE ON subscribers
   FOR EACH ROW
BEGIN
   IF (SELECT COUNT(*) FROM subscribers_update WHERE subscriber_id = OLD.id AND email = OLD.email AND list = OLD.list
            AND action = 'delete') = 0
   THEN
       INSERT INTO subscribers_update
       SET subscriber_id = OLD.id,
           email = OLD.email,
           list = OLD.list,
           action = 'delete';
   END IF;
END;

$$
DELIMITER ;

最後，我有一個 cron 作業，它會定期檢查subscribers_update任何更改，並執行腳本來更新第二個遠端數據庫。同樣，第二個數據庫具有完全不同的結構。

經過仔細檢查，這些觸發器似乎大大降低了伺服器速度，並導致多個死鎖。一個“簡單”的查詢，例如UPDATE subscribers SET bounce_soft = 0 WHERE list IN (15);幾乎殺死伺服器幾分鐘，並根據show engine innodb status.

對於數據庫專家，我還很遙遠，所以我想知道如何優化我的觸發器，和/或是否有更好的方法來跟踪此表的更改以更新第二個數據庫。

IF (SELECT COUNT(*) FROM subscribers_update
       WHERE subscriber_id = NEW.id
         AND email = NEW.email
         AND list = NEW.list
         AND action = 'update') = 0

–>

IF ( NOT EXISTS( SELECT 1 FROM subscribers_update
       WHERE subscriber_id = NEW.id
         AND email = NEW.email
         AND list = NEW.list
         AND action = 'update') )

並添加一個複合索引：

INDEX(subscriber_id, email, list, action)  -- in any order

引用自：https://dba.stackexchange.com/questions/252358