初始化

2021-11-4 大约 5 分钟

# 介绍

分表介绍

sharding-core支持自定义分表,流式聚合,多表join
虽然框架已经将分表封装的用户毫无感知了,但是也是需要使用者有部分分表的概念的了解
我们把表A分成A1,A2,A3这三张表,如果你在数据库里还是看到了表A那么就说明程序是不正确的,表A在分表后其实不应该存在了(大部分情况下)
IShardingTableDbContext这个接口在你需要支持分表的情况下需要加，如果您只是分库那么就不需要添加这个接口

# Demo

本次分表的demo源码：EFCoreShardingTable (opens new window)

# 分表使用

先拟定一个场景目前有用户表SysUser和订单表Order,再添加一个Setting配置表，用户我们按用户id进行取模分表，订单我们按时间月进行分表,配置表我们部分表首先创建一个空的aspnetcore web api。

# 安装ShardingCore

# 请对应安装您需要的版本
PM> Install-Package ShardingCore
# 请对应数据库版本
PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer

1
2
3
4

# 创建对象

    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 SettingCode { get; set; }
        public string Area { get; set; }
    }
    public class Setting
    {
        public string Code { get; set; }
        public string Name { 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

# 创建DbContext

这样我们就创建好了三张表，接下来我们创建我们的DbContext


    public class MyDbContext:AbstractShardingDbContext,IShardingTableDbContext
    {
        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.Property(o => o.SettingCode).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.ToTable(nameof(SysUser));
            });
            modelBuilder.Entity<Setting>(entity =>
            {
                entity.HasKey(o => o.Code);
                entity.Property(o => o.Code).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.Property(o=>o.Name).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.ToTable(nameof(Setting));
            });
        }

        public IRouteTail RouteTail { 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

自定义标题

构造函数必须是DbContextOptions<MyDbContext>或者DbContextOptions
OnModelCreating并不是说分表必须要这样，而是你原先efcore怎么使用就怎么使用，efcore配置对象有两种一种是DbSet+Attribute,另外一种是OnModelCreating+ModelBuilder,你可以选择你原先在用的任何一种
AbstractShardingDbContext这个对象是可以不继承的，但是如果要使用分表分库你必须实现IShardingTableDbContext这个接口,因为这个接口实现起来都是一样的所以默认你只需要继承AbstractShardingDbContext就可以了
IShardingTableDbContext这个接口在你需要支持分表的情况下需要加，如果您只是分库那么就不需要添加这个接口

# 创建虚拟路由

订单表按月分表,这边我们把订单从2021年1月份开始到现在具体


    //创建时间按月分表
    public class OrderVirtualTableRoute:AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<Order>
    {
        public override DateTime GetBeginTime()
        {
            return new DateTime(2021, 1, 1);
        }
        //注意一定要配置或者采用接口+标签也是可以的
        public override void Configure(EntityMetadataTableBuilder<Order> builder)
        {
            builder.ShardingProperty(o => o.CreationTime);
        }
    }
    //用户Id取模3分表
    public class SysUserVirtualTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<SysUser>
    {
        public SysUserVirtualTableRoute() : base(2, 3)
        {
        }

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

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

# Startup配置

ConfigureServices(IServiceCollection services)配置

        public void ConfigureServices(IServiceCollection services)
        {

            services.AddControllers();
            
             services.AddShardingDbContext<MyDbContext>().UseRouteConfig(op =>
            {
                op.AddShardingTableRoute<SysUserVirtualTableRoute>();
                op.AddShardingTableRoute<OrderVirtualTableRoute>();
                op.AddShardingTableRoute<MultiShardingOrderVirtualTableRoute>();
            }).UseConfig(op =>
            {
                //当无法获取路由时会返回默认值而不是报错
                op.ThrowIfQueryRouteNotMatch = false;
                op.UseShardingQuery((conStr, builder) =>
                {
                    builder.UseSqlServer(conStr).UseLoggerFactory(efLogger);
                });
                op.UseShardingTransaction((connection, builder) =>
                {
                    builder.UseSqlServer(connection).UseLoggerFactory(efLogger);
                });
                op.AddDefaultDataSource("ds0",
                    "Data Source=localhost;Initial Catalog=EFCoreShardingTableDB;Integrated Security=True;");
                op.AddReadWriteSeparation(sp =>
                {
                    return new Dictionary<string, IEnumerable<string>>()
                    {
                        {
                            "ds0", new List<string>()
                            {
                                "Data Source=localhost;Initial Catalog=EFCoreShardingTableDB;Integrated Security=True;"
                            }
                        }
                    };
                }, ReadStrategyEnum.Loop, defaultEnable: true);
            }).AddShardingCore();
        }

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

重要

!!!MyDbContext请勿注册成单例、ServiceLifetime.Singleton!!!

新建一个扩展方法用来初始化ShardingCore和初始化种子数据

    public static class StartupExtension
    {
        public static void UseShardingCore(this IApplicationBuilder app)
        {
            app.ApplicationServices.GetRequiredService<IShardingBootstrapper>().Start();
        }
        public static void InitSeed(this IApplicationBuilder app)
        {
            using (var serviceScope = app.ApplicationServices.CreateScope())
            {
                var myDbContext = serviceScope.ServiceProvider.GetRequiredService<MyDbContext>();
                if (!myDbContext.Set<Setting>().Any())
                {
                    List<Setting> settings = new List<Setting>(3);
                    settings.Add(new Setting()
                    {
                        Code = "Admin",
                        Name = "AdminName"
                    });
                    settings.Add(new Setting()
                    {
                        Code = "User",
                        Name = "UserName"
                    });
                    settings.Add(new Setting()
                    {
                        Code = "SuperAdmin",
                        Name = "SuperAdminName"
                    });

                    List<SysUser> users = new List<SysUser>(10);
                    for (int i = 0; i < 10; i++)
                    {
                        var uer=new SysUser()
                        {
                            Id = i.ToString(),
                            Name = $"MyName{i}",
                            SettingCode = settings[i % 3].Code
                        };
                        users.Add(uer);
                    }
                    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),
                            CreationTime = begin.AddDays(i)
                        };
                        orders.Add(order);
                    }
                    myDbContext.AddRange(settings);
                    myDbContext.AddRange(users);
                    myDbContext.AddRange(orders);
                    myDbContext.SaveChanges();
                }
            }
        }
    }

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

Configure(IApplicationBuilder app, IWebHostEnvironment env)配置

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            //建议补偿表在迁移后面
            using (var scope = app.ApplicationServices.CreateScope())
            {
                var myDbContext = scope.ServiceProvider.GetService<MyDbContext>();
                //如果没有迁移那么就直接创建表和库
                myDbContext.Database.EnsureCreated();
                //如果有迁移使用下面的
                // myDbContext.Database.Migrate();
            }
            app.ApplicationServices.UseAutoTryCompensateTable();
            // app.UseShardingCore();
            app.UseRouting();

            app.UseAuthorization();

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

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

# efcore日志(可选)

这边为了方便我们观察efcore的执行sql语句我们这边建议对efcore添加日志

Startup添加静态数据

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