🥇在 C#.NET 中优化 LINQ 查询的方法

介绍

В 本文考虑了一些优化方法 LINQ 查询.
在这里，我们提出了更多与以下相关的代码优化方法 LINQ 查询.

已知的是 LINQ（语言集成查询）是一种用于查询数据源的简单且方便的语言。

А LINQ 到 SQL 是一种访问 DBMS 中的数据的技术。这是一个处理数据的强大工具，查询是通过声明性语言构建的，然后将其转换为 SQL查询 平台并发送到数据库服务器执行。在我们的例子中，DBMS 的意思是 MS SQL Server.

但是， LINQ 查询 没有转换成最佳编写的 SQL查询，经验丰富的 DBA 可以编写包含优化的所有细微差别的内容 SQL 查询:

最佳连接（注册）并过滤结果（NeoCity)
使用连接和组条件时存在许多细微差别
更换条件有很多变化 IN 上 EXISTSи 不在, <> 上 EXISTS
通过临时表、CTE、表变量对结果进行中间缓存
使用句子（OPTION）带有说明和表格提示与（......）
使用索引视图作为在选择过程中消除冗余数据读取的方法之一

由此产生的主要性能瓶颈 SQL 查询 编译时 LINQ 查询 分别是：

将整个数据选择机制整合到一个请求中
复制相同的代码块，最终导致多次不必要的数据读取
多组条件（逻辑“与”和“或”）- AND и OR，组合成复杂的条件，导致优化器为必要的字段具有合适的非聚集索引，最终开始扫描聚集索引（索引扫描) 按条件组
子查询的深层嵌套使得解析非常成问题 SQL语句 以及开发人员的查询计划分析 DBA

优化方法

现在让我们直接转向优化方法。

1) 附加索引

最好考虑对主选择表进行过滤，因为整个查询通常是围绕一个或两个主表（应用程序-人员-操作）并使用一组标准条件（IsClosed、Canceled、Enabled、Status）构建的。为已识别的样本创建适当的索引非常重要。

当选择这些字段显着限制返回的查询集时，此解决方案是有意义的。

例如，我们有 500000 个应用程序。然而，只有 2000 个活跃应用程序。那么正确选择的索引将使我们免于 索引扫描 在大型表上，将允许您通过非聚集索引快速选择数据。

此外，还可以通过解析查询计划或收集系统视图统计信息的提示来识别索引的缺失 MS SQL Server:

所有视图数据都包含有关缺失索引的信息（空间索引除外）。

然而，索引和缓存通常是对抗写得不好所造成的后果的方法。 LINQ 查询 и SQL 查询.

正如生活中的严酷实践所表明的那样，对于企业来说，在特定期限内实现业务功能通常很重要。因此，大量请求通常会通过缓存转移到后台。

这在一定程度上是合理的，因为用户并不总是需要最新的数据，并且用户界面的响应能力处于可接受的水平。

这种方法可以解决业务需求，但最终会因为延迟问题的解决而降低信息系统的性能。

还值得记住的是，在搜索要添加的必要索引的过程中，建议 MS SQL 优化可能不正确，包括在以下条件下：

如果已经存在具有相似字段集的索引
如果由于索引限制而无法对表中的字段建立索引（更详细地描述这里).

2) 将属性合并为一个新属性

有时，一个表中作为一组条件基础的某些字段可以通过引入一个新字段来替换。

对于状态字段尤其如此，这些字段的类型通常是位或整数。

示例：

已关闭 = 0 且已取消 = 0 且已启用 = 0 替换为 状态 = 1.

这是引入整数 Status 属性的地方，以确保这些状态填充在表中。接下来，为这个新属性建立索引。

这是性能问题的根本解决方案，因为我们访问数据时不需要进行不必要的计算。

3）视图的物化

不幸的是，在 LINQ 查询 临时表、CTE 和表变量不能直接使用。

但是，还有另一种方法可以针对这种情况进行优化 - 索引视图。

条件组（来自上面的示例） 已关闭 = 0 且已取消 = 0 且已启用 = 0 （或一组其他类似条件）成为在索引视图中使用它们的好选择，缓存大集合中的一小部分数据。

但具体化视图时存在许多限制：

使用子查询、子句 EXISTS 应替换为使用注册
你不能使用句子 UNION, UNION ALL, 例外, 相交
您不能使用表提示和子句 OPTION
无法使用循环
无法在一个视图中显示不同表的数据

重要的是要记住，使用索引视图的真正好处只能通过实际索引来实现。

但是在调用视图时，可能不会使用这些索引，并且要显式使用它们，必须指定 有（不扩展）.

自从在 LINQ 查询 定义表提示是不可能的，因此您必须创建另一种表示形式 - 如下形式的“包装器”：

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4）使用表函数

经常在 LINQ 查询 大块子查询或使用具有复杂结构的视图的块形成具有非常复杂且次优执行结构的最终查询。

使用表函数的主要好处 LINQ 查询:

与视图的情况一样，能够作为对象使用和指定，但您可以传递一组输入参数：
来自函数（@param1，@param2 ...）
从而可以实现灵活的数据采样
在使用表函数的情况下，没有上述索引视图的情况那样的严格限制：
1. 表提示：
  通过 LINQ 您无法在查询时指定应使用哪些索引并确定数据隔离级别。
  但该函数具有这些功能。
  使用该函数，您可以实现相当恒定的执行查询计划，其中定义了使用索引和数据隔离级别的规则
