Hướng Dẫn So Sánh Subqueries, CTEs, Temp Tables, Views và Materialized Views trong AWS Aurora

Trong các ứng dụng hiện đại dựa trên dữ liệu, hiệu suất và tính dễ đọc của các truy vấn SQL có thể ảnh hưởng đến hiệu suất, khả năng bảo trì và năng suất của lập trình viên. AWS Aurora, dịch vụ cơ sở dữ liệu quan hệ hoàn toàn được quản lý tương thích với MySQL và PostgreSQL, cung cấp nhiều kỹ thuật để quản lý độ phức tạp của truy vấn và tối ưu hóa hiệu suất thông qua: Subqueries, Common Table Expressions (CTEs), Temporary Tables, Views, và Materialized Views.

Mỗi phương pháp này phục vụ các trường hợp sử dụng khác nhau, từ việc đơn giản hóa logic lồng nhau đến việc tổ chức các truy vấn có thể tái sử dụng cho đến việc lưu trữ các kết quả được tính toán trước.

Trong bài viết này, chúng ta sẽ tìm hiểu về định nghĩa, so sánh, trường hợp sử dụng thực tế và ví dụ trong AWS Aurora, giúp bạn phân biệt chúng và trả lời khi nào và tại sao nên sử dụng chúng.

1. Subquery

Subquery là một truy vấn lồng trong một truy vấn khác, được thực thi từ truy vấn bên trong ra bên ngoài trong quá trình thực thi và tạo ra các kết quả tạm thời.

Subquery thường được sử dụng trong việc lọc hoặc chuyển đổi đơn giản, đặc biệt khi kết quả trung gian là không cần thiết.

Ví dụ dưới đây minh họa cách viết Subquery trong Aurora:

-- Tìm các phòng ban có mức lương trung bình vượt quá 80,000
SELECT department_id, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg
WHERE avg_salary > 80000;

Như đã biết, đối với bất kỳ logic lồng nhau nào, nếu không được tối ưu hóa tốt, nó có thể dẫn đến các vấn đề về hiệu suất. Tuy nhiên, bộ tối ưu truy vấn của Aurora có thể xử lý nhiều trường hợp một cách hiệu quả, nhưng việc lồng nhau quá mức có thể làm giảm hiệu suất.

2. Common Table Expression (CTE)

Khi gặp phải mã lặp lại, giải pháp ban đầu thường là "định nghĩa một lần, sử dụng nhiều lần", và đó chính xác là những gì CTE thực hiện. CTE là một tập hợp kết quả tạm thời có tên được định nghĩa bằng câu lệnh "WITH", và giống như Subquery, cả hai đều tồn tại trong quá trình thực thi truy vấn.

Nếu chúng ta lấy ví dụ trên và viết lại bằng CTE, nó sẽ như sau:

-- Tìm các phòng ban có mức lương trung bình vượt quá 80,000 
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_id, d.avg_salary
FROM dept_avg d
WHERE d.avg_salary > 80000;

Có thể thấy rằng cách tiếp cận CTE dễ đọc và được tổ chức hơn. Giá trị của nó xuất hiện rõ ràng hơn trong các truy vấn phức tạp hoặc đệ quy và khi kết hợp nhiều tập kết quả, khiến nó được khuyến nghị cao hơn so với Subquery trong các trường hợp đơn giản.

3. Temporary Table

Để chuyển từ các phương pháp cấp độ thực thi sang thời gian tồn tại cấp phiên, các bảng tạm thời là lựa chọn hợp lý. Đây là một bảng được tạo ra một cách rõ ràng và chỉ tồn tại trong suốt thời gian phiên, phục vụ khi tập kết quả cần được tái sử dụng nhiều lần trong cùng một phiên. Phương pháp này cải thiện hiệu suất khi làm việc với các tập dữ liệu lớn, nhưng yêu cầu nhiều quyền hơn so với Subqueries/CTEs, nghĩa là cần có quyền bổ sung để tạo bảng hoặc thay đổi đối tượng cơ sở dữ liệu/schema, không giống như những phương pháp khác chỉ cần quyền SELECT.

Dưới đây là một ví dụ để làm rõ cách sử dụng phương pháp này để tính toán cùng một kết quả như các truy vấn trước:

-- Ví dụ về bảng tạm (tồn tại trong suốt phiên)
CREATE TEMP TABLE dept_avg_temp AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Bạn có thể thực hiện nhiều truy vấn trên bảng tạm:
SELECT department_id, avg_salary
FROM dept_avg_temp
WHERE avg_salary > 80000;

-- (Khi hoàn thành) có thể xóa nó
DROP TABLE IF EXISTS dept_avg_temp;

4. View

"View" là một bảng ảo được định nghĩa bởi một truy vấn. Nó đơn giản hóa truy vấn nhưng không lưu trữ dữ liệu, có thể được xem như một bản thiết kế để cung cấp một lớp trừu tượng và đóng gói cho việc tái sử dụng, bên cạnh việc đơn giản hóa bảo trì và tính nhất quán, ngay cả khi không cải thiện hiệu suất.

Dưới đây là một ví dụ về cách sử dụng "views" trong Aurora:

-- Tạo một view về mức lương trung bình của các phòng ban
CREATE VIEW dept_avg_view AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Sử dụng view
SELECT *
FROM dept_avg_view
WHERE avg_salary > 80000;

5. Materialized View

"Materialized View" lưu trữ kết quả của truy vấn một cách vật lý khác với view thông thường, và được sử dụng hiệu quả cho việc tính toán các tổng hợp tốn kém (ví dụ: báo cáo dashboard) giúp tăng tốc các truy vấn lặp lại trên các tập dữ liệu lớn. Khái niệm này được hỗ trợ trong AWS Aurora PostgreSQL, nhưng không phải Aurora MySQL, nhưng có thể mô phỏng bằng một bảng và làm mới theo lịch với quyền trong cả hai phần tạo. Về phương diện lưu trữ, views chủ yếu là vĩnh viễn cho đến khi bị xóa (views/materialized views), và dữ liệu kết quả được lưu trữ cho materialized views, tần suất làm mới dữ liệu là một chủ đề gây tranh cãi và phụ thuộc nhiều vào trường hợp sử dụng, vì tần suất làm mới cao có thể gây ảnh hưởng đến hiệu suất với tập dữ liệu lớn và tần suất thấp có thể làm phá vỡ tính nhất quán của dữ liệu snapshot cũ và trở nên lạc hậu so với kết quả gần đây.

Dưới đây là một ví dụ về cách viết một "Materialized view" cho kết quả của chúng ta:

-- Tạo một materialized view 
CREATE MATERIALIZED VIEW dept_avg_mv AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Truy vấn nó như một bảng bình thường
SELECT *
FROM dept_avg_mv
WHERE avg_salary > 80000;

-- Làm mới khi dữ liệu thay đổi
REFRESH MATERIALIZED VIEW dept_avg_mv;

So sánh các phương pháp

Dưới đây là bảng tóm tắt sự khác biệt giữa các phương pháp này:

Lưu ý: Ở đây, chúng tôi đang sử dụng các ví dụ so sánh cơ bản để tập trung vào việc minh họa sự khác biệt giữa các phương pháp trên cùng một ý tưởng và kết quả. Tôi khuyến khích bạn tiến thêm một bước nữa và thử nghiệm với những phương pháp này bằng cách sử dụng các truy vấn phức tạp hơn để áp dụng thông tin này và mở rộng ý nghĩa thực sự của các yếu tố này.

Kết luận

Trong AWS Aurora, việc chọn giữa Subqueries, CTEs, Temporary Tables, Views và Materialized Views hoàn toàn là về việc cân bằng giữa tính dễ đọc, tái sử dụng và hiệu suất, dựa trên nhu cầu kinh doanh.

Quy trình chọn phương pháp tốt nhất bắt đầu với Subqueries hoặc CTEs cho sự đơn giản, như các truy vấn không thường xuyên, nhưng nếu các kết quả trung gian được sử dụng lại nhiều lần trong suốt phiên, chúng ta có thể chuyển sang Temporary Tables. Tuy nhiên, Views thì vượt trội về khả năng bảo trì và trừu tượng, nhưng không phải về tốc độ. Cuối cùng, giải pháp caching của Materialized View là rất quan trọng cho các truy vấn báo cáo có khối lượng và tính toán cao, có thể được sử dụng hiệu quả trong Aurora PostgreSQL.