Prohoster > Blog > quản lý > Các phương pháp tối ưu hóa truy vấn LINQ trong C#.NET

Các phương pháp tối ưu hóa truy vấn LINQ trong C#.NET

Giới thiệu

В bài viết này một số phương pháp tối ưu hóa đã được xem xét truy vấn LINQ.
Ở đây chúng tôi cũng trình bày thêm một số cách tiếp cận để tối ưu hóa mã liên quan đến truy vấn LINQ.

Được biết, LINQ(Truy vấn tích hợp ngôn ngữ) là ngôn ngữ đơn giản và thuận tiện để truy vấn nguồn dữ liệu.

А LINQ sang SQL là một công nghệ để truy cập dữ liệu trong DBMS. Đây là một công cụ mạnh mẽ để làm việc với dữ liệu, trong đó các truy vấn được xây dựng thông qua ngôn ngữ khai báo, sau đó sẽ được chuyển đổi thành truy vấn SQL nền tảng và gửi đến máy chủ cơ sở dữ liệu để thực thi. Trong trường hợp của chúng tôi, khi nói đến DBMS, ý chúng tôi là Máy chủ MS SQL.

Tuy nhiên, truy vấn LINQ không được chuyển đổi thành văn bản tối ưu truy vấn SQL, mà một DBA có kinh nghiệm có thể viết với tất cả các sắc thái tối ưu hóa truy vấn SQL:

  1. kết nối tối ưu (THAM GIA) và lọc kết quả (Ở ĐÂU)
  2. nhiều sắc thái trong việc sử dụng kết nối và điều kiện nhóm
  3. nhiều biến thể trong điều kiện thay thế IN trên TỒN TẠIи KHÔNG VÀO, <> trên TỒN TẠI
  4. bộ nhớ đệm trung gian của kết quả thông qua các bảng tạm thời, CTE, biến bảng
  5. cách dùng câu (TÙY CHỌN) với hướng dẫn và gợi ý về bảng CÙNG VỚI (...)
  6. sử dụng các chế độ xem được lập chỉ mục như một trong những phương tiện để loại bỏ việc đọc dữ liệu dư thừa trong quá trình lựa chọn

Các tắc nghẽn hiệu suất chính của kết quả truy vấn SQL khi biên dịch truy vấn LINQ là:

  1. hợp nhất toàn bộ cơ chế lựa chọn dữ liệu trong một yêu cầu
  2. sao chép các khối mã giống hệt nhau, cuối cùng dẫn đến việc đọc nhiều dữ liệu không cần thiết
  3. nhóm điều kiện nhiều thành phần (logic “và” và “hoặc”) - VÀ и OR, kết hợp thành các điều kiện phức tạp, dẫn đến thực tế là trình tối ưu hóa, có các chỉ mục không được phân cụm phù hợp cho các trường cần thiết, cuối cùng bắt đầu quét theo chỉ mục được phân cụm (QUÉT CHỈ SỐ) theo nhóm điều kiện
  4. việc lồng sâu các truy vấn con làm cho việc phân tích cú pháp trở nên rất khó khăn câu lệnh SQL và phân tích kế hoạch truy vấn từ phía các nhà phát triển và DBA

Các phương pháp tối ưu hóa

Bây giờ hãy chuyển trực tiếp sang các phương pháp tối ưu hóa.

1) Lập chỉ mục bổ sung

Tốt nhất nên xem xét các bộ lọc trên các bảng lựa chọn chính, vì thông thường toàn bộ truy vấn được xây dựng xung quanh một hoặc hai bảng chính (ứng dụng-con người-hoạt động) và với một bộ điều kiện tiêu chuẩn (Đã đóng, Đã hủy, Đã bật, Trạng thái). Điều quan trọng là tạo ra các chỉ số thích hợp cho các mẫu được xác định.

Giải pháp này có ý nghĩa khi việc chọn các trường này hạn chế đáng kể tập hợp được trả về cho truy vấn.

Ví dụ: chúng tôi có 500000 đơn đăng ký. Tuy nhiên, chỉ có 2000 ứng dụng đang hoạt động. Sau đó, một chỉ mục được chọn chính xác sẽ cứu chúng ta khỏi QUÉT CHỈ SỐ trên một bảng lớn và sẽ cho phép bạn nhanh chóng chọn dữ liệu thông qua chỉ mục không được nhóm.

Ngoài ra, việc thiếu chỉ mục có thể được xác định thông qua lời nhắc phân tích kế hoạch truy vấn hoặc thu thập số liệu thống kê về chế độ xem hệ thống Máy chủ MS SQL:

  1. sys.dm_db_missing_index_groups
  2. sys.dm_db_missing_index_group_stats
  3. sys.dm_db_missing_index_details

