Spring Boot应用中MySQL/PostgreSQL数据TTL设置及批量删除优化咨询

针对大表批量删除卡顿的问题，结合MySQL和PostgreSQL的特性，以下是生产环境常用的标准解决思路：

一、分区表+分区删除（最优解） #

分区表是处理大表过期数据的首选方案，直接删除分区属于元数据操作，几乎瞬间完成，不会产生大量IO和锁表问题。

1. MySQL实现 #

MySQL 5.7+支持范围分区，按creation_time按月/按周划分分区：

-- 创建分区表（已有表需先转分区表）
CREATE TABLE your_table (
    id UUID PRIMARY KEY,
    creation_time TIMESTAMP NOT NULL,
    -- 其他业务字段
) 
PARTITION BY RANGE (TO_SECONDS(creation_time)) (
    -- 示例：创建最近3个月的分区，后续需定期新增
    PARTITION p_202403 VALUES LESS THAN (TO_SECONDS('2024-04-01 00:00:00')),
    PARTITION p_202404 VALUES LESS THAN (TO_SECONDS('2024-05-01 00:00:00')),
    PARTITION p_202405 VALUES LESS THAN (TO_SECONDS('2024-06-01 00:00:00'))
);

-- 手动删除3个月前的分区
ALTER TABLE your_table DROP PARTITION p_202403;

-- 提前创建下一个月的分区，避免写入失败
ALTER TABLE your_table ADD PARTITION p_202406 VALUES LESS THAN (TO_SECONDS('2024-07-01 00:00:00'));

用MySQL事件调度器自动管理分区：

CREATE EVENT manage_partitions
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-06-01 00:00:00'
DO
BEGIN
    -- 删除3个月前的分区
    SET @old_partition = CONCAT('p_', DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y%m'));
    SET @drop_sql = CONCAT('ALTER TABLE your_table DROP PARTITION ', @old_partition);
    PREPARE stmt FROM @drop_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 创建下一个月的分区
    SET @next_month = DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y%m');
    SET @next_month_start = CONCAT(@next_month, '-01 00:00:00');
    SET @next_partition = CONCAT('p_', @next_month);
    SET @add_sql = CONCAT('ALTER TABLE your_table ADD PARTITION ', @next_partition, ' VALUES LESS THAN (TO_SECONDS(\'', @next_month_start, '\'))');
    PREPARE stmt FROM @add_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

2. PostgreSQL实现 #

PostgreSQL 10+支持范围分区，配合pg_cron扩展实现自动分区管理：

-- 创建主分区表
CREATE TABLE your_table (
    id UUID PRIMARY KEY,
    creation_time TIMESTAMP NOT NULL,
    -- 其他业务字段
) PARTITION BY RANGE (creation_time);

-- 创建按月分区
CREATE TABLE your_table_202403 PARTITION OF your_table
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

CREATE TABLE your_table_202404 PARTITION OF your_table
FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- 安装pg_cron（需先在postgresql.conf配置shared_preload_libraries = 'pg_cron'）
CREATE EXTENSION pg_cron;

-- 定时清理3个月前的分区，每月1号凌晨执行
SELECT cron.schedule('clean-old-partitions', '0 0 1 * *', $$
DO $$
DECLARE
    rec RECORD;
BEGIN
    -- 删除过期分区
    FOR rec IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public' 
          AND tablename LIKE 'your_table_%'
          AND to_date(substring(tablename from 'your_table_(.*)'), 'YYYYMM') < current_date - interval '3 months'
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(rec.tablename) || ';';
    END LOOP;

    -- 提前创建下一个月的分区
    SET @next_month = to_char(current_date + interval '1 month', 'YYYYMM');
    SET @start_date = @next_month || '-01';
    SET @end_date = to_char(to_date(@start_date, 'YYYYMMDD') + interval '1 month', 'YYYYMMDD');
    SET @partition_name = 'your_table_' || @next_month;
    EXECUTE format('CREATE TABLE %I PARTITION OF your_table FOR VALUES FROM (%L) TO (%L);', @partition_name, @start_date, @end_date);
END $$;
$$);

二、批量删除优化（快速过渡方案） #

如果暂时无法改造为分区表，可将全量删除改为分批次执行，避免长时间锁表：

Spring Boot代码实现 #

@Scheduled(cron = "0 0 2 * * ?") // 凌晨2点低峰期执行
public void batchDeleteOldData() {
    final int BATCH_SIZE = 1000;
    final Duration PAUSE_DURATION = Duration.ofMillis(100);
    LocalDateTime cutoffTime = LocalDateTime.now().minusMonths(3);

    try {
        int deletedRows;
        do {
            // MySQL/PostgreSQL时间语法略有差异，按需调整
            deletedRows = jdbcTemplate.update(
                "DELETE FROM your_table WHERE creation_time < ? LIMIT ?",
                cutoffTime,
                BATCH_SIZE
            );

            if (deletedRows > 0) {
                Thread.sleep(PAUSE_DURATION.toMillis());
            }
        } while (deletedRows > 0);
    } catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        log.error("批量删除过期数据被中断", e);
    }
}

核心要点：

• 用LIMIT控制单次删除行数，缩短锁表时间
• 每次批量后短暂休眠，降低数据库资源占用
• 固定在业务低峰期执行任务

三、额外优化建议 #

• UUID主键优化：UUID作为主键易导致索引碎片化，建议使用带时间戳的UUID（如UUID v1），或创建(creation_time, id)复合索引，提升删除查询效率
• 监控告警：记录每次清理的行数、耗时，设置告警规则，避免任务失败导致数据堆积
• Kafka写入对齐：若使用分区表，Kafka生产者可按时间分区写入，让同时间段数据落到同一数据库分区，进一步提升清理效率

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