Nếu bạn đang tìm kiếm cách tối ưu hóa truy vấn SQL và tổ chức mã nguồn một cách khoa học, Common Table Expressions (CTEs) chính là giải pháp mạnh mẽ giúp bạn làm điều đó. CTEs cho phép bạn nhóm các truy vấn con phức tạp thành các khối logic riêng biệt, giúp mã SQL trở nên dễ đọc, dễ bảo trì và có cấu trúc rõ ràng hơn. Không chỉ cải thiện hiệu suất, CTEs còn giúp tái sử dụng truy vấn một cách linh hoạt, giảm thiểu sự lặp lại và nâng cao tính tối ưu trong quản lý dữ liệu.

Để tối ưu hóa các truy vấn phức tạp trong PostgreSQL, việc sử dụng Common Table Expressions (CTEs) là giải pháp hiệu quả. Ngoài ra, để đảm bảo hiệu suất cơ sở dữ liệu, bạn có thể khám phá các giải pháp VPS server mạnh mẽ, giúp tăng tốc xử lý dữ liệu tại DataOnline.

Một CTE bắt đầu với từ khóa WITH. Vì cú pháp như vậy, CTE đôi khi còn được gọi là WITH queries. Ở dạng đơn giản nhất, một CTE trông như sau:

-- pseudocode
WITH my_cte AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT ... 
FROM my_cte ;
-- or other operations like JOINS

CTE có tên my_cte tạm thời lưu trữ kết quả của truy vấn con nằm trong dấu ngoặc đơn. Kết quả này chỉ có hiệu lực trong phạm vi truy vấn, do đó, CTE cũng được gọi là inline views.

Mục lục nội dung

Yêu cầu

Để có thể tận dụng tối đa hướng dẫn này, bạn cần có một số kinh nghiệm thực hành với PostgreSQL. Đây là một hướng dẫn thực hành; bạn nên làm theo các ví dụ. Giả định rằng PostgreSQL đã được cài đặt và chạy trên một máy chủ độc lập hoặc dưới dạng một phiên bản được quản lý.

PostgreSQL đã hỗ trợ CTE từ phiên bản 8.4 (ra mắt năm 2009). Các ví dụ trong hướng dẫn này được kiểm tra trên PostgreSQL 14.5 và có khả năng tương thích với các phiên bản PostgreSQL gần đây.

Mô hình dữ liệu ví dụ 1

Để minh họa khái niệm, hướng dẫn này sử dụng một mô hình dữ liệu chuẩn hóa gồm hai bảng: node và edge. Giả sử các node đại diện cho con người, thì edge đại diện cho mối quan hệ giữa hai người. Ví dụ, “A là bạn của B”, “A theo dõi B”, “A báo cáo cho B”, v.v.

Bảng node có hai trường: một trường ID và một trường tên.
Bảng edge lưu trữ ID của hai node mà có quan hệ liền kề (được liên kết trực tiếp) với nhau.

Thiết lập các bảng

Tạo bảng node với 2 cột: node_id và node_name:

CREATE TABLE node (
    node_id INTEGER PRIMARY KEY,
    node_name VARCHAR NOT NULL
);

Tạo bảng edge với 2 cột: node1 và node2, cả hai đều là khóa ngoại tham chiếu đến bảng node:

CREATE TABLE edge (
    node1 INTEGER REFERENCES node (node_id),
    node2 INTEGER REFERENCES node (node_id),
    PRIMARY KEY (node1, node2)
);

Thêm dữ liệu thử nghiệm

Tạo một số node (đại diện cho con người):

INSERT INTO node (node_id, node_name) 
VALUES (1, 'Tom'), (2, 'Dick'), (3, 'Harry'), (4, 'Jane'), 
    (5, 'Susan'), (6, 'Mary'), (7, 'Sam'), (8, 'Sally'), (9, 'Jack') ;

Tạo các cạnh (edge) để xác định mối quan hệ giả định giữa các node:

INSERT INTO edge (node1, node2) 
VALUES (1, 2), (1, 8), (2, 3), (2, 4), (4, 5), (4, 6), (4, 7), (8, 9) ;

Giả sử:

Tom được liên kết với Dick và Sally;
Dick được liên kết với Harry và Jane;
Jane được liên kết với Susan, Mary, và Sam;
Sally được liên kết với Jack.

Khuyến nghị bạn nên vẽ sơ đồ mối quan hệ dưới dạng cây (trên giấy), sử dụng cả ID và tên.

Sử dụng CTE với truy vấn SELECT

Bắt đầu với View

Tạo một view có tên là my_view để hiển thị tên của các node (con người) và ID của các node liền kề được kết nối qua một cạnh đơn:

CREATE VIEW my_view AS
    SELECT n.node_name, e.node2
    FROM node n
    JOIN edge e 
    ON n.node_id = e.node1;

Sau đó, JOIN view này với bảng node để hiển thị tên của các cặp node liền kề:

