前言
您是否有以下场景：
- 多租户系统，数据库级别隔离
- 大数据量，需要分表分库（动态添加），分库分表全自动维护处理
- 租户之前可能需要使用不同的数据库模式，譬如有些租户要求用oracle，或者mmsql，或者mysql或者pgsql
- 多租户系统在不同的数据库环境下需要维护的表结构复杂繁琐，需要维护许多脚本
- 业务代码需要进行大范围的妥协来适应上述支持
- 系统需要支持读写分离（动态添加）
- 无需停机状态实时添加租户（租户线上签约）
当然我是一开始想先写这篇文章，但是写着写着发现有些时候这个问题就来了，譬如多数据库下efcore默认不支持迁移，经过不断地努力，大脑的思维宫殿我下意识就发现了解决方案，最终用一天时间解决了就是前面的一篇文章 EFCore高级Saas系统下单DbContext如何支持不同数据库的迁移 那么我们话不多说马上开始
接下来我们将实现A,B,C三个租户,其中A租户我们使用MSSQL的订单表使用按月分表,B租户我们使用MYSQL的订单表我们采用Id取模分表,C租户我们使用MSSQL也是使用订单按月分表但是起始时间和A不一样
管理租户数据
首先我们新建一个DbContext用来管理我们的租户信息
租户用户表
首先我们新建一张租户登录的用户表,每个用户就是我们对外的租户
public class SysUser { public string Id { get; set; } public string Name { get; set; } public string Password { get; set; } public DateTime CreationTime { get; set; } public bool IsDeleted { get; set; } }
租户配置表
然后我们新建一张租户的配置信息表用来后续初始化配置
public class SysUserTenantConfig { public string Id { get; set; } public string UserId { get; set; } /// /// 添加ShardingTenantOptions的Json包 /// public string ConfigJson { get; set; } public DateTime CreationTime { get; set; } public bool IsDeleted { get; set; } }
定义租户配置
//为了满足上述需求我们需要对数据库和订单分片方式进行区分 public class ShardingTenantOptions { /// /// 默认数据源名称 /// public string DefaultDataSourceName { get; set;} /// /// 默认数据库地址 /// public string DefaultConnectionString { get; set; } /// /// 数据库类型 /// public DbTypeEnum DbType { get; set; } /// /// 分片模式 取模还是按月 /// public OrderShardingTypeEnum OrderShardingType { get; set; } /// /// 按月分片其实时间 /// public DateTime BeginTimeForSharding { get; set; } /// /// 分片迁移的命名空间 /// public string MigrationNamespace { get; set; } } public enum DbTypeEnum { MSSQL = 1, MYSQL = 2 } public enum OrderShardingTypeEnum { Mod=1, ByMonth=2 }
租户持久化DbContext
新建一个dbcontext用来存储我们的租户信息，当然你也可以使用文件或者redis之类的都行
public class IdentityDbContext:DbContext { public IdentityDbContext(DbContextOptions options):base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.ApplyConfiguration(new SysUserMap()); modelBuilder.ApplyConfiguration(new SysUserTenantConfigMap()); } }
这样我们就完成了租户信息的存储
租户管理者
我们拥有了租户信息持久化的数据后需要对租户信息的使用进行配置
首先我们新建一个接口可以用来管理租户信息
public interface ITenantManager { /// /// 获取所有的租户 /// /// List GetAll(); /// /// 获取当前租户 /// /// TenantContext GetCurrentTenantContext(); /// /// 添加租户信息 /// /// /// /// bool AddTenantSharding(string tenantId, IShardingRuntimeContext shardingRuntimeContext); /// /// 创建租户环境 /// /// /// TenantScope CreateScope(string tenantId); } //租户的默认管理实现 public class DefaultTenantManager:ITenantManager { private readonly ITenantContextAccessor _tenantContextAccessor; private readonly ConcurrentDictionary _cache = new(); public DefaultTenantManager(ITenantContextAccessor tenantContextAccessor) { _tenantContextAccessor = tenantContextAccessor; } public List GetAll() { return _cache.Keys.ToList(); } public TenantContext GetCurrentTenantContext() { return _tenantContextAccessor.TenantContext; } public bool AddTenantSharding(string tenantId, IShardingRuntimeContext shardingRuntimeContext) { return _cache.TryAdd(tenantId, shardingRuntimeContext); } public TenantScope CreateScope(string tenantId) { if (!_cache.TryGetValue(tenantId, out var shardingRuntimeContext)) { throw new InvalidOperationException(“未找到对应租户的配置”); } _tenantContextAccessor.TenantContext = new TenantContext(shardingRuntimeContext); return new TenantScope(_tenantContextAccessor); } } //当前租户上下文访问者 public interface ITenantContextAccessor { TenantContext? TenantContext { get; set; } } //当前租户上下文访问者实现 public class TenantContextAccessor:ITenantContextAccessor { private static readonly AsyncLocal _tenantContext = new AsyncLocal(); public TenantContext? TenantContext { get => _tenantContext.Value; set => _tenantContext.Value = value; } } //租户上下文 public class TenantContext { private readonly IShardingRuntimeContext _shardingRuntimeContext; public TenantContext(IShardingRuntimeContext shardingRuntimeContext) { _shardingRuntimeContext = shardingRuntimeContext; } public IShardingRuntimeContext GetShardingRuntimeContext() { return _shardingRuntimeContext; } } //用来切换实现当前操作租户环境 public class TenantScope:IDisposable { public TenantScope(ITenantContextAccessor tenantContextAccessor) { TenantContextAccessor = tenantContextAccessor; } public ITenantContextAccessor TenantContextAccessor { get; } public void Dispose() { } }折叠
构思ShardingCore如何不通过依赖注入使用
其实ShardingCore可以默认不在依赖注入中进行依赖注入，首先我们看下普通情况下ShardingCore如何实现非依赖注入获取分片上下文
var shardingRuntimeContext = new ShardingRuntimeBuilder() .UseRouteConfig(o => { o.AddShardingTableRoute(); }).UseConfig(o => { o.ThrowIfQueryRouteNotMatch = false; o.UseShardingQuery((conStr, builder) => { builder.UseMySql(conStr, new MySqlServerVersion(new Version())) .UseLoggerFactory(efLogger) .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking); }); o.UseShardingTransaction((connection, builder) => { builder .UseMySql(connection, new MySqlServerVersion(new Version())) .UseLoggerFactory(efLogger) .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking); }); o.AddDefaultDataSource(“ds0”, “server=127.0.0.1;port=3306;database=dbdbd0;userid=root;password=root;”); o.UseShardingMigrationConfigure(b => { b.ReplaceService(); }); }).ReplaceService(ServiceLifetime.Singleton) .Build();
这样我们就获得了IShardingRuntimeContext,将不同的IShardingRuntimeContext放到不同的数据库中我们就可以实现不同的租户了
订单表
public class Order { public string Id { get; set; } public string Name { get; set; } public DateTime CreationTime { get; set; } public bool IsDeleted { get; set; } }
租户DbContext
public class TenantDbContext:AbstractShardingDbContext,IShardingTableDbContext { public TenantDbContext(DbContextOptions options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.ApplyConfiguration(new OrderMap()); } public IRouteTail RouteTail { get; set; } }
创建订单路由
订单按月分片路由
注意这边我们简单的通过采用一个静态字段来实现
public class OrderMonthTableRoute:AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute { private readonly ShardingTenantOptions _shardingTenantOptions; public OrderMonthTableRoute(ShardingTenantOptions shardingTenantOptions) { _shardingTenantOptions = shardingTenantOptions; } public override void Configure(EntityMetadataTableBuilder builder) { builder.ShardingProperty(o => o.CreationTime); } public override bool AutoCreateTableByTime() { return true; } public override DateTime GetBeginTime() { return _shardingTenantOptions.BeginTimeForSharding; } }
订单取模分片路由
public class OrderModTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute{ private readonly ShardingTenantOptions _shardingTenantOptions; public OrderModTableRoute(ShardingTenantOptions shardingTenantOptions) : base(2, 5) { _shardingTenantOptions = shardingTenantOptions; } public override void Configure(EntityMetadataTableBuilder builder) { builder.ShardingProperty(o => o.Id); }}
实现多数据库的code-first迁移
具体参考之前的博客EFCore高级Saas系统下单DbContext如何支持不同数据库的迁移
https://www.cnblogs.com/xuejiaming/p/16510482.html
分片创建者
public interface IShardingBuilder{ IShardingRuntimeContext Build(ShardingTenantOptions tenantOptions);}public class DefaultShardingBuilder:IShardingBuilder{ public static readonly ILoggerFactory efLogger = LoggerFactory.Create(builder => { builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole(); }); private readonly IServiceProvider _serviceProvider; public DefaultShardingBuilder(IServiceProvider serviceProvider) { _serviceProvider = serviceProvider; } public IShardingRuntimeContext Build(ShardingTenantOptions tenantOptions) { var shardingRuntimeBuilder = new ShardingRuntimeBuilder() .UseRouteConfig(o => { if (tenantOptions.OrderShardingType == OrderShardingTypeEnum.Mod) { o.AddShardingTableRoute(); } if (tenantOptions.OrderShardingType == OrderShardingTypeEnum.ByMonth) { o.AddShardingTableRoute(); } }).UseConfig(o => { o.ThrowIfQueryRouteNotMatch = false; o.UseShardingQuery((conStr, builder) => { if (tenantOptions.DbType == DbTypeEnum.MYSQL) { builder.UseMySql(conStr, new MySqlServerVersion(new Version())) .UseMigrationNamespace(new MySqlMigrationNamespace()); } if (tenantOptions.DbType == DbTypeEnum.MSSQL) { builder.UseSqlServer(conStr) .UseMigrationNamespace(new SqlServerMigrationNamespace()); } builder.UseLoggerFactory(efLogger) .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking) .ReplaceService(); }); o.UseShardingTransaction((connection, builder) => { if (tenantOptions.DbType == DbTypeEnum.MYSQL) { builder .UseMySql(connection, new MySqlServerVersion(new Version())); //.UseMigrationNamespace(new MySqlMigrationNamespace());//迁移只会用connection string创建所以可以不加 } if (tenantOptions.DbType == DbTypeEnum.MSSQL) { builder.UseSqlServer(connection); //.UseMigrationNamespace(new SqlServerMigrationNamespace()); } builder.UseLoggerFactory(efLogger) .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking); }); o.AddDefaultDataSource(tenantOptions.DefaultDataSourceName,tenantOptions.DefaultConnectionString); //注意这个迁移必须要十分重要 //注意这个迁移必须要十分重要 //注意这个迁移必须要十分重要 //注意这个迁移必须要十分重要 o.UseShardingMigrationConfigure(b => { if (tenantOptions.DbType == DbTypeEnum.MYSQL) { b.ReplaceService(); } if (tenantOptions.DbType == DbTypeEnum.MSSQL) { b.ReplaceService(); } }); }).AddServiceConfigure(s => { //IShardingRuntimeContext内部的依赖注入 s.AddSingleton(tenantOptions); }); if (tenantOptions.DbType == DbTypeEnum.MYSQL) { shardingRuntimeBuilder.ReplaceService(ServiceLifetime .Singleton); } if (tenantOptions.DbType == DbTypeEnum.MSSQL) { shardingRuntimeBuilder.ReplaceService(ServiceLifetime .Singleton); } return shardingRuntimeBuilder.Build(_serviceProvider); }}折叠
到此为止基本上我们已经完成了多租户的大部分配置了，jwt部分就不在这边赘述了因为之前有实现过
Startup
主要关键的启动点我们应该怎么配置呢
启动初始化租户
首先我们需要针对程序启动后进行租户的初始化操作
public static class TenantExtension { public static void InitTenant(this IServiceProvider serviceProvider) { var tenantManager = serviceProvider.GetRequiredService(); var shardingBuilder = serviceProvider.GetRequiredService(); using (var scope = serviceProvider.CreateScope()) { var identityDbContext = scope.ServiceProvider.GetRequiredService(); identityDbContext.Database.Migrate(); var sysUserTenantConfigs = identityDbContext.Set().ToList(); if (sysUserTenantConfigs.Any()) { foreach (var sysUserTenantConfig in sysUserTenantConfigs) { var shardingTenantOptions = JsonConvert.DeserializeObject(sysUserTenantConfig.ConfigJson); var shardingRuntimeContext = shardingBuilder.Build(shardingTenantOptions); tenantManager.AddTenantSharding(sysUserTenantConfig.UserId, shardingRuntimeContext); } } } var tenantIds = tenantManager.GetAll(); foreach (var tenantId in tenantIds) { using(tenantManager.CreateScope(tenantId)) using (var scope = serviceProvider.CreateScope()) { var shardingRuntimeContext = tenantManager.GetCurrentTenantContext().GetShardingRuntimeContext(); //开启定时任务 shardingRuntimeContext.UseAutoShardingCreate(); var tenantDbContext = scope.ServiceProvider.GetService(); // tenantDbContext.Database.Migrate(); //补偿表 shardingRuntimeContext.UseAutoTryCompensateTable(); } } } }
请求租户中间件
为了让我们的所有请求都可以使用指定对应的租户数据库
public class TenantSelectMiddleware { private readonly RequestDelegate _next; private readonly ITenantManager _tenantManager; public TenantSelectMiddleware(RequestDelegate next,ITenantManager tenantManager) { _next = next; _tenantManager = tenantManager; } /// /// 1.中间件的方法必须叫Invoke，且为public，非static。 /// 2.Invoke方法第一个参数必须是HttpContext类型。 /// 3.Invoke方法必须返回Task。 /// 4.Invoke方法可以有多个参数，除HttpContext外其它参数会尝试从依赖注入容器中获取。 /// 5.Invoke方法不能有重载。 /// /// Author : Napoleon /// Created : 2020/1/30 21:30 public async Task Invoke(HttpContext context) { if (context.Request.Path.ToString().StartsWith(“/api/tenant”, StringComparison.CurrentCultureIgnoreCase)) { if (!context.User.Identity.IsAuthenticated) { await _next(context); return; } var tenantId = context.User.Claims.FirstOrDefault((o) => o.Type == “uid”)?.Value; if (string.IsNullOrWhiteSpace(tenantId)) { await DoUnAuthorized(context, “not found tenant id”); return; } using (_tenantManager.CreateScope(tenantId)) { await _next(context); } } else { await _next(context); } } private async Task DoUnAuthorized(HttpContext context, string msg) { context.Response.StatusCode = 403; await context.Response.WriteAsync(msg); } }折叠
编写登录注册操作
startup处配置
[Route(“api/[controller]/[action]”)] [ApiController] [AllowAnonymous] public class PassportController : ControllerBase { private readonly IServiceProvider _serviceProvider; private readonly IdentityDbContext _identityDbContext; private readonly ITenantManager _tenantManager; private readonly IShardingBuilder _shardingBuilder; public PassportController(IServiceProvider serviceProvider, IdentityDbContext identityDbContext, ITenantManager tenantManager, IShardingBuilder shardingBuilder) { _serviceProvider = serviceProvider; _identityDbContext = identityDbContext; _tenantManager = tenantManager; _shardingBuilder = shardingBuilder; } [HttpPost] public async Task Register(RegisterRequest request) { if (await _identityDbContext.Set().AnyAsync(o => o.Name == request.Name)) return BadRequest(“user not exists”); var sysUser = new SysUser() { Id = Guid.NewGuid().ToString(“n”), Name = request.Name, Password = request.Password, CreationTime = DateTime.Now }; var shardingTenantOptions = new ShardingTenantOptions() { DbType = request.DbType, OrderShardingType = request.OrderShardingType, BeginTimeForSharding = request.BeginTimeForSharding.Value, DefaultDataSourceName = “ds0”, DefaultConnectionString = GetDefaultString(request.DbType, sysUser.Id) }; var sysUserTenantConfig = new SysUserTenantConfig() { Id = Guid.NewGuid().ToString(“n”), UserId = sysUser.Id, CreationTime = DateTime.Now, ConfigJson = JsonConvert.SerializeObject(shardingTenantOptions) }; await _identityDbContext.AddAsync(sysUser); await _identityDbContext.AddAsync(sysUserTenantConfig); await _identityDbContext.SaveChangesAsync(); var shardingRuntimeContext = _shardingBuilder.Build(shardingTenantOptions); _tenantManager.AddTenantSharding(sysUser.Id, shardingRuntimeContext); using (_tenantManager.CreateScope(sysUser.Id)) using (var scope = _serviceProvider.CreateScope()) { var runtimeContext = _tenantManager.GetCurrentTenantContext().GetShardingRuntimeContext(); runtimeContext.UseAutoShardingCreate(); //启动定时任务 var tenantDbContext = scope.ServiceProvider.GetService(); tenantDbContext.Database.Migrate(); runtimeContext.UseAutoTryCompensateTable(); } return Ok(); } [HttpPost] public async Task Login(LoginRequest request) { var sysUser = await _identityDbContext.Set() .FirstOrDefaultAsync(o => o.Name == request.Name && o.Password == request.Password); if (sysUser == null) return BadRequest(“name or password error”); //秘钥，就是标头，这里用Hmacsha256算法，需要256bit的密钥 var securityKey = new SigningCredentials(new SymmetricSecurityKey(Encoding.ASCII.GetBytes(“123123!@#!@#123123”)), SecurityAlgorithms.HmacSha256); //Claim，JwtRegisteredClaimNames中预定义了好多种默认的参数名，也可以像下面的Guid一样自己定义键名. //ClaimTypes也预定义了好多类型如role、email、name。Role用于赋予权限，不同的角色可以访问不同的接口 //相当于有效载荷 var claims = new Claim[] { new Claim(JwtRegisteredClaimNames.Iss, “https://localhost:5000”), new Claim(JwtRegisteredClaimNames.Aud, “api”), new Claim(“id”, Guid.NewGuid().ToString(“n”)), new Claim(“uid”, sysUser.Id), }; SecurityToken securityToken = new JwtSecurityToken( signingCredentials: securityKey, expires: DateTime.Now.AddHours(2), //过期时间 claims: claims ); var token = new JwtSecurityTokenHandler().WriteToken(securityToken); return Ok(token); } private string GetDefaultString(DbTypeEnum dbType, string userId) { switch (dbType) { case DbTypeEnum.MSSQL: return #34;Data Source=localhost;Initial Catalog=DB{userId};Integrated Security=True;”; case DbTypeEnum.MYSQL: return #34;server=127.0.0.1;port=3306;database=DB{userId};userid=root;password=L6yBtV6qNENrwBy7;”; default: throw new NotImplementedException(); } } } public class RegisterRequest { public string Name { get; set; } public string Password { get; set; } public DbTypeEnum DbType { get; set; } public OrderShardingTypeEnum OrderShardingType { get; set; } public DateTime? BeginTimeForSharding { get; set; } } public class LoginRequest { public string Name { get; set; } public string Password { get; set; } }折叠
启动配置
var builder = WebApplication.CreateBuilder(args);// Add services to the container.builder.Services.AddControllers();builder.Services.AddAuthentication();#region 用户系统配置builder.Services.AddDbContext(o => o.UseSqlServer(“Data Source=localhost;Initial Catalog=IdDb;Integrated Security=True;”));//生成密钥var keyByteArray = Encoding.ASCII.GetBytes(“123123!@#!@#123123”);var signingKey = new SymmetricSecurityKey(keyByteArray);//认证参数builder.Services.AddAuthentication(“Bearer”) .AddJwtBearer(o => { o.TokenValidationParameters = new TokenValidationParameters { ValidateIssuerSigningKey = true, IssuerSigningKey = signingKey, ValidateIssuer = true, ValidIssuer = “https://localhost:5000”, ValidateAudience = true, ValidAudience = “api”, ValidateLifetime = true, ClockSkew = TimeSpan.Zero, RequireExpirationTime = true, }; });#endregionbuilder.Services.AddSingleton();builder.Services.AddSingleton();builder.Services.AddSingleton();#region 配置ShardingCorevar provider = builder.Configuration.GetValue(“Provider”, “UnKnown”);//Add-Migration InitialCreate -Context TenantDbContext -OutputDir MigrationsSqlServer -Args “–provider SqlServer”//Add-Migration InitialCreate -Context TenantDbContext -OutputDir MigrationsMySql -Args “–provider MySql”builder.Services.AddDbContext((sp, b) =>{ var tenantManager = sp.GetRequiredService(); var currentTenantContext = tenantManager.GetCurrentTenantContext(); //如果有上下文那么创建租户dbcontext否则就是启动命令Add-Migration if (currentTenantContext != null) { var shardingRuntimeContext = currentTenantContext.GetShardingRuntimeContext(); b.UseDefaultSharding(shardingRuntimeContext); } if (args.IsNotEmpty()) { //命令启动时为了保证Add-Migration正常运行 if (provider == “MySql”) { b.UseMySql(“server=127.0.0.1;port=3306;database=TenantDb;userid=root;password=L6yBtV6qNENrwBy7;”, new MySqlServerVersion(new Version())) .UseMigrationNamespace(new MySqlMigrationNamespace()) .ReplaceService(); return; } if (provider == “SqlServer”) { b.UseSqlServer(“Data Source=localhost;Initial Catalog=TenantDb;Integrated Security=True;”) .UseMigrationNamespace(new SqlServerMigrationNamespace()) .ReplaceService(); return; } }});#endregionvar app = builder.Build();//初始化启动配置租户信息app.Services.InitTenant();app.UseAuthorization();//在认证后启用租户选择中间件app.UseMiddleware();app.MapControllers();app.Run();折叠
添加迁移脚本
持久化identity迁移
多租户SqlServer版本
多租户MySql版本
启动程序
启动程序我们发现IdentityDbContext已经创建好了,并且支持了自动迁移
创建A租户
{ “Name”:”A”, “Password”:”A”, “DbType”:1, “OrderShardingType”:2, “BeginTimeForSharding”:”2022-01-01″, “MigrationNamespace”:”ShardingCoreMultiTenantSys.Migrations.SqlServer”}
注意：MigrationNamespace应该自动生成，这边只是为了演示方便没写
完成
创建B租户
{ “Name”:”B”, “Password”:”B”, “DbType”:2, “OrderShardingType”:1, “BeginTimeForSharding”:”2022-01-01″, “MigrationNamespace”:”ShardingCoreMultiTenantSys.Migrations.Myql”}
完美创建
创建C租户
{ “Name”:”C”, “Password”:”C”, “DbType”:1, “OrderShardingType”:2, “BeginTimeForSharding”:”2022-06-01″, “MigrationNamespace”:”ShardingCoreMultiTenantSys.Migrations.SqlServer”}
C租户完美创建并且和A租户采用一样的分片规则不一样的分片起始时间
分别对abc进行crud
首先获取token,然后插入A租户
B租户
C租户
最后完成
文章来自https://www.cnblogs.com/xuejiaming/p/16508446.html
