Bài 17: Xử Lý Query Phức Tạp – Phân Rã Query Lớn Thành Nhiều Bước

"Khi query trở thành 'quái vật' hàng triệu dòng, hãy cắt nhỏ nó thành những miếng vừa ăn!"

1. Vấn Đề: Query Báo Cáo Tổng Hợp 10 Triệu Dòng Chậm và Tốn Tài Nguyên

Scenario:
Bảng sales chứa 10 triệu đơn hàng với các cột:

sale_id (PK)
product_id
sale_date
quantity
price

Yêu cầu: Tạo báo cáo tổng hợp theo năm và sản phẩm, bao gồm:

Tổng doanh thu.
Số lượng bán trung bình mỗi tháng.
Top 3 sản phẩm có doanh thu cao nhất từng năm.

Query ban đầu:

WITH yearly_sales AS (  
  SELECT  
    EXTRACT(YEAR FROM sale_date) AS year,  
    product_id,  
    SUM(quantity * price) AS total_revenue,  
    AVG(quantity) AS avg_monthly_quantity  
  FROM sales  
  GROUP BY year, product_id  
),  
top_products AS (  
  SELECT  
    year,  
    product_id,  
    total_revenue,  
    RANK() OVER (PARTITION BY year ORDER BY total_revenue DESC) AS rank  
  FROM yearly_sales  
)  
SELECT  
  y.year,  
  y.product_id,  
  y.total_revenue,  
  y.avg_monthly_quantity,  
  t.rank  
FROM yearly_sales y  
LEFT JOIN top_products t ON y.year = t.year AND y.product_id = t.product_id  
WHERE t.rank <= 3;

Kết quả: Thời gian thực thi ~25 giây, CPU đạt 90%.

2. Phân Tích Execution Plan

Bước 1: Chạy `EXPLAIN ANALYZE` (PostgreSQL)

Hash Left Join (cost=1,500,000.00..2,000,000.00 rows=500,000 width=40) 
  (actual time=24,500.123..25,000.456 rows=30,000 loops=1)  
  Hash Cond: (y.year = t.year AND y.product_id = t.product_id)  
  -> GroupAggregate (cost=800,000.00..1,200,000.00 rows=1,000,000 width=40) 
    (actual time=15,000.123..20,000.456 rows=1,000,000 loops=1)  
    Group Key: (EXTRACT(YEAR FROM sale_date)), product_id  
    -> Sort (cost=700,000.00..750,000.00 rows=10,000,000 width=20) 
      (actual time=10,000.123..12,000.456 rows=10,000,000 loops=1)  
      Sort Key: (EXTRACT(YEAR FROM sale_date)), product_id  
      Sort Method: external merge  Disk: 200,000KB  
      -> Seq Scan on sales (cost=0.00..200,000.00 rows=10,000,000 width=20) 
        (actual time=0.012..5,000.123 rows=10,000,000 loops=1)  
  -> Hash (cost=500,000.00..500,000.00 rows=30,000 width=20) 
    (actual time=9,500.012..9,500.012 rows=30,000 loops=1)  
    Buckets: 65,536  Batches: 1  Memory Usage: 3,000MB  
    -> Subquery Scan on t (cost=400,000.00..500,000.00 rows=30,000 width=20) 
      (actual time=8,000.123..9,200.456 rows=30,000 loops=1)  
      -> WindowAgg (cost=400,000.00..450,000.00 rows=1,000,000 width=40) 
        (actual time=8,000.123..9,000.456 rows=1,000,000 loops=1)  
        -> Sort (cost=400,000.00..425,000.00 rows=1,000,000 width=32) 
          (actual time=7,500.123..8,200.456 rows=1,000,000 loops=1)  
          Sort Key: EXTRACT(YEAR FROM sale_date), total_revenue DESC  
          Sort Method: external merge  Disk: 150,000KB  
          -> CTE Scan on yearly_sales y2 
(cost=800,000.00..900,000.00 rows=1,000,000 width=32) 
            (actual time=15,000.123..16,000.456 rows=1,000,000 loops=1)

Bước 2: Giải Mã Vấn Đề

Full Scan + Sort trên Disk: Bảng sales 10 triệu dòng được quét toàn bộ và sắp xếp 2 lần.
Hash Join Tốn Bộ Nhớ: Hash table 3,000MB cho top_products.
Bottleneck Chính:
1. Tính toán trên toàn bộ dataset → Tốn CPU và I/O.
2. Thiếu index hỗ trợ phân nhóm theo năm và sản phẩm.
3. Không chia nhỏ dữ liệu → Gây lock bảng khi chạy lâu.

3. Tối Ưu: Phân Rã Query Thành Các Bước Nhỏ

Bước 1: Chia Nhỏ Dữ Liệu Thành Batch Sử Dụng Temp Table

-- Bước 1: Lưu dữ liệu đã lọc vào temp table  
CREATE TEMP TABLE filtered_sales AS  
SELECT *  
FROM sales  
WHERE sale_date BETWEEN '2010-01-01' AND '2023-12-31';  

-- Bước 2: Tạo index trên temp table  
CREATE INDEX idx_temp_sales_year_product 
ON filtered_sales (EXTRACT(YEAR FROM sale_date), product_id);  

-- Bước 3: Tính toán tổng hợp từng phần  
CREATE TEMP TABLE yearly_sales AS  
SELECT  
  EXTRACT(YEAR FROM sale_date) AS year,  
  product_id,  
  SUM(quantity * price) AS total_revenue,  
  AVG(quantity) AS avg_monthly_quantity  
FROM filtered_sales  
GROUP BY year, product_id;  

-- Bước 4: Xếp hạng sản phẩm  
CREATE TEMP TABLE top_products AS  
SELECT  
  year,  
  product_id,  
  total_revenue,  
  RANK() OVER (PARTITION BY year ORDER BY total_revenue DESC) AS rank  
FROM yearly_sales;  

-- Bước 5: Lấy kết quả cuối cùng  
SELECT  
  y.year,  
  y.product_id,  
  y.total_revenue,  
  y.avg_monthly_quantity,  
  t.rank  
FROM yearly_sales y  
JOIN top_products t ON y.year = t.year AND y.product_id = t.product_id  
WHERE t.rank <= 3;

Bước 2: Execution Plan Sau Khi Tối Ưu

Nested Loop (cost=200,000.00..300,000.00 rows=30,000 width=40) 
  (actual time=3,000.123..3,500.456 rows=30,000 loops=1)  
  -> Seq Scan on yearly_sales y (cost=0.00..50,000.00 rows=1,000,000 width=40) 
    (actual time=0.012..500.123 rows=1,000,000 loops=1)  
  -> Index Scan using idx_top_products ON top_products t 
    (cost=0.42..0.50 rows=1 width=20) 
    (actual time=0.001..0.002 rows=0.03 loops=1,000,000)  
    Index Cond: (y.year = t.year AND y.product_id = t.product_id)  
    Filter: (rank <= 3)

Kết Quả:

Giảm Phạm Vi Dữ Liệu: Temp table filtered_sales chỉ chứa dữ liệu cần thiết.
Index Hỗ Trợ Truy Vấn: Tăng tốc độ JOIN và WHERE.
Chia Nhỏ Thành 5 Bước: Giảm áp lực bộ nhớ, tránh lock dữ liệu lâu.
Tổng Thời Gian: Từ 25 giây → 3.5 giây (86% cải thiện).

4. Tổng Kết

Chỉ Số	Trước Khi Tối Ưu	Sau Khi Tối Ưu
Total Cost	2,000,000	300,000
Execution Time	25,000 ms	3,500 ms
Bộ Nhớ Tiêu Thụ	3,000MB	500MB
Phương Pháp	Single Complex Query	Batch Processing

Lý Do Hiệu Quả:

Giảm kích thước dữ liệu xử lý qua temp table và batch.
Index trên temp table tối ưu hóa JOIN và filter.
Tránh tính toán trực tiếp trên bảng gốc 10 triệu dòng.

5. Bài Tập Thực Hành

Dataset Mẫu:

CREATE TABLE user_activities (  
    user_id INT,  
    activity_date DATE,  
    duration_minutes INT  
);  
-- Insert 50 triệu dòng dữ liệu

Yêu Cầu:

Viết query tổng hợp:
- Thời gian hoạt động trung bình mỗi người dùng theo tháng.
- Top 10 người dùng có thời gian hoạt động cao nhất từng năm.
Phân tích Execution Plan và xác định bottleneck.
Áp dụng kỹ thuật phân rã thành temp table và batch processing.

Câu Hỏi:

Khi nào nên sử dụng temp table thay vì CTE?

6. Mở Rộng & Thảo Luận

Chiến Lược Batch Processing Cho Dữ Liệu Cực Lớn

Phân Chia Dữ Liệu: Sử dụng WHERE và OFFSET để xử lý từng phần:

  DECLARE last_id INT DEFAULT 0;  
  WHILE TRUE DO  
    INSERT INTO temp_table  
    SELECT * FROM main_table  
    WHERE id > last_id  
    ORDER BY id  
    LIMIT 100000;  
    SET last_id = (SELECT MAX(id) FROM temp_table);  
    EXIT WHEN last_id IS NULL;  
  END WHILE;

Parallel Processing: Chạy nhiều batch song song (đòi hỏi ứng dụng hỗ trợ).

So Sánh Temp Table vs CTE

Tiêu Chí	Temp Table	CTE
Lưu Trữ	Vật lý trên disk hoặc memory	Tạm thời trong memory
Tái Sử Dụng	Có thể truy vấn nhiều lần	Chỉ tồn tại trong 1 query
Hiệu Suất	Tối ưu cho dữ liệu lớn	Phù hợp dataset nhỏ

Cảnh Báo Khi Dùng Temp Table

Tốn Disk I/O: Nếu temp table được lưu trên disk.
Quản Lý Phiên: Temp table chỉ tồn tại trong session hiện tại.

Case Study: Sử Dụng Materialized View Cho Báo Cáo Định Kỳ

CREATE MATERIALIZED VIEW mv_yearly_sales AS  
SELECT  
  EXTRACT(YEAR FROM sale_date) AS year,  
  product_id,  
  SUM(quantity * price) AS total_revenue  
FROM sales  
GROUP BY year, product_id;  

-- Làm mới dữ liệu hàng ngày  
REFRESH MATERIALIZED VIEW mv_yearly_sales;

Kết Luận

Phân rã query lớn thành các bước nhỏ không chỉ giảm tải tài nguyên mà còn giúp quản lý quy trình xử lý dữ liệu hiệu quả. Trong ví dụ này, thời gian thực thi giảm từ 25 giây xuống 3.5 giây nhờ kết hợp temp table, index, và batch processing. Trong bài tiếp theo (Bài 18), chúng ta sẽ khám phá cách tận dụng partitioning và parallel query để xử lý dữ liệu ở quy mô lớn hơn!

👉 Bài Tập Về Nhà: Tạo một temp table cho bảng user_activities, chia nhỏ dữ liệu thành các batch 100,000 dòng và đo hiệu suất!