选对 MySQL ADO.NET Provider：异步性能、许可证与 EF Core 集成才是真正的分水岭

// 在 Linux/macOS 上用 dotnet-counters 观察 ThreadPool 采样
// 高并发下如果 ThreadPool Queue Length 持续增长，大概率是伪异步
dotnet-counters monitor --process-id <PID> --counters System.Threading

<!-- 项目文件 .csproj -->
<!-- 移除 -->
<!-- <PackageReference Include="MySql.Data" Version="9.x.x" /> -->

<!-- 替换为 -->
<PackageReference Include="MySqlConnector" Version="2.4.0" />

<!-- 如果使用 EF Core，同时替换 Provider -->
<!-- 移除 -->
<!-- <PackageReference Include="MySql.EntityFrameworkCore" Version="9.x.x" /> -->

<!-- 替换为 -->
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="8.0.x" />

// Program.cs — Minimal API 示例
using MySqlConnector;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// MySqlConnector 的连接字符串与 MySql.Data 高度兼容，
// 但推荐显式指定以下参数以适配云环境
var connStr = new MySqlConnectionStringBuilder
{
    Server = builder.Configuration["Db:Host"] ?? "localhost",
    Database = builder.Configuration["Db:Name"] ?? "appdb",
    UserId = builder.Configuration["Db:User"] ?? "root",
    Password = builder.Configuration["Db:Pass"] ?? "",
    Port = 3306,
    // 关键：云 MySQL 强制 SSL
    SslMode = MySqlSslMode.Required,
    // 连接池调优（MySqlConnector 默认值已较合理，可按需微调）
    MinimumPoolSize = 5,
    MaximumPoolSize = 100,
    ConnectionIdleTimeout = 180, // 秒
    // 加速连接重试
    ConnectionTimeout = 15,
}.ConnectionString;

// 裸 ADO.NET 注册
builder.Services.AddScoped<MySqlConnection>(_ => new MySqlConnection(connStr));

// EF Core 注册（Pomelo）
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseMySql(connStr, ServerVersion.AutoDetect(connStr),
        mysqlOptions => mysqlOptions
            .EnableRetryOnFailure(maxRetryCount: 3)
            .CharSetBehavior(CharSetBehavior.NeverAppend)));

var app = builder.Build();

// 异步查询端点 — 真异步，不占线程池
app.MapGet("/users/{id}", async (int id, MySqlConnection db) =>
{
    await db.OpenAsync();
    using var cmd = new MySqlCommand("SELECT id, name, email FROM users WHERE id = @id", db);
    cmd.Parameters.AddWithValue("id", id);
    using var reader = await cmd.ExecuteReaderAsync();
    if (await reader.ReadAsync())
    {
        return Results.Ok(new
        {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            Email = reader.GetString(2)
        });
    }
    return Results.NotFound();
});

app.Run();

// EF Core DbContext 示例
public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
}

# 启动应用后，用 dotnet-counters 监控线程池
dotnet-counters monitor --process-id $(pgrep -f MyApp) \
  --counters System.Threading

# 用 wrk 或 hey 施加并发压力
hey -n 2000 -c 200 http://localhost:5000/users/1

# 观察：
# - ThreadPool Queue Length 应保持低位（< 10）
# - ThreadPool Thread Count 不应随并发线性飙升
# 如果切换前这两个指标明显更高，说明旧 Provider 的异步有问题