Shulou(Shulou.com)06/02 Report--

Editor to share with you. Net how to use the sub-table sub-library framework ShardingCore to achieve multi-field slicing. I hope you will get something after reading this article. Let's discuss it together.

Principle

Let's assume a very simple scenario where the order time is still sliced by month, and the query uses the following statement

/ / Snowflake id is not used here because snowflake id is difficult to display in the demonstration, so use the order number for demonstration format: yyyyMMddHHmmss+new Random (). Next (0x10000). ToString (). PadLeft (4 memorials 0') var dateTime = new DateTime (2021, 11,1); var order = await _ myDbContext.Set () .Where (o = > o.OrderNotification = 202112201900001111&&o.CreateTime

< dateTime).FirstOrDefaultAsync(); 上述语句OrderNo会查询Order_202112这张表，然后时间索引会查询......Order_202108、Order_202109、Order_202110,然后两者取一个交集我们发现其实是没有结果的,这个时候应该是返回默认值null或者直接报错 这就是一个简单的原理 直接开始 接下来我将用订单编号和创建时间来为大演示,数据库采用sqlserver(你也可以换成任意efcore支持的数据库),其中编号格式yyyyMMddHHmmss+new Random().Next(0,10000).ToString().PadLeft(4,'0'),创建时间是DateTime格式并且创建时间按月分表,这边不采用雪花id是因为雪花id的实现会根据workid和centerid的不一样而出现不一样的效果,接下来我们通过简单的5步操作实现多字段分片 添加依赖 首先我们添加两个依赖,一个是ShardingCore一个EFCore.SqlServer //请安装最新版本目前x.3.2.x+,第一个版本号6代表efcore的版本号Install-Package ShardingCore -Version 6.3.2Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 6.0.1

Create an order object public class Order {public string Id {get; set;} public string OrderNo {get; set;} public string Name {get; set;} public DateTime CreateTime {get; set;}} create DbContext

Here, you simply create a dbcontext and set up how order is mapped to the database. Of course, you can use attribute instead of fluentapi.

/ if you need to support split tables, you must implement / public class DefaultDbContext:AbstractShardingDbContext,IShardingTableDbContext {public DefaultDbContext (DbContextOptions options): base (options) {} protected override void OnModelCreating (ModelBuilder modelBuilder) {base.OnModelCreating (modelBuilder) ModelBuilder.Entity (o = > {o.HasKey (p = > p.Id); o.Property (p = > p.OrderNo). IsRequired (). HasMaxLength (128) .IsUnicode (false); o.Property (p = > p.Name). IsRequired (). HasMaxLength (128) .IsUnicode (false); o.ToTable (nameof (Order));}) } public IRouteTail RouteTail {get; set;}} create a sharded route

Here we use a monthly breakdown of the order creation time.

