Lịch sử đơn quét cả bảng: chỉ mục phủ và điều kiện seek được

1. Vấn đề: lịch sử đơn năm nay của đại lý 42 quét cả bảng #

Màn hình lịch sử đơn của BanHang hiện các đơn của một khách trong năm được chọn, mặc định là năm nay, với mã đơn, ngày, trạng thái và tổng tiền. Code EF Core viết tự nhiên nhất:

var don = await db.DonHang
    .Where(d => d.KhachHangId == khach && d.NgayTao.Year == nam)
    .Select(d => new { d.DonHangId, d.NgayTao, d.TrangThai, d.TongTien })
    .ToListAsync();

EF Core dịch .Year thành DATEPART(year, [d].[NgayTao]) = @nam. Với khách 42, đại lý B2B có khoảng 300.000 đơn từ 2024, trình tối ưu chọn quét cả PK_DonHang: khoảng 45.880 page trên BanHang 10 triệu dòng mỗi lần mở màn hình. Đường còn lại, seek IX_DonHang_KhachHang rồi key lookup để lấy TrangThai và TongTien, còn đắt hơn: khách 42 có khoảng 82.000 đơn năm 2026, mỗi đơn khoảng 3 page lookup như ở bài trước, tức khoảng 246.000 lần đọc.

Trên bảng thử thu nhỏ 10 lần, câu này đọc 4.608 page để trả 8.268 đơn năm 2026: mọi page lá của bảng, kể cả hai năm không ai hỏi tới. Nhân viên kinh doanh mở lịch sử đơn của đại lý nhiều lần mỗi ngày, và mỗi lần là một lượt quét cả bảng đơn hàng.

2. Mục đích: lịch sử đơn năm nay của khách 42 dưới 100 page #

• Trên bảng thử, câu lịch sử đơn năm 2026 của khách 42 đọc dưới 100 page, từ 4.608.
• Câu chỉ chạm partition của năm được chọn, không quét hai năm còn lại.
• Không còn key lookup: mọi cột câu cần nằm trên một chỉ mục.
• Giá phải trả đo được bằng page: chỉ mục lớn thêm bao nhiêu.
• Ngoài phạm vi: giá ghi đầy đủ của mỗi chỉ mục (bài 4.3), và kiểu tham số chuỗi (Kiểu tham số từ .NET làm truy vấn quét cả bảng).

3. Cơ sở lý thuyết: phủ cột, thứ tự khóa và điều kiện seek được #

Mốc vẫn là SQL Server 2019 và dbo.DonHang 10 triệu dòng phân vùng theo năm. Số trên BanHang là ước lượng theo công thức của Microsoft.

Chỉ mục phủ và INCLUDE #

Một chỉ mục phủ (covering) câu truy vấn khi mọi cột câu đó cần đều có trên chỉ mục, nên không còn key lookup. IX_DonHang_KhachHang (KhachHangId, NgayTao) đã phủ những câu chỉ cần KhachHangId, NgayTao và DonHangId, vì DonHangId có sẵn ở tầng lá dưới dạng row locator. Câu lịch sử đơn cần thêm TrangThai và TongTien.

INCLUDE thêm cột vào tầng lá, không thêm vào khóa. Cột INCLUDE không được sắp xếp và không nằm ở root hay tầng trung gian, nên không làm dòng tầng trên dài ra.

flowchart TB
  Q["Lịch sử đơn khách 42"] --> A["Seek IX_DonHang_KhachHang gốc"]
  A -->|"thiếu TrangThai, TongTien"| B["Key lookup vào PK_DonHang, 3 page mỗi dòng"]
  B --> R1["khoảng 900.900 lần đọc"]
  Q --> C["Seek IX_DonHang_KhachHang có INCLUDE"]
  C -->|"đủ cột ở tầng lá"| R2["khoảng 1.270 lần đọc"]

Thêm TrangThai (1 byte) và TongTien (9 byte) làm dòng lá thành 32 byte: 18 byte khóa và row locator, 10 byte hai cột mới, null bitmap 3 byte, header 1 byte. Tầng trên giữ 25 byte và 299 dòng mỗi page.

Con số 1.270 là 300.000 / 238 ≈ 1.261 page lá, cộng 2 page trên lá ở mỗi partition.

Thứ tự cột khóa #

