如何优化update_blank_holiday_record存储过程提升执行效率

我想了解是否有更好的方式编写或过滤以下存储过程，同时希望在现有的staff_shift表触发器中实现节假日的校验与正确记录键赋值。

现有存储过程代码 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_blank_holiday_record`()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE temp_staff_record_key VARCHAR(45);
    DECLARE temp_shift_date DATE;
    DECLARE temp_update_type INT;
    
    DECLARE staff_shift_result CURSOR FOR ((SELECT DISTINCT `staff_record_key`, `shift_date`, 0 FROM `staff_shift_holiday` WHERE (`shift_day_template_record_key` = 'HOL') UNION SELECT `staff_record_key`, `shift_date`, 1 FROM `pending_update_staff_balance` WHERE (!ISNULL(`shift_date`))) ORDER BY `shift_date` ASC);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    
    
    OPEN staff_shift_result;
    get_staff_shift:LOOP
        START TRANSACTION;
        FETCH staff_shift_result INTO temp_staff_record_key, temp_shift_date, temp_update_type;
        IF finished = 1 THEN
            LEAVE get_staff_shift;
        END IF;
        IF temp_update_type = 0 THEN
            SELECT `shift_date` INTO temp_shift_date FROM `staff_shift_holiday`WHERE (`staff_record_key` = temp_staff_record_key AND `shift_day_template_record_key` = 'HOL') ORDER BY `shift_date` ASC LIMIT 1;
        END IF;
        CALL UPDATE_HOLIDAY_RECORD(temp_staff_record_key, temp_shift_date);
        CALL UPDATE_STAFF_BALANCE(temp_staff_record_key);
        IF temp_update_type = 1 THEN
            DELETE FROM `pending_update_staff_balance` WHERE (`staff_record_key` = temp_staff_record_key) ORDER BY `record_id` ASC;
        END IF;
        COMMIT;
    END LOOP;
    CLOSE staff_shift_result;
END

存储过程功能说明 #

该存储过程用于员工法定节假日分配：先以通用记录键'HOL'注册记录，随后识别员工所选节假日并替换为对应具体记录键（如HOL-34FEC）。

性能问题与慢查询日志 #

目前设置每5分钟通过事件触发该过程，但有时执行耗时近300秒，导致应用冻结无法新增数据库记录。慢查询日志如下：

# Time: 2023-11-28T21:54:18.288680+08:00
# User@Host: root[root] @ localhost [localhost]  Id: 147871
# Schema: test Last_errno: 0  Killed: 0
# Query_time: 256.991099  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 66829634  Rows_affected: 0  Bytes_sent: 0
# Stored_routine: test.update_blank_holiday
use test;
SET timestamp=1701179658;
CALL update_blank_holiday_record();

现有触发器代码（staff_shift表） #

以下是HOL初始化触发器的部分代码，希望在此处实现后续节假日的校验与正确键赋值：

ELSEIF NEW.`shift_day_template_record_key` LIKE "HOL%" THEN
        SET NEW.`shift_day_template_calendar_label` = "Holiday";
        SET NEW.`shift_type` = 'Leave';
        SET NEW.`s1_in` = NULL;
        SET NEW.`s1_out` = NULL;
        SET NEW.`s2_in` = NULL;
        SET NEW.`s2_out` = NULL;
        SET NEW.`shift_total_hours` = 0;
        SET NEW.`shift_day_template_hex_color_code` = (SELECT holiday_hex_color_code FROM setting WHERE (property_record_key = NEW.`property_record_key` AND record_status = 'Active'));
        SET NEW.`shift_day_template_on_in_lieu` = "No";
        INSERT INTO `staff_shift_holiday` (`staff_shift_record_key`, `staff_record_key`, `shift_date`, `shift_day_template_record_key`) VALUES (NEW.`record_key`, NEW.`staff_record_key`, NEW.`shift_day_template_record_key`) AS NEW
        ON DUPLICATE KEY UPDATE `staff_shift_record_key` = NEW.`record_key`, `shift_day_template_record_key` = NEW.`shift_day_template_record_key`;
    END IF;

节假日模板表数据示例 #

已尝试方案 #

• 调整事件执行频率为夜间执行
• 过滤过程仅处理部分记录
  但因客户要求节假日需实时显示，必须保持较高执行频率。

优化建议 #

1. 存储过程性能优化

• 移除游标，改为批量处理：游标逐行处理是核心性能瓶颈，改用临时表批量存储待处理记录，减少循环开销
• 添加必要索引：
  • 给staff_shift_holiday创建联合索引：INDEX idx_staff_holiday (staff_record_key, shift_day_template_record_key, shift_date)
  • 给pending_update_staff_balance创建索引：INDEX idx_pending_staff_date (staff_record_key, shift_date)
• 移除冗余查询：原逻辑中temp_update_type = 0时的SELECT完全冗余，游标已获取temp_shift_date，无需重复查询
• 合并事务：将循环内的独立事务移到循环外，整个批量操作使用单个事务（需确认业务逻辑允许），减少事务提交的资源消耗

修改后的存储过程示例：

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_blank_holiday_record`()
BEGIN
    START TRANSACTION;
    
    -- 处理staff_shift_holiday中的HOL记录
    CREATE TEMPORARY TABLE temp_holiday_records
    SELECT DISTINCT staff_record_key, shift_date
    FROM staff_shift_holiday
    WHERE shift_day_template_record_key = 'HOL';
    
    WHILE EXISTS(SELECT 1 FROM temp_holiday_records) DO
        SELECT staff_record_key, shift_date INTO @staff_key, @shift_date FROM temp_holiday_records LIMIT 1;
        CALL UPDATE_HOLIDAY_RECORD(@staff_key, @shift_date);
        CALL UPDATE_STAFF_BALANCE(@staff_key);
        DELETE FROM temp_holiday_records WHERE staff_record_key = @staff_key AND shift_date = @shift_date;
    END WHILE;
    
    -- 处理pending_update_staff_balance中的记录
    CREATE TEMPORARY TABLE temp_pending_records
    SELECT DISTINCT staff_record_key
    FROM pending_update_staff_balance
    WHERE shift_date IS NOT NULL;
    
    WHILE EXISTS(SELECT 1 FROM temp_pending_records) DO
        SELECT staff_record_key INTO @staff_key FROM temp_pending_records LIMIT 1;
        CALL UPDATE_STAFF_BALANCE(@staff_key);
        DELETE FROM pending_update_staff_balance WHERE staff_record_key = @staff_key;
        DELETE FROM temp_pending_records WHERE staff_record_key = @staff_key;
    END WHILE;
    
    DROP TEMPORARY TABLE IF EXISTS temp_holiday_records;
    DROP TEMPORARY TABLE IF EXISTS temp_pending_records;
    
    COMMIT;
