12.7 - Stored Procedures và Views

Tóm tắt: Hai hiểu lầm phổ biến nhất về chủ đề này. "Stored procedure an toàn với SQL injection" — sai: một SP nối chuỗi vào dynamic SQL vẫn bị injection y như code ứng dụng; thứ bảo vệ bạn là tham số hoá, không phải nơi câu lệnh được lưu. "View giúp truy vấn nhanh hơn" — cũng sai: view thường chỉ là một định nghĩa được đặt tên, nội tuyến vào truy vấn chính, và view lồng view ba tầng tạo ra những plan không ai đoán được. Thứ thật sự lưu kết quả là indexed view (SQL Server) hoặc materialized view (PostgreSQL) — và chúng có ràng buộc riêng khá chặt.

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

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

Quyết định khi nào dùng stored procedure và khi nào không.
Viết SP an toàn, kể cả khi cần dynamic SQL.
Dùng view đúng mục đích và tránh view lồng nhau.
Chọn giữa indexed view và materialized view.
Đặt logic nghiệp vụ đúng tầng.

Nội dung bài học

12.7.1 — Khi nào dùng stored procedure

Nghiêng về SP	Nghiêng về ORM / SQL trong ứng dụng
Xử lý theo lô rất lớn, cần ở gần dữ liệu	CRUD thông thường
Nhiều hệ thống dùng chung một database	Một ứng dụng sở hữu database
DBA kiểm soát, không lộ schema	Team nhỏ, không có DBA
Hệ thống cũ đã có sẵn SP	Cần chuyển đổi hệ quản trị về sau
Cần quyền ở mức thủ tục, không ở mức bảng	Logic đổi thường xuyên

Lý do mạnh nhất để dùng SP là hàng đầu tiên: xử lý 10 triệu dòng thì để nó chạy trong database, thay vì kéo dữ liệu về ứng dụng rồi đẩy ngược lại. Một UPDATE theo lô trong SP nhanh hơn nhiều lần so với vòng lặp ở tầng ứng dụng.

Lý do phổ biến nhất nhưng yếu nhất là "SP nhanh hơn vì có plan được cache". Từ SQL Server 2005, truy vấn tham số hoá từ ứng dụng cũng được cache plan y hệt. Khác biệt hiệu năng gần như không còn.

Ba cái giá của SP:

Logic nằm ngoài kiểm soát mã nguồn trừ khi bạn chủ động quản lý — và rất nhiều team không làm.
Khó test. Không mock được, phải có database thật.
Khó refactor. Không có "tìm mọi nơi gọi thủ tục này".

Cái giá thứ nhất là nghiêm trọng nhất: "ai đó sửa SP trên production để chữa cháy" là kịch bản có thật, và không ai biết cho tới lần deploy sau ghi đè lên.

Nếu dùng SP, hãy quản lý chúng như code: file .sql trong repo, áp dụng qua migration (bài 12.8).

12.7.2 — SP không tự an toàn

sql

-- BỊ SQL INJECTION dù là stored procedure
CREATE PROCEDURE sp_SearchBad @Column NVARCHAR(50), @Value NVARCHAR(100) AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT * FROM Leads WHERE ' + @Column + N' = ''' + @Value + N'''';
    EXEC(@sql);                           -- NOI CHUOI -> injection
END;

Gọi với @Value = "' OR 1=1 --" và bạn lấy được toàn bộ bảng. Việc câu lệnh nằm trong SP không bảo vệ gì cả.

sql

-- AN TOÀN — tham số hoá, và whitelist cho tên cột
CREATE OR ALTER PROCEDURE sp_SearchLeads
    @Column NVARCHAR(50), @Value NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- Tên cột KHÔNG tham số hoá được -> phải whitelist
    IF @Column NOT IN ('Status', 'CompanyName', 'Source')
        THROW 50001, 'Cột không hợp lệ', 1;

    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT LeadId, CompanyName, Status FROM Leads WHERE '
        + QUOTENAME(@Column) + N' = @Value';

    EXEC sp_executesql @sql, N'@Value NVARCHAR(100)', @Value;
END;

Hai kỹ thuật kết hợp:

sp_executesql với tham số cho giá trị — an toàn tuyệt đối, và cho phép cache plan.
Whitelist cộng QUOTENAME cho tên đối tượng — vì tên bảng và tên cột không thể tham số hoá.

QUOTENAME một mình không đủ: nó bọc tên trong ngoặc vuông và escape đúng cách, nhưng vẫn cho phép truy cập những cột bạn không muốn lộ. Whitelist mới là lớp kiểm soát thật.

Mẫu tham số tuỳ chọn thì quay lại vấn đề catch-all ở bài 12.5:

sql

CREATE OR ALTER PROCEDURE sp_SearchLeadsFiltered
    @Status NVARCHAR(50) = NULL, @AssignedUserId INT = NULL,
    @PageNumber INT = 1, @PageSize INT = 20
AS
BEGIN
    SET NOCOUNT ON;

    IF @PageSize > 100 SET @PageSize = 100;         -- trần cứng

    SELECT  l.LeadId, l.CompanyName, l.Status, l.EstimatedValue, u.FullName
    FROM    Leads l
    LEFT JOIN Users u ON l.AssignedToUserId = u.UserId
    WHERE   (@Status IS NULL OR l.Status = @Status)
      AND   (@AssignedUserId IS NULL OR l.AssignedToUserId = @AssignedUserId)
    ORDER   BY l.CreatedAt DESC, l.LeadId DESC      -- tie-breaker
    OFFSET  (@PageNumber - 1) * @PageSize ROWS
    FETCH   NEXT @PageSize ROWS ONLY
    OPTION  (RECOMPILE);                            -- BAT BUOC cho catch-all
END;

Ba chi tiết: SET NOCOUNT ON bỏ thông báo "n rows affected" tốn một lượt đi về mạng cho mỗi câu lệnh; trần cứng cho @PageSize (bài 9.4); và tie-breaker trong ORDER BY để phân trang ổn định.

12.7.3 — View: đặt tên, không tăng tốc

sql

CREATE OR ALTER VIEW vw_LeadSummary AS
SELECT  l.LeadId, l.CompanyName, l.Status, l.EstimatedValue, l.CreatedAt,
        u.FullName AS SalesRep,
        DATEDIFF(DAY, l.CreatedAt, SYSUTCDATETIME()) AS AgeInDays
FROM    Leads l
LEFT JOIN Users u ON l.AssignedToUserId = u.UserId;

View không lưu dữ liệu — nó là một định nghĩa truy vấn được đặt tên, và optimizer nội tuyến nó vào truy vấn chính. Dùng view không nhanh hơn viết thẳng truy vấn đó.

Giá trị thật của view:

Che giấu độ phức tạp — một JOIN 5 bảng thành một tên.
Ổn định hợp đồng — đổi cấu trúc bảng mà không phá code, nếu view giữ nguyên hình dạng.
Phân quyền — cấp quyền trên view mà không cấp quyền trên bảng gốc.

Điểm 3 mạnh hơn nhiều người nghĩ:

sql

CREATE VIEW vw_LeadsPublic AS
SELECT LeadId, CompanyName, Status FROM Leads;       -- không có EstimatedValue

GRANT SELECT ON vw_LeadsPublic TO ReportingRole;     -- KHÔNG cấp quyền trên Leads

Cái giá lớn nhất là view lồng view:

sql

CREATE VIEW vw_A AS SELECT ... FROM BigTable;
CREATE VIEW vw_B AS SELECT ... FROM vw_A JOIN Other;
CREATE VIEW vw_C AS SELECT ... FROM vw_B JOIN Another;

SELECT * FROM vw_C WHERE Id = 42;     -- co the quet CA BigTable

Optimizer phải mở rộng toàn bộ chuỗi, và nó không phải lúc nào cũng đẩy được điều kiện lọc xuống tầng dưới cùng. Một truy vấn trông như lấy một dòng có thể quét vài bảng lớn.

Quy tắc thực dụng: một tầng view, không lồng. Cần phức tạp hơn thì viết truy vấn thật hoặc dùng materialized view.

