🥇C#.NET 中最佳化 LINQ 查詢的方法

介紹

В 這篇文章考慮了一些最佳化方法 LINQ 查詢.
這裡我們也介紹了一些與以下相關的程式碼最佳化方法 LINQ 查詢.

已知的是聯機（語言整合查詢）是一種用於查詢資料來源的簡單且方便的語言。

А LINQ 到 SQL 是一種存取 DBMS 中的資料的技術。這是一個處理資料的強大工具，查詢是透過聲明性語言建構的，然後將其轉換為 SQL查詢 平台並發送到資料庫伺服器執行。在我們的例子中，DBMS 的意思是 MS SQL Server.

但是， LINQ 查詢 沒有轉換成最佳編寫的 SQL查詢，經驗豐富的 DBA 可以編寫包含優化的所有細微差別的內容 SQL查詢:

最佳連接（註冊）並過濾結果（WHERE)
使用連接和群組條件時存在許多細微差別
更換條件有很多變化 IN 上存在и 不在, <> 上存在
透過臨時表、CTE、表變數對結果進行中間緩存
使用句子（股權）帶有說明和表格提示與（......）
使用索引視圖作為在選擇過程中消除冗餘資料讀取的方法之一

由此產生的主要效能瓶頸 SQL查詢 編譯時 LINQ 查詢 它們是：

將整個資料選擇機制整合到一個請求中
複製相同的程式碼區塊，最終導致多次不必要的資料讀取
多組條件（邏輯“與”和“或”）- 與 и 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 （或一組其他類似條件）成為在索引視圖中使用它們的好選擇，緩存大集合中的一小部分資料。

但具體化視圖時有許多限制：

使用子查詢、子句存在應替換為使用註冊
你不能使用句子 UNION, 全聯盟, 例外, 相交
您不能使用表提示和子句股權
無法使用循環
無法在一個視圖中顯示不同表的數據

重要的是要記住，使用索引視圖的真正好處只能透過實際索引來實現。

但是在呼叫視圖時，可能不會使用這些索引，並且要明確使用它們，必須指定 有（不擴展）.

自從在 LINQ 查詢 定義表提示是不可能的，因此您必須建立另一種表示形式 - 如下形式的「包裝器」：

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

4）使用表函數

經常在 LINQ 查詢 大塊子查詢或使用具有複雜結構的視圖的區塊形成具有非常複雜且次優執行結構的最終查詢。

使用表函數的主要好處 LINQ 查詢:

與視圖的情況一樣，能夠作為物件使用和指定，但您可以傳遞一組輸入參數：
來自函數（@param1，@param2 ...）
從而可以實現靈活的數據採樣
在使用表函數的情況下，沒有上述索引視圖的情況那樣的嚴格限制：
1. 表提示：
  通過聯機您無法在查詢時指定應使用哪些索引並確定資料隔離等級。
  但該函數具有這些功能。
  使用該函數，您可以實現相當恆定的執行查詢計劃，其中定義了使用索引和資料隔離等級的規則
2. 與索引視圖相比，使用該函數可以獲得：
  - 複雜的資料採樣邏輯（甚至使用循環）
  - 從許多不同的表中獲取數據
  - 使用 UNION и 存在
提供股權當我們需要提供並發控制時非常有用 選項（最大DOP N），查詢執行計劃的順序。例如：
- 您可以指定強制重新建立查詢計劃 選項（重新編譯）
- 您可以指定是否強制查詢計畫使用查詢中指定的連線順序 選項（強制命令）
有關更多詳細信息股權描述這裡.
使用最窄且最需要的資料切片：
無需在快取中儲存大型資料集（與索引視圖的情況相同），您仍然需要透過參數從中過濾資料。
例如，有一個表，其過濾器 WHERE 使用了三個字段 （一、二、三）.

傳統上，所有請求都有一個恆定的條件 a = 0 且 b = 0.

但是，該欄位的請求 c 更多變數。

讓條件 a = 0 且 b = 0 它確實幫助我們將所需的結果集限制為數千筆記錄，但是條件 с 將選擇範圍縮小到一百筆記錄。

這裡表函數可能是更好的選擇。

此外，表函數在執行時間上更具可預測性和一致性。

Примеры

讓我們來看看使用問題資料庫作為範例的範例實作。

有一個請求選擇，它組合了多個表並使用一個視圖（OperativeQuestions），其中透過電子郵件檢查從屬關係（透過存在）到「操作問題」：

要求 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 應該允許我們限制輸出選擇（透過存在）多達數百筆記錄。

看起來子查詢應該透過 [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 通過聯機是一個可以解決的問題。

專注和一致性在這項工作中非常重要。

在流程開始時：

有必要檢查請求所使用的資料（值、所選資料類型）
對此數據進行適當的索引
檢查表間連接條件的正確性

下一次優化迭代顯示：

請求的基礎並定義主要請求過濾器
重複相似的查詢區塊並分析條件的交集
在 SSMS 或其他 GUI 中 SQL服務器 自我優化 SQL查詢 （分配中間資料存儲，使用該存儲建立結果查詢（可能有多個））
在最後階段，以所得結果為基礎 SQL查詢，結構正在重建 LINQ查詢

所結果的 LINQ查詢 結構上應與已確定的最佳結構相同 SQL查詢 從第3點開始。

致謝

非常感謝同事們工作寶石 и 亞歷克斯·奧茲來自公司 Fortis 尋求協助準備本資料。

來源： www.habr.com

C#.NET 中最佳化 LINQ 查詢的方法

介紹