【.Net Core】ShardingCore分库分表解决方案之多租户

介绍

GitHub地址

ShardingCore 一款ef-core下高性能、轻量级针对分表分库读写分离的解决方案，具有零依赖、零学习成本、零业务代码入侵

dotnet下唯一一款全自动分表,多字段分表框架,拥有高性能,零依赖、零学习成本、零业务代码入侵,并且支持读写分离动态分表分库,同一种路由可以完全自定义的新星组件,通过本框架你不但可以学到很多分片的思想和技巧,并且更能学到Expression的奇思妙用

我不是efcore怎么办

这边肯定有小伙伴要问有没有不是efcore的,我这边很确信的和你讲有并且适应所有的ADO.NET包括sqlhelper
ShardingConnector 一款基于ado.net下的高性能分表分库解决方案目前已有demo案例，这个框架你可以认为是.Net版本的ShardingSphere但是目前仅实现了ShardingSphere-JDBC,后续将会实现ShardingSphere-Proxy

原理

ShardingCore的整体架构是一个壳dbcontext带多个dbcontext，壳dbcontext不进行增删改查，由内部的dbcontext自己去执行，这个因为efcore的一个对象对应一个表所限制的。

我们这边把壳dbcontext称作shellDbContext，执行的dbcontext叫做executorDbContext,对于ShardingCore还有一个要求就是需要初始化启动的时候Start()，Start()内部需要IServiceProvider来获取DbContext，所以说整个框架离不开ioc，那么就需要启动的时候依赖注入DbContext,又因为依赖注入如果是默认的只能允许单个构造函数。

实现

添加依赖

这边我们添加了三个包,分别是ShardingCore,Microsoft.EntityFrameworkCore.SqlServer,Pomelo.EntityFrameworkCore.MySql,其中ShardingCore用的是预览版的如果不勾选那么将无法显示出来,为什么我们需要添加额外的两个数据库驱动呢,原因是因为我们需要在不同的租户下实现不同的数据库的配置,比如租户A和我们签订的协议里面有说明系统使用开源数据库,或者希望使用Linux平台那么可以针对租户A进行配置MySql或者PgSql,租户B是资深软粉说需要使用MSSQL那么就可以针对其配置MSSQL.一般情况下我们可能不会出现多数据库的情况但是为了照顾到特殊情况我们这边也针对这种情况进行了支持。

公共用户存储

首先在我还没有创建租户的时候是不存在数据库的所以我的数据自然而然不会存在当前租户下,这边我们采用的是存储到其他数据库中,假设我们使用一个公共的数据库作为用户系统.

创建用户系统

创建系统用户和创建系统用户在数据库内的映射关系

   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<SysUser>
    {
        public void Configure(EntityTypeBuilder<SysUser> 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));
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

   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<SysUserTenantConfig>
    {
        public void Configure(EntityTypeBuilder<SysUserTenantConfig> 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));
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

    public class IdentityDbContext:DbContext
    {
        public IdentityDbContext(DbContextOptions<IdentityDbContext> options):base(options)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.ApplyConfiguration(new SysUserMap());
            modelBuilder.ApplyConfiguration(new SysUserTenantConfigMap());
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

创建一个租户的DbContext

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

        public IRouteTail RouteTail { get; set; }
    }
1
2
3
4
5
6
7
8

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

创建动态租户参数

动态租户分片配置信息在ShardingCore只需要实现IVirtualDataSourceConfigurationParams接口,但是这个接口有很多参数需要填写,所以这边框架针对这个接口进行了默认参数的抽象类AbstractVirtualDataSourceConfigurationParams。
这边我们针对配置参数进行配置采用新建一个配置json的对象

   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; }
    }
1
2
3
4
5
6
7
8

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

    public class SqlShardingConfiguration : AbstractVirtualDataSourceConfigurationParams<TenantDbContext>
    {
        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<TenantDbContext>();
            }
            else if (_dbType == DbTypeEnum.MYSQL)
            {
                TableEnsureManager = new MySqlTableEnsureManager<TenantDbContext>();
            }
            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;
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

