EF Core 与 MySQL：查询优化详解

 

1. 使用 AsNoTracking 提高查询性能

基本用法

// 常规查询（会跟踪实体变更）
var products = context.Products.Where(p => p.Price > 100).ToList();// 使用 AsNoTracking（不跟踪实体变更，性能更好）
var products = context.Products.AsNoTracking().Where(p => p.Price > 100).ToList();

应用场景

• 只读查询操作

• 数据展示场景

• 报表生成

• 大数据量查询

全局配置

// 在DbContext中配置全局不跟踪
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{optionsBuilder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString)).UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}// 或者针对特定查询启用跟踪
var products = context.Products.AsTracking() // 显式启用跟踪.Where(p => p.Price > 100).ToList();

2. 使用 Include 和 ThenInclude 进行贪婪加载

基本用法

// 加载单个关联实体
var blogs = context.Blogs.Include(b => b.Posts) // 加载Posts集合
    .ToList();// 加载多层关联实体
var blogs = context.Blogs.Include(b => b.Posts).ThenInclude(p => p.Comments) // 加载Posts下的Comments.Include(b => b.Author) // 加载单个Author
    .ToList();// 加载多个关联实体
var blogs = context.Blogs.Include(b => b.Posts).Include(b => b.Tags).ToList();

过滤包含的关联数据

// 只加载符合条件的关联数据（EF Core 5.0+）
var blogs = context.Blogs.Include(b => b.Posts.Where(p => p.IsPublished)).Include(b => b.Tags.OrderBy(t => t.Name).Take(5)).ToList();// 使用字符串方式包含（动态查询场景）
var blogs = context.Blogs.Include("Posts.Comments").ToList();

性能考虑

// 避免过度包含（N+1查询问题）
// 错误方式：会产生N+1查询
var blogs = context.Blogs.ToList();
foreach (var blog in blogs)
{var posts = context.Posts.Where(p => p.BlogId == blog.Id).ToList();// 处理posts...
}// 正确方式：使用Include一次性加载所有关联数据
var blogs = context.Blogs.Include(b => b.Posts).ToList();
foreach (var blog in blogs)
{// 直接访问blog.Posts，不会产生额外查询
}

3. 使用 Select 进行投影查询

基本投影

// 只选择需要的字段
var productInfo = context.Products.Where(p => p.Price > 100).Select(p => new {p.Id,p.Name,p.Price,CategoryName = p.Category.Name // 关联实体字段
    }).ToList();// 转换为DTO对象
var productDtos = context.Products.Select(p => new ProductDto{Id = p.Id,Name = p.Name,Price = p.Price,CategoryName = p.Category.Name}).ToList();

条件投影

var products = context.Products.Select(p => new {p.Id,p.Name,PriceCategory = p.Price > 100 ? "Expensive" : "Affordable",HasStock = p.Stock > 0}).ToList();

集合投影

var blogSummaries = context.Blogs.Select(b => new {b.Id,b.Title,PostCount = b.Posts.Count(),LatestPost = b.Posts.OrderByDescending(p => p.CreatedDate).Select(p => new { p.Title, p.CreatedDate }).FirstOrDefault()}).ToList();

4. 原始 SQL 查询

基本查询

// 使用FromSqlRaw执行原始SQL查询
var products = context.Products.FromSqlRaw("SELECT * FROM Products WHERE Price > {0} AND Stock > {1}", 100, 0).ToList();// 使用参数化查询防止SQL注入
var minPrice = 100;
var minStock = 0;
var products = context.Products.FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice} AND Stock > {minStock}").ToList();

与LINQ结合使用

// 原始SQL查询后继续使用LINQ
var expensiveProducts = context.Products.FromSqlRaw("SELECT * FROM Products WHERE Price > 100").Where(p => p.Stock > 0).OrderByDescending(p => p.Price).ToList();

执行非查询SQL

// 执行更新、删除等操作
var affectedRows = context.Database.ExecuteSqlRaw("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {0}", categoryId);// 使用存储过程
var products = context.Products.FromSqlRaw("EXEC GetExpensiveProducts @minPrice = {0}", 100).ToList();

5. 使用索引优化查询

在模型中定义索引

protected override void OnModelCreating(ModelBuilder modelBuilder)
{// 创建单列索引modelBuilder.Entity<Product>().HasIndex(p => p.Name);// 创建唯一索引modelBuilder.Entity<Product>().HasIndex(p => p.Sku).IsUnique();// 创建复合索引modelBuilder.Entity<Product>().HasIndex(p => new { p.CategoryId, p.Price });// 创建筛选索引（MySQL 8.0+）modelBuilder.Entity<Product>().HasIndex(p => p.Price).HasFilter("[Price] > 100");
}

