首页 > 数据库 >SqlSugar分表

SqlSugar分表

时间:2024-01-16 15:58:10浏览次数:40  
标签:Name SplitTable db var 分表 CreateTime SqlSugar

一、使用场景

 (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 { getset; }         public string Name { getset; }             [SugarColumn(IsNullable = true)]//设置为可空字段 (更多用法看文档 迁移)      public DateTime UpdateTime{get;set;}             [SplitField] //分表字段 在插入的时候会根据这个字段插入哪个表,在更新删除的时候用这个字段找出相关表      public DateTime CreateTime { getset; }  }      //按年分表格式如下  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);    //结合Where var list=db.Queryable<OrderSpliteTest>().Where(it=>it.Id>0).SplitTable(beginDate,endDate).ToPageList(1,2);    //注意: //1、 分页有 OrderBy写 SplitTable 后面 ,uinon all后在排序 //2、 Where尽量写到 SplitTable 前面,先过滤在union all  //原理:(sql union sql2) 写SplitTable 后面生成的括号外面,写前生成的在括号里面

2.3.2 查询: 选择最近的表

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

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

2.3.3 查询: 精准定位一张表

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

var name=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);//根据时间获取表名   //推荐: 表不存在不会报错 var list=db.Queryable<OrderSpliteTest>().SplitTable(tabs => tabs.InTableNames(name)).ToList()   //不推荐:查询不推荐用,删除和更新可以用 var list=Db.Queryable<SplitTestTable>().AS(name).Where(it => it.Id==data.Id).ToList(); //修改、删除、更新都可以用As(Name)

2.3.4 查询: 表达式定位哪几张表

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

2.3.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); 

2.3.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);      //技巧:如果是单表分表没有表返回第一个表可以防止报错  升级到:5.1.4.127 + SplitTable(it=>it.ContainsTableNamesIfNullDefaultFirst("table"))

2.3.7 查询: 性能优化

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

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

2.3.8 查询: 所有分表检索

没办法精确过滤表时用,Where一定要写SplitTable前面

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

 

2.4 插入 

因为我们用的是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>);//自动找表大数据写入     //不会自动建表 如果表100%存在用这个性能好些  db.Fastest<OrderSpliteTest>().AS(表名).BulkCopy(List<OrderSpliteTest>);//固定表大数据写入        //大数据写入方式如果用到雪花ID需要手动赋值:SnowFlakeSingle.Instance.NextId()  //部分数据库需配置 具体用法看文档: https://www.donet5.com/Home/Doc?typeId=2404

批量插入 因为我们是根据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.5 删除数据 

(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 tableName=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);//根据时间获取表名 db.Deleteable<SplitTestTable>().AS(tableName).Where(deldata).ExecuteCommand();  //DELETE FROM [SplitTestTable_20210101] WHERE [Id] IN (1454676863531089920)

(4)范围删除

var tables = db.SplitHelper<OrderSpliteTest>().GetTables().Take(3);//近3张分表 foreach (var item in tables) {    //删除1点到6点时间内数据    db.Deleteable<OrderSpliteTest>() .AS(item.TableName)//使用当前分表名                      .Where(it => it.Time.Hour < 1&&it.Time.Hour<6)                     .ExecuteCommand(); }

 

2.6 更新数据    

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

//直接根据实体集合更新 (全自动 找表更新) db.Updateable(updateList).SplitTable().ExecuteCommand();//.SplitTable()不能少   //BulkCopy分表更新 db.Fastest<OrderSpliteTest>().SplitTable().BulkUpdate(List<OrderSpliteTest>); //部分数据库需配置 具体用法看文档:      //范围更新 var tables = db.SplitHelper<OrderSpliteTest>().GetTables().Take(3);//近3张分表 foreach (var item in tables) {    //更新1点到6点时间内数据    db.Updateable<OrderSpliteTest>() .AS(item.TableName)//使用分表名                     .SetColumns(it=>new OrderSpliteTest(){ Static=1 })                     .Where(it => it.Time.Hour < 1&&it.Time.Hour<6)                     .ExecuteCommand(); }

更多用法:

//更新近3张表 db.Updateable(deldata).SplitTable(tas=>tas.Take(3)).ExecuteCommand();   //精准找单个表 var tableName=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);//根据时间获取表名 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.7 辅助方法

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.8 唯一判段

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