Quy tắc: cột so sánh bằng (=, IN) đứng trước, rồi một cột khoảng hoặc cột sắp xếp. Sau cột khoảng, các cột khóa phía sau không thu hẹp được seek nữa; chúng chỉ còn là điều kiện lọc phụ (residual predicate) áp lên từng dòng trong khoảng.

PK_DonHang có NgayTao đứng đầu, nên chính nó cho thấy giá của thứ tự ngược. Câu tháng 9 của khách 42 đi theo PK_DonHang phải đọc mọi đơn tháng 9: khoảng 13.100 × 30 = 393.000 dòng, tức khoảng 1.803 page, rồi lọc KhachHangId từng dòng. 1.803 page so với 27 page.

"Cột chọn lọc nhất đứng đầu" vì vậy không phải quy tắc chính. NgayTao gần như duy nhất, chọn lọc hơn KhachHangId nhiều, nhưng đặt nó lên đầu làm mất phép so sánh bằng trên khách. Độ chọn lọc chỉ dùng để xếp thứ tự giữa các cột cùng là so sánh bằng. NgayTao đứng sau KhachHangId còn cho sẵn thứ tự của ORDER BY NgayTao; trên bảng phân vùng, mở kế hoạch thực tế và tìm toán tử Sort để chắc (Điều tra truy vấn chậm, phần 3).

Điều kiện seek được #

Một điều kiện dùng được để seek (sargable) khi cột đứng trần ở một vế, so với một biểu thức không phụ thuộc dòng, cùng kiểu dữ liệu với cột. Bọc cột trong hàm hoặc phép tính thì engine phải tính biểu thức cho từng dòng rồi mới so. Với DonHang, điều kiện bọc NgayTao còn làm mất khả năng bỏ qua partition, vì engine không biết DATEPART(year, NgayTao) = 2026 ứng với partition nào.

CAST(NgayTao AS date) = '20261001' là ngoại lệ: engine nhận ra phép đổi từ ngày giờ sang ngày giữ thứ tự, tự tính một khoảng và vẫn seek. Khoảng tự tính có thể rộng hơn cần, và ước lượng số dòng của dạng này kém hơn dạng khoảng. Cùng quy tắc áp cho LIKE '%042' và cho tham số khác kiểu cột; phần chuyển kiểu ngầm nằm ở Kiểu tham số từ .NET làm truy vấn quét cả bảng.

Trong kế hoạch thực tế, điều kiện dùng để định vị nằm ở mục Seek Predicates của toán tử, điều kiện chỉ để lọc nằm ở Predicate.

4. Cách giải quyết: sửa điều kiện năm, rồi thêm INCLUDE #

Chọn hai cách đầu, theo thứ tự. Sửa điều kiện là việc không tốn gì và đúng cho mọi câu lọc ngày. INCLUDE có giá ở phía ghi: TrangThai đổi khoảng 3 lần trong đời một đơn (Mới, Đã thanh toán, Đang giao, Hoàn tất). Với khoảng 13.100 đơn mỗi ngày năm 2026, đó là thêm khoảng 39.000 lần sửa dòng trên chỉ mục này mỗi ngày, chấp nhận được cho một màn hình mở nhiều lần mỗi ngày.

Các bước:

1. Đổi .Year == nam thành >= dauNam && < dauNamSau, kiểm câu SQL bằng ToQueryString().
2. Khai báo INCLUDE trong model EF Core để model và database khớp nhau.
3. Trên bảng lớn đang chạy, đổi chỉ mục bằng CREATE INDEX ... WITH (DROP_EXISTING = ON) viết tay, không để migration xóa rồi tạo lại.
4. Đo số page đọc và kích thước chỉ mục trước và sau.

5. Cách cài đặt: IncludeProperties, khoảng nửa mở và DROP_EXISTING #

Môi trường: LocalDB SQL Server 2019 (15.0.4382), .NET 10.0.401, Microsoft.EntityFrameworkCore.SqlServer 10.0.3. Model và câu truy vấn sau khi sửa:

modelBuilder.Entity<DonHang>()
    .HasIndex(d => new { d.KhachHangId, d.NgayTao }, "IX_DonHang_KhachHang")
    .IncludeProperties(d => new { d.TrangThai, d.TongTien });