在迁移中创建索引

// 创建迁移后，可以自定义索引
migrationBuilder.CreateIndex(name: "IX_Products_CategoryId_Price",table: "Products",columns: new[] { "CategoryId", "Price" },filter: "Price > 100");

使用索引提示（MySQL 8.0+）

// 强制使用特定索引
var products = context.Products.FromSqlRaw("SELECT * FROM Products USE INDEX (IX_Products_Price) WHERE Price > 100").ToList();

监控查询性能

// 启用MySQL慢查询日志
// 在my.cnf或my.ini中添加：
// slow_query_log = 1
// slow_query_log_file = /var/log/mysql/mysql-slow.log
// long_query_time = 2// 使用EXPLAIN分析查询
var explainResult = context.Database.ExecuteSqlRaw("EXPLAIN SELECT * FROM Products WHERE Price > 100");

6. 其他优化技巧

分页优化

// 使用Keyset分页（基于值的分页）
var lastPrice = 100;
var lastId = 50;
var products = context.Products.Where(p => p.Price > lastPrice || (p.Price == lastPrice && p.Id > lastId)).OrderBy(p => p.Price).ThenBy(p => p.Id).Take(20).ToList();// 传统分页（适用于小数据集）
var pageNumber = 2;
var pageSize = 20;
var products = context.Products.OrderBy(p => p.Name).Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();

批量操作优化

// 使用AddRange批量添加
var products = new List<Product>();
// 添加多个产品到列表
context.Products.AddRange(products);
context.SaveChanges();// 使用ExecuteUpdate批量更新（EF Core 7.0+）
context.Products.Where(p => p.CategoryId == 1).ExecuteUpdate(p => p.SetProperty(x => x.Price, x => x.Price * 1.1m));// 使用ExecuteDelete批量删除（EF Core 7.0+）
context.Products.Where(p => p.Stock == 0).ExecuteDelete();

查询编译优化

// 使用编译查询（适用于频繁执行的查询）
private static readonly Func<ApplicationDbContext, int, IEnumerable<Product>> GetProductsByCategory = EF.CompileQuery((ApplicationDbContext context, int categoryId) => context.Products.Where(p => p.CategoryId == categoryId));// 使用编译查询
var products = GetProductsByCategory(context, 1).ToList();

连接池优化

// 在连接字符串中配置连接池
var connectionString = "server=localhost;database=efcoredb;user=root;password=yourpassword;Pooling=true;MinimumPoolSize=5;MaximumPoolSize=100;ConnectionTimeout=30;";

7. 性能监控和诊断

启用EF Core日志

// 在DbContext配置中启用敏感数据日志记录和详细错误
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{optionsBuilder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString)).EnableSensitiveDataLogging() // 仅开发环境.EnableDetailedErrors() // 仅开发环境.LogTo(Console.WriteLine, LogLevel.Information); // 记录SQL查询
}

使用MiniProfiler监控性能

// 安装MiniProfiler.EntityFrameworkCore
services.AddMiniProfiler(options => 
{options.RouteBasePath = "/profiler";options.ColorScheme = StackExchange.Profiling.ColorScheme.Auto;
}).AddEntityFramework();

分析查询性能

// 使用MySQL的EXPLAIN分析查询
var query = context.Products.Where(p => p.Price > 100);
var sql = query.ToQueryString(); // 获取生成的SQL
Console.WriteLine(sql);// 或者在数据库直接执行EXPLAIN
var explainResult = context.Database.ExecuteSqlRaw("EXPLAIN SELECT * FROM Products WHERE Price > 100");

总结

本教程详细介绍了EF Core与MySQL的查询优化技巧，包括：

1. 使用AsNoTracking提高只读查询性能

2. 使用Include和ThenInclude正确加载关联数据，避免N+1查询问题

3. 使用Select投影查询减少数据传输量

4. 使用原始SQL查询处理复杂场景

5. 使用索引优化查询性能

6. 其他优化技巧如分页、批量操作和查询编译

7. 性能监控和诊断工具的使用

优化查询性能是一个持续的过程，需要结合实际应用场景和数据库特性进行调整。建议定期分析慢查询日志，使用EXPLAIN分析查询计划，并根据结果调整索引和查询方式。

记住，最好的优化往往是基于实际性能分析而不是盲目猜测。在生产环境中，始终使用性能监控工具来识别和解决瓶颈问题。