查询缓慢排查清单

概述

查询性能问题是 EF Core 应用中最常见的性能瓶颈。本清单提供系统化的排查方法,帮助快速定位和解决慢查询问题。

排查流程

发现慢查询
    ↓
1. 确认问题(复现/测量)
    ↓
2. 收集信息(SQL/参数/执行计划)
    ↓
3. 分析原因(N+1/索引/数据量)
    ↓
4. 实施优化
    ↓
5. 验证效果

---

第一步: 确认问题

识别慢查询

// 方法 1: MiniProfiler(推荐)
// 安装: dotnet add package MiniProfiler.EntityFrameworkCore
// 访问: /profiler 查看所有查询耗时

// 方法 2: 日志记录
options.LogTo(message =>
{
    if (message.Contains("Executed DbCommand"))
    {
        var match = Regex.Match(message, @"Executed DbCommand \((\d+)ms\)");
        if (match.Success && int.TryParse(match.Groups[1].Value, out int ms))
        {
            if (ms > 1000)  // 超过 1 秒
            {
                _logger.LogWarning("SLOW QUERY: {Message}", message);
            }
        }
    }
}, LogLevel.Information);

// 方法 3: 自定义拦截器
public class SlowQueryInterceptor : DbCommandInterceptor
{
    private readonly ILogger<SlowQueryInterceptor> _logger;
    private readonly TimeSpan _threshold = TimeSpan.FromSeconds(1);

    public override async ValueTask<InterceptionResult<object>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<object> result,
        CancellationToken cancellationToken = default)
    {
        var stopwatch = Stopwatch.StartNew();
        
        var interceptorResult = await base.ReaderExecutingAsync(
            command, eventData, result, cancellationToken);
        
        stopwatch.Stop();
        
        if (stopwatch.Elapsed > _threshold)
        {
            _logger.LogWarning("""
                SLOW QUERY DETECTED
                Duration: {Duration}ms
                SQL: {Sql}
                """,
                stopwatch.ElapsedMilliseconds,
                command.CommandText);
        }
        
        return interceptorResult;
    }
}

基准测试

// 记录正常情况下的查询时间
var stopwatch = Stopwatch.StartNew();
var products = await context.Products.ToListAsync();
stopwatch.Stop();

_logger.LogInformation("Query took {Duration}ms", stopwatch.ElapsedMilliseconds);
// 正常: < 100ms
// 警告: 100-500ms
// 严重: > 500ms

---

第二步: 收集信息

查看生成的 SQL

// 方法 1: ToQueryString
var query = context.Products
    .Where(p => p.Price > 100)
    .Include(p => p.Category);

string sql = query.ToQueryString();
Console.WriteLine(sql);

// 方法 2: 日志
options.LogTo(Console.WriteLine, LogLevel.Information);

// 方法 3: SQL Profiler
// SQL Server Management Studio → Tools → SQL Server Profiler

检查执行计划

-- SQL Server
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT [p].[Id], [p].[Name], [p].[Price]
FROM [Products] AS [p]
WHERE [p].[Price] > 100;

-- 查看输出:
-- Table 'Products'. Scan count 1, logical reads 150
-- SQL Server Execution Times: CPU time = 15 ms, elapsed time = 42 ms

-- PostgreSQL
EXPLAIN ANALYZE
SELECT p.id, p.name, p.price
FROM products p
WHERE p.price > 100;

-- MySQL
EXPLAIN FORMAT=JSON
SELECT p.id, p.name, p.price
FROM products p
WHERE p.price > 100;

收集查询统计

// 查询执行次数和平均时间
public class QueryStatistics
{
    private readonly ConcurrentDictionary<string, List<long>> _queryTimes = new();

    public void RecordQuery(string sql, long durationMs)
    {
        _queryTimes.AddOrUpdate(
            sql,
            new List<long> { durationMs },
            (key, list) => { list.Add(durationMs); return list; });
    }

    public Dictionary<string, QueryStats> GetStatistics()
    {
        return _queryTimes.ToDictionary(
            kvp => kvp.Key,
            kvp => new QueryStats
            {
                Count = kvp.Value.Count,
                AvgDuration = kvp.Value.Average(),
                MaxDuration = kvp.Value.Max(),
                MinDuration = kvp.Value.Min()
            });
    }
}

public record QueryStats
{
    public int Count { get; init; }
    public double AvgDuration { get; init; }
    public long MaxDuration { get; init; }
    public long MinDuration { get; init; }
}

---

第三步: 分析原因

常见原因 1: N+1 查询问题

// ❌ 问题代码
var orders = await context.Orders.ToListAsync();

foreach (var order in orders)
{
    // ⚠️ 每次循环都执行一次查询
    order.Customer = await context.Customers
        .FirstOrDefaultAsync(c => c.Id == order.CustomerId);
}

// 症状:
// - 查询数量 = 1 + N (N 为订单数)
// - 每个查询很快,但总耗时长

// ✅ 解决方案 1: Include
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

