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+ 如果存在并发字段那么需要添加这段代码<br> /// 种子数据需要
/// </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;
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
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
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;<br><br>-----------------引用参考⬆️--------------------------<br><br>
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<ExternalContactTableRoute>();
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会自动创建外键映射。
最后添加数据验证是否符合预期
来源:https://www.cnblogs.com/kaili/p/18346556
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]