// [d].[NgayTao] >= @dauNam AND [d].[NgayTao] < @dauNamSau: seek được, chỉ partition của năm đó
DateTime dauNam = new(nam, 1, 1), dauNamSau = new(nam + 1, 1, 1);
var don = await db.DonHang
    .Where(d => d.KhachHangId == khach && d.NgayTao >= dauNam && d.NgayTao < dauNamSau)
    .Select(d => new { d.DonHangId, d.NgayTao, d.TrangThai, d.TongTien })
    .ToListAsync();

EF Core 10.0.3 sinh CREATE INDEX [IX_DonHang_KhachHang] ON [DonHang] ([KhachHangId], [NgayTao]) INCLUDE ([TrangThai], [TongTien]);. Lệnh không ghi ON ps_DonHang_Ngay, nhưng SQL Server tự đặt chỉ mục vào partition scheme của bảng, nên vẫn aligned; đã kiểm bằng sys.indexes trên LocalDB. Lệnh cũng không có DROP_EXISTING, nên với chỉ mục đang chạy trên bảng lớn, viết tay lệnh dưới trong migrationBuilder.Sql(...):

CREATE INDEX IX_DonHang_KhachHang
    ON dbo.DonHang (KhachHangId, NgayTao)
    INCLUDE (TrangThai, TongTien)
    WITH (DROP_EXISTING = ON)
    ON ps_DonHang_Ngay (NgayTao);

DROP_EXISTING dựng lại chỉ mục từ đầu; trên bản Standard, lệnh giữ khóa bảng đến khi xong, còn Enterprise thêm được ONLINE = ON. Các phép tính khác trong series vẫn dùng định nghĩa gốc IX_DonHang_KhachHang (KhachHangId, NgayTao), và Điều tra truy vấn chậm, phần 3 đi qua quyết định này trên một sự cố thật.

Chương trình đo chạy ba dạng câu lịch sử đơn của khách 42: không lọc năm, lọc bằng .Year, lọc bằng khoảng nửa mở. Mỗi dạng chạy trên IX_DonHang_KhachHang gốc, rồi trên bản có INCLUDE, kèm kích thước chỉ mục. Cuối chương trình, chỉ mục được trả về định nghĩa gốc để các bài khác chạy lại được. Bảng thử là Kumeo_chimuc dựng ở bài đầu chương; đầu file có #:property PublishAot=false vì EF Core không dựng model lúc chạy khi bật PublishAot.

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.3
#:property PublishAot=false
// Lịch sử đơn năm 2026 của khách 42: điều kiện năm viết bằng .Year hay khoảng nửa mở,
// trên IX_DonHang_KhachHang gốc rồi trên bản có INCLUDE (TrangThai, TongTien).
// Chạy: dotnet run LichSuDon.cs   (cần database Kumeo_chimuc đã dựng ở bài đầu chương)
using System.Data.Common;
using System.Text.RegularExpressions;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

const string cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_chimuc;Integrated Security=true;TrustServerCertificate=true";
await using var db = new BanHangDb(cs);

// Lệnh tạo chỉ mục EF Core sinh ra từ model có IncludeProperties
foreach (var line in db.Database.GenerateCreateScript().Split('\n'))
    if (line.Contains("IX_DonHang_KhachHang")) Console.WriteLine(line.Trim());
Console.WriteLine();

var conn = (SqlConnection)db.Database.GetDbConnection();
long reads = 0;
conn.InfoMessage += (_, e) =>
{
    foreach (Match m in Regex.Matches(e.Message, @"logical reads (\d+)"))
        reads += long.Parse(m.Groups[1].Value);
};
await db.Database.OpenConnectionAsync();

int khach = 42, nam = 2026;
DateTime dauNam = new(nam, 1, 1), dauNamSau = new(nam + 1, 1, 1);
var cachViet = new (string Ten, Func<IQueryable<DonHang>> Q)[]
{
    ("Không lọc năm",                  () => db.DonHang.Where(d => d.KhachHangId == khach)),
    ("NgayTao.Year == nam",            () => db.DonHang.Where(d => d.KhachHangId == khach && d.NgayTao.Year == nam)),
    ("NgayTao >= dauNam && < dauNamSau", () => db.DonHang.Where(d => d.KhachHangId == khach && d.NgayTao >= dauNam && d.NgayTao < dauNamSau)),
};

await DoiChiMuc(include: false);   // bắt đầu từ định nghĩa gốc
await Do("IX_DonHang_KhachHang gốc");
await DoiChiMuc(include: true);
await Do("Có INCLUDE (TrangThai, TongTien)");
await DoiChiMuc(include: false);   // trả về định nghĩa gốc cho các bài khác

