BigQuery 11 — Kỹ thuật tối ưu truy vấn

Vì sao cần một "hộp công cụ" tối ưu

Ở các bài trước bạn đã thấy từng mảnh: định dạng cột giúp chỉ đọc cột cần (BigQuery — Storage & định dạng cột), phân vùng cắt tỉa dữ liệu theo ngày (Partitioning), phân cụm gom dữ liệu theo cột lọc (Clustering). Bài này ráp chúng thành một hộp công cụ và bổ sung các kỹ thuật ở tầng câu query: khi bạn ngồi trước một câu SQL chạy chậm hoặc đắt, bạn cần biết rút công cụ nào ra và vì sao.

Mô hình tinh thần chủ đạo: một câu query BigQuery tốn tiền và thời gian ở ba chỗ — bytes đọc vào (scan), dữ liệu chuyển giữa các stage (shuffle), và tính toán trên slot (compute). Gần như mọi kỹ thuật dưới đây đều tấn công một trong ba chỗ này. Trước khi tối ưu, hãy luôn đo trước bằng dry-run và đọc query plan — "đoán mò rồi sửa" là cách lãng phí thời gian nhất; cách đọc plan để biết nút thắt ở đâu xem BigQuery — Chẩn đoán & tinh chỉnh.

Sơ đồ trên là cây quyết định: đi từ triệu chứng (chậm/đắt) tới nhóm kỹ thuật. Phần còn lại của bài đi qua từng nhánh theo cấu trúc vấn đề → cách làm → hiệu quả.

1. Tránh SELECT * — chỉ đọc cột cần

Vấn đề. BigQuery lưu cột (Capacitor trên Colossus), nên SELECT * buộc đọc mọi cột, kể cả cột STRING mô tả dài hay cột JSON nặng bạn chẳng dùng. Bytes đọc vào = tiền (on-demand) và slot-time (compute).

Cách làm. Liệt kê đích danh cột cần. Nếu bảng nhiều cột và bạn thật sự cần "gần hết", dùng SELECT * EXCEPT(cot_nang_1, cot_nang_2) để loại các cột lớn. Tránh SELECT * trong subquery/CTE trung gian vì nó kéo cột thừa qua cả chuỗi stage.

Hiệu quả. Trên bảng rộng, chỉ đọc 3–4 cột thay vì toàn bộ có thể giảm bytes quét hàng chục lần — đây là tối ưu rẻ nhất và tác động lớn nhất.

2. Filter & prune sớm — partition + cluster

Vấn đề. Đọc cả bảng rồi mới lọc là lãng phí. Với BigQuery, "lọc sớm" nghĩa là để engine loại bỏ dữ liệu trước cả khi đọc.

Cách làm.

• Partition pruning: đặt điều kiện WHERE trực tiếp trên cột phân vùng (thường là cột ngày), bằng hằng số hoặc biểu thức tất định. BigQuery bỏ qua toàn bộ partition không khớp trước khi tính bytes. Chi tiết ở Partitioning.
• Block pruning bằng cluster: trên bảng đã phân cụm, WHERE/JOIN trên cột cluster giúp BigQuery bỏ qua các block không chứa giá trị cần. Chi tiết ở Clustering.
• Đặt filter chọn lọc nhất càng sớm càng tốt trong query, trước join.

Cạm bẫy. Bọc cột phân vùng trong hàm (WHERE DATE(txn_ts) = ... khi phân vùng theo txn_ts) có thể vô hiệu pruning. So sánh trực tiếp trên cột phân vùng, hoặc phân vùng đúng kiểu dữ liệu bạn lọc.

Hiệu quả. Partition pruning cắt bytes theo tỉ lệ khoảng thời gian truy vấn (ví dụ 30 ngày / 730 ngày ≈ giảm 24 lần); cluster giảm thêm trong mỗi partition.

3. Giảm shuffle — join bảng nhỏ và denormalize

Vấn đề. Giữa các stage, Dremel phải shuffle dữ liệu qua mạng Jupiter (repartition theo khóa join/group). Shuffle lớn làm chậm query và nếu vượt bộ nhớ sẽ spill ra đĩa (shuffleOutputBytesSpilled > 0) — dấu hiệu nghẽn nặng. Xem cách những con số này hiện trong plan ở Chẩn đoán & tinh chỉnh.

Cách làm.

