天天看點
傳回

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

## 介紹

本文ShardinfCore版本x.6.0.20+

本期主角:

- [`ShardingCore`](https://github.com/dotnetcore/sharding-core) 一款ef-core下高性能、輕量級針對分表分庫讀寫分離的解決方案，具有零依賴、零學習成本、零業務代碼入侵适配

單dbcontext多資料庫自動遷移

之前發過一篇文章 EFCore進階Saas系統下單DbContext如何支援不同資料庫的遷移 這篇文章讓efcore可以支援在單dbcontext下支援多個資料庫的遷移來實作多租戶下的不同資料庫實作

前言

您是否有以下場景：

  • 多租戶系統，資料庫級别隔離
  • 大資料量，需要分表分庫（動态添加），分庫分表全自動維護處理
  • 租戶之間可能需要使用不同的資料庫模式，譬如有些租戶要求用oracle，或者mssql，或者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; }
        /// <summary>
        /// 添加ShardingTenantOptions的Json包
        /// </summary>
        public string ConfigJson { get; set; }
        public DateTime CreationTime { get; set; }
        public bool IsDeleted { get; set; }
    }

定義租戶配置 

//為了滿足上述需求我們需要對資料庫和訂單分片方式進行區分
    public class ShardingTenantOptions
    {
        /// <summary>
        /// 預設資料源名稱
        /// </summary>
        public  string DefaultDataSourceName { get; set;}
        /// <summary>
        /// 預設資料庫位址
        /// </summary>
        public  string DefaultConnectionString { get; set; }
        /// <summary>
        /// 資料庫類型
        /// </summary>
        public DbTypeEnum DbType { get; set; }
        /// <summary>
        /// 分片模式 取模還是按月
        /// </summary>
        public OrderShardingTypeEnum OrderShardingType { get; set; }
        /// <summary>
        /// 按月分片其實時間
        /// </summary>
        public DateTime BeginTimeForSharding { get; set; }
        /// <summary>
        /// 分片遷移的命名空間
        /// </summary>
        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<IdentityDbContext> options):base(options)
        {
            
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.ApplyConfiguration(new SysUserMap());
            modelBuilder.ApplyConfiguration(new SysUserTenantConfigMap());
        }
    }

這樣我們就完成了租戶資訊的存儲

租戶管理者

　　我們擁有了租戶資訊持久化的資料後需要對租戶資訊的使用進行配置

首先我們建立一個接口可以用來管理租戶資訊

public interface ITenantManager
    {
        /// <summary>
        /// 擷取所有的租戶
        /// </summary>
        /// <returns></returns>
        List<string> GetAll();

        /// <summary>
        /// 擷取目前租戶
        /// </summary>
        /// <returns></returns>
        TenantContext GetCurrentTenantContext();
        /// <summary>
        /// 添加租戶資訊
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="shardingRuntimeContext"></param>
        /// <returns></returns>
        bool AddTenantSharding(string tenantId, IShardingRuntimeContext shardingRuntimeContext);

        /// <summary>
        /// 建立租戶環境
        /// </summary>
        /// <param name="tenantId"></param>
        /// <returns></returns>
        TenantScope CreateScope(string tenantId);
    }
    //租戶的預設管理實作
    public class DefaultTenantManager:ITenantManager
    {
        private readonly ITenantContextAccessor _tenantContextAccessor;
        private readonly ConcurrentDictionary<string, IShardingRuntimeContext> _cache = new();

        public DefaultTenantManager(ITenantContextAccessor tenantContextAccessor)
        {
            _tenantContextAccessor = tenantContextAccessor;
        }

        public List<string> 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?> _tenantContext = new AsyncLocal<TenantContext?>();
        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()
        {
            TenantContextAccessor.TenantContext = null;
        }
    }

構思ShardingCore如何不通過依賴注入使用

其實ShardingCore可以預設不在依賴注入中進行依賴注入，首先我們看下普通情況下ShardingCore如何實作非依賴注入擷取