foreach (var (ten, q) in cachViet.Skip(1))
{
    var sql = q().ToQueryString();
    Console.WriteLine($"{ten,-34} WHERE {sql[(sql.IndexOf("WHERE") + 6)..].Trim()}");
}

async Task Do(string nhan)
{
    var trang = await db.Database.SqlQueryRaw<long>("""
        SELECT SUM(ps.used_page_count) AS Value
        FROM sys.dm_db_partition_stats AS ps
        JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
        WHERE i.object_id = OBJECT_ID(N'dbo.DonHang') AND i.name = N'IX_DonHang_KhachHang'
        """).SingleAsync();
    Console.WriteLine($"-- {nhan}: {trang:N0} page");
    await db.Database.ExecuteSqlRawAsync("SET STATISTICS IO ON");
    foreach (var (ten, q) in cachViet)
    {
        reads = 0;
        var dong = (await q().TagWith(nhan)
            .Select(d => new { d.DonHangId, d.NgayTao, d.TrangThai, d.TongTien })
            .ToListAsync()).Count;
        Console.WriteLine($"   {ten,-34} {dong,7:N0} dòng {reads,6:N0} logical reads");
    }
    await db.Database.ExecuteSqlRawAsync("SET STATISTICS IO OFF");
}

Task DoiChiMuc(bool include)
{
    var sql = $"""
        CREATE INDEX IX_DonHang_KhachHang ON dbo.DonHang (KhachHangId, NgayTao)
            {(include ? "INCLUDE (TrangThai, TongTien)" : "")}
            WITH (DROP_EXISTING = ON) ON ps_DonHang_Ngay (NgayTao);
        """;
    return db.Database.ExecuteSqlRawAsync(sql);
}

class DonHang
{
    public long DonHangId { get; set; }
    public DateTime NgayTao { get; set; }
    public int KhachHangId { get; set; }
    public byte TrangThai { get; set; }
    public decimal TongTien { get; set; }
}

class BanHangDb(string cs) : DbContext
{
    public DbSet<DonHang> DonHang => Set<DonHang>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) =>
        o.UseSqlServer(cs).AddInterceptors(new DocHetThongBao());
    protected override void OnModelCreating(ModelBuilder b)
    {
        var e = b.Entity<DonHang>();
        e.ToTable("DonHang");
        e.HasKey(d => new { d.NgayTao, d.DonHangId });
        e.Property(d => d.NgayTao).HasColumnType("datetime2(0)");
        e.Property(d => d.TongTien).HasColumnType("decimal(18, 2)");
        e.HasIndex(d => new { d.KhachHangId, d.NgayTao }, "IX_DonHang_KhachHang")
            .IncludeProperties(d => new { d.TrangThai, d.TongTien });
    }
}

// SqlClient chỉ phát InfoMessage của STATISTICS IO khi reader được đọc tới hết.
class DocHetThongBao : DbCommandInterceptor
{
    public override InterceptionResult DataReaderClosing(DbCommand c, DataReaderClosingEventData e, InterceptionResult r)
    {
        while (e.DataReader.NextResult()) { }
        return r;
    }
    public override async ValueTask<InterceptionResult> DataReaderClosingAsync(DbCommand c, DataReaderClosingEventData e, InterceptionResult r)
    {
        while (await e.DataReader.NextResultAsync()) { }
        return r;
    }
}

6. Chứng minh: 4.608 xuống 1.265, rồi xuống 47 lần đọc #

Đo trên LocalDB SQL Server 2019 (15.0.4382), .NET 10.0.401, EF Core 10.0.3, bảng thử 1 triệu đơn; khách 42 có 30.324 đơn, trong đó 8.268 đơn năm 2026. Số đọc lặp lại y hệt giữa các lần chạy.

CREATE INDEX [IX_DonHang_KhachHang] ON [DonHang] ([KhachHangId], [NgayTao]) INCLUDE ([TrangThai], [TongTien]);

-- IX_DonHang_KhachHang gốc: 2,986 page
   Không lọc năm                       30,324 dòng  4,608 logical reads
   NgayTao.Year == nam                  8,268 dòng  4,608 logical reads
   NgayTao >= dauNam && < dauNamSau     8,268 dòng  1,265 logical reads
