Chuyển tới nội dung chính

12.6 — 5. Query Optimization

Tóm tắt

Tối ưu truy vấn bắt đầu bằng đo, không bằng đoán. Công cụ chính là execution plan, và thứ cần nhìn đầu tiên không phải toán tử đắt nhất mà là chênh lệch giữa số dòng ước lượng và thực tế — sai lệch lớn nghĩa là optimizer đang làm việc với thông tin sai, và mọi quyết định sau đó đều sai theo. Hai vấn đề đặc thù của SQL Server đáng biết: parameter sniffing, khi plan được cache theo tham số lần đầu và tệ hại cho các tham số sau; và catch-all query kiểu WHERE (@x IS NULL OR Col = @x) — mẫu trông gọn nhưng sinh ra một plan dùng chung cho mọi tổ hợp tham số, và nó tệ cho hầu hết.

Mục tiêu bài học​

Sau bài này bạn có thể:

  • Đọc execution plan và biết nhìn vào đâu trước.
  • Nhận ra statistics cũ và sửa nó.
  • Chẩn đoán và xử lý parameter sniffing.
  • Viết truy vấn tìm kiếm nhiều tiêu chí mà vẫn dùng được index.
  • Theo một quy trình tối ưu có phương pháp.

Nội dung bài học​

12.6.1 — Đo trước​

-- SQL Server
SET STATISTICS IO ON;      -- so lan doc trang
SET STATISTICS TIME ON;    -- CPU và thời gian thực
-- Ctrl+M trong SSMS de bat actual execution plan

SELECT l.LeadId, l.CompanyName, u.FullName
FROM   Leads l
JOIN   Users u ON l.AssignedToUserId = u.UserId
WHERE  l.Status = 'New';
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT l.lead_id, l.company_name, u.full_name
FROM   leads l
JOIN   users u ON l.assigned_to_user_id = u.user_id
WHERE  l.status = 'New';

Đo bằng số lần đọc trang (logical reads), không bằng thời gian. Thời gian phụ thuộc vào tải máy, cache, và các truy vấn khác đang chạy. Số lần đọc trang ổn định và so sánh được giữa các lần chạy.

Một truy vấn đọc 500.000 trang để trả về 20 dòng là sai ở đâu đó — bất kể nó mất bao lâu.

Trong PostgreSQL, EXPLAIN chỉ ước lượng; EXPLAIN ANALYZE thật sự chạy truy vấn. Với UPDATE hay DELETE, bọc trong transaction rồi rollback.

12.6.2 — Đọc execution plan​

Theo thứ tự ưu tiên:

1. Ước lượng so với thực tế. Chênh lệch hàng chục lần nghĩa là optimizer đang chọn sai chiến lược dựa trên thông tin sai.

UPDATE STATISTICS Leads WITH FULLSCAN;    -- SQL Server
ANALYZE leads;                             -- PostgreSQL

Statistics cũ là nguyên nhân số một của "truy vấn này hôm qua nhanh, hôm nay chậm". Auto-update statistics của SQL Server chỉ kích hoạt sau khi khoảng 20% số dòng thay đổi — với bảng 50 triệu dòng, đó là 10 triệu thay đổi.

2. Toán tử quét trên bảng lớn. Table Scan hoặc Clustered Index Scan với hàng triệu dòng để trả về ít dòng → thiếu index hoặc truy vấn không SARGable (bài 12.5).

3. Key Lookup với số dòng lớn → cần covering index.

4. Sort đắt → có thể loại bỏ bằng index đã sắp đúng thứ tự.

5. Cảnh báo. SQL Server hiện tam giác vàng cho: implicit conversion, missing statistics, spill sang tempdb.

Implicit conversion đáng nói riêng:

-- CustomerCode là VARCHAR(20), tham số là NVARCHAR
WHERE CustomerCode = @code
-- => CONVERT_IMPLICIT trên CỘT => index không dùng được

