登录
首页 >  数据库 >  MySQL

.net core下分表分库解决方案--多租户

来源:SegmentFault

时间:2023-01-16 18:47:03 392浏览 收藏

本篇文章给大家分享《.net core下分表分库解决方案--多租户》,覆盖了数据库的常见基础知识,其实一个语言的全部知识点一篇文章是不可能说完的,但希望通过这些问题,让读者对自己的掌握程度有一定的认识(B 数),从而弥补自己的不足,更好的掌握它。

介绍

本期主角:

    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 SysUserMap:IEntityTypeConfiguration
    {
        public void Configure(EntityTypeBuilder builder)
        {
            builder.HasKey(o => o.Id);
            builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
            builder.Property(o => o.Name).IsRequired().HasMaxLength(50);
            builder.Property(o => o.Password).IsRequired().IsUnicode(false).HasMaxLength(50);
            builder.HasQueryFilter(o => o.IsDeleted == false);
            builder.ToTable(nameof(SysUser));
        }
    }

创建这个数据库该有的配置信息表,便于后期启动后重建

    public class SysUserTenantConfig
    {
        public string Id { get; set; }
        public string UserId { get; set; }
        /// 
        /// 添加ShardingCore配置的Json包
        /// 
        public string ConfigJson { get; set; }
        public DateTime CreationTime { get; set; }
        public bool IsDeleted { get; set; }
    }
    public class SysUserTenantConfigMap:IEntityTypeConfiguration
    {
        public void Configure(EntityTypeBuilder builder)
        {
            builder.HasKey(o => o.Id);
            builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
            builder.Property(o => o.UserId).IsRequired().IsUnicode(false).HasMaxLength(50);
            builder.Property(o => o.ConfigJson).IsRequired().HasMaxLength(2000);
            builder.HasQueryFilter(o => o.IsDeleted == false);
            builder.ToTable(nameof(SysUserTenantConfig));
        }
    }

创建对应的系统用户存储DbContext

    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());
        }
    }

创建一个租户的DbContext

    public class TenantDbContext:AbstractShardingDbContext,IShardingTableDbContext
    {
        public TenantDbContext(DbContextOptions options) : base(options)
        {
        }

        public IRouteTail RouteTail { get; set; }
    }

目前我们先定义好后续进行编写内部的租户代码

创建动态租户参数

动态租户分片配置信息在

    public class ShardingTenantOptions
    {
        public  string ConfigId { get; set;}
        public  int Priority { get; set;}
        public  string DefaultDataSourceName { get; set;}
        public  string DefaultConnectionString { get; set;}
        public DbTypeEnum DbType { get; set; }
    }

