SQLSugar进阶使用：高级查询与性能优化

文章目次

• 前言
  
• 一、高级查询
  
• 
  
  
  
  • 1.查全部
    
  • 2.查询总数
    
  • 3.按条件查询
    
  • 4.动态OR查询
    
  • 5.查前几条
    
  • 6.设置新表名
    
  • 7.分页查询
    
  • 8.排序 OrderBy
    
  • 9.联表查询
    
  • 10.动态表达式
    
  • 11.原生 Sql 操作 ，Sql和存储过程
    
  
  
• 二、性能优化
  
• 
  
  
  
  • 1.二级缓存
    
  • 2.批量操作
    
  • 3.异步操作
    
  • 4.分表组件，自动分表
    
  • 5.查询
    
  • 6.插入
    
  • 7.删除数据
    
  • 8.引入库
    
  • 9.读写分离/主从
    
  
  
• 总结
  

---

前言

SqlSugar作为一款专为.NET平台设计的轻量级ORM（对象关系映射）框架，其进阶使用中的高级查询与性能优化涵盖了多个方面的内容。

---

一、高级查询

SqlSugar提供了丰富的高级查询功能，以满足复杂的业务需求，主要包括：
1.查全部

1. List<Student> list=db.Queryable<Student>().ToList()
2. //select * from Student

复制代码

2.查询总数

1. int count=db.Queryable<Student>().Count()
2. //select count(1) from Student

复制代码

3.按条件查询

1. db.Queryable<Student>().Where(it=>it.Id==1).ToList()
2. //select * from Student where id=1
4. db.Queryable<Student>().Where(it=>it.name !=null).ToList()//不是null
5. //select * from Student where name is not null
7. db.Queryable<Student>().Where(it=>it.name ==null).ToList()//是null
8. //select * from Student where name is null
10. db.Queryable<Student>().Where(it=>it.name !="").ToList()//不是空 ,不为空
11. //select * from Student where name <> ''

复制代码

4.动态OR查询

1. var exp= Expressionable.Create<Student>();
2. exp.OrIF(条件,it=>it.Id==1);//.OrIf 是条件成立才会拼接OR
3. exp.Or(it =>it.Name.Contains("jack"));//拼接OR
4. var list=db.Queryable<Student>().Where(exp.ToExpression()).ToList();

复制代码

5.查前几条

1. db.Queryable<Student>().Take(10).ToList()
2. //select top 10 * from Student

复制代码

6.设置新表名

1. //例1：更新表名
2. db.Queryable<School>().AS("Student").ToList();
3. //生成的SQL  SELECT [ID],[NAME] FROM  Student
4. //动态表名 表别名 指定表明
6. //例2：给表名添加前缀
7. db.Queryable<School>().AS("dbo.School").ToList();
8. //生成的SQL  SELECT [ID],[NAME] FROM  dbo.School

复制代码

7.分页查询

1. .ToPageList(pagenumber, pageSize)// 不返回Count
2. .ToPageList(pagenumber, pageSize, ref totalCount)//返回Count
3. .ToPageList(pagenumber, pageSize, ref totalCount,ref totalPage)//返回Count+总页数

复制代码

8.排序 OrderBy

1. var list =db.Queryable<Student>()
2. .LeftJoin<School>((st, sc) =>st.SchoolId==sc.Id)
3. .OrderBy((st,sc)=>st.SchoolId)//写Select前面用法，正常都这么用
4. .Select((st,sc)=>new Dto(){ id=it.id ,Name=it.Name})
5. .ToList();

复制代码

9.联表查询

1. //联表查询
2. var query5 = db.Queryable<Order>()
3.          .LeftJoin<Custom>((o,cus) => o.CustomId == cus.Id)//多个条件用&&
4.          .LeftJoin<OrderDetail> ((o,cus,oritem) => o.Id == oritem.OrderId)
5.          .Where(o => o.Id == 1)  
6.          .Select((o,cus,oritem) => new ViewOrder {Id=o.Id,CustomName = cus.Name })
7.          .ToList();  //ViewOrder是一个新建的类，更多Select用法看下面文档

复制代码

10.动态表达式