Trong .NET, SqlParameter mặc định gửi chuỗi dưới dạng NVARCHAR. Nếu cột là VARCHAR, SQL Server phải chuyển đổi cột — và index chết. Cách sửa: khai rõ SqlDbType.VarChar, hoặc trong EF Core dùng .HasColumnType("varchar(20)").

Đây là một trong những nguyên nhân khó tìm nhất, vì truy vấn trông hoàn toàn đúng.

12.6.3 — Parameter sniffing​

SQL Server cache execution plan theo tham số lần đầu tiên chạy.

CREATE PROCEDURE sp_GetLeadsByStatus @Status NVARCHAR(50) AS
SELECT * FROM Leads WHERE Status = @Status;
Lan dau:  @Status = 'Won'  (10 dong)     -> plan: Index Seek + Key Lookup
Lan sau:  @Status = 'New'  (500.000 dong) -> VAN dung plan cu
=> 500.000 key lookup thay vi mot lan quet

Triệu chứng đặc trưng: cùng một stored procedure, lúc nhanh lúc chậm, và "khởi động lại SQL Server thì hết" — vì cache bị xoá và plan mới được tạo từ tham số khác.

Bốn cách xử lý, theo thứ tự nên thử:

-- 1. OPTION (RECOMPILE) — plan mới mỗi lần. Tốt cho SP chạy ít, tham số lệch nhiều.
SELECT * FROM Leads WHERE Status = @Status OPTION (RECOMPILE);

-- 2. OPTIMIZE FOR UNKNOWN — dùng thống kê trung bình, không sniff
SELECT * FROM Leads WHERE Status = @Status OPTION (OPTIMIZE FOR UNKNOWN);

-- 3. OPTIMIZE FOR giá trị cụ thể — khi biết rõ trường hợp phổ biến
SELECT * FROM Leads WHERE Status = @Status OPTION (OPTIMIZE FOR (@Status = 'New'));

-- 4. Tách thành các SP riêng cho trường hợp lệch nhiều

RECOMPILE tốn CPU biên dịch mỗi lần chạy — chấp nhận được với truy vấn chạy vài chục lần mỗi phút, nhưng không với truy vấn chạy hàng nghìn lần mỗi giây.

SQL Server 2022 có Parameter Sensitive Plan optimization tự cache nhiều plan cho các khoảng giá trị khác nhau, giảm đáng kể vấn đề này.

PostgreSQL xử lý khác: nó dùng plan chung sau 5 lần thực thi prepared statement, và có plan_cache_mode để điều khiển.

12.6.4 — Catch-all query​

-- Mau rat pho bien — va rat te
SELECT * FROM Leads
WHERE (@Status IS NULL OR Status = @Status)
  AND (@UserId IS NULL OR AssignedToUserId = @UserId)
  AND (@Company IS NULL OR CompanyName LIKE '%' + @Company + '%');

Vấn đề: optimizer phải tạo một plan duy nhất hoạt động cho mọi tổ hợp tham số. Nó không biết lần chạy nào sẽ truyền gì, nên thường chọn quét toàn bảng — an toàn cho mọi trường hợp, tệ cho hầu hết.

Ba cách sửa:

-- 1. OPTION (RECOMPILE) — đơn giản nhất, hiệu quả nhất
SELECT * FROM Leads
WHERE (@Status IS NULL OR Status = @Status)
  AND (@UserId IS NULL OR AssignedToUserId = @UserId)
OPTION (RECOMPILE);

Với RECOMPILE, SQL Server biết giá trị thật tại thời điểm chạy và loại bỏ các nhánh @x IS NULL không liên quan — plan được tối ưu cho đúng tổ hợp đó.

-- 2. Dynamic SQL có tham số (LƯU Ý: vẫn phải parameterized)
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Leads WHERE 1=1';

IF @Status IS NOT NULL SET @sql += N' AND Status = @Status';
IF @UserId IS NOT NULL SET @sql += N' AND AssignedToUserId = @UserId';