参数里面配置了当前数据库,这边比较简单我们就暂时使用单表分库的模式来实现,目前暂时不对每个租户分库进行演示。之后并且编写

    public class SqlShardingConfiguration : AbstractVirtualDataSourceConfigurationParams
    {
        private static readonly ILoggerFactory efLogger = LoggerFactory.Create(builder =>
        {
            builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
        });
        public override string ConfigId { get; }
        public override int Priority { get; }
        public override string DefaultDataSourceName { get; }
        public override string DefaultConnectionString { get; }
        public override ITableEnsureManager TableEnsureManager { get; }

        private readonly DbTypeEnum _dbType;
        public SqlShardingConfiguration(ShardingTenantOptions options)
        {
            ConfigId = options.ConfigId;
            Priority = options.Priority;
            DefaultDataSourceName = options.DefaultDataSourceName;
            DefaultConnectionString = options.DefaultConnectionString;
            _dbType = options.DbType;
            //用来快速判断是否存在数据库中的表
            if (_dbType == DbTypeEnum.MSSQL)
            {
                TableEnsureManager = new SqlServerTableEnsureManager();
            }
            else if (_dbType == DbTypeEnum.MYSQL)
            {
                TableEnsureManager = new MySqlTableEnsureManager();
            }
            else
            {
                throw new NotImplementedException();
            }
        }
        public override DbContextOptionsBuilder UseDbContextOptionsBuilder(string connectionString,
            DbContextOptionsBuilder dbContextOptionsBuilder)
        {
            switch (_dbType)
            {
                case DbTypeEnum.MSSQL:
                    {
                        dbContextOptionsBuilder.UseSqlServer(connectionString).UseLoggerFactory(efLogger);
                    }
                    break;
                case DbTypeEnum.MYSQL:
                    {
                        dbContextOptionsBuilder.UseMySql(connectionString, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
                    }
                    break;
                default: throw new NotImplementedException();
            }
            return dbContextOptionsBuilder;
        }

        public override DbContextOptionsBuilder UseDbContextOptionsBuilder(DbConnection dbConnection,
            DbContextOptionsBuilder dbContextOptionsBuilder)
        {
            switch (_dbType)
            {
                case DbTypeEnum.MSSQL:
                {
                    dbContextOptionsBuilder.UseSqlServer(dbConnection).UseLoggerFactory(efLogger);
                    }
                    break;
                case DbTypeEnum.MYSQL:
                {
                    dbContextOptionsBuilder.UseMySql(dbConnection, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
                    }
                    break;
                default: throw new NotImplementedException();
            }
            return dbContextOptionsBuilder;
        }
    }

编写用户注册接口

    [Route("api/[controller]/[action]")]
    [ApiController]
    [AllowAnonymous]
    public class PassportController:ControllerBase
    {
        private readonly IdentityDbContext _identityDbContext;

        public PassportController(IdentityDbContext identityDbContext)
        {
            _identityDbContext = identityDbContext;
        }
        [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()
            {
                ConfigId = sysUser.Id,
                Priority = new Random().Next(1,10),
                DbType = request.DbType,
                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();
            //注册完成后进行配置生成
            DynamicShardingHelper.DynamicAppendVirtualDataSourceConfig(new SqlShardingConfiguration(shardingTenantOptions));
            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 $"Data Source=localhost;Initial Catalog=DB{userId};Integrated Security=True;";
                case DbTypeEnum.MYSQL: return $"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 class LoginRequest
    {
        public string Name { get; set; }
        public string Password { get; set; }
    }

简单来说明一下,这边我们采用的是用户的id作为租户id,将租户id作为数据库配置,来支持多配置模式。到此为止我们的用户系统就已经完成了是不是十分的简单仅仅几段代码,用户这边注册完成后将会创建对应的数据库和对应的表,如果你是分表的那么将会自动创建对应的数据库表等信息。

租户系统

租户系统我们做一个订单的简单演示,使用订单id取模,取模取5来进行分表操作

新增租户系统的订单信息

    public class Order
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public DateTime CreationTime { get; set; }
        public bool IsDeleted { get; set; }
    }
    public class OrderMap:IEntityTypeConfiguration
    {
        public void Configure(EntityTypeBuilder builder)
        {
            builder.HasKey(o => o.Id);
            builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
            builder.Property(o => o.Name).IsRequired().HasMaxLength(100);
            builder.HasQueryFilter(o => o.IsDeleted == false);
            builder.ToTable(nameof(Order));
        }
    }

新增订单路由

public class OrderVirtualTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute
{
      public OrderVirtualTableRoute() : base(2, 5)
      {
      }

      public override void Configure(EntityMetadataTableBuilder builder)
      {
          builder.ShardingProperty(o => o.Id);
      }
}

简单的字符串取模

添加租户中间件

添加租户中间件,在系统中如果使用多配置那么就必须要指定本次创建的dbcontext使用的是哪个配置

    public class TenantSelectMiddleware
    {
        private readonly RequestDelegate _next;
        private readonly IVirtualDataSourceManager _virtualDataSourceManager;

        public TenantSelectMiddleware(RequestDelegate next, IVirtualDataSourceManager virtualDataSourceManager)
        {
            _next = next;
            _virtualDataSourceManager = virtualDataSourceManager;
        }

        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 (_virtualDataSourceManager.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);
        }
    }

该中间件拦截

    public static class TenantExtension
    {
        public static void InitTenant(this IServiceProvider serviceProvider)
        {
            using (var scope = serviceProvider.CreateScope())
            {
                var identityDbContext = scope.ServiceProvider.GetRequiredService();
                identityDbContext.Database.EnsureCreated();
                var sysUserTenantConfigs = identityDbContext.Set().ToList();
                if (sysUserTenantConfigs.Any())
                {
                    foreach (var sysUserTenantConfig in sysUserTenantConfigs)
                    {
                        var shardingTenantOptions = JsonConvert.DeserializeObject(sysUserTenantConfig.ConfigJson);
                        DynamicShardingHelper.DynamicAppendVirtualDataSourceConfig(
                            new SqlShardingConfiguration(shardingTenantOptions));
                    }
                }
            }
        }
    }

这边因为我们针对租户信息进行了初始化而不是硬编码,所以需要一个在启动的时候对租户信息进行动态添加

配置多租户

启动配置

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,
        };
    });
#endregion
#region 配置ShardingCore
builder.Services.AddShardingDbContext()
    .AddEntityConfig(op =>
    {
        op.CreateShardingTableOnStart = true;
        op.EnsureCreatedWithOutShardingTable = true;
        op.AddShardingTableRoute();
    })
    .AddConfig(op =>
    {
        //默认配置一个
        op.ConfigId = $"test_{Guid.NewGuid():n}";
        op.Priority = 99999;
        op.AddDefaultDataSource("ds0", "Data Source=localhost;Initial Catalog=TestTenantDb;Integrated Security=True;");
        op.UseShardingQuery((conStr, b) =>
        {
            b.UseSqlServer(conStr);
        });
        op.UseShardingTransaction((conn, b) =>
        {
            b.UseSqlServer(conn);
        });
    }).EnsureMultiConfig(ShardingConfigurationStrategyEnum.ThrowIfNull);

