.NET ORM 分表组件,自动分表

升级到5.1.3.25以上的MySql用户注意

以前MySql ORM创建的分表 bool类型 是bit现在是tinyint(1) ,如果2个分表类型不同查询会报错,统一改成新的tinyint(1)

一、使用场景

 (1)可扩展架构设计,比如一个ERP用5年不卡,到了10就卡了因为数据太多了,这个时候很多人都是备份然后清空数据

 (2) 数据量太多 ,例如每天都有 几十上百万的数据进入库,如果不分表后面查询将会非常缓慢

   (3)   性能瓶颈 ,数据库现有数据超过1个亿,很多情况下索引会莫名失效,性能大大下降

 

二、 内置分表使用

自带分表支持按年、按季、按月、按周、按日进行分表(支持混合使用)

2.1 定义实体

我们定义一个实体,主键不能用自增或者int ,设为long或者guid都可以,我例子就用自带的雪花ID实现分表

[SplitTable(SplitType.Year)]//按年分表 (自带分表支持 年、季、月、周、日)
[SugarTable("SplitTestTable_{year}{month}{day}")]//3个变量必须要有,这么设计为了兼容开始按年,后面改成按月、按日
 public class SplitTestTable
 {
     [SugarColumn(IsPrimaryKey =true)]
     public long Id { get; set; }
 
     public string Name { get; set; }
     
     [SugarColumn(IsNullable = true)]//设置为可空字段 (更多用法看文档 迁移)
     public DateTime UpdateTime{get;set;}
     
     [SplitField] //分表字段 在插入的时候会根据这个字段插入哪个表,在更新删除的时候用这个字段找出相关表
     public DateTime CreateTime { get; set; }
 } 
 
 //按年分表格式如下
 SplitTestTable_20220101
 
 //比如现在5月按月分表格式如下
 SplitTestTable_20220501
 
 //比如现在5月11日按日分表格式如下
 SplitTestTable_20220511
 
 //因为插入会根据实体自动建表 
 //比如何设置可空类型等设置需要知道如何配置:https://www.donet5.com/Home/Doc?typeId=1206

2.2 同步表和结构

假如分了20张表,实体类发生变更,那么 20张表可以自动同步结构,与实体一致

注意:插入会自动建表不需要这行代码,主要用于实体改动后同步多个表结构,或者一张表没有初始一张

禁止写到业务里面多次执行

//不写这行代码 你也可以用插入建表,插入用法看文档下面
db.CodeFirst
    .SplitTables()//标识分表
     .InitTables<SplitTestTable>(); //程序启动时加这一行,如果一张表没有会初始化一张

2.3 查询分表

(1)时间过滤

  通过开始时间和结束时间自动生成CreateTime的过滤并且找到对应时间的表

var list=db.Queryable<OrderSpliteTest>().SplitTable(beginDate,endDate).ToPageList(1,2); 
//注意:
//1、 分页有 OrderBy写 SplitTable 后面 ,uinon all后在排序
//2、 Where尽量写到 SplitTable 前面,先过滤在union all 
//原理:(sql union sql2) 写SplitTable 后面生成的括号外面,写前生成的在括号里面

(2) 选择最近的表

如果下面是按年分表 Take(3)  表示 只查 最近3年的 分表数据 

var list=db.Queryable<OrderSpliteTest>()
            .Where(it=>it.Pk==Guid.NewGuid())
            .SplitTable(tabs => tabs.Take(3))//近3张,也可以表达式选择
            .ToList();

(3)精准定位一张表

根据分表字段的值可以精准的定位到具体是哪一个分表,比Take(N)性能要高出很多

var name=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);//根据时间获取表名
var list=Db.Queryable<SplitTestTable>()As(name).Where(it => it.Id==data.Id).ToList();
//修改、删除、更新都可以用As(Name)

(4) 表达式定位哪几张表

Db.Queryable<SplitTestTable>()
          .Where(it => it.Id==data.Id)
          .SplitTable(tas => tas.Where(y=>y.TableName.Contains("2019")))//表名包含2019的表
          .ToList();

(5)分表Join正常表

//分表使用联表查询
var list=db.Queryable<Order>() // Order是分表
.SplitTable(tabs=>tabs.Take(3)) //可以换成1-8的所有分表写法,不是只能take
.LeftJoin<Custom>((o,c)=>o.CustomId==c.Id)//Custom正常表
.Select((o,c)=>new { name=o.Name,cname=c.Name }).ToPageList(1,2); 

(6)分表JOIN分表

var rightQuery=db.Queryable<Custom>().SplitTable(tabs=>tabs.Take(3)) ;
var list=db.Queryable<Order>().SplitTable(tabs=>tabs.Take(3)) 
.LeftJoin<Custom>(rightQuery,(o,c)=>o.CustomId==c.Id) // Join  rightQuery
.Select((o,c)=>new { name=o.Name,cname=c.Name }).ToPageList(1,2); 

(7)性能优化

条件尽可能写在SplitTable前面,因为这样会先过滤在合并