EXEC sp_executesql @sql,
     N'@Status NVARCHAR(50), @UserId INT', @Status, @UserId;

sp_executesql với tham số là an toàn với SQL injection — khác hẳn với nối chuỗi giá trị vào câu lệnh. Nó cũng cho phép cache plan riêng cho mỗi tổ hợp điều kiện.

// 3. Trong ung dung — EF Core sinh SQL dung theo dieu kien
var query = db.Leads.AsNoTracking();

if (status is not null)  query = query.Where(l => l.Status == status);
if (userId is not null)  query = query.Where(l => l.AssignedToUserId == userId);

var items = await query.ToListAsync(ct);

Cách 3 là mẫu được dùng nhiều nhất trong thực tế, và nó tự nhiên tránh được vấn đề: EF Core chỉ sinh những điều kiện thật sự có.

Còn LIKE '%' + @Company + '%' thì không có cách nào làm nó SARGable — cần full-text search nếu tìm kiếm văn bản là yêu cầu thật.

12.6.5 — N+1 ở tầng SQL​

N+1 không chỉ là vấn đề của ORM:

// SAI — một truy vấn cho MỖI customer
var customers = await db.Customers.ToListAsync(ct);
foreach (var c in customers)
    c.Contacts = await db.Contacts.Where(x => x.CustomerId == c.Id).ToListAsync(ct);

1.000 khách hàng là 1.001 lần đi về database. Mỗi lần chỉ mất 2ms, nhưng tổng là 2 giây — và phần lớn là độ trễ mạng chứ không phải công việc thật.

// DUNG — mot truy van
var customers = await db.Customers
    .Include(c => c.Contacts)
    .AsSplitQuery()
    .ToListAsync(ct);

Chi tiết trong bài Vì sao EF Core bắn 201 query cho 1 màn hình danh sách và ở Module 13.

Dấu hiệu nhận biết trong log SQL: nhiều truy vấn giống hệt nhau, chỉ khác tham số.

12.6.6 — Quy trình tối ưu​

  1. Tìm truy vấn tệ nhất, đừng đoán:
-- SQL Server: top 20 theo tổng thời gian
SELECT TOP 20
       SUBSTRING(t.text, (s.statement_start_offset/2)+1, 200) AS QueryText,
       s.execution_count,
       s.total_logical_reads / s.execution_count AS AvgReads,
       s.total_elapsed_time  / s.execution_count / 1000 AS AvgMs
FROM   sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER  BY s.total_elapsed_time DESC;
-- PostgreSQL (can extension pg_stat_statements)
SELECT calls, mean_exec_time, total_exec_time, query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

Sắp theo tổng thời gian, không theo thời gian trung bình. Một truy vấn 5ms chạy 1 triệu lần tốn nhiều hơn một truy vấn 2 giây chạy 10 lần — và nó cũng dễ sửa hơn.

  1. Đo baseline — số lần đọc trang và thời gian trước khi sửa.
  2. Đọc plan, tìm nguyên nhân gốc.
  3. Sửa một thứ, đo lại. Sửa nhiều thứ cùng lúc thì không biết cái nào có tác dụng.
  4. Kiểm tra trên dữ liệu thật. Truy vấn nhanh trên 1.000 dòng có thể sụp trên 10 triệu.
  5. Ghi lại những gì đã thử và kết quả.

Bước 5 đáng nhấn mạnh: optimizer chọn chiến lược khác nhau tuỳ kích thước dữ liệu. Tối ưu trên môi trường dev với dữ liệu nhỏ thường cho kết luận sai.

12.6.7 — Rà lại code của bạn​