// ✅ 解决方案 2: 批量加载
var orderIds = orders.Select(o => o.Id).ToList();
var customers = await context.Customers
    .Where(c => orders.Any(o => o.CustomerId == c.Id))
    .ToDictionaryAsync(c => c.Id);

foreach (var order in orders)
{
    order.Customer = customers[order.CustomerId];
}

常见原因 2: 缺少索引

// ❌ 问题: 频繁查询的列没有索引
var products = await context.Products
    .Where(p => p.CategoryId == 5)  // ⚠️ 全表扫描
    .ToListAsync();

// 检查索引
// SQL Server:
SELECT 
    i.name AS IndexName,
    c.name AS ColumnName
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
INNER JOIN sys.columns c ON ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Products');

// ✅ 解决方案: 添加索引
// Migration:
migrationBuilder.CreateIndex(
    name: "IX_Products_CategoryId",
    table: "Products",
    column: "CategoryId");

// 或 Fluent API:
modelBuilder.Entity<Product>(entity =>
{
    entity.HasIndex(p => p.CategoryId);
});

常见原因 3: 选择了不必要的列

// ❌ 问题: 加载所有列
var products = await context.Products.ToListAsync();
// SELECT * FROM Products (可能有 50+ 列)

// ✅ 解决方案: 投影查询
var products = await context.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price
        // 仅选择需要的列
    })
    .ToListAsync();

常见原因 4: 客户端评估

// ❌ 问题: 无法翻译到服务器端
var products = await context.Products.ToListAsync();  // 加载所有数据

// 然后在客户端过滤
var filtered = products.Where(p => CalculateScore(p) > 100).ToList();

double CalculateScore(Product p)
{
    // 复杂计算,无法翻译为 SQL
    return p.Price * p.Rating * Math.Log(p.ReviewCount + 1);
}

// ✅ 解决方案 1: 在数据库端过滤尽可能多的数据
var products = await context.Products
    .Where(p => p.Price > 50)  // 数据库过滤
    .Where(p => p.Rating > 3)   // 数据库过滤
    .ToListAsync();

// 然后在客户端进行复杂计算
var filtered = products.Where(p => CalculateScore(p) > 100).ToList();

// ✅ 解决方案 2: 使用可翻译的表达式
var products = await context.Products
    .Where(p => p.Price * p.Rating > 100)  // 可翻译
    .ToListAsync();

常见原因 5: 数据量过大

// ❌ 问题: 一次性加载大量数据
var allOrders = await context.Orders.ToListAsync();  // 100,000+ 条

// ✅ 解决方案 1: 分页
var pageSize = 50;
var page = 1;

var orders = await context.Orders
    .OrderByDescending(o => o.OrderDate)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

// ✅ 解决方案 2: 流式处理
await using var stream = context.Orders
    .AsNoTracking()
    .AsAsyncEnumerable();

await foreach (var order in stream)
{
    ProcessOrder(order);  // 逐个处理,不全部加载到内存
}

常见原因 6: 隐式 JOIN

// ❌ 问题: 导航属性导致额外 JOIN
var orders = await context.Orders
    .Select(o => new 
    { 
        o.Id,
        CustomerName = o.Customer.Name,  // ⚠️ 隐式 JOIN
        City = o.Customer.Address.City   // ⚠️ 又一个 JOIN
    })
    .ToListAsync();

// ✅ 解决方案: 显式控制 JOIN
var orders = await context.Orders
    .Join(context.Customers,
          o => o.CustomerId,
          c => c.Id,
          (o, c) => new 
          { 
              o.Id,
              CustomerName = c.Name
          })
    .ToListAsync();

---

第四步: 实施优化

优化策略 1: 添加索引

-- 单列索引
CREATE INDEX IX_Products_CategoryId ON Products(CategoryId);

-- 复合索引(注意列顺序)
CREATE INDEX IX_Orders_CustomerId_OrderDate 
ON Orders(CustomerId, OrderDate DESC);

-- 覆盖索引(包含查询所需的所有列)
CREATE INDEX IX_Products_Price_Name 
ON Products(Price) INCLUDE (Name);

-- 过滤索引(仅索引部分数据)
CREATE INDEX IX_Products_Active 
ON Products(Name) WHERE IsDeleted = 0;

优化策略 2: 查询拆分

// ❌ 问题: 复杂的 Include 导致巨大的 SQL
var order = await context.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Include(o => o.Customer)
    .Include(o => o.Payments)
    .FirstOrDefaultAsync(o => o.Id == orderId);

// ✅ 解决方案: 拆分为多个简单查询
var order = await context.Orders
    .FirstOrDefaultAsync(o => o.Id == orderId);

var items = await context.OrderItems
    .Where(i => i.OrderId == orderId)
    .Include(i => i.Product)
    .ToListAsync();

var customer = await context.Customers
    .FirstOrDefaultAsync(c => c.Id == order.CustomerId);

var payments = await context.Payments
    .Where(p => p.OrderId == orderId)
    .ToListAsync();

// 组合结果
order.Items = items;
order.Customer = customer;
order.Payments = payments;

