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

文章目录

前言

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

一、高级查询

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

1.查所有

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

2.查询总数

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

3.按条件查询

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

4.动态OR查询

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

5.查前几条

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

6.设置新表名

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

7.分页查询

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

8.排序 OrderBy

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

9.联表查询

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

10.动态表达式

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

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

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

**方法列表：**

二、性能优化

为了提升数据库操作的性能，SqlSugar提供了多种优化措施。

1.二级缓存

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

• 维护方便：SqlSugar的 二级缓存 支持单表和多表的 CRUD 自动缓存更新 ，减少了复杂的缓存KEY维护操作

• 使用方便：可以直接在Queryable.WithCache直接缓存当前对象

• 外部集成：通过实现ICacheService接口，来调用外部缓存方法，想用什么实现什么

  注意：Sql语句一直在变的就不适合缓存，比如 ID=？这种SQL都在变化，并且表的数量巨大这种情况就不要使用缓存了，缓存适合加

2.批量操作

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

**1.BulkCopy**
 大数据插入

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

**2.BulkUpdate**
 大数据更新

db.Fastest<Order>().BulkUpdate(GetList())//更新
db.Fastest<Order>().PageSize(100000).BulkUpdate(GetList())//更新
db.Fastest<Order>().BulkUpdate(GetList(),newstring[]{"Id"});//无主键
db.Fastest<Order>().BulkUpdate(GetList(),newstring[]{"id"},newstring[]{"name","time"})//只更新几列//DataTable                           
db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,newstring[]{"Id"});//Id是主键
db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,newstring[]{"Id"},更新的列);

**3. BulkMerge （5.1.4.109）**
 大数据 ：插入或者更新

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

**4. BulkQuery**
 普通查询就行了性能超快

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

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

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

3.异步操作

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

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

4.分表组件，自动分表

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

• 可扩展架构设计，比如一个ERP用5年不卡，到了10就卡了，很多人都是备份然后清空数据

• 数据量太多 ，例如每天都有 几十上百万的数据进入库，如果不分表后面查询将会非常缓慢

• 性能瓶颈 ，数据库现有数据超过1个亿，很多情况下索引会莫名失效，性能大大下降

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

[SplitTable(SplitType.Year)]//按年分表 （自带分表支持 年、季、月、周、日）[SugarTable("SplitTestTable_{year}{month}{day}")]//3个变量必须要有，这么设计为了兼容开始按年，后面改成按月、按日publicclassSplitTestTable{[SugarColumn(IsPrimaryKey =true)]publiclong Id {get;set;}publicstring Name {get;set;}[SugarColumn(IsNullable =true)]//设置为可空字段 (更多用法看文档 迁移)publicDateTime UpdateTime{get;set;}[SplitField]//分表字段 在插入的时候会根据这个字段插入哪个表，在更新删除的时候用这个字段找出相关表publicDateTime CreateTime {get;set;}}//按年分表格式如下
 SplitTestTable_20220101
  
 //比如现在5月按月分表格式如下
 SplitTestTable_20220501
  
 //比如现在5月11日按日分表格式如下
 SplitTestTable_20220511

**2.同步表和结构**：
 假如分了20张表，实体类发生变更，那么 20张表可以自动同步结构，与实体一致。

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

**注意**：插入会自动建表不需要这行代码，主要用于实体改动后同步多个表结构，或者一张表没有初始一张，禁止写到业务里面多次执行。

5.查询

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

//简单示例var list=db.Queryable<OrderSpliteTest>().SplitTable(beginDate,endDate).ToPageList(1,2);//结合Wherevar 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. 查询: 选择最近的表**
 如果下面是按年分表 Take(3) 表示 只查 最近3年的分表数据。

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

**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();

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

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

**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);

**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"))

**7. 查询: 性能优化**
 条件尽可能写在SplitTable前面，因为这样会先过滤在合并

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

**8.查询: 所有分表检索**

//如果是主键查询哪怕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();

6.插入

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

var data =newSplitTestTable(){
      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>);//固定表大数据写入

**注意**：.SplitTable不要漏掉了

7.删除数据

**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();}

8.引入库

推荐用法: 新功能 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=>newOrderSpliteTest(){ 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();

9.读写分离/主从

**1.配置从表**

• 如果存在事务所有操作都走主库，不存在事务 修改、写入、删除走主库，查询操作走从库
• HitRate 越大走这个从库的概率越大 假设A从库 HitRate设置为5,假设B从库 HitRate设置为10,A的概率为33.3% B的概率为66.6%

SqlSugarClient db =newSqlSugarClient(newConnectionConfig(){
                ConnectionString = Config.ConnectionString,//主库
                DbType = DbType.SqlServer,
                IsAutoCloseConnection =true,//从库
                SlaveConnectionConfigs =newList<SlaveConnectionConfig>(){newSlaveConnectionConfig(){ HitRate=10, ConnectionString=Config.ConnectionString2 },newSlaveConnectionConfig(){ HitRate=10, ConnectionString=Config.ConnectionString2 }}});

**2.强制走主库**

**新功能:5.0.8 用法和Queryable一样**

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

**事务**

//事务中全部会走主库

**通过配置走主库**

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

总结

综上所述，SqlSugar的高级查询功能提供了强大的数据筛选、排序和连表查询能力，而性能优化则通过缓存、SQL优化、批量操作等多种手段来提高数据库操作的效率。这些特性使得SqlSugar在.NET开发领域具有广泛的应用前景。

“笑对人生，智慧同行！博客新文出炉，微信订阅号更新更实时，等你笑纳~”