SQL và Cơ sở dữ liệu quan hệ: Nền tảng quan trọng trong Lộ trình .NET

Entity Framework Core sinh SQL thay bạn, nhưng nó không sinh index, không đọc execution plan, và không biết rằng WHERE Code = @p đang so sánh varchar với nvarchar nên index bị bỏ qua. SQL và cơ sở dữ liệu quan hệ vẫn là nền tảng bắt buộc trong lộ trình .NET, kể cả khi bạn không viết một câu SELECT nào bằng tay.

Bài này đủ để bạn đọc truy vấn của ORM, thêm đúng index, hiểu vì sao transaction treo, và biết khi nào denormalize là quyết định đúng.

Cập nhật 2026

  • SQL Server 2025 có kiểu json native và kiểu vector. EF Core 10 map complex type sang cột JSON bằng ToJson(), và hỗ trợ vector search qua SqlVector<float> cùng EF.Functions.VectorDistance.
  • EF Core 10 là bản LTS: migration bundle, script idempotent, và ExecuteUpdateAsync chạy trực tiếp xuống database, không qua change tracker.
  • PostgreSQL 17/18 vẫn là lựa chọn mặc định cho team muốn jsonb, GIN index và pgvector; cú pháp EXPLAIN (ANALYZE, BUFFERS) là công cụ đọc plan chuẩn.
  • Phần lớn lỗi production nằm ở index, isolation level và N+1 — trọng tâm dưới đây.

Mô hình quan hệ: nghĩ theo tập, không theo vòng lặp

SQL là ngôn ngữ declarative: bạn mô tả kết quả, optimizer chọn cách thực hiện. Điều này có hệ quả thực dụng — viết truy vấn theo kiểu “duyệt từng bản ghi rồi hỏi thêm” thường là dấu hiệu bạn đang dùng database như một hash map từ xa, và trả giá bằng round trip.

Schema tối thiểu dùng trong bài:

CREATE TABLE dbo.Customers (
    CustomerId  int IDENTITY PRIMARY KEY,
    Email       nvarchar(256) NOT NULL,
    DisplayName nvarchar(100) NOT NULL,
    CreatedAt   datetime2(0)  NOT NULL CONSTRAINT DF_Customers_Created DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_Customers_Email UNIQUE (Email)
);

CREATE TABLE dbo.Orders (
    OrderId    bigint IDENTITY PRIMARY KEY,
    CustomerId int           NOT NULL REFERENCES dbo.Customers(CustomerId),
    Status     tinyint       NOT NULL,
    Total      decimal(18,2) NOT NULL,
    CreatedAt  datetime2(0)  NOT NULL
);

Trên PostgreSQL, thay IDENTITY bằng GENERATED ALWAYS AS IDENTITY và nvarchar bằng text; phần còn lại của bài giữ nguyên logic.

JOIN và GROUP BY: hai lỗi hay gặp

SELECT c.DisplayName,
       COUNT(o.OrderId)              AS OrderCount,
       COALESCE(SUM(o.Total), 0)     AS Revenue
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
       ON o.CustomerId = c.CustomerId
      AND o.CreatedAt >= '2026-01-01'
WHERE c.CreatedAt >= '2024-01-01'
GROUP BY c.CustomerId, c.DisplayName
HAVING COUNT(o.OrderId) >= 2
ORDER BY Revenue DESC;

Lỗi thứ nhất: đặt điều kiện của bảng bên phải trong WHERE thay vì ON. Với LEFT JOIN, WHERE o.CreatedAt >= ... loại bỏ các dòng có o là NULL — truy vấn âm thầm trở thành INNER JOIN. Điều kiện lọc bảng phải nằm trong ON; điều kiện lọc bảng trái nằm trong WHERE.

Lỗi thứ hai: nhầm WHERE với HAVING. WHERE lọc trước khi gom nhóm, HAVING lọc sau khi gom nhóm và có thể dùng aggregate. Đặt COUNT(...) vào WHERE sẽ báo lỗi, còn đặt điều kiện không aggregate vào HAVING thì chạy được nhưng chậm hơn vì gom nhóm trước rồi mới lọc.

Khi cần “đơn hàng mới nhất của mỗi khách”, window function gọn hơn correlated subquery:

WITH Ranked AS (
    SELECT o.OrderId, o.CustomerId, o.Total, o.CreatedAt,
           ROW_NUMBER() OVER (PARTITION BY o.CustomerId ORDER BY o.CreatedAt DESC) AS rn
    FROM dbo.Orders AS o
)
SELECT CustomerId, OrderId, Total, CreatedAt
FROM Ranked
WHERE rn = 1;

