Sử dụng Materialized Views trong PostgreSQL: Tăng tốc truy vấn hiệu quả

Với một truy vấn SQL phức tạp, việc phải viết lại toàn bộ truy vấn mỗi khi cần lấy kết quả là không khả thi. Views giúp giải quyết vấn đề này. Một view là một truy vấn đã được đặt tên (được định nghĩa sẵn) và được xem như một bảng ảo chứa kết quả của truy vấn đó. Mã lệnh để tạo một view dựa trên một truy vấn thường có dạng như sau:

-- pseudocode
CREATE VIEW my_view AS
    SELECT ...
        FROM ... JOIN ... ON ...
        WHERE ... AND ... 
        ORDER BY ...
        LIMIT ...

Cơ bản, truy vấn được đặt thêm tiền tố CREATE VIEW my_view AS. Điều này tạo ra một view mới có tên my_view; bạn có thể truy vấn nó như một bảng thông thường:

-- pseudocode
SELECT ...  
    FROM my_view 
    WHERE ...

Nhờ vậy, bạn có thể truy cập kết quả của truy vấn chỉ bằng cách sử dụng view mà không cần phải viết lại toàn bộ truy vấn. Một view phần nào tương tự như một “hàm” (function) trong ngôn ngữ lập trình – chỉ là một cái tên ngắn gọn cho một truy vấn phức tạp, giúp cải thiện trải nghiệm người dùng và khả năng đọc của mã nguồn.

Tuy nhiên, bên dưới bề mặt, mỗi lần truy cập view, cơ sở dữ liệu sẽ chuyển đổi view đó thành truy vấn đầy đủ và (tái) đánh giá lại trước khi trả về kết quả. Việc tính toán lại một truy vấn phức tạp mỗi lần sẽ không hiệu quả và thậm chí không mang lại lợi thế về hiệu năng cho cơ sở dữ liệu. Ngược lại, việc thực thi lặp đi lặp lại các truy vấn phức tạp trên các bảng lớn sẽ làm giảm hiệu suất.

Giống như views, materialized views cũng cung cấp giao diện truy vấn nhất quán cho cơ sở dữ liệu. Materialized views che giấu các chi tiết về thiết kế và triển khai của cơ sở dữ liệu để đưa ra giao diện truy vấn đồng nhất cho tầng API. Materialized views lưu trữ (cache) kết quả của truy vấn trong một cấu trúc dữ liệu tồn tại, do đó có thể được truy cập mà không cần phải tính toán lại. Điều này giúp tiết kiệm thời gian cho các truy vấn phức tạp được truy cập nhiều lần.

Materialized views giải quyết vấn đề trên bằng cách lưu (cache) kết quả của một truy vấn đã được định nghĩa sẵn trong một cấu trúc dữ liệu tồn tại, tương tự như một bảng. Bạn có thể chạy các truy vấn SELECT và tạo các chỉ mục trên chúng như trên một bảng thông thường. Cũng có thể xây dựng materialized views dựa trên các truy vấn trên materialized views khác.

Materialized views có sẵn trong PostgreSQL, Oracle Database, SQL Server và một số hệ quản trị cơ sở dữ liệu khác. Tính năng này không có trên MySQL.

Yêu cầu

Bạn cần có kiến thức cơ bản về cơ sở dữ liệu PostgreSQL để có thể tận dụng tối đa hướng dẫn này. Giả định rằng bạn đã có kinh nghiệm với các thao tác cơ bản như cài đặt phần mềm, tạo cơ sở dữ liệu mới, tạo bảng, thực hiện các truy vấn tiêu chuẩn, v.v. Đối với các ví dụ SQL, giả sử rằng bạn đã có một instance PostgreSQL đang chạy trên Ubuntu, FreeBSD, CentOS  hoặc đang sử dụng dịch vụ cơ sở dữ liệu được quản lý.

Các ví dụ SQL trong hướng dẫn này được kiểm tra trên PostgreSQL 14.5 chạy trên FreeBSD 13.1-RELEASE. Chúng nên tương thích với các phiên bản PostgreSQL gần đây chạy trên các hệ điều hành hiện đại.

Thiết lập các bảng thử nghiệm

Trước khi tạo materialized view, hãy thiết lập hai bảng thử nghiệm và chèn dữ liệu mẫu vào chúng.

Tạo bảng product

CREATE TABLE IF NOT EXISTS product (
    product_id INTEGER PRIMARY KEY, 
    name VARCHAR(20) NOT NULL, 
    price SMALLINT NOT NULL
);

Tạo bảng orders

CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER, 
    product_id INTEGER REFERENCES product (product_id),
    PRIMARY KEY (order_id, product_id)
);

Kiểm tra mô tả của các bảng đã tạo:

\d orders

Chèn dữ liệu mẫu

Chèn một số dòng dữ liệu mẫu vào bảng product:

INSERT INTO product (product_id, name, price) VALUES (1, 'Floppy Disk Drive', 40);
 INSERT INTO product (product_id, name, price) VALUES (2, 'Oculus Quest', 400);

Chèn dữ liệu mẫu vào bảng orders:

INSERT INTO orders (order_id, product_id) VALUES (1, 1);
INSERT INTO orders (order_id, product_id) VALUES (1, 2);
INSERT INTO orders (order_id, product_id) VALUES (2, 1);

Kiểm tra dữ liệu trong các bảng:

SELECT * FROM orders;

SELECT * FROM product;

Tạo Materialized View

Tạo một materialized view dựa trên truy vấn JOIN:

CREATE MATERIALIZED VIEW mv_products_orders
AS
SELECT 
    p.product_id, 
    o.order_id, 
    p.name, 
    p.price 
FROM 
    product p 
JOIN 
    orders o 
ON 
    p.product_id = o.product_id;

Lệnh trên tạo ra materialized view mv_products_orders và chèn dữ liệu dựa trên các bảng cơ sở tại thời điểm tạo materialized view. Theo mặc định, tên các cột của materialized view được lấy từ các bảng cơ sở.

Kiểm tra định nghĩa của materialized view vừa tạo:

\d mv_products_orders

Kiểm tra dữ liệu trong materialized view:

SELECT * FROM mv_products_orders;

Để đổi tên materialized view, sử dụng lệnh ALTER:

ALTER MATERIALIZED VIEW mv_products_orders RENAME TO my_mv;

Tạo Chỉ mục trên Materialized View

Chỉ mục trên materialized view mang lại lợi ích tương tự như trên bảng thông thường – giúp tăng tốc độ truy vấn. Đặc biệt, một cách phổ biến để làm mới (refresh) materialized view (sẽ được đề cập sau) là sử dụng chỉ mục. Bạn có thể định nghĩa chỉ mục trên bất kỳ cột nào của materialized view.

CREATE UNIQUE INDEX product_order ON mv_products_orders (order_id, product_id);

Kiểm tra xem mô tả của materialized view có bao gồm chỉ mục hay không:

\d mv_products_orders

(Materialized) Views trên (Materialized) Views

Bạn có thể tạo materialized views dựa trên các materialized views khác.

CREATE MATERIALIZED VIEW my_mv
AS
SELECT 
    * from mv_products_orders
    limit 2;

Tương tự, cũng có thể tạo view dựa trên materialized view và ngược lại.

Làm mới (Cập nhật) Materialized Views

PostgreSQL không tự động làm mới materialized views. Điều này có nghĩa là, theo mặc định, dữ liệu trong materialized view sẽ trở nên lỗi thời khi các bảng cơ sở được cập nhật. Bạn cần làm mới materialized view bằng tay hoặc cấu hình hệ thống tự động làm điều đó.

Thêm một dòng mới vào bảng orders:

INSERT INTO orders (order_id, product_id) VALUES (2, 2);

Kiểm tra lại materialized view:

SELECT * FROM mv_products_orders;

Kết quả vẫn như trước vì materialized view chưa được cập nhật.

Làm mới thủ công

Lệnh REFRESH được sử dụng để làm mới nội dung của materialized view:

REFRESH MATERIALIZED VIEW mv_products_orders;

Kiểm tra lại materialized view để xác nhận rằng nó đã bao gồm dữ liệu mới từ bảng orders:

SELECT * FROM mv_products_orders;

Việc làm mới này sẽ xóa bỏ nội dung cũ và tái tạo materialized view. Lưu ý rằng, trong quá trình làm mới theo cách này, bạn không thể truy vấn materialized view vì thao tác làm mới sẽ khóa nó và chặn các truy vấn SELECT. Khóa này được giữ cho đến khi kết thúc giao dịch (refresh).

Tùy chọn CONCURRENTLY

Làm mới với tùy chọn CONCURRENTLY giải quyết vấn đề trên.

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders;

Với tùy chọn CONCURRENTLY, cơ sở dữ liệu không khóa các truy vấn SELECT trên materialized view trong quá trình làm mới. Khi tùy chọn này được chỉ định, nội bộ sẽ tạo ra một cấu trúc dữ liệu tạm thời chứa kết quả mới của truy vấn materialized view. Sau đó, kết quả cũ và mới được so sánh và các thay đổi được áp dụng lên materialized view ban đầu bằng các thao tác UPDATE và INSERT.

Lưu ý rằng, để làm mới theo cách CONCURRENTLY, materialized view phải chứa ít nhất một chỉ mục duy nhất dựa trên cột. Nếu không có chỉ mục duy nhất, hệ thống sẽ báo lỗi:

ERROR:  cannot refresh materialized view "public.mv_products_orders" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

