.NET sub-database and table high performance: waterfall paging

Description

• Through the above optimization, the high performance O(1) of streaming aggregation query under sequential query can be guaranteed

• Through the above optimization, the client fragmentation can be guaranteed to have the control of the minimum number of connections

• Setting a reasonable primary key can effectively solve our performance optimization under big data sharding

Practice

ShardingCore is currently continuously optimizing sharding queries and as much as possible without business code intrusion to achieve high-performance sharding query aggregation.

Next, I will show you the only fully automatic routing, multi-field fragmentation, code-free intrusion, high-performance sequential query framework fragmentation function in the traditional database field under dotnet , If you have used it I believe you will fall in love with him.

The first step to install dependencies

# ShardingCore core framework version 6.4.2.4 +
PM> Install-Package ShardingCore
# The database driver here chooses the community driver of mysql, the latest version of efcore6 is enough
PM> Install-Package Pomelo.EntityFrameworkCore.MySql

The second step is to add objects and context

Many friends have asked me if I must use fluentapi to use ShardingCore, it’s just a personal preference, and here I use dbset + attribute to achieve

//article table
[Table(nameof(Article))]
public class Article
{
    [MaxLength(128)]
    [Key]
    public string Id { get; set; }
    [MaxLength(128)]
    [Required]
    public string Title { get; set; }
    [MaxLength(256)]
    [Required]
    public string Content { get; set; }
    
    public DateTime PublishTime { get; set; }
}
context
public class MyDbContext:AbstractShardingDbContext, IShardingTableDbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
Add methods that will cause the model of efcore to be loaded in advance, such as Database.xxxx
    }

    public IRouteTail RouteTail { get; set; }
    
    public DbSet<Article> Articles { get; set; }
}

The third step is to add article routing

public class ArticleRoute:AbstractSimpleShardingDayKeyDateTimeVirtualTableRoute<Article>
{
    public override void Configure(EntityMetadataTableBuilder<Article> builder)
    {
        builder.ShardingProperty(o => o.PublishTime);
    }