• Đặt bảng lớn trước, bảng nhỏ sau trong mệnh đề JOIN. BigQuery tối ưu tốt nhất khi vế trái là bảng lớn nhất; bảng nhỏ ở vế phải có thể được broadcast tới các worker thay vì shuffle cả hai vế.
• Lọc trước khi join: giảm cardinality mỗi vế bằng WHERE (và pre-aggregate nếu được) trước khi ghép, để lượng dữ liệu phải shuffle nhỏ đi.
• Denormalize bằng nested/repeated: thay vì join orders với order_items mỗi lần truy vấn, lưu order_items như mảng STRUCT lồng trong orders. BigQuery đọc dữ liệu con cùng dòng cha, không cần shuffle join. Chi tiết mô hình lồng ở Nested & repeated.

Hiệu quả. Chuyển một join lớn thành đọc cột lồng loại bỏ hẳn stage shuffle tương ứng; đặt đúng thứ tự bảng và lọc sớm giảm shuffleOutputBytes và tránh spill.

4. Approximate aggregation — đủ chính xác, rẻ hơn nhiều

Vấn đề. COUNT(DISTINCT ...) chính xác trên hàng tỉ dòng buộc gom toàn bộ giá trị khác nhau, tốn nhiều bộ nhớ và shuffle. Nhiều bài toán (đếm số khách hoạt động, ước lượng phân vị thời gian phản hồi) không cần chính xác tuyệt đối.

Cách làm. Dùng các hàm xấp xỉ dựa trên HyperLogLog++ / sketch:

• APPROX_COUNT_DISTINCT(x) thay COUNT(DISTINCT x)
• APPROX_QUANTILES(x, 100) cho phân vị (p50, p95, p99…)
• APPROX_TOP_COUNT(x, n) cho top-N phổ biến

Hiệu quả. Sai số thường ở mức nhỏ (một vài phần trăm, tùy hàm) nhưng đổi lại nhanh hơn và nhẹ bộ nhớ đáng kể trên tập rất lớn — lý tưởng cho dashboard và cảnh báo, nơi xu hướng quan trọng hơn con số lẻ cuối.

-- Đếm số khách giao dịch phân biệt theo ngày trong quý — bản xấp xỉ, nhẹ và nhanh
SELECT
  txn_date,
  APPROX_COUNT_DISTINCT(customer_id) AS active_customers_approx,
  APPROX_QUANTILES(amount, 100)[OFFSET(95)] AS p95_amount
FROM `ncb-dwh.core.transactions`
WHERE txn_date BETWEEN "2026-04-01" AND "2026-06-30"
GROUP BY txn_date
ORDER BY txn_date;

5. Materialized views — tính sẵn phần lặp lại

Vấn đề. Nhiều dashboard/report chạy cùng một phép tổng hợp (ví dụ tổng giao dịch theo chi nhánh theo ngày) lặp đi lặp lại trên bảng lớn — mỗi lần quét lại từ đầu.

Cách làm. Tạo materialized view (MV) lưu sẵn kết quả tổng hợp. Khác view thường (chỉ là câu SQL chạy lại mỗi lần), MV lưu vật lý kết quả và BigQuery tự động cập nhật tăng dần khi bảng nền thay đổi. Quan trọng nhất: BigQuery có smart tuning / automatic rewrite — kể cả khi bạn query thẳng bảng gốc, optimizer có thể tự chuyển hướng sang MV nếu nó trả lời được, mà không cần bạn đổi câu query.

-- MV tổng hợp giao dịch theo ngày & chi nhánh; BigQuery tự làm mới tăng dần
CREATE MATERIALIZED VIEW `ncb-dwh.core.mv_daily_branch_txn`
PARTITION BY txn_date
CLUSTER BY branch_id
AS
SELECT
  txn_date,
  branch_id,
  COUNT(*)          AS txn_count,
  SUM(amount)       AS total_amount,
  APPROX_COUNT_DISTINCT(customer_id) AS active_customers
FROM `ncb-dwh.core.transactions`
GROUP BY txn_date, branch_id;

Giới hạn cần biết. MV hỗ trợ tập SQL con (aggregate phổ biến; nhiều dạng join/analytic bị hạn chế); dữ liệu mới chưa merge vào MV sẽ được đọc trực tiếp từ bảng nền (nên kết quả luôn tươi, nhưng phần delta vẫn tốn quét). Tra tài liệu cho danh sách phép được hỗ trợ.

Hiệu quả. Query trúng MV chỉ đọc kết quả đã tổng hợp (nhỏ hơn bảng gốc nhiều bậc), nên nhanh và rẻ hơn hẳn — mà không phải viết lại ứng dụng.

6. BI Engine — cache in-memory cho dashboard

