使用ShardingCore插件分表实践

1、新建Asp.Net Core WebApi项目

这里使用Visual Studio 2022，Asp.Net Core WebApi项目，.Net6框架开发分表功能

2、插件安装

使用nuget命令安装或者离线安装

PM> Install-Package ShardingCore -Version 7.7.1.7
PM> Install-Package Pomelo.EntityFrameworkCore.MySql -Version 7.0.0-alpha.1

3、准备数据库实体类

订单类

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

订单状态枚举

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

4、分表路由配置类

订单分表路由配置类，按订单产生的日期月份自动分表

/// <summary>
    /// 按月份自动分表
    /// </summary>
    public class OrderVirtualTableRoute : AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<Order>
    {
        public override bool AutoCreateTableByTime()
        {
            return true;
        }

        public override void Configure(EntityMetadataTableBuilder<Order> builder)
        {
            builder.ShardingProperty(o => o.CreationTime);//按创建时间字段分表
        }

        public override DateTime GetBeginTime()
        {
            return new DateTime(2021,1,1);
        }
    }

用户信息分表路由配置类，按照用户id取模3分表

/// <summary>
    /// 用户表按照id取模3分表
    /// </summary>
    public class SysUserVirtualTableRoute : AbstractSimpleShardingModKeyStringVirtualTableRoute<SysUser>
    {
        public SysUserVirtualTableRoute():base(2,3)
        {

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

5、数据库上下文类

负责维护数据库上下文信息，可配置实体与数据库表的对应关系

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(false).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.Name).IsRequired().IsUnicode(false).HasMaxLength(50);
                entity.Property(o => o.Area).IsRequired(false).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; }
    }

6、启动类的配置

using Microsoft.EntityFrameworkCore;
using ShardingCore;
using ShardingCore.Sharding.ReadWriteConfigurations;
using ShardingTableDemo;
var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddShardingDbContext<MyDbContext>().UseRouteConfig(op =>
{
    op.AddShardingTableRoute<SysUserVirtualTableRoute>();
    op.AddShardingTableRoute<OrderVirtualTableRoute>();
}).UseConfig(op =>
{
    op.ThrowIfQueryRouteNotMatch = false;
    op.UseShardingQuery((conStr, bd) =>
    {
        bd.UseMySql(conStr, new MySqlServerVersion(new Version())).UseLoggerFactory(StartupExtension.efLogger);
    });
    op.UseShardingTransaction((connection, bd) =>
    {
        bd.UseMySql(connection, new MySqlServerVersion(new Version())).UseLoggerFactory(StartupExtension.efLogger);
    });
    op.AddDefaultDataSource("ds0", "Server=127.0.0.1;port=3306;user=root;password=xxx1990;database=yzwDb1;");
    op.AddReadWriteSeparation(sp =>
    {
        return new Dictionary<string, IEnumerable<string>>
        {
            {"ds0",new List<string>()
            {
                "Server=127.0.0.1;port=3306;user=root;password=xxx1990;database=yzwDb1;"
            }
            }
        };
    }, ReadStrategyEnum.Loop, true);
}).AddShardingCore();

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();

var app = builder.Build();

app.UseAuthorization();

app.MapControllers();

using(var scope = app.Services.CreateScope())
{
    var myDbContext = scope.ServiceProvider.GetService<MyDbContext>();
    myDbContext.Database.EnsureCreated();
}
app.Services.UseAutoTryCompensateTable();
app.InitSeed();
app.Run();

7、数据库初始化

public static class StartupExtension
    {
        public static ILoggerFactory efLogger = LoggerFactory.Create(builder =>
        {
            builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Debug).AddConsole();
        });
        public static void InitSeed(this IApplicationBuilder app)
        {
            using(var servciceScope = app.ApplicationServices.CreateScope())
            {
                var myDbContext = servciceScope.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 user = new SysUser() { 
                            Id=i.ToString(),
                            Name=$"MyName{i}",
                            SettingCode = settings[i%3].Code
                        };
                        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),
                            CreationTime = begin.AddDays(i)
                        };
                        orders.Add(order);
                    }
                    myDbContext.AddRange(settings);
                    myDbContext.AddRange(users);
                    myDbContext.AddRange(orders);
                    myDbContext.SaveChanges();
                }
            }
        }
    }

