使用EF框架的优化(一)

在.Net中使用EF框架(.Net7，数据库SQL server)

在Entity Framework (EF)中，LINQ查询会被翻译成对应的SQL查询语句，以便与数据库进行交互。EF根据LINQ查询中的方法调用和操作符来进行翻译，一些常见的规则包括：

1. 简单的查询表达式会直接被翻译成对应的SQL语句，如 SELECT、FROM、WHERE、ORDER BY等。
2. LINQ查询中的方法调用，如 Where、OrderBy、Select等，会被翻译成对应的SQL语句操作符。
3. 集合操作符，如 Contains、Any、All等，会被翻译成SQL中的对应操作符，如 IN、EXISTS等。
4. 字符串操作、数学运算等也会被适当地翻译成SQL中的函数或操作符。
5. 对于复杂的LINQ查询，EF会尽可能地将其优化成效率更高的SQL查询语句。

需要注意的是，虽然EF会尽力将LINQ查询翻译成高效的SQL语句，但在特定情况下可能会存在翻译不准确或效率不佳的情况。为了优化查询性能，可以使用EF性能分析工具进行调优，并在需要时手动编写原生SQL语句来代替LINQ查询。

如果没有优化空间了，写原生的也是一种不错的优化！

EF在将LINQ查询翻译成SQL查询时，会根据查询条件的语义和结构来选择适当的操作符和函数。

先看一下，实际代码翻译成的SQL。

代码一：

 var materials = (await _material.GetQueryableAsync()).ToList();

代码一翻译的SQL：

SELECT [m].[Id], [m].[Comment], [m].[Ctime], [m].[Cuser], [m].[DefaultQuantity], [m].[DefaultStorageGroup], [m].[LowerBound], [m].[MaterialCode], [m].[MaterialGroup], [m].[MaterialType], [m].[Mtime], [m].[Muser], [m].[StandingTime], [m].[Uom], [m].[UpperBound], [m].[ValidDays], [m].[xDescription], [m].[xSpecification]
FROM [Materials] AS [m]

代码二：

 var materials = (await _material.GetQueryableAsync()).Where(x=>x.xDescription.Contains("罗")).ToList();

代码二翻译的SQL：

SELECT [m].[Id], [m].[Comment], [m].[Ctime], [m].[Cuser], [m].[DefaultQuantity], [m].[DefaultStorageGroup], [m].[LowerBound], [m].[MaterialCode], [m].[MaterialGroup], [m].[MaterialType], [m].[Mtime], [m].[Muser], [m].[StandingTime], [m].[Uom], [m].[UpperBound], [m].[ValidDays], [m].[xDescription], [m].[xSpecification]
FROM [Materials] AS [m]
WHERE [m].[xDescription] LIKE N'%罗%'

代码三：

 var materials = (await _material.GetQueryableAsync()).Where(x => x.xDescription.StartsWith("PPP")).ToList();

代码三翻译的SQL：

SELECT [m].[Id], [m].[Comment], [m].[Ctime], [m].[Cuser], [m].[DefaultQuantity], [m].[DefaultStorageGroup], [m].[LowerBound], [m].[MaterialCode], [m].[MaterialGroup], [m].[MaterialType], [m].[Mtime], [m].[Muser], [m].[StandingTime], [m].[Uom], [m].[UpperBound], [m].[ValidDays], [m].[xDescription], [m].[xSpecification]
FROM [Materials] AS [m]
WHERE [m].[xDescription] LIKE N'PPP%'

代码四：

 List<string> n = new List<string> {"zhao","qian","luo" };
 var materials = (await _material.GetQueryableAsync()).Where(x => n.Contains(x.xDescription)).ToList();

代码四翻译的SQL：

SELECT [m].[Id], [m].[Comment], [m].[Ctime], [m].[Cuser], [m].[DefaultQuantity], [m].[DefaultStorageGroup], [m].[LowerBound], [m].[MaterialCode], [m].[MaterialGroup], [m].[MaterialType], [m].[Mtime], [m].[Muser], [m].[StandingTime], [m].[Uom], [m].[UpperBound], [m].[ValidDays], [m].[xDescription], [m].[xSpecification]
FROM [Materials] AS [m]
WHERE [m].[xDescription] IN (N'zhao', N'qian', N'luo')

代码五：

  var materials = (await _material.GetQueryableAsync()).Where(x => x.xDescription.Contains("asd")).ToList();

代码五翻译成的SQL：

