Trino SQL Bài học 36: Tối ưu hóa truy vấn phức tạp với CTE (mệnh đề WITH)

Mục tiêu bài học:

• Nhận diện "nỗi đau" của Truy vấn lồng (Subquery Hell) khiến mã nguồn trở thành "Spaghetti Code".

• Nắm vững cú pháp của CTE (Common Table Expression) thông qua mệnh đề WITH.

• Thay đổi tư duy viết SQL từ "Trong ra ngoài" (Inside-out) sang "Từ trên xuống dưới" (Top-down) thuận với luồng tư duy con người.

• Góc nhìn Kỹ sư: Hiểu rõ cơ chế "Inlining" (Chèn mã nội tuyến) của Trino để không ngộ nhận về hiệu năng của CTE.

1. Nỗi đau mang tên "Truy vấn lồng" (Subquery Hell)

Trong phân tích dữ liệu, bạn hiếm khi chỉ dùng một lệnh SELECT là ra ngay kết quả. Thường thì bạn phải lọc một bảng, gom nhóm nó, rồi lại lấy kết quả đó đi JOIN với một bảng khác đã được lọc.

Cách truyền thống là viết Truy vấn lồng (Subquery): Đặt một câu SELECT bên trong mệnh đề FROM của một câu SELECT khác.

Ví dụ "Ác mộng":

SQL

-- Đọc câu SQL này, bạn phải dò từ lõi trong cùng ra ngoài, rất khó bảo trì
SELECT 
    c.mktsegment, 
    SUM(o.total_revenue)
FROM (
    SELECT custkey, SUM(totalprice) AS total_revenue
    FROM tpch.tiny.orders
    WHERE orderdate >= DATE '1995-01-01'
    GROUP BY custkey
) AS o
JOIN (
    SELECT custkey, mktsegment
    FROM tpch.tiny.customer
    WHERE acctbal > 0
) AS c ON o.custkey = c.custkey
GROUP BY c.mktsegment;

Khi các lớp lồng nhau đạt đến cấp độ 3 hoặc 4, việc đọc hiểu và gỡ lỗi (debug) một câu SQL trở thành cực hình.

2. Giải pháp: Khái niệm CTE và mệnh đề WITH

CTE (Common Table Expression - Biểu thức bảng chung) sinh ra để giải cứu bạn. Nó cho phép bạn định nghĩa các câu truy vấn con thành các Bảng ảo tạm thời, đặt cho chúng một cái tên dễ nhớ, và ghim chúng lên trên cùng của tập lệnh.

Cú pháp: Dùng từ khóa WITH, các CTE được cách nhau bởi dấu phẩy.

SQL

-- Chuyển đổi ví dụ trên sang cấu trúc CTE
WITH recent_orders AS (
    -- Bước 1: Tính tổng doanh thu từng khách hàng từ 1995
    SELECT custkey, SUM(totalprice) AS total_revenue
    FROM tpch.tiny.orders
    WHERE orderdate >= DATE '1995-01-01'
    GROUP BY custkey
),
good_customers AS (
    -- Bước 2: Lấy danh sách khách hàng không nợ xấu
    SELECT custkey, mktsegment
    FROM tpch.tiny.customer
    WHERE acctbal > 0
)
-- Bước 3: Ráp các mảnh ghép lại ở Truy vấn chính (Main Query)
SELECT 
    c.mktsegment, 
    SUM(o.total_revenue)
FROM recent_orders AS o
JOIN good_customers AS c ON o.custkey = c.custkey
GROUP BY c.mktsegment;

Lợi ích rõ rệt: Mã nguồn bây giờ chảy từ trên xuống dưới (Top-down) hệt như một câu chuyện. Kỹ sư tiếp nhận code của bạn có thể chạy thử từng khối CTE độc lập để kiểm tra dữ liệu trước khi chạy toàn bộ.

3. Khả năng tái sử dụng (Reusability)

Một siêu năng lực khác của CTE là khả năng tái sử dụng. Nếu bạn định nghĩa một Subquery ở mệnh đề FROM, bạn chỉ dùng được nó đúng 1 lần. Nếu muốn dùng lại, bạn phải copy/paste cả đoạn code đó.

Với CTE, bạn khai báo 1 lần ở đầu, và có thể gọi nó nhiều lần trong truy vấn chính (ví dụ khi kết hợp với UNION ALL hoặc Self Join).

SQL

WITH top_10_customers AS (
    SELECT custkey, SUM(totalprice) AS total_spent
    FROM tpch.tiny.orders
    GROUP BY custkey
    ORDER BY total_spent DESC
    LIMIT 10
)
-- Tái sử dụng bảng ảo top_10_customers 2 lần
SELECT 'Top 1' AS rank, custkey, total_spent 
FROM top_10_customers 
ORDER BY total_spent DESC LIMIT 1

UNION ALL

SELECT 'Top 10', custkey, total_spent 
FROM top_10_customers 
ORDER BY total_spent ASC LIMIT 1;

4. GÓC NHÌN KỸ SƯ: Cạm bẫy "Inlining" trên Trino

Nhiều Data Engineer chuyển từ PostgreSQL hay SQL Server sang Trino thường mang theo một "Ngộ nhận nguy hiểm" về CTE.

Sự ngộ nhận: "Nếu tôi gọi bảng ảo top_10_customers 2 lần trong truy vấn chính, Trino sẽ tính toán CTE đó 1 lần, lưu vào RAM, và tái sử dụng kết quả từ RAM cho 2 lần gọi. Vậy là tối ưu hiệu năng!"

Sự thật phũ phàng trong Trino: Trong kiến trúc của Trino (và Presto), mặc định CTE được xử lý như một dạng Macro thay thế văn bản (Inlining). Trino Engine KHÔNG lưu (materialize) kết quả của CTE vào bộ nhớ. Nếu bạn gọi CTE đó 3 lần ở truy vấn chính, Trino sẽ âm thầm copy toàn bộ đoạn mã SQL bên trong CTE đó và dán vào 3 vị trí tương ứng, sau đó bắt các Worker quét bảng gốc và tính toán lại từ đầu 3 lần.

• Bài học rút ra 1: CTE trong Trino chủ yếu mang lại lợi ích về Mặt logic và khả năng bảo trì Code (Readability), chứ không tự động tối ưu hóa tài nguyên tính toán như các RDBMS truyền thống.

• Bài học rút ra 2: Đừng lạm dụng CTE để bọc các bảng khổng lồ (hàng tỷ dòng) nếu bạn định gọi lại nó nhiều lần trong một truy vấn. Trong trường hợp đó, hãy cân nhắc tạo một bảng vật lý tạm thời (Table) thật sự.