Danh sách rà soát tối ưu truy vấn

  • •Đo bằng số lần đọc trang, không chỉ bằng thời gian.
  • •Kiểm tra chênh lệch ước lượng so với thực tế trước khi tối ưu.
  • •Statistics được cập nhật, nhất là với bảng lớn.
  • •Không có implicit conversion trong plan; kiểu tham số khớp kiểu cột.
  • •Stored procedure hay nhanh chậm thất thường đã được kiểm tra parameter sniffing.
  • •Truy vấn tìm kiếm nhiều tiêu chí không dùng mẫu catch-all không có RECOMPILE.
  • •Dynamic SQL luôn dùng tham số, không nối chuỗi giá trị.
  • •Không có vòng lặp nào gọi database cho từng phần tử.
  • •Truy vấn được sắp theo tổng thời gian để tìm mục tiêu tối ưu.
  • •Tối ưu được kiểm chứng trên dữ liệu có kích thước như production.

Bài tập áp dụng​

Bài 1 — CONVERT_IMPLICIT áp lên cột chứ không lên tham số​

Tạo cột VARCHAR(20), truy vấn từ .NET bằng tham số mặc định (NVARCHAR), và tìm cảnh báo CONVERT_IMPLICIT trong plan. Khai rõ SqlDbType.VarChar và so sánh số lần đọc trang.

Tiêu chí hoàn thành: bạn nêu được vì sao SQL Server chuyển cột chứ không chuyển tham số, dù chuyển tham số rõ ràng rẻ hơn.

Gợi ý và lời giải — Bài 1

Gợi ý. Quy tắc chuyển kiểu của SQL Server có thứ bậc ưu tiên, và nó được áp dụng máy móc, không cân nhắc chi phí.

Lời giải — bản mặc định:

CREATE TABLE Customers (
    Id           INT IDENTITY PRIMARY KEY,
    CustomerCode VARCHAR(20) NOT NULL,          -- VARCHAR, không phải NVARCHAR
    Name         NVARCHAR(200) NOT NULL
);
CREATE INDEX IX_Customers_Code ON Customers(CustomerCode);
// string của C# ánh xạ mặc định sang NVARCHAR
var customer = await _db.Customers
    .FirstOrDefaultAsync(c => c.CustomerCode == code, ct);
|--Index Scan(OBJECT:([IX_Customers_Code]),
              WHERE:(CONVERT_IMPLICIT(nvarchar(20),[CustomerCode])=[@__code_0]))

Table 'Customers'. logical reads 2184

CONVERT_IMPLICIT áp lên [CustomerCode] — tức là lên cột. Và một hàm bọc quanh cột thì index vô dụng, đúng như bài 12.5.

Khai rõ kiểu:

modelBuilder.Entity<Customer>()
    .Property(c => c.CustomerCode)
    .HasColumnType("varchar(20)");

hoặc với ADO.NET:

cmd.Parameters.Add("@code", SqlDbType.VarChar, 20).Value = code;
|--Index Seek(OBJECT:([IX_Customers_Code]), SEEK:([CustomerCode]=[@code]))

Table 'Customers'. logical reads 3

Từ 2.184 xuống 3.

Vì sao chuyển cột chứ không chuyển tham số. SQL Server có một bảng thứ bậc ưu tiên kiểu dữ liệu cố định. Khi hai vế của phép so sánh khác kiểu, vế có kiểu ưu tiên thấp hơn được nâng lên kiểu của vế kia.

NVARCHAR có ưu tiên cao hơn VARCHAR. Nên trong CustomerCode = @code, vế CustomerCode (VARCHAR) bị nâng lên NVARCHAR — tức là cột bị chuyển.

Quy tắc này được áp dụng trước khi optimizer nghĩ tới chi phí. Nó là quy tắc về ngữ nghĩa, không phải về hiệu năng: mục tiêu là bảo đảm phép so sánh cho kết quả đúng trong mọi trường hợp. Chuyển NVARCHAR xuống VARCHAR có thể mất dữ liệu — ký tự Unicode không biểu diễn được trong bảng mã 8 bit sẽ bị biến dạng. Nên chiều chuyển luôn là chiều an toàn, kể cả khi nó đắt.

