Trino SQL Bài học 37: Phân tích các loại Truy vấn lồng (Subqueries) và rủi ro hiệu năng

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

• Phân loại Truy vấn lồng dựa trên sự phụ thuộc dữ liệu: Độc lập (Uncorrelated) và Tương quan (Correlated).

• Hiểu rõ cơ chế hoạt động của các toán tử đi kèm Subquery: IN, EXISTS, ANY, ALL.

• Nhận diện "sát thủ hiệu năng" Correlated Subquery trong môi trường tính toán phân tán.

• Nắm vững kỹ thuật Decorrelation (Giải phụ thuộc) để viết lại Subquery thành JOIN tối ưu.

1. Subquery Độc lập (Uncorrelated Subquery)

Đây là loại truy vấn con "lành tính" nhất. Nó có thể đứng một mình, chạy thử độc lập mà không cần quan tâm đến câu truy vấn bên ngoài (Outer Query).

Cơ chế hoạt động: Trino sẽ chạy Subquery này đúng một lần duy nhất, lưu kết quả vào bộ nhớ, rồi lấy kết quả đó làm hằng số để chạy câu truy vấn bên ngoài.

Ứng dụng thực tế: Lọc dữ liệu bằng một con số động. Giả sử bạn muốn tìm các đơn hàng có giá trị lớn hơn mức trung bình của toàn công ty. Bạn không thể biết mức trung bình là bao nhiêu cho đến khi tính nó ra.

SQL

SELECT 
    orderkey, 
    totalprice
FROM tpch.tiny.orders
WHERE totalprice > (
    -- Subquery Độc lập: Chạy 1 lần duy nhất để ra một con số (Ví dụ: 150000)
    SELECT AVG(totalprice) FROM tpch.tiny.orders
);

(Loại Subquery trả về đúng 1 dòng, 1 cột như trên được gọi là Scalar Subquery).

2. Làm việc với tập hợp: IN và EXISTS

Nếu Subquery trả về một danh sách nhiều dòng (thay vì 1 con số), bạn không thể dùng dấu > hay =. Bạn phải dùng IN hoặc EXISTS.

• Dùng IN (So sánh giá trị): Quét toàn bộ danh sách trả về để xem giá trị có nằm trong đó không. Rất tốt cho danh sách nhỏ.

SQL

-- Tìm khách hàng thuộc các quốc gia ở Châu Á
SELECT name 
FROM tpch.tiny.customer
WHERE nationkey IN (
    SELECT nationkey FROM tpch.tiny.nation WHERE regionkey = 3 -- 3 là ASIA
);

• Dùng EXISTS (Kiểm tra tồn tại): Không quan tâm giá trị trả về là gì, chỉ cần Subquery có trả về ít nhất 1 dòng là phép thử trả về TRUE. Trino tối ưu EXISTS rất tốt vì nó sử dụng cơ chế "Short-circuit" (thấy 1 dòng là dừng quét ngay).

3. Subquery Tương quan (Correlated Subquery)

Đây là loại Subquery nguy hiểm nhất. Khác với truy vấn độc lập, Subquery Tương quan bị trói buộc với truy vấn bên ngoài. Nó chứa một cột thuộc về bảng bên ngoài, do đó nó không thể đứng độc lập để chạy thử.

Cơ chế hoạt động (Ác mộng vòng lặp): Hệ thống sẽ lấy dòng thứ 1 của bảng ngoài, truyền tham số vào Subquery, chạy Subquery 1 lần. Lấy dòng thứ 2 của bảng ngoài, lại truyền tham số, chạy Subquery lần 2. Nếu bảng ngoài có 1 tỷ dòng, Subquery sẽ bị ép phải chạy 1 tỷ lần.

Ví dụ về Correlated Subquery đặt ở mệnh đề SELECT:

SQL

SELECT 
    c.name,
    (
        -- Subquery này phụ thuộc vào c.custkey của bảng bên ngoài
        SELECT SUM(o.totalprice) 
        FROM tpch.tiny.orders AS o 
        WHERE o.custkey = c.custkey
    ) AS lifetime_value
FROM tpch.tiny.customer AS c;

4. Góc nhìn Kỹ sư: Rủi ro trên hệ thống Phân tán (Trino)

Trong các cơ sở dữ liệu truyền thống (OLTP) như MySQL hay SQL Server, với vài vạn dòng dữ liệu và Index đánh đầy đủ, Correlated Subquery vẫn chạy mượt mà.

Nhưng Trino là hệ thống OLAP phân tán (Distributed Engine). Triết lý của Trino là chia nhỏ khối dữ liệu khổng lồ ra cho nhiều Worker xử lý song song (Batch Processing). Correlated Subquery ép hệ thống phải thực hiện hành vi Row-by-Row (Xử lý từng dòng một - Nested Loop). Điều này phá vỡ hoàn toàn kiến trúc xử lý song song của Trino, gây ra thắt cổ chai CPU (CPU Bottleneck) trầm trọng, thậm chí báo lỗi Query Exceeded Maximum Time Limit.

Dù Trino Optimizer dưới nền có các thuật toán "Decorrelation" (tự động biến Subquery thành Join), nhưng với các logic phức tạp, Optimizer sẽ chịu thua và trả về lỗi.

5. Kỹ thuật Decorrelation (Giải phụ thuộc)

Quy tắc vàng của Data Engineer: Hãy tìm mọi cách viết lại Correlated Subquery thành mệnh đề JOIN kết hợp GROUP BY.

Chúng ta sẽ cấu trúc lại ví dụ ở phần 3 để nó chạy cực nhanh trên Trino:

SQL

-- CÁCH VIẾT TỐI ƯU (DÙNG LEFT JOIN VÀ GROUP BY)
SELECT 
    c.name,
    SUM(o.totalprice) AS lifetime_value
FROM tpch.tiny.customer AS c
LEFT JOIN tpch.tiny.orders AS o 
    ON c.custkey = o.custkey
GROUP BY 
    c.custkey, -- Luôn Group By Khóa chính để đảm bảo an toàn
    c.name;

Tại sao cách này nhanh? Trino sẽ dùng Hash Join để nối 2 bảng trong RAM một cách song song trên toàn bộ Cluster (xử lý hàng triệu dòng cùng lúc), thay vì phải lặp đi lặp lại hàm SUM cho từng khách hàng nhỏ lẻ.