ABP VNext 8 + MySQL 数据分表
项目使用ABP框架，最近有需求数据量会持续变大，需要分表存储。
发现ShardinfCore可以快速实现EF分表操作，并且作者@薛家明还特别为ABP集成写了教程，完美的选择。
ShardinfCore作者教程很齐全，这次以ABP 8.*的用户视角进行集成记录，希望帮到需要的人。
开发环境：
ABP VNext 8.1.5 + EF 8.0.4 + ShardinfCore 7.8.1.21 + Mysql 8.2.0
新同学注意区分ABP和ABP VNext，本文用的是这个：ABP.IO - Modern ASP.NET Core Web Application Platform | ABP.IO
参考资料：
Abp VNext分表分库，拒绝手动,我们要happy coding - 薛家明 - 博客园 (cnblogs.com)
.Net下极限生产力之efcore分表分库全自动化迁移CodeFirst - 薛家明 - 博客园 (cnblogs.com)
ABP EF CORE 7 集成ShardingCore实现分表 - cnblogsName - 博客园
集成操作
添加依赖包
// 只需要在YouProjectName.EntityFrameworkCore模块中安装依赖ShardinfCore 7.8.1.21 dotnet add package ShardingCore --version 7.8.1.21
定义分表类型接口
ABP使用Guid作为主键，但ShardinfCore分表键不能接受空值，需要做一些预处理，会在下面AbstractShardingAbpDbContext的CheckAndSetShardingKeyThatSupportAutoCreate方法中编写逻辑，可以根据分表类型反射属性进行预处理。
在YouProjectName.Domain.Shared模块中，添加2个Interface：
IShardingKeyIsCreationTime：根据创建时间分表
IShardingKeyIsGuId：根据ID分表
需要分表的Entity，按需继承类型接口
/// <summary> /// 分表键是Guid类型，用于分表时继承使用 /// </summary> public interface IShardingKeyIsGuId { } /// <summary> /// 分表键是CreationTime类型，用于分表时继承使用 /// </summary> public interface IShardingKeyIsCreationTime { }
封装抽象类集成IShardingDbContext接口实现分表能力
在YouProjectName.EntityFrameworkCore模块中，添加一个抽象类，用于继承AbpDbContext和IShardingDbContext
using Microsoft.EntityFrameworkCore; using ShardingCore.Core.VirtualRoutes.TableRoutes.RouteTails.Abstractions; using ShardingCore.Extensions; using ShardingCore.Sharding.Abstractions; using System; using System.ComponentModel.DataAnnotations.Schema; using System.Threading.Tasks; using Volo.Abp.Domain.Entities; using Volo.Abp.EntityFrameworkCore; using Volo.Abp.Reflection; namespace YourProjectName.EntityFrameworkCore; /// <summary> /// 继承sharding-core接口 /// 封装实现抽象类 /// </summary> /// <typeparam name="TDbContext"></typeparam> public abstract class AbstractShardingAbpDbContext<TDbContext> : AbpDbContext<TDbContext>, IShardingDbContext where TDbContext : DbContext { private bool _createExecutor = false; protected AbstractShardingAbpDbContext(DbContextOptions<TDbContext> options) : base(options) { } private IShardingDbContextExecutor _shardingDbContextExecutor; public IShardingDbContextExecutor GetShardingExecutor() { if (!_createExecutor) { _shardingDbContextExecutor = this.DoCreateShardingDbContextExecutor(); _createExecutor = true; } return _shardingDbContextExecutor; } private IShardingDbContextExecutor DoCreateShardingDbContextExecutor() { var shardingDbContextExecutor = this.CreateShardingDbContextExecutor(); if (shardingDbContextExecutor != null) { shardingDbContextExecutor.EntityCreateDbContextBefore += (sender, args) => { CheckAndSetShardingKeyThatSupportAutoCreate(args.Entity); }; shardingDbContextExecutor.CreateDbContextAfter += (sender, args) => { var dbContext = args.DbContext; if (dbContext is AbpDbContext<TDbContext> abpDbContext && abpDbContext.LazyServiceProvider == null) { abpDbContext.LazyServiceProvider = this.LazyServiceProvider; if (dbContext is IAbpEfCoreDbContext abpEfCoreDbContext && this.UnitOfWorkManager.Current != null) { abpEfCoreDbContext.Initialize( new AbpEfCoreDbContextInitializationContext( this.UnitOfWorkManager.Current ) ); } } }; } return shardingDbContextExecutor; } private void CheckAndSetShardingKeyThatSupportAutoCreate<TEntity>(TEntity entity) where TEntity : class { if (entity is IShardingKeyIsGuId) { if (entity is IEntity<Guid> guidEntity) { if (guidEntity.Id != default) { return; } var idProperty = entity.GetObjectProperty(nameof(IEntity<Guid>.Id)); var dbGeneratedAttr = ReflectionHelper .GetSingleAttributeOrDefault<DatabaseGeneratedAttribute>( idProperty ); if (dbGeneratedAttr != null && dbGeneratedAttr.DatabaseGeneratedOption != DatabaseGeneratedOption.None) { return; } EntityHelper.TrySetId( guidEntity, () => GuidGenerator.Create(), true ); } } else if (entity is IShardingKeyIsCreationTime) { AuditPropertySetter?.SetCreationProperties(entity); } } /// <summary> /// abp 5.x+ 如果存在并发字段那么需要添加这段代码
/// 种子数据需要 /// </summary> protected override void HandlePropertiesBeforeSave() { if (GetShardingExecutor() == null) { base.HandlePropertiesBeforeSave(); } } public IRouteTail RouteTail { get; set; } public override void Dispose() { _shardingDbContextExecutor?.Dispose(); base.Dispose(); } public override async ValueTask DisposeAsync() { if (_shardingDbContextExecutor != null) { await _shardingDbContextExecutor.DisposeAsync(); } await base.DisposeAsync(); } }
改造原DbContext
打开YouProjectNameDbContext.cs文件，继承刚刚添加的AbstractShardingAbpDbContext和IShardingTableDbContext
using Microsoft.EntityFrameworkCore; using Volo.Abp.AuditLogging.EntityFrameworkCore; using Volo.Abp.BackgroundJobs.EntityFrameworkCore; using Volo.Abp.Data; using Volo.Abp.DependencyInjection; using Volo.Abp.EntityFrameworkCore; using Volo.Abp.EntityFrameworkCore.Modeling; using Volo.Abp.FeatureManagement.EntityFrameworkCore; using Volo.Abp.Identity; using Volo.Abp.Identity.EntityFrameworkCore; using Volo.Abp.OpenIddict.EntityFrameworkCore; using Volo.Abp.PermissionManagement.EntityFrameworkCore; using Volo.Abp.SettingManagement.EntityFrameworkCore; using Volo.Abp.TenantManagement; using Volo.Abp.TenantManagement.EntityFrameworkCore; using ShardingCore.Sharding.Abstractions; namespace YourProjectName.EntityFrameworkCore; [ReplaceDbContext(typeof(IIdentityDbContext))] [ReplaceDbContext(typeof(ITenantManagementDbContext))] [ConnectionStringName("Default")] public class YourProjectNameDbContext : AbstractShardingAbpDbContext<YourProjectNameDbContext>, IIdentityDbContext, ITenantManagementDbContext, IShardingTableDbContext //如果dbcontext需要实现分表功能必须实现IShardingTableDbContext { // 原工程的代码，内容不用变... }
添加ShardingMigrationsSqlGenerator类
作用是在执行Migrator程序自动迁移时，可以创建分表结构。
在YouProjectName.EntityFrameworkCore模块中新建一个文件ShardingMigrationsSqlGenerator.cs
using System; using System.Linq; using JetBrains.Annotations; using Microsoft.EntityFrameworkCore.Metadata; using Microsoft.EntityFrameworkCore.Migrations; using Microsoft.EntityFrameworkCore.Migrations.Operations; using Microsoft.EntityFrameworkCore.Update; using Pomelo.EntityFrameworkCore.MySql.Infrastructure.Internal; using Pomelo.EntityFrameworkCore.MySql.Migrations; using ShardingCore.Core.RuntimeContexts; using ShardingCore.Helpers; namespace YouProjectName.EntityFrameworkCore; public class ShardingMigrationsSqlGenerator : MySqlMigrationsSqlGenerator { private readonly IShardingRuntimeContext _shardingRuntimeContext; public ShardingMigrationsSqlGenerator(IShardingRuntimeContext shardingRuntimeContext, MigrationsSqlGeneratorDependencies dependencies, ICommandBatchPreparer commandBatchPreparer, IMySqlOptions options) : base(dependencies, commandBatchPreparer, options) { _shardingRuntimeContext = shardingRuntimeContext; } protected override void Generate( MigrationOperation operation, IModel model, MigrationCommandListBuilder builder) { var oldCmds = builder.GetCommandList().ToList(); base.Generate(operation, model, builder); var newCmds = builder.GetCommandList().ToList(); var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList(); MigrationHelper.Generate(_shardingRuntimeContext, operation, builder, Dependencies.SqlGenerationHelper, addCmds); } }
编写分表路由规则Routes
在YouProjectName.EntityFrameworkCore模块中，新建Routes文件夹
新建路由规则类，告诉分表组件按照规则进行分表
按ID分表，需要继承AbstractSimpleShardingModKeyStringVirtualTableRoute<T>
using YouProjectName.MsgAudits; using ShardingCore.Core.EntityMetadatas; using ShardingCore.VirtualRoutes.Mods; namespace YouProjectName.Routes; /// <summary> /// 根据ID分表 /// </summary> public class KeywordTableRoute : AbstractSimpleShardingModKeyStringVirtualTableRoute<Keyword> { /// <summary> /// 简单说明就是表后缀是2位，分3个表，例00,01,02 /// </summary> public KeywordTableRoute() : base(2, 3) { } public override void Configure(EntityMetadataTableBuilder<Keyword> builder) { //告诉框架通过Id字段分表 builder.ShardingProperty(o => o.Id); } }
预期是这样
按时间月份分表，需要继承AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<T>
using System; using YouProjectName.MsgAudits; using ShardingCore.Core.EntityMetadatas; using ShardingCore.VirtualRoutes.Months; namespace YouProjectName.Routes; /// <summary> /// 设置ChatRecord表的分表路由规则 /// 根据时间月份分表 /// </summary> public class ChatRecordTableRoute : AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<ChatRecord> { public override bool AutoCreateTableByTime() => true; public override void Configure(EntityMetadataTableBuilder<ChatRecord> builder) { //告诉框架通过哪个字段分表，消息最好是按消息时间分表 builder.ShardingProperty(o => o.MsgTime); //builder.ShardingProperty(o=>o.CreationTime); // 可以选择按创建时间分表 } public override DateTime GetBeginTime() { //如果按消息时间分表，那这里应该返回最早消息的时间 return new DateTime(2023, 01, 01); } }
预期是这样
添加ShardinfCore 配置
在YouProjectName.EntityFrameworkCore模块中，编辑YouProjectNameEntityFrameworkCoreModule.cs，修改ConfigureServices方法添加配置
using Microsoft.Extensions.DependencyInjection; using Volo.Abp.AuditLogging.EntityFrameworkCore; using Volo.Abp.BackgroundJobs.EntityFrameworkCore; using Volo.Abp.EntityFrameworkCore; using Volo.Abp.EntityFrameworkCore.MySQL; using Volo.Abp.FeatureManagement.EntityFrameworkCore; using Volo.Abp.Identity.EntityFrameworkCore; using Volo.Abp.Modularity; using Volo.Abp.OpenIddict.EntityFrameworkCore; using Volo.Abp.PermissionManagement.EntityFrameworkCore; using Volo.Abp.SettingManagement.EntityFrameworkCore; using Volo.Abp.TenantManagement.EntityFrameworkCore; using Volo.Abp.EntityFrameworkCore.DependencyInjection; using Microsoft.EntityFrameworkCore;
using ShardingCore; using YouProjectName.Routes; using Microsoft.EntityFrameworkCore.Migrations; using Microsoft.Extensions.Configuration;
----------------------⬆️引用参考⬆️----------------------
public override void ConfigureServices(ServiceConfigurationContext context) { var configuration = context.Services.GetConfiguration(); context.Services.AddAbpDbContext<YouProjectNameDbContext>(options => { /* Remove "includeAllEntities: true" to create * default repositories only for aggregate roots */ options.AddDefaultRepositories(includeAllEntities: true); }); Configure<AbpDbContextOptions>(options => { /* The main point to change your DBMS. * See also YouProjectNameMigrationsDbContextFactory for EF Core tooling. */ options.UseMySQL(); //分表组件增加配置 options.Configure<YouProjectNameDbContext>(innerContext => { ShardingCoreExtension.UseDefaultSharding<YouProjectNameDbContext>(innerContext.ServiceProvider, innerContext.DbContextOptions); }); }); //分表组件单独配置内容 context.Services.AddShardingConfigure<YouProjectNameDbContext>() .UseRouteConfig(op => { // op.AddShardingDataSourceRoute<TodoDataSourceRoute>(); //分库规则，这次不包含 op.AddShardingTableRoute<ChatRecordTableRoute>(); //分表规则，单表添加 op.AddShardingTableRoute<KeywordTableRoute>(); }) .UseConfig((sp, op) => { //var loggerFactory = sp.GetRequiredService<ILoggerFactory>(); op.UseShardingQuery((conStr, builder) => { builder.UseMySql(conStr, MySqlServerVersion.LatestSupportedServerVersion); }); op.UseShardingTransaction((connection, builder) => { builder.UseMySql(connection, MySqlServerVersion.LatestSupportedServerVersion); }); op.UseShardingMigrationConfigure(builder => { builder.ReplaceService<IMigrationsSqlGenerator, ShardingMigrationsSqlGenerator>(); }); op.AddDefaultDataSource("ds0", configuration.GetConnectionString("Default")); }) .AddShardingCore(); // 你的其它代码... }
使用DbMigrator创建数据库结构
到此所有的配置都完成了，尝试分表是否生效。
清空Migrations文件夹在YouProjectName.EntityFrameworkCore模块中。
执行你的YouProjectName.DbMigrator，查看表结构是否符合预期。（DbMigrator会自动生成初始化迁移）
注意：不要使用“dotnet ef database update”这种方式更新数据库，分表不会正确创建。
注意：分表不能作为其它表的外键表。
注意：不能使用Include方式操作数据，如果使用改造为Join方式查询。
注意：如果DbMigrator执行报错，查看日志排查。提醒检查是否有Entity包含Entity的属性，EF会自动创建外键映射。
最后添加数据验证是否符合预期😊
