避免 N+1 查询

EF Core 最常见的性能陷阱及完整解决方案

📖 目录

• 什么是 N+1 查询
• 问题示例
• 解决方案
• 检测工具
• 最佳实践

---

什么是 N+1 查询

概念理解

N+1 查询问题是指:

• 1 次查询获取父实体列表
• N 次查询获取每个父实体的子实体

总查询次数 = 1 + N

例如:
SELECT * FROM Orders              -- 1 次
SELECT * FROM Customers WHERE Id=1  -- N 次
SELECT * FROM Customers WHERE Id=2
SELECT * FROM Customers WHERE Id=3
...

---

性能影响

数据量: 100 个订单
├─ N+1 查询: 101 次查询, 3000ms
├─ Include:   2 次查询, 150ms
└─ 性能提升: 20 倍!

数据量: 1000 个订单
├─ N+1 查询: 1001 次查询, 超时
├─ Include:    2 次查询, 300ms
└─ 性能提升: ∞ (从不可用到可用)

---

问题示例

❌ 错误示例 1: 循环中查询

// 获取所有订单
var orders = await context.Orders.ToListAsync();

foreach (var order in orders)
{
    // ⚠️ 每次循环都查询数据库!
    var customer = await context.Customers
        .FirstOrDefaultAsync(c => c.Id == order.CustomerId);
    
    Console.WriteLine($"{order.Id} - {customer.Name}");
}

// 总查询次数: 1 + 100 = 101 次

---

❌ 错误示例 2: 访问导航属性未 Include

// 未 Include Customer
var orders = await context.Orders.ToListAsync();

foreach (var order in orders)
{
    // ⚠️ 延迟加载触发查询（如果启用）
    // 或 Customer 为 null（如果未启用）
    Console.WriteLine(order.Customer.Name);
}

// 总查询次数: 1 + 100 = 101 次

---

❌ 错误示例 3: Select 中的子查询

var orders = await context.Orders.ToListAsync();

var result = orders.Select(o => new
{
    o.Id,
    // ⚠️ 每次访问都触发查询
    CustomerName = context.Customers
        .Where(c => c.Id == o.CustomerId)
        .Select(c => c.Name)
        .FirstOrDefault()
}).ToList();

// 总查询次数: 1 + 100 = 101 次

---

解决方案

✅ 方案 1: Include 预加载（推荐）

var orders = await context.Orders
    .Include(o => o.Customer)  // 预加载客户
    .ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine($"{order.Id} - {order.Customer.Name}");
}

// 总查询次数: 2 次
// SELECT * FROM Orders
// SELECT * FROM Customers WHERE Id IN (...)

---

✅ 方案 2: 投影查询（最佳）

var orders = await context.Orders
    .Select(o => new
    {
        o.Id,
        o.OrderDate,
        CustomerName = o.Customer.Name
    })
    .ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine($"{order.Id} - {order.CustomerName}");
}

// 总查询次数: 1 次
// SELECT o.Id, o.OrderDate, c.Name 
// FROM Orders o 
// JOIN Customers c ON o.CustomerId = c.Id

优势:

• ✅ 单次查询
• ✅ 最小数据传输
• ✅ 无跟踪开销
• ✅ 性能最优

---

✅ 方案 3: 批量加载

// 1. 先加载所有父实体
var orders = await context.Orders.ToListAsync();

// 2. 提取所有外键
var customerIds = orders.Select(o => o.CustomerId).Distinct().ToList();

// 3. 批量加载所有子实体
var customers = await context.Customers
    .Where(c => customerIds.Contains(c.Id))
    .ToDictionaryAsync(c => c.Id);

// 4. 在内存中关联
foreach (var order in orders)
{
    var customer = customers[order.CustomerId];
    Console.WriteLine($"{order.Id} - {customer.Name}");
}

// 总查询次数: 2 次

适用场景:

• 需要灵活处理
• 复杂的数据转换
• 多次使用同一批数据

---

✅ 方案 4: AsSplitQuery

var orders = await context.Orders
    .AsSplitQuery()  // 拆分为多个查询
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToListAsync();

// 生成 3 个简单查询,而非一个复杂 JOIN

---

多级 N+1 问题

❌ 错误: 多层嵌套

var categories = await context.Categories.ToListAsync();

foreach (var category in categories)
{
    var products = await context.Products
        .Where(p => p.CategoryId == category.Id)
        .ToListAsync();
    
    foreach (var product in products)
    {
        var reviews = await context.Reviews
            .Where(r => r.ProductId == product.Id)
            .ToListAsync();
        
        Console.WriteLine($"{category.Name} - {product.Name} - {reviews.Count}");
    }
}

// 查询次数: 1 + 10 + 100 = 111 次
// (10个分类,每个分类10个产品)

---

✅ 解决: 多层 Include

var categories = await context.Categories
    .Include(c => c.Products)
        .ThenInclude(p => p.Reviews)
    .ToListAsync();

foreach (var category in categories)
{
    foreach (var product in category.Products)
    {
        Console.WriteLine($"{category.Name} - {product.Name} - {product.Reviews.Count}");
    }
}

// 查询次数: 2-3 次（使用 AsSplitQuery）

---

✅ 更好: 多层投影

var categories = await context.Categories
    .Select(c => new
    {
        c.Name,
        Products = c.Products.Select(p => new
        {
            p.Name,
            ReviewCount = p.Reviews.Count
        }).ToList()
    })
    .ToListAsync();

// 查询次数: 2 次

---

检测工具

1. 日志监控

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    
    options.LogTo(
        Console.WriteLine,
        new[] { DbLoggerCategory.Database.Command.Name },
        LogLevel.Information);
});