SELECT 
    my_view.node_name AS node1_name, 
    node.node_name AS node2_name
FROM my_view 
JOIN node
ON my_view.node2 = node.node_id;

Truy vấn trên sẽ xuất ra 2 cột chứa tên của các cặp node liền kề.

Sử dụng CTE thay cho View

Một CTE tạo ra một truy vấn con có tên và sử dụng nó sau đó trong câu lệnh chính.

Viết lại ví dụ trên sử dụng CTE. Tạo một CTE có tên my_cte được định nghĩa tương tự như view my_view. Sau đó JOIN nó với bảng node để lấy tên của các cặp node liền kề:

WITH my_cte AS (
    SELECT n.node_name, e.node2
    FROM node n
    JOIN edge e 
    ON n.node_id = e.node1
)
SELECT 
    my_cte.node_name AS node1_name, 
    node.node_name AS node2_name
FROM my_cte 
JOIN node 
ON my_cte.node2 = node.node_id;

Lưu ý rằng phần định nghĩa của CTE (truy vấn SELECT bên trong dấu ngoặc đơn) giống hệt định nghĩa của view my_view trong phần trước. Kết quả truy vấn này sẽ giống như ví dụ trước sử dụng view.

Truy vấn con bên trong dấu ngoặc đơn có thể là bất kỳ truy vấn SQL hợp lệ nào. Truy vấn con và câu lệnh chính có thể dựa trên SELECT, INSERT, UPDATE, hoặc DELETE. Phần này minh họa cách sử dụng truy vấn SELECT trong CTE; phần sau sẽ nói về các truy vấn thay đổi dữ liệu, như INSERT.

Nhiều CTE trong cùng một truy vấn

Để có nhiều CTE trong cùng một truy vấn, chỉ cần dùng một từ khóa WITH cho toàn bộ truy vấn và phân cách các định nghĩa CTE khác nhau bằng dấu phẩy. Ví dụ, bắt đầu với ví dụ trước, chuyển phần thứ hai của truy vấn (sau định nghĩa của cte1) vào một CTE khác:

WITH
cte1 AS (
    SELECT n.node_name, e.node2
    FROM node n
    JOIN edge e 
    ON n.node_id = e.node1
)
,cte2 AS (
    SELECT 
        cte1.node_name AS node1_name, 
        node.node_name AS node2_name
    FROM cte1 
    JOIN node 
    ON cte1.node2 = node.node_id
)
SELECT * FROM cte2;

Kết quả truy vấn sẽ giống như ví dụ ở phần trước.

Lưu ý rằng CTE thứ hai (cte2) truy cập kết quả của CTE thứ nhất (cte1) trong định nghĩa của nó.

CTE lồng nhau

Bạn cũng có thể sử dụng một CTE bên trong định nghĩa của một CTE khác. Ví dụ, gói gọn cả 2 CTE ở ví dụ trên bên trong một CTE thứ ba:

WITH 
cte3 AS (
    WITH
    cte1 AS (
        SELECT n.node_name, e.node2
        FROM node n
        JOIN edge e 
        ON n.node_id = e.node1
    )
    ,cte2 AS (
        SELECT 
            cte1.node_name AS node1_name, 
            node.node_name AS node2_name
        FROM cte1 
        JOIN node 
        ON cte1.node2 = node.node_id
    )
    SELECT * FROM cte2
)
SELECT * FROM CTE3;

Lưu ý: Ví dụ này không mang lại lợi ích thực tiễn bổ sung trong trường hợp cụ thể mà chỉ nhằm minh họa ý tưởng của CTE lồng nhau.

View trên CTEs

Định nghĩa một view dựa trên một CTE giống như tạo view trên một truy vấn thông thường. Định nghĩa view nodes_n_edges chứa tên và ID của các cặp node liền kề:

CREATE VIEW nodes_n_edges AS
    WITH cte1 AS (
        SELECT n.node_id, n.node_name, e.node2
        FROM node n
        JOIN edge e 
        ON n.node_id = e.node1
    )
    SELECT 
        cte1.node_id AS node1_id, 
        cte1.node_name AS node1_name, 
        node.node_name AS node2_name,
        node.node_id AS node2_id
    FROM cte1 
    JOIN node 
    ON cte1.node2 = node.node_id;

View này sẽ được sử dụng ở các phần sau để tiện cho việc kiểm tra (hoặc kiểm tra lại) cấu trúc dữ liệu sau khi có các thay đổi.

SELECT * FROM nodes_n_edges;

CTE với các truy vấn thay đổi dữ liệu

Giả sử bạn cần thêm một node mới. Node mới này đại diện cho Jill và được liên kết với node của Jack.

Chèn một bản ghi mới vào bảng node với ID là 10 và tên là Jill:

INSERT INTO node (node_id, node_name) VALUES (10, 'Jill') ;

