12.11 - Ví dụ thực tế nhanh

Tóm tắt: Một tình huống gây bối rối: index có tồn tại, cột lọc đúng là cột đã đánh index, nhưng execution plan vẫn cho thấy table scan. Nguyên nhân nằm ở một nguyên tắc duy nhất mà nhiều người không biết: index chỉ dùng được khi cột được so sánh trực tiếp, ở dạng nguyên bản của nó. Bọc cột trong một hàm, ép kiểu ngầm, hay đặt wildcard ở đầu chuỗi — tất cả đều khiến database không còn cách nào dùng cấu trúc đã sắp xếp của index. Bài này liệt kê năm cách vô hiệu hoá index vô tình, kèm cách viết lại cho từng cái, và cách kiểm chứng bằng logical reads.

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

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

Nhận ra năm cách vô hiệu hoá index vô tình.
Viết lại truy vấn để index dùng được.
Kiểm chứng bằng logical reads thay vì cảm giác.
Hiểu vì sao ép kiểu ngầm nguy hiểm nhất.

Nội dung bài học

12.11.1 — Nguyên tắc chung

Index là một cấu trúc ĐÃ SẮP XẾP theo giá trị cột.
Database dùng được nó khi biết phải TÌM Ở ĐÂU.

Nếu cột bị BỌC trong một hàm, database KHÔNG biết kết quả hàm
sẽ rơi vào vị trí nào trong index -> phải tính hàm cho MỌI HÀNG
-> quét toàn bảng.

Nguyên tắc: cột phải đứng một mình ở một vế của phép so sánh, ở dạng nguyên bản.

12.11.2 — Cách 1: bọc cột trong hàm

sql

-- KHÔNG dùng index trên CreatedAt
SELECT * FROM Leads WHERE YEAR(CreatedAt) = 2026;
SELECT * FROM Leads WHERE CAST(CreatedAt AS DATE) = '2026-09-24';
SELECT * FROM Leads WHERE UPPER(Email) = '[email protected]';

-- DÙNG được index
SELECT * FROM Leads WHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01';
SELECT * FROM Leads WHERE CreatedAt >= '2026-09-24' AND CreatedAt < '2026-09-25';
SELECT * FROM Leads WHERE Email = '[email protected]';   -- collation thường không phân biệt hoa thường

Mẫu chung để viết lại: đổi từ "hàm của cột bằng giá trị" sang khoảng giá trị trên chính cột đó.

Với ngày tháng, dùng >= đầu khoảng AND < đầu khoảng tiếp theo — tránh BETWEEN với thời gian vì nó bao gồm cả mốc cuối, dễ sai khi cột có phần giờ phút.

12.11.3 — Cách 2: ép kiểu ngầm (nguy hiểm nhất)

sql

-- Cột CustomerCode là VARCHAR(20), tham số là NVARCHAR
SELECT * FROM Customers WHERE CustomerCode = N'KH001';

Đây là cách khó phát hiện nhất vì truy vấn trông hoàn toàn bình thường. Nhưng execution plan cho thấy:

|--Index Scan(OBJECT:([IX_Customers_Code]),
              WHERE:(CONVERT_IMPLICIT(nvarchar(20),[CustomerCode])=[@1]))

CONVERT_IMPLICIT áp lên cột, không phải lên tham số — nên nó chính là "bọc cột trong hàm" ở dạng ẩn.

Lý do SQL Server chuyển cột thay vì chuyển tham số: NVARCHAR có độ ưu tiên kiểu cao hơn VARCHAR, nên theo quy tắc chuyển kiểu, vế còn lại phải được nâng lên.

Trong .NET, đây là nguyên nhân rất phổ biến vì string của C# ánh xạ mặc định sang NVARCHAR:

csharp

// EF Core sinh tham số NVARCHAR — nếu cột là VARCHAR thì ép kiểu ngầm
var customer = await _db.Customers.FirstOrDefaultAsync(c => c.CustomerCode == code, ct);

csharp

// Sửa: khai báo đúng kiểu cột
b.Property(c => c.CustomerCode).HasColumnType("varchar(20)");

Cách phát hiện: tìm CONVERT_IMPLICIT trong execution plan. Mỗi lần thấy nó áp lên một cột là một index bị vô hiệu hoá.

12.11.4 — Cách 3: wildcard ở đầu chuỗi

sql

-- KHÔNG dùng index — không biết bắt đầu tìm từ đâu
SELECT * FROM Leads WHERE Name LIKE '%nguyen%';

-- DÙNG được index — biết tiền tố
SELECT * FROM Leads WHERE Name LIKE 'nguyen%';

Index sắp xếp theo thứ tự chuỗi, nên biết tiền tố là biết bắt đầu tìm ở đâu. Không biết phần đầu thì không có cách nào thu hẹp.

Khi nghiệp vụ bắt buộc tìm giữa chuỗi, có ba lựa chọn: full-text index của database, một search engine riêng, hoặc chấp nhận quét nếu bảng nhỏ (bài 19.9).

12.11.5 — Cách 4: OR giữa các cột khác nhau

sql

-- Khó dùng index — mỗi nhánh cần index khác nhau
SELECT * FROM Leads WHERE Email = '[email protected]' OR Phone = '0901234567';

-- Viết lại bằng UNION — mỗi nhánh dùng index riêng
SELECT * FROM Leads WHERE Email = '[email protected]'
UNION
SELECT * FROM Leads WHERE Phone = '0901234567';

Với OR, database thường phải quét vì không có index nào phủ cả hai điều kiện. UNION cho phép mỗi nhánh dùng index của riêng nó.

Dùng UNION (loại trùng) hay UNION ALL (giữ trùng) tuỳ nghiệp vụ — UNION ALL nhanh hơn nhưng có thể trả bản ghi hai lần nếu nó khớp cả hai điều kiện.

Lưu ý: OR trên cùng một cột thì không sao, database xử lý được:

sql

-- Ổn — cùng một cột
SELECT * FROM Leads WHERE Status = 'New' OR Status = 'Contacted';
SELECT * FROM Leads WHERE Status IN ('New', 'Contacted');      -- tương đương

12.11.6 — Cách 5: thứ tự cột sai trong index ghép

sql

CREATE INDEX IX_Leads_Tenant_Status ON Leads (TenantId, Status);

sql

-- DÙNG được index — có TenantId (cột đầu)
SELECT * FROM Leads WHERE TenantId = 'acme';
SELECT * FROM Leads WHERE TenantId = 'acme' AND Status = 'New';

-- KHÔNG dùng được — thiếu cột đầu
SELECT * FROM Leads WHERE Status = 'New';

Index ghép giống danh bạ sắp theo họ rồi tên: tìm được "mọi người họ Nguyễn" và "Nguyễn Văn An", nhưng không tìm nhanh được "mọi người tên An" vì danh bạ không sắp theo tên.

Nguyên tắc leftmost prefix: index (A, B, C) dùng được cho truy vấn lọc theo A, (A, B), hoặc (A, B, C) — nhưng không dùng được cho truy vấn chỉ lọc theo B hay C.

Hệ quả thực tế: thứ tự cột trong index là quyết định thiết kế, không phải chuyện tuỳ ý. Đặt cột được lọc bằng = lên trước, cột dùng để sắp xếp sau (bài 12.4).

12.11.7 — Kiểm chứng bằng logical reads

Đừng đoán — đo.

sql

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM Leads WHERE YEAR(CreatedAt) = 2026;
-- Table 'Leads'. Scan count 1, logical reads 48291     <- QUET

SELECT * FROM Leads WHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01';
-- Table 'Leads'. Scan count 1, logical reads 412       <- SEEK

logical reads là số trang dữ liệu database phải đọc — chỉ số khách quan nhất, vì nó không phụ thuộc vào cache hay tải hiện tại của máy. Hai lần chạy cùng truy vấn luôn cho cùng con số, khác với thời gian thực thi.

Với EF Core, xem SQL sinh ra trước:

csharp

var sql = _db.Leads.Where(l => l.CreatedAt.Year == 2026).ToQueryString();
// SELECT ... WHERE DATEPART(year, [l].[CreatedAt]) = 2026     <- hàm trên cột!

csharp

// Viết lại
var dau = new DateTime(2026, 1, 1);
var cuoi = new DateTime(2027, 1, 1);
var leads = await _db.Leads.Where(l => l.CreatedAt >= dau && l.CreatedAt < cuoi).ToListAsync(ct);

ToQueryString() là công cụ nhanh nhất để thấy EF Core đã dịch LINQ của bạn thành gì — nhiều biểu thức trông vô hại lại sinh ra hàm trên cột.

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

Danh sách rà soát index bị vô hiệu hoá

Không bọc cột trong hàm ở mệnh đề WHERE.
Điều kiện ngày tháng dùng khoảng giá trị, không dùng YEAR hay CAST.
Kiểu cột trong EF Core khớp với kiểu thật trong database.
Đã tìm CONVERT_IMPLICIT trong execution plan của truy vấn chậm.
Không dùng LIKE với wildcard ở đầu trên bảng lớn.
OR giữa các cột khác nhau được viết lại bằng UNION.
Truy vấn dùng được cột đầu tiên của index ghép.
Đo bằng logical reads, không đoán theo cảm giác.
Đã xem SQL sinh ra bằng ToQueryString trước khi tối ưu.

Bài tập áp dụng

Bài 1 — Đo cả năm cách

Với mỗi cách ở trên, chạy bản sai và bản đúng rồi so sánh logical reads.

Tiêu chí hoàn thành: bạn xếp được năm cách theo mức độ khó phát hiện, và giải thích được tiêu chí xếp hạng.

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

Gợi ý. "Khó phát hiện" không giống "tốn kém". Hãy hỏi: nhìn vào câu lệnh, có thấy gì bất thường không?

Lời giải — dữ liệu thử và index:

sql

CREATE INDEX IX_Leads_CreatedAt ON Leads(CreatedAt);
CREATE INDEX IX_Leads_Name      ON Leads(Name);
CREATE INDEX IX_Leads_Email     ON Leads(Email);
CREATE INDEX IX_Leads_Phone     ON Leads(Phone);
CREATE INDEX IX_Leads_Tenant_Status ON Leads(TenantId, Status);
SET STATISTICS IO ON;

Kết quả điển hình trên bảng một triệu dòng:

Cách	Bản sai	`logical reads`	Bản đúng	`logical reads`
1. Hàm trên cột	`YEAR(CreatedAt) = 2026`	48.291	`>= ... AND < ...`	412
2. Ép kiểu ngầm	`CustomerCode = N'KH001'`	3.180	cột khai `varchar(20)`	3
3. Wildcard đầu chuỗi	`Name LIKE '%nguyen%'`	48.291	`Name LIKE 'nguyen%'`	27
4. `OR` khác cột	`Email = .. OR Phone = ..`	48.291	`UNION` hai nhánh	8
5. Thiếu cột đầu index	`WHERE Status = 'New'`	48.291	thêm `TenantId = ..`	96

Bốn trên năm cách cho đúng cùng một con số 48.291 — đó chính là số trang của cả bảng. Khi thấy logical reads bằng nhau ở nhiều truy vấn khác nhau, gần như chắc chắn tất cả đều đang quét toàn bảng.

Cách 2 đáng chú ý: bản sai cho 3.180 chứ không phải 48.291, vì SQL Server vẫn dùng được index — nhưng ở dạng scan thay vì seek. Nó đọc hết index (nhỏ hơn bảng) rồi mới lọc. Nhanh hơn quét bảng, nhưng vẫn gấp 1.000 lần so với seek.

Xếp hạng theo mức độ khó phát hiện:

text

Khó nhất
   1. Ép kiểu ngầm         — câu lệnh trông HOÀN TOÀN bình thường
   2. Thiếu cột đầu index  — phải nhớ định nghĩa index mới thấy sai
   3. Hàm trên cột         — nhìn thấy hàm, nhưng quen mắt nên bỏ qua
   4. OR khác cột          — nhìn thấy OR, ít người biết đó là vấn đề
   5. Wildcard đầu chuỗi   — '%...' nhìn là biết
Dễ nhất

Tiêu chí xếp hạng: bao nhiêu thông tin ngoài câu lệnh mà bạn cần có để nhận ra?

Ép kiểu ngầm cần biết kiểu thật của cột trong database — thông tin không có trong câu lệnh, không có trong code C#, chỉ có trong schema. Và mọi thứ vẫn chạy đúng, chỉ chậm. Đây là lý do nó đứng đầu.
Thiếu cột đầu index cần biết định nghĩa index — cũng nằm ngoài câu lệnh.
Hàm trên cột nhìn là thấy, nhưng WHERE YEAR(CreatedAt) = 2026 đọc rất tự nhiên nên mắt trượt qua.
OR và wildcard đều hiện ngay trong câu lệnh.

Hệ quả cho quy trình làm việc. Ba cách cuối bắt được bằng đọc code — đưa vào checklist review là đủ. Hai cách đầu không bắt được bằng đọc code; chúng chỉ lộ ra trong execution plan hoặc trong thống kê truy vấn chậm:

sql

SELECT TOP 20
       qs.total_logical_reads / qs.execution_count AS avg_reads,
       qs.execution_count,
       SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                 ((CASE qs.statement_end_offset WHEN -1
                        THEN DATALENGTH(st.text)
                        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS stmt
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_reads DESC;

Sắp theo avg_reads chứ không theo tổng thời gian: một truy vấn đọc 48.000 trang mà chạy nhanh chỉ vì dữ liệu đang nằm trong bộ nhớ sẽ tụt hạng nếu sắp theo thời gian — cho đến ngày dữ liệu không còn vừa bộ nhớ nữa.

Bài 2 — Tìm ép kiểu ngầm

Tạo một cột VARCHAR, truy vấn bằng tham số NVARCHAR, và tìm CONVERT_IMPLICIT trong plan.

Tiêu chí hoàn thành: bạn giải thích được vì sao SQL Server chuyển cột chứ không chuyển tham số, và nêu được trường hợp ép kiểu ngầm vô hại.

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

Gợi ý. Tra "data type precedence" — quy tắc này có thứ tự cố định, không do optimizer chọn.

Lời giải:

sql

CREATE TABLE Customers (
    Id           INT IDENTITY PRIMARY KEY,
    CustomerCode VARCHAR(20) NOT NULL,
    Name         NVARCHAR(200) NOT NULL
);
CREATE INDEX IX_Customers_Code ON Customers(CustomerCode);

sql

SET SHOWPLAN_XML ON;
GO
DECLARE @code NVARCHAR(20) = N'KH001';
SELECT Id, Name FROM Customers WHERE CustomerCode = @code;
GO
SET SHOWPLAN_XML OFF;

text

|--Index Scan(OBJECT:([IX_Customers_Code]),
              WHERE:(CONVERT_IMPLICIT(nvarchar(20),[CustomerCode],0)=[@code]))

Table 'Customers'. logical reads 3180

Đổi kiểu tham số:

sql

DECLARE @code VARCHAR(20) = 'KH001';
SELECT Id, Name FROM Customers WHERE CustomerCode = @code;

text

|--Index Seek(OBJECT:([IX_Customers_Code]), SEEK:([CustomerCode]=[@code]))

Table 'Customers'. logical reads 3

Vì sao SQL Server chuyển cột chứ không chuyển tham số. Đây không phải lựa chọn của optimizer — nó là quy tắc ngôn ngữ, cố định trong chuẩn SQL và trong tài liệu SQL Server. Khi hai vế của phép so sánh khác kiểu, vế có độ ưu tiên thấp hơn được nâng lên kiểu của vế kia.

Thứ tự ưu tiên, từ cao xuống thấp (rút gọn):

text

datetime2 > datetimeoffset > datetime > date > float > decimal > int
         > NVARCHAR > NCHAR > VARCHAR > CHAR

NVARCHAR cao hơn VARCHAR, nên cột VARCHAR phải nâng lên NVARCHAR. Chiều ngược lại không được phép, vì nó có thể mất dữ liệu: NVARCHAR biểu diễn được ký tự mà VARCHAR với một code page cụ thể không có. Nếu SQL Server hạ tham số xuống VARCHAR, một tham số chứa ký tự tiếng Nhật sẽ bị biến thành ? và câu lệnh trả sai kết quả trong im lặng.

Nói cách khác: quy tắc này hy sinh hiệu năng để bảo toàn tính đúng đắn. Và đó là đánh đổi hợp lý — chỉ là bạn cần biết nó tồn tại.

Vì sao đây là vấn đề đặc biệt của .NET. string của C# ánh xạ mặc định sang NVARCHAR. Nếu database được tạo từ một hệ thống cũ, từ script DBA viết tay, hay từ một migration đời đầu, các cột chuỗi rất có thể là VARCHAR. Mọi truy vấn EF Core trên cột đó đều dính ép kiểu ngầm.

csharp

// Sửa ở tầng cấu hình — một dòng, áp cho mọi truy vấn trên cột này
b.Property(c => c.CustomerCode).HasColumnType("varchar(20)");

csharp

// Sửa ở tầng ADO.NET — phải nhớ ở MỌI chỗ gọi, nên dễ sót
cmd.Parameters.Add("@code", SqlDbType.VarChar, 20).Value = code;

Rà cả database một lượt:

sql

SELECT t.name AS table_name, c.name AS column_name, ty.name AS type_name
FROM   sys.columns c
JOIN   sys.tables  t  ON t.object_id = c.object_id
JOIN   sys.types   ty ON ty.user_type_id = c.user_type_id
WHERE  ty.name IN ('varchar', 'char')
ORDER  BY t.name, c.name;

Mỗi dòng trả về là một cột mà code C# sẽ so sánh bằng tham số NVARCHAR.

Khi nào ép kiểu ngầm vô hại — và đây là nửa quan trọng còn lại của câu hỏi. Nó vô hại khi việc chuyển đổi áp lên tham số hoặc hằng số, không áp lên cột:

sql

-- Cột INT so với hằng chuỗi: '42' được chuyển sang INT — MỘT LẦN, trên hằng số
SELECT * FROM Leads WHERE Id = '42';
-- |--Index Seek(SEEK:([Id]=(42)))          <- vẫn seek, không sao

Ở đây INT có độ ưu tiên cao hơn VARCHAR, nên vế được nâng lại chính là hằng số. Cột giữ nguyên dạng nguyên bản, index dùng được bình thường.

Quy tắc rút gọn để đọc plan:

text

CONVERT_IMPLICIT(..., [TênCột], ...)     -> index HỎNG, phải sửa
CONVERT_IMPLICIT(..., [@thamso], ...)    -> vô hại, bỏ qua

Vị trí của CONVERT_IMPLICIT mới là thứ quyết định, không phải sự có mặt của nó. Đây cũng là lý do "tìm CONVERT_IMPLICIT rồi sửa hết" là lời khuyên sai — một nửa số lần bạn tìm thấy nó, chẳng có gì để sửa cả.

Và cẩn thận với chiều ngược của cột NVARCHAR so với hằng VARCHAR: lúc đó hằng số được nâng lên, cột giữ nguyên — cũng vô hại. Trực giác "cứ khác kiểu là hỏng" không đúng; chỉ khi cột nằm ở phía bị chuyển thì index mới mất tác dụng.

Bài 3 — Kiểm tra EF Core của chính dự án

Chạy ToQueryString() trên 5 truy vấn phức tạp nhất trong dự án và tìm hàm áp lên cột.

Tiêu chí hoàn thành: bạn tìm được ít nhất một biểu thức LINQ trông vô hại nhưng sinh ra hàm trên cột, và biết cách chặn loại lỗi này trước khi nó lên production.

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

Gợi ý. Chú ý các biểu thức làm việc với ngày tháng và với chữ hoa chữ thường.

Lời giải — bảy mẫu LINQ hay sinh hàm trên cột:

csharp

// 1. Truy cập thành phần của ngày
.Where(l => l.CreatedAt.Year == 2026)
// WHERE DATEPART(year, [l].[CreatedAt]) = 2026
.Where(l => l.CreatedAt >= new DateTime(2026,1,1) && l.CreatedAt < new DateTime(2027,1,1))

// 2. Bỏ phần giờ
.Where(l => l.CreatedAt.Date == today)
// WHERE CONVERT(date, [l].[CreatedAt]) = @today
.Where(l => l.CreatedAt >= today && l.CreatedAt < today.AddDays(1))

// 3. Chuẩn hoá hoa thường
.Where(l => l.Email.ToLower() == email.ToLower())
// WHERE LOWER([l].[Email]) = @email
.Where(l => l.Email == email)          // collation mặc định đã không phân biệt hoa thường

// 4. Contains sinh wildcard hai đầu
.Where(l => l.Name.Contains(keyword))
// WHERE [l].[Name] LIKE '%' + @keyword + '%'
.Where(l => l.Name.StartsWith(keyword))
// WHERE [l].[Name] LIKE @keyword + '%'     <- seek được

// 5. Số ngày chênh lệch
.Where(l => (DateTime.UtcNow - l.CreatedAt).TotalDays > 30)
// WHERE DATEDIFF(day, [l].[CreatedAt], GETUTCDATE()) > 30
.Where(l => l.CreatedAt < DateTime.UtcNow.AddDays(-30))

// 6. Ghép chuỗi để so sánh
.Where(l => l.FirstName + " " + l.LastName == fullName)
// WHERE [l].[FirstName] + N' ' + [l].[LastName] = @fullName
// -> tách thành hai điều kiện riêng, hoặc thêm computed column PERSISTED có index

// 7. Ép kiểu trong so sánh
.Where(l => l.Code.ToString() == input)
// WHERE CONVERT(nvarchar(max), [l].[Code]) = @input
// -> parse ở phía C# rồi so sánh đúng kiểu

Mẫu 3 đáng nói thêm: ToLower() ở đây không chỉ vô ích mà còn có hại. Collation mặc định của SQL Server (*_CI_*) vốn đã không phân biệt hoa thường, nên ToLower() không thêm tính đúng đắn nào — nó chỉ làm hỏng index. Nếu collation của bạn có phân biệt hoa thường và bạn thật sự cần so sánh không phân biệt, cách đúng là đổi collation của cột hoặc thêm một computed column đã chuẩn hoá, không phải gọi hàm trong WHERE.

Mẫu 6 và 7 hay bị bỏ sót vì chúng không "trông giống" gọi hàm. Nhưng phép + trên chuỗi và ToString() đều trở thành biểu thức áp lên cột trong SQL, với đúng hậu quả như YEAR().

Cách xem SQL sinh ra:

csharp

var sql = _db.Leads.Where(l => l.CreatedAt.Year == 2026).ToQueryString();
_logger.LogInformation("SQL: {Sql}", sql);

Hoặc bật log toàn bộ ở môi trường dev:

csharp

options.UseSqlServer(conn)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging();   // chỉ ở dev — nó in ra cả giá trị tham số

Chặn trước khi lên production — ba lớp, từ rẻ tới đắt:

1. Test khẳng định trên SQL sinh ra. Rẻ nhất, chạy trong vài mili giây, không cần database:

csharp

[Theory]
[InlineData("DATEPART")]
[InlineData("CONVERT(date")]
[InlineData("LOWER(")]
[InlineData("UPPER(")]
[InlineData("DATEDIFF")]
public void Truy_van_danh_sach_lead_khong_sinh_ham_tren_cot(string ham)
{
    using var db = TaoInMemoryContext();

    var sql = db.Leads
        .Where(l => l.TenantId == "acme" && l.Status == "New")
        .OrderByDescending(l => l.CreatedAt)
        .Take(50)
        .ToQueryString();

    sql.Should().NotContain(ham);
}

2. Ngưỡng logical reads trong test tích hợp. Cần database thật, nhưng bắt được cả những trường hợp mà chuỗi SQL trông ổn:

csharp

[Fact]
public async Task Danh_sach_lead_khong_duoc_quet_toan_bang()
{
    var reads = await DoLogicalReadsAsync(() => _service.LayDanhSachAsync("acme", ct));
    reads.Should().BeLessThan(500);    // bảng thử có ~48.000 trang
}

3. Cảnh báo trên production. Lớp cuối, bắt những gì hai lớp trên bỏ lọt — truy vấn mới, dữ liệu tăng, plan đổi:

sql

-- Chạy định kỳ, cảnh báo khi một truy vấn vượt ngưỡng
SELECT TOP 10 total_logical_reads / execution_count AS avg_reads, execution_count
FROM   sys.dm_exec_query_stats
WHERE  total_logical_reads / execution_count > 10000
ORDER  BY avg_reads DESC;

Lớp 1 là nơi đáng đầu tư nhất. Nó chạy trong CI, không cần hạ tầng gì, và bắt đúng loại lỗi khó thấy nhất khi review — vì nó kiểm tra kết quả dịch của LINQ chứ không kiểm tra LINQ. Một người viết .Where(l => l.CreatedAt.Date == today) trong pull request sẽ thấy test đỏ ngay, thay vì thấy một sự cố sáu tháng sau.

Một cảnh báo về lớp 1: ToQueryString() sinh SQL theo provider đang dùng. Nếu test dùng SQLite in-memory còn production dùng SQL Server, chuỗi SQL sẽ khác nhau và phép khẳng định có thể sai cả hai chiều. Dùng UseSqlServer với một chuỗi kết nối giả — ToQueryString() không mở kết nối, nên nó chạy được mà không cần database nào tồn tại.

Tự kiểm tra

Vì sao bọc cột trong hàm làm mất index?

Vì index là cấu trúc đã sắp xếp theo giá trị cột. Khi cột bị bọc trong hàm, database không biết kết quả hàm sẽ rơi vào vị trí nào trong index, nên phải tính hàm cho mọi hàng và quét toàn bảng.

Vì sao ép kiểu ngầm nguy hiểm nhất?

Vì truy vấn trông hoàn toàn bình thường, không có hàm nào nhìn thấy được. Nhưng SQL Server thêm CONVERT_IMPLICIT lên cột, và đó chính là bọc cột trong hàm ở dạng ẩn.

Vì sao SQL Server chuyển cột thay vì chuyển tham số?

Vì NVARCHAR có độ ưu tiên kiểu cao hơn VARCHAR, nên theo quy tắc chuyển kiểu, vế còn lại phải được nâng lên. Trong .NET điều này rất phổ biến vì string của C# ánh xạ mặc định sang NVARCHAR.

Vì sao LIKE với wildcard ở đầu không dùng được index?

Vì index sắp xếp theo thứ tự chuỗi, nên biết tiền tố là biết bắt đầu tìm ở đâu. Không biết phần đầu thì không có cách nào thu hẹp phạm vi tìm kiếm.

Nguyên tắc leftmost prefix của index ghép là gì?

Index gồm các cột A, B, C dùng được cho truy vấn lọc theo A, hoặc A và B, hoặc cả ba. Nó không dùng được cho truy vấn chỉ lọc theo B hay C, giống danh bạ sắp theo họ rồi tên thì không tìm nhanh được theo tên.

Vì sao logical reads đáng tin hơn thời gian thực thi?

Vì nó là số trang dữ liệu database phải đọc, không phụ thuộc vào cache hay tải hiện tại của máy. Hai lần chạy cùng truy vấn luôn cho cùng con số.

Kết luận

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

Cột phải đứng một mình ở một vế của phép so sánh để index dùng được.
Ép kiểu ngầm là cách vô hiệu hoá index khó thấy nhất — tìm CONVERT_IMPLICIT.
Đo bằng logical reads, và xem SQL sinh ra bằng ToQueryString().

Tham khảo

Điều hướng

Bài trước: 12.10 — Mini case study
Bài tiếp theo: 12.12 — Review and Assessment
Về module: Trang mục lục

Module 0 - Chuẩn bị môi trường

Module 1 - Programming Logic

Module 2 - Computer Science Basics

Module 3 - Git + Developer Workflow

Module 4 - C# Core

Module 5 - Advanced C#

Module 6 - Async Programming

Module 7 - Dependency Injection

Module 8 - ASP.NET Core Fundamentals

Module 9 - Web API Professional

Module 10 - Authentication + Authorization

Module 11 - SignalR

Module 12 - SQL Deep Dive

Module 13 - Entity Framework Core

Module 14 - Caching + Background Jobs

Module 15 - Docker + Deployment

Module 16 - Clean Architecture

Module 17 - Distributed Systems

Module 18 - Microservices

Module 19 - Performance Engineering

12.11 - Ví dụ thực tế nhanh

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

Nội dung bài học

12.11.1 — Nguyên tắc chung

12.11.2 — Cách 1: bọc cột trong hàm

12.11.3 — Cách 2: ép kiểu ngầm (nguy hiểm nhất)

12.11.4 — Cách 3: wildcard ở đầu chuỗi

12.11.5 — Cách 4: OR giữa các cột khác nhau

12.11.6 — Cách 5: thứ tự cột sai trong index ghép

12.11.7 — Kiểm chứng bằng logical reads

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

Bài tập áp dụng

Bài 1 — Đo cả năm cách

Bài 2 — Tìm ép kiểu ngầm

Bài 3 — Kiểm tra EF Core của chính dự án

Tự kiểm tra

Kết luận

Tham khảo

Điều hướng

12.11 - Ví dụ thực tế nhanh ​

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

Nội dung bài học ​

12.11.1 — Nguyên tắc chung ​

12.11.2 — Cách 1: bọc cột trong hàm ​

12.11.3 — Cách 2: ép kiểu ngầm (nguy hiểm nhất) ​

12.11.4 — Cách 3: wildcard ở đầu chuỗi ​

12.11.5 — Cách 4: OR giữa các cột khác nhau ​

12.11.6 — Cách 5: thứ tự cột sai trong index ghép ​

12.11.7 — Kiểm chứng bằng logical reads ​

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

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

Bài 1 — Đo cả năm cách ​

Bài 2 — Tìm ép kiểu ngầm ​

Bài 3 — Kiểm tra EF Core của chính dự án ​

Tự kiểm tra ​

Kết luận ​

Tham khảo ​

Điều hướng ​

12.11 - Ví dụ thực tế nhanh

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

Nội dung bài học

12.11.1 — Nguyên tắc chung

12.11.2 — Cách 1: bọc cột trong hàm

12.11.3 — Cách 2: ép kiểu ngầm (nguy hiểm nhất)

12.11.4 — Cách 3: wildcard ở đầu chuỗi

12.11.5 — Cách 4: OR giữa các cột khác nhau

12.11.6 — Cách 5: thứ tự cột sai trong index ghép

12.11.7 — Kiểm chứng bằng logical reads

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

Bài tập áp dụng

Bài 1 — Đo cả năm cách

Bài 2 — Tìm ép kiểu ngầm

Bài 3 — Kiểm tra EF Core của chính dự án

Tự kiểm tra

Kết luận

Tham khảo

Điều hướng