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 { 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);　
 
//结合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万的）

posted @ 2024-01-16 15:45 yswenli 阅读(1131) 评论(0) 编辑收藏举报

刷新页面返回顶部

yswenli

http://www.cnblogs.com/yswenli/

SqlSugar分表

二、内置分表使用

2.1 定义实体

2.2 同步表和结构

2.3.1 查询: 时间过滤

2.3.2 查询: 选择最近的表

2.3.3 查询: 精准定位一张表

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

2.3.5 查询: 分表Join正常表

2.3.6 查询: 分表JOIN分表

2.3.7 查询: 性能优化

2.3.8 查询: 所有分表检索

2.4 插入

2.5 删除数据

2.6 更新数据

2.7 辅助方法

2.8 唯一判段

三、自定义分表：按单词

3.1 按首字母拼音分表

3.2 创建分表类

3.3 使用自定义分表

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

五、性能优化

六、视频教程

七、时序数据库

八、老表数据迁移分表

步骤1：

步骤2：

公告

yswenli

http://www.cnblogs.com/yswenli/

SqlSugar分表

二、 内置分表使用

2.1 定义实体

2.2 同步表和结构

2.3.1 查询: 时间过滤

2.3.2 查询: 选择最近的表

2.3.3 查询: 精准定位一张表

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

2.3.5 查询: 分表Join正常表

2.3.6 查询: 分表JOIN分表

2.3.7 查询: 性能优化

2.3.8 查询: 所有分表检索

2.4 插入

2.5 删除数据

2.6 更新数据

2.7 辅助方法

2.8 唯一判段

三、 自定义分表：按单词

3.1 按首字母拼音分表

3.2 创建分表类

3.3 使用自定义分表

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

五、性能优化

六、视频教程

七、时序数据库

八、老表数据迁移分表

步骤1：

步骤2：

公告

二、内置分表使用

2.4 插入　

2.5 删除数据　

2.6 更新数据　　　

三、自定义分表：按单词