ABP VNext 8 + MySQL 数据分表

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

来源： 时间：2024/8/30 14:48:10  点击：3  

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

 

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

 

评论

阅读排行

  1. Freezable ---探索WPF中Freezable承载数据的原理
  2. SqlSugar框架之WPF应用端功能介绍
  3. WPF 如何利用Blend给Button添加波纹效果
  4. .NET周刊【8月第4期 2024-08-25】
  5. dotnet C# 分享基础 for 循环的写法
  6. .NET8 Blazor 从入门到精通：（一）关键概念
  7. 自定义分页控件
  8. 【.NET项目分享】免费开源的静态博客生成工具EasyBlog,5分钟拥有自己的博客

最新资讯

  1. .Net PdfiumViewer 打印时无法渲染电子签名问题的解决方法
  2. .NET 内存管理两种有效的资源释放方式
  3. AOT漫谈专题(第三篇): 如何获取C#程序的CPU利用率
  4. .NET云原生应用实践（二）：Sticker微服务RESTful API的实现
  5. （系列六）.net8 全局异常捕获机制
  6. Some bytes have been replaced with the Unicode substitution character while loading file
  7. .NET 8 实现无实体库表 API 部署服务
  8. .NET程序获取当前IP经纬度，并通过经纬度实现天气查询功能