千万级大表如何删除数据？

-- 一个看似简单的删除操作
DELETE FROM user_operation_log 
WHERE create_time < '2023-01-01';

-- 实际上MySQL会这样处理：
-- 1. 获取表的写锁
-- 2. 逐行扫描10,000,000条记录
-- 3. 对每条匹配的记录：
--    - 写入undo log（用于回滚）
--    - 写入redo log（用于恢复）
--    - 更新所有相关索引
--    - 标记记录为删除状态
-- 4. 事务提交后才真正释放空间

-- 存储过程实现分批删除
DELIMITER $$
CREATE PROCEDURE batch_delete_by_id()
BEGIN
    DECLARE done INTDEFAULTFALSE;
    DECLARE batch_size INTDEFAULT1000;
    DECLARE max_id BIGINT;
    DECLARE min_id BIGINT;
    DECLARE current_id BIGINTDEFAULT0;
    
    -- 获取需要删除的数据范围
    SELECTMIN(id), MAX(id) INTO min_id, max_id 
    FROM user_operation_log 
    WHERE create_time < '2023-01-01';
    
    WHILE current_id < max_id DO
        -- 每次删除一个批次
        DELETEFROM user_operation_log 
        WHEREidBETWEEN current_id AND current_id + batch_size - 1
        AND create_time < '2023-01-01';
        
        -- 提交事务，释放锁
        COMMIT;
        
        -- 休眠一下，让数据库喘口气
        DOSLEEP(0.1);
        
        -- 更新进度
        SET current_id = current_id + batch_size;
        
        -- 记录日志（可选）
        INSERTINTO delete_progress_log 
        VALUES (NOW(), current_id, batch_size);
    ENDWHILE;
END$
DELIMITER ;

// Java代码实现基于时间的分批删除
@Service
@Slf4j
public class BatchDeleteService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 基于时间范围的分批删除
     */
    public void batchDeleteByTime(String tableName, String timeColumn, 
                                  Date startTime, Date endTime, 
                                  int batchDays) {
        
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startTime);
        
        int totalDeleted = 0;
        long startMs = System.currentTimeMillis();
        
        while (calendar.getTime().before(endTime)) {
            Date batchStart = calendar.getTime();
            calendar.add(Calendar.DAY_OF_YEAR, batchDays);
            Date batchEnd = calendar.getTime();
            
            // 确保不超过结束时间
            if (batchEnd.after(endTime)) {
                batchEnd = endTime;
            }
            
            String sql = String.format(
                "DELETE FROM %s WHERE %s BETWEEN ? AND ? LIMIT 1000",
                tableName, timeColumn
            );
            
            int deleted = jdbcTemplate.update(sql, batchStart, batchEnd);
            totalDeleted += deleted;
            
            log.info("批次删除完成: {}-{}, 删除{}条, 总计{}条",
                    batchStart, batchEnd, deleted, totalDeleted);
            
            // 控制删除频率，避免对数据库造成过大压力
            if (deleted > 0) {
                try {
                    Thread.sleep(500); // 休眠500ms
                } catch (InterruptedException e) {
                    Thread.currentThread().interrupt();
                    break;
                }
            } else {
                // 没有数据可删，跳到下一个时间段
                continue;
            }
            
            // 每删除10000条记录一次进度
            if (totalDeleted % 10000 == 0) {
                logProgress(totalDeleted, startMs);
            }
        }
        
        log.info("删除任务完成! 总计删除{}条记录, 耗时{}秒",
                totalDeleted, (System.currentTimeMillis() - startMs) / 1000);
    }
    
    private void logProgress(int totalDeleted, long startMs) {
        long costMs = System.currentTimeMillis() - startMs;
        double recordsPerSecond = totalDeleted * 1000.0 / costMs;
        
        log.info("删除进度: {}条, 速率: {}/秒, 耗时: {}秒",
                totalDeleted, String.format("%.2f", recordsPerSecond), costMs / 1000);
    }
}

-- 简单的LIMIT分批删除
DELIMITER $$
CREATE PROCEDURE batch_delete_with_limit()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE total_deleted INT DEFAULT 0;
    
    WHILE done = 0 DO
        -- 每次删除1000条
        DELETE FROM user_operation_log 
        WHERE create_time < '2023-01-01'
        LIMIT batch_size;
        
        -- 检查是否还有数据
        SET done = ROW_COUNT() = 0;
        SET total_deleted = total_deleted + ROW_COUNT();
        
        -- 提交释放锁
        COMMIT;
        
        -- 休眠控制频率
        DOSLEEP(0.1);
        
        -- 每删除10000条输出日志
        IF total_deleted % 10000 = 0 THEN
            SELECT CONCAT('已删除: ', total_deleted, ' 条记录') AS progress;
        END IF;
    END WHILE;
    
    SELECT CONCAT('删除完成! 总计: ', total_deleted, ' 条记录') ASresult;
