考虑事务的过程优化：性能分析与回滚

当你写procedure的时候，它们经常变成你数据库的“心脏”，负责一堆操作。但这些procedure也可能变成“瓶颈”，特别是如果：

1. 它们做了没必要的操作（比如老是查同一批数据）。
2. index用得不高效。
3. 在一个transaction里做太多操作。

有个很懂的程序员说过：“让写得烂的代码跑快点，就像让懒人去跑步一样。”所以优化procedure不只是提速，更是打好基础！

减少transaction里的操作数量

每个PostgreSQL的transaction都会有维护自己操作的额外开销。transaction越大，锁持有得越久，被其他用户block的概率也越高。想减少这些影响：

1. 别把太多操作塞进一个transaction。
2. 用EXCEPTION END，把变更限制在本地。只需要部分回滚时很有用。
3. 把大的transaction拆成几个小的（如果你的业务逻辑允许的话）。

例子：把批量插入数据拆成“小包”：

-- 例子：分批加载，每批commit一次
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
                -- 记录error；这条数据的变更会被回滚
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- 每1000条commit一次
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- 最后一次commit
END;
$$;

小贴士：别忘了，每次COMMIT都会保存变更，所以提前确认拆分transaction不会破坏数据完整性。

用index加速查询

比如你有个orders表，有一百万条数据，经常按customer_id查。如果没index，查询会全表扫描：

CREATE INDEX idx_customer_id ON orders(customer_id);

现在像这样查：

SELECT * FROM orders WHERE customer_id = 42;

会快很多，避免全表扫描。

注意：写procedure时，确保用到的字段都建了index，特别是filter、排序、join用到的。

用EXPLAIN ANALYZE分析性能

EXPLAIN能显示查询的执行计划（PostgreSQL打算怎么执行），ANALYZE会加上实际执行的统计（比如花了多少时间）。典型例子：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

怎么在procedure里用？

你可以把procedure里的复杂查询单独拿出来，用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 $$;

分析和优化例子

原始procedure（慢）：

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;

发生了什么？对sales表的每一行都执行一次SUM(amount)子查询，操作次数爆炸，超慢。

优化后：

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;

现在SUM子查询只跑一次，所有数据一次性更新。

出错时的数据回滚

如果procedure里出错了，你可以只回滚一部分transaction。比如：

BEGIN
    -- 插入数据
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- 这个块相当于回滚到内部savepoint！
        RAISE WARNING '更新数据时出错: %', SQLERRM;
END;

实战：写个稳健的订单处理procedure

比如你的任务是：处理订单。如果过程中出错（比如库存不够），订单要取消，error要记录。

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_in_stock INT;
BEGIN
    -- 检查库存
    SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION '库存不足';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... 其他操作
    EXCEPTION
        WHEN OTHERS THEN
            -- 这个块里的变更都会回滚！
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, '处理出错: ' || SQLERRM);
            RAISE NOTICE '订单处理出错: %', SQLERRM;
    END;

    -- 如果没出错，后面的代码继续执行
    -- 可以记录：订单处理成功
END;
$$;

• 即使出错，订单不会被处理，日志会写进order_logs表。
• 出错时会触发内部savepoint，你不会丢掉所有上下文。

procedure优化和稳定的基本规则

1. procedure里的查询要用index。
2. 大操作要拆成小批次（batch），分步处理。
3. 学会记录error——单独建个表专门存批量操作的error日志。
4. “部分”回滚只能用带EXCEPTION的嵌套块。
5. 别在PL/pgSQL里用ROLLBACK TO SAVEPOINT——会报语法错。
6. procedure里只有在autocommit连接模式下才能用COMMIT/SAVEPOINT！
7. 重查询的执行计划（EXPLAIN ANALYZE）要在procedure外分析，集成前先搞清楚。