Thanaoscar

Bài 14: Giải Cứu Query Với Sort Và Hash Aggregate

Thana Oscar's photo
Thana Oscar
·

4 min read

"Sort và Hash Aggregate có thể 'ngốn' tài nguyên – hãy kiểm soát chúng trước khi quá muộn!"

1. Vấn Đề: Query Tổng Hợp Bị Chậm Do Sắp Xếp và Gom Nhóm

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

  • sale_id (PK)

  • product_id

  • sale_date

  • quantity

  • price

Thực hiện query tổng hợp:

SELECT product_id, SUM(quantity * price) AS total_revenue  
FROM sales  
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'  
GROUP BY product_id  
ORDER BY total_revenue DESC;

Kết quả: Thời gian thực thi ~15 giây.

2. Phân Tích Execution Plan

Bước 1: Chạy EXPLAIN ANALYZE (PostgreSQL)
Sort (cost=350000.00..351250.00 rows=500000 width=20) 
     (actual time=14000.123..14500.456 rows=10000 loops=1)  
  Sort Key: (SUM((quantity * price))) DESC  
  Sort Method: external merge  Disk: 50000KB  
  -> HashAggregate (cost=250000.00..300000.00 rows=500000 width=20) 
                   (actual time=12000.123..13500.789 rows=10000 loops=1)  
        Group Key: product_id  
        -> Seq Scan on sales (cost=0.00..150000.00 rows=1000000 width=20) 
                             (actual time=0.012..8000.123 rows=900000 loops=1)  
              Filter: (sale_date BETWEEN '2023-01-01' AND '2023-12-31')  
              Rows Removed by Filter: 4100000
Bước 2: Giải Mã Vấn Đề

  • HashAggregate: Gom nhóm 900,000 dòng → Tốn 12 giây.

  • Sort trên Disk: Sắp xếp 10,000 nhóm theo total_revenue → Tốn thêm 3 giây.

  • Bottleneck Chính:

    1. Full Scan do thiếu index trên sale_date.

    2. HashAggregate tốn bộ nhớ và CPU.

    3. Sort trên Disk làm chậm query.

3. Tối Ưu: Kết Hợp Index và Điều Chỉnh Bộ Nhớ

Bước 1: Tạo Index Trên sale_date và Sử Dụng Covering Index
CREATE INDEX idx_sales_date_covering ON sales(sale_date) INCLUDE (product_id, quantity, price);
Bước 2: Tăng work_mem Để Xử Lý Sort Trong Memory
SET work_mem = '256MB';  -- Áp dụng cho session hiện tại
Bước 3: Execution Plan Sau Khi Tối Ưu
Sort (cost=50000.00..50125.00 rows=50000 width=20) 
     (actual time=1500.123..1800.456 rows=10000 loops=1)  
  Sort Key: (SUM((quantity * price))) DESC  
  Sort Method: quicksort  Memory: 100MB  
  -> HashAggregate (cost=40000.00..45000.00 rows=50000 width=20) 
                   (actual time=1200.123..1400.789 rows=10000 loops=1)  
        Group Key: product_id  
        -> Index Only Scan using idx_sales_date_covering on sales 
                   (cost=0.42..25000.00 rows=900000 width=20) 
                   (actual time=0.032..500.123 rows=900000 loops=1)  
              Index Cond: ((sale_date >= '2023-01-01'::date) AND 
                           (sale_date <= '2023-12-31'::date))
Kết Quả:

  • Index Only Scan: Thay thế Full Scan → Giảm I/O (từ 5 triệu dòng → 900,000 dòng).

  • HashAggregate Nhanh Hơn: Nhờ dữ liệu đã được lọc sẵn.

  • Sort trên Memory: Thay vì Disk → Giảm thời gian từ 3 giây → 0.3 giây.

  • Tổng Thời Gian: Từ 15 giây → 1.8 giây (88% cải thiện).

4. Tổng Kết

Chỉ SốTrước Khi Tối ƯuSau Khi Tối Ưu
Total Cost350,00050,000
Execution Time15,000 ms1,800 ms
Sort MethodDisk (External Merge)Memory (Quicksort)
Phương Pháp Truy CậpFull Table ScanIndex Only Scan

Lý Do Hiệu Quả:

  • Covering index cung cấp tất cả dữ liệu cần thiết → Tránh truy cập bảng gốc.

  • Tăng work_mem giúp xử lý Sort và HashAggregate trong memory.

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

Dataset Mẫu:
CREATE TABLE sensor_data (  
    sensor_id INT,  
    log_time TIMESTAMP,  
    value FLOAT  
);  
-- Insert 10 triệu dòng dữ liệu
Yêu Cầu:

  1. Chạy query:

     SELECT sensor_id, AVG(value)  
 FROM sensor_data  
 WHERE log_time BETWEEN '2024-01-01' AND '2024-06-30'  
 GROUP BY sensor_id  
 ORDER BY AVG(value) DESC;

  2. Phân tích Execution Plan và xác định nguyên nhân gây chậm.

  3. Tạo index phù hợp và điều chỉnh work_mem để tối ưu.

Câu Hỏi:
  • Tại sao Sort trên Memory lại nhanh hơn nhiều so với Sort trên Disk?

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

Khi Nào Không Nên Tăng work_mem?

  • Hệ thống thiếu bộ nhớ: Tăng work_mem quá cao → Gây swap hoặc OOM (Out-Of-Memory).

  • Nhiều query đồng thời: Mỗi query chiếm một phần work_mem → Tổng bộ nhớ tiêu thụ có thể vượt giới hạn.

Cân Bằng Giữa Index và HashAggregate

  • Trường hợp không thể loại bỏ Sort: Sử dụng index trên cột sắp xếp để database bỏ qua node Sort.
    Ví dụ:

      CREATE INDEX idx_sales_revenue ON sales(product_id, (quantity * price) DESC);
Case Study: Sử Dụng Partial Index Cho Phạm Vi Dữ Liệu Cố Định
-- Tạo index chỉ cho dữ liệu năm 2023  
CREATE INDEX idx_sales_2023 ON sales(sale_date)  
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';
  • Ưu điểm: Giảm kích thước index → Tối ưu hiệu suất.
Cảnh Báo Khi Sử Dụng Index Cho Sort

  • Chi phí duy trì index: Ảnh hưởng đến hiệu suất ghi.

  • Over-indexing: Tạo quá nhiều index → Tăng thời gian maintain.

Kết Luận

Sort và Hash Aggregate là "kẻ thù thầm lặng" khi xử lý dữ liệu lớn. Bằng cách kết hợp index phù hợp và điều chỉnh bộ nhớ, bạn có thể giảm thời gian thực thi từ 15 giây xuống còn 1.8 giây. Trong bài tiếp theo (Bài 15), chúng ta sẽ khám phá cách tối ưu các thuật toán JOIN!

👉 Bài Tập Về Nhà: Thử nghiệm với work_mem trên PostgreSQL: Tăng giảm giá trị và đo lường hiệu suất khi Sort 1 triệu dòng!

SQL