Thanaoscar

Bài 9: Tối ưu GROUP BY và HAVING – Bí quyết xử lý aggregation "nặng đô"

Thana Oscar's photo
Thana Oscar
·

5 min read

Dưới đây là nội dung chi tiết cho Bài 9: Tối ưu GROUP BY và HAVING – Phân tích sâu cơ chế aggregation và bí quyết xử lý dữ liệu "nặng đô", được viết dưới góc nhìn chuyên sâu của kỹ sư database:

Thám hiểm sâu vào cơ chế thực thi aggregation và kỹ thuật tối ưu đẳng cấp

1. Hiểu sâu quy trình thực thi GROUP BY

1.1. Vòng đời của một aggregation query

  1. Scan & Filter: Đọc dữ liệu từ bảng, áp dụng WHERE.

  2. Sort/Shuffle: Sắp xếp hoặc phân phối dữ liệu theo key.

  3. Grouping: Gom nhóm và tính toán aggregate (SUM, AVG, COUNT).

  4. Filter kết quả: Áp dụng HAVING.

1.2. Các thuật toán grouping phổ biến

Thuật toánNguyên lýƯu/Nhược
Hash AggregationDùng hash table để nhóm các row cùng keyNhanh, nhưng tốn memory.
Sort AggregationSắp xếp trước, sau đó nhóm liên tiếpTiết kiệm memory, nhưng chậm hơn.
HybridKết hợp hash và sort tùy data sizeCân bằng giữa tốc độ và tài nguyên.

2. Phân tích bottleneck trong GROUP BY

2.1. Case Study: Thống kê doanh thu theo tháng

Query:

SELECT 
  DATE_TRUNC('month', order_date) AS month,  
  SUM(total_amount) AS revenue  
FROM orders  
GROUP BY month  
ORDER BY month;

Execution Plan (PostgreSQL):

GroupAggregate  (cost=1.2M..1.5M rows=12)  
  Group Key: (date_trunc('month'::text, order_date))  
  ->  Sort  (cost=1.2M..1.3M rows=1M)  
        Sort Key: (date_trunc('month'::text, order_date))  
        ->  Seq Scan on orders  (cost=0..150K rows=1M)

➜ Bottleneck:

  • Full Scan + Sort → Tốn I/O và CPU.

  • Giải pháp: Sử dụng index trên order_date để tránh sort.

2.2. Cơ chế tận dụng index cho GROUP BY

Covering Index:

CREATE INDEX idx_orders_monthly ON orders (date_trunc('month', order_date)) INCLUDE (total_amount);

Execution Plan tối ưu:

GroupAggregate  (cost=0.1..0.5M rows=12)  
  Group Key: (date_trunc('month'::text, order_date))  
  ->  Index Only Scan using idx_orders_monthly on orders

→ Giảm cost từ 1.5M → 0.5M (3 lần).

3. Tối ưu HAVING: Tránh "filter quá muộn"

3.1. HAVING vs WHERE: Điểm khác biệt cốt lõi

  • WHERE: Lọc dữ liệu trước khi grouping → Giảm lượng data xử lý.

  • HAVING: Lọc sau khi grouping → Tốn tài nguyên tính toán.

Ví dụ tối ưu:

-- Chưa tối ưu  
SELECT user_id, SUM(amount) AS total  
FROM transactions  
GROUP BY user_id  
HAVING SUM(amount) > 1000;  

-- Tối ưu (nếu có thể)  
SELECT user_id, SUM(amount) AS total  
FROM transactions  
WHERE amount > 0  -- Lọc trước để giảm data  
GROUP BY user_id  
HAVING SUM(amount) > 1000;

4. Kỹ thuật tối ưu đẳng cấp

4.1. Materialized View cho aggregation định kỳ

Bài toán: Thống kê doanh thu hàng ngày với 100M rows.

CREATE MATERIALIZED VIEW daily_revenue AS  
SELECT 
  DATE(order_date) AS day,  
  SUM(total_amount) AS revenue  
FROM orders  
GROUP BY day;  

-- Làm mới MV hàng ngày  
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Ưu điểm:

  • Query từ MV nhanh hơn 100-1000 lần.

  • Giảm tải cho OLTP database.

4.2. Sử dụng Window Function thay thế GROUP BY

Khi cần kết hợp aggregation và chi tiết:

SELECT 
  order_id,  
  customer_id,  
  total_amount,  
  SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total  
FROM orders;

Lợi ích:

  • Tránh phải join lại bảng customer.

  • Linh hoạt trong phân tích.

4.3. Approximate Aggregation cho big data

Dùng probabilistic data structures để giảm độ chính xác, tăng tốc độ:

  • HyperLogLog (HLL): Đếm unique values.

  • T-Digest: Tính percentiles.

Ví dụ (PostgreSQL):

SELECT 
  date,  
  APPROX_COUNT_DISTINCT(user_id) AS unique_users  
FROM logs  
GROUP BY date;

→ Tốc độ tăng 10x, sai số ~1%.

5. Case Study: Xử lý real-time aggregation trên 10 tỷ rows

5.1. Kiến trúc Lambda

  • Batch Layer (Hadoop/Spark): Xử lý aggregation toàn bộ data mỗi ngày.

  • Speed Layer (Kafka/Flink): Xử lý real-time trên data stream.

  • Serving Layer (Druid/ClickHouse): Merge kết quả batch + real-time.

5.2. Tối ưu trên ClickHouse

MergeTree Engine + Aggregation States:

CREATE TABLE revenue_stats (  
  day Date,  
  product_id UInt32,  
  revenue AggregateFunction(sum, Decimal(18,2))  
) ENGINE = AggregatingMergeTree()  
ORDER BY (day, product_id);  

-- Insert data  
INSERT INTO revenue_stats  
SELECT 
  day,  
  product_id,  
  sumState(total_amount)  
FROM orders  
GROUP BY day, product_id;  

-- Query final result  
SELECT 
  day,  
  product_id,  
  sumMerge(revenue)  
FROM revenue_stats  
GROUP BY day, product_id;

Hiệu quả: Xử lý 10 tỷ rows trong vài giây.

6. Bài tập thực hành

Tối ưu truy vấn sau:

SELECT 
  category,  
  AVG(rating) AS avg_rating,  
  COUNT(*) AS total_reviews  
FROM products  
WHERE rating IS NOT NULL  
GROUP BY category  
HAVING COUNT(*) > 1000  
ORDER BY avg_rating DESC;

Gợi ý giải pháp:

  1. Thêm covering index (category, rating).

  2. Thay COUNT(*) bằng COUNT(rating) nếu hợp lý.

  3. Dùng Materialized View nếu query chạy thường xuyên.

7. Tổng kết

  • GROUP BY là "con thú" tốn tài nguyên: Hiểu rõ thuật toán và data distribution để chọn chiến lược phù hợp.

  • Tối ưu đa tầng: Từ index, query pattern đến kiến trúc hệ thống.

  • Công cụ phù hợp: Sử dụng OLAP databases (ClickHouse, Druid) cho aggregation cực lớn.

Aggregation tối ưu không chỉ là viết query đúng – đó là nghệ thuật cân bằng giữa tốc độ, tài nguyên và độ chính xác!

Preview bài tiếp theo:
Bài 10: Kiểm soát Transaction – Bảo vệ toàn vẹn dữ liệu và xử lý deadlock.

SQL