Chiến lược tối ưu truy vấn

Giới thiệu về BigQuery

Matt Forrest

Field CTO

Ba quy tắc ngắn gọn

Có ba quy tắc tối ưu chính:

Giảm lượng dữ liệu cần xử lý
Tối ưu các phép toán trong truy vấn
Giảm kích thước đầu ra của truy vấn

¹ https://cloud.google.com/bigquery/docs/best-practices-performance-compute#use-bi-engine

Giảm lượng dữ liệu

Tránh dùng SELECT *, chỉ chọn cột cần thiết
Lọc dữ liệu sớm và thường xuyên trong CTE
Lọc bằng mệnh đề WHERE sớm và thường xuyên

Tối ưu phép join

Đảm bảo giảm dữ liệu bằng CTE.
Join bằng kiểu dữ liệu INT64.

WITH filter_my_data AS (SELECT 
-- Filter data with 
-- WHERE in the CTE first
)
SELECT 
-- This query will run 
-- faster with less data
JOIN a USING (user_id)

Tối ưu mệnh đề WHERE

Trong BigQuery, ưu tiên
- BOOL
- INT
- FLOAT
- DATE
Dùng WHERE với STRING hoặc BYTE là không tối ưu.

Không tối ưu

SELECT user_id, date_ordered
FROM dataset.table
WHERE product = 'shoes'

Tối ưu

SELECT user_id, date_ordered
FROM dataset.table
WHERE product_id = 1234

Tối ưu ORDER BY

ORDER BY luôn nên đặt ngoài cùng (cuối truy vấn)
Ngoại lệ duy nhất: dùng ORDER BY trong mệnh đề window

ORDER BY không tối ưu

Chưa tối ưu

WITH order_total AS (SELECT
user_id,           
sum(product_price) as order_sum
FROM orders
GROUP BY user_id   
-- Order by is not at the end of the query
ORDER BY last_purchase_date
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN order_total USING (user_id);

ORDER BY đã tối ưu

Tối ưu

WITH order_total AS (SELECT
user_id,    
last_purchase_date
sum(product_price) as order_sum
GROUP BY user_id         
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN a USING (user_id)
-- Order by should always be at the end
ORDER BY orders_total.last_purchase_date;

Dùng EXISTS vs. COUNT

Nếu chỉ cần biết bản ghi có tồn tại, hãy dùng EXISTS
Tránh dùng COUNT cho trường hợp này

SELECT EXISTS (

  -- Write the main query as a subquery within the exists call
  SELECT
    user_id
  FROM
    dataset.table
  WHERE
    product_category = 'home_goods'
    AND status = 'Closed Account'
);

Các cách tối ưu khác

Dùng hàm tổng hợp xấp xỉ như APPROX_TOP_SUM hoặc APPROX_COUNT_DISTINCT.
Nhiều bảng BigQuery được phân vùng theo ngày — hãy lọc ngày trong mệnh đề WHERE.

Ayo berlatih!

Giới thiệu về BigQuery