Index: cấu trúc, thứ tự cột và covering

Index B-tree trong SQL Server và PostgreSQL đều là cây cân bằng: tra cứu O(log n), duyệt range hiệu quả vì các lá nằm theo thứ tự khóa. Khác biệt quan trọng nhất với người viết truy vấn là clustered index (SQL Server): bảng được lưu vật lý theo khóa clustered, thường là primary key. Mọi nonclustered index đều trỏ tới clustered key, nên clustered key càng rộng thì index phụ càng lớn.

Ba quy tắc đủ để giải quyết phần lớn trường hợp:

  • Thứ tự cột trong composite index quyết định index có dùng được hay không. Index (CustomerId, CreatedAt) phục vụ truy vấn lọc theo CustomerId, hoặc CustomerId + range CreatedAt. Nó không phục vụ truy vấn chỉ lọc CreatedAt. Đây là “leftmost prefix” — cột equality đứng trước, cột range đứng sau.
  • Covering index tránh key lookup. Nếu truy vấn chỉ cần vài cột ngoài khóa, đưa chúng vào INCLUDE để index chứa đủ dữ liệu.
  • Foreign key không tự động có index. Cả SQL Server và PostgreSQL đều không tạo index cho cột FK. Thiếu index này làm cả truy vấn join lẫn DELETE trên bảng cha chậm.
-- SQL Server: seek theo CustomerId, sort sẵn theo CreatedAt, cover cột Total
CREATE INDEX IX_Orders_Customer_CreatedAt
    ON dbo.Orders (CustomerId, CreatedAt DESC)
    INCLUDE (Total, Status);

-- PostgreSQL: cùng ý tưởng
CREATE INDEX ix_orders_customer_created_at
    ON orders (customer_id, created_at DESC)
    INCLUDE (total, status);

Mỗi index thêm chi phí ghi cho INSERT/UPDATE/DELETE; chỉ thêm khi có plan chứng minh.

Hai biến thể nên biết: filtered index (SQL Server) và partial index (PostgreSQL) chỉ index một phần bảng, rất hữu ích cho cột có nhiều NULL hoặc trạng thái hiếm:

CREATE INDEX IX_Orders_Pending
    ON dbo.Orders (CreatedAt)
    WHERE Status = 0;   -- chỉ đơn hàng đang chờ

Đọc execution plan thay vì đánh giá bằng cảm giác

SQL Server: bật actual execution plan trong SSMS, hoặc dùng thống kê IO để biết truy vấn đọc bao nhiêu page:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT CustomerId, COUNT(*) FROM dbo.Orders WHERE CreatedAt >= '2026-01-01' GROUP BY CustomerId;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, COUNT(*) FROM orders WHERE created_at >= '2026-01-01' GROUP BY customer_id;

Đọc plan theo thứ tự sau:

  1. Scan hay Seek? Table Scan/Seq Scan trên bảng lớn với predicate chọn ít dòng là dấu hiệu thiếu index hoặc index không dùng được.
  2. Estimated rows so với actual rows. Lệch vài bậc độ lớn nghĩa là statistics cũ hoặc predicate không sargable. Cập nhật statistics (UPDATE STATISTICS, ANALYZE) trước khi kết luận.
  3. Key lookup / RID lookup. Nhiều lookup nghĩa là index thiếu cột cover — thêm INCLUDE.
  4. Sort và spill. Sort warning hoặc external merge disk trong PostgreSQL nghĩa là work_mem không đủ; đôi khi sửa bằng index có thứ tự đúng.
  5. Implicit conversion. SQL Server hiển thị CONVERT_IMPLICIT khi so sánh varchar với nvarchar. Index trên cột varchar sẽ không seek được. Đây là lỗi cực phổ biến khi cột code là varchar còn tham số EF Core gửi xuống là nvarchar.

Transaction và isolation: cái giá của “đọc thấy dữ liệu mới nhất”

ACID không phải một công tắc. Mỗi isolation level đổi một phần tính chất để lấy concurrency. Hiểu mức nào đang chạy là điều kiện để debug deadlock và lost update.