Public class OrderVirtualRoute: AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute {/ configuration main subtable field is CreateTime, and the additional subtable field is OrderNo / public override void Configure (EntityMetadataTableBuilder builder) {builder.ShardingProperty (o = > o.CreateTime); builder.ShardingExtraProperty (o = > o.OrderNo) } / whether you want to automatically create a monthly table / public override bool AutoCreateTableByTime () {return true during the program run } / when the sub-table was created / public override DateTime GetBeginTime () {return new DateTime (2021, 9, 1) } / configure additional sharding routing rules / public override Expression GetExtraRouteFilter (object shardingKey, ShardingOperatorEnum shardingOperator, string shardingPropertyName) {switch (shardingPropertyName) {case nameof (Order.OrderNo): return GetOrderNoRouteFilter (shardingKey) ShardingOperator) Default: throw new NotImplementedException (shardingPropertyName) }} / private Expression GetOrderNoRouteFilter of the order number / private Expression GetOrderNoRouteFilter (object shardingKey, ShardingOperatorEnum shardingOperator) {/ / convert the subtable field to the order number var orderNo = shardingKey?.ToString ()? String.Empty; / / determine whether the order number is our format if (! CheckOrderNo (orderNo, out var orderTime)) {/ / return false directly if the format is different, then this query will not go through any route because it is linked by and, which can effectively prevent malicious attacks return tail = > false } / / tail var currentTail = TimeFormatToTail (orderTime) of the current time; / / because it is a monthly breakdown, get the time of the next month to determine whether the id is created at the critical point var nextMonthFirstDay = ShardingCoreHelper.GetNextMonthFirstDay (DateTime.Now); if (orderTime.AddSeconds (10) > nextMonthFirstDay) {var nextTail = TimeFormatToTail (nextMonthFirstDay) Return DoOrderNoFilter (shardingOperator, orderTime, currentTail, nextTail);} / / because it is a monthly breakdown, get the time at the beginning of the month to determine whether id is an if created at the critical point (orderTime.AddSeconds (- 10)

< ShardingCoreHelper.GetCurrentMonthFirstDay(DateTime.Now)) { //上个月tail var previewTail = TimeFormatToTail(orderTime.AddSeconds(-10)); return DoOrderNoFilter(shardingOperator, orderTime, previewTail, currentTail); } return DoOrderNoFilter(shardingOperator, orderTime, currentTail, currentTail); } private Expression DoOrderNoFilter(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] 尾巴20210101不应该被返回 if (currentMonth == shardingKey) return tail =>

String.Compare (tail, maxTail, StringComparison.Ordinal)

< 0; return tail =>

String.Compare (tail, maxTail, StringComparison.Ordinal) String.Compare (tail, maxTail, StringComparison.Ordinal) tail = = minTail;} else {return tail = > tail = = minTail | | tail = = maxTail }} default: {return tail = > true } private bool CheckOrderNo (string orderNo, out DateTime orderTime) {/ / yyyyMMddHHmmss+new Random (). Next (010000). ToString (). PadLeft (4) if (orderNo.Length = = 18) {if (DateTime.TryParseExact (orderNo.Substring (0,14), "yyyyMMddHHmmss", CultureInfo.InvariantCulture DateTimeStyles.None, out var parseDateTime)) {orderTime = parseDateTime Return true;}} orderTime = DateTime.MinValue; return false;}}

Here, let me explain why it takes so much code to slice with extra fields. In fact, this is true, because you use the order creation time CreateTime for sharding, so the assignment principle of CreateTime and OrderNo is that it should not be possible to assign values at the same time in the system. It may be a few subtleties or even femtoseconds, but in order to eliminate this difference, a critical point compatibility algorithm is used to implement it. Let's take a look at the code

Var order=new Order () / execute the id generated here is 2021-11-30 23:59:59.999.999order.OrderNo=DateTime.Now.ToString ("yyyyMMddHHmmss") + "xxx" / / business code / / the specific execution time is uncertain, even without business code, there is no way to guarantee that the two generation times are the same, of course, if you can do the same, you don't need to write so complicated at all. / / the time generated here is 2021-12-01 00:00:00.000.000order.CreateTime=DateTime.Now.

Of course, adding 10 seconds before and after adding 10 seconds in the system is a relatively conservative estimate. You can use before and after a second or even hundreds of milliseconds is ok, specific business implementation, because most of the creation time may be generated by the framework after the submission rather than new Order, of course, do not rule out this situation. Of course, if you only need to think about equal, you can only write the judgment of equal without considering all the cases.

