SqlSugar高效批量删除

最新推荐文章于 2025-11-30 19:25:58 发布

原创最新推荐文章于 2025-11-30 19:25:58 发布 · 101 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库ORM 专栏收录该内容

7 篇文章

订阅专栏

在实际应用开发中，批量删除操作是常见的数据库操作需求。SqlSugar提供了多种高效的批量删除方案，本文将全面解析各种方法的性能特点、适用场景和最佳实践。

1. 基础批量删除方法

1.1 根据主键批量删除

// 方法1：使用主键列表直接删除
var ids = new[] { 1, 2, 3, 4, 5 };
var result = db.Deleteable<Order>().In(ids).ExecuteCommand();

// 方法2：使用实体列表删除（基于主键）
var orders = GetOrdersToDelete();
var result = db.Deleteable(orders).ExecuteCommand();

// 异步版本
var result = await db.Deleteable<Order>().In(ids).ExecuteCommandAsync();

1.2 根据条件批量删除

// 根据条件删除数据
var result = db.Deleteable<Order>()
    .Where(o => o.Status == OrderStatus.Deleted)
    .ExecuteCommand();

// 复杂条件删除
var result = db.Deleteable<Order>()
    .Where(o => o.CreateTime < DateTime.Now.AddYears(-1) && o.Amount < 100)
    .ExecuteCommand();

2. 高性能批量删除方案

2.1 分页批量删除（大数据量推荐

public async Task<long> BatchDeleteInPages<T>(Expression<Func<T, bool>> whereExpression, int pageSize = 1000) where T : class, new()
{
    var totalDeleted = 0L;
    var hasMoreData = true;
    var pageIndex = 0;

    while (hasMoreData)
    {
        try
        {
            // 先查询要删除的ID（避免表锁）
            var idsToDelete = await db.Queryable<T>()
                .Where(whereExpression)
                .OrderBy("Id")  // 确保排序一致性
                .Select("Id")   // 只查询ID字段
                .Skip(pageIndex * pageSize)
                .Take(pageSize)
                .SelectColumns("Id")
                .ToListAsync();

            if (!idsToDelete.Any())
            {
                hasMoreData = false;
                break;
            }

            var idList = idsToDelete.Select(x => (x as dynamic).Id).ToList();
            
            // 执行批量删除
            var deletedCount = await db.Deleteable<T>()
                .In(idList)
                .ExecuteCommandAsync();

            totalDeleted += deletedCount;
            pageIndex++;

            // 避免数据库压力过大，适当延迟
            if (hasMoreData)
                await Task.Delay(100);
                
            Console.WriteLine($"已删除 {totalDeleted} 条记录...");
        }
        catch (Exception ex)
        {
            // 记录日志并继续
            Console.WriteLine($"第{pageIndex + 1}页删除失败: {ex.Message}");
            break;
        }
    }

    return totalDeleted;
}

// 使用示例
var deletedCount = await BatchDeleteInPages<Order>(o => 
    o.CreateTime < DateTime.Now.AddYears(-1) && o.Status == OrderStatus.Completed);

2.2 使用存储过程批量删除

// 创建批量删除存储过程
public async Task<int> BulkDeleteWithProcedure(string tableName, DateTime deleteBefore)
{
    var parameters = new[]
    {
        new SugarParameter("@TableName", tableName),
        new SugarParameter("@DeleteBefore", deleteBefore),
        new SugarParameter("@DeletedCount", 0, true) // 输出参数
    };

    await db.Ado.UseStoredProcedure().ExecuteCommandAsync(
        "usp_BatchDeleteOldData", parameters);

    return Convert.ToInt32(parameters[2].Value);
}

// 对应的SQL Server存储过程示例
/*
CREATE PROCEDURE usp_BatchDeleteOldData
    @TableName NVARCHAR(100),
    @DeleteBefore DATETIME,
    @DeletedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Sql NVARCHAR(1000);
    SET @Sql = N'DELETE FROM ' + @TableName + 
               N' WHERE CreateTime < @DeleteBefore';
    
    EXEC sp_executesql @Sql, 
         N'@DeleteBefore DATETIME, @DeletedCount INT OUTPUT',
         @DeleteBefore, @DeletedCount OUTPUT;
END
*/