END$
DELIMITER ;

@Service  
@Slf4j
public class TableRebuildService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 重建表方式删除数据
     */
    public void rebuildTableForDeletion(String sourceTable, String condition) {
        String newTable = sourceTable + "_new";
        String oldTable = sourceTable + "_old";
        
        try {
            // 1. 创建新表
            log.info("开始创建新表: {}", newTable);
            jdbcTemplate.execute("CREATE TABLE " + newTable + " LIKE " + sourceTable);
            
            // 2. 导入需要保留的数据
            log.info("开始导入保留数据");
            String insertSql = String.format(
                "INSERT INTO %s SELECT * FROM %s WHERE %s", 
                newTable, sourceTable, condition
            );
            int keptCount = jdbcTemplate.update(insertSql);
            log.info("成功导入{}条保留数据", keptCount);
            
            // 3. 创建索引（可选，在导入后创建索引效率更高）
            log.info("开始创建索引");
            createIndexes(newTable);
            
            // 4. 数据验证
            log.info("开始数据验证");
            if (!validateData(sourceTable, newTable, condition)) {
                throw new RuntimeException("数据验证失败");
            }
            
            // 5. 原子切换
            log.info("开始表切换");
            switchTables(sourceTable, newTable, oldTable);
            
            // 6. 删除旧表（可选立即或延后）
            log.info("开始删除旧表");
            dropTableSafely(oldTable);
            
            log.info("表重建删除完成!");
            
        } catch (Exception e) {
            log.error("表重建过程发生异常", e);
            // 清理临时表
            cleanupTempTable(newTable);
            throw e;
        }
    }
    
    private void createIndexes(String tableName) {
        // 根据业务需要创建索引
        String[] indexes = {
            "CREATE INDEX idx_create_time ON " + tableName + "(create_time)",
            "CREATE INDEX idx_user_id ON " + tableName + "(user_id)"
        };
        
        for (String sql : indexes) {
            jdbcTemplate.execute(sql);
        }
    }
    
    private boolean validateData(String sourceTable, String newTable, String condition) {
        // 验证新表数据量是否正确
        Integer newCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM " + newTable, Integer.class);
        
        Integer expectedCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM " + sourceTable + " WHERE " + condition, Integer.class);
        
        return newCount.equals(expectedCount);
    }
    
    private void switchTables(String sourceTable, String newTable, String oldTable) {
        // 原子性的表重命名操作
        String sql = String.format(
            "RENAME TABLE %s TO %s, %s TO %s", 
            sourceTable, oldTable, newTable, sourceTable
        );
        jdbcTemplate.execute(sql);
    }
    
    private void dropTableSafely(String tableName) {
        try {
            jdbcTemplate.execute("DROP TABLE " + tableName);
        } catch (Exception e) {
            log.warn("删除表失败: {}, 需要手动清理", tableName, e);
        }
    }
    
    private void cleanupTempTable(String tableName) {
        try {
            jdbcTemplate.execute("DROP TABLE IF EXISTS " + tableName);
        } catch (Exception e) {
            log.warn("清理临时表失败: {}", tableName, e);
        }
    }
}

-- 查看表的分区情况
SELECT table_name, partition_name, table_rows
FROM information_schema.partitions 
WHERE table_name = 'user_operation_log';

-- 直接删除整个分区（秒级完成）
ALTER TABLE user_operation_log DROPPARTITION p202201, p202202;