编写用户注册接口

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

        public PassportController(IdentityDbContext identityDbContext)
        {
            _identityDbContext = identityDbContext;
        }
        [HttpPost]
        public async Task<IActionResult> Register(RegisterRequest request)
        {
            if (await _identityDbContext.Set<SysUser>().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<IActionResult> Login(LoginRequest request)
        {
            var sysUser = await _identityDbContext.Set<SysUser>().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; }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

简单来说明一下,这边我们采用的是用户的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<Order>
    {
        public void Configure(EntityTypeBuilder<Order> 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));
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

新增订单路由

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

      public override void Configure(EntityMetadataTableBuilder<Order> builder)
      {
          builder.ShardingProperty(o => o.Id);
      }
}
1
2
3
4
5
6
7
8
9
10
11

简单的字符串取模

添加租户中间件

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

    public class TenantSelectMiddleware
    {
        private readonly RequestDelegate _next;
        private readonly IVirtualDataSourceManager<TenantDbContext> _virtualDataSourceManager;

        public TenantSelectMiddleware(RequestDelegate next, IVirtualDataSourceManager<TenantDbContext> 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);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

该中间件拦截/api/tenant路径下的所有请求并且针对这些请求添加对应的租户信息

配置租户扩展初始化数据

    public static class TenantExtension
    {
        public static void InitTenant(this IServiceProvider serviceProvider)
        {
            using (var scope = serviceProvider.CreateScope())
            {
                var identityDbContext = scope.ServiceProvider.GetRequiredService<IdentityDbContext>();
                identityDbContext.Database.EnsureCreated();
                var sysUserTenantConfigs = identityDbContext.Set<SysUserTenantConfig>().ToList();
                if (sysUserTenantConfigs.Any())
                {
                    foreach (var sysUserTenantConfig in sysUserTenantConfigs)
                    {
                        var shardingTenantOptions = JsonConvert.DeserializeObject<ShardingTenantOptions>(sysUserTenantConfig.ConfigJson);
                        DynamicShardingHelper.DynamicAppendVirtualDataSourceConfig(
                            new SqlShardingConfiguration(shardingTenantOptions));
                    }
                }
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

配置多租户

启动配置Startup

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
builder.Services.AddAuthentication();
#region 用户系统配置

builder.Services.AddDbContext<IdentityDbContext>(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<TenantDbContext>()
    .AddEntityConfig(op =>
    {
        op.CreateShardingTableOnStart = true;
        op.EnsureCreatedWithOutShardingTable = true;
        op.AddShardingTableRoute<OrderVirtualTableRoute>();
    })
    .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<IShardingBootstrapper>().Start();
//初始化启动配置租户信息
app.Services.InitTenant();
app.UseAuthorization();
app.UseAuthorization();
//在认证后启用租户选择中间件
app.UseMiddleware<TenantSelectMiddleware>();

app.MapControllers();

app.Run();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

编写租户操作

    [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<IActionResult> 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<IActionResult> UpdateOrder([FromQuery]string id)
        {
            var order =await _tenantDbContext.Set<Order>().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<IActionResult> GetOrders()
        {
            var orders =await _tenantDbContext.Set<Order>().ToListAsync();
            return Ok(orders);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

启动项目

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

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

通过截图我们可以看到ShardingCore也是为我们创建好了对应的数据库和对应的表信息

登录租户

首先我们登录

TenantA用户token

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJodHRwczovL2xvY2FsaG9zdDo1MDAwIiwiYXVkIjoiYXBpIiwiaWQiOiJkNGMwZjZiNzI5MzE0M2VlYWM0Yjg3NzUwYzE4MWUzOSIsInVpZCI6ImMxMWRkZjFmNTY0MjQwZjc5YTQzNTEzZGMwNmVjZGMxIiwiZXhwIjoxNjQxODI4ODQ0fQ.zJefwnmcIEZm-kizlN7DhwTRgGxiCg52Esa8QmHiEKY

TenantB用户token

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJodHRwczovL2xvY2FsaG9zdDo1MDAwIiwiYXVkIjoiYXBpIiwiaWQiOiIwNzY4NzUwMmVjYzY0NTMyOGFkNTcwZDRkYjMwNDI3MSIsInVpZCI6ImVkODg4YTc3MzAwYTQ4NjZhYmUyNWY2MTE1NmEwZTQzIiwiZXhwIjoxNjQxODI4ODgxfQ.cL0d010jdXLXNGT8M0wsRMqn3VeIxFnV0keM0H3SPzo

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

AddOrder
租户A插入一个订单,订单Id：aef6905f512a4f72baac5f149ef32d21

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

两个用户处理

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

UpdateOrder

GetOrders

来源

.NET Core 框架 WTM 的分表分库实现
.net core下分表分库解决方案–多租户