DATEDIFF(...) AS AgeInDays trong ví dụ trên còn một vấn đề nữa: truy vấn WHERE AgeInDays > 30 không SARGable (bài 12.4). Viết WHERE CreatedAt < DATEADD(DAY, -30, SYSUTCDATETIME()) trên bảng gốc thì mới dùng được index.

12.7.4 — Indexed view và materialized view

sql

-- SQL Server: indexed view — CẬP NHẬT ĐỒNG BỘ
CREATE VIEW vw_CustomerLeadCount
WITH SCHEMABINDING                       -- BAT BUOC
AS
SELECT  c.CustomerId, COUNT_BIG(*) AS LeadCount    -- phai la COUNT_BIG
FROM    dbo.Customers c
JOIN    dbo.Leads l ON l.ConvertedToCustomerId = c.CustomerId
GROUP   BY c.CustomerId;

CREATE UNIQUE CLUSTERED INDEX IX_vw_CustomerLeadCount
    ON vw_CustomerLeadCount (CustomerId);          -- lúc này mới LƯU dữ liệu

Ràng buộc của indexed view khá chặt: bắt buộc SCHEMABINDING, tên hai phần (dbo.), COUNT_BIG thay COUNT, không OUTER JOIN, không subquery, không hàm không xác định.

Đánh đổi quan trọng nhất: indexed view được cập nhật đồng bộ trong cùng transaction với bảng gốc. Mọi INSERT vào Leads phải cập nhật view — nên nó làm ghi chậm hơn để đổi lấy đọc nhanh.

sql

-- PostgreSQL: materialized view — CẬP NHẬT THỦ CÔNG
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT  DATE_TRUNC('month', converted_at) AS month,
        SUM(estimated_value) AS revenue,
        COUNT(*) AS deals_closed
FROM    leads
WHERE   status = 'Won'
GROUP   BY DATE_TRUNC('month', converted_at);

CREATE UNIQUE INDEX ON mv_monthly_revenue (month);   -- CAN cho CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

	Indexed view (SQL Server)	Materialized view (PostgreSQL)
Cập nhật	Tự động, đồng bộ	Thủ công hoặc theo lịch
Dữ liệu	Luôn mới	Cũ tới lần refresh sau
Tác động lên ghi	Làm chậm mọi `INSERT`/`UPDATE`	Không
Ràng buộc	Rất chặt	Gần như không

CONCURRENTLY cho phép đọc trong lúc refresh, nhưng bắt buộc có unique index và chậm hơn refresh thường.

Chọn thế nào: dữ liệu phải luôn mới → indexed view; báo cáo chấp nhận cũ vài phút → materialized view với job refresh. Cái sau đúng cho hầu hết bảng điều khiển, và nó không làm chậm đường ghi.

Khác biệt nằm ở thời điểm view được cập nhật so với lệnh ghi:

12.7.5 — Logic nghiệp vụ đặt ở đâu

sql

-- Nen tranh: quy tac nghiep vu trong SP
IF @EstimatedValue > 500000000 AND @CustomerTier = 'Bronze'
    THROW 50002, 'Khách hàng Bronze không được tạo deal trên 500 triệu', 1;

Quy tắc này ở trong database thì: không test được bằng unit test, không tái sử dụng được từ background job hay một service khác, không xuất hiện trong code review bình thường, và không ai tìm thấy khi đọc code ứng dụng.

Thuộc về database	Thuộc về ứng dụng
Toàn vẹn dữ liệu (constraint, FK)	Quy tắc nghiệp vụ
Tính toán trên tập lớn	Điều phối luồng
Bảo mật ở mức dòng, mức cột	Phân quyền theo vai trò
Audit ở mức dữ liệu	Validation đầu vào

Ranh giới: database bảo vệ dữ liệu không bao giờ được sai; ứng dụng chứa quy tắc có thể thay đổi theo yêu cầu kinh doanh.

"Email phải duy nhất" là toàn vẹn dữ liệu — thuộc về database. "Khách hàng Bronze không được tạo deal lớn" là quy tắc kinh doanh có thể đổi tháng sau — thuộc về ứng dụng (bài 8.5).

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

Danh sách rà soát SP và view

