Trino 6 — Tối ưu hiệu năng truy vấn

Các bài trước đã dạy cách viết truy vấn: Trino 2 — Kiến trúc mổ xẻ cách một câu SELECT thành hàng trăm task song song, Trino 4 — SQL & liên hợp dạy JOIN xuyên nguồn, Trino 5 — Truy vấn Lakehouse đào sâu quét dữ liệu trên data lake. Bài này trả lời câu hỏi thực chiến nhất: tại sao câu này chạy 4 phút mà câu kia chạy 4 giây, và làm sao sửa?

Điểm mấu chốt cần thấm ngay: hiệu năng Trino quyết định phần lớn bởi lượng dữ liệu phải quét và phải chuyển vào engine, chứ không phải bởi việc bạn viết SQL "khéo" cỡ nào. Một câu SQL "xấu" nhưng chỉ quét 2 partition sẽ luôn thắng một câu SQL "đẹp" quét cả bảng tỉ dòng. Vì vậy tối ưu Trino là chuỗi ưu tiên rõ ràng: giảm dữ liệu quét → đẩy việc xuống nguồn → chọn kiểu join đúng → cho engine đủ bộ nhớ. Với dân ngân hàng, đây là khác biệt giữa dashboard rủi ro cập nhật trong 30 giây và bản báo cáo chờ nửa buổi sáng.

Bước 0: đo trước khi sửa — EXPLAIN & Web UI

Không tối ưu bằng cảm giác. Trino cho hai công cụ chẩn đoán chính xác.

EXPLAIN in ra kế hoạch phân tán (distributed plan) — cấu trúc truy vấn được chia thành các stage, mỗi stage là một nhóm task chạy song song trên worker. Đọc plan từ dưới lên (từ lá TableScan lên gốc Output):

-- Minh hoạ (Trino) — KHÔNG đánh dấu chạy được vì đụng catalog lake/core
EXPLAIN
SELECT c.segment, SUM(t.amount) AS tong
FROM lake.txn.transactions t
JOIN core.public.customers c ON t.customer_id = c.id
WHERE t.txn_date >= DATE '2026-06-01'
GROUP BY c.segment;

Ba biến thể cần biết:

EXPLAIN ANALYZE là công cụ vàng: nó thực thi câu và gắn số liệu thật vào từng node. Bạn nhìn thấy ngay node nào nuốt phần lớn thời gian CPU, node nào đọc 1,8 tỉ dòng, cạnh join nào phình dữ liệu. Đọc plan cần tìm ba thứ:

• Số dòng ở mỗi TableScan: có đúng như kỳ vọng không? Nếu bạn tưởng câu chỉ quét 7 ngày mà scan báo 1,8 tỉ dòng → partition pruning không xảy ra, đây là nút thắt số một.
• filterPredicate / điều kiện đẩy xuống: xem WHERE của bạn có nằm ngay tại TableScan không (đã pushdown) hay nằm ở node Filter phía trên (làm trong engine, tốn hơn).
• Kiểu join & phân phối: REPLICATED (broadcast) hay PARTITIONED (shuffle) — quyết định lớn về hiệu năng, mục dưới bàn kỹ.

Trino Web UI (thường ở cổng 8080 của coordinator) bổ sung góc nhìn runtime cho câu đang/đã chạy: query timeline (thời gian từng stage), tỉ lệ CPU time vs wall time (chênh lệch lớn = chờ I/O hoặc mất cân bằng), dữ liệu đọc (input bytes/rows từng stage), và số driver/split đang chạy. Nếu một stage chiếm 90% wall time với vài split, bạn có skew (lệch dữ liệu) hoặc thiếu song song.

Đòn bẩy #1: Pushdown — đẩy việc xuống nguồn

Đây là đòn bẩy lớn nhất, đặc biệt với connector JDBC (Postgres/MySQL) và data lake. Nguyên tắc: mọi thứ Trino đẩy xuống nguồn thì nguồn tự làm và chỉ trả về ít dữ liệu; mọi thứ không đẩy được thì Trino phải kéo toàn bộ dữ liệu thô về engine rồi mới lọc. Ba loại pushdown quan trọng:

• Predicate pushdown (đẩy điều kiện lọc): WHERE t.txn_date >= DATE '2026-06-01' được đẩy xuống thành partition pruning trên lake (bỏ qua file không liên quan) hoặc mệnh đề WHERE ở Postgres. Kết quả: nguồn quét ít, trả về ít.
• Projection pushdown (đẩy chọn cột): chỉ đọc các cột thực sự dùng. Với Parquet/ORC (định dạng cột), chỉ đọc amount, customer_id thay vì cả 60 cột → giảm I/O nhiều lần. Đây là lý do SELECT * là kẻ thù: nó vô hiệu hoá projection pushdown.
• Aggregate pushdown (đẩy tổng hợp): với một số connector, COUNT/SUM/GROUP BY đơn giản được đẩy xuống nguồn tự tính. SELECT COUNT(*) FROM core.public.customers để Postgres đếm rồi trả về một con số, thay vì kéo 4 triệu dòng về engine.

Cách chặn pushdown một cách vô tình (anti-pattern hay gặp):

• Bọc cột lọc trong hàm: WHERE CAST(txn_date AS VARCHAR) = '2026-06-01' hay WHERE year(txn_date) = 2026 thường không pushdown/prune được. Hãy so sánh cột trần với hằng số: WHERE txn_date >= DATE '2026-06-01' AND txn_date < DATE '2026-07-01'.
• LIKE '%abc%' (dấu % đầu) không đẩy được như prefix match.
• Ép kiểu hai vế lệch nhau khiến nguồn không nhận diện được điều kiện.

Luôn kiểm chứng bằng EXPLAIN: nếu điều kiện xuất hiện ngay tại TableScan (thuộc tính constraint/filterPredicate) là đã pushdown; nếu nó nằm ở node ScanFilter/Filter phía trên hoặc scan báo full-table thì chưa đẩy được — sửa câu.

Đòn bẩy #2: Bố cục dữ liệu — quyết định lớn cho lake

Với data lake, cách dữ liệu nằm trên đĩa quan trọng hơn cả cách viết SQL. Bạn không thể pushdown filter hiệu quả nếu dữ liệu bố cục kém. Năm yếu tố:

• Partitioning (phân vùng) theo cột lọc phổ biến. Chia bảng giao dịch theo txn_date (và có thể branch_id) để WHERE txn_date = ... chỉ quét đúng thư mục ngày đó — partition pruning. Chọn cột phân vùng theo cách người dùng hay lọc, không phải theo cột có nhiều giá trị nhất. Tránh phân vùng quá mịn (partition theo giờ trên bảng ít dữ liệu → hàng vạn thư mục rỗng, ngược tác dụng).
• Định dạng cột Parquet/ORC. Cả hai lưu theo cột + nén + có min/max thống kê per-block (row group/stripe), cho phép predicate pushdown ở mức block: bỏ qua nguyên block nếu min/max không khớp WHERE. Tránh CSV/JSON thô cho bảng lớn — không nén cột, không pushdown block, chậm gấp bội.
• Kích thước file — tránh "small files". Hàng triệu file nhỏ (vài KB) là sát thủ hiệu năng: mỗi file tốn một lượt mở/đọc metadata, tạo quá nhiều split nhỏ, coordinator ngợp lập lịch. Mục tiêu thực tế: file ~128 MB–1 GB. Định kỳ compaction (gộp file nhỏ) — với Iceberg/Delta là thao tác chuẩn.
• Sorting/clustering trong file. Sắp xếp dữ liệu theo cột hay lọc (VD customer_id) làm min/max mỗi block hẹp lại → bỏ qua block hiệu quả hơn (data skipping). Iceberg hỗ trợ sort order; một số hệ có Z-ordering cho nhiều cột.
• Table statistics. Trino có cost-based optimizer (CBO) — bộ tối ưu dựa trên chi phí — nhưng CBO chỉ khôn khi có thống kê bảng (số dòng, số giá trị distinct, min/max, tỉ lệ NULL mỗi cột). Thiếu stats, Trino đoán mò và chọn sai thứ tự join / sai kiểu phân phối.

Đòn bẩy #3: JOIN đúng cách

JOIN là nơi câu truy vấn phân tích "sống hay chết". Trino có hai kiểu phân phối join:

• Broadcast join (REPLICATED): bảng nhỏ được sao chép nguyên vẹn tới mọi worker, bảng lớn quét song song và join tại chỗ. Nhanh vì không shuffle bảng lớn qua mạng. Điều kiện: bảng "build" (thường bên phải) đủ nhỏ để nằm gọn trong RAM một worker.
• Partitioned/distributed join (PARTITIONED): cả hai bảng được shuffle theo khoá join (băm sang worker theo cùng khoá), rồi join từng phần. Bắt buộc khi cả hai bảng đều lớn, không bảng nào broadcast nổi. Tốn băng thông mạng nhưng không tràn RAM.

Nguyên tắc thực chiến:

1. Đưa bảng nhỏ về broadcast. JOIN fact-lớn-ở-lake × dimension-nhỏ (khách hàng, sản phẩm, chi nhánh) → cho dimension làm bảng build broadcast. Đây là pattern an toàn số một cho báo cáo ngân hàng.
2. Thứ tự join quan trọng — để CBO lo, nếu có stats. Bộ tối ưu chọn thứ tự và kiểu phân phối dựa trên số dòng ước lượng. Không có stats → phải tự sắp: đặt bảng nhỏ hơn ở vị trí build. Tham số join_distribution_type và join_reordering_strategy (AUTOMATIC khi có stats) điều khiển hành vi này.
3. Dynamic filtering (lọc động) — vũ khí mạnh nhất. Trino quét bảng nhỏ trước, thu tập giá trị khoá join (VD danh sách product_id sau khi lọc), rồi đẩy tập đó xuống làm filter cho bảng lớn trước khi quét. Nghĩa là bảng fact tỉ dòng chỉ đọc đúng các partition/block khớp với dimension đã lọc. Với JOIN fact × dimension đã có WHERE mạnh trên dimension, dynamic filtering có thể cắt 90%+ dữ liệu quét. Đây là lý do đừng vội materialize — hãy để Trino lọc động.
4. Tránh cross join & join thiếu điều kiện. JOIN quên ON, hoặc điều kiện join sai kiểu, sinh tích Descartes (mỗi dòng bảng A × mỗi dòng bảng B) — bùng nổ theo cấp số nhân. Trong EXPLAIN ANALYZE, cạnh join có số dòng ra lớn hơn nhiều tích hai đầu vào hợp lý là dấu hiệu.

Đòn bẩy #4: Cost-based optimizer & ANALYZE

CBO chỉ tốt bằng chất lượng thống kê nó có. Thu thập stats bằng lệnh ANALYZE:

-- Minh hoạ (Trino) — thu thập thống kê bảng cho CBO
ANALYZE lake.txn.transactions;

Sau ANALYZE, CBO biết bảng nào lớn/nhỏ, cột nào nhiều distinct → chọn đúng thứ tự join, đúng broadcast vs partitioned, ước lượng đúng số dòng mỗi stage. Không có stats là nguyên nhân âm thầm phổ biến nhất khiến plan tồi: Trino có thể chọn shuffle một bảng đáng lẽ broadcast, hoặc join theo thứ tự làm phình dữ liệu trung gian. Với bảng lake cập nhật thường xuyên, chạy ANALYZE định kỳ (hoặc để hệ Iceberg/Delta duy trì stats) là việc bảo trì bắt buộc.

Đòn bẩy #5: Bộ nhớ, spill & resource groups

Trino xử lý trong bộ nhớ. Khi câu vượt hạn mức, nó fail chứ không âm thầm chậm — nên quản lý bộ nhớ là để câu chạy được và để cô lập workload.