if (!db.Queryable<OrderSpliteTest>().SplitTable().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

 

//配置方式1:配置自定义分表 db.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService =new WordSplitService();   //配置方式2:高版本支持了特性使用自定义分表 5.1.4.78 [SplitTable(SplitType._Custom01,typeof(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:按年月

支持2种方式配置自定义分表

//配置方式1:配置自定义分表 db.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService =new yyyyMMService();   //配置方式2:高版本支持了特性使用自定义分表 5.1.4.78 [SplitTable(SplitType.Month,SplitType._Custom01,typeof(yyyyMMService))]

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

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

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

 

五、性能优化

(1) 分表字段要建索引

(2) where写到SplitTable前面

var list=db.Queryable<OrderSpliteTest>() Where(it=>it.id>1)//where写SplitTable前面 .SplitTable(beginDate,endDate).ToPageList(1,2)

(3) 如果分页可以不查询count,给个固定前10页 (特别mysql查询count是比较慢的)

六、视频教程

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万的)

 

标签:Name,SplitTable,db,var,分表,CreateTime,SqlSugar
From: https://www.cnblogs.com/yswenli/p/17967843

相关文章

  • SqlSugar多库/多租户
    1、多库和多租户如果是接口ISugarClient先看一下标题6,看完在看这儿1.1固定多数据库模式数据库数量是固定的一般在声明的全部加上//通过ConfigId进行数据库区分var db = new SqlSugarClient(new List<ConnectionConfig>(){ //这儿声名所有上下文都生效 new ......
  • SqlSugar常见问题汇总
    1、已有打开的与此Command相关联的DataReader,必须首先将它关闭。ThereisalreadyanopenDataReaderassociatedwiththisConnectionwhichmustbeclosedfirst.或者出现connectionisclosed出现这个错一般是线程安全引起的解决方案: https://www.donet5.com/Home/......
  • SqlSugar的Repository
    1、仓储说明仓储可以让你的方法更加的规范,需要什么方法都封装到仓储中,下次就能重复使用,并且能很好的和你业务拆分开 这种设计模式简单粗暴用起来也方便,文章下面有可以运行的DEMO 2、仓储方法仓储有一套自带的数据库操作方法,比起db.xx.xxx来说可能更简便些满足一些常用需......
  • SqlSugar的查询函数SqlFunc
    用法我们可以使用SqlFunc这个类调用Sql函数,用法如下:db.Queryable<Student>().Where(it => SqlFunc.ToLower(it.Name) == SqlFunc.ToLower("JACK")).ToList(); C#函数支持一些常用的C#函数.ToString .Contains .Length.ToLower .ToUpper .ToSubstrin......
  • SqlSugar的Where用法
    1、普通表达式查询//id=@idvar list=db.Queryable<Student>().Where(it => it.Id == id).ToList(); //多个条件var list2=db.Queryable<Student>().WhereIF(id>0,it => it.Id == id)// 如果id>0 添加条件 id=@id  .WhereIF(name!=null,it => it.nam......
  • SqlSugar的Select用法
    Select位置正常情况:应该在最后面,一般是.Where(..).OrderBy(..).Select(..).ToList()特殊情况:如果Select不是最后一个位置,则Select要加MergeTable()合并成一个表,Select(...).MergeTable().Where语法糖:Select(...).MergeTable()在新版本中可以用SelectMergeTable(it=>newx......
  • SqlSugar跨库查询/多库查询
    一、跨库方式1:跨库导航(5.1.3.24)优点1:支持跨服务器,支持跨数据库品种,支持任何类型数据库 优点2:  超级强大的性能,能达到本库联表性能缺点:不支持子表过滤主表(方案有ToList后在内存过滤,如果分页可以查前1000条主表在内存分页前端只显示前10页)[Tenant("db2")] //......
  • SqlSugar新增数据
    1、插入方式1.1单条插入实体//返回插入行数db.Insertable(insertObj).ExecuteCommand(); //都是参数化实现//异步: await db.Insertable(insertObj).ExecuteCommandAsync() //插入返回自增列 (实体除ORACLE外实体要配置自增,Oracle需要配置序列)db.Insertable(in......
  • SqlSugar更新数据
    1、根据实体对象更新所谓按实体对象更新就是:db.Updateable(参数对象)有参数的重载db.Updateable(实体或者集合).ExecuteCommand() //右标题1 下面的所有菜单优点1、代码少2、支持各种批量修改缺点1、不支持表达式和sql函数2、依赖 实体对象,没实体对象就需......
  • SqlSugar删除数据
    1、根据实体删除1.1强类型实体 需要配置主键,根据主键删除需要给实体配置主键,参考文档实体配置//单个实体db.Deleteable<Student>(new Student() { Id = 1 }).ExecuteCommand(); //List<实体> (可以不加Where)List<Student> list=new List<Student>(){......