Mọi dynamic SQL dùng sp_executesql với tham số, không nối chuỗi giá trị.
Tên bảng và cột trong dynamic SQL đi qua whitelist, không chỉ QUOTENAME.
Stored procedure được quản lý trong mã nguồn và áp dụng qua migration.
Mọi SP có SET NOCOUNT ON.
SP có tham số tuỳ chọn dùng OPTION (RECOMPILE).
SP phân trang có trần cứng cho pageSize và tie-breaker trong ORDER BY.
Không có view nào lồng quá một tầng.
Không lọc theo cột tính toán của view; lọc trên cột gốc của bảng.
Indexed view chỉ dùng khi dữ liệu bắt buộc phải luôn mới.
Materialized view có job refresh theo lịch và unique index nếu dùng CONCURRENTLY.
Quy tắc nghiệp vụ có thể thay đổi nằm ở tầng ứng dụng, không nằm trong SP.

Bài tập áp dụng

Bài 1 — Stored procedure vẫn bị SQL injection

Viết SP nối chuỗi vào EXEC, gọi nó với ' OR 1=1 -- và xác nhận bạn lấy được toàn bộ bảng. Chuyển sang sp_executesql và kiểm chứng.

Tiêu chí hoàn thành: bạn nêu được vì sao niềm tin "dùng stored procedure là an toàn" lại sai, và điều gì mới thật sự quyết định an toàn.

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

Gợi ý. Cái bảo vệ bạn không phải là nơi câu lệnh được viết, mà là cách giá trị đi vào câu lệnh.

Lời giải — SP dễ tổn thương:

sql

CREATE PROCEDURE SearchLeadsBad @Name NVARCHAR(200)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT Id, Name, Value FROM Leads WHERE Name = N''' + @Name + N'''';
    EXEC(@sql);                       -- NỐI CHUỖI
END

sql

EXEC SearchLeadsBad @Name = N''' OR 1=1 --';

Câu lệnh thực tế được thực thi:

sql

SELECT Id, Name, Value FROM Leads WHERE Name = N'' OR 1=1 --'

text

(1000000 rows affected)          <- TOÀN BỘ bảng

Và không dừng ở đó:

sql

EXEC SearchLeadsBad @Name = N'''; DROP TABLE Leads; --';

Câu trả lời cho tiêu chí. Niềm tin "stored procedure an toàn trước injection" đến từ một quan sát đúng nhưng bị hiểu sai: SP viết theo cách thông thường — câu lệnh tĩnh, tham số là tham số — thì thật sự an toàn.

Nhưng cái làm nên an toàn ở đó không phải là "nó nằm trong SP". Nó là việc giá trị đi vào câu lệnh dưới dạng tham số, nên database luôn coi giá trị đó là dữ liệu, không bao giờ là mã lệnh.

Ngay khi bạn nối chuỗi — dù bên trong SP, bên trong C#, hay ở bất kỳ đâu — ranh giới giữa mã và dữ liệu biến mất, và SP không bảo vệ được gì. Ngược lại, một câu SQL viết thẳng trong C# với tham số hoá đầy đủ thì hoàn toàn an toàn.

text

An toàn hay không quyết định bởi:   tham số hoá
KHÔNG quyết định bởi:               SP hay không SP, ORM hay không ORM

Bản an toàn — sp_executesql với tham số:

sql

CREATE PROCEDURE SearchLeadsSafe
    @Name    NVARCHAR(200),
    @SortCol SYSNAME = N'CreatedAt'
AS
BEGIN
    -- Tên cột KHÔNG tham số hoá được -> phải whitelist
    IF @SortCol NOT IN (N'CreatedAt', N'Name', N'Value')
        THROW 50001, N'Cột không hợp lệ', 1;

    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT Id, Name, Value FROM Leads WHERE Name = @Name ORDER BY '
        + QUOTENAME(@SortCol);

    EXEC sp_executesql @sql, N'@Name NVARCHAR(200)', @Name = @Name;
END

sql