Vì sao đây là lỗi khó phát hiện nhất trong nhóm này:

  • Truy vấn trông hoàn toàn bình thường, không có hàm nào trong code.
  • Nó trả về đúng kết quả.
  • Trên dữ liệu nhỏ thì không ai thấy gì.
  • Nó nằm ở tầng ánh xạ kiểu, không nằm ở câu SQL bạn viết.

Cách rà soát toàn hệ thống:

SELECT TOP 50
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_reads,
       SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
                 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
                   ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE  CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%'
ORDER  BY qs.total_logical_reads DESC;

Mỗi dòng trả về là một truy vấn đang mất index vì ép kiểu ngầm.

Cách phòng tận gốc: dùng NVARCHAR cho mọi cột chuỗi, trừ khi có lý do rõ ràng để dùng VARCHAR. Dung lượng tăng gấp đôi cho dữ liệu ASCII, nhưng bạn loại bỏ hẳn một lớp lỗi — và với phần lớn hệ thống, đó là đánh đổi đúng. Nếu phải giữ VARCHAR vì lý do dung lượng hay tương thích, hãy khai kiểu tường minh trong cấu hình EF Core cho mọi cột như vậy.

Cùng lỗi này xảy ra với số:

// Cột là INT, tham số là BIGINT hoặc DECIMAL
_db.Orders.Where(o => o.CustomerId == longValue)    // ép kiểu ngầm trên cột

Bài 2 — Parameter sniffing: cùng truy vấn, hai plan, hai số phận​

Tạo bảng có một giá trị chiếm 90% và một giá trị chiếm 0,01%. Chạy stored procedure với giá trị hiếm trước, rồi với giá trị phổ biến, và đo. Xoá cache plan, đảo thứ tự và đo lại.

Tiêu chí hoàn thành: bạn nêu được vì sao parameter sniffing không phải một lỗi, mà là một tối ưu hoá đôi khi phản tác dụng.

Gợi ý và lời giải — Bài 2

Gợi ý. Biên dịch một truy vấn tốn thời gian. Hãy nghĩ xem SQL Server làm gì để không phải biên dịch lại mỗi lần chạy.

Lời giải — dữ liệu lệch:

INSERT INTO Leads (Status, Name, Value)
SELECT CASE WHEN g <= 900000 THEN N'Closed' ELSE N'New' END, N'...', g
FROM   generate_series(1, 1000000) g;      -- 90% Closed, 10% New

UPDATE TOP (100) Leads SET Status = N'Escalated';   -- 0,01%

CREATE INDEX IX_Leads_Status ON Leads(Status);
CREATE PROCEDURE GetLeadsByStatus @Status NVARCHAR(50)
AS
SELECT Id, Name, Value, CreatedAt FROM Leads WHERE Status = @Status;

Chạy giá trị hiếm trước:

DBCC FREEPROCCACHE;
EXEC GetLeadsByStatus N'Escalated';     -- 100 dòng
EXEC GetLeadsByStatus N'Closed';        -- 900.000 dòng
Escalated:  4 ms,      logical reads 312        <- plan Index Seek + Key Lookup
Closed:    18.400 ms,  logical reads 2.847.000  <- CÙNG plan, 900.000 lookup

Đảo thứ tự:

DBCC FREEPROCCACHE;
EXEC GetLeadsByStatus N'Closed';        -- 900.000 dòng
EXEC GetLeadsByStatus N'Escalated';     -- 100 dòng
Closed:     2.100 ms,  logical reads 8.900      <- plan Clustered Index Scan
Escalated:  1.980 ms,  logical reads 8.900      <- CÙNG plan, quét cả bảng cho 100 dòng

Cùng một stored procedure, cùng dữ liệu, thời gian chênh gần 9 lần — chỉ vì thứ tự chạy lần đầu.

Vì sao đây không phải lỗi. Biên dịch một truy vấn phức tạp tốn vài chục mili-giây, đôi khi hơn. Nếu biên dịch lại cho mỗi lần chạy, một hệ thống với hàng nghìn truy vấn mỗi giây sẽ dành phần lớn CPU cho việc biên dịch thay vì lấy dữ liệu.