-- 定期删除过期分区的存储过程
DELIMITER $
CREATE PROCEDURE auto_drop_expired_partitions()
BEGIN
    DECLARE expired_partition VARCHAR(64);
    DECLARE done INT DEFAULT FALSE;
    
    -- 查找需要删除的分区（保留最近12个月）
    DECLARE cur CURSOR FOR
    SELECT partition_name 
    FROM information_schema.partitions 
    WHERE table_name = 'user_operation_log'
    AND partition_name LIKE'p%'
    AND STR_TO_DATE(REPLACE(partition_name, 'p', ''), '%Y%m') < DATE_SUB(NOW(), INTERVAL12MONTH);
    
    DECLARE CONTINUE HANDLER FOR NOT FOUNDS ET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO expired_partition;
        IF done THEN
            LEAVE read_loop;
        ENDIF;
        
        -- 删除过期分区
        SET @sql = CONCAT('ALTER TABLE user_operation_log DROP PARTITION ', expired_partition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 记录日志
        INSERT INTO partition_clean_log 
        VALUES (NOW(), expired_partition, 'DROPPED');
    END LOOP;
    
    CLOSE cur;
END$
DELIMITER ;

-- 将普通表改造成分区表
-- 步骤1: 创建分区表
CREATE TABLE user_operation_log_partitioned (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT,
    operation VARCHAR(100),
    create_time DATETIME,
    PRIMARY KEY (id, create_time)  -- 分区键必须包含在主键中
) PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time)) (
    PARTITION p202201 VALUES LESS THAN (202202),
    PARTITION p202202 VALUES LESS THAN (202203),
    PARTITION p202203 VALUES LESS THAN (202204),
    PARTITION p202204 VALUES LESS THAN (202205),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 步骤2: 导入数据
INSERTINTO user_operation_log_partitioned 
SELECT * FROM user_operation_log;

-- 步骤3: 切换表
RENAME TABLE
    user_operation_log TO user_operation_log_old,
    user_operation_log_partitioned TO user_operation_log;

-- 步骤4: 定期维护：添加新分区
ALTER TABLE user_operation_log REORGANIZE PARTITION pfuture INTO (
    PARTITION p202205 VALUESLESSTHAN (202206),
    PARTITION p202206 VALUESLESSTHAN (202207),
    PARTITION pfuture VALUESLESSTHAN MAXVALUE
);

@Service
@Slf4j
public class PartitionManagerService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 自动管理分区
     */
    @Scheduled(cron = "0 0 2 * * ?")  // 每天凌晨2点执行
    public void autoManagePartitions() {
        log.info("开始分区维护任务");
        
        try {
            // 1. 删除过期分区（保留最近12个月）
            dropExpiredPartitions();
            
            // 2. 创建未来分区
            createFuturePartitions();
            
            log.info("分区维护任务完成");
            
        } catch (Exception e) {
            log.error("分区维护任务失败", e);
        }
    }
    
    private void dropExpiredPartitions() {
        String sql = "SELECT partition_name " +
                    "FROM information_schema.partitions " +
                    "WHERE table_name = 'user_operation_log' " +
                    "AND partition_name LIKE 'p%' " +
                    "AND STR_TO_DATE(REPLACE(partition_name, 'p', ''), '%Y%m') < DATE_SUB(NOW(), INTERVAL 12 MONTH)";
        
        List<String> expiredPartitions = jdbcTemplate.queryForList(sql, String.class);
        
        for (String partition : expiredPartitions) {
            try {
                jdbcTemplate.execute("ALTER TABLE user_operation_log DROP PARTITION " + partition);
                log.info("成功删除分区: {}", partition);
                
                // 记录操作日志
                logPartitionOperation("DROP", partition, "SUCCESS");
                
            } catch (Exception e) {
                log.error("删除分区失败: {}", partition, e);
                logPartitionOperation("DROP", partition, "FAILED: " + e.getMessage());
            }
        }
    }
    
    private void createFuturePartitions() {
        // 创建未来3个月的分区
        for (int i = 1; i <= 3; i++) {
            LocalDate futureDate = LocalDate.now().plusMonths(i);
            String partitionName = "p" + futureDate.format(DateTimeFormatter.ofPattern("yyyyMM"));
            int partitionValue = futureDate.getYear() * 100 + futureDate.getMonthValue();
            int nextPartitionValue = partitionValue + 1;
            
            try {
                String sql = String.format(
                    "ALTER TABLE user_operation_log REORGANIZE PARTITION pfuture INTO (" +
                    "PARTITION %s VALUES LESS THAN (%d), " +
                    "PARTITION pfuture VALUES LESS THAN MAXVALUE)",
                    partitionName, nextPartitionValue
                );
                
                jdbcTemplate.execute(sql);
                log.info("成功创建分区: {}", partitionName);
                logPartitionOperation("CREATE", partitionName, "SUCCESS");
                
            } catch (Exception e) {
                log.warn("创建分区失败（可能已存在）: {}", partitionName, e);
            }
        }
    }
    
    private void logPartitionOperation(String operation, String partition, String status) {
        jdbcTemplate.update(
            "INSERT INTO partition_operation_log(operation, partition_name, status, create_time) VALUES (?, ?, ?, NOW())",
            operation, partition, status
        );
    }
}

