滾動更新 innodb 表中的所有行（如果中止則不回滾）

我有下表，它儲存社會保障死亡主文件數據庫（~86M 行）：

CREATE TABLE `death_master_files` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ssn_an` smallint(5) unsigned DEFAULT NULL,
 `ssn_gn` tinyint(3) unsigned DEFAULT NULL,
 `ssn_sn` smallint(5) unsigned DEFAULT NULL,
 `as_of` date DEFAULT NULL,
 `change_type` varchar(1) DEFAULT NULL,
 `last_name` varchar(20) DEFAULT NULL,
 `first_name` varchar(15) DEFAULT NULL,
 `middle_name` varchar(15) DEFAULT NULL,
 `name_suffix` varchar(4) DEFAULT NULL,
 `verify_proof_code` varchar(1) DEFAULT NULL,
 `death_year` smallint(4) unsigned DEFAULT NULL,
 `death_month` tinyint(3) unsigned DEFAULT NULL,
 `death_day` tinyint(3) unsigned DEFAULT NULL,
 `birth_year` smallint(4) unsigned DEFAULT NULL,
 `birth_month` tinyint(3) unsigned DEFAULT NULL,
 `birth_day` tinyint(3) unsigned DEFAULT NULL,
 `state_of_residence` varchar(2) DEFAULT NULL,
 `last_known_zip_residence` varchar(5) DEFAULT NULL,
 `last_known_zip_payment` varchar(5) DEFAULT NULL,
 `extra` varchar(7) DEFAULT NULL,
 `lifespan` mediumint(8) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idx_ssn_as_of` (`ssn_an`,`ssn_gn`,`ssn_sn`,`as_of`),
 KEY `index_death_master_files_on_last_name_and_first_name` (`last_name`,`first_name`),
 KEY `idx_dob_ssn` (`birth_year`,`birth_month`,`ssn_an`)
) ENGINE=InnoDB AUTO_INCREMENT=85952194 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

我嘗試lifespan使用填充update death_master_files set lifespan = datediff(concat(death_year, "-", death_month, "-", ifnull(death_day,1)), concat(birth_year, "-", birth_month, "-", ifnull(birth_day,1)));

不幸的是，有一個不相關的問題導致伺服器重新啟動，從而中途終止了事務。現在我正在查看ROLLING BACK 1 lock struct(s), heap size 376, 1 row lock(s), undo log entries 32937515我的SHOW ENGINE INNODB STATUS，它阻止了該表上的所有進一步操作，直到它完成。:(

一種速度改進似乎是使用update death_master_files set lifespan = datediff(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (ifnull(death_day,1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (ifnull(birth_day,1)-1) DAY);而不是字元串操作。

無需對此進行任何交易。該表尚未使用，該lifespan列是NULL此更新填充的值。更新是多執行不變的。目前數據庫上也沒有從站。

我要讓它完成回滾（這需要幾個小時……）。不過，下次我執行它時，我希望它執行時只寫，沒有回滾/事務，這樣如果有東西死了，它可以從中斷的地方恢復。

那麼：關於如何UPDATE在整個表上執行，每行滾動更新（無鎖定/事務）的任何建議？

（獎勵：如果我也不必OPTIMIZE TABLE在此之後執行操作，那就太好了，因為使用 8 GB 表也需要一段時間。這是自填充數據庫以來的唯一更改

$$ by 1krow bulk inserts from the raw file parser $$.)

我最終這樣做了，分批進行 1krow （如果沒有要更新的內容，每批大約需要 0.2 秒，否則需要 2 秒；基本上，如果是 10k、5k 或 1k 的批次，每行的總速度相同） ：

SET SESSION sql_mode = 'strict_all_tables';
SET SESSION sql_warnings = 1;
\W
DELIMITER //
DROP PROCEDURE IF EXISTS update_dmf_lifespan//
CREATE PROCEDURE update_dmf_lifespan(INOUT i INT)
BEGIN
SET @i = i;
SET @max = (select id from death_master_files order by id desc limit 1);
setloop: LOOP
 UPDATE death_master_files SET lifespan = DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) WHERE DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) >= 0 and id BETWEEN @i AND @i+1000;
 SELECT @i, ROW_COUNT(), LAST_INSERT_ID();
 SELECT *, DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) as diff from death_master_files WHERE DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) < 0 and id BETWEEN @i AND @i+1000;
 SET @i = @i + 1000;
 IF @i + 1000 > @max THEN
  LEAVE setloop;
 END IF;
END LOOP setloop;
END
//
SET @i = 1//
CALL update_dmf_lifespan(@i)//
DROP PROCEDURE IF EXISTS update_dmf_lifespan//
DELIMITER ;

……所以這可能需要 2 天的時間才能執行，OPTIMIZE TABLE之後還要更多。：-/

另外，我還沒有弄清楚如何同時獲得SHOW WARNINGSand ROW_COUNT()，因為它們似乎不能在單個語句中連接，並且兩個輸出結果都基於前一個語句（包括彼此）。

我已將其設置為帶有可恢復點的嚴格模式，這讓我可以在收到警告後手動檢查和恢復。

這肯定不是最好的選擇，儘管我不確定什麼會更好。對於死亡日期在出生日期之前的情況（數據並非完全 100% 可靠），因此它會在生命週期未簽名時引發，它不會更新，而是使用 select 輸出。但是，這不會擷取其他警告來源。

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