Nên SQL Server làm điều hợp lý: lần đầu chạy, nó ngửi giá trị tham số thực tế, dựng plan tối ưu cho giá trị đó, rồi cache lại dùng cho mọi lần sau. Với phần lớn truy vấn — nơi phân bố dữ liệu tương đối đều — đây là tối ưu hoá rất tốt.

Nó chỉ phản tác dụng khi dữ liệu lệch mạnh và plan tối ưu cho giá trị này lại là plan tệ cho giá trị kia. Lúc đó, "lần chạy đầu tiên sau khi restart" trở thành thứ quyết định hiệu năng của cả ngày — và đó là lý do triệu chứng kinh điển là "sáng nay hệ thống chậm, khởi động lại thì hết".

Bốn cách xử lý, và khi nào dùng cái nào:

-- 1. OPTION (RECOMPILE) — plan mới mỗi lần. Tốt cho SP chạy ít, tham số lệch nhiều.
SELECT ... WHERE Status = @Status OPTION (RECOMPILE);

Đơn giản nhất và thường hiệu quả nhất. Cái giá là chi phí biên dịch mỗi lần chạy — không đáng kể nếu SP chạy vài trăm lần một ngày, nhưng đáng kể nếu nó chạy nghìn lần một giây.

-- 2. OPTIMIZE FOR UNKNOWN — dùng thống kê trung bình, không sniff
SELECT ... WHERE Status = @Status OPTION (OPTIMIZE FOR UNKNOWN);

Cho một plan "trung bình", không tối ưu cho ai nhưng cũng không thảm hoạ cho ai. Hợp lý khi bạn muốn hiệu năng ổn định hơn là tối ưu.

-- 3. OPTIMIZE FOR giá trị cụ thể — khi biết rõ trường hợp phổ biến
SELECT ... WHERE Status = @Status OPTION (OPTIMIZE FOR (@Status = N'Closed'));
-- 4. Tách thành các SP riêng cho trường hợp lệch nhiều
IF @Status = N'Escalated'
    EXEC GetLeadsByStatus_Rare @Status;
ELSE
    EXEC GetLeadsByStatus_Common @Status;

Xấu về mặt code nhưng cho kết quả tốt nhất, vì mỗi nhánh có plan riêng được cache riêng.

Cách phát hiện trong hệ thống đang chạy:

SELECT TOP 20
       qs.execution_count,
       qs.min_worker_time  / 1000 AS min_ms,
       qs.max_worker_time  / 1000 AS max_ms,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_ms,
       OBJECT_NAME(st.objectid, st.dbid) AS proc_name
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE  qs.execution_count > 100
ORDER  BY (qs.max_worker_time - qs.min_worker_time) DESC;

Khoảng cách lớn giữa min_ms và max_ms là dấu hiệu đặc trưng: cùng một truy vấn, lúc nhanh lúc chậm. Nếu chênh lệch tới hàng trăm lần, gần như chắc chắn là parameter sniffing.

Trên PostgreSQL vấn đề nhẹ hơn vì nó dùng generic plan sau năm lần chạy đầu và so sánh chi phí trước khi quyết định — nhưng plan_cache_mode vẫn chỉnh được nếu cần.

Bài 3 — Truy vấn catch-all và plan cho trường hợp tệ nhất​

Viết truy vấn catch-all 4 tham số, chạy với đúng một tham số và xem plan. Thêm OPTION (RECOMPILE) và so sánh.

Tiêu chí hoàn thành: bạn giải thích được vì sao optimizer phải dựng plan cho mọi tổ hợp tham số, chứ không phải cho tổ hợp bạn đang truyền.

Gợi ý và lời giải — Bài 3

Gợi ý. Plan được cache và dùng lại. Lần sau người ta có thể gọi cùng truy vấn với tổ hợp tham số hoàn toàn khác.

Lời giải — truy vấn catch-all:

CREATE PROCEDURE SearchLeads
    @Status     NVARCHAR(50)  = NULL,
    @OwnerId    INT           = NULL,
    @MinValue   DECIMAL(18,2) = NULL,
    @NameSearch NVARCHAR(200) = NULL
AS
SELECT Id, Name, Value, Status, OwnerId
FROM   Leads
WHERE  (@Status     IS NULL OR Status  = @Status)
  AND  (@OwnerId    IS NULL OR OwnerId = @OwnerId)
  AND  (@MinValue   IS NULL OR Value  >= @MinValue)
  AND  (@NameSearch IS NULL OR Name LIKE N'%' + @NameSearch + N'%');
EXEC SearchLeads @OwnerId = 42;         -- chỉ MỘT tham số
|--Clustered Index Scan(OBJECT:([PK_Leads]))
Table 'Leads'. logical reads 8921

Quét toàn bảng, dù có index trên OwnerId và chỉ 300 dòng khớp.

Vì sao optimizer phải dựng plan cho mọi tổ hợp. Plan được cache theo câu lệnh, không phải theo giá trị tham số. Lần sau, cùng thủ tục này có thể được gọi với:

EXEC SearchLeads @NameSearch = N'Phát';
EXEC SearchLeads @Status = N'New', @MinValue = 1000000;
EXEC SearchLeads;                        -- không tham số nào

Plan được cache phải đúng với tất cả những lần gọi đó. Optimizer không thể chọn Index Seek trên OwnerId, vì lần gọi tiếp theo có thể không truyền @OwnerId — và lúc đó seek sẽ cho kết quả sai.

Phương án duy nhất luôn đúng là quét bảng rồi lọc từng dòng. Nên optimizer chọn nó, và bạn nhận plan tệ nhất trong mọi tình huống.

Thêm OPTION (RECOMPILE):

SELECT ...
WHERE  (@Status IS NULL OR Status = @Status)
  AND  ...
OPTION (RECOMPILE);
|--Index Seek(OBJECT:([IX_Leads_OwnerId]), SEEK:([OwnerId]=(42)))
Table 'Leads'. logical reads 14

Từ 8.921 xuống 14.

Vì sao nó hoạt động. RECOMPILE bảo SQL Server đừng cache plan này. Không cache nghĩa là không cần plan đúng cho mọi tổ hợp — nó biên dịch riêng cho lần gọi này, biết rằng @Status IS NULL là TRUE và @OwnerId = 42, nên nó rút gọn được biểu thức và chọn index phù hợp.

Đây là đánh đổi rất rõ: chi phí biên dịch mỗi lần chạy đổi lấy plan tối ưu cho đúng lần chạy đó. Với màn hình tìm kiếm mà người dùng bấm vài trăm lần một ngày, chi phí biên dịch (vài mili-giây) hoàn toàn không đáng kể so với việc tiết kiệm hàng giây mỗi lần.

Khi nào RECOMPILE không phù hợp: truy vấn chạy hàng nghìn lần mỗi giây. Lúc đó chi phí biên dịch cộng dồn trở thành gánh nặng CPU thật.

Cách thứ hai — dynamic SQL có tham số:

DECLARE @sql NVARCHAR(MAX) = N'SELECT Id, Name, Value, Status, OwnerId FROM Leads WHERE 1=1';

IF @Status     IS NOT NULL SET @sql += N' AND Status  = @Status';
IF @OwnerId    IS NOT NULL SET @sql += N' AND OwnerId = @OwnerId';
IF @MinValue   IS NOT NULL SET @sql += N' AND Value  >= @MinValue';
IF @NameSearch IS NOT NULL SET @sql += N' AND Name LIKE N''%'' + @NameSearch + N''%''';

EXEC sp_executesql @sql,
     N'@Status NVARCHAR(50), @OwnerId INT, @MinValue DECIMAL(18,2), @NameSearch NVARCHAR(200)',
     @Status, @OwnerId, @MinValue, @NameSearch;