分片上下文

 
var shardingRuntimeContext = new ShardingRuntimeBuilder<DefaultShardingDbContext>()
    .UseRouteConfig(o =>
    {
        o.AddShardingTableRoute<SysUserTableRoute>();
    }).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<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();
        });
    }).ReplaceService<ITableEnsureManager, MySqlTableEnsureManager>(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<TenantDbContext> 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<Order>
    {
        private readonly ShardingTenantOptions _shardingTenantOptions;

        public OrderMonthTableRoute(ShardingTenantOptions shardingTenantOptions)
        {
            _shardingTenantOptions = shardingTenantOptions;
        }
        public override void Configure(EntityMetadataTableBuilder<Order> builder)
        {
            builder.ShardingProperty(o => o.CreationTime);
        }

        public override bool AutoCreateTableByTime()
        {
            return true;

        }

        public override DateTime GetBeginTime()
        {
            return _shardingTenantOptions.BeginTimeForSharding;
        }
    }

訂單取模分片路由 

public class OrderModTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<Order>
{
    private readonly ShardingTenantOptions _shardingTenantOptions;

    public OrderModTableRoute(ShardingTenantOptions shardingTenantOptions) : base(2, 5)
    {
        _shardingTenantOptions = shardingTenantOptions;
    }

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

實作多資料庫的code-first遷移

具體參考之前的部落格EFCore進階Saas系統下單DbContext如何支援不同資料庫的遷移

https://www.cnblogs.com/xuejiaming/p/16510482.html

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

分片建立者 

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<TenantDbContext>()
            .UseRouteConfig(o =>
            {
                if (tenantOptions.OrderShardingType == OrderShardingTypeEnum.Mod)
                {
                    o.AddShardingTableRoute<OrderModTableRoute>();
                }
                if (tenantOptions.OrderShardingType == OrderShardingTypeEnum.ByMonth)
                {
                    o.AddShardingTableRoute<OrderMonthTableRoute>();
                }
            }).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<IMigrationsAssembly,MultiDatabaseMigrationsAssembly>();
                });
                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<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();
                    }
                    if (tenantOptions.DbType == DbTypeEnum.MSSQL)
                    {
                        b.ReplaceService<IMigrationsSqlGenerator, ShardingSqlServerMigrationsSqlGenerator>();
                    }
                });
            }).AddServiceConfigure(s =>
            {
                //IShardingRuntimeContext内部的依賴注入
                s.AddSingleton(tenantOptions);
            });
        
        if (tenantOptions.DbType == DbTypeEnum.MYSQL)
        {
            shardingRuntimeBuilder.ReplaceService<ITableEnsureManager, MySqlTableEnsureManager>(ServiceLifetime
                .Singleton);
        }
        if (tenantOptions.DbType == DbTypeEnum.MSSQL)
        {
            shardingRuntimeBuilder.ReplaceService<ITableEnsureManager, SqlServerTableEnsureManager>(ServiceLifetime
                .Singleton);
        }
        return shardingRuntimeBuilder.Build(_serviceProvider);
    }
}

到此為止基本上我們已經完成了多租戶的大部配置設定置了，jwt部分就不在這邊贅述了因為之前有實作過

Startup

主要關鍵的啟動點我們應該怎麼配置呢

啟動初始化租戶

首先我們需要針對程式啟動後進行租戶的初始化操作

