.NET 5 全自動分表組件，.NET 分表方案 ，分表架構與設計

 一、疑問&目的

 1.1 分表使用場景

  （1）可擴展架構設計，比如一個ERP用5年不卡，到了10就卡了因為資料太多了，這個時候很多人都是備份然后清空資料，這個作業大并且麻煩，以前的資料很難在使用

  （2） 資料量太多 ，例如每天都有 幾十上百萬的資料進入庫，如果不分表后面查詢將會非常緩慢

    (3)   性能瓶頸 ，資料庫現有資料超過1個億，很多情況下索引會莫名失效，性能大大下降，如果使用分表，我們只需要針對一個分表進行操作性能大大提升

 1.2  我的目的

說到ORM很多會想到EF Core Dapper SqlSugar Chloe  Freesql等，但是說到分表組件或許者大家想不到一個名詞

我寫這一篇文章最大的目的是想大家記住 全自動的分表組件 【 SqlSugar 】

沒錯SqlSugar是一個ORM框架，并且已經完美支持了自動分表 ，希望你能喜歡

二、SqlSugar分表優勢

2.1 簡單 

 CURD和分表一體化 ，小白也能很好的使用分表，只要會使用 ORM 就會使用分表 ，2者語法同步 就多 一個 .SplitTable　　　

 2.2 功能強大

支持 表結構同步，自動建表，強大的查詢陳述句，插入，更新，洗掉

 2.3 組件化設計

默認集成了按  日、周、月、季、年的方式表,  比如，我想根據加密演算法分表，或者根據多個欄位分表，或者不滿意現在的分表 都可以已擴展實作

    //實作下面介面就能將自定義的分表集成到ORM中
    public interface ISplitTableService
    {
        //1 根據現有表選出當前分表
        List<SplitTableInfo> GetAllTables(ISqlSugarClient db,EntityInfo EntityInfo,List<DbTableInfo> tableInfos);

        //2 獲取表名
        string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo);
        string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo, SplitType type);
        string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object fieldValue);

        //3 獲取分組欄位的值
        object GetFieldValue(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object entityValue);
    }
    //說白了重寫介面分為3類，基本要的引數都有現成的，可以模仿我現有分表的代碼輕松實作

三、 自帶分表的使用

自帶分表支持按年、按月、按日、按季、按周進行分表

 3.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; }
        [SplitField] //分表欄位  在插入的時候會根據這個欄位插入哪個表，在更新洗掉的時候也能可方便的用這個欄位找出相關表
        public DateTime CreateTime { get; set; }
    }　

 3.2 初始化和同步結構

　（1）如果一張表沒有 查詢肯定會報錯，所以程式啟動時需要建一張表

    （2）假如分了20張表，物體類發生變更，那么 20張表可以自動同步結構，與物體一致

db.CodeFirst.SplitTables().InitTables<SplitTestTable>(); //程式啟動時加這一行

 3.2 查詢用法

 （1）基礎篩選

 下面運算式中的tas可以對當前分表進行篩選，Take(3) 表示最近3張表，如果是按年那么就是最近3年的資料，按月就是最近3個月的資料

Db.Queryable<SplitTestTable>().Where(it => it.Name.Contains("a")).SplitTable(tas => tas.Take(3)).ToList();//先過濾在合并

Db.Queryable<SplitTestTable>().SplitTable(tas => tas.Take(3)).Where(it => it.Name.Contains("a")).ToList();//先合并在過濾

　(2)精準篩選

根據分表欄位的值可以精準的定位到具體是哪一個分表

比Take(N)性能要高出很多

 var name=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);
 Db.Queryable<SplitTestTable>().Where(it => it.Id==data.Id).SplitTable(tas => tas.InTableNames(name)).ToList();

（3）運算式過濾　　

 Db.Queryable<SplitTestTable>().Where(it => it.Id==data.Id).SplitTable(tas => tas.Where(y=>y.TableName.Contains("2019"))).ToList();

  (4)  聯表查詢　

//分表使用聯表查詢
var list=db.Queryable<Order>()
              .SplitTable(tabs=>tabs.Take(3))//Order表只取最近3張表和Custom JOIN
              .LeftJoin<Custom>((o,c)=>o.CustomId==c.Id).Select((o,c)=>new { name=o.Name,cname=c.Name }).ToPageList(1,2);　　

 (5)  分頁查詢　 

  var count = 0;
  db.Queryable<SplitTestTable>().Where(it => it.Name.Contains("a")).SplitTable(tas => tas.Take(3)).ToPageList(1,2,ref count);//

3.3 插入　

因為我們用的是Long所以采用雪ID插入(guid也可以禁止使用自增列), 物體結構看上面 3.1

 var data = https://www.cnblogs.com/sunkaixuan/archive/2021/10/31/new SplitTestTable()
 {
       CreateTime=Convert.ToDateTime("2019-12-1"),
       Name="jack"
   };

  db.Insertable(data).SplitTable().ExecuteReturnSnowflakeId();//插入并回傳雪花ID　　

因為我們是根據CreateTime進行的分表，生成的SQL陳述句如下：

INSERT INTO [SplitTestTable_20190101] --如果表不存在會自動建表
           ([Id],[Name],[CreateTime])
     VALUES
           (@Id,@Name,@CreateTime) ;

批量插入　

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個不同的表中

 3.4 洗掉資料　

 （1）懶人模式洗掉

最近3張表都執行一遍洗掉

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

生在的SQL如下：

DELETE FROM [SplitTestTable_20220101] WHERE [Id] IN (1454675372531515392)

DELETE FROM [SplitTestTable_20210101] WHERE [Id] IN (1454675372531515392)

DELETE FROM [SplitTestTable_20200101] WHERE [Id] IN (1454675372531515392)

  （2）精準洗掉   　　

 相對于上面的操作性能更高，可以精準找到具體表

var deldata = https://www.cnblogs.com/sunkaixuan/archive/2021/10/31/new SplitTestTable()
            {
                Id = id,
                CreateTime = DateTime.Now
            };
 var tableName = db.SplitHelper(deldata).GetTableNames();
 db.Deleteable().Where(deldata).SplitTable(tas=>tas.InTableNames(tableName)).ExecuteCommand();

　生成的SQL如下:

DELETE FROM [SplitTestTable_20210101] WHERE [Id] IN (1454676863531089920)

 3.5 更新資料　　

更新的用法基本上和洗掉一樣

//更新近3張表
db.Updateable(deldata).SplitTable(tas=>tas.Take(3)).ExecuteCommand();
 
//精準找到表名并且更新資料
var tableName = db.SplitHelper(deldata).GetTableNames();
db.Updateable(deldata).SplitTable(tas => tas.InTableNames(tableName)).ExecuteCommand();


//通過運算式過濾出要更新的表
db.Updateable(deldata).SplitTable(tas => tas.Where(y=>y.TableName.Contains("_2019"))).ExecuteCommand();

四、 自定義分表使用 

 上面的分表功能是我們自帶集成的，比如我想實作自定義的分表我該如何實作呢？

  4.1 按首字母拼音分表

  我們就寫個按24個字母進行分表的小例子，來學習一下如何自定義分表

  4.2  創建分表類

我們新建一個類繼承成ISplitTableService 介面 

 public class WordSplitService : ISplitTableService
    {
        /// <summary>
        /// 回傳資料用于 tas 進行篩選
        /// </summary>
        /// <param name="db"></param>
        /// <param name="EntityInfo"></param>
        /// <param name="tableInfos"></param>
        /// <returns></returns>
        public List<SplitTableInfo> GetAllTables(ISqlSugarClient db, EntityInfo EntityInfo, List<DbTableInfo> tableInfos)
        {
            List<SplitTableInfo> result = new List<SplitTableInfo>();
            foreach (var item in tableInfos)
            {
                if (item.Name.Contains(EntityInfo.DbTableName+"_First")) //區分標識如果不用正則符復雜一些，防止找錯表
                {
                    SplitTableInfo data = https://www.cnblogs.com/sunkaixuan/archive/2021/10/31/new SplitTableInfo()
                    {
                        TableName = item.Name //要用item.name不要寫錯了
                    };
                    result.Add(data);
                }
            }
            return result.OrderBy(it=>it.TableName).ToList();
        }

        /// 

        /// 獲取分表欄位的值
        /// 
        /// 
        /// 默認表名
        /// 
        /// 

  4.3  使用自定義分表
代碼：

//使用自定義分表
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) ;


查詢指定表　

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　　

 
SqlSugar ORM 新語法　
需要 NUGET安裝 SqlSugarCore  5.0.4.3-proview-03
以前有些人說SQLSUGAR聯表不優美，所以我也支了最優美聯表語法：
新語法1：
簡單的聯表

   var leftQueryable = db.Queryable<Custom>()
        .LeftJoin<OrderItem>((c, i) => c.Id == i.ItemId)
       .Select(c =>c).ToList();　

SQL如下：

SELECT c.* FROM [Custom] c Left JOIN [OrderDetail] i ON ( [c].[Id] = [i].[ItemId] )　　

新語法2:
表  和 Queryable 進行聯表

        var rigtQueryable = db.Queryable<Custom>()
            .LeftJoin<OrderItem>((o, i) => o.Id == i.ItemId)
                .Select(o => o);

        var List = db.Queryable<Order>()
            .LeftJoin(rigtQueryable, (c, j) => c.CustomId == j.Id)
            .Select(c => c).ToList();　　

SQL如下：

SELECT c.* FROM [Order] c Left JOIN (SELECT o.* FROM [Custom] o Left JOIN [OrderDetail] i ON ( [o].[Id] = [i].[ItemId] )  ) j ON ( [c].[CustomId] = [j].[Id] )

新語法3
 Queryable 和表 進行聯表

    var leftQueryable = db.Queryable<Custom>()
        .LeftJoin<OrderItem>((c, i) => c.Id == i.ItemId)
         .Select(c =>c) ;

    var List = db.Queryable(leftQueryable)
        .LeftJoin<Order>((o, cu) => o.Id == cu.Id)
        .Select(o => o).ToList();　　

SQL如下：

SELECT o.* FROM  (SELECT * FROM  (SELECT c.* FROM [Custom] c Left JOIN [OrderDetail] i ON ( [c].[Id] = [i].[ItemId] )  ) t ) o Left JOIN [Order] cu  ON ( [o].[Id] = [cu].[Id] )


　　
總結
SqlSugar 一直在創新，并且越來越好，相信分表功能，新的語法一定會讓很多人喜歡
 　　　
原始碼下載：　
　https://github.com/donet5/SqlSugar     沒點關注的點一波關注 ，你們的贊或者一個star就是我們這些開源些的動力
 
Nuget 安裝

 
 
　　
　　
　　
　　





      
  
      轉載請註明出處，本文鏈接：https://www.uj5u.com/net/342976.html
      
標籤：.NET技术 
      
上一篇：走進WPF之資源
下一篇：WPF 寫一個提醒工具軟體(完整專案)