优化无索引Spring Batch元数据表清理SQL以解决表锁问题

问题背景 #

Spring Batch的元数据表不会自动清理，手动清理BATCH_JOB_EXECUTION_PARAMS表时出现表锁，要求清理过程中不影响其他批处理任务正常执行。该表结构如下：

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
    JOB_EXECUTION_ID BIGINT NOT NULL ,
    PARAMETER_NAME VARCHAR(100) NOT NULL ,
    PARAMETER_TYPE VARCHAR(100) NOT NULL ,
    PARAMETER_VALUE VARCHAR(2500) ,
    IDENTIFYING CHAR(1) NOT NULL ,
    constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
    references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

当前执行的清理SQL：

SELECT MAX(JOB_EXECUTION_ID) 
    FROM BATCH_JOB_EXECUTION 
    WHERE CREATE_TIME < <ANYTIME>;

DELETE FROM BATCH_JOB_EXECUTION_PARAMS 
    WHERE JOB_EXECUTION_ID <= <MAX_JOB_EXECUTION_ID>;

执行计划显示该DELETE操作走全表扫描（type=ALL），未使用索引，导致InnoDB锁定整张表：

优化方案 #

1. 为JOB_EXECUTION_ID添加索引 #

虽然表中存在外键约束JOB_EXEC_PARAMS_FK，但执行计划显示未使用该索引（可能是统计信息过时或索引未正确生效）。手动创建独立索引确保DELETE操作走索引扫描，仅锁定符合条件的行：

CREATE INDEX IDX_JOB_EXEC_PARAMS_JOB_ID ON BATCH_JOB_EXECUTION_PARAMS(JOB_EXECUTION_ID);

创建完成后重新执行DELETE，执行计划的type应变为range，避免全表扫描带来的表锁。

2. 分批删除数据 #

即使有索引，一次性删除大量数据仍会导致长时间锁表。采用分批删除策略，每次删除少量数据，减少锁持有时间：

-- 先获取待清理的最大JOB_EXECUTION_ID
SET @max_job_id = (SELECT MAX(JOB_EXECUTION_ID) FROM BATCH_JOB_EXECUTION WHERE CREATE_TIME < '<ANYTIME>');

-- 循环分批删除，每次删1000条（可根据实际数据量调整）
REPEAT
    DELETE FROM BATCH_JOB_EXECUTION_PARAMS
    WHERE JOB_EXECUTION_ID <= @max_job_id
    LIMIT 1000;
UNTIL ROW_COUNT() = 0 END REPEAT;

3. 临时调整事务隔离级别（可选） #

若当前使用REPEATABLE READ隔离级别，可临时切换为READ COMMITTED，缩小锁范围并减少锁持有时间：

-- 临时设置会话级隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 执行清理操作
-- ...

-- 恢复原隔离级别（假设原级别为REPEATABLE READ）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. 确认外键关联的清理顺序 #

建议先清理BATCH_JOB_EXECUTION_PARAMS子表，再清理BATCH_JOB_EXECUTION父表，避免父表记录被锁定影响子表删除操作。

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