3. 高级批量删除技巧

3.1 带事务的级联删除

public async Task<bool> BatchDeleteWithTransaction<T>(List<long> ids) where T : class, new()
{
    try
    {
        db.Ado.BeginTran();
        
        // 1. 先删除关联表数据
        await db.Deleteable<OrderDetail>()
            .Where(od => ids.Contains(od.OrderId))
            .ExecuteCommandAsync();
            
        // 2. 再删除主表数据
        await db.Deleteable<T>()
            .In(ids)
            .ExecuteCommandAsync();
            
        // 3. 记录删除日志
        await db.Insertable(ids.Select(id => new DeleteLog
        {
            TableName = typeof(T).Name,
            RecordId = id,
            DeleteTime = DateTime.Now
        }).ToList()).ExecuteCommandAsync();

        db.Ado.CommitTran();
        return true;
    }
    catch (Exception ex)
    {
        db.Ado.RollbackTran();
        _logger.LogError(ex, "批量删除事务失败");
        return false;
    }
}

3.2 软删除实现

// 实体类定义软删除
public class Order : ISoftDelete
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public long Id { get; set; }
    
    public string OrderNo { get; set; }
    
    [SugarColumn(IsSoftDelete = true)]  // 软删除标记
    public bool IsDeleted { get; set; }
    
    public DateTime? DeleteTime { get; set; }
}

// 批量软删除
public async Task<int> SoftBatchDelete<T>(List<long> ids) where T : class, ISoftDelete, new()
{
    return await db.Updateable<T>()
        .SetColumns(t => new T 
        { 
            IsDeleted = true,
            DeleteTime = DateTime.Now
        })
        .Where(t => ids.Contains(t.Id))
        .ExecuteCommandAsync();
}

4. 性能优化策略

4.1 删除条件优化

public async Task<int> OptimizedBatchDelete<T>(Expression<Func<T, bool>> condition) where T : class, new()
{
    // 方法1：使用索引字段作为删除条件
    var result = await db.Deleteable<T>()
        .Where(condition)
        .ExecuteCommandAsync();
        
    return result;
}

// 推荐的删除条件（使用索引字段）
public async Task CleanOldData()
{
    // 好的实践：使用索引字段
    await db.Deleteable<Order>()
        .Where(o => o.CreateTime < DateTime.Now.AddMonths(-6))  // CreateTime应该有索引
        .ExecuteCommandAsync();
        
    // 避免的实践：使用非索引字段
    // await db.Deleteable<Order>()
    //     .Where(o => o.Description.Contains("test"))  // Description可能没有索引
    //     .ExecuteCommandAsync();
}

4.2 批量删除参数调优

public class BatchDeleteConfig
{
    public int BatchSize { get; set; } = 1000;
    public int TimeoutSeconds { get; set; } = 300;
    public int DelayMilliseconds { get; set; } = 100;
}

public async Task<BatchDeleteResult> OptimizedBatchDelete<T>(
    Expression<Func<T, bool>> condition, 
    BatchDeleteConfig config = null) where T : class, new()
{
    config ??= new BatchDeleteConfig();
    var result = new BatchDeleteResult();
    
    db.Ado.CommandTimeOut = config.TimeoutSeconds;
    
    var stopwatch = Stopwatch.StartNew();
    
    try
    {
        // 获取总记录数
        result.TotalRecords = await db.Queryable<T>()
            .Where(condition)
            .CountAsync();
            
        // 分页删除
        result.DeletedCount = await BatchDeleteInPages(condition, config.BatchSize);
        
        stopwatch.Stop();
        result.ElapsedMilliseconds = stopwatch.ElapsedMilliseconds;
    }
    catch (Exception ex)
    {
        result.Error = ex.Message;
    }
    
    return result;
}

SqlSugar批量删除的最佳实践建议：