Tất cả dữ liệu chế độ xem đều chứa thông tin về các chỉ mục bị thiếu, ngoại trừ các chỉ mục không gian.

Tuy nhiên, chỉ mục và bộ nhớ đệm thường là phương pháp chống lại hậu quả của việc viết kém. truy vấn LINQ и truy vấn SQL.

Như thực tế khắc nghiệt của cuộc sống cho thấy, điều quan trọng đối với một doanh nghiệp là phải triển khai các tính năng kinh doanh theo những thời hạn nhất định. Và do đó, các yêu cầu nặng thường được chuyển xuống nền bằng bộ nhớ đệm.

Điều này một phần hợp lý vì không phải lúc nào người dùng cũng cần dữ liệu mới nhất và giao diện người dùng có mức độ phản hồi chấp nhận được.

Cách tiếp cận này cho phép giải quyết các nhu cầu kinh doanh, nhưng cuối cùng làm giảm hiệu suất của hệ thống thông tin bằng cách trì hoãn các giải pháp cho vấn đề.

Cũng cần nhớ rằng trong quá trình tìm kiếm các chỉ mục cần thiết để thêm, các gợi ý MSSQL tối ưu hóa có thể không chính xác, bao gồm cả trong các điều kiện sau:

  1. nếu đã có các chỉ mục có tập hợp trường tương tự
  2. nếu các trường trong bảng không thể lập chỉ mục do hạn chế lập chỉ mục (được mô tả chi tiết hơn đây).

2) Hợp nhất các thuộc tính thành một thuộc tính mới

Đôi khi một số trường trong một bảng làm cơ sở cho một nhóm điều kiện có thể được thay thế bằng cách đưa vào một trường mới.

Điều này đặc biệt đúng đối với các trường trạng thái, thường là kiểu bit hoặc số nguyên.

Ví dụ:

IsClosed = 0 VÀ Đã hủy = 0 VÀ Đã bật = 0 được thay thế bởi Trạng thái = 1.

Đây là nơi giới thiệu thuộc tính Trạng thái số nguyên để đảm bảo rằng các trạng thái này được đưa vào bảng. Tiếp theo, thuộc tính mới này được lập chỉ mục.

Đây là giải pháp cơ bản cho vấn đề hiệu suất vì Chúng tôi truy cập dữ liệu mà không cần tính toán không cần thiết.

3) Vật chất hóa khung nhìn

Thật không may, trong truy vấn LINQ Các bảng tạm thời, CTE và các biến bảng không thể được sử dụng trực tiếp.

Tuy nhiên, có một cách khác để tối ưu hóa cho trường hợp này - lượt xem được lập chỉ mục.

Nhóm điều kiện (từ ví dụ trên) IsClosed = 0 VÀ Đã hủy = 0 VÀ Đã bật = 0 (hoặc một tập hợp các điều kiện tương tự khác) trở thành một lựa chọn tốt để sử dụng chúng trong chế độ xem được lập chỉ mục, lưu vào bộ đệm một phần dữ liệu nhỏ từ một tập hợp lớn.

Nhưng có một số hạn chế khi hiện thực hóa một chế độ xem:

  1. sử dụng truy vấn con, mệnh đề TỒN TẠI nên được thay thế bằng cách sử dụng THAM GIA
  2. bạn không thể sử dụng câu UNION, ĐOÀN KẾT TẤT CẢ, NGOẠI LỆ, GIAO NHAU
  3. Bạn không thể sử dụng gợi ý và mệnh đề bảng TÙY CHỌN
  4. không có khả năng làm việc với chu kỳ
  5. Không thể hiển thị dữ liệu trong một chế độ xem từ các bảng khác nhau

Điều quan trọng cần nhớ là lợi ích thực sự của việc sử dụng chế độ xem được lập chỉ mục chỉ có thể đạt được bằng cách thực sự lập chỉ mục cho chế độ xem đó.

Nhưng khi gọi một khung nhìn, những chỉ mục này có thể không được sử dụng và để sử dụng chúng một cách rõ ràng, bạn phải chỉ định VỚI(KHÔNG MỞ RỘNG).

Kể từ trong truy vấn LINQ Không thể xác định gợi ý bảng, vì vậy bạn phải tạo một cách biểu diễn khác - một "trình bao bọc" có dạng sau:

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

4) Sử dụng hàm bảng

