本文目录导读：
在PHP项目中批量清理数据时防止锁表,可以采取以下几个关键策略：
分批处理（最重要）
function batchCleanData($table, $batchSize = 1000) {
$processed = 0;
while (true) {
// 每次只处理一小批
$sql = "DELETE FROM {$table}
WHERE create_time < '2024-01-01'
LIMIT {$batchSize}";
$affected = $db->exec($sql);
if ($affected == 0) {
break;
}
$processed += $affected;
// 分批之间暂停一下
usleep(100000); // 0.1秒
}
return $processed;
}
使用主键范围分批
function batchByIdRange($table, $startId, $endId, $batchSize = 1000) {
$currentId = $startId;
while ($currentId <= $endId) {
$batchEndId = min($currentId + $batchSize - 1, $endId);
$sql = "DELETE FROM {$table}
WHERE id BETWEEN {$currentId} AND {$batchEndId}";
$db->exec($sql);
$currentId = $batchEndId + 1;
// 可选：记录进度
// logProcess("Processed IDs: {$currentId} - {$batchEndId}");
}
}
使用索引优化（减少锁范围）
// 先获取需要删除的ID列表
$sql = "SELECT id FROM large_table
WHERE status = 'expired'
LIMIT 10000"; // 获取ID列表
$ids = $db->fetchAll($sql);
// 分批删除
$chunks = array_chunk($ids, 1000);
foreach ($chunks as $chunk) {
$idsStr = implode(',', $chunk);
// 使用主键删除，避免全表扫描和锁表
$sql = "DELETE FROM large_table
WHERE id IN ({$idsStr})";
$db->exec($sql);
usleep(50000);
}
使用软删除加物理删除
class DataCleaner {
// 第一步：标记为删除（不影响业务）
public function softDelete($table, $condition) {
$sql = "UPDATE {$table}
SET deleted_at = NOW(),
is_deleted = 1
WHERE {$condition}
LIMIT 5000";
return $db->exec($sql);
}
// 第二步：在低峰期物理删除
public function hardDelete($table) {
$sql = "DELETE FROM {$table}
WHERE deleted_at < DATE_SUB(NOW(), INTERVAL 1 DAY)
LIMIT 1000";
return $db->exec($sql);
}
}
使用临时表策略
// 1. 创建临时表
$db->exec("CREATE TEMPORARY TABLE tmp_delete_ids (
id INT PRIMARY KEY
) ENGINE=MEMORY");
// 2. 批量插入需要删除的ID
$db->exec("INSERT INTO tmp_delete_ids (id)
SELECT id FROM source_table
WHERE condition
LIMIT 10000");
// 3. 通过JOIN删除
$sql = "DELETE t FROM source_table t
INNER JOIN tmp_delete_ids tmp ON t.id = tmp.id";
$db->exec($sql);
设置锁超时和事务隔离级别
// 设置锁等待超时（避免长时间阻塞）
$db->exec("SET innodb_lock_wait_timeout = 5");
// 使用READ COMMITTED隔离级别（减少间隙锁）
$db->exec("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
// 开启事务
$db->beginTransaction();
try {
// 执行分批删除
batchCleanData($table);
$db->commit();
} catch (Exception $e) {
$db->rollBack();
throw $e;
}
完整的清理函数示例
function safeCleanData($table, $conditions, $options = []) {
$defaults = [
'batch_size' => 1000,
'sleep_ms' => 100,
'max_runtime' => 300, // 5分钟
'max_rows' => 1000000,
'chunk_by_id' => false,
'id_column' => 'id'
];
$config = array_merge($defaults, $options);
$startTime = time();
$totalDeleted = 0;
if ($config['chunk_by_id']) {
// 基于ID范围分批
$minId = $db->fetchOne("SELECT MIN({$config['id_column']}) FROM {$table} WHERE {$conditions}");
$maxId = $db->fetchOne("SELECT MAX({$config['id_column']}) FROM {$table} WHERE {$conditions}");
for ($id = $minId; $id <= $maxId; $id += $config['batch_size']) {
// 检查运行时间
if (time() - $startTime > $config['max_runtime']) {
break;
}
$endId = min($id + $config['batch_size'] - 1, $maxId);
$sql = "DELETE FROM {$table}
WHERE {$config['id_column']} BETWEEN {$id} AND {$endId}
AND {$conditions}";
$affected = $db->exec($sql);
$totalDeleted += $affected;
// 检查删除数量上限
if ($totalDeleted >= $config['max_rows']) {
break;
}
usleep($config['sleep_ms'] * 1000);
}
} else {
// 基于LIMIT分批
while ($totalDeleted < $config['max_rows']) {
if (time() - $startTime > $config['max_runtime']) {
break;
}
$sql = "DELETE FROM {$table}
WHERE {$conditions}
LIMIT {$config['batch_size']}";
$affected = $db->exec($sql);
if ($affected == 0) {
break;
}
$totalDeleted += $affected;
usleep($config['sleep_ms'] * 1000);
}
}
return $totalDeleted;
}
// 使用示例
$deleted = safeCleanData('user_logs', "create_time < '2024-01-01'", [
'batch_size' => 500,
'sleep_ms' => 200,
'max_runtime' => 120,
'max_rows' => 100000
]);
关键建议：
- 选择低峰期：在业务流量最低的时候执行
- 监控数据库：使用
SHOW PROCESSLIST监控锁等待情况 - 预留回滚方案：删除前备份数据
- 考虑使用pt-archiver：Percona Toolkit提供的专业工具
- 分批设置合理大小：一般500-2000条/批，根据表结构调整
- 添加索引：确保WHERE条件能使用索引，减少锁范围