ShardingCore launch configuration ILoggerFactory efLogger = LoggerFactory.Create (builder = > {builder.AddFilter ((category, level) = > category = = DbLoggerCategory.Database.Command.Name & & level = = LogLevel.Information). AddConsole (); var builder = WebApplication.CreateBuilder (args); / / Add services to the container.builder.Services.AddControllers (); builder.Services.AddShardingDbContext ((conStr,builder) = > builder .UseSqlServer (conStr) .UseLoggerFactory (efLogger)) .begin (o = > {o.CreateShardingTableOnStart = true) O.EnsureCreatedWithOutShardingTable = true;}) .AddShardingTransaction ((connection, builder) = > {builder.UseSqlServer (connection) .UseLoggerFactory (efLogger);}) .AddDefaultDataSource ("ds0", "DataSource = localhost;Initial Catalog=ShardingMultiProperties;Integrated Security=True;") / / if you are sqlserve, you only need to modify the link string here. AddShardingTableRoute (op = > {op.AddShardingTableRoute ()) }) .AddTableEnsureManager (sp= > new SqlServerTableEnsureManager ()) / / tell ShardingCore which tables are available at startup. End (); var app = builder.Build (); / / Configure the HTTP request pipeline.app.Services.GetRequiredService (). Start (); app.UseAuthorization (); app.MapControllers (); / / add some additional seed data using (var serviceScope = app.Services.CreateScope ()) {var defaultDbContext = serviceScope.ServiceProvider.GetService () If (! defaultDbContext.Set (). Any ()) {var orders = new List (8); var beginTime = new DateTime (2021, 9, 5); for (int I = 0; I

< 8; i++) { var orderNo = beginTime.ToString("yyyyMMddHHmmss") + i.ToString().PadLeft(4, '0'); orders.Add(new Order() { Id = Guid.NewGuid().ToString("n"), CreateTime = beginTime, Name = $"Order" + i, OrderNo = orderNo }); beginTime = beginTime.AddDays(1); if (i % 2 == 1) { beginTime = beginTime.AddMonths(1); } } defaultDbContext.AddRange(orders); defaultDbContext.SaveChanges(); }}app.Run(); 整个配置下来其实也就两个地方需要配置还是相对比较简单的,直接启动开始我们的测试模式 测试默认配置下的测试public async Task Test1() { //订单名称全表扫描 Console.WriteLine("--------------Query Name Begin--------------"); var order1 = await _defaultDbContext.Set().Where(o=>

O.Nameplate = "Order3") .FirstOrDefaultAsync (); Console.WriteLine ("- Query Name End-"); / / order number query precise positioning Console.WriteLine ("- Query OrderNo Begin-") Var order2 = await _ defaultDbContext.Set () .Where (o = > o.OrderNotification = "202110080000000003") .FirstOrDefaultAsync (); Console.WriteLine ("- Query OrderNo End-") / / the creation time query accurately locates Console.WriteLine ("- Query OrderCreateTime Begin-"); var dateTime = new DateTime (2021 10 FirstOrDefaultAsync 08); var order4 = await _ defaultDbContext.Set (). Where (o = > o.CreateTimetries = dateTime). FirstOrDefaultAsync () Console.WriteLine ("- Query OrderCreateTime End-"); / / order number in accurately locates Console.WriteLine ("- Query OrderNo Contains Begin-") Var orderNos = new string [] {"202110080000000003", "202111090000000004"}; var order5 = await _ defaultDbContext.Set (). Where (o = > orderNos.Contains (o.OrderNo)). ToListAsync (); Console.WriteLine ("- Query OrderNo Contains End-") / / order number and creation time query accurately locate no routing result throwing error or return default Console.WriteLine ("- Query OrderNo None Begin-"); var time = new DateTime (2021) Var order6 = await _ defaultDbContext.Set () .Where (o = > o.OrderNotification = "202110080000000003" & & o.CreateTime > time) .FirstOrDefaultAsync (); Console.WriteLine ("- Query OrderNo None End-") / / incorrect order number is thrown or default is returned to prevent breakdown of database Console.WriteLine ("- Query OrderNo Not Check Begin-"); var order3 = await _ defaultDbContext.Set () .Where (o = > o.OrderNo = = "a02110080000000003") .FirstOrDefaultAsync () Console.WriteLine ("- Query OrderNo Not Check End-"); return Ok ();}

Test result

The test results are perfect, except when we can't match the route, so how do we set it?

Test no route returns the default value builder.Services.AddShardingDbContext (...) .begin (o = > {.... O.ThrowIfQueryRouteNotMatch = false;// configuration does not throw an exception by default})

Let's look at the test results again.

Why do we test to query directly without going through the database? the reason is that when we do the fragment intersection of various attributes, the framework will choose to throw an exception or return the default value, and when we write the route, we judge that the format is incorrect and return return tail = > false; directly makes all the intersections empty, so we will not make a meaningless database query.

After reading this article, I believe you have a certain understanding of ".net how to use the sub-table sub-library framework ShardingCore to achieve multi-field fragmentation". If you want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.