SELECT [m].[Id], [m].[Comment], [m].[Ctime], [m].[Cuser], [m].[DefaultQuantity], [m].[DefaultStorageGroup], [m].[LowerBound], [m].[MaterialCode], [m].[MaterialGroup], [m].[MaterialType], [m].[Mtime], [m].[Muser], [m].[StandingTime], [m].[Uom], [m].[UpperBound], [m].[ValidDays], [m].[xDescription], [m].[xSpecification]
FROM [Materials] AS [m]
WHERE [m].[xDescription] LIKE N'%asd%'

代码六：

 string sss = "asd";
 var materials = (await _material.GetQueryableAsync()).Where(x => x.xDescription.Contains(sss)).ToList();

代码六翻译成的SQL：

exec sp_executesql N'SELECT [m].[Id], [m].[Comment], [m].[Ctime], [m].[Cuser], [m].[DefaultQuantity], [m].[DefaultStorageGroup], [m].[LowerBound], [m].[MaterialCode], [m].[MaterialGroup], [m].[MaterialType], [m].[Mtime], [m].[Muser], [m].[StandingTime], [m].[Uom], [m].[UpperBound], [m].[ValidDays], [m].[xDescription], [m].[xSpecification]
FROM [Materials] AS [m]
WHERE (@__sss_0 LIKE N'''') OR CHARINDEX(@__sss_0, [m].[xDescription]) > 0',N'@__sss_0 nvarchar(255)',@__sss_0=N'asd'

这段SQL语句使用了`exec sp_executesql`来执行动态SQL查询。该查询语句筛选了Materials表中xDescription列包含子字符串"asd"的记录。在这里，参数@__sss_0被用于传递查询条件，通过CHARINDEX函数进行模糊匹配。通过使用sp_executesql存储过程，可以安全地执行动态SQL语句并提高执行效率。

在某些情况下，Entity Framework可以将LINQ查询翻译成使用存储过程的SQL语句。这通常是由于EF的查询优化策略，以及查询中的复杂性或性能需求导致的选择。存储过程通常用于执行复杂的逻辑或需要高性能的查询，因此EF可能会选择将查询翻译成存储过程来提高性能或处理特定的业务逻辑需求。

另外，使用存储过程还可以提供更好的查询计划重用性、参数化查询、查询的缓存等优势，以及一些数据库级别的安全性控制。因此，在特定情况下，EF可能会选择翻译LINQ查询成使用存储过程的SQL来执行查询。

在EF中，当使用带参数的Contains方法时，EF会尝试将该操作翻译成数据库的函数或操作符。在一些数据库系统中，类似于SQL Server这样的系统并不直接支持带参数的Contains方法。因此，EF会将带参数的Contains翻译成相应数据库系统支持的函数或操作符。

在SQL Server中，CHARINDEX函数可以用来实现类似于带参数的Contains方法的功能，用于查找子字符串在另一个字符串中的位置。因此，EF可能会选择将带参数的Contains翻译成SQL Server支持的CHARINDEX函数来执行查询。

这种转换的目的是为了确保LINQ查询在不同数据库系统中的兼容性和正确性，同时确保在数据库级别上执行的查询语句具有最佳性能。因此，EF在翻译带参数的Contains时选择使用CHARINDEX函数是为了适应不同数据库系统，并尽可能提供高效的查询执行。

代码五和代码六的对比：

这两个查询语句的作用是相同的，都是查询Materials表中xDescription字段包含"asd"字符串的记录。两者的区别在于第一个查询使用了LIKE运算符，而第二个查询将条件翻译成了使用CHARINDEX函数。

从性能和查询执行效率角度来说，使用CHARINDEX函数的查询可能会更加高效。因为CHARINDEX函数通常比LIKE运算符更快速，尤其是在大型数据库中进行模糊查询时。CHARINDEX函数针对指定的字符串进行精准的查找，而LIKE运算符则会对模糊匹配进行处理，可能会导致性能上的损耗。

因此，从性能和查询效率的角度来看，使用CHARINDEX函数的查询可能会更好一些。但是在实际应用中，具体选择何种查询方式还需根据实际情况和需求来进行评估和选择。

-----------------------------------------------------------------------------------------------------------------------------------

在使用Entity Framework进行查询时，EF会尝试将LINQ查询中的Contains方法翻译成对应的SQL查询语句，具体翻译成IN操作符还是LIKE操作符取决于查询条件的具体情况：

当使用Contains方法进行查询时，如果查询条件是针对一个集合中的多个数值进行匹配时，EF会将Contains方法翻译成SQL中的IN操作符。比如在查询时，使用了集合来传递多个值进行匹配时，EF会将其翻译成IN操作符的SQL查询语句。
当使用Contains方法进行查询时，如果查询条件是针对一个字符串进行模糊匹配时，EF会将Contains方法翻译成SQL中的LIKE操作符。比如在查询时，使用Contains方法来查找字符串中包含特定子字符串时，EF会将其翻译成LIKE操作符的SQL查询语句。

需要注意的是，EF在进行查询翻译时会根据具体的查询条件和数据库类型进行转换，开发人员可以根据实际情况选择合适的查询方式来优化数据库查询性能。

说明：有人在写字符串匹配时，翻译成CHARINDEX，有参数时就会出现。CHARINDEX 函数来查找子字符串的位置。CHARINDEX(substring, string [, start_location])

解释：

在C#中，Contains方法是用于检查集合或数组中是否包含指定元素的方法。当调用Contains方法时，它会遍历集合或数组中的每个元素，并比较是否与指定元素相等。如果找到匹配的元素，则返回true；否则返回false。Contains方法通常用于判断一个集合中是否包含特定元素，以便采取相应的逻辑操作。

在C#中，以下类型的对象可以使用Contains方法：

1. 集合类对象，如：
- List<T>
- HashSet<T>
- Dictionary<TKey, TValue>
- Queue<T>
- Stack<T>
- ObservableCollection<T>

2. 数组类对象，如：
- 数组

3. 字符串类型的对象，用于检查子字符串的存在。

在C#中，除了IEnumerable<T>接口的对象外，还有一些类似的接口或对象可以通过LINQ的Contains扩展方法来检查是否包含特定元素，例如：

1. ICollection<T>接口的对象，如List<T>、HashSet<T>等。
2. IQueryable<T>接口的对象，通常用于与数据库进行交互的LINQ查询。
3. IQueryable接口的对象，表示实现了LINQ查询的数据源。
4. IDictionary<TKey, TValue>接口的对象，如Dictionary<TKey, TValue>等。

这些接口或对象通常可以通过LINQ的Contains扩展方法来检查是否包含特定元素，进而进行相应的处理。

——————————————————————————————————————————————————————————————————————————————————————————————

在Entity Framework (EF) 中使用 Contains() 方法可能会导致索引失效的情况，并非完全不存在。当在 EF 查询中使用 Contains() 方法时，EF 会将其翻译成 SQL 中的 IN 操作符，而某些数据库管理系统在处理大型 IN 操作时可能会导致索引失效，从而影响查询性能。

通常情况下，对于包含大量元素的 IN 操作，数据库优化器可能会选择不使用索引，而是进行全表扫描，这可能导致查询性能下降。为避免这种情况，可以考虑以下策略：

1. 尽量减少 Contains() 方法的使用，尤其是当集合中包含大量元素时。可以考虑拆分查询或使用其他方法来替代 Contains()。
2. 确保数据库表的索引适当设置，以便优化 IN 操作的性能。
3. 使用 EF 的性能分析工具，如 SQL Profiler 或 Entity Framework Profiler，来检测性能问题并进行优化。

总的来说，虽然在某些情况下使用 Contains() 方法可能会导致索引失效，但在实际开发中，可以通过一些优化策略来提高查询性能。

程序优化三大点：缓存、异步、SQL

优化点一：

判断查询出的列表是否有值时，使用 .Any(),尽量不使用 .Count(); .FirstOrDefault()

优化点二：

正确使用Find(id=10)来代替FirstOrDefault(t=>t.id=10)，Find会优先查询缓存，当前面已经查询过这条数据的时候使用，而FirstOrDefault每次都会查询数据库；当id=10的数据被修改之后，find查出的数据是新数据。

优化点三：

禁用实体追踪当我们从数据库中查询出数据时，上下文就会创建实体快照，从而追踪实体。在调用 SaveChanges 时，实体有任何更改都会保存到数据库中。

但是当我们只需要查询出实体而不需要修改时（只读），实体追踪就没有任何用途了。这时我们就可以调用 AsNoTracking 获取非追踪的数据，这样可以提高查询性能。

var users = db.Users.AsNoTracking().ToList();
注：如果是多表查询可以在查询前
db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
这样就把所有表查询设置成了非追踪状态

优化点四：正常情况优化程度不高，除非特复杂的SQL

使用 EF.Functions.xxx 进行查询，

(1).使用 EF.Functions.Like进行模糊查询要比 StartsWith、Contains 和 EndsWith 方法生成的SQL语句性能更优。

注：在Entity Framework Core 2.0中增加一个很酷的功能：EF.Functions.Like()，最终解析为SQL中的Like语句，以便于在 LINQ 查询中直接调用。需要引用Microsoft.EntityFrameworkCore命名空间用的是Like

使用固定字符串 EF LINQ 语法转换SQL 时，Contains、StartsWith、EndsWith都转换成了LIKE ；但是使用字符串变量时，Contains、StartsWith、EndsWith分别转换成了 CHARINDEX、LEFT、RIGHT

  var data1 = dbContext.T_UserInfor.Where(u => EF.Functions.Like(u.userName, "%p%")).ToList();
  //或者
  var data2 = (from p in dbContext.T_UserInfor
               where EF.Functions.Like(p.userName, "%p%")
               select p).ToList();

(2).还有EF.Functions.DateDiffDay (DateDiffHour、DateDiffMonth),求天、小时、月之间的数量。

优化点五：单表查询优化

1、EF Core框架已经本地缓存机制memorycache，所以我们访问一个接口，二次访问的性能相比首次会提升一大截

2、尽可能的通过主键查询

3、在进行字符串模糊查询时，分为三种情况

//StartsWith，相当于sql语句的like 'A%'
var result= ProductContext.Products.Where(p => p.ProductName.StartsWith("A")).ToList();

//EndsWith，相当于sql语句的like '%A'
var result= ProductContext.Products.Where(p => p.ProductName.EndsWith("A")).ToList();

//Contains，相当于sql语句的like '%A%'
var result= ProductContext.Products.Where(p => p.ProductName.Contains("A")).ToList();

其中的Contains()会导致索引失效，查询比较大的数据时不建议使用

4、指定列查询。即字段查询、传输需要时间，字段越多，所需的时间就越多，所以我们可以指定我们所需的字段

ProductContext.Products.Select(p =>new { p.ProductId, p.ProductName})

翻译的SQL：

select ProductId,ProductName from Products

5、分页查询（常用于客户端查询）

int pageIndex = 1;
int pageSize = 10;

var result= ProductContext.Products
                .Where(p => p.ProductName.StartsWith("A"))
                .Take(pageSize) // 限制结果集数量。
                .Skip((pageIndex - 1) * pageSize) // 数据的偏移量
                .ToList();

6、一次性查询数据量较多时(如导出报表)，借助缓冲区处理，即直接ToList()、ToArray()

ps：某些时候使用缓冲区而不是缓存，是因为缓冲区使用时会清空，而缓存不到过期时间不自动清空，某些场景下会浪费内存空间

//默认流式处理，遍历使用result时每次循环都会查询数据库
var result= ProductContext.Products.Where(p => p.ProductName.StartsWith("A"));

//缓冲区处理（一次性将数据查出，使用result时，直接从队列中取数据）
var result= ProductContext.Products.Where(p => p.ProductName.StartsWith("A")).ToList();

7、EF Core会对查询出来的数据进行缓存、跟踪。跟踪监控造成额外的空间浪费，但能方便更新数据，所以在不涉及修改的情况下（只查询时），我们可以用AsNoTracking()方法来手动关闭跟踪

var result= ProductContext.Products
            .Where(p => p.ProductName.StartsWith("A"))
            .AsNoTracking()
            .ToList();

8、使用异步 ToListAsync()

优化点六：多表联合查询优化

1、懒加载Include()，关联查询一次性加载

//主表为product表，副表为产品变更日志表productLogs
var result= ProductContext.Product.Include(p=> p.productLogs).ToList();

这里会存在笛卡尔积的问题，即副表关联数据为null时（假设某产品没有变更记录），也会查询副表，如果副表null数据较多时，会造成性能下降。

那么我们可以通过拆分查询AsSplitQuery() 解决这个问题

var result= ProductContext.Product.Include(p=> p.productLogs).AsSplitQuery().ToList();

原理
默认预先加载（懒加载）时，EF core为我们生成的sql语句为left join语句，查询结果为主表、副表的所有字段；右表数据的字段会存在null。
数据库查询进行笛卡尔积查询，实际查询了4次
拆分查询时，EF Core会生成两个sql语句：
1、单表查询主表product
2、主表product与副表productLogs进行inner join，查询结果为副表的所有字段
实际查询了2次
所以会提升性能

2、自定义sql语句。即不使用EF Core本身生成的sql，写原生SQL，大数据处理适用。

var result= ProductContext.Product.FromSqlRaw("select * from product").ToList();

posted @ 2024-04-29 16:38 IT苦行僧-QF 阅读(5) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

IT苦行僧-QF