时间:2025-04-30 09:25
人气:
作者:admin
以下是一个基于 Entity Framework Extensions 的 BulkDelete 高效删除海量数据的方案,包含性能优化、稳定性保障和错误处理机制:
csharp
using System;
using System.Linq;
using Z.EntityFramework.Plus;
public class BulkDeleteService
{
private const int BatchSize = 50000; // 根据服务器内存和网络调整
private const int MaxRetryAttempts = 3;
private readonly TimeSpan RetryDelay = TimeSpan.FromSeconds(5);
public void BulkDeleteRecords(MyDbContext context, DateTime deleteBefore)
{
var query = context.YourEntities
.Where(x => x.CreatedDate < deleteBefore)
.OrderBy(x => x.Id); // 按主键排序确保分页稳定
int totalDeleted = 0;
bool hasMoreData = true;
var deleteOptions = new BulkDeleteOptions
{
BatchSize = BatchSize,
UseTransaction = false, // 关闭事务提升性能
CommandTimeout = 600, // 适当延长超时时间
ExecuteHook = (x) =>
{
// 内存优化配置
x.BatchTimeout = 60;
x.TrackingEntities = false;
}
};
while (hasMoreData)
{
try
{
// 分页删除(使用子查询确保分页准确)
var subQuery = query.Take(BatchSize);
int deletedCount = RetryPolicy(() => subQuery.DeleteFromQuery(deleteOptions));
totalDeleted += deletedCount;
hasMoreData = deletedCount == BatchSize;
Console.WriteLine($"Deleted {totalDeleted:N0} records, LastID: {GetLastProcessedId(context)}");
// 定期释放资源
if (totalDeleted % (BatchSize * 10) == 0)
{
context.Dispose();
context = new MyDbContext();
query = context.YourEntities.Where(...); // 重建查询
}
}
catch (Exception ex)
{
LogError(ex, totalDeleted);
throw; // 根据业务需求决定是否终止
}
}
}
private int RetryPolicy(Func<int> action)
{
int attempts = 0;
while (true)
{
try
{
return action();
}
catch (SqlException ex) when (attempts < MaxRetryAttempts)
{
if (IsTransientError(ex.Number))
{
attempts++;
Thread.Sleep(RetryDelay);
continue;
}
throw;
}
}
}
private bool IsTransientError(int errorNumber)
{
// SQL Server 瞬态错误代码列表
int transientErrors = { 4060, 40197, 40501, 40613, 49918, 49919, 49920, 4221 };
return transientErrors.Contains(errorNumber);
}
private long GetLastProcessedId(MyDbContext context)
{
return context.YourEntities
.OrderByDescending(x => x.Id)
.Select(x => x.Id)
.FirstOrDefault();
}
private void LogError(Exception ex, int processedCount)
{
// 记录错误和当前进度到持久化存储
File.AppendAllText("delete_log.txt",
$"{DateTime.UtcNow:u} Error after {processedCount} records: {ex}\n");
}
}
关键优化点:
Take(BatchSize).DeleteFromQuery() 确保准确分页-- 执行后建议
UPDATE STATISTICS YourTable WITH FULLSCAN;
ALTER INDEX ALL ON YourTable REBUILD;
// 时间范围分段删除
var dateRanges = GenerateDateChunks(deleteBefore);
foreach (var range in dateRanges)
{
context.YourEntities
.Where(x => x.CreatedDate >= range.Start && x.CreatedDate < range.End)
.DeleteFromQuery();
}
注意事项:
对于超大规模数据(超过1亿条),建议结合数据库原生工具(如SQL Server的Partition Switching)实现秒级数据删除。
上一篇:管道