MySQL 8.0.22：加速cod_res_rev空值填充的优化方案咨询

需求背景 #

数据库存储员工个人合同数据，需提升数据质量：cod_res_rev列仅部分有值，其余为NULL，要求按worker_id分组，用组内最近的前一行非空cod_res_rev值填充后续NULL，直到遇到下一个非空值为止。

当前采用循环存储过程实现，但数据库含2500万条记录，单员工最大记录数约4000，该方案效率极低，需替换为高效更新方案。

示例表结构及数据 #

drop table if exists ml_arm;
create table ml_arm (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    worker_id int,
    dt_start date,
    dt_end date,
    cod_res varchar(50),
    cod_res_rev varchar(50),
    id_lag int,
    id_lead int,
    PRIMARY KEY (id)
);

insert into 
    ml_arm(id, worker_id, dt_start, dt_end, cod_res, cod_res_rev, id_lag, id_lead)
values
    ('12', '20', '2014-05-02', '2014-07-08', '', 'I040', NULL, '13'),
    ('13', '20', '2017-01-14', '2017-01-31', '', NULL, '12', '14'),
    ('14', '20', '2017-11-06', '2017-12-15', 'I040', NULL, '13', NULL),
    ('20', '29', '2014-11-24', '2017-02-11', '', 'N.D.', NULL, NULL),
    ('21', '42', '2016-01-22', '2016-05-05', 'G582', 'G582', NULL, NULL),
    ('23', '45', '2013-08-07', '2014-04-06', 'G582', 'G582', NULL, '24'),
    ('24', '45', '2014-05-07', '2014-05-10', 'G582', NULL, '23', NULL),
    ('25', '48', '2012-08-11', '2012-08-31', 'G582', 'G582', NULL, '26'),
    ('26', '48', '2013-08-10', '2013-08-31', 'G582', NULL, '25', NULL),
    ('53', '71', '2016-12-01', '2017-05-31', '', 'N.D.', NULL, '54'),
    ('54', '71', '2017-06-01', '2020-05-29', '', NULL, '53', '55'),
    ('55', '71', '2020-06-01', '2099-01-01', '', NULL, '54', NULL)
;

当前低效的循环存储过程 #

-- 基于员工最大记录数创建辅助表
drop table if exists max_count;
create table max_count 
as select worker_id, count(*) n 
from ml_arm
group by worker_id;
alter table max_count add unique index (worker_id); 

DROP PROCEDURE IF EXISTS doiterate;
delimiter //

CREATE PROCEDURE doiterate()
BEGIN
  DECLARE total INT unsigned DEFAULT 0;
  WHILE total <= (select MAX(n) from max_count) DO

update ml_arm a
left outer join ml_arm b on a.id_lag = b.id
set a.cod_res_rev = 
    case 
    when a.cod_res_rev is NULL and a.worker_id = b.worker_id and b.cod_res_rev is not NULL
    then b.cod_res_rev 
    else a.cod_res_rev 
    end;

    SET total = total + 1;
  END WHILE;
END//  

delimiter ;

CALL doiterate(); 

高效更新方案（基于窗口函数） #

利用MySQL 8.0支持的窗口函数，通过一次扫描完成分组和填充计算，彻底避免循环迭代的低效问题。

步骤1：创建优化索引（可选但强烈建议） #

为窗口函数的分区和排序操作创建复合索引，大幅提升查询效率：

CREATE INDEX idx_worker_dt ON ml_arm(worker_id, dt_start);

步骤2：执行高效更新语句 #

WITH filled_data AS (
    SELECT 
        id,
        -- 同一分组内取非空的cod_res_rev值
        MAX(cod_res_rev) OVER (
            PARTITION BY worker_id, grp
            ORDER BY dt_start
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS filled_cod_res_rev
    FROM (
        SELECT 
            *,
            -- 按worker_id分组，累计统计非空cod_res_rev的次数，生成分组标识grp
            COUNT(cod_res_rev) OVER (
                PARTITION BY worker_id
                ORDER BY dt_start
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS grp
        FROM ml_arm
    ) t
)
-- 仅更新原表中cod_res_rev为NULL的记录
UPDATE ml_arm a
JOIN filled_data b ON a.id = b.id
SET a.cod_res_rev = b.filled_cod_res_rev
WHERE a.cod_res_rev IS NULL;

方案说明 #

1. 分组标识生成：通过COUNT(cod_res_rev)窗口函数，按worker_id分组、dt_start排序，累计统计非空值的出现次数，将连续的空值记录归为同一grp分组（同一分组共享最近的非空值）。
2. 空值填充：在同一grp分组内，用MAX(cod_res_rev)提取唯一的非空值，填充该分组内所有空值记录。
3. 高效性：仅需两次扫描（一次计算分组，一次更新），避免了循环存储过程中最多4000次全表更新的巨大开销，适合处理千万级数据量。

内容的提问来源于stack exchange，提问作者Nicola Caravaggio