Phân tích các lỗi phổ biến khi tạo thủ tục phân tích

Hôm nay, để kết thúc hành trình epic với PL/pgSQL này, tụi mình phải hiểu rõ: lỗi trong thủ tục phân tích là không thể tránh khỏi. Tại sao? Vì làm phân tích là đụng tới dữ liệu to đùng, tính toán phức tạp và đôi khi điều kiện cũng khá lắt léo. Truy vấn hay thủ tục càng phức tạp thì càng giống như mê cung, chỉ cần lạc một bước là kết quả đã sai bét.

May mà đa số lỗi đều quen thuộc và có thể đoán trước (và phòng tránh được). Giờ mình sẽ phân tích từng lỗi một nhé.

1. Thiếu index trên các trường quan trọng

Index giống như GPS trong thế giới database vậy. Không có nó, database phải đi bộ từng dòng một trong bảng. Với bảng nhỏ thì còn chịu được, chứ dữ liệu mà lên tới triệu dòng thì truy vấn của bạn sẽ chậm hơn cả Windows XP chạy trên Pentium III luôn ấy.

Ví dụ, bạn có bảng đơn hàng và muốn tính tổng doanh số tháng trước:

SELECT SUM(order_total)
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month';

Nếu trường order_date không có index, PostgreSQL sẽ phải quét toàn bộ bảng (Seq Scan). Và gần như lúc nào cũng chậm.

Giải pháp: dùng index thôi! Chỉ cần lệnh này là đủ:

CREATE INDEX idx_order_date ON orders (order_date);

Bây giờ PostgreSQL sẽ tìm kiếm theo trường order_date nhanh hơn nhiều.

Sử dụng truy vấn không hiệu quả

Có những truy vấn nhìn thì đẹp mà chạy thì như cục gạch bê tông thay vì chìa khóa. Ví dụ, dùng subquery mà hoàn toàn có thể thay bằng join, hoặc filter dư thừa.

Ví dụ như này:

SELECT product_id, SUM(order_total)
FROM orders
WHERE product_id IN (SELECT id FROM products WHERE category = 'electronics')
GROUP BY product_id;

Làm thế này sẽ tốt hơn:

SELECT o.product_id, SUM(o.order_total)
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE p.category = 'electronics'
GROUP BY o.product_id;

Cách này giúp PostgreSQL khỏi phải chạy subquery cho từng dòng và tăng tốc độ lên đáng kể.

Cấu trúc bảng tạm sai

Bảng tạm (temporary table) là công cụ mạnh nếu bạn dùng hợp lý. Nhưng nếu quên thêm cột cần thiết hoặc index, bảng tạm sẽ thành nút thắt cổ chai, làm chậm cả thủ tục.

Ví dụ nhé. Tạo bảng tạm để tính toán trung gian:

CREATE TEMP TABLE temp_sales AS
SELECT region, SUM(order_total) AS total_sales
FROM orders
GROUP BY region;

Nhưng sau đó bạn lại cần filter theo cột total_sales, mà lại không có index trên trường này.

Trước khi dùng bảng tạm, hãy nghĩ xem bạn sẽ thao tác với nó thế nào. Nếu cần filter theo cột nào, thêm index cho cột đó luôn:

CREATE INDEX idx_temp_sales_total_sales ON temp_sales (total_sales);

Lỗi tính toán (ví dụ chia cho 0)

Chia cho 0 là lỗi kinh điển trong phân tích. SQL không dễ dãi bỏ qua đâu, nó sẽ dừng truy vấn ngay lập tức.

Ví dụ, bạn muốn tính giá trị trung bình của đơn hàng:

SELECT SUM(order_total) / COUNT(*) AS avg_order_value
FROM orders;

Nếu bảng orders không có dữ liệu, sẽ bị chia cho 0 và truy vấn lỗi ngay.

Để tránh, hãy xử lý trường hợp đếm bằng 0 như sau:

SELECT
    CASE 
        WHEN COUNT(*) = 0 THEN 0
        ELSE SUM(order_total) / COUNT(*)
    END AS avg_order_value
FROM orders;

Không log và kiểm soát quá trình thực thi

Thủ tục PL/pgSQL có thể rất phức tạp, gồm nhiều bước: từ tính toán trung gian đến báo cáo cuối cùng. Nếu có lỗi ở đâu đó mà không log, bạn sẽ không biết nó hỏng ở bước nào và vì sao.

Ví dụ, bạn viết thủ tục tính toán chỉ số mà quên kiểm tra dữ liệu ở từng bước. Kết quả là cả thủ tục sập khi gặp dữ liệu bất ngờ (như bảng rỗng chẳng hạn).

Để tránh, hãy log ở mỗi bước quan trọng trong thủ tục. Ví dụ:

RAISE NOTICE 'Bắt đầu tính doanh số';
-- Code của bạn ở đây...

RAISE NOTICE 'Module % hoàn thành thành công', module;

Với thủ tục phức tạp hơn, nên lưu log vào bảng riêng:

CREATE TABLE log_analytics (
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_message TEXT
);

Trong thủ tục, thêm dòng này:

INSERT INTO log_analytics (log_message)
VALUES ('Thủ tục hoàn thành thành công');

Vấn đề hiệu năng do thiếu tối ưu hóa

Tối ưu hóa không chỉ cho truy vấn mà còn cho cả thủ tục. Nếu nhiều người dùng cùng chạy thủ tục, nó có thể thành nút thắt cổ chai của hệ thống.

Ví dụ, thủ tục này tính lại chỉ số cho tất cả các vùng, dù chỉ cần dữ liệu cho một vùng:

CREATE OR REPLACE FUNCTION calculate_sales()
RETURNS VOID AS $$
BEGIN
    -- Tính lại cho tất cả các vùng
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Cách này gây tải thừa.

Giải pháp: thêm tham số để lọc dữ liệu, truyền vào vùng cần thiết:

CREATE OR REPLACE FUNCTION calculate_sales(p_region TEXT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    WHERE region = p_region
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Bây giờ thủ tục sẽ không xử lý dữ liệu thừa, truy vấn cũng nhanh hơn.

Bỏ qua công cụ phân tích hiệu năng

Các tool như EXPLAIN ANALYZE là bạn thân, giúp bạn biết truy vấn chậm ở đâu và sửa thế nào. Nếu bạn viết thủ tục mà không phân tích hiệu năng, thì giống như coder lượng tử mà không có oscilloscope – chạy thì chạy, chứ bên trong ra sao thì chịu.

Ví dụ nhé. Vấn đề trong truy vấn này sẽ lộ ra khi dùng EXPLAIN ANALYZE:

SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

Truy vấn này không hiệu quả vì hàm EXTRACT() làm mất tác dụng của index.

Giải quyết như sau. Phân tích truy vấn bằng:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_date >= DATE '2023-01-01' AND order_date < DATE '2024-01-01';

Làm sao tránh lỗi phổ biến?

Để tránh lỗi, hãy làm theo các best practice sau:

1. Dùng index cho các trường dùng để filter hoặc join.
2. Tối ưu truy vấn: bỏ subquery dư thừa, dùng JOIN.
3. Log quá trình thực thi. Sẽ dễ debug hơn nếu có gì đó sai.
4. Luôn kiểm tra thủ tục bằng tool như EXPLAIN ANALYZE.
5. Thấy hiệu năng kém? Nghĩ tới partition hoặc chỉnh lại logic truy vấn.

Giờ thì bạn đã có đủ kiến thức để dự đoán và phòng tránh những lỗi có thể khiến team phân tích của bạn mất cà phê và Wi-Fi chỉ vì truy vấn chậm rồi nhé.