EF Core 与 MySQL：日志和调试详解

月未盈 發表於 2025-9-18 22:33:00

本文将详细讲解EF Core与MySQL的日志和调试，分为三个部分：EF Core日志配置、MySQL日志查看以及使用调试工具分析查询性能。
<h2>1. 配置 EF Core 日志</h2>
<h3>基本日志配置</h3>
<div class="cnblogs_code">
<pre>// 在DbContext配置中启用日志记录
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = "server=localhost;database=efcoredb;user=root;password=yourpassword";

optionsBuilder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
 .EnableSensitiveDataLogging() // 记录参数值（仅开发环境）
 .EnableDetailedErrors() // 提供更详细的错误信息（仅开发环境）
 .LogTo(Console.WriteLine, LogLevel.Information) // 输出到控制台
 .LogTo(
 message => Debug.WriteLine(message), // 输出到调试输出
 LogLevel.Debug
 );
}</pre>
</div>
<h3>使用 ILoggerFactory（ASP.NET Core 应用）</h3>
<div class="cnblogs_code">
<pre>// 在Program.cs或Startup.cs中配置
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<ApplicationDbContext>(options =>
{
 var connectionString = Configuration.GetConnectionString("DefaultConnection");
 options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
 .EnableSensitiveDataLogging(Environment.IsDevelopment())
 .EnableDetailedErrors(Environment.IsDevelopment())
 .UseLoggerFactory(LoggerFactory.Create(builder =>
 {
 builder
 .AddConsole()
 .AddDebug()
 .AddFilter(DbLoggerCategory.Database.Command.Name, LogLevel.Information);
 }));
});
}</pre>
</div>
<h3>自定义日志过滤器</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 只记录执行时间超过100ms的查询
optionsBuilder.LogTo(
message =>
{
 if (message.Contains("CommandExecuted") && message.Contains("Elapsed"))
 {
 var elapsedMatch = Regex.Match(message, @"Elapsed:(\d+)ms");
 if (elapsedMatch.Success && int.Parse(elapsedMatch.Groups[1].Value) > 100)
 {
 Console.WriteLine($"慢查询: {message}");
 }
 }
},
LogLevel.Information
);</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h3>结构化日志记录</h3>
<div class="cnblogs_code">
<pre>// 使用Serilog等结构化日志框架
Log.Logger = new LoggerConfiguration()
.MinimumLevel.Information()
.WriteTo.Console(outputTemplate: "[{Timestamp:HH:mm:ss} {Level:u3}] {Message:lj}{NewLine}{Exception}")
.WriteTo.File("logs/efcore-.txt", rollingInterval: RollingInterval.Day)
.CreateLogger();

services.AddDbContext<ApplicationDbContext>(options =>
{
options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
 .LogTo(Log.Logger.Information, LogLevel.Information)
 .LogTo(Log.Logger.Warning, LogLevel.Warning)
 .LogTo(Log.Logger.Error, LogLevel.Error);
});</pre>
</div>
<h2>2. 查看 MySQL 日志</h2>
<h3>启用和配置 MySQL 日志</h3>
<div class="cnblogs_code">
<pre>-- 查看当前日志配置
SHOW VARIABLES LIKE '%log%';

-- 启用通用查询日志（记录所有查询）
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询视为慢查询

-- 启用二进制日志（用于复制和恢复）
SET GLOBAL log_bin = 'ON';

-- 启用错误日志（通常默认开启）
SHOW VARIABLES LIKE 'log_error';</pre>
</div>
<h3>在 MySQL 配置文件中设置日志</h3>
<div class="cnblogs_code">
<pre># /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf

# 通用查询日志
general_log = 1
general_log_file = /var/log/mysql/general.log

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# 错误日志
log_error = /var/log/mysql/error.log

# 二进制日志
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M</pre>
</div>
<h3>分析 MySQL 日志</h3>
<div class="cnblogs_code">
<pre># 查看慢查询日志
sudo tail -f /var/log/mysql/slow.log

# 使用mysqldumpslow分析慢查询日志
sudo mysqldumpslow /var/log/mysql/slow.log

# 按执行时间排序查看最慢的查询
sudo mysqldumpslow -s t /var/log/mysql/slow.log

# 查看通用查询日志
sudo tail -f /var/log/mysql/general.log

# 使用pt-query-digest分析查询（需要安装Percona Toolkit）
sudo pt-query-digest /var/log/mysql/slow.log</pre>
</div>
<h3>在应用程序中记录 MySQL 性能指标</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 创建性能监控服务
public class MySqlPerformanceMonitor
{
private readonly ApplicationDbContext _context;
private readonly ILogger<MySqlPerformanceMonitor> _logger;

public MySqlPerformanceMonitor(ApplicationDbContext context, ILogger<MySqlPerformanceMonitor> logger)
{
 _context = context;
 _logger = logger;
}

public async Task MonitorQueryPerformanceAsync(Func<Task> databaseOperation, string operationName)
{
 var stopwatch = Stopwatch.StartNew();

 try
 {
 await databaseOperation();
 stopwatch.Stop();

 if (stopwatch.ElapsedMilliseconds > 1000) // 超过1秒的记录为警告
 {
 _logger.LogWarning("慢查询检测: {Operation} 耗时 {ElapsedMs}ms",
 operationName, stopwatch.ElapsedMilliseconds);
 }
 else if (stopwatch.ElapsedMilliseconds > 500) // 超过0.5秒的记录为信息
 {
 _logger.LogInformation("查询性能: {Operation} 耗时 {ElapsedMs}ms",
 operationName, stopwatch.ElapsedMilliseconds);
 }
 }
 catch (Exception ex)
 {
 stopwatch.Stop();
 _logger.LogError(ex, "查询执行失败: {Operation} 耗时 {ElapsedMs}ms",
 operationName, stopwatch.ElapsedMilliseconds);
 throw;
 }
}
}</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h2>3. 使用调试工具分析查询性能</h2>
<h3>使用 EF Core 的诊断工具</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 启用诊断监听器
DiagnosticListener.AllListeners.Subscribe(new EfCoreDiagnosticListener());

public class EfCoreDiagnosticListener : IObserver<DiagnosticListener>
{
public void OnNext(DiagnosticListener value)
{
 if (value.Name == DbLoggerCategory.Name)
 {
 value.Subscribe(new EfCoreObserver());
 }
}

public void OnCompleted() { }
public void OnError(Exception error) { }
}

public class EfCoreObserver : IObserver<KeyValuePair<string, object>>
{
public void OnNext(KeyValuePair<string, object> value)
{
 if (value.Key == RelationalEventId.CommandExecuted.Name)
 {
 var command = (IDbCommand)value.Value.GetType().GetProperty("Command").GetValue(value.Value);
 var duration = (TimeSpan)value.Value.GetType().GetProperty("Duration").GetValue(value.Value);

 if (duration.TotalMilliseconds > 100)
 {
 Debug.WriteLine($"慢查询: {command.CommandText} 耗时: {duration.TotalMilliseconds}ms");
 }
 }
}

public void OnCompleted() { }
public void OnError(Exception error) { }
}</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h3>使用 Application Insights 监控</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 在Startup.cs中配置Application Insights
public void ConfigureServices(IServiceCollection services)
{
services.AddApplicationInsightsTelemetry(Configuration["APPLICATIONINSIGHTS_CONNECTION_STRING"]);

services.AddDbContext<ApplicationDbContext>(options =>
{
 options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
 .EnableSensitiveDataLogging()
 .AddApplicationInsightsDiagnostics(); // 如果支持的话
 });
}

// 或者手动记录依赖跟踪
public async Task<List<Product>> GetProductsAsync()
{
var operation = telemetryClient.StartOperation<DependencyTelemetry>("MySQL Query");
operation.Telemetry.Type = "MySQL";
operation.Telemetry.Data = "SELECT * FROM Products";

try
{
 var products = await _context.Products.ToListAsync();
 operation.Telemetry.Success = true;
 return products;
}
catch (Exception ex)
{
 operation.Telemetry.Success = false;
 telemetryClient.TrackException(ex);
 throw;
}
finally
{
 telemetryClient.StopOperation(operation);
}
}</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h3>使用 MiniProfiler</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 安装MiniProfiler.EntityFrameworkCore
// 在Startup.cs中配置
public void ConfigureServices(IServiceCollection services)
{
services.AddMiniProfiler(options =>
{
 options.RouteBasePath = "/profiler";
 options.ColorScheme = StackExchange.Profiling.ColorScheme.Auto;
 options.EnableMvcFilterProfiling = true;
 options.EnableMvcViewProfiling = true;
 options.TrackConnectionOpenClose = true;
}).AddEntityFramework();
}

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
app.UseMiniProfiler();
// ... 其他中间件
}

// 在视图中添加MiniProfiler
@inject StackExchange.Profiling.MiniProfiler Profiler
@MiniProfiler.RenderIncludes(Profiler)</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h3>使用 JetBrains dotTrace 或 dotMemory</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 在代码中添加性能分析点
public async Task<List<Product>> GetExpensiveProductsAsync()
{
// 使用JetBrains Profiler API（需要安装JetBrains.Profiler.Api包）
 JetBrains.Profiler.Api.MeasureProfiler.StartCollectingData();

try
{
 var products = await _context.Products
 .Where(p => p.Price > 100)
 .Include(p => p.Category)
 .ToListAsync();

 return products;
}
finally
{
 JetBrains.Profiler.Api.MeasureProfiler.SaveData();
}
}</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h3>使用 MySQL EXPLAIN 分析查询计划</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 在代码中执行EXPLAIN分析查询
public async Task<string> ExplainQueryAsync(IQueryable query)
{
var sql = query.ToQueryString();
var explainSql = $"EXPLAIN {sql}";

using (var command = _context.Database.GetDbConnection().CreateCommand())
{
 command.CommandText = explainSql;
 _context.Database.OpenConnection();

 using (var result = await command.ExecuteReaderAsync())
 {
 var explanation = new StringBuilder();
 while (await result.ReadAsync())
 {
 for (int i = 0; i < result.FieldCount; i++)
 {
 explanation.AppendLine($"{result.GetName(i)}: {result.GetValue(i)}");
 }
 explanation.AppendLine("---");
 }
 return explanation.ToString();
 }
}
}