Mỗi tổ hợp tham số sinh ra một câu lệnh khác nhau, nên mỗi tổ hợp có plan riêng được cache riêng — không cần RECOMPILE, và plan vẫn tối ưu.

Bắt buộc dùng sp_executesql với tham số, không nối chuỗi giá trị. Nối chuỗi là mở cửa cho SQL injection, và nó cũng phá luôn việc cache plan vì mỗi giá trị sinh một câu lệnh khác.

EF Core sinh ra dynamic SQL đúng kiểu này một cách tự nhiên:

var query = _db.Leads.AsQueryable();

if (status  is not null) query = query.Where(l => l.Status == status);
if (ownerId is not null) query = query.Where(l => l.OwnerId == ownerId);
if (minValue is not null) query = query.Where(l => l.Value >= minValue);

var results = await query.ToListAsync(ct);

Mỗi tổ hợp điều kiện sinh một câu SQL riêng với đúng những mệnh đề cần — đây là một trong những chỗ ORM cho kết quả tốt hơn stored procedure viết tay, miễn là bạn không gom hết vào một truy vấn catch-all.

Tự kiểm tra​

Câu hỏi thường gặp

Nên nhìn vào đâu đầu tiên trong execution plan?

Chênh lệch giữa số dòng ước lượng và số dòng thực tế. Sai lệch lớn nghĩa là optimizer đang làm việc với thông tin sai, nên mọi quyết định sau đó đều sai theo, và nguyên nhân thường là statistics cũ.

Vì sao đo bằng số lần đọc trang tốt hơn đo bằng thời gian?

Vì thời gian phụ thuộc vào tải máy, cache và các truy vấn khác đang chạy, còn số lần đọc trang ổn định và so sánh được giữa các lần. Một truy vấn đọc nửa triệu trang để trả về 20 dòng là sai ở đâu đó bất kể nó mất bao lâu.

Implicit conversion gây hại thế nào?

Khi kiểu tham số không khớp kiểu cột, database phải chuyển đổi chính cột đó cho mọi dòng, và index không dùng được. Trong .NET, SqlParameter mặc định gửi chuỗi dưới dạng NVARCHAR nên cột VARCHAR sẽ bị chuyển đổi. Đây là một trong những nguyên nhân khó tìm nhất vì truy vấn trông hoàn toàn đúng.

Parameter sniffing là gì?

SQL Server cache execution plan theo tham số của lần chạy đầu tiên, nên nếu lần đầu chạy với giá trị hiếm thì plan tối ưu cho vài dòng sẽ được dùng lại cho giá trị phổ biến có hàng trăm nghìn dòng. Triệu chứng đặc trưng là cùng một stored procedure lúc nhanh lúc chậm, và khởi động lại thì hết.

Vì sao catch-all query lại tệ?

Vì optimizer phải tạo một plan duy nhất hoạt động cho mọi tổ hợp tham số, nên nó thường chọn quét toàn bảng để an toàn cho mọi trường hợp, và tệ cho hầu hết. Cách sửa đơn giản nhất là thêm OPTION RECOMPILE để optimizer biết giá trị thật và loại bỏ các nhánh không liên quan.

Nên sắp xếp truy vấn theo tiêu chí nào để tìm mục tiêu tối ưu?

Theo tổng thời gian, không theo thời gian trung bình. Một truy vấn 5 mili giây chạy một triệu lần tốn nhiều hơn một truy vấn 2 giây chạy 10 lần, và nó thường cũng dễ sửa hơn.

Kết luận​

Ba điều đáng nhớ nhất:

  1. Nhìn chênh lệch ước lượng và thực tế trước. Statistics cũ làm mọi thứ sau đó sai.
  2. Catch-all query cần OPTION (RECOMPILE) hoặc sinh SQL động có tham số.
  3. Sắp theo tổng thời gian. Truy vấn nhanh chạy rất nhiều lần mới là gánh nặng thật.

Tham khảo​

Điều hướng​

Bài liên quan​