.Where(it => it.Pk == Guid.NewGuid()) //先过滤
.SplitTable(tabs => tabs.Take(3))//在分表

(8)所有分表检索(没办法精确过滤表时用)

//如果是主键查询哪怕100个分表都很快 
var list = db.Queryable<OrderSpliteTest>()
         .Where(it => it.Pk == Guid.NewGuid()) //适合有索引列,单条或者少量数据查询
          .SplitTable(tabs => tabs).ToList();//没有条件就是全部表


2.3 插入 

因为我们用的是Long所以采用雪花ID插入(guid也可以禁止使用自增列), 实体结构看上面 3.1

注意:.SplitTable不要漏掉了

var data = new SplitTestTable()
{
      CreateTime=Convert.ToDateTime("2019-12-1"),
      Name="jack"
 };
 //雪花ID
 db.Insertable(data).SplitTable().ExecuteReturnSnowflakeIdList();//插入并返回雪花ID并且自动赋值ID   
  //服务器时间修改、不同端口用同一个代码、多个程序插入一个表都需要用到WorkId
  //保证WorkId唯一 ,程序启动时配置 SnowFlakeSingle.WorkId=从配置文件读取;
 
 
 //GUID
  db.Insertable(data).SplitTable().ExecuteCommand();//插入GUID 自动赋值 ID
  
 //大数据写入
  db.Fastest<OrderSpliteTest>().SplitTable().BulkCopy(List<OrderSpliteTest>);
 //大数据写入方式如果用到雪花ID需要手动赋值:SnowFlakeSingle.Instance.NextId()
 //部分数据库需配置 具体用法看文档: https://www.donet5.com/Home/Doc?typeId=2404
 
// sql 如下
//INSERT INTO [SplitTestTable_20190101] --如果表不存在会自动建表
//           ([Id],[Name],[CreateTime])
//     VALUES
//           (@Id,@Name,@CreateTime)

批量插入 因为我们是根据CreateTime进行的分表,生成的SQL语句如下:

var datas = new List<SplitTestTable>(){
new SplitTestTable(){CreateTime=Convert.ToDateTime("2019-12-1"),Name="jack"} ,
new SplitTestTable(){CreateTime=Convert.ToDateTime("2022-02-1"),Name="jack"},
new SplitTestTable(){CreateTime=Convert.ToDateTime("2020-02-1"),Name="jack"},
new SplitTestTable(){CreateTime=Convert.ToDateTime("2021-12-1"),Name="jack"}
};

db.Insertable(datas).SplitTable().ExecuteReturnSnowflakeIdList();//插入返回雪花ID集合

 执行完生成的表

生成的Sql: 

自动识别4条记录,分别插入4个不同的表中  

2.4 删除数据 

(1)推荐用法:新功能 5.0.7.7 preview及以上版本

 //直接根据实体集合删除 (全自动 找表插入)
 db.Deleteable(deleteList).SplitTable().ExecuteCommand();//,SplitTable不能少

(2)最近3张表都执行一遍删除

db.Deleteable<SplitTestTable>().In(id).SplitTable(tas=>tas.Take(3)).ExecuteCommand();

(3)精准删除     

 相对于上面的操作性能更高,可以精准找到具体表

var deldata = new SplitTestTable()
            {
                Id = id,
                CreateTime = DateTime.Now
            };
 var tableName = db.SplitHelper(deldata).GetTableNames();
 db.Deleteable<SplitTestTable>().As(tableName).Where(deldata).ExecuteCommand();
 //DELETE FROM [SplitTestTable_20210101] WHERE [Id] IN (1454676863531089920)


2.5 更新数据    

推荐用法: 新功能 5.0.7.7 preview及以上版本 

//直接根据实体集合更新 (全自动 找表更新)
db.Updateable(updateList).SplitTable().ExecuteCommand();//.SplitTable()不能少

//BulkCopy分表更新
db.Fastest<OrderSpliteTest>().SplitTable().BulkUpdate(List<OrderSpliteTest>);
//部分数据库需配置 具体用法看文档: https://www.donet5.com/Home/Doc?typeId=2404

更多用法:

//更新近3张表
db.Updateable(deldata).SplitTable(tas=>tas.Take(3)).ExecuteCommand();

//精准找单个表
db.Updateable(deldata).As(tableName).ExecuteCommand();//实体
db.Updateable<TestEnity>().As(tableName).SetColumns(..).Where(...).ExecuteCommand();//表达式

//通过表达式过滤出要更新的表
db.Updateable(deldata).SplitTable(tas => tas.Where(y=>y.TableName.Contains("_2019"))).ExecuteCommand();

2.6 辅助方法

ORM不是所有功能都支持分表,我们可以分表辅助方法实现

//用例1:获取所有表表名 ,可以用于循环处理每个表
var tables=db.SplitHelper<Order>().GetTables();


//例2:根据分表字段的值获取表名
var tableName = db.SplitHelper<Order>().GetTableName(DateTime.Now);//有重载
db.Updateable(data).As(tableName).ExecuteCommand()