END

2. 触发器中实现自动赋值正确节假日键

在触发器中直接根据shift_date匹配对应的具体节假日记录键，避免后续批量处理：

ELSEIF NEW.`shift_day_template_record_key` = 'HOL' THEN
    -- 根据日期匹配具体节假日记录键（假设节假日模板表名为shift_day_template，含holiday_date字段）
    SET NEW.`shift_day_template_record_key` = (
        SELECT shift_day_template_record_key
        FROM shift_day_template
        WHERE shift_day_template_record_key LIKE 'HOL-%'
          AND holiday_date = NEW.`shift_date`
        LIMIT 1
    );
    -- 未匹配到则保留HOL
    IF NEW.`shift_day_template_record_key` IS NULL THEN
        SET NEW.`shift_day_template_record_key` = 'HOL';
    END IF;
    
    -- 原有逻辑不变
    SET NEW.`shift_day_template_calendar_label` = "Holiday";
    SET NEW.`shift_type` = 'Leave';
    SET NEW.`s1_in` = NULL;
    SET NEW.`s1_out` = NULL;
    SET NEW.`s2_in` = NULL;
    SET NEW.`s2_out` = NULL;
    SET NEW.`shift_total_hours` = 0;
    SET NEW.`shift_day_template_hex_color_code` = (SELECT holiday_hex_color_code FROM setting WHERE (property_record_key = NEW.`property_record_key` AND record_status = 'Active'));
    SET NEW.`shift_day_template_on_in_lieu` = "No";
    INSERT INTO `staff_shift_holiday` (`staff_shift_record_key`, `staff_record_key`, `shift_date`, `shift_day_template_record_key`) VALUES (NEW.`record_key`, NEW.`staff_record_key`, NEW.`shift_date`, NEW.`shift_day_template_record_key`) AS NEW
    ON DUPLICATE KEY UPDATE `staff_shift_record_key` = NEW.`record_key`, `shift_day_template_record_key` = NEW.`shift_day_template_record_key`;
END IF;

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