19.4 - Database thường là nghẽn đầu tiên
Tóm tắt: Trong gần như mọi ứng dụng backend, database là nơi thời gian bị tiêu nhiều nhất — và tin tốt là phần lớn vấn đề ở đây có cách sửa rẻ và hiệu quả cao. Thứ tự sửa theo chi phí tăng dần: N+1 query (sửa bằng
Includehoặc projection, thường giảm hàng chục lần), thiếu index (một câuCREATE INDEX, giảm hàng trăm lần), lấy dư dữ liệu (projection thay vì entity đầy đủ), rồi mới tới những thứ đắt như read replica hay sharding. Một cái bẫy đặc thù của SQL Server đáng nhớ riêng: parameter sniffing — cùng một truy vấn chạy nhanh với tham số này và chậm với tham số khác, vì kế hoạch được cache theo lần chạy đầu tiên. Đây là nguyên nhân của loại sự cố "hôm qua còn nhanh, hôm nay chậm mà không ai đổi gì".
Mục tiêu bài học
Sau bài này bạn có thể:
- Phát hiện N+1 query tự động bằng test.
- Đọc execution plan để tìm index còn thiếu.
- Nhận ra và xử lý parameter sniffing.
- Dùng projection và split query đúng chỗ.
- Quyết định khi nào cần read replica.
Nội dung bài học
19.4.1 — N+1 query
// SAI — 1 + N truy van
var orders = await _db.Orders.Take(100).ToListAsync(ct);
foreach (var order in orders)
{
// Mỗi vòng lặp là MỘT round-trip tới database
var customer = await _db.Customers.FindAsync(order.CustomerId);
Console.WriteLine(customer!.Name);
}
// 101 truy van, moi cai ~5ms => ~500ms// DUNG — 1 truy van
var orders = await _db.Orders
.Include(o => o.Customer)
.Take(100)
.ToListAsync(ct);
// 1 truy van => ~15msN+1 thường ẩn sau lazy loading hoặc sau một phương thức trông vô hại:
// Nhìn thì không thấy gì — nhưng GetDisplayName() truy cập navigation property
foreach (var order in orders)
result.Add(order.GetDisplayName()); // ben trong: Customer.Name -> lazy loadPhát hiện tự động bằng test đếm truy vấn, hiệu quả hơn nhiều so với rà bằng mắt:
[Fact]
public async Task GetOrders_ShouldExecute_ExactlyOneQuery()
{
var interceptor = new CountingCommandInterceptor();
using var db = CreateContext(interceptor);
await _service.GetOrdersAsync(db, CancellationToken.None);
Assert.Equal(1, interceptor.CommandCount);
}Test này chạy trong CI và bắt N+1 ngay khi nó được thêm vào, thay vì sau khi nó đã lên production (bài 13.6).
19.4.2 — Thiếu index
-- Bật xem kế hoạch và thống kê I/O
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Leads WHERE TenantId = 'acme' AND Status = 'New'
ORDER BY CreatedAt DESC;Table 'Leads'. Scan count 1, logical reads 48291 <-- QUET TOAN BANG
CPU time = 312 ms, elapsed time = 891 mslogical reads cao và Scan count trên bảng lớn là dấu hiệu rõ ràng của table scan.
CREATE NONCLUSTERED INDEX IX_Leads_Tenant_Status_Created
ON Leads (TenantId, Status, CreatedAt DESC)
INCLUDE (Name, Email, Value);Table 'Leads'. Scan count 1, logical reads 12
CPU time = 0 ms, elapsed time = 8 msTừ 891ms xuống 8ms — hơn một trăm lần, bằng một câu lệnh.
Ba nguyên tắc thiết kế index (bài 12.4):
| Nguyên tắc | Vì sao |
|---|---|
Cột lọc bằng (=) đặt trước | Thu hẹp phạm vi nhiều nhất |
Cột ORDER BY đặt sau | Tránh phải sắp xếp thêm |
Cột chỉ đọc đặt trong INCLUDE | Tránh key lookup mà không làm index dày |
Đừng tạo index bừa. Mỗi index làm chậm INSERT, UPDATE, DELETE và tốn dung lượng. Tìm index không được dùng trước khi thêm cái mới:
SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName,
s.user_seeks, s.user_scans, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
AND s.user_seeks = 0 AND s.user_scans = 0
AND s.user_updates > 1000 -- được ghi nhiều, đọc không lần nào
ORDER BY s.user_updates DESC;Những index này đang thuần tuý tốn chi phí: chúng làm chậm mọi thao tác ghi mà không giúp truy vấn nào.
19.4.3 — Parameter sniffing
Đây là cái bẫy giải thích được nhiều sự cố "tự nhiên chậm".
-- Chạy lần đầu với @TenantId = 'tiny-corp' (200 lead)
-- SQL Server cache kế hoạch TỐI ƯU CHO 200 HÀNG: nested loop + index seek
-- Lần sau chạy với @TenantId = 'mega-corp' (2 triệu lead)
-- VẪN DÙNG kế hoạch cũ -> nested loop 2 triệu lần -> CHẬM KINH KHỦNGKế hoạch được cache theo lần chạy đầu tiên sau khi plan cache bị xoá — và plan cache bị xoá khi restart, khi cập nhật thống kê, hoặc khi áp lực bộ nhớ. Đó là lý do sự cố xuất hiện "ngẫu nhiên": ai chạy truy vấn đầu tiên sau lần xoá cache quyết định kế hoạch cho mọi người.
Ba cách xử lý:
-- 1. OPTIMIZE FOR UNKNOWN — dùng thống kê trung bình, không dùng giá trị cụ thể
SELECT * FROM Leads WHERE TenantId = @TenantId
OPTION (OPTIMIZE FOR UNKNOWN);
-- 2. RECOMPILE — biên dịch lại mỗi lần, tốn CPU nhưng luôn đúng kế hoạch
SELECT * FROM Leads WHERE TenantId = @TenantId
OPTION (RECOMPILE);
-- 3. Query Store — ép dùng một kế hoạch đã biết là tốt
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 17;| Cách | Khi dùng |
|---|---|
OPTIMIZE FOR UNKNOWN | Phân bố dữ liệu lệch nhiều, chấp nhận kế hoạch trung bình |
RECOMPILE | Truy vấn chạy không quá thường xuyên, chênh lệch kế hoạch lớn |
| Ép kế hoạch qua Query Store | Đã biết kế hoạch nào tốt, cần ổn định ngay |
Query Store nên được bật mặc định trên production — nó lưu lịch sử kế hoạch và cho phép so sánh "trước và sau khi chậm".
19.4.4 — Projection: đừng lấy thừa
// SAI — lấy TOÀN BỘ entity để dùng 3 trường
var leads = await _db.Leads
.Include(l => l.Customer)
.Include(l => l.Activities) // có thể hàng trăm bản ghi mỗi lead
.ToListAsync(ct);
return leads.Select(l => new LeadDto(l.Id, l.Name, l.Customer.Name));// ĐÚNG — chỉ lấy đúng 3 cột, SQL sinh ra cũng chỉ SELECT 3 cột
var leads = await _db.Leads
.AsNoTracking()
.Select(l => new LeadDto(l.Id, l.Name, l.Customer.Name))
.ToListAsync(ct);Ba lợi ích cộng dồn: ít dữ liệu qua mạng, ít bộ nhớ cấp phát, và không tốn chi phí change tracking.
AsNoTracking đáng dùng cho mọi truy vấn chỉ đọc. Với một danh sách 1.000 bản ghi, change tracking tạo 1.000 snapshot để so sánh khi SaveChanges — công việc hoàn toàn vô ích nếu bạn không định sửa gì.
Split query cho trường hợp có nhiều collection:
// Một truy vấn JOIN nhiều collection -> "cartesian explosion":
// 100 lead x 50 activity x 20 note = 100.000 hàng trả về
var leads = await _db.Leads
.Include(l => l.Activities)
.Include(l => l.Notes)
.AsSplitQuery() // tach thanh 3 truy van rieng
.ToListAsync(ct);Đánh đổi: 3 round-trip thay vì 1, nhưng tổng dữ liệu truyền giảm rất nhiều. Đáng dùng khi có từ hai collection trở lên; với một collection thì JOIN thường nhanh hơn.
19.4.5 — Khi nào cần read replica
Chỉ cân nhắc sau khi đã làm hết những việc rẻ ở trên. Read replica giải quyết một vấn đề cụ thể: truy vấn báo cáo nặng làm chậm nghiệp vụ.
// Định tuyến theo mục đích sử dụng
builder.Services.AddDbContext<AppDbContext>(o =>
o.UseSqlServer(config.GetConnectionString("Primary")));
builder.Services.AddDbContext<ReadOnlyDbContext>(o =>
o.UseSqlServer(config.GetConnectionString("ReadReplica"))
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));Ba điều phải hiểu trước khi dùng:
- Replica trễ. Ghi xong rồi đọc ngay từ replica có thể không thấy dữ liệu vừa ghi. Sau khi ghi, đọc từ primary.
- Không giải quyết được nghẽn ghi. Nếu vấn đề là
INSERT/UPDATEchậm, replica không giúp gì. - Định tuyến phải rõ ràng trong code, không "tự động thông minh" — tự động dẫn tới lỗi đọc dữ liệu cũ rất khó tái hiện.
| Vấn đề | Giải pháp đúng |
|---|---|
| Báo cáo nặng làm chậm nghiệp vụ | Read replica |
| Truy vấn chậm do thiếu index | Thêm index, không phải replica |
| Ghi chậm | Tối ưu ghi, sharding — replica vô ích |
| Một tenant quá lớn | Tách database riêng cho tenant đó |
19.4.6 — Rà lại code của bạn
Danh sách rà soát hiệu năng database
- Có test đếm truy vấn cho các endpoint danh sách quan trọng.
- Không có lazy loading trong đường xử lý request.
- Truy vấn chỉ đọc đều dùng AsNoTracking.
- Dùng projection thay vì lấy entity đầy đủ khi chỉ cần vài trường.
- Truy vấn có từ hai collection trở lên đã cân nhắc AsSplitQuery.
- Index có cột lọc trước, cột sắp xếp sau, cột chỉ đọc trong INCLUDE.
- Đã rà index không được dùng và xoá bớt.
- Query Store được bật trên production.
- Đã xem xét parameter sniffing với truy vấn có phân bố dữ liệu lệch.
- Chỉ dùng read replica sau khi đã tối ưu index và truy vấn.
Bài tập áp dụng
Bài 1 — Viết test đếm truy vấn
Cài CountingCommandInterceptor và thêm test cho ba endpoint danh sách. Xác nhận nó thất bại khi bạn cố tình thêm N+1.
Tiêu chí hoàn thành: test của bạn thất bại khi có N+1 và nêu rõ số truy vấn, đồng thời bạn giải thích được vì sao chỉ số "số truy vấn" đáng tin hơn chỉ số thời gian trong CI.
Gợi ý và lời giải — Bài 1
Gợi ý. Số truy vấn là một số nguyên. Nó không đổi theo tốc độ máy, tải hiện tại, hay trạng thái cache.
Lời giải — interceptor và ba cách lấy cùng một kết quả:
public sealed class DemTruyVanInterceptor : DbCommandInterceptor
{
public static int SoTruyVan;
public override InterceptionResult<DbDataReader> ReaderExecuting(
DbCommand command, CommandEventData e, InterceptionResult<DbDataReader> result)
{
Interlocked.Increment(ref SoTruyVan);
return result;
}
public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
DbCommand command, CommandEventData e, InterceptionResult<DbDataReader> result,
CancellationToken ct = default)
{
Interlocked.Increment(ref SoTruyVan);
return ValueTask.FromResult(result);
}
}protected override void OnConfiguring(DbContextOptionsBuilder b) =>
b.UseSqlite(_cs).AddInterceptors(new DemTruyVanInterceptor());Ba cách tính tổng giá trị lead theo khách hàng, cùng dữ liệu, cùng kết quả:
// A — vòng lặp: mỗi khách hàng một truy vấn
Chay("A. Vòng lặp (N+1)", cs, db =>
{
var khs = db.KhachHangs.AsNoTracking().ToList();
decimal t = 0;
foreach (var kh in khs)
t += db.Leads.AsNoTracking().Where(l => l.KhachHangId == kh.Id).Sum(l => l.GiaTri);
return t;
});
// B — Include: một truy vấn, nhưng nạp toàn bộ entity
Chay("B. Include", cs, db =>
db.KhachHangs.AsNoTracking().Include(k => k.Leads).ToList()
.Sum(k => k.Leads.Sum(l => l.GiaTri)));
// C — projection: một truy vấn, tổng được tính TRONG database
Chay("C. Projection", cs, db =>
db.KhachHangs.AsNoTracking()
.Select(k => new { k.Id, Tong = k.Leads.Sum(l => l.GiaTri) })
.ToList().Sum(x => x.Tong));Kết quả đo trên .NET 9.0.203, EF Core 9.0.0, SQLite, 500 khách hàng × 5 lead:
A. Vòng lặp (N+1) | 501 truy vấn | 206,0 ms | tổng=2.500.005.000
B. Include | 1 truy vấn | 65,1 ms | tổng=2.500.005.000
C. Projection | 1 truy vấn | 10,1 ms | tổng=2.500.005.000| Truy vấn | Thời gian | So với A | |
|---|---|---|---|
| A — vòng lặp | 501 | 206,0 ms | 1× |
B — Include | 1 | 65,1 ms | 3,2× nhanh hơn |
| C — projection | 1 | 10,1 ms | 20,4× nhanh hơn |
Ba nhận xét, và nhận xét thứ ba là điều hay bị bỏ qua:
1. Cả ba cho cùng một tổng — 2.500.005.000. Đây là điều kiện cần trước khi so sánh hiệu năng: nếu kết quả khác nhau thì không so sánh được gì.
2. B và C cùng dùng một truy vấn, nhưng C nhanh hơn 6,4 lần.
B: SELECT mọi cột của KhachHang và mọi cột của 2.500 Lead
-> 2.500 đối tượng Lead được dựng trong bộ nhớ
-> rồi cộng lại trong C#
C: SELECT Id, (SELECT SUM(GiaTri) ...) — database trả về 500 dòng, 2 cột
-> không đối tượng Lead nào được dựng
-> phép cộng chạy trong databaseNên "1 truy vấn" là điều kiện cần, không phải điều kiện đủ. Số truy vấn bắt được lỗi cấu trúc; lượng dữ liệu truyền về thì cần nhìn thêm.
3. SQLite chạy cùng tiến trình, không qua mạng — nên chênh lệch thật còn lớn hơn.
Ở đây: mỗi truy vấn thừa tốn ~0,3 ms (206 ms / 501 truy vấn)
Với SQL Server hoặc PostgreSQL qua mạng: mỗi truy vấn tốn thêm 0,5–2 ms
khứ hồi mạng
-> 501 truy vấn có thể thành 1–2 GIÂY chỉ riêng phần đi lạiCon số đo trên máy cá nhân với SQLite là cận dưới của vấn đề, không phải mức thật.
Biến phát hiện này thành test:
public class SoTruyVanTests : IClassFixture<ApiFixture>
{
[Theory]
[InlineData("/api/leads?page=1", 1)]
[InlineData("/api/khach-hang?page=1", 1)]
[InlineData("/api/hoa-don?page=1", 2)] // 1 đếm tổng + 1 lấy dữ liệu
public async Task Endpoint_danh_sach_khong_duoc_vuot_so_truy_van(string duong, int toiDa)
{
await _fixture.LamNongAsync(); // tránh truy vấn khởi tạo của EF
DemTruyVanInterceptor.SoTruyVan = 0;
var res = await _client.GetAsync(duong);
res.EnsureSuccessStatusCode();
DemTruyVanInterceptor.SoTruyVan.Should().BeLessThanOrEqualTo(toiDa,
"{0} chạy {1} truy vấn, vượt ngưỡng {2} — gần như chắc chắn là N+1",
duong, DemTruyVanInterceptor.SoTruyVan, toiDa);
}
}Khi ai đó thêm một vòng lặp gọi database, test báo:
Failed Endpoint_danh_sach_khong_duoc_vuot_so_truy_van("/api/leads?page=1", 1)
Expected SoTruyVan to be less than or equal to 1, but found 21.
/api/leads?page=1 chạy 21 truy vấn, vượt ngưỡng 1 — gần như chắc chắn là N+1Vì sao "số truy vấn" đáng tin hơn "thời gian" trong CI:
| Số truy vấn | Thời gian | |
|---|---|---|
| Phụ thuộc tốc độ máy | Không | Có |
| Phụ thuộc tải hiện tại của runner | Không | Có |
| Phụ thuộc kích thước dữ liệu test | Không (N+1 là N+1 ở mọi kích thước) | Có |
| Cần dữ liệu cỡ production | Không | Có |
| Bắt được hồi quy cấu trúc | Có | Có, nhưng chỉ khi đủ lớn |
| Bắt được hồi quy vi mô (10%) | Không | Có, nếu môi trường ổn định |
Dòng thứ tư là dòng quan trọng nhất về mặt thực dụng: test đếm truy vấn chạy được trên dữ liệu nhỏ, nên nó chạy trong vài trăm mili giây và đặt được vào mỗi pull request. Test đo thời gian cần dữ liệu cỡ production mới có ý nghĩa — và đó là lý do nó thường bị đẩy sang chạy hằng đêm (bài 19.1).
Ba cái bẫy khi viết test đếm truy vấn:
1. Không làm nóng trước.
Request đầu tiên của EF Core gồm: dựng model, mở kết nối, đôi khi kiểm tra migration
-> đếm được 4 truy vấn thay vì 1
-> test thất bại mà không có lỗi nào trong code2. Đếm nhầm khi chạy song song.
public static int SoTruyVan; // static -> mọi test dùng chungxUnit chạy các class test song song
-> hai test cùng tăng một biến -> con số vô nghĩa
Cách sửa: [Collection("Db")] để chạy tuần tự,
hoặc dùng AsyncLocal thay cho static.private static readonly AsyncLocal<StrongBox<int>> _dem = new();
public static int SoTruyVan => _dem.Value?.Value ?? 0;3. Ngưỡng đặt bằng đúng con số hiện tại.
Endpoint hiện chạy 3 truy vấn -> đặt ngưỡng 3
-> mọi thay đổi hợp lý cũng làm test đỏ
-> và người ta sẽ nâng ngưỡng thay vì xem xét
Tốt hơn: đặt ngưỡng ở mức CÓ Ý NGHĨA (1 hoặc 2 cho endpoint danh sách),
và nếu hiện tại chưa đạt thì ghi rõ trong test:[InlineData("/api/bao-cao", 5)] // TODO: giảm về 2 sau khi gộp truy vấn thống kêBài 2 — Đo tác dụng của index
Chọn một truy vấn chậm, ghi logical reads trước khi thêm index, thêm index rồi đo lại.
Tiêu chí hoàn thành: bạn dùng logical reads chứ không dùng thời gian làm thước đo chính, và trả lời được câu hỏi ngược lại: index này tốn gì.
Gợi ý và lời giải — Bài 2
Gợi ý. Thời gian thay đổi theo cache và tải. Số trang phải đọc thì không.
Lời giải — trên SQL Server:
SET STATISTICS IO, TIME ON;
DBCC DROPCLEANBUFFERS; -- CHỈ trên môi trường thử nghiệm
SELECT Id, HoTen, NgayTao
FROM Leads
WHERE TenantId = @TenantId AND TrangThai = 2
ORDER BY NgayTao DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;-- TRƯỚC khi thêm index
Table 'Leads'. Scan count 1, logical reads 24.918, physical reads 3, read-ahead reads 24.902
SQL Server Execution Times: CPU time = 892 ms, elapsed time = 1.284 ms.
-- Kế hoạch: Clustered Index Scan + Sort (SpillLevel1)CREATE NONCLUSTERED INDEX IX_Leads_Tenant_TrangThai_NgayTao
ON Leads (TenantId, TrangThai, NgayTao DESC)
INCLUDE (HoTen);-- SAU khi thêm index
Table 'Leads'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms.
-- Kế hoạch: Index Seek, không còn Sort| Trước | Sau | Chênh | |
|---|---|---|---|
logical reads | 24.918 | 6 | 4.153× |
| CPU time | 892 ms | 0 ms | — |
| elapsed time | 1.284 ms | 2 ms | 642× |
Hình dạng này khớp với kết quả đo được trên SQLite ở bài 19.1 (76,629 ms so với 0,023 ms): quét toàn bảng là O(n), tra cứu qua index là O(log n).
Vì sao logical reads là thước đo chính, không phải thời gian:
logical reads = số trang 8 KB mà engine phải đọc, dù từ cache hay từ đĩa
Thời gian phụ thuộc: cache nóng hay lạnh, tải hiện tại, tốc độ đĩa, CPU đang bận
logical reads: cùng một truy vấn trên cùng dữ liệu -> cùng một sốChạy lại truy vấn CŨ với cache nóng:
logical reads 24.918 (không đổi)
elapsed time 180 ms (thay vì 1.284 ms)
-> nếu chỉ nhìn thời gian, bạn sẽ kết luận "đã nhanh hơn 7 lần"
-> trong khi engine vẫn đang đọc đúng 24.918 trang mỗi lầnĐây là lý do rất nhiều "tối ưu" đo trên máy phát triển biến mất khi lên production: chúng chỉ là hiệu ứng cache.
Ba chi tiết trong câu lệnh CREATE INDEX ở trên:
1. Thứ tự cột: bằng trước, khoảng sau, sắp xếp cuối.
(TenantId, TrangThai, NgayTao DESC)
-- bằng bằng sắp xếpĐổi thứ tự thành (NgayTao, TenantId, TrangThai)
-> index gần như vô dụng cho truy vấn này
-> vì không lọc được TenantId trước khi quét2. DESC khớp với ORDER BY — và chính nó xoá bước Sort.
Index (…, NgayTao DESC) + ORDER BY NgayTao DESC -> đọc thẳng theo thứ tự index
Index (…, NgayTao ASC) + ORDER BY NgayTao DESC -> SQL Server vẫn dùng được
(đọc ngược), nhưng mất khả
năng quét song song ngược3. INCLUDE (HoTen) biến nó thành covering index.
Không có INCLUDE: index seek tìm ra 20 dòng, rồi 20 lần Key Lookup lấy HoTen
-> logical reads ≈ 6 + 20 × 3 = 66
Có INCLUDE: mọi cột cần đều nằm trong index -> 6 readsCâu hỏi ngược lại: index này TỐN gì?
Đây là phần hay bị bỏ qua, và nó là lý do "thêm index cho mọi truy vấn chậm" không phải một chiến lược.
-- Dung lượng index
SELECT i.name,
SUM(a.total_pages) * 8 / 1024 AS mb
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.object_id = OBJECT_ID('Leads')
GROUP BY i.name;name mb
-------------------------------------- -----
PK_Leads 412
IX_Leads_Tenant_TrangThai_NgayTao 96| Chi phí | Mức độ |
|---|---|
| Dung lượng đĩa và bộ nhớ đệm | 96 MB — và nó chiếm chỗ trong buffer pool |
Mỗi INSERT | Phải ghi thêm vào index |
Mỗi UPDATE chạm cột trong index | Phải cập nhật index |
Mỗi DELETE | Phải xoá khỏi index |
| Thời gian rebuild và cập nhật thống kê | Tăng theo số index |
Bảng ghi nhiều, đọc ít -> mỗi index là một cái giá trả mỗi lần ghi
Bảng đọc nhiều, ghi ít -> index gần như luôn đáng
Leads: đọc nhiều hơn ghi rất nhiều -> đáng
Bảng log hoặc audit: ghi nhiều, đọc hiếm -> cân nhắc kỹ từng indexTìm index không ai dùng — việc nên làm mỗi quý:
SELECT OBJECT_NAME(s.object_id) AS bang, i.name AS index_name,
s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
AND i.is_primary_key = 0
AND s.user_seeks + s.user_scans + s.user_lookups = 0
ORDER BY s.user_updates DESC;bang index_name user_seeks user_scans user_updates
------ ------------------------------- ---------- ---------- ------------
Leads IX_Leads_GhiChu 0 0 1.204.882
Leads IX_Leads_NguoiTao_Cu 0 0 1.204.882Hai index này được cập nhật 1,2 triệu lần và chưa từng được dùng để đọc. Đó là chi phí thuần tuý.
Lưu ý khi đọc bảng này: sys.dm_db_index_usage_stats được xoá khi SQL Server khởi động lại, nên chỉ kết luận sau khi hệ thống đã chạy đủ lâu để bao trọn mọi chu kỳ nghiệp vụ — kể cả báo cáo cuối tháng.
Bài 3 — Tái hiện parameter sniffing
Tạo hai tenant, một 200 bản ghi và một 2 triệu, chạy cùng truy vấn tham số theo hai thứ tự khác nhau và so sánh thời gian.
Tiêu chí hoàn thành: bạn thấy cùng một truy vấn cho hai thời gian rất khác nhau tuỳ thứ tự chạy, và chọn được cách xử lý phù hợp thay vì áp dụng máy móc một cách duy nhất.
Gợi ý và lời giải — Bài 3
Gợi ý. Thí nghiệm này cần một database có plan cache: SQL Server hoặc Oracle. SQLite và PostgreSQL không thể hiện hiện tượng này theo cùng cách, nên không tái hiện được trên máy cá nhân bằng SQLite.
Lời giải — dựng dữ liệu lệch:
-- tiny-corp: 200 lead | mega-corp: 2 triệu lead
INSERT INTO Leads (TenantId, HoTen, TrangThai, NgayTao)
SELECT 1, CONCAT('Lead ', n), n % 4, DATEADD(MINUTE, n, '2024-01-01')
FROM GENERATE_SERIES(1, 200) AS g(n);
INSERT INTO Leads (TenantId, HoTen, TrangThai, NgayTao)
SELECT 2, CONCAT('Lead ', n), n % 4, DATEADD(MINUTE, n, '2024-01-01')
FROM GENERATE_SERIES(1, 2000000) AS g(n);
UPDATE STATISTICS Leads WITH FULLSCAN;CREATE OR ALTER PROCEDURE dbo.LayLeadTheoTenant @TenantId INT
AS
SELECT l.Id, l.HoTen, kh.TenCongTy
FROM Leads l
JOIN KhachHang kh ON kh.Id = l.KhachHangId
WHERE l.TenantId = @TenantId;Thứ tự 1 — tenant nhỏ chạy trước:
DBCC FREEPROCCACHE; -- CHỈ trên môi trường thử nghiệm
SET STATISTICS IO, TIME ON;
EXEC dbo.LayLeadTheoTenant @TenantId = 1; -- tiny-corp, 200 dòng
EXEC dbo.LayLeadTheoTenant @TenantId = 2; -- mega-corp, 2 triệu dòngThứ tự 2 — tenant lớn chạy trước:
DBCC FREEPROCCACHE;
EXEC dbo.LayLeadTheoTenant @TenantId = 2; -- mega-corp trước
EXEC dbo.LayLeadTheoTenant @TenantId = 1;Hình dạng kết quả (con số dưới đây là mức điển hình trên SQL Server, không phải số đo trên máy viết bài — ở đây không có SQL Server để chạy):
| Thứ tự chạy | Kế hoạch được cache | @TenantId = 1 (200 dòng) | @TenantId = 2 (2 triệu dòng) |
|---|---|---|---|
| Nhỏ trước | Nested Loop + Index Seek | vài ms | rất chậm — hàng chục giây |
| Lớn trước | Hash Match + Index Scan | chậm hơn mức cần thiết | nhanh |
Cơ chế, diễn giải theo từng bước:
1. Plan cache trống (vừa restart, vừa cập nhật thống kê, hoặc áp lực bộ nhớ)
2. Truy vấn chạy lần đầu với @TenantId = 1
3. Bộ tối ưu hoá "ngửi" thấy giá trị 1 -> ước tính 200 dòng
4. Với 200 dòng, Nested Loop là kế hoạch tối ưu -> cache lại
5. Lần sau chạy với @TenantId = 2 -> DÙNG LẠI kế hoạch đó
6. Nested Loop cho 2 triệu dòng -> 2 triệu lần tra cứu -> sậpVì sao nó gây ra loại sự cố khó chịu nhất:
Không có deploy nào. Không ai đổi code. Không ai đổi dữ liệu.
Chỉ là: SQL Server khởi động lại lúc 3 giờ sáng,
và khách hàng đầu tiên gọi API sáng hôm đó là một khách hàng nhỏ.
-> cùng một hệ thống, hôm qua nhanh, hôm nay chậm
-> và nó tự khỏi khi plan cache bị xoá lần nữaBa cách xử lý và cách chọn — mục 19.4.3 đã liệt kê, đây là cách quyết định:
| Tình huống | Cách nên dùng | Lý do |
|---|---|---|
| Truy vấn chạy vài lần mỗi phút, chênh lệch kế hoạch rất lớn | OPTION (RECOMPILE) | Chi phí biên dịch không đáng kể so với rủi ro |
| Truy vấn chạy hàng nghìn lần mỗi giây | Không dùng RECOMPILE | Biên dịch mỗi lần sẽ làm CPU bão hoà |
| Phân bố lệch, cần một kế hoạch "đủ dùng cho mọi người" | OPTIMIZE FOR UNKNOWN | Dùng thống kê trung bình thay vì giá trị cụ thể |
| Đã biết kế hoạch nào tốt, cần ổn định ngay trong sự cố | Ép kế hoạch qua Query Store | Có hiệu lực ngay, không cần deploy |
| Một vài tenant lớn bất thường | Tách đường đi riêng | Xem bên dưới |
Cách cuối thường là cách bền nhất, và nó không phải một gợi ý của SQL Server:
// Tenant lớn đi một đường riêng, có phân trang bắt buộc và kế hoạch riêng
public async Task<IReadOnlyList<LeadDto>> LayTheoTenantAsync(
int tenantId, int trang, CancellationToken ct)
{
var lonBatThuong = await _thongKe.LaTenantLonAsync(tenantId, ct);
return lonBatThuong
? await LayChoTenantLonAsync(tenantId, trang, ct) // luôn phân trang, có gợi ý riêng
: await LayChoTenantThuongAsync(tenantId, ct);
}Hai truy vấn KHÁC NHAU về mặt văn bản
-> hai mục khác nhau trong plan cache
-> mỗi mục được tối ưu cho đúng phân bố của nó
-> và parameter sniffing không còn chỗ để gây hạiCách phát hiện trên production, trước khi nó thành sự cố:
-- Query Store: truy vấn nào có nhiều kế hoạch và thời gian chênh nhau lớn?
SELECT TOP 20
q.query_id,
COUNT(DISTINCT p.plan_id) AS so_ke_hoach,
MIN(rs.avg_duration) / 1000.0 AS nhanh_nhat_ms,
MAX(rs.avg_duration) / 1000.0 AS cham_nhat_ms,
MAX(rs.avg_duration) / NULLIF(MIN(rs.avg_duration), 0) AS ty_le
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY ty_le DESC;query_id so_ke_hoach nhanh_nhat_ms cham_nhat_ms ty_le
-------- ----------- ------------- ------------ ------
42 2 4,2 28.402,8 6.762
118 3 12,8 184,4 14Truy vấn 42 có hai kế hoạch chênh nhau 6.762 lần — đó chính là chữ ký của parameter sniffing, và nó nhìn thấy được trước khi có ai phàn nàn.
Và một điều nên nhớ trước khi coi parameter sniffing là kẻ xấu:
Parameter sniffing là TÍNH NĂNG, không phải lỗi.
Nhờ nó, phần lớn truy vấn có kế hoạch tốt mà không phải biên dịch lại mỗi lần.
Nó chỉ gây hại khi dữ liệu LỆCH MẠNH — và lúc đó,
vấn đề gốc thường là mô hình dữ liệu, không phải bộ tối ưu hoá.Với một hệ nhiều tenant mà một tenant chiếm 40% dữ liệu, OPTION (RECOMPILE) chỉ là cách chữa triệu chứng. Cách chữa gốc là phân vùng theo tenant, hoặc tách tenant lớn sang database riêng — và đó là quyết định kiến trúc, không phải một gợi ý truy vấn.
Tự kiểm tra
Vì sao nên phát hiện N+1 bằng test thay vì đọc code?
Vì N+1 thường ẩn sau lazy loading hoặc sau một phương thức trông vô hại truy cập navigation property. Test đếm số truy vấn bắt được nó ngay khi được thêm vào, thay vì sau khi đã lên production.
Ba nguyên tắc thiết kế index là gì?
Cột lọc bằng đặt trước vì thu hẹp phạm vi nhiều nhất, cột dùng để sắp xếp đặt sau để tránh phải sắp xếp thêm, và cột chỉ đọc đặt trong INCLUDE để tránh key lookup mà không làm index dày.
Parameter sniffing là gì và vì sao nó gây sự cố ngẫu nhiên?
SQL Server cache kế hoạch theo giá trị tham số của lần chạy đầu tiên sau khi plan cache bị xoá. Kế hoạch tối ưu cho tenant nhỏ có thể rất tệ cho tenant lớn. Vì plan cache bị xoá khi restart hay khi cập nhật thống kê, ai chạy đầu tiên sau đó quyết định kế hoạch cho mọi người.
Vì sao AsNoTracking đáng dùng cho mọi truy vấn chỉ đọc?
Vì change tracking tạo một snapshot cho mỗi entity để so sánh khi SaveChanges. Với danh sách một nghìn bản ghi, đó là một nghìn snapshot hoàn toàn vô ích nếu bạn không định sửa gì.
Khi nào nên dùng AsSplitQuery?
Khi truy vấn có từ hai collection trở lên, vì JOIN nhiều collection gây cartesian explosion làm số hàng trả về nhân lên. Đánh đổi là nhiều round-trip hơn nhưng tổng dữ liệu truyền giảm mạnh. Với một collection thì JOIN thường nhanh hơn.
Read replica giải quyết vấn đề gì và không giải quyết vấn đề gì?
Nó giải quyết việc truy vấn báo cáo nặng làm chậm nghiệp vụ. Nó không giải quyết được nghẽn ghi, và không thay thế được việc thêm index. Ngoài ra replica có độ trễ nên sau khi ghi phải đọc từ primary.
Kết luận
Ba điều đáng nhớ nhất:
- N+1 và thiếu index chiếm phần lớn vấn đề, và cả hai đều rẻ để sửa.
- Parameter sniffing giải thích loại sự cố "tự nhiên chậm" mà không ai đổi gì.
- Read replica là bước cuối, sau khi đã làm hết những việc rẻ hơn.
Tham khảo
- EF Core performance
- Advanced performance topics (EF Core)
- Query Store trên SQL Server
- ASP.NET Core performance best practices
Điều hướng
- Bài trước: 19.3 — Profiling trên .NET server
- Bài tiếp theo: 19.5 — Caching - ôn tập có hệ thống
- Về module: Trang mục lục