1. //用例1:连写
2. Expression<Func<Order, bool>> exp = Expressionable.Create<Order>() //创建表达式
3.               .AndIF(p > 0, it => it.Id == p)
4.               .AndIF(name != null, it => it.Name == name && it.Sex==1)
5.               .ToExpression();//注意 这一句 不能少
6.                
7. var list = db.Queryable<Order>().Where(exp).ToList();//直接用就行了不需要判段 null和加true
8.    
10. //用例2:分开写法
11. var expable= Expressionable.Create<Order>();
12. ...逻辑
13. expable.And(it.xx==x);
14. ...逻辑
15. expable.And(it.yy==y);
16. ...逻辑
17. var exp=expable.ToExpression();//要用变量 var exp=
18. db.Queryable<Order>().Where(exp).ToList()//直接用就行了不需要判段 null和加true
20. //用例3：多表查询
21. var exp=Expressionable.Create<Order,T2,T3>()
22.          .And((x,y,z)=>z.id==1).ToExpression();//注意 这一句 不能少
23.          
24.          
25. //技巧 WhereIF 有时候更方便
26. var list=db.Queryable<T>()
27. .WhereIF(p>0,it=>it.p==p)
28. .WhereIF(y>0,it=>it.y==y)
29. .ToList()

复制代码

11.原生 Sql 操作 ，Sql和存储过程

1. //调用Sql
2. db.Ado.具体方法
4. //调用存储过程
5. db.Ado.UseStoredProcedure().具体方法

复制代码

方法列表：

二、性能优化

为了提拔数据库操作的性能，SqlSugar提供了多种优化措施。
1.二级缓存

二级缓存是将结果集进行缓存，当SQL和参数没发生变化的时候从缓存内里读取数据，减少数据库的读取操作。

• 维护方便：SqlSugar的 二级缓存 支持单表和多表的 CRUD 自动缓存更新 ，减少了复杂的缓存KEY维护操作
  
• 使用方便：可以直接在Queryable.WithCache直接缓存当前对象
  
• 外部集成：通过实现ICacheService接口，来调用外部缓存方法，想用什么实现什么
  

注意：Sql语句不停在变的就不适合缓存，比如 ID=？这种SQL都在变化，并且表的数量巨大这种环境就不要使用缓存了，缓存适合加
2.批量操作

批量操作海量数据操作ORM性能瓶颈在实体转换上面，并且不能使用常规的Sql去实现。
当列越多转换越慢，SqlSugar将转换性能做到极致，并且采用数据库最佳API，操作数据库到达极限性能。
1.BulkCopy
大数据插入

1. db.Fastest<Order>().BulkCopy(lstData);//插入
2. db.Fastest<Order>().PageSize(100000).BulkCopy(insertObjs);
3. db.Fastest<System.Data.DataTable>().AS("order").BulkCopy(dataTable);

复制代码

2.BulkUpdate
大数据更新

1. db.Fastest<Order>().BulkUpdate(GetList())//更新
2. db.Fastest<Order>().PageSize(100000).BulkUpdate(GetList())//更新
3. db.Fastest<Order>().BulkUpdate(GetList(),new string[] { "Id"});//无主键
4. db.Fastest<Order>().BulkUpdate(GetList(), new string[]{"id"},
5.                      new string[]{"name","time"})//只更新几列
6. //DataTable                           
7. db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,new string[] {"Id"});//Id是主键
8. db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,new string[] {"Id"},更新的列);

复制代码

3. BulkMerge （5.1.4.109）
大数据 ：插入大概更新

1. //原理
2. //Oracle和SqlServer使用了Merge Into+BulkCopy
3. //其他库底层是 db.Storageable(list).ExecuteSqlBulkCopy()
5. db.Fastest<Order>().BulkMerge(List);
6. db.Fastest<Order>().PageSize(100000).BulkMerge(List);
8. //DataTable 升级到:SqlSugarCore 5.1.4.147-preview15+
9. db.Fastest<System.Data.DataTable>()
10.         .AS("Order")//表名
11.         //第一个参数datatable
12.         //第二个参数主键
13.         //第三个参数是不是自增 true为自增
14.        .BulkMerge(dt, new string[] { "Id" },true);

复制代码

4. BulkQuery
平凡查询就行了性能超快

1. db.Queryable<Order>().ToList();//比Dapper略快
3. //分页降低内存 适合复杂的DTO转换和导出
4. List<Order> order = new List<Order>();
5. db.Queryable<Order>().ForEach(it=> { order.Add(it); /*禁止这儿操作数据库因为会循环*/} ,2000);

复制代码

5. BulkDelete
直接用分页删除就行了

1. db.Deleteable<Order>(list).PageSize(1000).ExecuteCommand();

复制代码

3.异步操作

