PHP 大表数据清理定时任务

wen PHP项目 2026-08-11 159

本文目录导读：

PHP 大表数据清理定时任务

基础架构设计
配置文件 config.php
主入口脚本 cleanup_main.php
清理进度记录脚本 show_progress.php
Cron 配置示例
中断恢复机制
最佳实践建议

我来为您设计一个完整的PHP大表数据清理定时任务方案。

基础架构设计

主清理脚本 `cleanup.php`

<?php
/**
 * 大表数据清理定时任务
 * 支持分片删除、进度记录、异常恢复
 */
class LargeTableCleaner {
    private $pdo;
    private $config;
    private $logFile;
    public function __construct($config) {
        $this->config = $config;
        $this->logFile = $config['log_file'] ?? '/var/log/cleanup.log';
        $this->initDatabase();
    }
    private function initDatabase() {
        try {
            $this->pdo = new PDO(
                sprintf(
                    "mysql:host=%s;dbname=%s;charset=utf8mb4",
                    $this->config['db']['host'],
                    $this->config['db']['database']
                ),
                $this->config['db']['username'],
                $this->config['db']['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_TIMEOUT => 60
                ]
            );
        } catch (PDOException $e) {
            $this->log("Database connection failed: " . $e->getMessage(), 'ERROR');
            exit(1);
        }
    }
    public function run() {
        $startTime = microtime(true);
        $this->log("开始清理任务");
        try {
            foreach ($this->config['tables'] as $tableConfig) {
                $this->cleanTable($tableConfig);
            }
            $execTime = round(microtime(true) - $startTime, 2);
            $this->log("清理任务完成，耗时: {$execTime}秒");
        } catch (Exception $e) {
            $this->log("清理失败: " . $e->getMessage(), 'ERROR');
            $this->notifyAdmin($e->getMessage());
        }
    }
    private function cleanTable($tableConfig) {
        $tableName = $tableConfig['name'];
        $condition = $tableConfig['condition'];
        $batchSize = $tableConfig['batch_size'] ?? 5000;
        $maxTotal = $tableConfig['max_total'] ?? PHP_INT_MAX;
        $this->log("开始清理表: {$tableName}");
        $deletedTotal = 0;
        $continue = true;
        while ($continue && $deletedTotal < $maxTotal) {
            // 计算当前批次实际删除数量
            $currentBatchSize = min($batchSize, $maxTotal - $deletedTotal);
            // 执行删除
            $deleted = $this->deleteBatch($tableName, $condition, $currentBatchSize);
            $deletedTotal += $deleted;
            $this->log("表 {$tableName} 已删除: {$deletedTotal} 条记录");
            // 检查是否需要继续
            if ($deleted < $batchSize) {
                $continue = false;
            }
            // 检查超时
            if ($this->isTimeout()) {
                $this->log("达到时间限制，暂停清理");
                break;
            }
            // 暂停，减轻数据库压力
            usleep(500000); // 0.5秒
        }
        $this->log("表 {$tableName} 清理完成，共删除: {$deletedTotal} 条记录");
    }
    private function deleteBatch($tableName, $condition, $limit) {
        try {
            // 使用主键批量删除，效率更高
            $sql = sprintf(
                "DELETE FROM `%s` WHERE id IN (
                    SELECT id FROM (
                        SELECT id FROM `%s` WHERE %s LIMIT %d
                    ) tmp
                )",
                $tableName,
                $tableName,
                $condition,
                $limit
            );
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            return $stmt->rowCount();
        } catch (PDOException $e) {
            // 如果IN子查询方式失败，回退到简单删除
            try {
                $sql = sprintf(
                    "DELETE FROM `%s` WHERE %s LIMIT %d",
                    $tableName,
                    $condition,
                    $limit
                );
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute();
                return $stmt->rowCount();
            } catch (PDOException $e2) {
                $this->log("删除失败: " . $e2->getMessage(), 'ERROR');
                return 0;
            }
        }
    }
    private function isTimeout() {
        if ($this->config['max_execution_time'] === null) {
            return false;
        }
        static $startTime = null;
        if ($startTime === null) {
            $startTime = time();
        }
        return (time() - $startTime) >= $this->config['max_execution_time'];
    }
    private function log($message, $level = 'INFO') {
        $logMessage = sprintf(
            "[%s] [%s] %s\n",
            date('Y-m-d H:i:s'),
            $level,
            $message
        );
        file_put_contents($this->logFile, $logMessage, FILE_APPEND | LOCK_EX);
        echo $logMessage;
    }
    private function notifyAdmin($message) {
        // 发送邮件或通知管理员
        if (!empty($this->config['admin_email'])) {
            mail(
                $this->config['admin_email'],
                '数据清理失败警报',
                "失败时间: " . date('Y-m-d H:i:s') . "\n失败信息: " . $message
            );
        }
    }
}

配置文件 `config.php`

<?php
return [
    'db' => [
        'host' => 'localhost',
        'database' => 'your_database',
        'username' => 'your_username',
        'password' => 'your_password'
    ],
    'log_file' => '/var/log/cleanup.log',
    'admin_email' => 'admin@example.com',
    'max_execution_time' => 1800, // 30分钟
    'lock_file' => '/tmp/cleanup.lock',
    'tables' => [
        // 示例1：日志表
        [
            'name' => 'operation_logs',
            'condition' => "created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)",
            'batch_size' => 5000,
            'max_total' => 1000000
        ],
        // 示例2：临时数据表
        [
            'name' => 'temp_data',
            'condition' => "expire_time < NOW()",
            'batch_size' => 10000,
            'max_total' => PHP_INT_MAX
        ],
        // 示例3：旧订单表
        [
            'name' => 'old_orders',
            'condition' => "status = 'cancelled' AND created_at < DATE_SUB(NOW(), INTERVAL 365 DAY)",
            'batch_size' => 3000,
            'max_total' => 500000
        ]
    ]
];