Cũng cần lưu ý rằng chỉ có thể chạy một thao tác làm mới trên một materialized view tại một thời điểm (ngay cả với tùy chọn CONCURRENTLY).

Ưu và Nhược điểm

Nếu việc làm mới (refresh) liên quan đến nhiều dữ liệu mới, tốc độ làm mới sẽ nhanh hơn khi không sử dụng tùy chọn CONCURRENTLY. Điều này là do các phép so sánh và các thao tác UPDATE liên quan trong quá trình làm mới theo kiểu concurrent.

Mẹo thực tế: Nói chung, nên tiến hành vacuum định kỳ cho cơ sở dữ liệu để dọn dẹp các cấu trúc dữ liệu không sử dụng và giải phóng không gian. Điều này đặc biệt quan trọng với việc làm mới theo kiểu CONCURRENTLY vì quá trình này liên quan đến việc tạo ra các cấu trúc dữ liệu tạm thời. Nên tiến hành vacuum sau khi làm mới. Tuy nhiên, chủ đề vacuum khá rộng và không nằm trong phạm vi của hướng dẫn này.

Làm mới tự động

Tính đến tháng 11 năm 2022, PostgreSQL không có tính năng tự động làm mới materialized views. Tuy nhiên, bạn có thể thiết lập tự động làm mới thông qua các công cụ khác.

Cron Jobs

Một cách phổ biến để tự động làm mới materialized views là sử dụng cron jobs:

https://docs.vultr.com/how-to-use-the-cron-task-scheduler

Thêm dòng lệnh này vào crontab của người dùng postgres sẽ gọi lệnh psql mỗi 15 phút, truyền vào tên cơ sở dữ liệu và câu lệnh SQL làm mới materialized view. Lưu ý rằng, nếu bạn không tạo hoặc kết nối đến một cơ sở dữ liệu cụ thể, theo mặc định, các truy vấn sẽ được thực thi trên cơ sở dữ liệu postgres.

Mẹo thực tế: Vì chỉ có thể chạy một thao tác làm mới cùng một lúc, nên bạn cần có ý tưởng về thời gian làm mới mất bao lâu trước khi sắp xếp cron jobs cho nó.

Triggers

Bạn cũng có thể sử dụng trigger để cập nhật materialized views. Để làm điều này, hãy tạo một hàm (function) làm mới materialized view. Sau đó, trên các bảng mà dữ liệu được dùng trong materialized view, thiết lập trigger để gọi hàm này sau các thao tác INSERT, UPDATE và DELETE.

Tạo một hàm PL/pgSQL (SQL Procedural Language) để làm mới materialized view:

CREATE OR REPLACE FUNCTION mv_refresh()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders;
    RETURN NULL;
END;
$$;

Tạo một trigger gọi hàm này khi thực hiện các thao tác INSERT, UPDATE hoặc DELETE trên bảng orders:

CREATE TRIGGER mv_trigger 
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH STATEMENT EXECUTE PROCEDURE mv_refresh();

Kiểm tra lại định nghĩa của bảng orders có bao gồm trigger hay không:

\d orders

Tương tự, bạn có thể thêm trigger gọi hàm mv_refresh() khi dữ liệu trong bảng product thay đổi.

Xóa một dòng khỏi bảng orders:

DELETE FROM orders WHERE order_id = 1 AND product_id = 2;

Kiểm tra lại materialized view để xác nhận rằng dòng đã bị xóa không còn xuất hiện:

SELECT * FROM mv_products_orders;

Xóa Materialized Views

Xóa một materialized view tương tự như xóa một view thông thường.

DROP MATERIALIZED VIEW mv_products_orders;

Để xóa materialized view cùng với tất cả các đối tượng phụ thuộc vào nó, sử dụng tùy chọn CASCADE:

DROP MATERIALIZED VIEW IF EXISTS mv_products_orders CASCADE;

Lệnh trên sẽ xóa cả materialized view mv_products_orders cũng như my_mv được tạo dựa trên nó.

Kết luận

Giống như bất kỳ công cụ tối ưu hóa nào, materialized views mang lại lợi ích nhưng cũng đi kèm với những đánh đổi. Chúng có thể cải thiện hiệu năng đáng kể trong các trường hợp truy vấn dữ liệu phức tạp và thường xuyên, nhưng đồng thời đòi hỏi quản lý chặt chẽ để đảm bảo dữ liệu luôn được cập nhật một cách hiệu quả. Việc lựa chọn sử dụng materialized views cần cân nhắc kỹ lưỡng dựa trên nhu cầu thực tế, tần suất cập nhật dữ liệu và yêu cầu về hiệu năng của hệ thống. Khi được áp dụng đúng cách, đây là một công cụ mạnh mẽ giúp tối ưu hóa truy vấn và nâng cao trải nghiệm người dùng trong các hệ thống cơ sở dữ liệu lớn.