释放主线程的占用，进步吞吐量，直接运行并不能进步速度，合适场景使用，固然全部都写异步也并没大问题，只是代码难提拔了，建议合适场景使用，高并发，数据库请求时间过长等场景使用。
SqlSugar中ToList是同步方法，那么ToListAsync这种是异步方法，通过Async来区别是同步还是异步方法。

1. var data=await db.Queryable<Order>().FirstAsync();
2. var list= await db.Queryable<Order>().Where(it=>it.Id==1).ToListAsync();
3.   
4. //分页需要特别注意用法
5. RefAsync<int> total = 0;
6. var list=await Db.Queryable<Order>().ToPageListAsync(1, 2, total);

复制代码

4.分表组件，自动分表

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

• 可扩展架构设计，比如一个ERP用5年不卡，到了10就卡了，很多人都是备份然后清空数据
  
• 数据量太多 ，例如天天都有 几十上百万的数据进入库，如果不分表后面查询将会非常缓慢
  
• 性能瓶颈 ，数据库现有数据凌驾1个亿，很多环境下索引会莫名失效，性能大大降落
  

1.界说实体:
我们界说一个实体，主键不能用自增大概int ，设为long大概guid都可以，我例子就用自带的雪花ID实现分表。

1. [SplitTable(SplitType.Year)]//按年分表 （自带分表支持 年、季、月、周、日）
2. [SugarTable("SplitTestTable_{year}{month}{day}")]//3个变量必须要有，这么设计为了兼容开始按年，后面改成按月、按日
3. public class SplitTestTable
4. {
5.      [SugarColumn(IsPrimaryKey =true)]
6.      public long Id { get; set; }
7.   
8.      public string Name { get; set; }
9.       
10.      [SugarColumn(IsNullable = true)]//设置为可空字段 (更多用法看文档 迁移)
11.      public DateTime UpdateTime{get;set;}
12.       
13.      [SplitField] //分表字段 在插入的时候会根据这个字段插入哪个表，在更新删除的时候用这个字段找出相关表
14.      public DateTime CreateTime { get; set; }
15. }　
16.   
17. //按年分表格式如下
18. SplitTestTable_20220101
19.   
20. //比如现在5月按月分表格式如下
21. SplitTestTable_20220501
22.   
23. //比如现在5月11日按日分表格式如下
24. SplitTestTable_20220511

复制代码

2.同步表和布局：
如果分了20张表，实体类发生变更，那么 20张表可以自动同步布局，与实体同等。

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

复制代码

注意：插入会自动建表不必要这行代码，主要用于实体改动后同步多个表布局，大概一张表没有初始一张，克制写到业务内里多次实行。
5.查询

1.查询: 时间过滤
通过开始时间和结束时间自动生成CreateTime的过滤并且找到对应时间的表。

1. //简单示例
2. var list=db.Queryable<OrderSpliteTest>().SplitTable(beginDate,endDate).ToPageList(1,2);　
4. //结合Where
5. var list=db.Queryable<OrderSpliteTest>().Where(it=>it.Id>0).SplitTable(beginDate,endDate).ToPageList(1,2);　
7. //注意：
8. //1、 分页有 OrderBy写 SplitTable 后面 ,uinon all后在排序
9. //2、 Where尽量写到 SplitTable 前面，先过滤在union all
10. //原理：(sql union sql2) 写SplitTable 后面生成的括号外面，写前生成的在括号里面

复制代码

2. 查询: 选择最近的表
如果下面是按年分表 Take(3) 表示 只查 最近3年的分表数据。

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

复制代码

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

1. var name=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);//根据时间获取表名
3. //推荐：表不存在不会报错
4. var list=db.Queryable<OrderSpliteTest>().SplitTable(tabs => tabs.InTableNames(name)).ToList()
6. //不推荐：查询不推荐用，删除和更新可以用
7. var list=Db.Queryable<SplitTestTable>().AS(name).Where(it => it.Id==data.Id).ToList();

复制代码

4.查询: 表达式定位哪几张表

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

复制代码

5.查询: 分表Join正常表

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

复制代码

6.查询: 分表JOIN分表

1. var rightQuery=db.Queryable<Custom>().SplitTable(tabs=>tabs.Take(3)) ;
2. var list=db.Queryable<Order>().SplitTable(tabs=>tabs.Take(3))
3. .LeftJoin<Custom>(rightQuery,(o,c)=>o.CustomId==c.Id) // Join  rightQuery
4. .Select((o,c)=>new { name=o.Name,cname=c.Name }).ToPageList(1,2);　
7. //技巧：如果是单表分表没有表返回第一个表可以防止报错  升级到：5.1.4.127 +
8. SplitTable(it=>it.ContainsTableNamesIfNullDefaultFirst("table"))

