本次我们的主题就是极限生产力,其他语言望尘莫及的分表分库全自动化Migrations Code-First 加 efcore 分表分库无感开发
还记得上次发布博客还是在上次,上次发布了如何兼容WTM框架后也有不少小伙伴来问我如何兼容如何迁移等问题,经过这么多框架的兼容我自己也认识到了一些问题,譬如在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进行扩展来达到分库分表+code-first自动迁移开发
//请安装最新版本目前x.6.0.3+,第一个版本号6代表efcore的版本号Install-Package ShardingCore -Version 6.6.0.3Install-Package Pomelo.EntityFrameworkCore.MySql -Version 6.0.1Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.6public class TodoItem{ public string Id { get; set; } public string Text { get; set; }}简单的将对象和数据库做了一下映射当然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
分表分库自动迁移
接下来我们将针对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已经全部完成,这不仅大大的提高了程序的性能并且大大的方便了开发人员的维护。
完成了efcore原生的分表分库迁移我们将进行abp下的操作
首先我们去github下的abp-samples里面下载对应的demo测试，这边选择todo-mvc
接着我们本地打开安装依赖,只需要安装·ShardingCore· 6.6.0.3。
因为ShardingCore需要add,update,remove的时候shardingkey不可以为空,你可以自己赋值，但是这样efcore部分特性就不能用了,所以我们做一下兼容
//在TodoApp.Domain.Shared新增两个接口（非必须） public interface IShardingKeyIsCreationTime { } public class IShardingKeyIsGuId { }因为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); } } 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); } }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(); } }启动
等待输出
查询
验证
到此为止我们这边完成了针对abpvnext的分表分库+自动化迁移的操作
接下来我们开始集成Furion的操作
首先依旧安装依赖
Install-Package Furion -Version 3.7.5//请安装最新版本目前x.6.0.5+,第一个版本号6代表efcore的版本号Install-Package ShardingCore -Version 6.6.0.5Install-Package Pomelo.EntityFrameworkCore.MySql -Version 6.0.1Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.6public 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
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
//静态创建IShardingRuntimeContextpublic 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(); }}//Programusing TodoApp;Serve.Run(RunOptions.Default .AddComponent<ShardingCoreComponent>() .UseComponent<UseShardingCoreComponent>());
增删改查
之前也有一次继承过之后也有因为迁移过于麻烦所以这边ShardingCore出了更加完善迁移方案并且使用起来code-first更加无感
//请安装最新版本目前x.6.0.5+,第一个版本号6代表efcore的版本号Install-Package ShardingCore -Version 6.6.0.5Install-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); }}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); }}因为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(); }}因为过于长所以这边只显示主要部分其余通过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); }} 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); } }移除掉了之前的多余代码
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(); //.... }
(ShardingWithFrameWork)[https://github.com/xuejmnet/ShardingWithFramework] https://github.com/xuejmnet/ShardingWithFramework
您都看到这边了确定不点个star或者赞吗,一款.Net不得不学的分库分表解决方案,简单理解为sharding-jdbc在.net中的实现并且支持更多特性和更优秀的数据聚合,拥有原生性能的97%,并且无业务侵入性,支持未分片的所有efcore原生查询