// 使用方式
var query = _context.Products.Where(p => p.Price > 100);
var explanation = await ExplainQueryAsync(query);
Console.WriteLine(explanation);</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h3>使用 Visual Studio 的调试工具</h3>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03">
<div class="cnblogs_code">
<pre>// 在Visual Studio中使用诊断工具
// 1. 打开"诊断工具"窗口（调试 -> 窗口 -> 显示诊断工具）
// 2. 开始调试并记录性能
// 3. 分析CPU使用率、内存分配和数据库查询

// 使用条件断点调试特定查询
var products = _context.Products
.Where(p => p.Price > 100) // 在这里设置条件断点：p.Price > 1000
 .ToList();

// 使用即时窗口测试查询
// 在调试期间，可以在即时窗口中执行：
// _context.Products.Where(p => p.Price > 100).ToList()</pre>
</div>
</div>
</div>
</div>
</div>
</div>
<h3>创建自定义性能分析中间件</h3>
<div class="cnblogs_code">
<pre>// ASP.NET Core中间件，用于记录所有数据库操作
public class QueryPerformanceMiddleware
{
private readonly RequestDelegate _next;
private readonly ILogger<QueryPerformanceMiddleware> _logger;

public QueryPerformanceMiddleware(RequestDelegate next, ILogger<QueryPerformanceMiddleware> logger)
{
 _next = next;
 _logger = logger;
}

public async Task InvokeAsync(HttpContext context, ApplicationDbContext dbContext)
{
 var stopwatch = Stopwatch.StartNew();
 var originalQueryTrackingBehavior = dbContext.ChangeTracker.QueryTrackingBehavior;

 try
 {
 // 设置为NoTracking以提高性能，除非需要修改数据
 dbContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

 await _next(context);
 }
 finally
 {
 stopwatch.Stop();
 dbContext.ChangeTracker.QueryTrackingBehavior = originalQueryTrackingBehavior;

 // 记录请求处理时间和数据库查询统计
 _logger.LogInformation("请求 {Method} {Path} 处理时间: {ElapsedMs}ms, 数据库查询次数: {QueryCount}",
 context.Request.Method, context.Request.Path, stopwatch.ElapsedMilliseconds,
 dbContext.GetQueryCount());
 }
}
}

// 扩展方法获取查询次数
public static class DbContextExtensions
{
public static int GetQueryCount(this DbContext context)
{
 // 这种方法可能不准确，更好的方式是使用诊断监听器
 return 0; // 实际实现需要更复杂的方法
 }
}

// 在Startup.cs中注册中间件
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
app.UseMiddleware<QueryPerformanceMiddleware>();
// ... 其他中间件
}</pre>
</div>
<h2>总结</h2>
本教程详细介绍了EF Core与MySQL的日志记录和调试技术，包括：
<ol start="1">
<li>
配置EF Core日志：
<ul>
<li>
基本日志配置和敏感数据记录
</li>
<li>
使用ILoggerFactory集成ASP.NET Core日志系统
</li>
<li>
自定义日志过滤器和结构化日志记录
</li>
</ul>
</li>
<li>
查看MySQL日志：
<ul>
<li>
启用和配置各种MySQL日志（通用查询、慢查询、错误日志）
</li>
<li>
分析MySQL日志以识别性能问题
</li>
<li>
在应用程序中集成MySQL性能监控
</li>
</ul>
</li>
<li>
使用调试工具分析查询性能：
<ul>
<li>
EF Core诊断工具和监听器
</li>
<li>
Application Insights集成
</li>
<li>
MiniProfiler实时性能分析
</li>
<li>
JetBrains专业分析工具
</li>
<li>
MySQL EXPLAIN查询计划分析
</li>
<li>
Visual Studio调试工具
</li>
<li>
自定义性能分析中间件
</li>
</ul>
</li>
</ol>
通过这些工具和技术，我们可以：
<ul>
<li>
识别和解决性能瓶颈
</li>
<li>
优化数据库查询
</li>
<li>
监控应用程序的健康状况
</li>
<li>
诊断和解决生产环境中的问题
</li>
</ul>
建议在开发环境中启用详细日志记录和性能分析，而在生产环境中则使用更保守的配置，只记录警告和错误，并定期检查慢查询日志以识别需要优化的查询。
 
EF Core系列的总结到这儿就告一段落了，后面有其他的知识点再补充，接下来计划开启RabbitMQ系列。 
来源：https://www.cnblogs.com/jixingsuiyuan/p/19099782

頁: [1]

琼殿技术论坛's Archiver

EF Core 与 MySQL：日志和调试详解