8、启动程序，查看数据库

9、新建OrderController，测试查询、修改、删除接口

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace ShardingTableDemo.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class OrderController:ControllerBase
    {
        private MyDbContext dbContext;
        public OrderController(MyDbContext myDbContext)
        {
            this.dbContext = myDbContext;
        }
        [Route("Query")]
        public async Task<IActionResult> Query()
        {
            var sysUser = await this.dbContext.Set<SysUser>().Where(o => o.Id == "1").FirstOrDefaultAsync();
            var dateTime = new DateTime(2021, 3, 5);
            var order = await this.dbContext.Set<Order>().Where(o => o.CreationTime >= dateTime).OrderBy(o => o.CreationTime).FirstOrDefaultAsync();
            var orderIdOne = await this.dbContext.Set<Order>().FirstOrDefaultAsync(o => o.Id == "3");
            var sysUsers = await this.dbContext.Set<SysUser>().Where(o => o.Id == "1" || o.Id == "6").ToListAsync();
            return Ok(new object[] 
            {
                sysUser,
                order,
                orderIdOne,
                sysUsers
            });
        }

        [Route("QueryJoin1")]
        //分表与普通表之间的join查询
        public async Task<IActionResult> QueryJoin1()
        {
            var sql = from user in this.dbContext.Set<SysUser>().Where(o => o.Id == "1" || o.Id == "6")
                      join setting in this.dbContext.Set<Setting>()
                      on user.SettingCode equals setting.Code
                      select new
                      {
                          user.Id,
                          user.Name,
                          user.Area,
                          user.SettingCode,
                          SettingName = setting.Name
                      };
            return Ok(await sql.ToListAsync());
        }
        [Route("QueryJoin2")]
        //分表与分表之间的join查询
        public async Task<IActionResult> QueryJoin2()
        {
            var begin = new DateTime(2021, 3, 2);
            var end = new DateTime(2021, 4, 3);
            var sql1 = from user in this.dbContext.Set<SysUser>().Where(o => o.Id == "1" || o.Id == "6")
                       join order in this.dbContext.Set<Order>().Where(o => o.CreationTime >= begin && o.CreationTime <= end)
                       on user.Id equals order.Payer
                       select new
                       {
                           user.Id,
                           user.Name,
                           user.Area,
                           user.SettingCode,
                           OrderId=order.Id,
                           order.Payer,
                           order.CreationTime
                       };
            return Ok(await sql1.ToListAsync());
        }

        /// <summary>
        /// 使用EFCore追踪式修改，只更改变化了的字段
        /// </summary>
        /// <returns></returns>
        [Route("Update")]
        public async Task<IActionResult> Update()
        {
            var sysUser = await this.dbContext.Set<SysUser>().Where(o => o.Id == "1").FirstOrDefaultAsync();
            sysUser.Name = "new name";
            var i = await this.dbContext.SaveChangesAsync();
            return Ok(i);
        }

        /// <summary>
        /// 非追踪式修改，全部字段更新
        /// </summary>
        /// <returns></returns>
        [Route("UpdateNoTracking")]
        public async Task<IActionResult> UpdateNoTracking()
        {
            var sysUser = await this.dbContext.Set<SysUser>().AsNoTracking().Where(o => o.Id == "1").FirstOrDefaultAsync();
            sysUser.Name = "new name";
            this.dbContext.Update(sysUser);
            var i = await this.dbContext.SaveChangesAsync();
            return Ok(i);
        }
        [Route("Delete")]
        public async Task<IActionResult> Delete()
        {
            var sysUser = await this.dbContext.Set<SysUser>().Where(o => o.Id == "9").FirstOrDefaultAsync();
            this.dbContext.Remove(sysUser);
            var i = await this.dbContext.SaveChangesAsync();
            return Ok(i);
        }
    }
}

参考内容：初始化 | ShardingCore文档