Vấn đề. Dashboard tương tác (Looker Studio, Looker, hay công cụ BI khác) bắn nhiều query nhỏ, độ trễ nhạy cảm vào cùng vài bảng. Ngay cả khi mỗi query rẻ, độ trễ đọc từ storage mỗi lần vẫn khiến dashboard "khựng".

Cách làm. Cấp phát một BI Engine reservation (dung lượng RAM) cho project/region. BI Engine giữ dữ liệu nóng trong bộ nhớ, phục vụ trực tiếp các query đủ điều kiện với độ trễ rất thấp và vector processing. Nó minh bạch với ứng dụng: không cần đổi query, BigQuery tự dùng BI Engine khi query khớp.

Sơ đồ minh hoạ tầng tăng tốc: bảng nền → materialized view (giảm dữ liệu) → BI Engine (cache RAM) → dashboard. Hai kỹ thuật bổ trợ nhau — MV thu nhỏ dữ liệu phải phục vụ, BI Engine phục vụ nó ở tốc độ bộ nhớ.

Hiệu quả. Query trúng BI Engine trả về ở mức mili-giây và không tính bytes billed on-demand cho phần phục vụ từ bộ nhớ (bạn trả cho dung lượng reservation thay vì bytes). Kết quả: dashboard mượt, chi phí dự đoán được.

7. Search index — tìm điểm dữ liệu trong cột lớn

Vấn đề. Tìm một giá trị hiếm trong cột text/JSON lớn (ví dụ tra một mã tham chiếu, một số điện thoại trong log giao dịch) bằng WHERE col LIKE '%...%' buộc quét toàn bộ cột — đắt và chậm.

Cách làm. Tạo search index trên cột (hoặc ALL COLUMNS) rồi dùng hàm SEARCH(). BigQuery dùng index để nhảy thẳng tới các block chứa token cần, bỏ qua phần còn lại — tương tự tra mục lục thay vì đọc cả sách.

-- Tạo search index rồi tìm giao dịch chứa một mã tham chiếu cụ thể
CREATE SEARCH INDEX idx_txn_notes
ON `ncb-dwh.core.transactions`(note, ref_code);

SELECT txn_id, txn_date, amount
FROM `ncb-dwh.core.transactions`
WHERE SEARCH(note, 'REF-2026-BATCH-0091');

Hiệu quả. Với truy vấn "kim trong đống rơm" (needle-in-haystack) trên bảng rất lớn, search index giảm mạnh bytes quét và độ trễ so với LIKE/scan. Không hợp cho query phân tích quét diện rộng — chỉ hợp tìm điểm dữ liệu chọn lọc.

8. Tránh self-join / cross-join lớn

Vấn đề. CROSS JOIN (hoặc join thiếu điều kiện) sinh tích Descartes — N × M dòng — bùng nổ dữ liệu và shuffle. SELF JOIN để so dòng với dòng khác (ví dụ so giao dịch với giao dịch trước của cùng khách) thường nhân đôi việc đọc bảng và tạo join nặng.

Cách làm.

• Thay self-join bằng window function (LAG, LEAD, SUM() OVER (PARTITION BY ...)): tính toán "so với dòng trước/sau" trong một lần quét, không cần ghép bảng với chính nó. Xem Joins & window functions.
• Với CROSS JOIN, kiểm tra xem có điều kiện ghép thực sự bị bỏ sót không; nếu cần nhân với một tập nhỏ (ví dụ bảng lịch ngày), đảm bảo vế kia thật sự nhỏ.

-- Thay self-join bằng LAG: tính khoảng cách tới giao dịch trước của mỗi khách
SELECT
  customer_id,
  txn_id,
  txn_ts,
  amount,
  amount - LAG(amount) OVER (
    PARTITION BY customer_id ORDER BY txn_ts
  ) AS delta_vs_prev
FROM `ncb-dwh.core.transactions`
WHERE txn_date BETWEEN "2026-06-01" AND "2026-06-30";

Hiệu quả. Bỏ một self-join lớn thường loại hẳn một stage join + shuffle, đổi lấy một analytic function chạy trong một lần đọc — nhanh và rẻ hơn rõ rệt.

9. Xử lý data skew

Vấn đề. Khi dữ liệu phân bố lệch theo khóa (một branch_id ôm 40% giao dịch, hoặc một customer_id NULL gom hết), một số worker phải xử lý phần lớn dữ liệu trong khi số còn lại rảnh. Trong query plan, dấu hiệu là chênh lệch lớn giữa computeMsAvg và computeMsMax (hoặc waitMs, recordsRead avg vs max) của một stage — vài worker "cày" mãi, kéo dài cả stage.

Cách làm.

• Lọc bỏ khóa rác trước join/group (loại NULL, giá trị sentinel gom cục).
• Pre-aggregate để giảm số dòng trước bước bị lệch.
• Salting: thêm hậu tố ngẫu nhiên vào khóa lệch để tách nó thành nhiều nhóm nhỏ, aggregate hai bước (theo khóa+salt, rồi gộp lại).
• Tránh ORDER BY toàn cục không cần thiết (dồn về ít worker); chỉ sort khi thật sự cần và kèm LIMIT.

Hiệu quả. Cân lại tải giúp computeMsMax tiến gần computeMsAvg, stage nghẽn kết thúc sớm hơn, tổng slot-time giảm. Cách đọc các chỉ số avg-vs-max để phát hiện skew xem Chẩn đoán & tinh chỉnh.

Bảng tổng hợp kỹ thuật

Use case thực tế

Đội Data NCB có báo cáo giám sát giao dịch chạy mỗi giờ trên bảng transactions 2 năm (~2.4 TB) và join với customers (5 triệu dòng) để gắn phân khúc khách. Bản đầu:

• SELECT * cả hai bảng, join customers (nhỏ) đặt trước transactions (lớn), không có WHERE theo ngày → mỗi lần quét ~2.5 TB, shuffle lớn, thỉnh thoảng spill.
• Đếm khách hoạt động bằng COUNT(DISTINCT customer_id) chính xác trên toàn bảng.

Các can thiệp, không đổi kết quả nghiệp vụ:

1. Chọn 5 cột cần thay SELECT * → bytes rớt còn ~180 GB/lần.
2. Partition pruning WHERE txn_date trong 90 ngày → còn ~22 GB/lần.
3. Đảo thứ tự join: transactions (lớn) trước, customers (nhỏ) sau, lọc customers active trước join → shuffle nhỏ hẳn, hết spill.
4. APPROX_COUNT_DISTINCT cho số khách hoạt động → nhẹ RAM, sai số dưới ~1%, đủ cho báo cáo xu hướng.
5. Materialized view mv_daily_branch_txn cho phần tổng hợp theo ngày/chi nhánh; dashboard Looker Studio trỏ vào MV và được BI Engine cache → làm mới ở mili-giây.

Kết quả: bytes mỗi lần chạy từ ~2.5 TB xuống ~22 GB (giảm ~99%), dashboard từ vài giây xuống dưới một giây, và không còn spill trong plan. Đội đặt thêm maximum_bytes_billed làm cầu dao và soi lại plan định kỳ theo Chẩn đoán & tinh chỉnh.

Ghi nhớ

• Mọi kỹ thuật đều tấn công một trong ba chỗ tốn: scan (bytes đọc), shuffle (dữ liệu qua mạng), compute (slot-time) — xác định đúng chỗ nghẽn trước khi sửa.
• Rẻ và mạnh nhất: chọn cột thay SELECT *, rồi prune sớm bằng partition + cluster; đừng bọc cột phân vùng trong hàm kẻo mất pruning.
• Giảm shuffle bằng đặt bảng lớn trước bảng nhỏ, lọc sớm, và denormalize nested để bỏ join lặp.
• Approximate aggregation (APPROX_COUNT_DISTINCT, APPROX_QUANTILES) đổi sai số nhỏ lấy tốc độ lớn trên tập rất lớn.
• Materialized view lưu sẵn aggregate + auto-rewrite; BI Engine cache in-memory cho dashboard tương tác — hai tầng bổ trợ nhau.
• Search index + SEARCH() cho tìm điểm dữ liệu trong cột text lớn; không dùng cho quét phân tích diện rộng.
• Thay self-join bằng window function; tránh cross-join không điều kiện gây tích Descartes.
• Data skew lộ ra ở chênh lệch avg vs max trong stage — lọc khóa rác, pre-aggregate hoặc salting để cân tải.

Nguồn tham khảo

• Best practices — performance overview: https://cloud.google.com/bigquery/docs/best-practices-performance-overview
• Materialized views introduction: https://cloud.google.com/bigquery/docs/materialized-views-intro
• BI Engine introduction: https://cloud.google.com/bigquery/docs/bi-engine-intro
• Search indexes: https://cloud.google.com/bigquery/docs/search-index
• Nested & repeated data: https://cloud.google.com/bigquery/docs/nested-repeated
• Clustered tables: https://cloud.google.com/bigquery/docs/clustered-tables
• Query plan and timeline: https://cloud.google.com/bigquery/docs/query-plan-explanation
• "Google BigQuery: The Definitive Guide" — Lakshmanan & Tigani, O'Reilly