2. 与索引视图相比，使用该函数可以获得：
  - 复杂的数据采样逻辑（甚至使用循环）
  - 从许多不同的表中获取数据
  - 使用 UNION и EXISTS
建议 OPTION 当我们需要提供并发控制时非常有用 选项（最大DOP N），查询执行计划的顺序。例如：
- 您可以指定强制重新创建查询计划 选项（重新编译）
- 您可以指定是否强制查询计划使用查询中指定的连接顺序 选项（强制命令）
更多详情 OPTION 描述这里.
使用最窄且最需要的数据切片：
无需在缓存中存储大型数据集（与索引视图的情况相同），您仍然需要通过参数从中过滤数据。
例如，有一个表，其过滤器 NeoCity 使用了三个字段 （一、二、三）.
传统上，所有请求都有一个恒定的条件 a = 0 和 b = 0.
但是，该字段的请求 c 更多变数。
让条件 a = 0 和 b = 0 它确实帮助我们将所需的结果集限制为数千条记录，但是条件 с 将选择范围缩小到一百条记录。
这里表函数可能是更好的选择。
此外，表函数在执行时间上更具可预测性和一致性。

Примеры

让我们看一下使用问题数据库作为示例的示例实现。

有一个请求选择，它组合了多个表并使用一个视图（OperativeQuestions），其中通过电子邮件检查从属关系（通过 EXISTS）到“操作问题”：

要求 1

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL) 
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));

该视图具有相当复杂的结构：它具有子查询连接并使用排序 DISTINCT，，这通常是一个相当资源密集型的操作。

OperativeQuestions 的样本大约有一万条记录。

此查询的主要问题是，对于外部查询中的记录，在 [OperativeQuestions] 视图上执行内部子查询，这对于 [Email] = @p__linq__0 应该允许我们限制输出选择（通过 EXISTS）多达数百条记录。

看起来子查询应该通过 [Email] = @p__linq__0 计算一次记录，然后将这几百条记录通过 Id 和 Questions 连接起来，查询会很快。

事实上，所有表之间存在顺序连接：检查 Id Questions 与 OperativeQuestions 中的 Id 的对应关系，并通过 Email 进行过滤。

事实上，该请求适用于所有数以万计的 OperativeQuestions 记录，但仅需要通过电子邮件获取感兴趣的数据。

OperativeQuestions查看文本：

要求 2

 
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM            [dbo].Questions AS Q INNER JOIN
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
                     (SELECT   1 AS HasNoObjects
                      WHERE   NOT EXISTS
                                    (SELECT   1
                                     FROM     [dbo].ObjectUserAccesses AS BOU
                                     WHERE   BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE        CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL 
AND (BO.HasNoObjects = 1 OR
              EXISTS (SELECT   1
                           FROM   [dbo].ObjectUserAccesses AS BOU INNER JOIN
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               WHERE  BOU.ProcessUserAccessId = BPU.Id 
                               AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));

DbContext 中的初始视图映射 (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

初始 LINQ 查询

var businessObjectsData = await context
    .OperativeQuestions
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

在这种特殊情况下，我们正在考虑在不改变基础设施的情况下解决这个问题，不引入具有现成结果的单独表（“活动查询”），这需要一种机制来填充数据并保持最新。

虽然这是一个很好的解决方案，但还有另一种选择来优化这个问题。

主要目的是通过 OperativeQuestions 视图中的 [Email] = @p__linq__0 缓存条目。

将表函数[dbo].[OperativeQuestionsUserMail]引入数据库。

通过发送电子邮件作为输入参数，我们得到一个值表：

要求 3


CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        SELECT Id, @Email
        FROM [OperativeQuestions]  AS [x] WHERE [x].[Email] = @Email;
     
    RETURN;
END

这将返回具有预定义数据结构的值表。

为了使对 OperativeQuestionsUserMail 的查询达到最优并具有最优的查询计划，需要严格的结构，而不是 退货表作为退货...

在本例中，所需的查询 1 转换为查询 4：

要求 4

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
    SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);

在 DbContext 中映射视图和函数 (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

最终 LINQ 查询

var businessObjectsData = await context
    .OperativeQuestions
    .GetByUserEmail(Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

执行时间从 200-800 ms 下降到 2-20 ms 等，即快了数十倍。

如果我们更平均，那么我们得到的不是 350 毫秒，而是 8 毫秒。

从明显的优势我们还可以得到：

普遍减少阅读负担，
显着降低阻塞的可能性
将平均阻塞时间减少到可接受的值

结论

数据库调用的优化和微调 MS SQL 通过 LINQ 是一个可以解决的问题。

专注和一致性在这项工作中非常重要。

在流程开始时：

有必要检查请求所使用的数据（值、所选数据类型）
对此数据进行适当的索引
检查表间连接条件的正确性

下一次优化迭代揭示：

请求的基础并定义主要请求过滤器
重复相似的查询块并分析条件的交集
在 SSMS 或其他 GUI 中 SQL服务器 自我优化 SQL查询 （分配中间数据存储，使用该存储构建结果查询（可能有多个））
在最后阶段，以所得结果为基础 SQL查询，结构正在重建 LINQ查询

所结果的 LINQ查询 结构上应与已确定的最佳结构相同 SQL查询 从第3点开始。

C#.NET 中优化 LINQ 查询的方法

介绍