优化策略 3: 缓存

// 内存缓存
private readonly IMemoryCache _cache;

public async Task<List<Category>> GetCategoriesAsync()
{
    return await _cache.GetOrCreateAsync("categories", async entry =>
    {
        entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(30);
        
        return await context.Categories
            .AsNoTracking()
            .ToListAsync();
    });
}

// 分布式缓存(Redis)
private readonly IDistributedCache _distributedCache;

public async Task<Product?> GetProductAsync(int id)
{
    var cacheKey = $"product:{id}";
    var cached = await _distributedCache.GetStringAsync(cacheKey);
    
    if (cached != null)
    {
        return JsonSerializer.Deserialize<Product>(cached);
    }
    
    var product = await context.Products.FindAsync(id);
    
    if (product != null)
    {
        var json = JsonSerializer.Serialize(product);
        await _distributedCache.SetStringAsync(cacheKey, json, 
            new DistributedCacheEntryOptions
            {
                AbsoluteExpirationRelativeToNow = TimeSpan.FromHours(1)
            });
    }
    
    return product;
}

优化策略 4: 预编译查询

// 高频查询使用预编译
private static readonly Func<AppDbContext, int, Task<Product?>> _getProductById = 
    EF.CompileAsyncQuery((AppDbContext db, int id) => 
        db.Products.FirstOrDefault(p => p.Id == id));

// 使用(性能提升 10-20%)
var product = await _getProductById(context, productId);

优化策略 5: 批量操作

# 安装 BulkExtensions
dotnet add package EFCore.BulkExtensions

// 批量插入
var products = Enumerable.Range(1, 10000)
    .Select(i => new Product { Name = $"Product {i}", Price = i })
    .ToList();

await context.BulkInsertAsync(products);  // 比 AddRange 快 10-50 倍

// 批量更新
await context.Products
    .Where(p => p.CategoryId == 1)
    .BatchUpdateAsync(p => new Product { Price = p.Price * 1.1m });

// 批量删除
await context.Products
    .Where(p => p.IsDeleted)
    .BatchDeleteAsync();

---

第五步: 验证效果

性能对比

// 优化前
var stopwatch = Stopwatch.StartNew();
var products = await context.Products.ToListAsync();
stopwatch.Stop();
_logger.LogInformation("Before optimization: {Duration}ms", stopwatch.ElapsedMilliseconds);

// 优化后
stopwatch.Restart();
var productsOptimized = await context.Products
    .AsNoTracking()
    .Select(p => new { p.Id, p.Name })
    .ToListAsync();
stopwatch.Stop();
_logger.LogInformation("After optimization: {Duration}ms", stopwatch.ElapsedMilliseconds);

// 输出:
// Before optimization: 1250ms
// After optimization: 85ms
// Improvement: 93% ⬆️

监控持续性能

// Application Insights
_telemetryClient.TrackMetric("QueryDuration", durationMs);

// Prometheus
QueryDurationHistogram.Observe(durationMs / 1000.0);

// 自定义仪表板
app.MapGet("/api/metrics/queries", () =>
{
    return Results.Ok(_queryStatistics.GetStatistics());
});

---

排查工具总结

工具	用途	适用场景
MiniProfiler	可视化性能分析	开发环境
SQL Profiler	捕获 SQL 语句	所有环境
EXPLAIN ANALYZE	执行计划分析	数据库层面
DiagnosticListener	自定义监控	生产环境
Application Insights	APM 监控	Azure 部署

---

最佳实践清单

✅ 必须做的

• [ ] 为外键列添加索引
• [ ] 为频繁查询的列添加索引
• [ ] 使用 AsNoTracking() 进行只读查询
• [ ] 使用投影查询减少数据传输
• [ ] 避免 N+1 查询(使用 Include)
• [ ] 实现分页(不要一次性加载所有数据)
• [ ] 启用查询计划缓存
• [ ] 监控慢查询并设置告警

❌ 绝对不要做的

• [ ] 不要在循环中执行查询
• [ ] 不要加载不需要的列
• [ ] 不要忽略 N+1 问题
• [ ] 不要在客户端进行大量过滤
• [ ] 不要忘记释放 DbContext
• [ ] 不要在生产环境启用详细日志
• [ ] 不要使用 SELECT *

---

总结

快速诊断流程图

查询缓慢?
│
├─ 查询次数多?
│  └─ ✅ N+1 问题 → 使用 Include
│
├─ 单次查询慢?
│  ├─ 全表扫描? → ✅ 添加索引
│  ├─ 返回数据多? → ✅ 分页/投影
│  └─ 复杂 JOIN? → ✅ 查询拆分
│
├─ 数据量大?
│  └─ ✅ 批量操作/缓存
│
└─ 频繁执行?
   └─ ✅ 预编译查询/二级缓存

核心要点

1. 先测量: 确认问题,收集数据
2. 找根因: N+1/索引/数据量/客户端评估
3. 选方案: 根据原因选择优化策略
4. 验证效果: 对比优化前后性能
5. 持续监控: 建立性能基线和告警