EF Core中IDatabaseModelFactory.Create()生成数据库模型过慢求助

问题核心 #

你的场景下，IDatabaseModelFactory.Create()慢的根源有两个：

1. MariaDB 10.4版本中，查询INFORMATION_SCHEMA的DELETE_RULE字段时单表耗时3-5秒，驱动默认串行查询所有表的该字段，导致100张表累计耗时超10分钟；
2. 升级到MariaDB 10.11.7后，虽然单表查询性能提升，但Pomelo驱动仍采用串行查询逻辑，未像你手动操作那样批量读取元数据，导致差距仍达40秒。

可行优化方案 #

1. 自定义数据库模型工厂，跳过DELETE_RULE查询 #

既然不需要对比DeleteBehavior，直接重写MariaDbDatabaseModelFactory，移除对DELETE_RULE的查询逻辑：

public class NoDeleteRuleDbModelFactory : MariaDbDatabaseModelFactory
{
    public NoDeleteRuleDbModelFactory(MariaDbDatabaseModelFactoryDependencies dependencies)
        : base(dependencies)
    {
    }

    protected override void ProcessForeignKeys(DatabaseModel databaseModel, IReadOnlyList<Column> columns)
    {
        // 直接批量查询外键核心信息，跳过DELETE_RULE
        var schema = Dependencies.Connection.Database;
        var foreignKeys = Dependencies.QueryExecutor.Execute<ForeignKeyDto>(
            @"SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, COLUMN_NAME, REFERENCED_COLUMN_NAME
              FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
              WHERE REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA = @schema",
            new { schema });

        // 手动构建外键对象，设置默认DeleteBehavior（不影响你的对比逻辑）
        foreach (var fkDto in foreignKeys)
        {
            var table = databaseModel.Tables.First(t => t.Name == fkDto.TABLE_NAME);
            var referencedTable = databaseModel.Tables.First(t => t.Name == fkDto.REFERENCED_TABLE_NAME);
            var column = table.Columns.First(c => c.Name == fkDto.COLUMN_NAME);
            var refColumn = referencedTable.Columns.First(c => c.Name == fkDto.REFERENCED_COLUMN_NAME);

            table.ForeignKeys.Add(new ForeignKey(
                fkDto.CONSTRAINT_NAME,
                new List<Column> { column },
                new List<Column> { refColumn },
                referencedTable,
                DeleteBehavior.Restrict));
        }
    }

    // 定义DTO接收查询结果
    private class ForeignKeyDto
    {
        public string CONSTRAINT_NAME { get; set; }
        public string TABLE_NAME { get; set; }
        public string REFERENCED_TABLE_NAME { get; set; }
        public string COLUMN_NAME { get; set; }
        public string REFERENCED_COLUMN_NAME { get; set; }
    }
}

然后在DbContext配置中替换默认服务：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseMariaDb("Your_Connection_String")
        .ReplaceService<IDatabaseModelFactory, NoDeleteRuleDbModelFactory>();
}

这个方案直接砍掉了慢查询的根源，能把加载时间降到和你手动查询相近的水平。

2. 并行预加载元数据并缓存 #

如果不想修改驱动逻辑，可以提前并行查询所有需要的元数据并缓存，再自定义工厂读取缓存：

// 预加载并缓存元数据（可放在启动逻辑中）
public static class MetadataCache
{
    public static List<TableDto> Tables { get; set; }
    public static List<ColumnDto> Columns { get; set; }
    public static List<ForeignKeyDto> ForeignKeys { get; set; }

    public static async Task LoadAsync(string connectionString, string schema)
    {
        using var conn = new MySqlConnection(connectionString);
        await conn.OpenAsync();

        // 并行查询所有元数据
        var tableTask = conn.QueryAsync<TableDto>(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schema",
            new { schema });
        var columnTask = conn.QueryAsync<ColumnDto>(
            "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema",
            new { schema });
        var fkTask = conn.QueryAsync<ForeignKeyDto>(
            "SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, COLUMN_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA = @schema",
            new { schema });

        await Task.WhenAll(tableTask, columnTask, fkTask);

        Tables = tableTask.Result.ToList();
        Columns = columnTask.Result.ToList();
        ForeignKeys = fkTask.Result.ToList();
    }

    // 定义DTO类
    public class TableDto { public string TABLE_NAME { get; set; } }
    public class ColumnDto { public string TABLE_NAME { get; set; } public string COLUMN_NAME { get; set; } public string DATA_TYPE { get; set; } public string IS_NULLABLE { get; set; } }
    public class ForeignKeyDto { public string CONSTRAINT_NAME { get; set; } public string TABLE_NAME { get; set; } public string REFERENCED_TABLE_NAME { get; set; } public string COLUMN_NAME { get; set; } public string REFERENCED_COLUMN_NAME { get; set; } }
}

// 自定义缓存工厂
public class CachedDbModelFactory : IDatabaseModelFactory
{
    public DatabaseModel Create(DatabaseModelFactoryOptions options)
    {
        var model = new DatabaseModel();

        // 从缓存加载表
        foreach (var tableDto in MetadataCache.Tables)
        {
            var table = new Table(tableDto.TABLE_NAME, model);
            model.Tables.Add(table);

            // 加载列
            var columns = MetadataCache.Columns.Where(c => c.TABLE_NAME == tableDto.TABLE_NAME);
            foreach (var colDto in columns)
            {
                table.Columns.Add(new Column(
                    colDto.COLUMN_NAME,
                    table,
                    Dependencies.TypeMappingSource.FindMapping(colDto.DATA_TYPE),
                    colDto.IS_NULLABLE == "YES"));
            }

            // 加载外键
            var fks = MetadataCache.ForeignKeys.Where(f => f.TABLE_NAME == tableDto.TABLE_NAME);
            foreach (var fkDto in fks)
            {
                var referencedTable = model.Tables.First(t => t.Name == fkDto.REFERENCED_TABLE_NAME);
                var column = table.Columns.First(c => c.Name == fkDto.COLUMN_NAME);
                var refColumn = referencedTable.Columns.First(c => c.Name == fkDto.REFERENCED_COLUMN_NAME);

                table.ForeignKeys.Add(new ForeignKey(
                    fkDto.CONSTRAINT_NAME,
                    new List<Column> { column },
                    new List<Column> { refColumn },
                    referencedTable,
                    DeleteBehavior.Restrict));
            }
        }

        return model;
    }

    public MariaDbDatabaseModelFactoryDependencies Dependencies { get; }

    public CachedDbModelFactory(MariaDbDatabaseModelFactoryDependencies dependencies)
    {
        Dependencies = dependencies;
    }
}

使用时先调用MetadataCache.LoadAsync()预加载，再注册缓存工厂即可。

3. 优化MariaDB配置 #

针对MariaDB 10.11.7，开启元数据缓存进一步提速：

-- 全局开启INFORMATION_SCHEMA统计缓存，有效期1小时
SET GLOBAL information_schema_stats_expiry = 3600;
-- 也可以临时设置会话级
SET SESSION information_schema_stats_expiry = 3600;

这个配置让MariaDB缓存INFORMATION_SCHEMA的查询结果，避免重复扫描磁盘。

补充说明 #

EfCore.SchemaCompare使用的Pomelo驱动默认采用串行单表查询元数据，而你手动查询是批量一次性读取，这是两者性能差距的核心原因。上面的方案要么跳过不必要的字段，要么改用并行批量查询，都能有效缩小差距。

内容的提问来源于stack exchange，提问作者JIT Solution