本文目录导读：
我来为您设计一个完整的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;
}
这个方案提供了完整的大表数据清理解决方案,包括分批处理、断点恢复、监控统计、任务锁等功能，确保数据清理任务安全、高效地运行。