• Memory limits. query.max-memory-per-node (giới hạn mỗi worker) và query.max-memory (tổng toàn cụm cho một câu) chặn một câu tham lam nuốt hết RAM cụm. Câu vượt hạn sẽ bị hủy với lỗi "Query exceeded ... memory limit".
• Spill to disk (tràn ra đĩa). Bật spill cho phép các phép nặng bộ nhớ (hash join, aggregation, sort lớn) đổ bớt dữ liệu ra đĩa cục bộ thay vì fail. Đánh đổi: chậm hơn nhiều (I/O đĩa) nhưng câu chạy xong. Dùng cho ETL nặng/báo cáo lớn, không nên là mặc định cho truy vấn tương tác.
• Query queue / Resource groups. Chia tài nguyên cụm theo nhóm workload: dành hạn mức riêng cho dashboard tương tác (ưu tiên cao, câu nhỏ nhanh) tách khỏi báo cáo ETL nặng (chạy nền, hàng đợi riêng). Điều này ngăn một câu SELECT * khổng lồ của một analyst làm treo dashboard giám sát rủi ro của cả phòng. Cấu hình bằng số câu chạy đồng thời, hạn mức bộ nhớ, độ ưu tiên cho từng nhóm.

Đòn bẩy #6: Giảm dữ liệu & mẹo tăng tốc

Những việc nhỏ nhưng cộng dồn lớn:

• Bỏ SELECT *. Chỉ chọn cột cần → bật projection pushdown, giảm I/O và dữ liệu shuffle. Quy tắc cứng cho mọi truy vấn production trên lake.
• Lọc và LIMIT sớm. Đẩy WHERE mạnh nhất có thể; LIMIT giúp Trino dừng sớm với một số nguồn.
• Approximate functions. approx_distinct(customer_id) (HyperLogLog) thay COUNT(DISTINCT ...) và approx_percentile(amount, 0.95) (t-digest) thay percentile chính xác — nhanh nhiều lần trên hàng tỉ dòng, sai số ~2%. Với dashboard quản trị ("số khách hoạt động", "p95 số tiền"), sai số này chấp nhận được.
• Caching. Trino có nhiều lớp cache tùy phiên bản/connector: cache metadata (thông tin file/partition, đỡ liệt kê object storage nhiều lần) và các cơ chế cache dữ liệu/kết quả trung gian. Cache metadata đặc biệt giá trị khi bảng có rất nhiều file trên object storage chậm.

Anti-pattern & cách chẩn đoán query chậm

Bảng "khám bệnh" nhanh — triệu chứng trong EXPLAIN ANALYZE/Web UI và nguyên nhân:

SQL "▶ Chạy được" — Đọc kế hoạch với EXPLAIN

Câu dưới chạy được trên sandbox PostgreSQL (một câu EXPLAIN đơn cho một SELECT hợp lệ trên đúng 5 bảng). Trên Trino cú pháp EXPLAIN <select> là như nhau; điểm khác là plan sẽ hiện thêm node phân tán và pushdown. Mục đích: tập thói quen luôn EXPLAIN trước khi chạy câu lớn — đọc plan để thấy join, filter và ước lượng dòng ở mỗi node:

-- ▶ Chạy được
EXPLAIN
SELECT c.city,
       a.currency,
       COUNT(t.id)                 AS so_giao_dich,
       ROUND(SUM(t.amount)::numeric, 2) AS tong_tien
FROM transactions t
JOIN accounts  a ON t.account_id = a.id
JOIN customers c ON a.customer_id = c.id
WHERE t.kind = 'debit'
  AND t.created_at >= TIMESTAMP '2026-01-01 00:00:00'
GROUP BY c.city, a.currency
ORDER BY tong_tien DESC;

Đọc plan từ dưới lên: xem điều kiện t.kind = 'debit' và mốc thời gian có được áp ngay tại bước quét transactions không (dấu hiệu filter đẩy sát nguồn), kiểu join giữa ba bảng, và ước lượng số dòng mỗi node. Trên PostgreSQL đây là kế hoạch executor cục bộ; trên Trino, chính tư duy đọc plan này giúp bạn phát hiện: scan quét quá nhiều dòng, filter chưa pushdown, hay một cạnh join phình dữ liệu — trước khi câu chạy tốn tài nguyên cụm.

Use case thực tế

Bối cảnh (NCB): Dashboard giám sát rủi ro cần câu: "Tổng dư nợ thẻ và p95 số tiền giao dịch 30 ngày qua theo chi nhánh và phân khúc". Bảng transactions trên data lake (~1,8 tỉ dòng/tháng, Parquet), phân vùng theo txn_date. Câu đầu tiên chạy 4 phút 10 giây — quá chậm cho dashboard.

Chẩn đoán bằng EXPLAIN ANALYZE + Web UI:

1. TableScan trên transactions báo 1,8 tỉ dòng dù chỉ cần 30 ngày → partition pruning thất bại. Nguyên nhân: điều kiện viết WHERE substr(txn_date_str, 1, 7) = '2026-06' (bọc hàm) nên không prune được.
2. Bảng dimension customers (~4 triệu dòng) bị PARTITIONED shuffle thay vì broadcast → tốn mạng. Nguyên nhân: chưa chạy ANALYZE, CBO không biết nó đủ nhỏ để broadcast.
3. Web UI cho thấy tỉ lệ CPU time / wall time thấp → nhiều thời gian chờ đọc small files (một số ngày có hàng chục nghìn file nhỏ chưa compaction).

Cách sửa:

1. Viết lại điều kiện thành cột trần: WHERE txn_date >= current_date - INTERVAL '30' DAY → partition pruning chỉ quét ~30 partition (~420 triệu dòng).
2. Chạy ANALYZE lake.txn.transactions và bảng dimension → CBO chuyển customers sang broadcast, kích hoạt dynamic filtering lọc bảng fact theo tập khoá dimension đã lọc.
3. Compaction gộp small files về ~256 MB; đổi SELECT * thành đúng 6 cột cần.
4. Dùng approx_percentile(amount, 0.95) thay percentile chính xác.

Kết quả: câu xuống còn ~28 giây — nhanh gấp ~9 lần, đủ cho dashboard tương tác. Không đổi một dòng logic nghiệp vụ nào; toàn bộ là giảm dữ liệu quét + pushdown + join đúng kiểu + stats. Kỹ thuật tương tự áp dụng cho engine khác — xem Spark 5 — Tuning và dbt 6 — Incremental & performance.

Ghi nhớ

• Tối ưu Trino theo thứ tự ưu tiên: giảm dữ liệu quét → pushdown → join đúng kiểu → đủ bộ nhớ. Lượng dữ liệu quét quan trọng hơn "mẹo SQL".
• Luôn đo trước khi sửa: EXPLAIN/EXPLAIN ANALYZE (số dòng, CPU/wall mỗi node, pushdown) và Web UI (timeline, stage, dữ liệu đọc, skew).
• Pushdown là đòn bẩy lớn nhất: predicate + projection (+ aggregate) đẩy xuống nguồn giảm mạnh dữ liệu vào engine. Đừng bọc cột lọc trong hàm — sẽ chặn pruning/pushdown.
• Bố cục dữ liệu quyết định cho lake: partition theo cột hay lọc, Parquet/ORC, tránh small files (nhắm ~128MB–1GB), sort/cluster, và table stats cho CBO.
• JOIN: broadcast bảng nhỏ (fact-lớn × dimension-nhỏ), partitioned khi cả hai lớn; dynamic filtering lọc bảng lớn theo bảng nhỏ; tránh cross join.
• ANALYZE thu thập thống kê — thiếu stats là nguyên nhân âm thầm khiến CBO chọn plan tồi.
• Bộ nhớ: memory limit + spill để câu lớn chạy xong; resource groups cô lập dashboard tương tác khỏi ETL nặng.
• Giảm dữ liệu: bỏ SELECT *, lọc/LIMIT sớm, approximate functions (approx_distinct/approx_percentile) cho tốc độ, tận dụng caching metadata.