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

果糖大資料科技發表於2021-10-31

元件架構

一、疑問&目的

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）按時間自動過濾

通過開始時間和結束時間自動生成CreateTime的過濾並且找到對應時間的表

var lis2t = db.Queryable<OrderSpliteTest>()
.SplitTable(DateTime.Now.Date.AddYears(-1), DateTime.Now)
.ToOffsetPage(1,2);

　(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 = 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 = new SplitTestTable()
            {
                Id = id,
                CreateTime = DateTime.Now
            };
 var tableName = db.SplitHelper(deldata).GetTableNames();
 db.Deleteable<SplitTestTable>().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 = new SplitTableInfo()
                    {
                        TableName = item.Name //要用item.name不要寫錯了
                    };
                    result.Add(data);
                }
            }
            return result.OrderBy(it=>it.TableName).ToList();
        }

        /// <summary>
        /// 獲取分表欄位的值
        /// </summary>
        /// <param name="db"></param>
        /// <param name="entityInfo"></param>
        /// <param name="splitType"></param>
        /// <param name="entityValue"></param>
        /// <returns></returns>
        public object GetFieldValue(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object entityValue)
        {
            var splitColumn = entityInfo.Columns.FirstOrDefault(it => it.PropertyInfo.GetCustomAttribute<SplitFieldAttribute>() != null);
            var value = splitColumn.PropertyInfo.GetValue(entityValue, null);
            return value;
        }
        /// <summary>
        /// 預設表名
        /// </summary>
        /// <param name="db"></param>
        /// <param name="EntityInfo"></param>
        /// <returns></returns>
        public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo)
        {
            return entityInfo.DbTableName + "_FirstA"; 
        }

        public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType type)
        {
            return entityInfo.DbTableName + "_FirstA";//目前模式少不需要分類(自帶的有 日、周、月、季、年等進行區分)
        }

        public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object fieldValue)
        {
            return entityInfo.DbTableName + "_First" + GetFirstCode(fieldValue+""); //根據值按首字母
        }

        #region 獲取首字母
        /// <summary>
        /// 在指定的字串列表CnStr中檢索符合拼音索引字串
        /// </summary>
        /// <param name="CnStr">漢字字串</param>
        /// <returns>相對應的漢語拼音首字母串</returns>
        private static string GetFirstCode(string CnStr)
        {
            string Surname = CnStr.Substring(0, 1);
            string strTemp = GetSpellCode(Surname);
            return strTemp;
        }

        /// <summary>
        /// 得到一個漢字的拼音第一個字母，如果是一個英文字母則直接返回大寫字母
        /// </summary>
        /// <param name="CnChar">單個漢字</param>
        /// <returns>單個大寫字母</returns>
        private static string GetSpellCode(string CnChar)
        {
            long iCnChar;
            byte[] arrCN = System.Text.Encoding.Default.GetBytes(CnChar);

            //如果是字母，則直接返回
            if (arrCN.Length == 1)
            {
                CnChar = CnChar.ToUpper();
            }
            else
            {
                int area = (short)arrCN[0];
                int pos = (short)arrCN[1];
                iCnChar = (area << 8) + pos;

                // iCnChar match the constant
                string letter = "ABCDEFGHJKLMNOPQRSTWXYZ";
                int[] areacode = { 45217, 45253, 45761, 46318, 46826, 47010, 47297, 47614, 48119, 49062, 49324, 49896, 50371, 50614, 50622, 50906, 51387, 51446, 52218, 52698, 52980, 53689, 54481, 55290 };
                for (int i = 0; i < 23; i++)
                {
                    if (areacode[i] <= iCnChar && iCnChar < areacode[i + 1])
                    {
                        CnChar = letter.Substring(i, 1);
                        break;
                    }
                }
            }
            return CnChar;
        }
        #endregion
    }
　　

 4.2 建立實體類
    /// <summary>
    /// 隨便設定一個分類
    /// </summary>
    [SplitTable(SplitType._Custom01)]
    public class WordTestTable 
    {
        [SugarColumn(IsPrimaryKey =true)]
        public long Id { get; set; }

        [SplitField] //標識一下分表欄位
        public string Name { get; set; }

   
        public DateTime CreateTime { get; set; }

    }

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 安裝

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

分庫分表架構方案設計
2018-07-20
架構
.Net分表分庫動態化處理
2022-05-29
分庫分表的框架如何設計自動路由
2021-11-09
框架路由
常用分庫分表方案
2021-10-26
Mysql分庫分表方案
2017-11-07
MySql
.Net 下高效能分表分庫元件-連線模式原理
2021-12-08
元件模式
.Net下極限生產力之efcore分表分庫全自動化遷移CodeFirst
2022-07-07
.NET ORM 分表分庫【到底】怎麼做？
2020-08-30
ORM
分庫分表的理想方案
2017-02-17
NetCore框架WTM的分表分庫實現
2022-06-09
NetCore框架
分庫分表系列：分庫分表的前世今生
2022-03-19
分庫分表
2024-08-20
.Net/C#分庫分表高效能O(1)瀑布流分頁
2022-03-08
C#
分庫分表注意
2019-03-18
MySQL分庫分表
2015-10-30
MySql
[Mysql]分庫分表
2024-07-23
MySql
MySQL 分庫分表方案，總結太全了。。
2024-04-24
MySql
MySQL使用event進行自動分表
2014-04-14
MySql
徹底搞清MySQL分庫分表（垂直分庫，垂直分表，水平分庫，水平分表）
2021-02-01
MySql
.Net下你不得不看的分表分庫解決方案-多欄位分片
2021-12-27
基於.NET架構的樹形動態報表設計與應用
2021-09-09
架構
EFCore 分表
2024-10-07
mycat配置分庫分表
2021-06-29
Mycat分庫分表配置
2021-02-04
分庫分表總結
2020-12-09
Mycat分庫分表（一）
2020-11-15
【Mysql】OneProxy分庫分表
2016-03-29
MySql
基因法分庫分表
2024-03-23
MySQL 常用分庫分表方案，都在這裡了！
2021-09-09
MySql
資料量大了一定要分表，分庫分表元件Sharding-JDBC入門與專案實戰
2020-08-01
元件JDBC
MySQL分表後原分割槽表處理方案
2021-01-09
MySql
徹底搞清分庫分表（垂直分庫，垂直分表，水平分庫，水平分表）
2020-10-27
oracle分表效率,資料庫分庫分表是什麼，什麼情況下需要用分庫分表
2022-09-12
Oracle資料庫
MyCat分庫分表、讀寫分離
2019-02-25
Mycat 讀寫分離+分庫分表
2017-07-18
一種簡單易懂的 MyBatis 分庫分表方案
2019-03-10
MyBatis
10億級別訂單的分庫分表方案
2018-12-10
讀寫分離 & 分庫分表 & 深度分頁
2024-03-09

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

一、疑問&目的

1.1 分表使用場景

1.2 我的目的

二、SqlSugar分表優勢

2.1 簡單

2.2 功能強大

2.3 元件化設計

三、 自帶分表的使用

3.1 定義實體

3.2 初始化和同步結構

3.2 查詢用法

3.3 插入

3.4 刪除資料

3.5 更新資料

四、 自定義分表使用

4.1 按首字母拼音分表

4.2 建立分表類

4.3 使用自定義分表

SqlSugar ORM 新語法

新語法1：

新語法2:

新語法3

總結

原始碼下載：

Nuget 安裝

相關文章

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

三、自帶分表的使用

3.3 插入　

3.4 刪除資料　

3.5 更新資料　　

四、自定義分表使用

SqlSugar ORM 新語法　

原始碼下載：　