Trong SQL, chúng ta có thể sử dụng View như 1 dạng shortcut gọi tới câu lệnh truy vấn. Nội dung của View chính là kết quả thực thi của câu truy vấn đó. Materialized View cũng như vậy, tuy nhiên điểm khác biệt nằm ở chỗ: Materialized View có cache lại kết quả của lần truy vấn gần nhất xuống disk.

Ta hoàn toàn có thể sử dụng Materialized View được với hầu hết các cơ sở dữ liệu quan hệ: nó có thể đã được hỗ trợ sẵn hàm như trong PostgreSQL,MicrosoftSQL server, IBM DB2,… hoặc cũng có thể qua rất nhiều bước khá phức tạp như MySQL,… Với mục đích minh họa đơn giản, nên trong bài này chúng ta sẽ thử thực hành với PostgreSQL.

Trước hết, ta cùng nhau xem qua schema của dữ liệu đã nhé:

CREATE TABLE "public"."products" (
  product_id SERIAL PRIMARY KEY,
  product_name TEXT,
  brand TEXT
);

CREATE TABLE "public"."orders" (
    "id" SERIAL PRIMARY KEY,
    "product_id" INTEGER NOT NULL,
    "customer_id" INTEGER NOT NULL,
    "date_key" INTEGER NOT NULL,
    "price" BIGINT NOT NULL,
    CONSTRAINT "orders_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "public"."products"("product_id")
);

Nếu không có sẵn PostgreSQL trong máy, bạn có thể cài bằng lệnh:
docker run -it --rm -p 5432:5432 -e POSTGRES_PASSWORD=postgres -d postgres
Download sample data tại đây nhé.

Tạo Materialized View

Để tạo Materialized View, ta dụng lệnh CREATE MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

• Chỉ định tên của Materialized View (view_name) ngay sau mệnh đề CREATE MATERIALIZED VIEW.
• Chỉ định câu truy vấn sử dụng để load dữ liệu ở ngay sau từ khóa AS.
• Nếu bạn muốn load sẵn dữ liệu vào Materialized View ngay từ lúc khởi tạo, thì đặt thêm WITH DATA ở cuối. Ngược lại thì đặt WITH NO DATA.
  • Trong trường hợp WITH NO DATA: Materialized View sẽ được đánh dấu là không thể đọc được, ta chỉ có thể truy vấn được từ nó chỉ khi nó đã được load dữ liệu vào thôi.

Thử thực hành với sample data ban nãy:

CREATE MATERIALIZED VIEW orders_mat_view
AS
SELECT
 brand,
 date_key,
 COUNT(*) AS amount,
 SUM(price) AS total_price
FROM
 orders
 JOIN products ON products.product_id = orders.product_id
GROUP BY
 brand,
 date_key
WITH DATA;

Như vậy, giống với View, sau này khi cần thống kê dữ liệu đơn hàng theo brand+ngày, ta không cần phải gõ lại toàn bộ câu lệnh rất dài bên trên nữa, mà chỉ cần tóm gọn lại như sau:

SELECT * FROM orders_mat_view;

// tương tự như Table
// có thể thực hiện sort record bên trong Materialized View
SELECT * FROM orders_mat_view ORDER BY total_price DESC;

Ưu điểm của Materialized View so với View đó là thời gian response rất nhanh, gần như tức thì, vì dữ liệu đã được tính toán và lưu sẵn trong disk. DB sẽ chỉ cần lôi kết quả ra và truy vấn đơn giản hơn (kiểu như lọc theo brand, lọc theo ngày, hay sort theo amount, total_price,…).

Refresh dữ liệu trong Materialized View

Ta bắt đầu từ kết quả của truy vấn sau:

SELECT * FROM orders_mat_view WHERE brand = 'Omeprazole' AND date_key = 140101;

brand	date_key	amount	total_price
Omeprazole	140101	2	1555

Bây giờ ta sẽ bổ sung thêm 1 đơn hàng mới đến từ 1 sản phẩm (product_id=803) thuộc brand Omeprazole và vào ngày 140101, rồi xem lại kết quả:

INSERT INTO "public"."orders" ("id","product_id","customer_id","date_key","price") VALUES ('201','803','5301','140101','400');

SELECT * FROM orders_mat_view WHERE brand = 'Omeprazole' AND date_key = 140101;

brand	date_key	amount	total_price
Omeprazole	140101	2	1555

Như vậy, dữ liệu hiện có trong Material View vẫn mới chỉ lưu kết quả tính toán đơn hàng kể từ lần load đầu tiên, trong khi bảng orders đã xuất hiện thêm bản ghi mới. Để cập nhật lại nó, ta sử dụng lệnh sau:

REFRESH MATERIALIZED VIEW orders_mat_view;

SELECT * FROM orders_mat_view WHERE brand = 'Omeprazole' AND date_key = 140101;

brand	date_key	amount	total_price
Omeprazole	140101	3	1955

Trong quá trình refresh dữ liệu, PostgreSQL sẽ lock toàn bộ bảng, do đó bạn sẽ không thể truy vấn vào nó được. Để giải quyết vấn đề này, ta sử dụng từ khóa CONCURRENTLY:

CREATE UNIQUE INDEX idx_brand_date_key ON orders_mat_view (brand,date_key);

REFRESH MATERIALIZED VIEW CONCURRENTLY orders_mat_view;

Với từ khóa CONCURRENTLY, PostgreSQL sẽ tạo 1 phiên bản tạm thời của Materialized View, rồi so sánh giữa 2 phiên bản và chỉ INSERT và UPDATE những chỗ có sự khác biệt. Ta vẫn có thể truy vấn được trong lúc nó đang được cập nhật. Một lưu ý đó là từ khóa CONCURRENTLY chỉ khả dụng khi trong Materialized View có 1 unique index, đó là lý do mình có nhắc tới câu lệnh CREATE UNIQUE INDEX ở ngay trên.

Ứng dụng

Materialized View thường được ứng dụng nhiều trong lĩnh vực thống kê, với đặc thù cần phải response đủ nhanh với dữ liệu cực lớn. Chi tiết hơn nữa về phần này, ta sẽ tìm hiểu tiếp ở bài tiếp theo: Database 302: OLTP hay OLAP?