# 安装Percona Toolkit
# Ubuntu/Debian: 
sudo apt-get install percona-toolkit

# 使用pt-archiver归档删除数据
pt-archiver \
    --source h=localhost,D=test,t=user_operation_log \
    --where"create_time < '2023-01-01'" \
    --limit 1000 \
    --commit-each \
    --sleep 0.1 \
    --statistics \
    --progress 10000 \
    --why-not \
    --dry-run  # 先试运行，确认无误后移除此参数

# 实际执行删除
pt-archiver \
    --source h=localhost,D=test,t=user_operation_log \
    --where"create_time < '2023-01-01'" \
    --limit 1000 \
    --commit-each \
    --sleep 0.1 \
    --purge

@Component
@Slf4j
public class SmartDeleteTool {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 智能删除决策
     */
    public void smartDelete(String tableName, String condition) {
        try {
            // 1. 分析表状态
            TableAnalysisResult analysis = analyzeTable(tableName, condition);
            
            // 2. 根据分析结果选择最佳方案
            DeleteStrategy strategy = chooseBestStrategy(analysis);
            
            // 3. 执行删除
            executeDelete(strategy, tableName, condition);
            
        } catch (Exception e) {
            log.error("智能删除失败", e);
            throw e;
        }
    }
    
    private TableAnalysisResult analyzeTable(String tableName, String condition) {
        TableAnalysisResult result = new TableAnalysisResult();
        
        // 分析表大小
        result.setTotalRows(getTableRowCount(tableName));
        result.setDeleteRows(getDeleteRowCount(tableName, condition));
        result.setDeleteRatio(result.getDeleteRows() * 1.0 / result.getTotalRows());
        
        // 分析表结构
        result.setHasPartition(isTablePartitioned(tableName));
        result.setHasPrimaryKey(hasPrimaryKey(tableName));
        result.setIndexCount(getIndexCount(tableName));
        
        // 分析系统负载
        result.setSystemLoad(getSystemLoad());
        
        return result;
    }
    
    private DeleteStrategy chooseBestStrategy(TableAnalysisResult analysis) {
        if (analysis.isHasPartition() && analysis.getDeleteRatio() > 0.3) {
            return DeleteStrategy.PARTITION_DROP;
        }
        
        if (analysis.getDeleteRatio() > 0.5) {
            return DeleteStrategy.TABLE_REBUILD;
        }
        
        if (analysis.getTotalRows() > 10_000_000) {
            return DeleteStrategy.BATCH_DELETE_WITH_PAUSE;
        }
        
        return DeleteStrategy.BATCH_DELETE;
    }
    
    private void executeDelete(DeleteStrategy strategy, String tableName, String condition) {
        switch (strategy) {
            case PARTITION_DROP:
                executePartitionDrop(tableName, condition);
                break;
            case TABLE_REBUILD:
                executeTableRebuild(tableName, condition);
                break;
            case BATCH_DELETE_WITH_PAUSE:
                executeBatchDeleteWithPause(tableName, condition);
                break;
            default:
                executeBatchDelete(tableName, condition);
        }
    }
    
    // 各种策略的具体实现...
    
    private long getTableRowCount(String tableName) {
        String sql = "SELECT COUNT(*) FROM " + tableName;
        return jdbcTemplate.queryForObject(sql, Long.class);
    }
    
    private long getDeleteRowCount(String tableName, String condition) {
        String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + condition;
        return jdbcTemplate.queryForObject(sql, Long.class);
    }
    
    private boolean isTablePartitioned(String tableName) {
        String sql = "SELECT COUNT(*) FROM information_schema.partitions " +
                    "WHERE table_name = ? AND partition_name IS NOT NULL";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName);
        return count != null && count > 0;
    }
    
    // 其他分析方法...
}

enum DeleteStrategy {
    BATCH_DELETE,           // 普通分批删除
    BATCH_DELETE_WITH_PAUSE, // 带休眠的分批删除
    TABLE_REBUILD,          // 重建表
    PARTITION_DROP,         // 删除分区
    ONLINE_MIGRATION        // 在线迁移
}

@Data
class TableAnalysisResult {
    private long totalRows;
    private long deleteRows;
    private double deleteRatio;
    private boolean hasPartition;
    private boolean hasPrimaryKey;
    private int indexCount;
    private double systemLoad;
}