.Net极限生产力之分表分库全自动化Migrations Code-First
目录
- 开始
- 移除静态容器
- 原生efcore
- 启动程序
- 添加todo字段并迁移
- 集成AbpVNext
- 新建两个接口用于赋值创建时间和guid
- AbpDbContext抽象类
- 新增分库分表路由
- 编写sqlserver分片迁移脚本生成
- abp的efcore模块注入
- 启动abp迁移项目
- 集成Furion
- 新增todoitem
- 新增分表分库路由
- 新增分表路由
- 编写迁移文件
- 启动注入
- 添加迁移文件
- 集成WTM
- 添加依赖
- 新增分表分库路由
- 创建DbContextCreator
- 静态构造IShardingRuntimeContext
- 创建抽象分片DbContext
- 注入ShardingCore
开始
本次我们的主题就是极限生产力,其他语言望尘莫及的分表分库全自动化Migrations Code-First 加 efcore 分表分库无感开发
,经过这么多框架的兼容我自己也认识到了一些问题,譬如在ShardingCore初始化前使用(毕竟efcore)的初始化是在依赖注入的时候不需要手动调用初始化，比如efcore.tool的迁移的问题,本项目不能迁移,因为efcore.tool在使用命令的时候不会调用Configure导致无法初始化的bug,导致迁移必须要通过新建控制台程序,而不能在本项目内迁移,再或者code-first和ShardingCore的启动参数冲突导致需要平凡修改，并且不支持分库,之前有小伙伴分了300个库如果自动迁移不能用确实是一件很头疼的事情,虽然这些问题对于分库分表而言其实是小事情，但是如果一旦分表分库到达一定的量级就会难以维护。所以ShardingCore在最近三周内开启了新的版本,新版本主要是解决上述痛点并且将代码更加标准的使用
开发软件一般是先能用，然后好用，最后标准化，ShardingCore也是如此，因为需要扩展efcore所以有时候在不熟悉efcore的扩展方式的时候只能靠静态类来进行注入访问,而静态类其实是一个非常不标准的用法,除非万不得已。那么新版本x.6.x.x ShardingCore带来了什么请往下看
移除静态容器
静态容器的使用导致ShardingCore在整个应用程序声明周期只有一份数据,那么数据都是共享的这个对于后续的测试维护扩展是相当的不利的，没有单例那种隔离性来的好,所以移除了ShardingContainer,通过提供IShardingRuntimeContext来保证和之前的参数结构的访问,同一个DbContext类型在使用不同的IShardingRuntimeContext后可以表现出不同的分表分库特性。
原生efcore
首先我们针对原生efcore进行扩展来达到分库分表+code-first自动迁移开发
添加依赖 ShardingCore 6.6.0.3 MySql
//请安装最新版本目前x.6.0.3+,第一个版本号6代表efcore的版本号 Install-Package ShardingCore -Version 6.6.0.3 Install-Package Pomelo.EntityFrameworkCore.MySql -Version 6.0.1 Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.6
创建一个todo实体
public class TodoItem
{
public string Id { get; set; }
public string Text { get; set; }
}
创建dbcontext
简单的将对象和数据库做了一下映射当然DbSet+Attribute也是可以的
public class MyDbContext:AbstractShardingDbContext,IShardingTableDbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
{
}
public IRouteTail RouteTail { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<TodoItem>(mb =>
{
mb.HasKey(o => o.Id);
mb.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");
mb.Property(o => o.Text).IsRequired().HasMaxLength(256).HasComment("事情");
mb.ToTable(nameof(TodoItem));
});
}
}
新建分库分表路由
分库路由
public class TodoItemDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<TodoItem,string>
{
/// <summary>
/// id的hashcode取模余3分库
/// </summary>
/// <param name="shardingKey"></param>
/// <returns></returns>
/// <exception cref="InvalidOperationException"></exception>
public override string ShardingKeyToDataSourceName(object shardingKey)
{
if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");
var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());
return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2
}
private readonly List<string> _dataSources = new List<string>() { "ds0", "ds1", "ds2" };
public override List<string> GetAllDataSourceNames()
{
return _dataSources;
}
public override bool AddDataSourceName(string dataSourceName)
{
throw new NotImplementedException();
}
/// <summary>
/// id分库
/// </summary>
/// <param name="builder"></param>
public override void Configure(EntityMetadataDataSourceBuilder<TodoItem> builder)
{
builder.ShardingProperty(o => o.Id);
}
public override Func<string, bool> GetRouteToFilter(string shardingKey, ShardingOperatorEnum shardingOperator)
{
var t = ShardingKeyToDataSourceName(shardingKey);
switch (shardingOperator)
{
case ShardingOperatorEnum.Equal: return tail => tail == t;
default:
{
return tail => true;
}
}
}
}
分表路由：
public class TodoItemTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<TodoItem>
{
public TodoItemTableRoute() : base(2, 3)
{
}
/// <summary>
/// 正常情况下不会用内容来做分片键因为作为分片键有个前提就是不会被修改
/// </summary>
/// <param name="builder"></param>
public override void Configure(EntityMetadataTableBuilder<TodoItem> builder)
{
builder.ShardingProperty(o => o.Text);
}
}
新建迁移数据库脚本生成
public class ShardingMySqlMigrationsSqlGenerator:MySqlMigrationsSqlGenerator
{
private readonly IShardingRuntimeContext _shardingRuntimeContext;
public ShardingMySqlMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider annotationProvider, IMySqlOptions options,IShardingRuntimeContext shardingRuntimeContext) : base(dependencies, annotationProvider, options)
{
_shardingRuntimeContext = shardingRuntimeContext;
}
protected override void Generate(
MigrationOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
var oldCmds = builder.GetCommandList().ToList();
base.Generate(operation, model, builder);
var newCmds = builder.GetCommandList().ToList();
var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();
MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);
}
}
配置依赖注入
ILoggerFactory efLogger = LoggerFactory.Create(builder =>
{
builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
});
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddShardingDbContext<MyDbContext>()
.UseRouteConfig(op =>
{
op.AddShardingTableRoute<TodoItemTableRoute>();
op.AddShardingDataSourceRoute<TodoItemDataSourceRoute>();
})
.UseConfig((sp,op) =>
{
op.UseShardingQuery((con, b) =>
{
b.UseMySql(con, new MySqlServerVersion(new Version()))
.UseLoggerFactory(efLogger);
});
op.UseShardingTransaction((con, b) =>
{
b.UseMySql(con, new MySqlServerVersion(new Version()))
.UseLoggerFactory(efLogger);
});
op.AddDefaultDataSource("ds0", "server=127.0.0.1;port=3306;database=mydb0;userid=root;password=root;");
op.AddExtraDataSource(sp=>new Dictionary<string, string>()
{
{"ds1", "server=127.0.0.1;port=3306;database=mydb1;userid=root;password=root;"},
{"ds2", "server=127.0.0.1;port=3306;database=mydb2;userid=root;password=root;"}
});
op.UseShardingMigrationConfigure(b =>
{
b.ReplaceService<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();
});
}).AddShardingCore();
var app = builder.Build();
// Configure the HTTP request pipeline.
//如果有按时间分片的需要加定时任务否则可以不加
app.Services.UseAutoShardingCreate();
using (var scope = app.Services.CreateScope())
{
var defaultShardingDbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();
if (defaultShardingDbContext.Database.GetPendingMigrations().Any())
{
defaultShardingDbContext.Database.Migrate();
}
}
//如果需要在启动后扫描是否有表却扫了可以添加这个
//app.Services.UseAutoTryCompensateTable();
//......
app.Run();
添加迁移文件
Add-Migration Init
启动程序
分表分库自动迁移
crud
添加todo字段并迁移
接下来我们将针对TodoItem添加一个name字段并且新增一张既不分库也不分表的表然后进行迁移
public class TodoItem
{
public string Id { get; set; }
public string Text { get; set; }
public string Name { get; set; }
}
public class TodoTest
{
public string Id { get; set; }
public string Test { get; set; }
}
//docontext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<TodoItem>(mb =>
{
mb.HasKey(o => o.Id);
mb.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");
mb.Property(o => o.Text).IsRequired().HasMaxLength(256).HasComment("事情");
mb.Property(o => o.Name).HasMaxLength(256).HasComment("姓名");
mb.ToTable(nameof(TodoItem));
});
modelBuilder.Entity<TodoTest>(mb =>
{
mb.HasKey(o => o.Id);
mb.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");
mb.Property(o => o.Test).IsRequired().HasMaxLength(256).HasComment("测试");
mb.ToTable(nameof(TodoTest));
});
}
不出意外我们成功了然后再次启动
启动程序后我们惊奇的发现不单原先的表新增了一个name字段，并且为分片未分开的表也被添加进来了
到此为止efcore的原生分库分表+全自动化迁移Code-First已经全部完成,这不仅大大的提高了程序的性能并且大大的方便了开发人员的维护。
集成AbpVNext
完成了efcore原生的分表分库迁移我们将进行abp下的操作
首先我们去github下的abp-samples里面下载对应的demo测试，这边选择todo-mvc
接着我们本地打开安装依赖,只需要安装·ShardingCore· 6.6.0.3。
新建两个接口用于赋值创建时间和guid
因为ShardingCore需要add,update,remove的时候shardingkey不可以为空,你可以自己赋值，但是这样abp的部分特性就不能用了,所以我们做一下兼容
//在TodoApp.Domain.Shared新增两个接口（非必须）
public interface IShardingKeyIsCreationTime
{
}
public interface IShardingKeyIsGuId
{
}
public class TodoItem : BasicAggregateRoot<Guid>,IShardingKeyIsGuId//,IShardingKeyIsCreationTime
{
public string Text { get; set; }
}
//不做时间分片所以不需要提前赋值
public class TodoItem : BasicAggregateRoot<Guid>,IShardingKeyIsGuId//,IShardingKeyIsCreationTime
{
public string Text { get; set; }
}
AbpDbContext抽象类
因为Abp需要继承AbpDbContext所以这边进行一个修改因为ShardingCore只需要接口所以可以满足任何情况
//为了篇幅移除了大部分代码剩下的可以在文末demo处查看
public abstract class AbstractShardingAbpDbContext<TDbContext> : AbpDbContext<TDbContext>, IShardingDbContext, ISupportShardingReadWrite
where TDbContext : DbContext
{
private readonly IShardingDbContextExecutor _shardingDbContextExecutor;
protected AbstractShardingAbpDbContext(DbContextOptions<TDbContext> options) : base(options)
{
var wrapOptionsExtension = options.FindExtension<ShardingWrapOptionsExtension>();
if (wrapOptionsExtension != null)
{
_shardingDbContextExecutor = new ShardingDbContextExecutor(this);
}
}
public DbContext GetDbContext(string dataSourceName, CreateDbContextStrategyEnum strategy, IRouteTail routeTail)
{
var dbContext = _shardingDbContextExecutor.CreateDbContext(strategy, dataSourceName, routeTail);
if (dbContext is AbpDbContext<TDbContext> abpDbContext && abpDbContext.LazyServiceProvider == null)
{
abpDbContext.LazyServiceProvider = this.LazyServiceProvider;
}
return dbContext;
}
}
新增分库分表路由
todoitem id取模分库
public class TodoDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<TodoItem,string>
{
public override string ShardingKeyToDataSourceName(object shardingKey)
{
if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");
var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());
return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2
}
public override List<string> GetAllDataSourceNames()
{
return new List<string>()
{
"ds0", "ds1", "ds2"
};
}
public override bool AddDataSourceName(string dataSourceName)
{
throw new NotImplementedException();
}
public override void Configure(EntityMetadataDataSourceBuilder<TodoItem> builder)
{
builder.ShardingProperty(o => o.Id);
}
public override Func<string, bool> GetRouteToFilter(string shardingKey, ShardingOperatorEnum shardingOperator)
{
var t = ShardingKeyToDataSourceName(shardingKey);
switch (shardingOperator)
{
case ShardingOperatorEnum.Equal: return tail => tail == t;
default:
{
return tail => true;
}
}
}
}
todoitem text 取模分表：
public class TodoTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<TodoItem>
{
public TodoTableRoute() : base(2, 5)
{
}
public override void Configure(EntityMetadataTableBuilder<TodoItem> builder)
{
builder.ShardingProperty(o => o.Text);
}
}
编写sqlserver分片迁移脚本生成
public class ShardingSqlServerMigrationsSqlGenerator: SqlServerMigrationsSqlGenerator
{
private readonly IShardingRuntimeContext _shardingRuntimeContext;
public ShardingSqlServerMigrationsSqlGenerator(IShardingRuntimeContext shardingRuntimeContext,[NotNull] MigrationsSqlGeneratorDependencies dependencies, [NotNull] IRelationalAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations)
{
_shardingRuntimeContext = shardingRuntimeContext;
}
protected override void Generate(
MigrationOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
var oldCmds = builder.GetCommandList().ToList();
base.Generate(operation, model, builder);
var newCmds = builder.GetCommandList().ToList();
var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();
MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);
}
}
abp的efcore模块注入
TodoAppEntityFrameworkCoreModule编写注入
public class TodoAppEntityFrameworkCoreModule : AbpModule
{
public static readonly ILoggerFactory efLogger = LoggerFactory.Create(builder =>
{
builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
});
public override void PreConfigureServices(ServiceConfigurationContext context)
{
TodoAppEfCoreEntityExtensionMappings.Configure();
}
public override void ConfigureServices(ServiceConfigurationContext context)
{
context.Services.AddAbpDbContext<TodoAppDbContext>(options =>
{
/* Remove "includeAllEntities: true" to create
* default repositories only for aggregate roots */
options.AddDefaultRepositories(includeAllEntities: true);
});
Configure<AbpDbContextOptions>(options =>
{
/* The main point to change your DBMS.
* See also TodoAppDbContextFactory for EF Core tooling. */
options.UseSqlServer();
options.Configure<TodoAppDbContext>(innerContext =>
{
ShardingCoreExtension.UseDefaultSharding<TodoAppDbContext>(innerContext.ServiceProvider, innerContext.DbContextOptions);
});
});
context.Services.AddShardingConfigure<TodoAppDbContext>()
.UseRouteConfig(op =>
{
op.AddShardingDataSourceRoute<TodoDataSourceRoute>();
op.AddShardingTableRoute<TodoTableRoute>();
})
.UseConfig((sp, op) =>
{
//var loggerFactory = sp.GetRequiredService<ILoggerFactory>();
op.UseShardingQuery((conStr, builder) =>
{
builder.UseSqlServer(conStr).UseLoggerFactory(efLogger);
});
op.UseShardingTransaction((connection, builder) =>
{
builder.UseSqlServer(connection).UseLoggerFactory(efLogger);
});
op.UseShardingMigrationConfigure(builder =>
{
builder.ReplaceService<IMigrationsSqlGenerator, ShardingSqlServerMigrationsSqlGenerator>();
});
op.AddDefaultDataSource("ds0", "Server=.;Database=TodoApp;Trusted_Connection=True");
op.AddExtraDataSource(sp =>
{
return new Dictionary<string, string>()
{
{ "ds1", "Server=.;Database=TodoApp1;Trusted_Connection=True" },
{ "ds2", "Server=.;Database=TodoApp2;Trusted_Connection=True" }
};
});
})
.AddShardingCore();
}
public override void OnPostApplicationInitialization(ApplicationInitializationContext context)
{
base.OnPostApplicationInitialization(context);
//创建表的定时任务如果有按年月日系统默认路由的需要系统创建的记得开起来
context.ServiceProvider.UseAutoShardingCreate();
//补偿表 //自动迁移的话不需要
//context.ServiceProvider.UseAutoTryCompensateTable();
}
}
启动abp迁移项目
启动：
等待输出：
插入todoitem
查询
验证
到此为止我们这边完成了针对abpvnext的分表分库+自动化迁移的操作
集成Furion
接下来我们开始集成Furion的操作
首先依旧安装依赖
3.7.5+版本直接参考demo相对简单很多
添加依赖 ShardingCore 6.6.0.3 MySql
Install-Package Furion -Version 3.7.5 //请安装最新版本目前x.6.0.5+,第一个版本号6代表efcore的版本号 Install-Package ShardingCore -Version 6.6.0.5 Install-Package Pomelo.EntityFrameworkCore.MySql -Version 6.0.1 Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.6
新增todoitem
public class TodoItem:IEntity, IEntityTypeBuilder<TodoItem>
{
public string Id { get; set; }
public string Text { get; set; }
public void Configure(EntityTypeBuilder<TodoItem> entityBuilder, DbContext dbContext, Type dbContextLocator)
{
entityBuilder.HasKey(o => o.Id);
entityBuilder.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");
entityBuilder.Property(o => o.Text).IsRequired().HasMaxLength(256).HasComment("事情");
entityBuilder.ToTable(nameof(TodoItem));
}
}
新增带分片的DbContext和Abp一样
抽象对象直接看远吗,这边直接新增一个dbcontext
public class MyDbContext : AppShardingDbContext<MyDbContext>,IShardingTableDbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
{
}
public IRouteTail RouteTail { get; set; }
}
新增分表分库路由
新增分库路由：
public class TodoItemDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<TodoItem,string>
{
/// <summary>
/// id的hashcode取模余3分库
/// </summary>
/// <param name="shardingKey"></param>
/// <returns></returns>
/// <exception cref="InvalidOperationException"></exception>
public override string ShardingKeyToDataSourceName(object shardingKey)
{
if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");
var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());
return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2
}
private readonly List<string> _dataSources = new List<string>() { "ds0", "ds1", "ds2" };
public override List<string> GetAllDataSourceNames()
{
return _dataSources;
}
public override bool AddDataSourceName(string dataSourceName)
{
throw new NotImplementedException();
}
/// <summary>
/// id分库
/// </summary>
/// <param name="builder"></param>
public override void Configure(EntityMetadataDataSourceBuilder<TodoItem> builder)
{
builder.ShardingProperty(o => o.Id);
}
public override Func<string, bool> GetRouteToFilter(string shardingKey, ShardingOperatorEnum shardingOperator)
{
var t = ShardingKeyToDataSourceName(shardingKey);
switch (shardingOperator)
{
case ShardingOperatorEnum.Equal: return tail => tail == t;
default:
{
return tail => true;
}
}
}
}
新增分表路由
public class TodoItemTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<TodoItem>
{
public TodoItemTableRoute() : base(2, 3)
{
}
/// <summary>
/// 正常情况下不会用内容来做分片键因为作为分片键有个前提就是不会被修改
/// </summary>
/// <param name="builder"></param>
public override void Configure(EntityMetadataTableBuilder<TodoItem> builder)
{
builder.ShardingProperty(o => o.Text);
}
}
编写迁移文件
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure.Internal;
using Pomelo.EntityFrameworkCore.MySql.Migrations;
using ShardingCore.Core.RuntimeContexts;
using ShardingCore.Helpers;
namespace TodoApp;
public class ShardingMySqlMigrationsSqlGenerator:MySqlMigrationsSqlGenerator
{
private readonly IShardingRuntimeContext _shardingRuntimeContext;
public ShardingMySqlMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider annotationProvider, IMySqlOptions options,IShardingRuntimeContext shardingRuntimeContext) : base(dependencies, annotationProvider, options)
{
_shardingRuntimeContext = shardingRuntimeContext;
}
protected override void Generate(
MigrationOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
var oldCmds = builder.GetCommandList().ToList();
base.Generate(operation, model, builder);
var newCmds = builder.GetCommandList().ToList();
var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();
MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);
}
}
启动注入
这边简单看了一下furion貌似没有提供Func<IServiceProvider,DbContextOptionBuilder>的efcore注入方式所以这边不得已采用静态方式,
如果采用静态的方式需要实现一个接口IDbContextCreator
//静态创建IShardingRuntimeContext
public class ShardingCoreProvider
{
private static ILoggerFactory efLogger = LoggerFactory.Create(builder =>
{
builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
});
private static readonly IShardingRuntimeContext instance;
public static IShardingRuntimeContext ShardingRuntimeContext => instance;
static ShardingCoreProvider()
{
instance=new ShardingRuntimeBuilder<MyDbContext>().UseRouteConfig(op =>
{
op.AddShardingTableRoute<TodoItemTableRoute>();
op.AddShardingDataSourceRoute<TodoItemDataSourceRoute>();
})
.UseConfig((sp,op) =>
{
op.UseShardingQuery((con, b) =>
{
b.UseMySql(con, new MySqlServerVersion(new Version()))
.UseLoggerFactory(efLogger);
});
op.UseShardingTransaction((con, b) =>
{
b.UseMySql(con, new MySqlServerVersion(new Version()))
.UseLoggerFactory(efLogger);
});
op.AddDefaultDataSource("ds0", "server=127.0.0.1;port=3306;database=furion0;userid=root;password=root;");
op.AddExtraDataSource(sp=>new Dictionary<string, string>()
{
{"ds1", "server=127.0.0.1;port=3306;database=furion1;userid=root;password=root;"},
{"ds2", "server=127.0.0.1;port=3306;database=furion2;userid=root;password=root;"}
});
op.UseShardingMigrationConfigure(b =>
{
b.ReplaceService<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();
});
}).ReplaceService<IDbContextCreator, CustomerDbContextCreator>(ServiceLifetime.Singleton).Build();
}
}
//启动服务
public class ShardingCoreComponent:IServiceComponent
{
public void Load(IServiceCollection services, ComponentContext componentContext)
{
services.AddControllers();
services.AddEndpointsApiExplorer();
services.AddSwaggerGen();
services.AddDatabaseAccessor(options =>
{
// 配置默认数据库
options.AddDb<MyDbContext>(o =>
{
o.UseDefaultSharding<MyDbContext>(ShardingCoreProvider.ShardingRuntimeContext);
});
});
//依赖注入
services.AddSingleton<IShardingRuntimeContext>(sp => ShardingCoreProvider.ShardingRuntimeContext);
}
}
public class CustomerDbContextCreator:ActivatorDbContextCreator<MyDbContext>
{
public override DbContext GetShellDbContext(IShardingProvider shardingProvider)
{
var dbContextOptionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
dbContextOptionsBuilder.UseDefaultSharding<MyDbContext>(ShardingCoreProvider.ShardingRuntimeContext);
return new MyDbContext(dbContextOptionsBuilder.Options);
}
}
public class UseShardingCoreComponent:IApplicationComponent
{
public void Load(IApplicationBuilder app, IWebHostEnvironment env, ComponentContext componentContext)
{
//......
app.ApplicationServices.UseAutoShardingCreate();
var serviceProvider = app.ApplicationServices;
using (var scope = app.ApplicationServices.CreateScope())
{
var defaultShardingDbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();
if (defaultShardingDbContext.Database.GetPendingMigrations().Any())
{
defaultShardingDbContext.Database.Migrate();
}
}
// app.Services.UseAutoTryCompensateTable();
}
}
//Program
using TodoApp;
Serve.Run(RunOptions.Default
.AddComponent<ShardingCoreComponent>()
.UseComponent<UseShardingCoreComponent>());
添加迁移文件
启动：
增删改查：
集成WTM
之前也有一次继承过之后也有因为迁移过于麻烦所以这边ShardingCore出了更加完善迁移方案并且使用起来code-first更加无感
添加依赖
添加依赖 ShardingCore 6.6.0.3 MySql
//请安装最新版本目前x.6.0.5+,第一个版本号6代表efcore的版本号 Install-Package ShardingCore -Version 6.6.0.5 Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.6
新增分表分库路由
//分库路由
public class TodoDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<Todo,string>
{
/// <summary>
/// id的hashcode取模余3分库
/// </summary>
/// <param name="shardingKey"></param>
/// <returns></returns>
/// <exception cref="InvalidOperationException"></exception>
public override string ShardingKeyToDataSourceName(object shardingKey)
{
if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");
var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());
return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2
}
private readonly List<string> _dataSources = new List<string>() { "ds0", "ds1", "ds2" };
public override List<string> GetAllDataSourceNames()
{
return _dataSources;
}
public override bool AddDataSourceName(string dataSourceName)
{
throw new NotImplementedException();
}
/// <summary>
/// id分库
/// </summary>
/// <param name="builder"></param>
public override void Configure(EntityMetadataDataSourceBuilder<Todo> builder)
{
builder.ShardingProperty(o => o.Id);
}
public override Func<string, bool> GetRouteToFilter(string shardingKey, ShardingOperatorEnum shardingOperator)
{
var t = ShardingKeyToDataSourceName(shardingKey);
switch (shardingOperator)
{
case ShardingOperatorEnum.Equal: return tail => tail == t;
default:
{
return tail => true;
}
}
}
}
//分表路由
public class TodoTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<Todo>
{
public TodoTableRoute() : base(2, 3)
{
}
/// <summary>
/// 正常情况下不会用内容来做分片键因为作为分片键有个前提就是不会被修改
/// </summary>
/// <param name="builder"></param>
public override void Configure(EntityMetadataTableBuilder<Todo> builder)
{
builder.ShardingProperty(o => o.Name);
}
}
创建DbContextCreator
public class WTMDbContextCreator:IDbContextCreator
{
public DbContext CreateDbContext(DbContext shellDbContext, ShardingDbContextOptions shardingDbContextOptions)
{
var context = new DataContext((DbContextOptions<DataContext>)shardingDbContextOptions.DbContextOptions);
context.RouteTail = shardingDbContextOptions.RouteTail;
return context;
}
public DbContext GetShellDbContext(IShardingProvider shardingProvider)
{
var dbContextOptionsBuilder = new DbContextOptionsBuilder<DataContext>();
dbContextOptionsBuilder.UseDefaultSharding<DataContext>(ShardingCoreProvider.ShardingRuntimeContext);
return new DataContext(dbContextOptionsBuilder.Options);
}
}
迁移脚本
public class ShardingMySqlMigrationsSqlGenerator:MySqlMigrationsSqlGenerator
{
private readonly IShardingRuntimeContext _shardingRuntimeContext;
public ShardingMySqlMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider annotationProvider, IMySqlOptions options,IShardingRuntimeContext shardingRuntimeContext) : base(dependencies, annotationProvider, options)
{
_shardingRuntimeContext = shardingRuntimeContext;
}
protected override void Generate(
MigrationOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
var oldCmds = builder.GetCommandList().ToList();
base.Generate(operation, model, builder);
var newCmds = builder.GetCommandList().ToList();
var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();
MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);
}
}
静态构造IShardingRuntimeContext
因为WTM在创建dbcontext并不是通过依赖注入创建的而是由其余的内部实现所以为了兼容我们这边只能通过静态IShardingRuntimeContext注入
public class ShardingCoreProvider
{
private static ILoggerFactory efLogger = LoggerFactory.Create(builder =>
{
builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
});
private static readonly IShardingRuntimeContext instance;
public static IShardingRuntimeContext ShardingRuntimeContext => instance;
static ShardingCoreProvider()
{
instance=new ShardingRuntimeBuilder<DataContext>().UseRouteConfig(op =>
{
op.AddShardingTableRoute<TodoRoute>();
op.AddShardingDataSourceRoute<TodoDataSourceRoute>();
})
.UseConfig((sp,op) =>
{
op.UseShardingQuery((con, b) =>
{
b.UseMySql(con, new MySqlServerVersion(new Version()))
.UseLoggerFactory(efLogger);
});
op.UseShardingTransaction((con, b) =>
{
b.UseMySql(con, new MySqlServerVersion(new Version()))
.UseLoggerFactory(efLogger);
});
op.AddDefaultDataSource("ds0", "server=127.0.0.1;port=3306;database=wtm0;userid=root;password=root;");
op.AddExtraDataSource(sp=>new Dictionary<string, string>()
{
{"ds1", "server=127.0.0.1;port=3306;database=wtm1;userid=root;password=root;"},
{"ds2", "server=127.0.0.1;port=3306;database=wtm2;userid=root;password=root;"}
});
op.UseShardingMigrationConfigure(b =>
{
b.ReplaceService<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();
});
}).ReplaceService<IDbContextCreator, WTMDbContextCreator>(ServiceLifetime.Singleton).Build();
}
}
创建抽象分片DbContext
因为过于长所以这边只显示主要部分其余通过demo查看
public abstract class AbstractShardingFrameworkContext:FrameworkContext, IShardingDbContext, ISupportShardingReadWrite
{
protected IShardingDbContextExecutor ShardingDbContextExecutor
{
get;
}
public AbstractShardingFrameworkContext(CS cs)
: base(cs)
{
ShardingDbContextExecutor =new ShardingDbContextExecutor(this);
IsExecutor = false;
}
public AbstractShardingFrameworkContext(string cs, DBTypeEnum dbtype)
: base(cs, dbtype)
{
ShardingDbContextExecutor =new ShardingDbContextExecutor(this);
IsExecutor = false;
}
public AbstractShardingFrameworkContext(string cs, DBTypeEnum dbtype, string version = null)
: base(cs, dbtype, version)
{
ShardingDbContextExecutor =new ShardingDbContextExecutor(this);
IsExecutor = false;
}
public AbstractShardingFrameworkContext(DbContextOptions options) : base(options)
{
var wrapOptionsExtension = options.FindExtension<ShardingWrapOptionsExtension>();
if (wrapOptionsExtension != null)
{
ShardingDbContextExecutor =new ShardingDbContextExecutor(this);;
}
IsExecutor = wrapOptionsExtension == null;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (this.CSName!=null)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseDefaultSharding<DataContext>(ShardingCoreProvider.ShardingRuntimeContext);
}
}
public DbContext GetDbContext(string dataSourceName, CreateDbContextStrategyEnum strategy, IRouteTail routeTail)
{
return ShardingDbContextExecutor.CreateDbContext(strategy, dataSourceName, routeTail);
}
}
修改dbcontext
public class DataContextFactory : IDesignTimeDbContextFactory<DataContext>
{
public DataContext CreateDbContext(string[] args)
{
var virtualDataSource = ShardingCoreProvider.ShardingRuntimeContext.GetVirtualDataSource();
var defaultConnectionString = virtualDataSource.DefaultConnectionString;
return new DataContext(defaultConnectionString, DBTypeEnum.MySql);
}
}
注入ShardingCore
移除掉了之前的多余代码
public void ConfigureServices(IServiceCollection services){
//....
services.AddSingleton<IShardingRuntimeContext>(sp => ShardingCoreProvider.ShardingRuntimeContext);
}
public void Configure(IApplicationBuilder app, IOptionsMonitor<Configs> configs)
{
IconFontsHelper.GenerateIconFont();
// using (var scope = app.ApplicationServices.CreateScope())
// {
// var requiredService = scope.ServiceProvider.GetRequiredService<WTMContext>();
// var requiredServiceDc = requiredService.DC;
// }
//定时任务
app.ApplicationServices.UseAutoShardingCreate();
using (var dbconContext=new DataContextFactory().CreateDbContext(new string[0]))
{
dbconContext.Database.Migrate();
}
//补齐表防止iis之类的休眠导致按天按月的表没有新建
//app.ApplicationServices.UseAutoTryCompensateTable();
//....
}
迁移：
启动程序：
crud：
到此这篇关于.Net极限生产力之分表分库全自动化Migrations Code-First的文章就介绍到这了,更多相关.Net自动化Migrations Code-First内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
赞 (0)
相关推荐
-
.Net使用分表分库框架ShardingCore实现多字段分片
目录 介绍 项目地址 背景 原理 直接开始 添加依赖 创建一个订单对象 创建DbContext 创建分片路由 ShardingCore启动配置 测试 默认配置下的测试 测试无路由返回默认值 总结 介绍 本期主角:ShardingCore 一款ef-core下高性能.轻量级针对分表分库读写分离的解决方案,具有零依赖.零学习成本.零业务代码入侵 dotnet下唯一一款全自动分表,多字段分表框架,拥有高性能,零依赖.零学习成本.零业务代码入侵,并且支持读写分离动态分表分库,同一种路由可以完全自定义的新
-
CodeFirst从零开始搭建Asp.Net Core2.0网站
一步步教大家如何搭建Asp.Net Core2.0网站,以下所有都是建立在.NETCore2.0环境已经搭建好 右键解决方案>新建项目> 选择Web>ASP.NETCoreWeb应用程序(.NET Core) 选择Web应用程序,暂时不选择启用Docker,身份验证选择个人用户账户(会自动生成一系列和用户认证的代码) 随后生代码层次目录如下: 其中会包含身份信息的相关实现,比如相关实体信息(user)之类的,如果想对扩展微软自动的生成的用户实体类,可在Models中的Applicatio
-
asp.net mvc CodeFirst模式数据库迁移步骤详解
利用Code First模式构建好基本的类后,项目也开始搭建完毕并成功运行,而且已经将数据库表结构自动生成了. 但是,我有新的类要加入,有字段需要修改,那怎么办呢,删库,跑路 ? 哈哈 利用数据库迁移,将原有结构不改动,将新建类进行单独建表操作,或者是已有数据库表,改变字段,那就修改表. 迁移步骤: 1.打开程序包管理器控制台:工具->NuGet包管理器->程序包管理器控制台.(当然还有其它方式也可以打开,我比较喜欢这种) 点击后将弹出程序包管理器控制台 极其要注意的是默认项目!!! 2.启动
-
详解在.net core中完美解决多租户分库分表的问题
前几天有人想做一个多租户的平台,每个租户一个库,可以进行水平扩展,应用端根据登录信息,切换到不同的租户库 计划用ef core实现,他们说做不出来,需要动态创建dbContext,不好实现 然而这个使用CRL很轻松就能解决了 以下为演示数据库,有两个库testdb和testdb2,查询结果如下 目标: 根据传入登录信息连不不同的库,查询返回结果,如登录人为01,返回d1.default,登录人为02 返回 d2.default 实际上这个需求就是分库分表的实现,通过设置数据库/表映射关系,根据传
-
.NET Core实现分表分库、读写分离的通用 Repository功能
首先声明这篇文章不是标题党,我说的这个类库是 FreeSql.Repository,它作为扩展库现实了通用仓储层功能,接口规范参考 abp vnext 定义,实现了基础的仓储层(CURD). 安装 dotnet add package FreeSql.Repository 可用于:.net framework 4.6+..net core 2.1+ 定义 var fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.Da
-
.Net极限生产力之分表分库全自动化Migrations Code-First
目录 开始 移除静态容器 原生efcore 启动程序 添加todo字段并迁移 集成AbpVNext 新建两个接口用于赋值创建时间和guid AbpDbContext抽象类 新增分库分表路由 编写sqlserver分片迁移脚本生成 abp的efcore模块注入 启动abp迁移项目 集成Furion 新增todoitem 新增分表分库路由 新增分表路由 编写迁移文件 启动注入 添加迁移文件 集成WTM 添加依赖 新增分表分库路由 创建DbContextCreator 静态构造IShardingRun
-
超大数据量存储常用数据库分表分库算法总结
当一个应用的数据量大的时候,我们用单表和单库来存储会严重影响操作速度,如mysql的myisam存储,我们经过测试,200w以下的时候,mysql的访问速度都很快,但是如果超过200w以上的数据,他的访问速度会急剧下降,影响到我们webapp的访问速度,而且数据量太大的话,如果用单表存储,就会使得系统相当的不稳定,mysql服务很容易挂掉.所以当数据量超过200w的时候,建议系统工程师还是考虑分表. 以下是几种常见的分表算法. 1.按自然时间来分表/分库; 如一个应用的数据在一年后数据量会达到2
-
MySQL数据库优化之分表分库操作实例详解
本文实例讲述了MySQL数据库优化之分表分库操作.分享给大家供大家参考,具体如下: 分表分库 垂直拆分 垂直拆分就是要把表按模块划分到不同数据库表中(当然原则还是不破坏第三范式),这种拆分在大型网站的演变过程中是很常见的.当一个网站还在很小的时候,只有小量的人来开发和维护,各模块和表都在一起,当网站不断丰富和壮大的时候,也会变成多个子系统来支撑,这时就有按模块和功能把表划分出来的需求.其实,相对于垂直切分更进一步的是服务化改造,说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务,通过服务间的
-
MySQL 分表分库怎么进行数据切分
关系型数据库本身比较容易成为系统瓶颈,单机存储容量.连接数.处理能力都有限.当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从库.优化索引,做很多操作时性能仍下降严重.此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间. 数据库分布式核心内容无非就是数据切分(Sharding)以及切分后对数据的定位.整合.数据切分就是将数据分散存储到多个数据库中,使得单一数据库中的数据量变小,通过扩充主机的数量缓解单一数据库的性能问题,从而达到提升数据库操作性能的目
-
使用ShardingSphere-Proxy实现分表分库
目录 1.环境准备 2.数据库脚本准备 3.配置ShardingSphere-Proxy 分表原理解析 参考:Sharding-Proxy的基本功能使用 1. 环境准备 MySql 5.7 apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz jdk 1.8 mysql-connector-java-5.1.49.jar 2. 数据库脚本准备 # 创建商品数据库 CREATE DATABASE IF NOT EXISTS `products`
-
mysql数据库分表分库的策略
一.先说一下为什么要分表: 当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了.分表的目的就在于此,减小数据库的负担,缩短查询时间.日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表.这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕.分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率.数据库中的数据量不一定是可控的,在未进行分
-
mysql分表分库的应用场景和设计方式
很多朋友在论坛和留言区域问mysql在什么情况下才需要进行分库分表,以及采用何种设计方式才是最优的选择,根据这些问题,小编为大家整理了关于MySQL分库分表的应用场景和最优的设计方式举例. 一. 分表 场景:对于大型的互联网应用来说,数据库单表的记录行数可能达到千万级甚至是亿级,并且数据库面临着极高的并发访问.采用Master-Slave复制模式的MySQL架构, 只能够对数据库的读进行扩展,而对数据库的写入操作还是集中在Master上,并且单个Master挂载的Slave也不可能无限制多,Sl
-
SpringBoot整合sharding-jdbc实现自定义分库分表的实践
目录 一.前言 二.简介 1.分片键 2.分片算法 三.程序实现 一.前言 SpringBoot整合sharding-jdbc实现分库分表与读写分离 本文将通过自定义算法来实现定制化的分库分表来扩展相应业务 二.简介 1.分片键 用于数据库/表拆分的关键字段 ex: 用户表根据user_id取模拆分到不同的数据库中 2.分片算法 可参考:https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere