N+1: đo SQL và dữ liệu khi nạp bằng EF Core

Câu hỏi bài này trả lời: một màn hình chỉ có vài đơn hàng nhưng phát ra nhiều SQL; sửa bằng Include, split hay projection, và kiểm thế nào để không đánh đổi dữ liệu đúng lấy số query thấp?

Cần biết trước: C#, LINQ, quan hệ một–nhiều và lab database. Bài đọc PostgreSQL 18.6 trong fixture local; .NET SDK 10.0.401/runtime 10.0.12, EF Core và provider Npgsql 10.0.0 là môi trường thử trên macOS arm64. Nhánh Docker/Linux chưa kiểm. Ghim package để đo một tổ hợp cụ thể, không coi đây là đề xuất phiên bản mới nhất cho production.

Cùng một màn hình, cùng một seed

Màn hình trả ID đơn, tên khách và các dòng SKU/số lượng. Dùng ba bảng wiki_lab.orders, customers, order_items: mỗi đơn có đúng ba dòng. Chọn N đơn đầu theo ID; sắp dòng theo ID trước khi so kết quả.

N+1 ở đây được viết tường minh: một query lấy đơn, rồi mỗi đơn gọi một query khách và một query dòng. Vì vậy cần kiểm 1 + 2N, không mặc định N+1 luôn có đúng N+1 câu. Không bật proxy hay lazy loading. Cả bốn cách dùng context mới và no tracking; cache entity hoặc navigation fixup của context cũ không được che query.

Tạo thư mục trống và chép bốn file cách A của bài lab: lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql. Các khối bash chạy ở thư mục này. lab_seed reset schema nên không dùng một lab còn chứa phép thử cần giữ.

. ./lab-local.sh
lab_up
lab_seed
lab_whoami

Project nhỏ, không migration hay server HTTP

Lưu hai file sau vào thư mục eflab. Không gọi EnsureDeleted, EnsureCreated hoặc SaveChanges: chương trình chỉ đọc fixture.

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="10.0.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.0" />
    <PackageReference Include="Npgsql" Version="10.0.0" />
  </ItemGroup>
</Project>

using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;
using Npgsql;

if (args.Length != 2 || !int.TryParse(args[1], out var n) || n < 1 || n > 20)
    throw new ArgumentException("Dùng: mode N, với 1 <= N <= 20");
var mode = args[0];
if (!new[] { "nplus1", "include", "split", "projection" }.Contains(mode))
    throw new ArgumentException("Mode không hợp lệ");
var dir = Environment.GetEnvironmentVariable("LAB_DIR")
    ?? throw new InvalidOperationException("Thiếu LAB_DIR");
if (!Path.IsPathRooted(dir) || !File.Exists(Path.Combine(dir, ".wiki-lab")))
    throw new InvalidOperationException("Thiếu dấu xác nhận lab");
Environment.SetEnvironmentVariable("PGPASSWORD", null);
Environment.SetEnvironmentVariable("PGPASSFILE", Path.Combine(dir, "no-pgpass"));
var connection = new NpgsqlConnectionStringBuilder
{
    Host = dir, Username = "lab", Database = "postgres", Pooling = false,
    Passfile = Path.Combine(dir, "no-pgpass"), Timeout = 10
}.ConnectionString;
await using (var probe = new NpgsqlConnection(connection))
{
    await probe.OpenAsync();
    await using var check = new NpgsqlCommand(
        "SELECT value FROM wiki_lab.lab_info WHERE key='lab'", probe);
    if ((string?)await check.ExecuteScalarAsync() != "wiki-lab")
        throw new InvalidOperationException("Kết nối không phải lab");
}
using var log = new StreamWriter($"sql-{mode}-{n}.log");
var options = new DbContextOptionsBuilder<LabDb>()
    .UseNpgsql(connection)
    .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)
    .LogTo(log.WriteLine, new[] { RelationalEventId.CommandExecuted },
        LogLevel.Information, DbContextLoggerOptions.SingleLine)
    .Options;
await using var db = new LabDb(options);
db.ChangeTracker.LazyLoadingEnabled = false;
var roots = db.Orders.OrderBy(o => o.Id).Take(n);
List<OrderDto> result;
if (mode == "nplus1")
{
    result = new();
    foreach (var order in await roots.ToListAsync())
    {
        var customer = await db.Customers.SingleAsync(c => c.Id == order.CustomerId);
        var items = await db.Items.Where(i => i.OrderId == order.Id).OrderBy(i => i.Id)
            .Select(i => new ItemDto(i.Sku, i.Qty)).ToListAsync();
        result.Add(new OrderDto(order.Id, customer.Name, items));
    }
}
else if (mode == "projection")
{
    result = await roots.AsSingleQuery().Select(o => new OrderDto(o.Id, o.Customer.Name,
        o.Items.OrderBy(i => i.Id).Select(i => new ItemDto(i.Sku, i.Qty)).ToList()))
        .ToListAsync();
}
else
{
    var query = roots.Include(o => o.Customer).Include(o => o.Items);
    var orders = await (mode == "split" ? query.AsSplitQuery() : query.AsSingleQuery())
        .ToListAsync();
    result = orders.Select(o => new OrderDto(o.Id, o.Customer.Name,
        o.Items.OrderBy(i => i.Id).Select(i => new ItemDto(i.Sku, i.Qty)).ToList()))
        .ToList();
}
if (db.ChangeTracker.Entries().Any())
    throw new InvalidOperationException("Đã track entity ngoài dự kiến");
await File.WriteAllTextAsync($"result-{mode}-{n}.json", JsonSerializer.Serialize(result));
Console.WriteLine($"{mode} N={n} orders={result.Count} items={result.Sum(o => o.Items.Count)} tracked=0");

record ItemDto(string Sku, int Qty);
record OrderDto(int Id, string Customer, List<ItemDto> Items);

sealed class LabDb(DbContextOptions<LabDb> options) : DbContext(options)
{
    public DbSet<OrderRow> Orders => Set<OrderRow>();
    public DbSet<CustomerRow> Customers => Set<CustomerRow>();
    public DbSet<ItemRow> Items => Set<ItemRow>();
    protected override void OnModelCreating(ModelBuilder model)
    {
        model.Entity<OrderRow>(b =>
        {
            b.ToTable("orders", "wiki_lab");
            b.HasKey(o => o.Id);
            b.Property(o => o.Id).HasColumnName("id");
            b.Property(o => o.CustomerId).HasColumnName("customer_id");
            b.Property(o => o.Status).HasColumnName("status");
            b.Property(o => o.TotalCents).HasColumnName("total_cents");
            b.HasOne(o => o.Customer).WithMany().HasForeignKey(o => o.CustomerId);
            b.HasMany(o => o.Items).WithOne().HasForeignKey(i => i.OrderId);
        });
        model.Entity<CustomerRow>(b =>
        {
            b.ToTable("customers", "wiki_lab");
            b.HasKey(c => c.Id);
            b.Property(c => c.Id).HasColumnName("id");
            b.Property(c => c.Name).HasColumnName("name");
            b.Property(c => c.Country).HasColumnName("country");
        });
        model.Entity<ItemRow>(b =>
        {
            b.ToTable("order_items", "wiki_lab");
            b.HasKey(i => i.Id);
            b.Property(i => i.Id).HasColumnName("id");
            b.Property(i => i.OrderId).HasColumnName("order_id");
            b.Property(i => i.Sku).HasColumnName("sku");
            b.Property(i => i.Qty).HasColumnName("qty");
            b.Property(i => i.PriceCents).HasColumnName("price_cents");
        });
    }
}
sealed class OrderRow
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Status { get; set; } = "";
    public int TotalCents { get; set; }
    public CustomerRow Customer { get; set; } = null!;
    public List<ItemRow> Items { get; set; } = new();
}
sealed class CustomerRow
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Country { get; set; } = "";
}
sealed class ItemRow
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string Sku { get; set; } = "";
    public int Qty { get; set; }
    public int PriceCents { get; set; }
}

Mapper chỉ có cột cần cho ví dụ; Include còn nạp status/total/country/price, trong khi DTO projection không cần chúng. Log chỉ ghi sự kiện CommandExecuted thành công, không bật sensitive-data logging. Probe lab dùng Npgsql trực tiếp trước khi mở log EF; seed, restore/build và probe không thuộc số query màn hình.

Kiểm dữ liệu và đếm lại từ log

Lưu checker bên ngoài eflab, cạnh các file lab. Expected DTO được tính từ công thức seed, không dùng kết quả N+1 làm chân lý cho ba cách kia.

import json
from pathlib import Path

for n in (3, 7):
    expected = [
        {"Id": g, "Customer": f"customer-{g * g % 2000 + 1}",
         "Items": [{"Sku": f"sku-{g * i * 31 % 500}", "Qty": i} for i in (1, 2, 3)]}
        for g in range(1, n + 1)
    ]
    results = []
    for mode, count in (("nplus1", 1 + 2 * n), ("include", 1), ("split", 2), ("projection", 1)):
        data = json.loads(Path(f"result-{mode}-{n}.json").read_text())
        sql = Path(f"sql-{mode}-{n}.log").read_text()
        actual = sql.count("Executed DbCommand")
        assert actual == count, (mode, n, actual, count)
        assert data == expected, (mode, n, "DTO khác công thức seed")
        assert sum(len(o["Items"]) for o in data) == n * 3
        results.append(data)
        print(f"{mode} N={n} queries={actual} orders={len(data)} items={n * 3}")
    assert all(data == results[0] for data in results)
    single = Path(f"sql-include-{n}.log").read_text()
    projected = Path(f"sql-projection-{n}.log").read_text()
    assert single.count("JOIN") >= 2
    for column in ("status", "total_cents", "country", "price_cents"):
        assert column in single and column not in projected, (n, column)
print("DTO equal; seed equal")

dotnet restore eflab/eflab.csproj --source https://api.nuget.org/v3/index.json
dotnet build eflab/eflab.csproj --no-restore -c Release

. ./lab-local.sh
. ./lab.env
lab_target
for n in 3 7; do
  for mode in nplus1 include split projection; do
    dotnet run --project eflab/eflab.csproj --no-build -c Release -- "$mode" "$n" >/dev/null
  done
done
python3 check-ef.py

Expected cần kiểm lại trên máy người đọc (không phải benchmark tốc độ):

nplus1 N=3 queries=7 orders=3 items=9
include N=3 queries=1 orders=3 items=9
split N=3 queries=2 orders=3 items=9
projection N=3 queries=1 orders=3 items=9
nplus1 N=7 queries=15 orders=7 items=21
include N=7 queries=1 orders=7 items=21
split N=7 queries=2 orders=7 items=21
projection N=7 queries=1 orders=7 items=21
DTO equal; seed equal

Mở sql-include-7.log và sql-split-7.log: single ghép khách/dòng vào cùng SELECT; split có SELECT gốc và SELECT collection. Projection vẫn một câu ở provider/version và LINQ này. Một Select khác có thể sinh SQL khác; ToQueryString() giúp xem hình dạng SQL nhưng không chứng minh số câu đã thực thi.

Chạy lại sau reset, giữ code nhưng nạp lại cùng seed:

. ./lab-local.sh
. ./lab.env
lab_seed
for n in 3 7; do
  for mode in nplus1 include split projection; do
    dotnet run --project eflab/eflab.csproj --no-build -c Release -- "$mode" "$n" >/dev/null
  done
done
python3 check-ef.py

Số query chưa phải chi phí tải dữ liệu

Trong seed này, single JOIN cho N đơn × 3 dòng = 3N hàng SQL, dù DTO chỉ có N đơn. Cột đơn/khách lặp trên từng hàng. Split đọc N hàng gốc rồi 3N hàng collection; N+1 đọc N hàng gốc, N hàng khách và 3N hàng dòng qua nhiều câu. Đó là suy luận từ quan hệ và SQL đã xem, không phải phép đo bytes hoặc bộ nhớ. Projection bớt cột nhưng vẫn cần dữ liệu các dòng.

Một câu truyền hàng lớn có thể tốn hơn hai câu nhỏ. Ngược lại, split thêm round trip và buffering; nhiều câu còn có thể thấy dữ liệu ở các thời điểm khác nhau nếu có writer. Lab không có writer, nên DTO bằng nhau ở đây không chứng minh consistency khi có cập nhật đồng thời. Muốn snapshot chung phải chọn transaction/isolation phù hợp với engine và chịu chi phí của nó. Tài liệu single/split giải thích các trade-off này.

Case chỉ Include một collection và một reference; không có tích Cartesian giữa hai collection ngang cấp. Nếu thêm payments cùng cấp với items, số hàng JOIN có thể là items × payments. Đừng gọi mọi dữ liệu lặp là cùng một kiểu Cartesian explosion.

No tracking giảm công việc ChangeTracker; nó không tự loại query trong loop. Projection chỉ chứa scalar/DTO nên không track entity; projection chứa nguyên entity có thể vẫn track. Tài liệu tracking nêu khác biệt này. Lab kiểm tracker rỗng nhưng không đo allocation/peak RSS hay thời gian API, không kết luận projection nhanh hơn một tỷ lệ cố định.

Lỗi thường gặp và cách chọn

Không đếm câu bằng số lần gọi hàm LINQ: LINQ xây biểu thức, truy vấn thường chạy khi enumerate. SingleAsync trong loop phát query thật ở ví dụ này. Efficient Querying là nguồn đọc thêm về nạp dữ liệu và chọn cột.

Reset, cleanup và bài tập

lab_seed reset schema; lab_clean dừng hai server và xóa dữ liệu riêng theo dấu lab. Log/JSON cùng thư mục thực hành chỉ chứa dữ liệu giả. Nếu bước build/query lỗi, vẫn chạy cleanup; đừng sửa Host sang database thật để thử cho qua.

. ./lab-local.sh
lab_clean
echo cleaned

Ba bài tập chưa chạy trong lượt kiểm của bài: thử N=1/20 và đếm lại; thêm collection payments để quan sát tích hàng; chạy writer giữa hai câu split rồi kiểm isolation. Giữ cùng yêu cầu DTO khi so các phương án.

Học tiếp: EXPLAIN ANALYZE, composite index và deadlock/retry. Package/provider tham chiếu Npgsql EF 10 và NuGet 10.0.0, đọc ngày 2026-10-03.