复制代码

7. 查询: 性能优化
条件尽大概写在SplitTable前面，因为如许会先过滤在合并

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

复制代码

8.查询: 全部门表检索

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

复制代码

6.插入

因为我们用的是Long以是采用雪花ID插入(guid也可以克制使用自增列)

1. var data = new SplitTestTable()
2. {
3.       CreateTime=Convert.ToDateTime("2019-12-1"),//要配置分表字段通过分表字段建表
4.       Name="jack"
5. };
6. //雪花ID+表不存在会建表
7. db.Insertable(data).SplitTable().ExecuteReturnSnowflakeIdList();//插入并返回雪花ID并且自动赋值ID　  
8.   //服务器时间修改、不同端口用同一个代码、多个程序插入一个表都需要用到WorkId
9.   //保证WorkId唯一 ，程序启动时配置 SnowFlakeSingle.WorkId=从配置文件读取;
10.   
11.   
12. //GUID+表不存在会建表
13.   db.Insertable(data).SplitTable().ExecuteCommand();//插入GUID 自动赋值 ID
14.    
15. //大数据写入+表不存在会建表
16. db.Fastest<OrderSpliteTest>().SplitTable().BulkCopy(List<OrderSpliteTest>);//自动找表大数据写入
17.   
18. //不会自动建表 如果表100%存在用这个性能好些
19. db.Fastest<OrderSpliteTest>().AS(表名).BulkCopy(List<OrderSpliteTest>);//固定表大数据写入

复制代码

注意：.SplitTable不要遗漏了
7.删除数据

1.推荐用法：新功能 5.0.7.7 preview及以上版本

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

复制代码

2.最近3张表都实行一遍删除

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

复制代码

3.精准删除
相对于上面的操作性能更高，可以精准找到具体表

1. var tableName=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);//根据时间获取表名
2. db.Deleteable<SplitTestTable>().AS(tableName).Where(deldata).ExecuteCommand();
3. //DELETE FROM [SplitTestTable_20210101] WHERE [Id] IN (1454676863531089920)

复制代码

4.范围删除

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

复制代码

8.引入库

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

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

复制代码

更多用法：

1. //更新近3张表
2. db.Updateable(deldata).SplitTable(tas=>tas.Take(3)).ExecuteCommand();
4. //精准找单个表
5. var tableName=Db.SplitHelper<SplitTestTable>().GetTableName(data.CreateTime);//根据时间获取表名
6. db.Updateable(deldata).AS(tableName).ExecuteCommand();//实体
7. db.Updateable<TestEnity>().AS(tableName).SetColumns(..).Where(...).ExecuteCommand();//表达式
9. //通过表达式过滤出要更新的表
10. db.Updateable(deldata).SplitTable(tas => tas.Where(y=>y.TableName.Contains("_2019"))).ExecuteCommand();

复制代码

9.读写分离/主从

1.配置从表

• 如果存在事务全部操作都走主库，不存在事务 修改、写入、删除走主库，查询操作走从库
  
• HitRate 越大走这个从库的概率越大
  假设A从库 HitRate设置为5,假设B从库 HitRate设置为10,A的概率为33.3% B的概率为66.6%
  SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
  {
  ConnectionString = Config.ConnectionString,//主库
  DbType = DbType.SqlServer,
  IsAutoCloseConnection = true,
  //从库
  SlaveConnectionConfigs = new List() {
  new SlaveConnectionConfig() { HitRate=10, ConnectionString=Config.ConnectionString2 } ,
  new SlaveConnectionConfig() { HitRate=10, ConnectionString=Config.ConnectionString2 }
  }
  });
  

2.逼迫走主库
新功能:5.0.8 用法和Queryable一样

1. Db.MasterQueryable<T>().Where(x=>x.id==1).ToList() //强制走主库
2. //5.1.3.22修复了异步不会生效

复制代码

事务

1. //事务中全部会走主库

复制代码

通过配置走主库

1. //方式1.强制配置
2. db.Ado.IsDisableMasterSlaveSeparation=true

复制代码

---

总结

综上所述，SqlSugar的高级查询功能提供了强盛的数据筛选、排序和连表查询能力，而性能优化则通过缓存、SQL优化、批量操作等多种本领来进步数据库操作的效率。这些特性使得SqlSugar在.NET开发领域具有广泛的应用前景。
“笑对人生，聪明偕行！博客新文出炉，微信订阅号更新更及时，等你笑纳~”

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。