#endregion

var app = builder.Build();

// Configure the HTTP request pipeline.
app.Services.GetRequiredService().Start();
//初始化启动配置租户信息
app.Services.InitTenant();
app.UseAuthorization();
app.UseAuthorization();
//在认证后启用租户选择中间件
app.UseMiddleware();

app.MapControllers();

app.Run();

编写租户操作

    [Route("api/tenant/[controller]/[action]")]
    [ApiController]
    [Authorize(AuthenticationSchemes = "Bearer")]
    public class TenantController : ControllerBase
    {
        private readonly TenantDbContext _tenantDbContext;

        public TenantController(TenantDbContext tenantDbContext)
        {
            _tenantDbContext = tenantDbContext;
        }
        public async Task AddOrder()
        {
            var order = new Order()
            {
                Id = Guid.NewGuid().ToString("n"),
                CreationTime = DateTime.Now,
                Name = new Random().Next(1,100)+"_name"
            };
            await _tenantDbContext.AddAsync(order);
            await _tenantDbContext.SaveChangesAsync();
            return Ok(order.Id);
        }
        public async Task UpdateOrder([FromQuery]string id)
        {
            var order =await _tenantDbContext.Set().FirstOrDefaultAsync(o=>o.Id==id);
            if (order == null) return BadRequest();
            order.Name = new Random().Next(1, 100) + "_name";
            await _tenantDbContext.SaveChangesAsync();
            return Ok(order.Id);
        }
        public async Task GetOrders()
        {
            var orders =await _tenantDbContext.Set().ToListAsync();
            return Ok(orders);
        }
    }

启动项目

这边我们基本上已经配置好我们所需要的之后我们就可以直接启动项目了

这边我们通过接口注册了一个TenantA的用户并且选择了使用MSSQL,这边成就帮我们自动生成好了对应的数据库表结构
接下来我么再注册一个TenantB用户选择MySql

通过截图我们可以看到

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJodHRwczovL2xvY2FsaG9zdDo1MDAwIiwiYXVkIjoiYXBpIiwiaWQiOiJkNGMwZjZiNzI5MzE0M2VlYWM0Yjg3NzUwYzE4MWUzOSIsInVpZCI6ImMxMWRkZjFmNTY0MjQwZjc5YTQzNTEzZGMwNmVjZGMxIiwiZXhwIjoxNjQxODI4ODQ0fQ.zJefwnmcIEZm-kizlN7DhwTRgGxiCg52Esa8QmHiEKY

TenantB用户token

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJodHRwczovL2xvY2FsaG9zdDo1MDAwIiwiYXVkIjoiYXBpIiwiaWQiOiIwNzY4NzUwMmVjYzY0NTMyOGFkNTcwZDRkYjMwNDI3MSIsInVpZCI6ImVkODg4YTc3MzAwYTQ4NjZhYmUyNWY2MTE1NmEwZTQzIiwiZXhwIjoxNjQxODI4ODgxfQ.cL0d010jdXLXNGT8M0wsRMqn3VeIxFnV0keM0H3SPzo

接下来我们分别对两个租户进行交叉处理

AddOrder

租户A插入一个订单,订单Id:

aef6905f512a4f72baac5f149ef32d21

TenantB用户也插入一个订单,订单id:

450f5dd0e82442eca33dfcf3d57fafa3


两个用户处理

通过日志打印明显能够感觉出来两者是区分了不同的数据库

UpdateOrder

GetOrders

总结

通过上述功能的演示相信很多小伙伴应该已经知道他具体的运作流程了,通过配置多个租户信息,在

ShardingCore
上实现多配置,动态配置,来保证在多租户模式下的分表分库读写分离依然可以使用,并且拥有跟好的适泛性。
如果你需要开发一个大型程序,领导上来就是分库分表,那么在以前大概率是会花费非常多的精力在处理分片这件事情上,而最终项目是否可以做完并且使用还是一个巨大的问题,但是现在不一样了,毕竟
ShardingCore
之前并没有一款非常好用的分片组件在.net上,并且拥有非常完美的orm作为支持,基本上重来没有一个框架说多租户模式是可以选择数据库的,之前市面上所有的多租户你只能选择一种数据库,目前.Net在开源的状态下我相信会有越来越好的组件框架诞生,毕竟这么好的语言如果配上丰富的生态那将是所有.Neter的福音。

最后的最后

demo地址 https://github.com/xuejmnet/S...

您都看到这边了确定不点个star或者赞吗,一款.Net不得不学的分库分表解决方案,简单理解为sharding-jdbc在.net中的实现并且支持更多特性和更优秀的数据聚合,拥有原生性能的97%,并且无业务侵入性,支持未分片的所有efcore原生查询

本篇关于《.net core下分表分库解决方案--多租户》的介绍就到此结束啦,但是学无止境,想要了解学习更多关于数据库的相关知识,请关注golang学习网公众号!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>