前言:数据库 :Sql Server2016
编辑器:VS2019
环境 :.net core 和efcoreV
数据量:近千万条
相关联的表:3个相关表
!!!:这些表创建时没有外键等相关连接,所以麻烦
需求:展示个人用户所有所有的信息,数据分别存在大概5个表里面,有接近6万人,查询排列展示出来
吐槽:一开始搞了半天弄到5秒,不行!再改弄到3秒,还是不行!!最后压缩到了1.4秒左右。这可能是目前我的极限了,如果有大佬有办法 请!请!请! 给我留言说一下哪里还能优化
答案:
一般有FromSqlRaw 和FromSql,我们选FromSqlRaw来用,而FromSql在Efcore3.0中显示已过时
DbContext.set<ResultList>.FromSqlRaw("select * from ...").ToList() //ResultList是实体类,在数据库中是视图.Tolist()必加
详情往下看
一般来说我们在lamdba表达式中进行处理的时候,查询处理多个表我实测三万人要处理17分钟左右(看电脑性能)
例子:以下都是简写,有说不通的地方那就对了,不重要,知道干嘛就行了
首先这些最终都是被存到了内存里面了,处理起来耗内存,而且这只是一个人查都占很大内存,
有人说可以不往内存里面存,但是那些排序取第一条,截取其中十几条展示等等操作很难操作,照样很慢
var Data = await _userServer.query().ToListAsync(); //所有的用户
var File= await _fileServer.query().ToListAsync();
var Photo= await _photoServer.query().ToListAsync();
var Cert = await _certServer.query().ToListAsync();
var result = await Data.Select(x => new Filter
{
Id=x.Id,
Name = x.Name,
IdCard=x.IdCard,
File= File.Where(y =>y.IdCard==x.IdCard).Select(t =>new Filter //文件有很多,取最新的一条
{
Id=t.Id,
Name=t.Name,
CertCode=t.CertCode,
}).OrderByDescending(t => t.Id).FirstOrDefault(),
Photo = photo.Where(y =>y.IdCard==x.IdCard).Select(t => new Filter //照片也可以有很多张,取最新
{
Id = t.Id,
Name = t.Name,
Photo=t.Photo,
}).OrderByDescending(t => t.Id).FirstOrDefault(),
Certs = _certService.Query(y => y.IdCard== x.IdCard).OrderByDescending(y => y.Id).FirstOrDefault(),
}).OrderByDescending(x => x.Id).ToPagedListAsync(Page,PageSize);
看着上面的就很慢,而且,就是说几万条数据要在程序里面循环处理十几万次才能得出答案,
而最快的方法当然是 原生Sql语句 永远的神
数据
一个人可能有多条信息,多条账号,重名等等等问题,先写好Sql语句 ,最好在用的时候加上【】把给括起来,有时候找不到
WITH datas as //这里使用With为了加快sql运行速度,with为SQL片段,截取一块来用
(select IdCard, Name from [dbo].[user] GROUP BY IdCard, Name)
File as
(select * from [dbo].[user])
select Data.Id,
Data.Name,
F.file,
P.photo,
C.Cert
from datas
left join File F //为什么要用left join 因为一个人对应的 文件 照片 证书 可能为空
on F.IdCard=datas.IdCard
and F.name=datas.name
left join ........
left join......... //懒得写了,跟上一个一样
现在数据库就完成了,但是这样虽然拼起来了我们查询也有了,还有一个问题就是我们在后端用什么东西接收存起来
接收
.netcroe+Efcore 自定义类接收时候报错,不能生成该类型文件得把类加到DbContext中才能正确生成接收
public class Db : DbContext
{
public CertMSDb(DbContextOptions<Db> options) : base(options)
{
}
public virtual DbSet<File> Files{ get; set; }
public virtual DbSet<Photo> Photos{ get; set; }
public virtual DbSet<Cert> Certs{ get; set; }
//我们得自己加一条不然不能直接接收我们的数据
public virtual DbSet<ResultList> ResultLists{ get; set; }
}
//这个类型就跟我们的取出来的值保持一致
public class ResultList
{
public int Id{get;set;}
public int Name{get;set;}
public int File{get;set;}
public int Photo{get;set;}
public int Cert{get;set;}
}
完成后使用就简单,可以简写
//一般我们使用
var Data = _userServer.query<ResultList>();
//或者
var data =DbContext.set<ResultList>();
//还有
var data =DbContext.set<ResultList>().FromSqlRaw("sql");
//直接使用 注意,加了s的实例后的名字
var data =DbContext.ResultLists.FromSqlRaw("sql").ToList(); //注意后面得加上ToList()或者其他的
上述为我处理的方式简写版本可能写的改的有漏了,更多可以在这上面加其他功能