CodeGym /Các khóa học /SQL SELF /Tối ưu hóa truy vấn với window functions

Tối ưu hóa truy vấn với window functions

SQL SELF

Mức độ, Bài học

Có sẵn

Còn một chi tiết quan trọng nữa mà tụi mình chưa bàn tới — hiệu năng của truy vấn dùng window functions. Dù câu truy vấn có đẹp cỡ nào mà không tối ưu thì cũng chậm như rùa thôi. Hôm nay tụi mình sẽ xử lý vụ này nhé!

Window functions cực kỳ linh hoạt và mạnh mẽ. Nhưng sự linh hoạt đó vừa là món quà, vừa là mối đe dọa cho hiệu năng. PostgreSQL thì không phải kiểu "ma thuật" đâu, nó cần tài nguyên để xử lý dữ liệu. Nếu bạn áp window functions lên bảng khổng lồ, truy vấn của bạn sẽ giống như chạy marathon tại chỗ luôn ấy.

Tối ưu hóa sẽ giúp bạn:

Tăng tốc truy vấn khi làm việc với dữ liệu lớn.
Giảm tải cho database.
Làm cho truy vấn của bạn thân thiện hơn với server (và cả đồng nghiệp nếu họ cũng xài chung database!).

Giờ thì cùng lặn sâu vào xem làm sao để truy vấn của bạn bay như xe đua trên đường đua nhé.

Kiến thức cơ bản về window functions

Trước khi tối ưu, phải hiểu cái gì làm truy vấn bị chậm đã. PostgreSQL xử lý window functions như sau:

Sắp xếp dữ liệu nếu truy vấn có ORDER BY trong OVER().
Xử lý từng dòng trong phạm vi window hoặc group đã định.
Trả về kết quả cho từng dòng.

Giờ tưởng tượng bạn có bảng sales với 10 triệu dòng. Nếu truy vấn không có filter, PostgreSQL sẽ xử lý từng dòng một. Đó không còn là marathon nữa — mà là chạy bộ trên máy không có điểm dừng luôn.

Làm sao tăng tốc window functions?

Dùng indexes để tăng tốc sắp xếp

Phần lớn window functions dùng ORDER BY trong OVER() để kiểm soát thứ tự dòng. Nghĩa là PostgreSQL phải sắp xếp dữ liệu trước khi chạy window function.

Nếu bạn có index trên cột (hoặc các cột) dùng trong ORDER BY, PostgreSQL sẽ sắp xếp nhanh hơn nhiều.

Ví dụ

CREATE INDEX idx_sales_date ON sales (sale_date);

Giờ nếu bạn viết truy vấn sắp xếp theo sale_date, index sẽ phát huy tác dụng:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Không có index trên sale_date thì mỗi lần chạy truy vấn sẽ phải sắp xếp rất tốn tài nguyên — PostgreSQL sẽ "hoảng" tìm cách sắp xếp cho nhanh nhất.

Dùng filter với WHERE

Giảm lượng dữ liệu là kỹ thuật tối ưu hóa then chốt. Nếu bạn không cần xử lý cả 10 triệu dòng mà chỉ cần dữ liệu năm gần nhất — hãy thu hẹp phạm vi với WHERE!

Ví dụ

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';

Kiểu như bạn lọc nước bẩn qua rây để giữ lại thông tin hữu ích thôi.

Chọn window frame phù hợp

Khi dùng window functions với aggregation, ví dụ SUM(), chọn đúng window frame rất quan trọng. Nếu bạn dùng frame mặc định (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), PostgreSQL sẽ lấy tất cả dòng trước dòng hiện tại. Với bảng lớn thì không tối ưu chút nào.

Ví dụ: dùng ROWS

Nếu bạn chỉ cần lấy vài dòng trước dòng hiện tại, nên chỉ rõ bằng ROWS:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM sales;

Trong trường hợp này PostgreSQL chỉ xử lý 3 dòng (2 dòng trước + dòng hiện tại) cho mỗi dòng. Hiệu quả hơn nhiều so với mặc định phải xử lý hàng trăm dòng.

Giảm số lượng window functions

Mỗi window function được PostgreSQL xử lý riêng biệt. Nếu bạn dùng nhiều window functions, PostgreSQL có thể phải sắp xếp cho từng cái, làm truy vấn chậm đi. Tuy nhiên, nếu các tham số window (như PARTITION BY và ORDER BY) giống nhau, PostgreSQL có thể xử lý hiệu quả hơn.

Ví dụ: tối ưu với cùng một window

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

Cả hai function (SUM() và ROW_NUMBER()) dùng cùng một window. PostgreSQL chỉ cần sắp xếp một lần — quá ngon.

Partitioning bảng

Nếu bảng của bạn quá lớn, nên nghĩ đến việc chia nhỏ vật lý. PostgreSQL cho phép tạo partitioned tables để dữ liệu nằm ở các segment khác nhau. Cách này tăng tốc xử lý đáng kể.

Ví dụ tạo bảng partitioned

CREATE TABLE sales_partitioned (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

Sau đó tạo các partition, ví dụ theo năm:

CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

Giờ nếu bạn dùng WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31', PostgreSQL sẽ tự động chỉ truy cập đúng partition cần thiết.

Chi tiết về partitioning bảng bạn sẽ học kỹ hơn ở cuối khóa nha :P

Tránh dữ liệu thừa (SELECT chỉ lấy cái cần)

Chỉ chọn những cột cần thiết cho function và kết quả của bạn. Nếu window function chỉ cần product_id, sale_date và amount, đừng kéo theo cả "sở thú" bảng với dữ liệu sinh học khách hàng làm gì.

Ví dụ truy vấn "tiết kiệm"

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Dữ liệu ít hơn — PostgreSQL làm việc nhẹ hơn.

Dùng materialization (MATERIALIZED VIEW)

Nếu bạn thường xuyên chạy cùng một phép tính với window functions, có thể lưu kết quả vào materialized view. Materialized View lưu dữ liệu trên ổ cứng để tránh phải chạy lại truy vấn phức tạp nhiều lần.

Ví dụ tạo materialized view

CREATE MATERIALIZED VIEW sales_running_total AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Giờ bạn chỉ cần truy vấn dữ liệu thôi:

SELECT * FROM sales_running_total WHERE product_id = 10;

Lên kế hoạch truy vấn với EXPLAIN và EXPLAIN ANALYZE

Như các phần khác của SQL, bạn có thể dùng EXPLAIN hoặc EXPLAIN ANALYZE để xem PostgreSQL thực thi truy vấn thế nào — và chỗ nào bị nghẽn cổ chai.

Ví dụ phân tích truy vấn

EXPLAIN ANALYZE
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Công cụ này sẽ cho bạn biết PostgreSQL tốn thời gian ở đâu, từ đó tối ưu chỗ bị nghẽn.

Window functions là công cụ phân tích dữ liệu cực mạnh, nhưng phải dùng cẩn thận. Muốn nhanh? Chọn index, thêm filter, nhớ partition và đừng ngại dùng materialized view. PostgreSQL thích được dùng một cách thông minh lắm đó!

Trước Bài học trước

Kế tiếp Bài học tiếp theo

Bình luận

TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION