Tối ưu hóa procedure với transaction: phân tích hiệu năng và rollback

Khi bạn code procedure, chúng thường là "trái tim" của database, thực hiện rất nhiều thao tác. Nhưng cũng chính chúng có thể là "nút thắt cổ chai", nhất là khi:

1. Chúng thực hiện thao tác không cần thiết (ví dụ: truy cập đi truy cập lại cùng dữ liệu).
2. Chúng dùng index không hiệu quả.
3. Làm quá nhiều thao tác trong một transaction.

Như một dev lão làng từng nói: "Tối ưu code tệ cũng giống như bảo thằng bạn lười chạy nhanh hơn". Vậy nên tối ưu procedure không chỉ là tăng tốc, mà là cải thiện tận gốc!

Giảm số lượng thao tác trong một transaction

Mỗi transaction trong PostgreSQL đều có overhead để quản lý thao tác của nó. Transaction càng to thì càng giữ lock lâu, càng dễ gây block cho user khác. Để giảm mấy cái này:

1. Đừng nhét quá nhiều thao tác vào một transaction.
2. Dùng EXCEPTION END để giới hạn thay đổi ở phạm vi nhỏ. Cái này hay nếu chỉ một phần thao tác cần rollback.
3. Chia transaction lớn thành nhiều transaction nhỏ (nếu logic app cho phép).

Ví dụ: chia insert dữ liệu hàng loạt thành "batch":

-- Ví dụ: Procedure để load batch với commit từng đợt
CREATE PROCEDURE batch_load()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    batch_cnt INT := 0;
BEGIN
    FOR r IN SELECT * FROM staging_table LOOP
        BEGIN
            INSERT INTO target_table (col1, col2) VALUES (r.col1, r.col2);
            batch_cnt := batch_cnt + 1;
        EXCEPTION
            WHEN OTHERS THEN
                -- Ghi log lỗi; thay đổi của phần tử này sẽ bị rollback
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- commit mỗi 1000 thao tác
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- commit cuối cùng
END;
$$;

Mẹo: nhớ là mỗi COMMIT sẽ lưu thay đổi, nên phải chắc là chia transaction không làm hỏng tính toàn vẹn dữ liệu nhé.

Dùng index để tăng tốc truy vấn

Giả sử bạn có bảng orders với cả triệu dòng, và hay truy vấn theo customer_id. Không có index thì query sẽ quét hết bảng:

CREATE INDEX idx_customer_id ON orders(customer_id);

Giờ các truy vấn kiểu này:

SELECT * FROM orders WHERE customer_id = 42;

sẽ chạy nhanh hơn nhiều, không cần quét hết bảng nữa.

Lưu ý: khi viết procedure, hãy đảm bảo các trường dùng trong filter, sort, join đều có index nhé.

Phân tích hiệu năng với EXPLAIN ANALYZE

EXPLAIN cho biết plan thực thi query (PostgreSQL sẽ chạy thế nào), còn ANALYZE thêm thống kê thực tế (ví dụ: mất bao lâu để chạy). Ví dụ điển hình:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Dùng nó trong procedure thế nào?

Bạn có thể "bóc tách" query phức tạp trong procedure, chạy riêng với EXPLAIN ANALYZE:

DO $$
BEGIN
    RAISE NOTICE 'Query Plan: %',
    (
        SELECT query_plan
        FROM pg_stat_statements
        WHERE query = 'SELECT * FROM orders WHERE customer_id = 42'
    );
END $$;

Ví dụ phân tích và cải thiện

Procedure ban đầu (chậm):

CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
    UPDATE sales
    SET total = (
        SELECT SUM(amount)
        FROM orders
        WHERE orders.sales_id = sales.id
    );
END $$ LANGUAGE plpgsql;

Chuyện gì xảy ra? Với mỗi dòng trong bảng sales sẽ chạy subquery SUM(amount), dẫn đến rất nhiều thao tác. Rất chậm.

Cách cải thiện:

CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
    UPDATE sales as s
    SET total = o.total_amount
    FROM (
        SELECT sales_id, SUM(amount) as total_amount
        FROM orders
        GROUP BY sales_id
    ) o
    WHERE o.sales_id = s.id;
END $$ LANGUAGE plpgsql;

Giờ subquery với SUM chỉ chạy một lần, update hết dữ liệu luôn.

Rollback dữ liệu khi có lỗi

Nếu có gì đó sai trong procedure, bạn có thể rollback chỉ một phần transaction. Ví dụ:

BEGIN
    -- Insert dữ liệu
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Block này giống rollback về savepoint bên trong!
        RAISE WARNING 'Lỗi khi cập nhật dữ liệu: %', SQLERRM;
END;

Thực hành: viết procedure xử lý đơn hàng ổn định

Giả sử bạn cần xử lý đơn hàng. Nếu có lỗi (ví dụ: hết hàng), đơn sẽ bị hủy và lỗi được ghi log.

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_in_stock INT;
BEGIN
    -- Kiểm tra tồn kho
    SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Không còn hàng trong kho';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... thao tác khác
    EXCEPTION
        WHEN OTHERS THEN
            -- Mọi thay đổi trong block này sẽ bị rollback!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Lỗi xử lý: ' || SQLERRM);
            RAISE NOTICE 'Lỗi xử lý đơn hàng: %', SQLERRM;
    END;

    -- Phần còn lại vẫn chạy nếu không có lỗi
    -- Có thể ghi log: đơn hàng xử lý thành công
END;
$$;

• Dù có lỗi thì đơn cũng không bị xử lý, và log sẽ vào bảng order_logs.
• Khi lỗi sẽ có savepoint bên trong, bạn không mất hết context đâu.

Nguyên tắc tối ưu và ổn định cho procedure

1. Dùng index cho query trong procedure.
2. Chia thao tác lớn thành batch nhỏ, xử lý từng đợt.
3. Biết log lỗi — tạo bảng riêng để log lỗi cho thao tác hàng loạt.
4. Rollback "từng phần" chỉ dùng block lồng với EXCEPTION.
5. Đừng dùng ROLLBACK TO SAVEPOINT trong PL/pgSQL — sẽ lỗi cú pháp đó.
6. Trong procedure chỉ dùng COMMIT/SAVEPOINT khi gọi ở chế độ autocommit của connection!
7. Phân tích plan của query nặng (EXPLAIN ANALYZE) ngoài procedure, trước khi tích hợp vào.

2

Nhiệm vụ

SQL SELF, mức độ, bài học

Đã khóa

Chia nhỏ giao dịch lớn thành các batch

Chia nhỏ giao dịch lớn thành các batch

Mở