EXEC SearchLeadsSafe @Name = N''' OR 1=1 --';

text

(0 rows affected)          <- tìm đúng khách hàng tên là "' OR 1=1 --"

Chuỗi tấn công được xử lý như một giá trị bình thường, và không có khách hàng nào mang cái tên đó.

Chú ý hai kỹ thuật khác nhau trong đoạn trên:

Thành phần	Kỹ thuật	Vì sao
Giá trị (`@Name`)	Tham số hoá	Tham số hoá được
Tên cột (`@SortCol`)	Whitelist + `QUOTENAME`	Tên đối tượng không tham số hoá được

Đây là điểm nhiều người vấp: bạn không thể viết ORDER BY @SortCol. Tên bảng, tên cột, và các từ khoá phải đi qua danh sách trắng — không có cách nào khác an toàn.

Ba nơi injection lọt vào mà người ta ít ngờ:

csharp

// 1. Chuỗi nội suy trong EF Core — ExecuteSqlRaw KHÔNG tham số hoá
_db.Database.ExecuteSqlRaw($"UPDATE Leads SET Status = '{status}' WHERE Id = {id}");

// ĐÚNG — FromSql / ExecuteSql nội suy nhưng THAM SỐ HOÁ tự động
_db.Database.ExecuteSql($"UPDATE Leads SET Status = {status} WHERE Id = {id}");

csharp

// 2. Sắp xếp động từ tham số người dùng
query = query.OrderBy(sortBy);                      // với thư viện dynamic LINQ
// -> phải whitelist sortBy, xem bài 9.5

sql

-- 3. Nối chuỗi trong trigger hoặc job, nơi ít ai review

Cách rà soát:

bash

grep -rniE "EXEC\s*\(|ExecuteSqlRaw|FromSqlRaw" --include=*.sql --include=*.cs src/

Mỗi kết quả cần kiểm: giá trị đi vào bằng tham số, hay bằng phép nối chuỗi?

Bài 2 — View lồng nhau và điều optimizer làm được

Tạo chuỗi 3 view chồng lên nhau trên bảng 1 triệu dòng, chạy SELECT ... WHERE Id = 42, và xem execution plan. So sánh với truy vấn viết thẳng.

Tiêu chí hoàn thành: bạn nêu được optimizer làm được gì với view lồng nhau, và trường hợp nào nó không làm được.

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

Gợi ý. View không lưu dữ liệu. Hãy nghĩ xem optimizer nhìn thấy gì khi bạn truy vấn một view.

Lời giải — ba view chồng nhau:

sql

CREATE VIEW vw_LeadsActive AS
SELECT * FROM Leads WHERE DeletedAt IS NULL;

CREATE VIEW vw_LeadsWithOwner AS
SELECT l.*, u.Name AS OwnerName
FROM   vw_LeadsActive l
LEFT JOIN Users u ON u.Id = l.OwnerId;

CREATE VIEW vw_LeadsReport AS
SELECT Id, Name, Value, Status, OwnerName, CreatedAt
FROM   vw_LeadsWithOwner
WHERE  Status <> N'Deleted';

sql

SET STATISTICS IO ON;
SELECT * FROM vw_LeadsReport WHERE Id = 42;

text

|--Nested Loops(Left Outer Join)
   |--Clustered Index Seek(OBJECT:([PK_Leads]), SEEK:([Id]=(42)))
   |--Clustered Index Seek(OBJECT:([PK_Users]), SEEK:([Id]=[OwnerId]))

Table 'Leads'. logical reads 3
Table 'Users'. logical reads 3

Giống hệt truy vấn viết thẳng. Chỉ 6 lần đọc trang, dù đi qua ba tầng view.

Optimizer làm được gì. Nó nở (expand) định nghĩa view vào truy vấn trước khi tối ưu — view chỉ là một đoạn văn bản SQL được thay thế vào. Sau khi nở, nó có một truy vấn phẳng duy nhất và tối ưu bình thường, bao gồm cả việc đẩy điều kiện xuống (predicate pushdown):

text

Bạn viết:      SELECT * FROM vw_LeadsReport WHERE Id = 42
Optimizer thấy: SELECT ... FROM Leads l LEFT JOIN Users u ...
                WHERE l.DeletedAt IS NULL AND l.Status <> 'Deleted' AND l.Id = 42

Điều kiện Id = 42 được đẩy xuống tận bảng gốc, nên nó dùng được clustered index seek. Tầng view không tốn gì cả.

Trường hợp optimizer KHÔNG làm được — và đây mới là điều cần nhớ:

1. View có TOP, ORDER BY hoặc OFFSET. Những thứ này buộc phải vật chất hoá kết quả trung gian trước khi lọc tiếp:

sql

CREATE VIEW vw_Recent AS
SELECT TOP (100000) * FROM Leads ORDER BY CreatedAt DESC;

SELECT * FROM vw_Recent WHERE Id = 42;

Điều kiện Id = 42 không đẩy xuống được — vì "100.000 dòng mới nhất rồi lọc theo Id" khác hẳn "lọc theo Id trong 100.000 dòng mới nhất". Optimizer phải lấy đủ 100.000 dòng trước.

2. View có hàm cửa sổ hoặc DISTINCT. Tương tự: ROW_NUMBER() phải được tính trên toàn tập trước khi lọc.

3. View gọi hàm vô hướng do người dùng viết. Trước SQL Server 2019, mỗi dòng là một lần gọi hàm và optimizer không nhìn vào bên trong được.

4. View lồng nhau quá sâu. Optimizer có giới hạn thời gian cho việc tối ưu; với chuỗi 8–10 view mỗi cái có JOIN riêng, nó có thể bỏ cuộc và chọn một plan chưa tối ưu.

Giá trị thật của view — không phải hiệu năng:

sql

-- Giới hạn cột mà một vai trò được xem
CREATE VIEW vw_LeadsPublic AS
SELECT LeadId, CompanyName, Status FROM Leads;       -- không có EstimatedValue

GRANT SELECT ON vw_LeadsPublic TO ReportingRole;     -- KHÔNG cấp quyền trên Leads

View là công cụ đóng gói và phân quyền, không phải công cụ tăng tốc. Dùng nó để giấu độ phức tạp và giới hạn dữ liệu, đừng kỳ vọng nó làm truy vấn nhanh hơn — nó không làm chậm đi là đã tốt.

Và đừng chồng quá ba tầng. Không phải vì hiệu năng mà vì khả năng gỡ rối: khi một truy vấn chậm, bạn phải đọc ngược năm định nghĩa view để hiểu nó thật sự làm gì.

Bài 3 — Cái giá của indexed view

Đo thời gian chèn 100.000 dòng vào bảng, rồi tạo indexed view tổng hợp trên bảng đó và đo lại.

Tiêu chí hoàn thành: bạn nêu được vì sao indexed view làm chậm ghi, và so sánh được nó với bảng tổng hợp cập nhật theo lịch.

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

Gợi ý. Chữ "indexed" nghĩa là kết quả view được lưu thật trên đĩa. Hãy nghĩ xem chuyện gì xảy ra với bản lưu đó khi bảng gốc thay đổi.

Lời giải — đo trước:

sql

SET STATISTICS TIME ON;

INSERT INTO Leads (CustomerId, Name, Value, Status)
SELECT ..., ..., ..., ... FROM generate_series(1, 100000);

text

CPU time = 2.8 s,  elapsed time = 3.4 s

Tạo indexed view:

sql

CREATE VIEW vw_CustomerLeadCount
WITH SCHEMABINDING
AS
SELECT CustomerId, COUNT_BIG(*) AS LeadCount, SUM(Value) AS TotalValue
FROM   dbo.Leads
GROUP  BY CustomerId;

CREATE UNIQUE CLUSTERED INDEX IX_vw_CustomerLeadCount
    ON vw_CustomerLeadCount (CustomerId);          -- lúc này mới LƯU dữ liệu

Đo lại cùng phép chèn:

text

CPU time = 9.1 s,  elapsed time = 11.7 s

Chèn chậm hơn khoảng 3,4 lần.

Vì sao indexed view làm chậm ghi. Khi tạo clustered index trên view, SQL Server vật chất hoá kết quả của view và lưu nó như một bảng thật. Từ đó, mỗi INSERT, UPDATE, DELETE trên Leads phải cập nhật đồng bộ, trong cùng transaction bản lưu đó:

text

INSERT một lead cho CustomerId = 42
  -> ghi dòng vào Leads
  -> tìm dòng CustomerId = 42 trong indexed view
  -> cập nhật LeadCount += 1 và TotalValue += Value
  -> tất cả trong CÙNG một transaction

Tức là mỗi lần ghi trở thành hai lần ghi, cộng thêm một lần tìm. Và vì nó nằm trong cùng transaction, khoá được giữ lâu hơn — làm tăng cả tranh chấp lẫn xác suất deadlock.

Đổi lại, đọc rất nhanh:

sql

SELECT CustomerId, LeadCount, TotalValue
FROM   vw_CustomerLeadCount WITH (NOEXPAND)
WHERE  CustomerId = 42;

text

Table 'vw_CustomerLeadCount'. logical reads 3

So với việc tự tổng hợp trên một triệu dòng, đây là khác biệt rất lớn.

So với bảng tổng hợp cập nhật theo lịch:

	Indexed view	Bảng tổng hợp theo lịch
Độ mới của dữ liệu	Tức thì	Trễ bằng chu kỳ job
Chi phí ghi	Mỗi lần ghi	Chỉ khi job chạy
Tác động tới giao dịch	Kéo dài transaction	Không
Ràng buộc cú pháp	Rất nhiều	Không
Tự dùng khi truy vấn khớp	Có (bản Enterprise)	Không

Ràng buộc cú pháp của indexed view là thứ hay làm người ta nản: bắt buộc SCHEMABINDING, bắt buộc COUNT_BIG(*) thay vì COUNT(*), không được có LEFT JOIN, không được có subquery, không được có DISTINCT, không được dùng SUM trên cột nullable. Nhiều báo cáo thực tế không viết lại được cho vừa các ràng buộc này.

Bảng tổng hợp cập nhật theo lịch:

sql

CREATE TABLE CustomerLeadStats (
    CustomerId  INT PRIMARY KEY,
    LeadCount   INT NOT NULL,
    TotalValue  DECIMAL(18,2) NOT NULL,
    UpdatedAt   DATETIME2(3) NOT NULL
);

-- Job chạy mỗi 15 phút
MERGE CustomerLeadStats AS t
USING (SELECT CustomerId, COUNT(*) AS c, SUM(Value) AS v FROM Leads GROUP BY CustomerId) AS s
   ON t.CustomerId = s.CustomerId
WHEN MATCHED THEN UPDATE SET LeadCount = s.c, TotalValue = s.v, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED THEN INSERT VALUES (s.CustomerId, s.c, s.v, SYSUTCDATETIME());

Quy tắc chọn:

text

Con số PHẢI đúng ngay lập tức (tồn kho lúc đặt hàng)  -> indexed view
Con số trễ 15 phút vẫn dùng được (bảng điều khiển)    -> bảng tổng hợp

Và với phần lớn báo cáo nghiệp vụ, câu trả lời là vế thứ hai — vì không ai ra quyết định dựa trên chênh lệch 15 phút của một con số tổng hợp.

PostgreSQL có materialized view, nhưng nó cập nhật THỦ CÔNG:

sql

CREATE MATERIALIZED VIEW mv_customer_lead_count AS
SELECT customer_id, COUNT(*) AS lead_count, SUM(value) AS total_value
FROM   leads GROUP BY customer_id;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_lead_count;

Tức là nó thuộc nhóm thứ hai trong bảng trên — bạn tự quyết định khi nào làm mới, và ghi không bị chậm đi chút nào.

Tự kiểm tra

Stored procedure có tự an toàn với SQL injection không?

Không. Một SP nối chuỗi vào dynamic SQL vẫn bị injection y như code ứng dụng. Thứ bảo vệ bạn là tham số hoá chứ không phải nơi câu lệnh được lưu; dùng sp_executesql với tham số cho giá trị, và whitelist cho tên bảng hay tên cột vì chúng không tham số hoá được.

SP có nhanh hơn truy vấn từ ứng dụng không?

Từ SQL Server 2005 thì gần như không, vì truy vấn tham số hoá từ ứng dụng cũng được cache plan y hệt. Lý do mạnh nhất để dùng SP là xử lý theo lô rất lớn ngay trong database thay vì kéo dữ liệu về ứng dụng rồi đẩy ngược lại.

View có giúp truy vấn nhanh hơn không?

Không. View không lưu dữ liệu mà chỉ là một định nghĩa được đặt tên, được optimizer nội tuyến vào truy vấn chính. Giá trị của nó là che giấu độ phức tạp, ổn định hợp đồng, và cho phép cấp quyền trên view mà không cấp quyền trên bảng gốc.

View lồng nhau gây vấn đề gì?

Optimizer phải mở rộng toàn bộ chuỗi và không phải lúc nào cũng đẩy được điều kiện lọc xuống tầng dưới cùng, nên một truy vấn trông như lấy một dòng có thể quét vài bảng lớn. Quy tắc thực dụng là một tầng view, không lồng.

Indexed view khác materialized view thế nào?

Indexed view của SQL Server được cập nhật tự động và đồng bộ trong cùng transaction với bảng gốc, nên dữ liệu luôn mới nhưng nó làm chậm mọi thao tác ghi, và ràng buộc khá chặt. Materialized view của PostgreSQL phải refresh thủ công nên dữ liệu cũ tới lần refresh sau, đổi lại không ảnh hưởng đường ghi.

Ranh giới giữa logic thuộc database và thuộc ứng dụng là gì?

Database bảo vệ những gì không bao giờ được sai, như ràng buộc toàn vẹn và khoá ngoại. Ứng dụng chứa quy tắc có thể thay đổi theo yêu cầu kinh doanh. Email phải duy nhất là toàn vẹn dữ liệu, còn khách hàng hạng Bronze không được tạo deal lớn là quy tắc kinh doanh có thể đổi tháng sau.

Kết luận

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

Tham số hoá bảo vệ bạn, không phải stored procedure.
View là đặt tên, không phải tăng tốc. Không lồng quá một tầng.
Indexed view làm chậm đường ghi. Báo cáo thường nên dùng materialized view với job refresh.

Tham khảo

Điều hướng

Bài trước: 12.6 — Transactions và Locking
Bài tiếp theo: 12.8 — Database Migration Strategy
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.7 - Stored Procedures và Views

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

Nội dung bài học

12.7.1 — Khi nào dùng stored procedure

12.7.2 — SP không tự an toàn

12.7.3 — View: đặt tên, không tăng tốc

12.7.4 — Indexed view và materialized view

12.7.5 — Logic nghiệp vụ đặt ở đâu

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

Bài tập áp dụng

Bài 1 — Stored procedure vẫn bị SQL injection

Bài 2 — View lồng nhau và điều optimizer làm được

Bài 3 — Cái giá của indexed view

Tự kiểm tra

Kết luận

Tham khảo

Điều hướng

12.7 - Stored Procedures và Views ​

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

Nội dung bài học ​

12.7.1 — Khi nào dùng stored procedure ​

12.7.2 — SP không tự an toàn ​

12.7.3 — View: đặt tên, không tăng tốc ​

12.7.4 — Indexed view và materialized view ​

12.7.5 — Logic nghiệp vụ đặt ở đâu ​

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

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

Bài 1 — Stored procedure vẫn bị SQL injection ​

Bài 2 — View lồng nhau và điều optimizer làm được ​

Bài 3 — Cái giá của indexed view ​

Tự kiểm tra ​

Kết luận ​

Tham khảo ​

Điều hướng ​

12.7 - Stored Procedures và Views

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

Nội dung bài học

12.7.1 — Khi nào dùng stored procedure

12.7.2 — SP không tự an toàn

12.7.3 — View: đặt tên, không tăng tốc

12.7.4 — Indexed view và materialized view

12.7.5 — Logic nghiệp vụ đặt ở đâu

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

Bài tập áp dụng

Bài 1 — Stored procedure vẫn bị SQL injection

Bài 2 — View lồng nhau và điều optimizer làm được

Bài 3 — Cái giá của indexed view

Tự kiểm tra

Kết luận

Tham khảo

Điều hướng