ShardingCore官方文档
GitHub (opens new window)

初始化

2021-11-15 使用指南 About 5 min

# 介绍

分表介绍

  1. sharding-core支持自定义分表,流式聚合,多表join
  2. 虽然框架已经将分表封装的用户毫无感知了,但是也是需要使用者有部分分表的概念的了解
  3. 我们吧表A分成A1,A2,A3这三张表,如果你在数据库里还是看到了表A那么就说明程序是不正确的,表A在分表后其实不应该存在了(大部分情况下)
  4. 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

自定义标题

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

# 创建虚拟路由

  1. 订单表按月分表,这边我们把订单从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>((conStr, builder) =>
                {
                    builder.UseSqlServer(conStr);
                }).Begin(op =>
                {
                    //如果您使用code-first建议选择false
                    op.CreateShardingTableOnStart = true;
                    //如果您使用code-first建议修改为fsle
                    op.EnsureCreatedWithOutShardingTable = true;
                }).AddShardingTransaction((connection, builder) =>
                {
                    builder.UseSqlServer(connection);
                }).AddDefaultDataSource("ds0",
                    "Data Source=localhost;Initial Catalog=EFCoreShardingTableDB;Integrated Security=True;")
                .AddShardingTableRoute(op =>
                {
                    op.AddShardingTableRoute<SysUserVirtualTableRoute>();
                    op.AddShardingTableRoute<OrderVirtualTableRoute>();
                }).End();
        }
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

重要

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

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

!!!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();
            }
            //初始化ShardingCore
            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

# 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();
        });
1
2
3
4

在所有builder.UseSqlServer(conStr);

都改成builder.UseSqlServer(conStr).UseLoggerFactory(efLogger);

启动后我们将可以看到数据库和表会被自动创建，并且会将种子数据进行插入到内部供我们可以查询测试

Edit this page
Last update: January 7, 2022 22:00
Contributors: xuejiaming

查询

Github
Copyright © 2022 xuejiaming