Mở đầu — vì sao bài này quan trọng
Hãy tưởng tượng bạn là BA của một sàn thương mại điện tử. Sếp yêu cầu một dashboard "Daily Revenue" để theo dõi doanh thu mỗi sáng. Bạn viết một query JOIN 5 bảng — orders, order_items, products, customers, payments — rồi GROUP BY theo ngày. Query chạy mất 8 giây mỗi lần. Một mình bạn chạy thì không sao. Nhưng dashboard có 20 widget, và mỗi sáng cả phòng kinh doanh 15 người cùng mở. 20 widget × 8 giây × 15 người = database "ngộp thở", report load mãi không xong, và đội DevOps bắt đầu gửi tin nhắn hỏi "ai đang đánh database vậy?".
Vấn đề ở đây không phải query của bạn viết sai. Query đúng hoàn toàn. Vấn đề là bạn đang tính toán lại cùng một con số đã biết, lặp đi lặp lại. Doanh thu ngày 25/06 là một con số cố định — nó sẽ không thay đổi nữa. Vậy tại sao mỗi lần ai đó mở dashboard, database lại phải JOIN 5 bảng và quét hàng triệu dòng để tính lại đúng con số đó?
Đây chính là lúc materialized view và pre-aggregated table (bảng tổng hợp sẵn) bước vào. Chúng là vũ khí giúp một BA biến report chạy 8 giây thành report chạy 80 mili-giây. Quan trọng hơn, hiểu được khái niệm này giúp bạn nói chuyện đúng ngôn ngữ với data engineer: thay vì kêu "dashboard chậm quá", bạn có thể đề xuất "hay là mình pre-aggregate bảng doanh thu theo ngày, refresh lúc 2 giờ sáng?". Đó là khác biệt giữa một BA biết SQL và một BA hiểu cách dữ liệu vận hành.
Khái niệm cốt lõi
View thường vs Materialized View
Bạn đã học view (bài 54 sẽ đi sâu) — về bản chất, view thường chỉ là một câu query được lưu lại dưới một cái tên. Khi bạn SELECT từ view, database chạy lại toàn bộ query bên dưới ngay lúc đó. View không lưu dữ liệu, nó chỉ lưu "công thức". Vì vậy view thường không giúp gì cho hiệu năng — query 8 giây bọc trong view vẫn là 8 giây.
Materialized view thì khác hẳn. Nó chạy query một lần, rồi lưu kết quả ra đĩa như một bảng thật. Lần sau bạn SELECT từ nó, database chỉ đọc kết quả đã lưu sẵn — không JOIN, không GROUP BY lại. Đổi lại, dữ liệu trong materialized view là một "ảnh chụp" tại thời điểm tính, nên có thể cũ (stale) cho đến khi bạn refresh nó.
Đây là sự đánh đổi cốt lõi của cả bài này:
| View thường | Materialized View | |
|---|---|---|
| Lưu dữ liệu? | Không, chỉ lưu query | Có, lưu kết quả ra đĩa |
| Tốc độ đọc | Chậm (chạy lại query) | Nhanh (đọc sẵn) |
| Độ tươi của dữ liệu | Luôn mới nhất | Cũ cho đến khi refresh |
| Tốn dung lượng? | Không | Có |
PostgreSQL có sẵn, MySQL thì không
Đây là điểm rất thực tế cho BA Việt Nam, vì hai database này phổ biến nhất ở ta:
PostgreSQL hỗ trợ materialized view trực tiếp:
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
DATE(o.created_at) AS order_date,
COUNT(DISTINCT o.id) AS so_don,
SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY DATE(o.created_at);
Để cập nhật lại số liệu, bạn chạy:
REFRESH MATERIALIZED VIEW mv_daily_revenue;
MySQL không có materialized view như một tính năng built-in. Ở MySQL, bạn phải tự "giả lập" nó bằng một pre-aggregated table thật — tức là một bảng thường, được nạp dữ liệu định kỳ bằng INSERT ... SELECT. Đây gọi là summary table hoặc rollup table.
Pre-aggregated table — khái niệm rộng hơn
Đừng để cái tên đánh lừa. Materialized view chỉ là một cách để có pre-aggregated table. Bản chất của ý tưởng là: tính toán nặng một lần, vào lúc rảnh (ví dụ 2 giờ sáng), lưu kết quả gọn nhẹ lại, để giờ làm việc ai cũng đọc nhanh.
Một pre-aggregated table tốt thường có đặc điểm: ít dòng hơn bảng gốc rất nhiều (vì đã GROUP BY), chỉ chứa đúng các cột mà report cần, và có sẵn index trên cột thời gian. Bảng orders của một sàn TMĐT có thể 50 triệu dòng; nhưng bảng daily_revenue tổng hợp theo ngày chỉ có khoảng 1.000 dòng (3 năm × 365 ngày). Đọc 1.000 dòng nhanh hơn quét 50 triệu dòng gấp hàng vạn lần.
Full refresh vs Incremental refresh
Có hai cách làm tươi dữ liệu:
- Full refresh: xóa hết, tính lại toàn bộ từ đầu. Đơn giản, chắc chắn đúng, nhưng tốn. PostgreSQL
REFRESH MATERIALIZED VIEWmặc định là full refresh. - Incremental refresh (cập nhật tăng dần): chỉ tính lại phần dữ liệu mới (ví dụ chỉ ngày hôm qua), giữ nguyên phần cũ. Tiết kiệm hơn nhiều khi bảng lớn, nhưng cần bạn tự viết logic.
Đừng quên CONCURRENTLY
Khi PostgreSQL chạy REFRESH MATERIALIZED VIEW thông thường, nó khóa view lại — ai đang đọc sẽ phải chờ. Với dashboard luôn có người xem, điều này gây gián đoạn. Giải pháp:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;
Người dùng vẫn đọc được dữ liệu cũ trong lúc refresh chạy nền. Điều kiện: view phải có một UNIQUE index. Nhớ kỹ điểm này — đây là lỗi rất hay gặp ở phần sau.
Tình huống thực tế
Tình huống 1: Sàn TMĐT kiểu Tiki — dashboard doanh thu 8 giây
Một công ty bán lẻ online (gọi là "ShopViet") có dashboard điều hành mở mỗi sáng. Widget "Doanh thu 30 ngày gần nhất" JOIN 5 bảng và mất 8 giây. Vào 9 giờ sáng, khi cả phòng cùng vào họp giao ban, database CPU vọt lên 95%, dashboard quay vòng vòng.
BA của ShopViet phân tích: doanh thu các ngày trong quá khứ không bao giờ đổi nữa — chỉ ngày hôm nay là còn biến động. Vậy tính lại toàn bộ lịch sử mỗi lần là lãng phí. Họ tạo một materialized view mv_daily_revenue như ví dụ ở trên, và đặt lịch refresh lúc 3 giờ sáng (qua công cụ lập lịch của hệ thống, sẽ nói ở bài 49 này phần dưới).
Kết quả: widget từ 8 giây xuống còn 40 mili-giây — vì giờ nó chỉ SELECT * FROM mv_daily_revenue ORDER BY order_date DESC LIMIT 30. CPU lúc giao ban không còn nhảy. Cái giá phải trả: số liệu của ngày hôm nay chỉ cập nhật đến 3 giờ sáng. Đội kinh doanh đồng ý — họ chỉ cần số liệu lịch sử chính xác, còn doanh thu "real-time trong ngày" thì xem ở một widget riêng nhỏ (query thẳng, chỉ lọc đúng hôm nay nên nhẹ).
Bài học: Hãy tách "phần dữ liệu đã đóng băng" (lịch sử) khỏi "phần dữ liệu còn động" (hôm nay). Pre-aggregate phần đóng băng, query thẳng phần còn động. Đây là pattern vàng.
Tình huống 2: Fintech kiểu MoMo — báo cáo giao dịch theo tỉnh thành
Một ví điện tử (gọi là "PayViet") cần report số lượng và giá trị giao dịch theo tỉnh thành, theo loại dịch vụ (nạp điện thoại, chuyển tiền, thanh toán hóa đơn), theo từng ngày. Bảng transactions có 200 triệu dòng và tăng 2 triệu dòng mỗi ngày. Đội phân tích chạy report này hàng chục lần mỗi ngày để làm slide cho ban giám đốc.
Họ dùng MySQL, nên không có materialized view. Giải pháp: tạo bảng summary_tx_daily và một job incremental:
-- Bảng tổng hợp
CREATE TABLE summary_tx_daily (
ngay DATE,
tinh VARCHAR(50),
loai_dv VARCHAR(50),
so_giao_dich BIGINT,
gia_tri DECIMAL(18,2),
PRIMARY KEY (ngay, tinh, loai_dv)
);-- Job chạy mỗi đêm: chỉ nạp dữ liệu của ngày hôm qua
INSERT INTO summary_tx_daily (ngay, tinh, loai_dv, so_giao_dich, gia_tri)
SELECT
DATE(created_at), province, service_type,
COUNT(*), SUM(amount)
FROM transactions
WHERE DATE(created_at) = CURRENT_DATE - INTERVAL 1 DAY
GROUP BY DATE(created_at), province, service_type;
Mỗi đêm job chỉ quét 2 triệu dòng của ngày hôm qua (chứ không phải 200 triệu), tính ra vài nghìn dòng tổng hợp rồi INSERT vào. Report từ bảng summary_tx_daily chạy gần như tức thời vì bảng này chỉ vài trăm nghìn dòng.
Bài học: Với bảng cực lớn, incremental refresh là bắt buộc. Nếu PayViet làm full refresh 200 triệu dòng mỗi đêm thì job sẽ chạy hàng giờ và có thể không kịp trước giờ làm việc.
Tình huống 3: Ride-hailing kiểu Grab/Be — cái bẫy "dữ liệu cũ"
Một app gọi xe ("RideViet") làm materialized view tổng hợp số cuốc xe hoàn thành theo tài xế, để tính thưởng. View refresh lúc nửa đêm. Một tài xế khiếu nại: "Tôi chạy 18 cuốc hôm nay mà dashboard chỉ hiện 12, hệ thống ăn gian!".
Sự thật: dashboard đang đọc materialized view chụp lúc nửa đêm hôm qua, nên thiếu các cuốc trong ngày. Không ai sai về mặt kỹ thuật, nhưng BA đã không truyền đạt rõ độ trễ dữ liệu cho người dùng cuối. Họ sửa bằng cách thêm dòng chữ nhỏ trên dashboard: "Số liệu cập nhật đến 00:00 hôm nay" và bổ sung một query real-time cho riêng phần cuốc trong ngày.
Bài học: Materialized view đổi tốc độ lấy độ tươi. Là BA, bạn phải ghi rõ "dữ liệu tính đến lúc nào" trên report. Một dòng chú thích nhỏ tránh được hàng giờ tranh cãi và mất niềm tin.
Hướng dẫn từng bước
Đây là quy trình để bạn đề xuất và triển khai một pre-aggregated table cho một report chậm:
Bước 1 — Xác nhận report thật sự chậm và lặp lại. Đừng pre-aggregate mọi thứ. Chỉ làm khi report (a) chạy chậm, và (b) được chạy thường xuyên với cùng logic. Một report ad-hoc chạy một lần mỗi quý thì không đáng.
Bước 2 — Tách phần "đóng băng" khỏi phần "còn động". Hỏi: dữ liệu của những ngày trong quá khứ có còn thay đổi không? Nếu không, đó là ứng viên hoàn hảo để pre-aggregate.
Bước 3 — Quyết định độ chi tiết (granularity). Tổng hợp theo ngày? Theo tuần? Theo ngày × tỉnh × sản phẩm? Càng chi tiết, bảng càng linh hoạt nhưng càng to. Nguyên tắc: tổng hợp đến mức chi tiết nhất mà report của bạn cần, không hơn. Nếu chỉ cần theo ngày, đừng lưu theo giờ.
Bước 4 — Viết query tổng hợp và tạo đối tượng lưu trữ.
- PostgreSQL:
CREATE MATERIALIZED VIEW ... - MySQL:
CREATE TABLE summary_...rồiINSERT ... SELECT
REFRESH ... CONCURRENTLY ở PostgreSQL.CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_revenue (order_date);
Bước 6 — Lập lịch refresh. Đây là phần "vận hành". Dùng pg_cron (extension của PostgreSQL), hoặc cron của hệ điều hành, hoặc công cụ điều phối như Airflow/dbt. Đặt giờ vào lúc tải thấp (thường 2–4 giờ sáng):
-- ví dụ với pg_cron: refresh lúc 3h sáng mỗi ngày
SELECT cron.schedule('refresh_revenue', '0 3 *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue');
Bước 7 — Ghi rõ độ trễ dữ liệu cho người dùng. Thêm chú thích "cập nhật đến HH:MM" trên dashboard. Đừng bỏ qua bước này.
Bước 8 — Theo dõi thời gian job refresh. Khi dữ liệu lớn dần, job full refresh sẽ chậm dần. Đến lúc nào đó bạn cần chuyển sang incremental. Hãy theo dõi để biết thời điểm đó trước khi job "không kịp" trước giờ làm.
Lỗi thường gặp & mẹo
Lỗi 1: Tưởng materialized view tự cập nhật. Đây là hiểu lầm số một. Materialized view không tự làm tươi khi bảng gốc thay đổi. Nếu bạn không REFRESH, nó sẽ mãi mãi hiển thị số liệu của lần tạo. Rất nhiều người mới quên đặt lịch refresh rồi tưởng dữ liệu "đứng hình".
Lỗi 2: Quên UNIQUE index nên không dùng được CONCURRENTLY. Bạn chạy REFRESH ... CONCURRENTLY và PostgreSQL báo lỗi "cannot refresh materialized view concurrently". Nguyên nhân gần như luôn là thiếu UNIQUE index. Tạo nó trước.
Lỗi 3: Pre-aggregate sai granularity rồi không tính ngược lại được. Nếu bạn chỉ lưu AVG theo ngày, bạn không thể tính AVG cho cả tuần từ đó (trung bình của trung bình là sai). Mẹo: thay vì lưu AVG, hãy lưu SUM và COUNT riêng — rồi report tự chia SUM/COUNT ở bất kỳ mức nào. Đây là nguyên tắc "additive measure": ưu tiên lưu các đại lượng cộng được (SUM, COUNT) thay vì các đại lượng không cộng được (AVG, DISTINCT COUNT).
Lỗi 4: Pre-aggregate cả phần dữ liệu đang biến động. Nếu bạn nạp luôn dữ liệu hôm nay vào bảng tổng hợp lúc 3 giờ sáng, thì cả ngày con số đó bị "đóng băng sai". Mẹo: incremental refresh nên xóa và nạp lại ngày hôm qua + hôm nay mỗi lần chạy, để bắt các đơn hàng đến muộn (late-arriving data) — ví dụ đơn được xác nhận sau nửa đêm.
Lỗi 5: Lạm dụng, tạo hàng chục materialized view rồi quên. Mỗi view tốn dung lượng và tốn thời gian refresh. Đừng tạo cho mọi report. Một bộ vài bảng tổng hợp được thiết kế tốt (theo ngày, theo ngày × sản phẩm, theo ngày × khu vực) thường phục vụ được hàng chục report khác nhau.
Mẹo: So sánh chéo để kiểm tra đúng đắn. Sau khi tạo bảng tổng hợp, hãy chạy query gốc và query trên bảng tổng hợp cho cùng một khoảng thời gian, so sánh kết quả. Lệch nhau là dấu hiệu logic GROUP BY hoặc filter có vấn đề. Làm bước này một lần khi triển khai sẽ tránh được những con số sai âm thầm trên dashboard.
Bài tập thực hành
Giả sử bạn có bảng orders(id, customer_id, created_at, status) và order_items(order_id, product_id, quantity, unit_price) của một sàn TMĐT Việt Nam.
- Tạo materialized view (PostgreSQL): Viết
CREATE MATERIALIZED VIEW mv_monthly_revenuetổng hợp doanh thu theo tháng (chỉ tính đơnstatus = 'completed'), gồm số đơn, tổng doanh thu, và SUM cùng COUNT của giá trị đơn (để sau này tính được giá trị đơn trung bình ở bất kỳ mức nào).
- Thêm index: Tạo UNIQUE index phù hợp để có thể refresh CONCURRENTLY.
- Phiên bản MySQL: Viết lại bài 1 dưới dạng
CREATE TABLE summary_monthly_revenue+ câuINSERT ... SELECT. Suy nghĩ: làm sao để mỗi đêm chỉ nạp lại tháng hiện tại thay vì toàn bộ lịch sử?
- Tình huống thiết kế: Đội marketing muốn report doanh thu theo ngày × danh mục sản phẩm × tỉnh thành. Bảng gốc 80 triệu dòng. Hãy mô tả (bằng lời) bạn sẽ: chọn granularity nào, refresh full hay incremental, đặt lịch lúc nào, và ghi chú độ trễ ra sao cho người dùng.
- Phát hiện cái bẫy: Một bạn đồng nghiệp lưu
AVG(unit_price)theo ngày trong bảng tổng hợp, rồi định lấy trung bình các giá trị đó để ra "giá trung bình theo tháng". Giải thích vì sao sai và cách sửa.
Tóm tắt
- View thường lưu công thức và chạy lại mỗi lần; materialized view lưu kết quả ra đĩa và đọc nhanh, đổi lại dữ liệu có thể cũ cho đến khi
REFRESH. - PostgreSQL có materialized view sẵn; MySQL không có, phải tự làm pre-aggregated table bằng
CREATE TABLE+INSERT ... SELECTđịnh kỳ. - Pattern vàng: tách phần dữ liệu đã đóng băng (lịch sử) để pre-aggregate, query thẳng phần còn động (hôm nay).
- Với bảng cực lớn, dùng incremental refresh (chỉ nạp lại dữ liệu mới) thay vì full refresh.
- Ở PostgreSQL, dùng
REFRESH ... CONCURRENTLYđể không khóa người đọc — nhớ tạo UNIQUE index. - Lưu SUM và COUNT thay vì AVG để giữ tính cộng được; và luôn ghi rõ độ trễ dữ liệu trên report.