PHP项目批量清理数据如何防止锁表

本文目录导读：

1. 分批处理（最重要）
2. 使用主键范围分批
3. 使用索引优化（减少锁范围）
4. 使用软删除加物理删除
5. 使用临时表策略
6. 设置锁超时和事务隔离级别
7. 完整的清理函数示例
8. 关键建议：

在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
]);

关键建议：

1. 选择低峰期：在业务流量最低的时候执行
2. 监控数据库：使用SHOW PROCESSLIST监控锁等待情况
3. 预留回滚方案：删除前备份数据
4. 考虑使用pt-archiver：Percona Toolkit提供的专业工具
5. 分批设置合理大小：一般500-2000条/批，根据表结构调整
6. 添加索引：确保WHERE条件能使用索引，减少锁范围