EF Core 性能优化题 题：给定百万级订单表，实现高效分页 + 动态条件查询 + 导出 Excel（避免内存爆炸）。 关键：`AsNoTracking()` + `Skip/Take` + `IQueryable` 动态构建 + `ToListAsync` + NPOI 流式导出
// 订单实体 public class Order {public long Id { get; set; }public string OrderNo { get; set; }public decimal Amount { get; set; }public DateTime CreateTime { get; set; }public int Status { get; set; } // 0=待支付 1=已完成 2=已取消public string CustomerName { get; set; } }// DbContext public class AppDbContext : DbContext {public DbSet<Order> Orders => Set<Order>();public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { } }// 订单查询条件 public class OrderQueryDto {public string? OrderNo { get; set; }public DateTime? StartTime { get; set; }public DateTime? EndTime { get; set; }public int? Status { get; set; }public string? CustomerName { get; set; }// 分页参数public int PageIndex { get; set; } = 1;public int PageSize { get; set; } = 20; }• AsNoTracking()：禁用跟踪，大幅提升查询速度，减少内存占用 • IQueryable 动态构建：只拼接条件，不执行 SQL，最后统一执行 • Skip/Take：数据库端分页，只查当前页数据 • ToListAsync()：异步执行，不阻塞线程，适配高并发 using Microsoft.EntityFrameworkCore;public class OrderService {private readonly AppDbContext _dbContext;public OrderService(AppDbContext dbContext){_dbContext = dbContext;}/// <summary>/// 动态条件 + 高效分页查询/// </summary>public async Task<(List<Order> List, long TotalCount)> GetOrderPageAsync(OrderQueryDto query){// 1. 基础查询：禁用跟踪 + 无锁查询（百万级必加）var queryable = _dbContext.Orders.AsNoTracking().AsSplitQuery() // 复杂查询拆分，避免笛卡尔积 .AsQueryable();// 2. 动态构建条件（IQueryable 延迟执行，不触发查询）if (!string.IsNullOrWhiteSpace(query.OrderNo))queryable = queryable.Where(o => o.OrderNo.Contains(query.OrderNo));if (query.StartTime.HasValue)queryable = queryable.Where(o => o.CreateTime >= query.StartTime.Value);if (query.EndTime.HasValue)queryable = queryable.Where(o => o.CreateTime <= query.EndTime.Value);if (query.Status.HasValue)queryable = queryable.Where(o => o.Status == query.Status.Value);if (!string.IsNullOrWhiteSpace(query.CustomerName))queryable = queryable.Where(o => o.CustomerName.Contains(query.CustomerName));// 3. 获取总条数（仅 COUNT，高效）var totalCount = await queryable.LongCountAsync();// 4. 数据库端分页（核心：Skip/Take 生成 SQL 分页）var skip = (query.PageIndex - 1) * query.PageSize;var list = await queryable.OrderByDescending(o => o.CreateTime) // 必须排序，Skip/Take 要求 .Skip(skip).Take(query.PageSize).ToListAsync();return (list, totalCount);} } • 不加载全表到内存：流式读取数据、流式写入 Excel • NPOI 流式写入：SXSSF 模式，自动刷盘，内存占用极低 • 分批次读取：每次只查 1000 条，循环写入，内存恒定 Install-Package NPOI Install-Package NPOI.OpenXml4Net Install-Package NPOI.OpenXmlFormats using NPOI.SS.UserModel; using NPOI.XSSF.Streaming; using System.IO;/// <summary> /// 百万级订单流式导出 Excel（无内存爆炸） /// </summary> public async Task<byte[]> ExportOrderExcelAsync(OrderQueryDto query) {// 1. 复用动态查询条件（不加载全表）var queryable = _dbContext.Orders.AsNoTracking().AsQueryable();// 动态条件（和分页查询完全一致）if (!string.IsNullOrWhiteSpace(query.OrderNo))queryable = queryable.Where(o => o.OrderNo.Contains(query.OrderNo));if (query.StartTime.HasValue)queryable = queryable.Where(o => o.CreateTime >= query.StartTime.Value);if (query.EndTime.HasValue)queryable = queryable.Where(o => o.CreateTime <= query.EndTime.Value);if (query.Status.HasValue)queryable = queryable.Where(o => o.Status == query.Status.Value);if (!string.IsNullOrWhiteSpace(query.CustomerName))queryable = queryable.Where(o => o.CustomerName.Contains(query.CustomerName));// 2. 排序（必须）queryable = queryable.OrderByDescending(o => o.Id);// 3. NPOI 流式工作簿（SXSSF 关键：低内存模式）var workbook = new SXSSFWorkbook(1000); // 每次保留1000行在内存var sheet = workbook.CreateSheet("订单数据");// 4. 创建表头var headerRow = sheet.CreateRow(0);headerRow.CreateCell(0).SetCellValue("订单ID");headerRow.CreateCell(1).SetCellValue("订单号");headerRow.CreateCell(2).SetCellValue("订单金额");headerRow.CreateCell(3).SetCellValue("创建时间");headerRow.CreateCell(4).SetCellValue("状态");headerRow.CreateCell(5).SetCellValue("客户姓名");var rowIndex = 1;var batchSize = 1000; // 每批次读取1000条（可调整）long skip = 0;// 5. 流式循环读取 + 写入（核心：永不加载全表）while (true){// 每次只查 1000 条var batchData = await queryable.Skip((int)skip).Take(batchSize).ToListAsync();if (batchData.Count == 0) break; // 无数据退出// 写入当前批次foreach (var order in batchData){var row = sheet.CreateRow(rowIndex++);row.CreateCell(0).SetCellValue(order.Id);row.CreateCell(1).SetCellValue(order.OrderNo);row.CreateCell(2).SetCellValue((double)order.Amount);row.CreateCell(3).SetCellValue(order.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));row.CreateCell(4).SetCellValue(order.Status switch { 0 => "待支付", 1 => "已完成", 2 => "已取消" });row.CreateCell(5).SetCellValue(order.CustomerName);}skip += batchSize;}// 6. 流式写入到字节数组（不占用大内存）using var ms = new MemoryStream();workbook.Write(ms, true); // 流式写入 workbook.Close();ms.Position = 0;return ms.ToArray(); }最终 API 调用（Web API） [ApiController] [Route("api/orders")] public class OrderController : ControllerBase {private readonly OrderService _orderService;public OrderController(OrderService orderService){_orderService = orderService;}// 分页查询[HttpGet("page")]public async Task<IActionResult> GetPage([FromQuery] OrderQueryDto query){var (list, total) = await _orderService.GetOrderPageAsync(query);return Ok(new { List = list, Total = total });}// 流式导出[HttpGet("export")]public async Task<IActionResult> Export([FromQuery] OrderQueryDto query){var bytes = await _orderService.ExportOrderExcelAsync(query);return File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"订单导出_{DateTime.Now:yyyyMMddHHmmss}.xlsx");} }数据库索引（性能提升 10~100 倍）CREATE INDEX IX_Orders_CreateTime ON Orders(CreateTime); CREATE INDEX IX_Orders_Status ON Orders(Status); CREATE INDEX IX_Orders_OrderNo ON Orders(OrderNo);