Mức Hành vi thực tế Khi nào
Read Committed (mặc định SQL Server) Đọc không thấy dữ liệu chưa commit; dùng lock chia sẻ nên writer có thể bị block Mặc định của hầu hết ứng dụng OLTP
Read Committed Snapshot (RCSI) Đọc lấy bản đã commit tại thời điểm câu lệnh bắt đầu, không block writer Bật khi đọc nhiều, ghi vừa; tốn version store trong tempdb
Snapshot Cả transaction thấy một ảnh nhất quán Báo cáo, đọc nhiều bảng cần nhất quán
Serializable Như tuần tự; PostgreSQL dùng SSI và có thể abort Nghiệp vụ tài chính, tồn kho; phải retry

SQL Server mặc định là Read Committed có lock. Nhiều team tưởng “đọc không block” là mặc định vì database của họ đã bật RCSI:

ALTER DATABASE Shop
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

PostgreSQL mặc định Read Committed nhưng triển khai bằng MVCC: reader không block writer và writer không block reader. Cái giá là VACUUM và bloat — một chi tiết vận hành, không phải chi tiết cú pháp.

Lost update: đừng đọc rồi ghi

-- Sai: hai request cùng đọc 10, cùng ghi 9
UPDATE dbo.Products SET Stock = 9 WHERE ProductId = 42;

-- Đúng: điều kiện nằm trong chính câu UPDATE, atomic
UPDATE dbo.Products
SET Stock = Stock - 1
WHERE ProductId = 42 AND Stock >= 1;

Nếu logic phức tạp hơn một phép trừ, dùng optimistic concurrency với rowversion trong SQL Server hoặc xmin trong PostgreSQL, và xử lý DbUpdateConcurrencyException bằng cách tải lại rồi thử lại. Với EF Core:

// File: Data/Product.cs
public sealed class Product
{
    public int Id { get; set; }
    public int Stock { get; set; }
    public byte[] RowVersion { get; set; } = [];   // rowversion, map IsRowVersion()
}

EF Core thêm WHERE RowVersion = @old vào câu UPDATE; nếu 0 dòng bị ảnh hưởng, nó ném exception thay vì ghi đè. Đây là cách rẻ nhất để tránh lost update mà không cần khóa bi quan.

Deadlock

Deadlock xảy ra khi hai transaction giữ lock theo thứ tự ngược nhau. SQL Server chọn một victim và trả lỗi 1205; PostgreSQL abort với SQLSTATE 40P01. Không có cấu hình nào loại bỏ hoàn toàn deadlock — chỉ giảm xác suất và retry đúng.

  • Giữ transaction ngắn: không gọi HTTP, không gửi email, không chờ người dùng trong lúc transaction mở.
  • Thứ tự truy cập nhất quán: luôn cập nhật bảng A rồi B, không có ngoại lệ.
  • Index đúng predicate: scan toàn bảng trong transaction giữ nhiều lock hơn seek.
  • Retry với backoff: bắt đúng mã lỗi, không retry mọi DbException.

EnableRetryOnFailure của EF Core được thiết kế cho lỗi transient (mất kết nối, timeout), không phải để che logic deadlock. Nếu bạn muốn retry deadlock, viết execution strategy riêng hoặc bắt lỗi ở tầng repository, và đảm bảo thao tác là idempotent.

SQL injection: parameter hóa, không nối chuỗi

// ĐÚNG: tham số hóa
var customer = await db.Customers
    .FromSqlInterpolated($"SELECT * FROM dbo.Customers WHERE Email = {email}")
    .SingleOrDefaultAsync(ct);

// ĐÚNG với Dapper
var rows = await connection.QueryAsync<Customer>(
    "SELECT * FROM dbo.Customers WHERE Email = @Email", new { Email = email });

// SAI: nối chuỗi, đầu vào do người dùng kiểm soát
var sql = $"SELECT * FROM dbo.Customers WHERE Email = '{email}'";
await db.Database.ExecuteSqlRawAsync(sql);

FromSqlInterpolated biến nội suy thành DbParameter. FromSqlRaw chỉ an toàn khi bạn tự truyền SqlParameter hoặc khi chuỗi là hằng số. Tên bảng, tên cột và ORDER BY không thể parameter hóa — nếu chúng đến từ input, dùng whitelist, tuyệt đối không nối chuỗi.

ORM giúp mặc định an toàn, nhưng không cứu được ExecuteSqlRaw với string ghép. Quy tắc review: bất kỳ Raw nào có dấu $ hoặc + đều phải giải trình.

Chuẩn hóa hay denormalize?

