ShardingCore分库实践

开发环境：Visual studio 项目模板：Asp.Net Core Web Api 框架：.Net5

1.创建项目

2.安装ShardingCore+MySql

使用Nuget管理包工具安装即可，

ShardingCore版本：6.8.0.9 （ShardingCore）

MySql版本：5.0.0（Pomelo.EntityFrameworkCore.MySql）

3.添加实体类

订单状态枚举类

public enum OrderStatusEnum
    {
        NoPay=1,
        Paying=2,
        Payed=3,
        PayFail=4
    }

订单实体类

public class Order
    {
        public string Id { get; set; }
        public string Payer { get; set; }
        public long Money { get; set; }
        public string Area { get; set; }
        public OrderStatusEnum OrderStatus { get; set; }
        public DateTime CreationTime { get; set; }
    }

系统用户实体类

public class SysUser
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public string Area { get; set; }
    }

4.添加数据库上下文

using Microsoft.EntityFrameworkCore;
using ShardingCore.Sharding;

namespace ShardingCoreDatabase
{
    public class MyDbContext:AbstractShardingDbContext
    {
        public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Order>(entity=>
            {
                entity.HasKey(o => o.Id);
                entity.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.Property(o => o.Payer).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.Property(o => o.Area).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.Property(o => o.OrderStatus).HasConversion<int>();
                entity.ToTable(nameof(Order));
            });
            modelBuilder.Entity<SysUser>(entity => 
            {
                entity.HasKey(o => o.Id);
                entity.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.Property(o => o.Name).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.Property(o => o.Area).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.ToTable(nameof(SysUser));
            });
        }
    }
}

5.添加数据库路由配置类

订单数据库路由配置类

using ShardingCore.Core.EntityMetadatas;
using ShardingCore.Core.VirtualRoutes;
using ShardingCore.Core.VirtualRoutes.DataSourceRoutes.Abstractions;
using System;
using System.Collections.Generic;
using System.Linq;

namespace ShardingCoreDatabase
{
    public class OrderVirtualDataSourceRoute : AbstractShardingOperatorVirtualDataSourceRoute<Order, string>
    {
        private readonly List<string> _dataSource = new List<string> { "A", "B", "C" };
        protected virtual string ConvertToShardingKey(object shardingKey)
        {
            return shardingKey?.ToString() ?? string.Empty;
        }
        public override bool AddDataSourceName(string dataSourceName)
        {
            if (_dataSource.Any(o => o == dataSourceName))
                return false;
            _dataSource.Add(dataSourceName);
            return true;
        }

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

        public override List<string> GetAllDataSourceNames()
        {
            return _dataSource;
        }

        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 override string ShardingKeyToDataSourceName(object shardingKey)
        {
            return ConvertToShardingKey(shardingKey);
        }
    }
}

用户数据库路由配置类

using ShardingCore.Core.EntityMetadatas;
using ShardingCore.Core.VirtualRoutes;
using ShardingCore.Core.VirtualRoutes.DataSourceRoutes.Abstractions;
using System;
using System.Collections.Generic;
using System.Linq;

namespace ShardingCoreDatabase
{
    public class SysUserVirtualDataSourceRoute : AbstractShardingOperatorVirtualDataSourceRoute<SysUser, string>
    {
        private readonly List<string> _dataSources = new List<string>()
        {
            "A", "B", "C"
        };
        protected virtual string ConvertToShardingKey(object shardingKey)
        {
            return shardingKey?.ToString() ?? string.Empty;
        }
        //我们设置区域就是数据库
        public override string ShardingKeyToDataSourceName(object shardingKey)
        {
            return ConvertToShardingKey(shardingKey);
        }

        public override List<string> GetAllDataSourceNames()
        {
            return _dataSources;
        }

        public override bool AddDataSourceName(string dataSourceName)
        {
            if (_dataSources.Any(o => o == dataSourceName))
                return false;
            _dataSources.Add(dataSourceName);
            return true;
        }

        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 override void Configure(EntityMetadataDataSourceBuilder<SysUser> builder)
        {
            builder.ShardingProperty(o => o.Area);
        }
    }
}

6.启动类配置分库服务

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using ShardingCore;
using System;
using System.Collections.Generic;

namespace ShardingCoreDatabase
{
    public class Startup
    {
        public static readonly ILoggerFactory efLogger = LoggerFactory.Create(builder => {
            builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
        });
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {

            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "ShardingCoreDatabase", Version = "v1" });
            });
            //分库路由配置，数据源配置
            services.AddShardingDbContext<MyDbContext>().UseRouteConfig(o => 
            {
                o.AddShardingDataSourceRoute<OrderVirtualDataSourceRoute>();
                o.AddShardingDataSourceRoute<SysUserVirtualDataSourceRoute>();
            }).UseConfig(op=> 
            {
                op.ThrowIfQueryRouteNotMatch = false;
                op.UseShardingQuery((conStr, builder) => 
                {
                    builder.UseMySql(conStr, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
                });
                op.UseShardingTransaction((connection, builder) => 
                {
                    builder.UseMySql(connection, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
                });
                op.AddDefaultDataSource("A", "Server=192.168.1.xx;port=3306;user=root;password=xxxxxxx;database=yzwDb_A;");
                op.AddExtraDataSource(sp => {
                    return new Dictionary<string, string>()
                    {
                        { 
                            "B",
                            "Server=192.168.1.xx;port=3306;user=root;password=xxxxxx;database=yzwDb_B;"
                        },
                        {
                            "C",
                            "Server=192.168.1.xx;port=3306;user=root;password=xxxxxx;database=yzwDb_C;"
                        }
                    };
                });
            }).AddShardingCore();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "ShardingCoreDatabase v1"));
            }

            using(var scope = app.ApplicationServices.CreateScope())
            {
                var myDbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();
                myDbContext.Database.EnsureCreated();
            }
            app.ApplicationServices.UseAutoTryCompensateTable();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
            app.InitSeed();
        }
    }
}

7.数据库初始化

新增数据库初始化脚本处理类

using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;

namespace ShardingCoreDatabase
{
    public static class StartupExtension
    {
        public static void InitSeed(this IApplicationBuilder app)
        {
            using (var serviceScope = app.ApplicationServices.CreateScope())
            {
                var myDbContext = serviceScope.ServiceProvider.GetRequiredService<MyDbContext>();
                if (!myDbContext.Set<SysUser>().Any())
                {
                    string[] areas = new string[] { "A", "B", "C" };
                    List<SysUser> users = new List<SysUser>(10);
                    for (int i = 0; i < 10; i++)
                    {
                        var user = new SysUser()
                        {
                            Id = i.ToString(),
                            Name = $"MyName{i}",
                            Area = areas[i % 3]
                        };
                        users.Add(user);
                    }

                    List<Order> orders = new List<Order>(300);
                    var begin = new DateTime(2021, 1, 1, 3, 3, 3);
                    for (int i = 0; i < 300; i++)
                    {
                        var order = new Order()
                        {
                            Id = i.ToString(),
                            Payer = $"{i % 10}",
                            Money = 100 + new Random().Next(100, 3000),
                            OrderStatus = (OrderStatusEnum)(i % 4 + 1),
                            Area = areas[i % 3],
                            CreationTime = begin.AddDays(i)
                        };
                        orders.Add(order);
                    }

                    myDbContext.AddRange(users);
                    myDbContext.AddRange(orders);
                    myDbContext.SaveChanges();
                }
            }
        }
    }
}

8.启动程序

至此，分库功能完成。

参考：初始化 | ShardingCore文档