-- Có INCLUDE (TrangThai, TongTien): 4,225 page
   Không lọc năm                       30,324 dòng    139 logical reads
   NgayTao.Year == nam                  8,268 dòng    139 logical reads
   NgayTao >= dauNam && < dauNamSau     8,268 dòng     47 logical reads
NgayTao.Year == nam                WHERE [d].[KhachHangId] = @khach AND DATEPART(year, [d].[NgayTao]) = @nam
NgayTao >= dauNam && < dauNamSau   WHERE [d].[KhachHangId] = @khach AND [d].[NgayTao] >= @dauNam AND [d].[NgayTao] < @dauNamSau

So với tiêu chí ở mục 2:

• Dưới 100 page. Hai sửa đổi cùng nhau đưa câu từ 4.608 xuống 47 lần đọc, gấp khoảng 98 lần.
• Chỉ chạm partition của năm. Với chỉ mục gốc, khoảng nửa mở đọc 1.265 page: chỉ partition 2026 của PK_DonHang, 272.636 dòng, tức khoảng 272.636 / 218 ≈ 1.251 page lá. .Year đọc 4.608, cả ba partition.
• Không còn key lookup. Có INCLUDE, câu không lọc năm đọc 139 page cho 30.324 đơn, tức khoảng 30.324 / 238 page lá cộng các page trên, thay vì quét cả bảng. Với .Year, câu vẫn đọc đủ 139 page vì DATEPART chỉ là điều kiện phụ trên mọi đơn của khách; khoảng nửa mở đọc 47 vì seek dừng ở đơn năm 2026.
• Giá phải trả. Chỉ mục từ 2.986 lên 4.225 page, thêm 41%, khớp phép tính 29.676 → 42.019 page lá trên BanHang ở mục 3.

Chưa đo ở đây: thời gian ghi thêm khi TrangThai đổi trên chỉ mục rộng hơn. Bài chi phí ghi và chỉ mục thừa đo phía ghi.

7. Kết luận #

Câu chậm thường cần hai sửa đổi cùng lúc: điều kiện viết sao cho seek được, và chỉ mục chứa đủ cột câu cần. Sửa điều kiện không tốn gì; INCLUDE đổi key lookup lấy dung lượng và công ghi, nên chỉ dành cho câu chạy nhiều.

Trong dự án .NET của bạn:

• Điều kiện ngày trong LINQ viết >= tu && < den; tránh .Year, .Month, .Date trên cột có chỉ mục. Kiểm câu SQL bằng ToQueryString().
• Khai báo chỉ mục phủ bằng HasIndex(...).IncludeProperties(...) cho câu chạy nhiều, ghi tên câu đó ngay cạnh khai báo.
• Đổi chỉ mục đang chạy trên bảng lớn bằng CREATE INDEX ... WITH (DROP_EXISTING = ON) viết tay trong migrationBuilder.Sql.
• Đo used_page_count của chỉ mục trước và sau khi thêm INCLUDE, và đo câu trên khách lớn nhất.

Những chỗ hay hiểu sai #

• "Cột chọn lọc nhất đặt đầu." Cột so sánh bằng đặt đầu. Độ chọn lọc chỉ xếp thứ tự giữa các cột cùng là so sánh bằng.
• "INCLUDE thêm cột vào mọi tầng của cây." INCLUDE chỉ nằm ở tầng lá. Root và tầng trung gian giữ nguyên.
• "Có chỉ mục phủ thì cách viết điều kiện không quan trọng." Có INCLUDE, .Year vẫn đọc 139 page thay vì 47, vì nó đọc mọi đơn của khách.

Đọc tiếp #

• Chỉ mục B-tree và key lookup: bài trước, độ sâu cây và giá của key lookup.
• Chỉ mục lọc cho đơn đang mở: bài sau, chỉ mục chỉ chứa 1% bảng.
• Kiểu tham số từ .NET làm truy vấn quét cả bảng: chuyển kiểu ngầm từ tham số nvarchar.
• Điều tra truy vấn chậm, phần 3: thêm INCLUDE trên một sự cố thật, kế hoạch TOP qua partition.

Nguồn #

• Index architecture and design guide
• Estimate the size of a nonclustered index
• Partitioned tables and indexes
• Rob Farley — Beware the width of the covering range
• EF Core — Index features specific to SQL Server
• EF Core — Function mappings of the SQL Server provider