//例3:根据当前对象获取表名,有这个功能就可以把List进行分类
var tableNames =db.SplitHelper(List<T> dataList)).GetTableNames();//根据实体集合获取表名集合
var tableName =db.SplitHelper(T data).GetTableName();//根据实体获取表名 

Order_20220101- [记录1,记录2 ....]
Order_20210101- [记录1,记录2 ...]

Db.Storageable(Order_20220101所有记录集合).As("Order_20220101").ExecuteCommand()

2.7唯一判段

唯一列要加上索引,这样性能才能保证上百张表里面也不会慢

    if (!db.Queryable<OrderSpliteTest>().SplitTable(tabs => tabs).Any(it => it.Name==p)) 
    {
       //唯一不存在
    }


三、 自定义分表:按单词

疑问: 自定义分表可以支持多个字段

答:可以的,只要你重写的方法兼容多个字段就可以

上面的分表功能是我们自带集成的,比如我想实现自定义的分表我该如何实现呢?

3.1 按首字母拼音分表

 我们就写个按24个字母进行分表的小例子,来学习一下如何自定义分表

3.2  创建分表类

我们新建一个类继承成ISplitTableService 接口 

 public interface ISplitTableService
 {
        //获取表名用于 SplitTable tas 筛选
        List<SplitTableInfo> GetAllTables(ISqlSugarClient db,EntityInfo EntityInfo,List<DbTableInfo> tableInfos);
        //获取默认表名
        string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo);
        string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo, SplitType type);
        //根据分表字段获取表名
        string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object fieldValue);
        //获取分表字段的值
        object GetFieldValue(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object entityValue);
 }

3.3  使用自定义分表

创建一个WordSplitService.cs继承ISplitTableService

用例下载: 

USplit.rar


//配置自定义分表
db.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService =new WordSplitService();

//插入数据
db.Insertable(new WordTestTable(){CreateTime=DateTime.Now,Name="BC"}).SplitTable().ExecuteReturnSnowflakeId();
db.Insertable(new WordTestTable(){CreateTime=DateTime.Now,Name="AC"}).SplitTable().ExecuteReturnSnowflakeId();
db.Insertable(new WordTestTable(){CreateTime=DateTime.Now,Name="ZBZ"}).SplitTable().ExecuteReturnSnowflakeId();  

 执行完数据库就多了3张表,因为是按首字母分的表 ,插入了3条记录自动创建了3张表,插入生成的SQL:

INSERT INTO [WordTestTable_FirstB]
           ([Id],[Name],[CreateTime])
     VALUES
           (@Id,@Name,@CreateTime) ;
INSERT INTO [WordTestTable_FirstA]
           ([Id],[Name],[CreateTime])
     VALUES
           (@Id,@Name,@CreateTime) ;
INSERT INTO [WordTestTable_FirstZ]
           ([Id],[Name],[CreateTime])
     VALUES
           (@Id,@Name,@CreateTime) ;

查询分表

//查询字母A开头的分
var listall = db.Queryable<WordTestTable>().Where(it => it.Name == "all").SplitTable(tas => tas.ContainsTableNames("_FirstA")).ToList();    
//生成的SQL:
//SELECT * FROM  (SELECT [Id],[Name],[CreateTime] FROM [WordTestTable_FirstA]  WHERE ( [Name] = @Name0UnionAll1 )) unionTable 

 

四、自定义分表2:按年月

我们自带的时间分表格式为  xx_20220101这种,如果我想格式为 xx_202201 那么我们可以用自定义分表

查看:https://www.donet5.com/Ask/9/16110

注意:自定义的时间分表是没办法后期改变分表类型,比如你设置了按月那么以后就不能改按周,如果想扩展性强用自带的时间分表


五、视频教程

https://www.bilibili.com/video/BV13B4y1h7Wu?p=4


六、时序数据库

上面介绍的都是关系型数据库对业务进行分表,如果使用时序数据库 那么数据库会自动分表

并且使用和正常表一样, 分表的事情都交给数据库,当成正常表去用就行了

https://www.donet5.com/Home/Doc?typeId=2434


七、老表数据迁移分表

步骤1:

老表改名为 xxxxxx ( 随便什么名字), 防止和分表的表名冲突引起错误

如果比较慢:你也可以改新建的名字 比如以前叫 order现在改 OrderNew

步骤2:

var pageList = new List<新类>();//主键不能是自增
var pageSize=200000;//每次读取20万 (数据库是1-20万 20万-40万这样读取)
db.Queryable<object>().As("xxxxxx").Select<新类>().ForEach(it =>{
                    pageList.Add(it);
                    if (pageList.Count==pageSize) //每个分页的最一次执行,防止循操作库 
                    {
                      db2.Fastest<新类>().SplitTable().BulkCopy(pageList);//插入分批数据
                      pageList = new List<新类>();//清空
                         
                    }
                },pageSize);//设置分页 
 db2.Fastest<新类>().SplitTable().BulkCopy(pageList);//插入剩余的 (最后一页可能有不足20万的)


文档:SqlSugar5.0