    public override bool AutoCreateTableByTime()
    {
        return true;
    }

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

So far basically Article has supported the score table by day

The fourth step is to add query configuration to let the framework know that we are sequentially splitting tables and defining the order of splitting tables

public class TailDayReverseComparer : IComparer<string>
{
    public int Compare(string? x, string? y)
    {
        //The program uses positive order by default, that is, it is sorted in positive order by time. We need to use reverse order, so we can directly call the original comparator and multiply by negative one.
        return Comparer<string>.Default.Compare(x, y) * -1;
    }
}
//The review condition that the current query satisfies must be the query of a single shard object, which can be joined to a common non-shard table
public class ArticleEntityQueryConfiguration: IEntityQueryConfiguration<Article>
{
    public void Configure(EntityQueryBuilder<Article> builder)
    {
        / / Set the default framework for the sort order of Article, here is the reverse order
        builder.ShardingTailComparer(new TailDayReverseComparer());
        The following settings have the same effect as the above, so that the framework really uses reverse order for Article suffix sorting
        //builder.ShardingTailComparer(Comparer<string>.Default, false);
        
        // Briefly explain the meaning of the following configuration
        //Which attribute of the first parameter table name Article is sorted in order is the same as Tail sorted by day. PublishTime is used here
        //The second parameter indicates whether the attribute PublishTime asc is consistent with the ShardingTailComparer configured above, and true means it is consistent. Obviously, this is the opposite because the tail sorting is reversed by default.
        //The third parameter indicates whether it is an Article attribute, and it is also possible to set the same name here, because considering the select of the anonymous object
        builder.AddOrder(o => o.PublishTime, false,SeqOrderMatchEnum.Owner|SeqOrderMatchEnum.Named);
        //The id used here for demonstration is a simple time format, so it is the same as the time configuration
        builder.AddOrder(o => o.Id, false,SeqOrderMatchEnum.Owner|SeqOrderMatchEnum.Named);
        //Set here what kind of sorting method to use if the query does not have the order with the above configuration by default
        //The first parameter indicates whether it is the same as the configuration of ShardingTailComparer. The current configuration is in reverse order, that is, the query starts from the latest time. If it is false, the query starts from the earliest time
        //The fuse is configured later, that is, the fuse condition is reviewed, such as FirstOrDefault, which can be blown only if one is satisfied
        builder.AddDefaultSequenceQueryTrip(true, CircuitBreakerMethodNameEnum.Enumerator, CircuitBreakerMethodNameEnum.FirstOrDefault);

        //The configuration here is the limit on the number of connections opened by default when using the sequential query configuration. The startup can set a default number of threads that is the current cpu at the beginning. This is optimized to only need one thread. Of course, if Cross-table, then it is serial execution
        builder.AddConnectionsLimit(1, LimitMethodNameEnum.Enumerator, LimitMethodNameEnum.FirstOrDefault);
    }
}

Step 5 Add configuration to route

public class ArticleRoute:AbstractSimpleShardingDayKeyDateTimeVirtualTableRoute<Article>
{
    //Omit.....
    public override IEntityQueryConfiguration<Article> CreateEntityQueryConfiguration()
    {
        return new ArticleEntityQueryConfiguration();
    }
}

Step 6 startup configuration

var builder = WebApplication. CreateBuilder(args);

// Add services to the container.
ILoggerFactory efLogger = LoggerFactory. Create(builder =>
{
    builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name & amp; & amp; level == LogLevel.Information).AddConsole();
});
builder.Services.AddControllers();
builder.Services.AddShardingDbContext<MyDbContext>()
    .AddEntityConfig(o =>
    {
        o.CreateShardingTableOnStart = true;
        o.EnsureCreatedWithOutShardingTable = true;
        o.AddShardingTableRoute<ArticleRoute>();
    })
    .AddConfig(o =>
    {
        o.ConfigId = "c1";
        o.UseShardingQuery((conStr, b) =>
        {
            b.UseMySql(conStr, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
        });
        o.UseShardingTransaction((conn, b) =>
        {
            b.UseMySql(conn, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
        });
        o.AddDefaultDataSource("ds0", "server=127.0.0.1;port=3306;database=ShardingWaterfallDB;userid=root;password=root;");
        o.ReplaceTableEnsureManager(sp => new MySqlTableEnsureManager<MyDbContext>());
    }).EnsureConfig();

var app = builder. Build();

app.Services.GetRequiredService<IShardingBootstrapper>().Start();
using (var scope = app. Services. CreateScope())
{
    var myDbContext = scope. ServiceProvider. GetRequiredService<MyDbContext>();
    if (!myDbContext. Articles. Any())
    {
        List<Article> articles = new List<Article>();
        var beginTime = new DateTime(2022, 3, 1, 1, 1,1);
        for (int i = 0; i < 70; i ++ )
        {
            var article = new Article();
            article.Id = beginTime.ToString("yyyyMMddHHmmss");
            article.Title = "Title" + i;
            article.Content = "Content" + i;
            article.PublishTime = beginTime;
            articles. Add(article);
            beginTime = beginTime.AddHours(2).AddMinutes(3).AddSeconds(4);
        }
        myDbContext. AddRange(articles);
        myDbContext. SaveChanges();
    }
}
app. MapControllers();

app.Run();

The seventh step is to write the query expression

public async Task<IActionResult> Waterfall([FromQuery] string lastId,[FromQuery]int take)
{
    Console.WriteLine($"-----------start query, lastId:[{lastId}],take:[{take}]-----------");
    var list = await _myDbContext.Articles.WhereIf(o => String.Compare(o.Id, lastId) < 0,!string.IsNullOrWhiteSpace(lastId)).Take(take)..OrderByDescending(o => o.PublishTime) ToListAsync();
    return Ok(list);
}

Run the program

Because the 07 table does not exist, this query will query the 07 and 06 tables, and then we will perform the next pagination and pass in the last id

Because there is no rule for fragment routing of Article.Id, there is no way to filter the id, then we configure the fragmentation rules of Id

First write code for ArticleRoute

public class ArticleRoute:AbstractSimpleShardingDayKeyDateTimeVirtualTableRoute<Article>
{
    public override void Configure(EntityMetadataTableBuilder<Article> builder)
    {
        builder.ShardingProperty(o => o.PublishTime);
        builder.ShardingExtraProperty(o => o.Id);
    }

    public override bool AutoCreateTableByTime()
    {
        return true;
    }

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

    public override IEntityQueryConfiguration<Article> CreateEntityQueryConfiguration()
    {
        return new ArticleEntityQueryConfiguration();
    }

    public override Expression<Func<string, bool>> GetExtraRouteFilter(object shardingKey, ShardingOperatorEnum shardingOperator, string shardingPropertyName)
    {
        switch (shardingPropertyName)
        {
            case nameof(Article.Id): return GetArticleIdRouteFilter(shardingKey, shardingOperator);
        }

      return base.GetExtraRouteFilter(shardingKey, shardingOperator, shardingPropertyName);
    }
    /// <summary>
    /// The route of the article id
    /// </summary>
    /// <param name="shardingKey"></param>
    /// <param name="shardingOperator"></param>
    /// <returns></returns>
    private Expression<Func<string, bool>> GetArticleIdRouteFilter(object shardingKey,
        ShardingOperatorEnum shardingOperator)
    {
        //Convert the sub-table field into an order number
        var id = shardingKey?.ToString()  string.Empty;
        //Determine whether the order number is in the format we meet
        if (!CheckArticleId(id, out var orderTime))
        {
            //If the format is different, return false directly, then this query will not go through any routing because it is linked by and, which can effectively prevent malicious attacks
            return tail => false;
        }

        //Tail at the current time
        var currentTail = TimeFormatToTail(orderTime);
        //Because the table is divided by month, get the time of the next month to judge whether the id is created at the critical point
        //var nextMonthFirstDay = ShardingCoreHelper.GetNextMonthFirstDay(DateTime.Now);//This is wrong
        var nextMonthFirstDay = ShardingCoreHelper. GetNextMonthFirstDay(orderTime);
        if (orderTime. AddSeconds(10) > nextMonthFirstDay)
        {
            var nextTail = TimeFormatToTail(nextMonthFirstDay);
            return DoArticleIdFilter(shardingOperator, orderTime, currentTail, nextTail);
        }
        //Because the table is divided by month, get the time at the beginning of this month to judge whether the id is created at the critical point
        //if (orderTime.AddSeconds(-10) < ShardingCoreHelper.GetCurrentMonthFirstDay(DateTime.Now))//This is wrong
        if (orderTime.AddSeconds(-10) < ShardingCoreHelper.GetCurrentMonthFirstDay(orderTime))
        {
            //Last month tail
            var previewTail = TimeFormatToTail(orderTime. AddSeconds(-10));

            return DoArticleIdFilter(shardingOperator, orderTime, previewTail, currentTail);
        }

        return DoArticleIdFilter(shardingOperator, orderTime, currentTail, currentTail);

    }

    private Expression<Func<string, bool>> DoArticleIdFilter(ShardingOperatorEnum shardingOperator, DateTime shardingKey, string minTail, string maxTail)
    {
        switch (shardingOperator)
        {
            case ShardingOperatorEnum. GreaterThan:
            case ShardingOperatorEnum. GreaterThanOrEqual:
                {
                    return tail => String.Compare(tail, minTail, StringComparison.Ordinal) >= 0;
                }

            case ShardingOperatorEnum. LessThan:
                {
                    var currentMonth = ShardingCoreHelper. GetCurrentMonthFirstDay(shardingKey);
                    //At critical value o=>o.time < [2021-01-01 00:00:00] tail 20210101 should not be returned
                    if (currentMonth == shardingKey)
                        return tail => String.Compare(tail, maxTail, StringComparison.Ordinal) < 0;
                    return tail => String.Compare(tail, maxTail, StringComparison.Ordinal) <= 0;
                }
            case ShardingOperatorEnum.LessThanOrEqual:
                return tail => String.Compare(tail, maxTail, StringComparison.Ordinal) <= 0;
            case ShardingOperatorEnum.Equal:
                {
                    var isSame = minTail == maxTail;
                    if (isSame)
                    {
                        return tail => tail == minTail;
                    }
                    else
                    {
                        return tail => tail == minTail || tail == maxTail;
                    }
                }
            default:
                {
                    return tail => true;
                }
        }
    }

    private bool CheckArticleId(string orderNo, out DateTime orderTime)
    {
        //yyyyMMddHHmmss
        if (orderNo. Length == 14)
        {
            if (DateTime.TryParseExact(orderNo, "yyyyMMddHHmmss", CultureInfo.InvariantCulture,
                    DateTimeStyles. None, out var parseDateTime))
            {
                orderTime = parseDateTime;
                return true;
            }
        }

        orderTime = DateTime. MinValue;
        return false;
    }
}

Complete routing: Multi-field fragmentation for Id and support greater than less than sorting

The above is the optimization of multi-field sharding. For more details, you can click here for the blog. You have to look at the sub-table and sub-database solution under .Net-multi-field sharding

Then we continue to query to see the results

The same goes for the third page

DEMO:https://github.com/xuejmnet/ShardingWaterfallApp

Summary

Although the current framework is a very young framework, I believe that my performance optimization in the field of sharding should not find the second one under all the existing frameworks of .net, and the whole framework also supports union all aggregation, which can It satisfies the query of special statements listed in group + first, and has high performance. It is not only a fully automatic sharding but also a high-performance framework with many features and performance. The goal is to squeeze out the last performance of client sharding. .

at last

As a dotnet programmer, I believe that our previous shard selection scheme did not have a good shard selection except for mycat and shardingsphere-proxy, but I believe that through Analysis of the principle of ShardingCore, you can not only understand the knowledge points of sharding under big data, but also participate in it or implement one by yourself. I believe that only when you understand the principle of sharding, dotnet will have more For good talents and future, we need not only elegant packaging, but also an understanding of the principles.

I believe that the ecology of dotnet will gradually improve in the future with this almost perfect grammar

Reposted from: Xue Jiaming

Link: cnblogs.com/xuejiaming/p/15966501.html

-

Technical group: Add Xiaobian WeChat and comment into the group

Editor WeChat: mm1552923

Public number: dotNet Programming Daquan