Sử dụng 2 CTE để lần lượt lấy ID của Jack và Jill dựa trên tên của họ. Sau đó, dùng các ID này trong truy vấn INSERT để tạo một bản ghi vào bảng edge:

WITH 
node1_id AS (
    SELECT node_id 
    FROM node
    WHERE node_name = 'Jack'
)
,node2_id AS (
    SELECT node_id
    FROM node
    WHERE node_name = 'Jill'
)
INSERT INTO edge (node1, node2)
VALUES
    ((SELECT node_id FROM node1_id), (SELECT node_id FROM node2_id)) ;

Kiểm tra lại view nodes_n_edges để xác nhận rằng Jill đã được thêm và Jack được liên kết với Jill:

SELECT * FROM nodes_n_edges;

CTE với INSERT…RETURNING

Trong ví dụ trước, khi tạo một node mới:

Trường ID (trong bảng node) được chọn thủ công và chèn vào.
Bản ghi trong bảng khác (edge) được chèn sau đó qua một truy vấn riêng biệt.

Trong thực tế, thường xảy ra trường hợp ID được tự động sinh ra khi một dòng mới được chèn vào. ID tự động sinh này sau đó được dùng để tạo các dòng mới trong các bảng khác. Tất cả cần phải xảy ra trong một giao dịch duy nhất. Đây là điều khó khăn khi sử dụng kiểu truy vấn như ví dụ trước. Và đó chính là lúc từ khóa RETURNING phát huy tác dụng. Từ khóa RETURNING trong PostgreSQL trả về giá trị từ các truy vấn thay đổi dữ liệu.

Khi áp dụng CTEs trong PostgreSQL, bạn cần một môi trường lưu trữ ổn định để thử nghiệm. Tìm hiểu giá thuê VPS hợp lý và các gói dịch vụ phù hợp để triển khai cơ sở dữ liệu hiệu quả tại DataOnline.

Tạo mô hình dữ liệu khác với bảng có trường ID tự động sinh

Mô hình dữ liệu ví dụ 2

Xét một mô hình dữ liệu gồm 2 bảng: business và address.

Bảng business có 2 cột: business_id và business_name. Trường business_id là số tự tăng được sinh ra tự động.
Bảng address có 2 cột: business_id (khóa ngoại tham chiếu đến bảng business) và address (trường ký tự dùng để lưu địa chỉ của doanh nghiệp).

Với mỗi tài khoản doanh nghiệp mới, một bản ghi được chèn vào bảng business. Bảng sẽ tự động sinh ra ID. Truy vấn INSERT…RETURNING sẽ trả về ID tự sinh của doanh nghiệp mới. ID này được sử dụng trong truy vấn con tiếp theo để tạo một bản ghi mới trong bảng address. CTE với mệnh đề INSERT…RETURNING cung cấp một cách tiện lợi để xử lý tất cả trong một truy vấn duy nhất.

Tạo bảng business:

CREATE TABLE business (
    business_id SERIAL PRIMARY KEY, 
    business_name VARCHAR(20)
);

Tạo bảng address:

CREATE TABLE address (
    business_id INTEGER PRIMARY KEY REFERENCES business (business_id), 
    business_address VARCHAR (200)
);

Truy vấn với ID tự động sinh

Trong truy vấn dưới đây, CTE đầu tiên chèn một dòng mới vào bảng business và trả về ID tự sinh. CTE thứ hai sử dụng ID đó để chèn một dòng mới vào bảng address.

WITH 
    new_business AS (
        INSERT INTO business (business_name)
        VALUES ('City Bakery')
        RETURNING business_id, business_name
    ),
    new_address AS (
        INSERT INTO address (business_id, business_address)
        SELECT business_id, 'address line 1' FROM new_business
        RETURNING business_id, business_address
    )
SELECT 
    new_business.business_id, new_business.business_name, new_address.business_address
FROM new_address
JOIN new_business
ON new_business.business_id = new_address.business_id ;

Trong truy vấn này, các trường business_name và business_address được mã hóa cứng (hard-coded). Trong thực tế, chúng thường được truyền vào dưới dạng đầu vào (ví dụ, từ một người dùng tạo tài khoản mới trên giao diện web). Truy vấn trên trả về ID tự sinh kèm theo tên và địa chỉ của doanh nghiệp mới.

Đi xa hơn: Recursive CTEs

Hướng dẫn này đã bàn về các CTE thông thường (không đệ quy), rất hữu ích trong việc cải thiện cấu trúc mã và khả năng đọc. Trong các bảng có trường tự động sinh, như ID, CTE cũng hữu ích để chèn các dòng mới trong một truy vấn duy nhất. Tài liệu PostgreSQL còn trình bày nhiều trường hợp sử dụng phức tạp và thú vị của CTE cũng như giải thích cơ chế hoạt động của nhiều tính năng.

Tài liệu, Hướng dẫn sử dụng Linux

Tìm Hiểu Cách Sử Dụng Common Table Expressions (CTEs) Trong PostgreSQL