// 控制台输出:
// Executed DbCommand (12ms) [...]
// Executed DbCommand (8ms) [...]
// Executed DbCommand (10ms) [...]
// ... (大量查询警告!)

---

2. 自定义拦截器

public class NPlusOneDetector : DbCommandInterceptor
{
    private readonly ILogger<NPlusOneDetector> _logger;
    private int _queryCount = 0;
    private DateTime _lastReset = DateTime.UtcNow;

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        _queryCount++;
        
        // 每秒超过 10 次查询,可能存在问题
        if ((DateTime.UtcNow - _lastReset).TotalSeconds <= 1 && _queryCount > 10)
        {
            _logger.LogWarning("Possible N+1 query detected: {Count} queries in 1 second", 
                _queryCount);
        }
        
        if ((DateTime.UtcNow - _lastReset).TotalSeconds > 1)
        {
            _queryCount = 0;
            _lastReset = DateTime.UtcNow;
        }
        
        return base.ReaderExecuting(command, eventData, result);
    }
}

// 注册
options.AddInterceptors(new NPlusOneDetector(logger));

---

3. MiniProfiler

dotnet add package MiniProfiler.EntityFrameworkCore

builder.Services.AddMiniProfiler(options =>
{
    options.RouteBasePath = "/profiler";
});

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.UseMiniProfilerProfiledConnection();
});

访问: https://localhost:5001/profiler

功能:

• ✅ 可视化所有查询
• ✅ 显示查询时间
• ✅ 标记重复查询
• ✅ 检测 N+1 问题

---

4. EF Core Power Tools

Visual Studio 扩展:

• 分析 LINQ 查询
• 查看生成的 SQL
• 性能建议

---

最佳实践

1. 始终 Include 或投影

// ✅ 规则: 访问导航属性前,确保已 Include 或投影
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

// 或
var orders = await context.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToListAsync();

---

2. API 层统一使用投影

[HttpGet]
public async Task<ActionResult<IEnumerable<OrderDto>>> GetOrders()
{
    return await _context.Orders
        .Select(o => new OrderDto
        {
            Id = o.Id,
            CustomerName = o.Customer.Name,
            TotalAmount = o.Items.Sum(i => i.Quantity * i.Product.Price)
        })
        .ToListAsync();
}

---

3. 代码审查检查清单

□ 是否有循环中的数据库查询?
□ 是否访问了未 Include 的导航属性?
□ 是否可以使用投影替代?
□ 是否使用了 AsNoTracking()?
□ 查询次数是否合理?

---

4. 单元测试检测

[Fact]
public async Task GetOrders_ShouldNotCauseNPlusOneQueries()
{
    var queryCount = 0;
    
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(connectionString)
        .LogTo(_ => queryCount++, LogLevel.Information)
        .Options;
    
    await using var context = new AppDbContext(options);
    
    // 执行查询
    var orders = await context.Orders
        .Include(o => o.Customer)
        .ToListAsync();
    
    // 验证查询次数
    Assert.True(queryCount <= 3, $"Too many queries: {queryCount}");
}

---

5. 性能基准测试

public class NPlusOneBenchmark
{
    [Benchmark]
    public async Task BadApproach()
    {
        var orders = await context.Orders.ToListAsync();
        foreach (var order in orders)
        {
            var customer = await context.Customers
                .FirstOrDefaultAsync(c => c.Id == order.CustomerId);
        }
    }

    [Benchmark]
    public async Task GoodApproach()
    {
        var orders = await context.Orders
            .Include(o => o.Customer)
            .ToListAsync();
    }

    [Benchmark]
    public async Task BestApproach()
    {
        var orders = await context.Orders
            .Select(o => new { o.Id, CustomerName = o.Customer.Name })
            .ToListAsync();
    }
}

典型结果:

方法	时间	查询数	相对性能
Bad (N+1)	3000ms	101	1.0x
Good (Include)	150ms	2	20x
Best (Projection)	80ms	1	37.5x

---

特殊情况处理

1. 条件 Include

// EF Core 5.0+
var customers = await context.Customers
    .Include(c => c.Orders.Where(o => o.OrderDate >= DateTime.Today.AddDays(-30)))
    .ToListAsync();

---

2. 动态 Include

IQueryable<Order> query = context.Orders;

if (includeCustomer)
{
    query = query.Include(o => o.Customer);
}

if (includeItems)
{
    query = query.Include(o => o.Items);
}

var orders = await query.ToListAsync();

---

3. 分页时的 N+1

// ❌ 错误
var orders = await context.Orders
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

foreach (var order in orders)
{
    var customer = await context.Customers.FindAsync(order.CustomerId);
}

// ✅ 正确
var orders = await context.Orders
    .Include(o => o.Customer)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

---

📊 性能对比总结

不同数据量对比

记录数	N+1 查询	Include	投影	提升
10	50ms	15ms	10ms	5x
100	300ms	50ms	30ms	10x
1,000	3s	150ms	80ms	37x
10,000	超时	500ms	200ms	∞

---

💡 小结

核心要点:

• ✅ N+1 查询是最常见的性能陷阱
• ✅ 使用 Include 预加载关联数据
• ✅ 优先使用投影查询
• ✅ 使用工具检测 N+1 问题
• ✅ 代码审查时重点检查
• ✅ API 层统一使用投影

解决原则:

访问导航属性 → Include 或投影
API 返回 → 投影查询
复杂场景 → 批量加载
不确定 → 使用 MiniProfiler 检测

下一步:

1. 学习 投影查询
2. 掌握 预加载 Include
3. 理解 性能优化原则