Thường trong truy vấn LINQ Các khối truy vấn con lớn hoặc các khối sử dụng dạng xem có cấu trúc phức tạp sẽ tạo thành một truy vấn cuối cùng có cấu trúc thực thi rất phức tạp và chưa tối ưu.

Lợi ích chính của việc sử dụng hàm bảng trong truy vấn LINQ:

  1. Khả năng, như trong trường hợp các khung nhìn, được sử dụng và chỉ định làm đối tượng, nhưng bạn có thể chuyển một tập hợp các tham số đầu vào:
    TỪ CHỨC NĂNG(@param1, @param2 ...)
    Kết quả là có thể đạt được việc lấy mẫu dữ liệu linh hoạt
  2. Trong trường hợp sử dụng hàm bảng, không có hạn chế mạnh mẽ nào như trong trường hợp các chế độ xem được lập chỉ mục được mô tả ở trên:
    1. Gợi ý bảng:
      xuyên qua LINQ Bạn không thể chỉ định chỉ mục nào sẽ được sử dụng và xác định mức độ cách ly dữ liệu khi truy vấn.
      Nhưng chức năng này có những khả năng này.
      Với hàm này, bạn có thể đạt được kế hoạch truy vấn thực thi khá ổn định, trong đó các quy tắc làm việc với chỉ mục và mức độ tách biệt dữ liệu được xác định
    2. Việc sử dụng chức năng này cho phép, so với các chế độ xem được lập chỉ mục, có được:
      • logic lấy mẫu dữ liệu phức tạp (thậm chí sử dụng vòng lặp)
      • lấy dữ liệu từ nhiều bảng khác nhau
      • việc sử dụng các UNION и TỒN TẠI

  3. Đề nghị TÙY CHỌN rất hữu ích khi chúng ta cần cung cấp khả năng kiểm soát đồng thời TÙY CHỌN(TỐI ĐA N), thứ tự của kế hoạch thực hiện truy vấn. Ví dụ:
    • bạn có thể chỉ định việc tạo lại bắt buộc kế hoạch truy vấn TÙY CHỌN (BIÊN BẢN)
    • bạn có thể chỉ định xem có buộc kế hoạch truy vấn sử dụng thứ tự tham gia được chỉ định trong truy vấn hay không TÙY CHỌN (LỆNH BẮT BUỘC)

    Thêm chi tiết về TÙY CHỌN mô tả đây.

  4. Sử dụng lát dữ liệu hẹp nhất và được yêu cầu nhiều nhất:
    Không cần lưu trữ các tập dữ liệu lớn trong bộ đệm (như trường hợp với các chế độ xem được lập chỉ mục), từ đó bạn vẫn cần lọc dữ liệu theo tham số.
    Ví dụ: có một bảng có bộ lọc Ở ĐÂU ba trường được sử dụng (a,b,c).

    Thông thường, tất cả các yêu cầu đều có điều kiện không đổi a = 0 và b = 0.

    Tuy nhiên, yêu cầu đối với trường c biến đổi hơn.

    Hãy để điều kiện a = 0 và b = 0 Nó thực sự giúp chúng ta giới hạn tập kết quả cần thiết ở mức hàng nghìn bản ghi, nhưng điều kiện trên с thu hẹp lựa chọn xuống còn một trăm bản ghi.

    Ở đây chức năng bảng có thể là một lựa chọn tốt hơn.

    Ngoài ra, chức năng bảng có thể dự đoán được và nhất quán hơn về thời gian thực hiện.

Ví dụ

Hãy xem một ví dụ triển khai sử dụng cơ sở dữ liệu Câu hỏi làm ví dụ.

Có một yêu cầu CHỌN, kết hợp nhiều bảng và sử dụng một chế độ xem (OperativeQuestions), trong đó mối liên kết được kiểm tra qua email (thông qua TỒN TẠI) đến “Câu hỏi hoạt động”:

Yêu cầu số 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])
));

Khung nhìn có cấu trúc khá phức tạp: nó có các phép nối truy vấn con và sử dụng tính năng sắp xếp DISTINCT, nhìn chung là một hoạt động khá tốn nhiều tài nguyên.

Một mẫu từ OperativeQuestions có khoảng mười nghìn bản ghi.

Vấn đề chính với truy vấn này là đối với các bản ghi từ truy vấn bên ngoài, một truy vấn con nội bộ được thực thi trên chế độ xem [OperativeQuestions], điều này sẽ cho phép [Email] = @p__linq__0 cho phép chúng tôi giới hạn lựa chọn đầu ra (thông qua TỒN TẠI) lên tới hàng trăm bản ghi.