public static class TenantExtension
    {
        public static void InitTenant(this IServiceProvider serviceProvider)
        {
            var tenantManager = serviceProvider.GetRequiredService<ITenantManager>();
            var shardingBuilder = serviceProvider.GetRequiredService<IShardingBuilder>();
            
            using (var scope = serviceProvider.CreateScope())
            {
                var identityDbContext = scope.ServiceProvider.GetRequiredService<IdentityDbContext>();
                identityDbContext.Database.Migrate();
                var sysUserTenantConfigs = identityDbContext.Set<SysUserTenantConfig>().ToList();
                if (sysUserTenantConfigs.Any())
                {
                    foreach (var sysUserTenantConfig in sysUserTenantConfigs)
                    {
                        var shardingTenantOptions = JsonConvert.DeserializeObject<ShardingTenantOptions>(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>();
                    //
                    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;
        }

        /// <summary>
        /// 1.中間件的方法必須叫Invoke，且為public，非static。
        /// 2.Invoke方法第一個參數必須是HttpContext類型。
        /// 3.Invoke方法必須傳回Task。
        /// 4.Invoke方法可以有多個參數，除HttpContext外其它參數會嘗試從依賴注入容器中擷取。
        /// 5.Invoke方法不能有重載。
        /// </summary>
        /// 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<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()
            {
                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>();
                tenantDbContext.Database.Migrate();
                runtimeContext.UseAutoTryCompensateTable();
            }

            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 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<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

builder.Services.AddSingleton<ITenantManager, DefaultTenantManager>();
builder.Services.AddSingleton<ITenantContextAccessor, TenantContextAccessor>();
builder.Services.AddSingleton<IShardingBuilder, DefaultShardingBuilder>();

#region 配置ShardingCore

var provider = builder.Configuration.GetValue("Provider", "UnKnown");
//Add-Migration InitialCreate -Context TenantDbContext -OutputDir Migrations\SqlServer -Args "--provider SqlServer"
//Add-Migration InitialCreate -Context TenantDbContext -OutputDir Migrations\MySql -Args "--provider MySql"
builder.Services.AddDbContext<TenantDbContext>((sp, b) =>
{
    var tenantManager = sp.GetRequiredService<ITenantManager>();
    var currentTenantContext = tenantManager.GetCurrentTenantContext();
    //如果有上下文那麼建立租戶dbcontext否則就是啟動指令Add-Migration
    if (currentTenantContext != null)
    {
        var shardingRuntimeContext = currentTenantContext.GetShardingRuntimeContext();
        b.UseDefaultSharding<TenantDbContext>(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<IMigrationsAssembly, MultiDatabaseMigrationsAssembly>();
            return;
        }

        if (provider == "SqlServer")
        {
            b.UseSqlServer("Data Source=localhost;Initial Catalog=TenantDb;Integrated Security=True;")
                .UseMigrationNamespace(new SqlServerMigrationNamespace())
                .ReplaceService<IMigrationsAssembly, MultiDatabaseMigrationsAssembly>();
            return;
        }
    }
});

#endregion


var app = builder.Build();

//初始化啟動配置租戶資訊
app.Services.InitTenant();
app.UseAuthorization();

//在認證後啟用租戶選擇中間件
app.UseMiddleware<TenantSelectMiddleware>();

app.MapControllers();

app.Run();

添加遷移腳本

持久化identity遷移

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

多租戶SqlServer版本

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

多租戶MySql版本

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

啟動程式

啟動程式我們發現IdentityDbContext已經建立好了,并且支援了自動遷移

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

建立A租戶 

{
    "Name":"A",
    "Password":"A",
    "DbType":1,
    "OrderShardingType":2,
    "BeginTimeForSharding":"2022-01-01",
    "MigrationNamespace":"ShardingCoreMultiTenantSys.Migrations.SqlServer"
}

注意：

MigrationNamespace

應該自動生成，這邊隻是為了示範友善沒寫

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

完成

建立B租戶 

{
    "Name":"B",
    "Password":"B",
    "DbType":2,
    "OrderShardingType":1,
    "BeginTimeForSharding":"2022-01-01",
    "MigrationNamespace":"ShardingCoreMultiTenantSys.Migrations.Myql"
}

完美建立

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

建立C租戶 

{
    "Name":"C",
    "Password":"C",
    "DbType":1,
    "OrderShardingType":2,
    "BeginTimeForSharding":"2022-06-01",
    "MigrationNamespace":"ShardingCoreMultiTenantSys.Migrations.SqlServer"
}

C租戶完美建立并且和A租戶采用一樣的分片規則不一樣的分片起始時間

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

分别對abc進行crud

首先擷取token,然後插入

A租戶

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

B租戶

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

C租戶

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

最後完成

在Saas系統下多租戶零腳本分表分庫讀寫分離解決方案

最後的最後

附上demo:ShardingCoreMultiTenantSys https://github.com/xuejmnet/ShardingCoreMultiTenantSys

您都看到這邊了确定不點個star或者贊嗎,一款.Net不得不學的分庫分表解決方案,簡單了解為sharding-jdbc在.net中的實作并且支援更多特性和更優秀的資料聚合,擁有原生性能的97%,并且無業務侵入性,支援未分片的所有efcore原生查詢

  • github位址 https://github.com/xuejmnet/sharding-core
  • gitee位址 https://gitee.com/dotnetchina/sharding-core
dotnetcore efcore EntityFrameworkCore sharding shardingcore 多租戶 分表 分庫
上一篇: ASP.NET Core部署手冊：5.Linux篇
下一篇: ASP.NET Core部署手冊：4.注意事項和問題排查

繼續閱讀