ABP VNext 8 + MySQL 数据分表

ABP VNext 8 + MySQL 数据分表 VNext MySQL 数据分表 ABP SQL My

项目使用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会自动创建外键映射。

 

最后添加数据验证是否符合预期😊

 

评论