Mặc định là 3NF: mỗi fact nằm một chỗ, cập nhật một chỗ. Denormalize khi read pattern rõ ràng và write pattern chấp nhận được:

  • Đúng: thêm cột OrderTotal đã tính sẵn vào bảng Orders để tránh join 3 bảng mỗi lần hiển thị danh sách. Write path cập nhật cùng transaction, có test.
  • Đúng: materialized view (PostgreSQL) hoặc indexed view (SQL Server) cho báo cáo, refresh theo lịch.
  • Sai: copy CustomerName vào Orders mà không có trigger/job đồng bộ, rồi tranh luận vì sao báo cáo lệch.

Quyết định dựa trên tỉ lệ đọc/ghi và yêu cầu nhất quán, không dựa trên “bảng này nhìn đẹp hơn”. Denormalize là nợ kỹ thuật có lãi suất: nếu không trả, dữ liệu sẽ lệch âm thầm.

Migration: sinh script, review, rồi mới chạy

dotnet ef migrations add AddOrdersCustomerIndex --project src/Shop.Data
dotnet ef migrations script --idempotent --output sql/2026-11-orders-index.sql
dotnet ef database update --project src/Shop.Data

Ba quy tắc vận hành:

  1. Review SQL sinh ra, không chỉ review file C#. EF Core có thể sinh DROP/ADD cho rename cột, và trên bảng lớn điều đó nghĩa là mất dữ liệu hoặc khóa lâu.
  2. Không chạy migration tự động lúc khởi động khi có nhiều instance. Hai pod cùng chạy Migrate() sẽ đánh nhau; dùng job deploy riêng, chạy một lần.
  3. Expand-contract cho thay đổi phá vỡ. Thêm cột nullable → backfill theo batch → deploy code đọc/ghi cột mới → sau vài ngày mới xóa cột cũ. Đây là cách duy nhất để zero-downtime mà không cần cửa sổ bảo trì.

Trên bảng lớn, thêm cột và tạo index có thể khóa ghi; đo trên staging với dữ liệu cùng kích thước.

Chọn hệ quản trị: bảng quyết định ngắn

Bối cảnh Lựa chọn Lưu ý
Stack Microsoft, cần json/vector native, tooling quen SQL Server 2025 Licensing; RCSI và isolation cần hiểu rõ
Open source, jsonb, extension, triển khai Linux PostgreSQL 17/18 VACUUM/bloat; connection pooling với PgBouncer
Test, desktop, mobile, dữ liệu một file SQLite Một writer; không dùng cho tải ghi đồng thời cao

Sai lầm thường gặp

  • SELECT * trong production. Kéo thêm cột phá covering index và làm payload phình ra; liệt kê cột cụ thể.
  • So sánh cột với hàm. WHERE YEAR(CreatedAt) = 2026 không sargable; viết range >= '2026-01-01' AND < '2027-01-01'.
  • Transaction bao quanh lời gọi HTTP. Giữ lock trong khi chờ mạng là công thức deadlock và timeout.
  • Chạy migration tự động ở startup với nhiều instance. Dùng job riêng; xem mục trên.

2027: vector và JSON thành kỹ năng cơ bản

EF Core 11 đi cùng .NET 11 (GA tháng 11/2026, STS) và tiếp tục mở rộng truy vấn vector, JSON. Điều không đổi: index, plan và isolation vẫn quyết định ứng dụng chạy nhanh hay treo.

Checklist

  1. Bật logging SQL của EF Core ở môi trường dev và đếm query cho một request điển hình.
  2. Mỗi bảng có FK đều có index trên cột FK; kiểm tra bằng query system view, không bằng mắt.
  3. Đọc plan của 5 truy vấn chậm nhất trước khi thêm bất kỳ index nào.
  4. Biết database đang chạy isolation nào; nếu là SQL Server, xác nhận RCSI bật hay tắt.
  5. Mọi UPDATE nghiệp vụ có điều kiện trong câu lệnh hoặc rowversion, không đọc-rồi-ghi.
  6. Migration chạy bằng script review được, không tự động ở startup khi có nhiều instance.

Đi tiếp: Chọn cơ sở dữ liệu phù hợp: SQL Server, PostgreSQL hay MySQL mở rộng bảng quyết định ở trên. Làm chủ EF Core Migrations đi sâu vào expand-contract và bundle. Khi đã hiểu index và plan, bài Stored Procedures, Constraints & Triggers là bước tiếp theo về phía database.