主入口脚本 `cleanup_main.php`

<?php
/**
 * 清理任务主入口
 * 支持多种运行模式
 */
require_once 'config.php';
require_once 'cleanup.php';
class CleanerManager {
    private $config;
    private $lockFile;
    public function __construct($config) {
        $this->config = $config;
        $this->lockFile = $config['lock_file'] ?? '/tmp/cleanup.lock';
    }
    public function execute() {
        // 防止重复运行
        if (!$this->acquireLock()) {
            echo date('Y-m-d H:i:s') . " 已有清理任务在运行\n";
            exit(1);
        }
        try {
            // 创建清理器实例
            $cleaner = new LargeTableCleaner($this->config);
            $cleaner->run();
        } finally {
            $this->releaseLock();
        }
    }
    private function acquireLock() {
        // 使用 flock 实现进程锁
        $fp = fopen($this->lockFile, 'w');
        if ($fp === false) {
            return false;
        }
        if (!flock($fp, LOCK_EX | LOCK_NB)) {
            fclose($fp);
            return false;
        }
        // 存储文件指针
        $this->lockFp = $fp;
        return true;
    }
    private function releaseLock() {
        if (isset($this->lockFp) && is_resource($this->lockFp)) {
            flock($this->lockFp, LOCK_UN);
            fclose($this->lockFp);
        }
    }
}
// 执行清理
$manager = new CleanerManager($config);
$manager->execute();

清理进度记录脚本 `show_progress.php`

<?php
/**
 * 查看清理进度
 */
class ProgressViewer {
    private $pdo;
    private $config;
    public function __construct($config) {
        $this->config = $config;
        $this->initDatabase();
    }
    private function initDatabase() {
        $this->pdo = new PDO(
            sprintf(
                "mysql:host=%s;dbname=%s;charset=utf8mb4",
                $this->config['db']['host'],
                $this->config['db']['database']
            ),
            $this->config['db']['username'],
            $this->config['db']['password']
        );
    }
    public function showTableStats() {
        echo "=== 表数据清理统计 ===\n\n";
        foreach ($this->config['tables'] as $tableConfig) {
            $tableName = $tableConfig['name'];
            // 获取当前表的数据量
            $sql = "SELECT COUNT(*) as total FROM `{$tableName}`";
            $stmt = $this->pdo->query($sql);
            $total = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
            // 获取满足清理条件的数据量
            $condition = $tableConfig['condition'];
            $sql = "SELECT COUNT(*) as count FROM `{$tableName}` WHERE {$condition}";
            $stmt = $this->pdo->query($sql);
            $deletable = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
            printf(
                "表: %-20s 总量: %-15d 可清理: %-15d 清理比例: %.2f%%\n",
                $tableName,
                $total,
                $deletable,
                ($deletable / max($total, 1)) * 100
            );
        }
    }
}
$config = require 'config.php';
$viewer = new ProgressViewer($config);
$viewer->showTableStats();

Cron 配置示例

# crontab 配置
# 每天凌晨2点执行，清理超过90天的数据
0 2 * * * /usr/bin/php /path/to/cleanup_main.php >> /var/log/cleanup_cron.log 2>&1
# 也可以使用 systemd timer 方式
# 创建 /etc/systemd/system/db-cleanup.service
[Unit]
Description=Database Cleanup Service
[Service]
Type=oneshot
ExecStart=/usr/bin/php /path/to/cleanup_main.php
User=www-data
# 创建 /etc/systemd/system/db-cleanup.timer
[Unit]
Description=Database Cleanup Timer
[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
[Install]
WantedBy=timers.target

中断恢复机制

<?php
/**
 * 断点续传处理
 */
class ResumeCleaner {
    private $pdo;
    private $progressTable = 'cleanup_progress';
    public function saveProgress($tableName, $lastId, $deletedCount) {
        $sql = "INSERT INTO {$this->progressTable} 
                (table_name, last_id, deleted_count, update_time)
                VALUES (?, ?, ?, NOW())
                ON DUPLICATE KEY UPDATE
                last_id = VALUES(last_id),
                deleted_count = deleted_count + VALUES(deleted_count),
                update_time = NOW()";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$tableName, $lastId, $deletedCount]);
    }
    public function getProgress($tableName) {
        $sql = "SELECT * FROM {$this->progressTable} WHERE table_name = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$tableName]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    public function getLastDeleteId($tableName) {
        $progress = $this->getProgress($tableName);
        return $progress['last_id'] ?? 0;
    }
}

最佳实践建议

1 性能优化

分批删除：避免大事务，每批5000-10000条
索引优化：确保清理条件字段有索引
低峰期执行：设置在工作负载较低的时段
连接池：使用持久连接减少连接开销

2 监控指标

// 记录性能指标
$this->log("批次删除: {$deleted} 条, 耗时: {$time}ms, 平均速度: " . 
           round($deleted / $time, 2) . "条/ms");

3 故障处理

// 异常重试机制
private function retryDelete($tableName, $condition, $limit, $retries = 3) {
    for ($i = 0; $i < $retries; $i++) {
        try {
            return $this->deleteBatch($tableName, $condition, $limit);
        } catch (Exception $e) {
            $this->log("删除异常(第{$i+1}次尝试): " . $e->getMessage(), 'WARNING');
            sleep(2);
        }
    }
    return 0;
}

这个方案提供了完整的大表数据清理解决方案,包括分批处理、断点恢复、监控统计、任务锁等功能，确保数据清理任务安全、高效地运行。

本文地址： https://bmjup.com/post/43222.html