Và có vẻ như truy vấn con sẽ tính toán các bản ghi một lần bằng [Email] = @p__linq__0, sau đó vài trăm bản ghi này sẽ được kết nối bằng Id với Câu hỏi và truy vấn sẽ nhanh chóng.

Trên thực tế, có sự kết nối tuần tự của tất cả các bảng: kiểm tra sự tương ứng của Câu hỏi Id với Id từ OperativeQuestions và lọc theo Email.

Trên thực tế, yêu cầu này hoạt động với tất cả hàng chục nghìn bản ghi OperativeQuestions, nhưng chỉ cần dữ liệu quan tâm qua Email.

Văn bản xem OperativeQuestions:

Yêu cầu số 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));

Ánh xạ chế độ xem ban đầu trong 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");
    }
}

Truy vấn LINQ ban đầu

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

Trong trường hợp cụ thể này, chúng tôi đang xem xét giải pháp cho vấn đề này mà không cần thay đổi cơ sở hạ tầng, không giới thiệu một bảng riêng với các kết quả được tạo sẵn (“Truy vấn hoạt động”), bảng này sẽ yêu cầu một cơ chế để điền dữ liệu và cập nhật bảng đó .

Mặc dù đây là một giải pháp tốt nhưng vẫn có một lựa chọn khác để tối ưu hóa vấn đề này.

Mục đích chính là lưu các mục vào bộ đệm theo [Email] = @p__linq__0 từ chế độ xem OperativeQuestions.

Đưa hàm bảng [dbo].[OperativeQuestionsUserMail] vào cơ sở dữ liệu.

Bằng cách gửi Email làm tham số đầu vào, chúng tôi nhận được một bảng giá trị:

Yêu cầu số 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

Điều này trả về một bảng giá trị có cấu trúc dữ liệu được xác định trước.

Để các truy vấn tới OperativeQuestionsUserMail được tối ưu và có các kế hoạch truy vấn tối ưu thì cần phải có một cấu trúc chặt chẽ chứ không phải BẢNG TRẢ LẠI NHƯ TRẢ LẠI...

Trong trường hợp này, Truy vấn 1 bắt buộc được chuyển đổi thành Truy vấn 4:

Yêu cầu số 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]);

Ánh xạ các khung nhìn và chức năng trong 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})");
}

Truy vấn LINQ cuối cùng

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

Thứ tự thời gian thực hiện đã giảm từ 200-800 ms xuống còn 2-20 ms, v.v., tức là nhanh hơn hàng chục lần.

Nếu chúng ta lấy nó ở mức trung bình hơn, thì thay vì 350 mili giây, chúng ta có 8 mili giây.

Từ những lợi thế rõ ràng, chúng tôi cũng nhận được:

  1. giảm tải đọc nói chung,
  2. giảm đáng kể khả năng bị chặn
  3. giảm thời gian chặn trung bình xuống giá trị chấp nhận được

Đầu ra

Tối ưu hóa và tinh chỉnh các cuộc gọi cơ sở dữ liệu MSSQL xuyên qua LINQ là một vấn đề có thể giải quyết được.

Sự chú ý và nhất quán là rất quan trọng trong công việc này.

Khi bắt đầu quá trình:

  1. cần kiểm tra dữ liệu mà yêu cầu hoạt động (giá trị, loại dữ liệu đã chọn)
  2. thực hiện lập chỉ mục thích hợp cho dữ liệu này
  3. kiểm tra tính đúng đắn của điều kiện nối giữa các bảng

Lần lặp tối ưu hóa tiếp theo cho thấy:

  1. cơ sở của yêu cầu và xác định bộ lọc yêu cầu chính
  2. lặp lại các khối truy vấn tương tự và phân tích giao điểm của các điều kiện
  3. trong SSMS hoặc GUI khác cho SQL server tự tối ưu hóa truy vấn SQL (phân bổ bộ lưu trữ dữ liệu trung gian, xây dựng truy vấn kết quả bằng cách sử dụng bộ lưu trữ này (có thể có một số))
  4. ở giai đoạn cuối, lấy kết quả làm cơ sở truy vấn SQL, cấu trúc đang được xây dựng lại truy vấn LINQ

Kết quả truy vấn LINQ phải có cấu trúc giống hệt với cấu trúc tối ưu đã được xác định truy vấn SQL từ điểm 3.

Sự nhìn nhận

Cảm ơn đồng nghiệp nhiều việc làm и alex_ozr Từ công ty căng để được hỗ trợ chuẩn bị tài liệu này.

Nguồn: www.habr.com

Thêm một lời nhận xét