PostgreSQL 9.6存储过程性能优化：批量插入产品时触发器性能问题

嘿，我完全懂你遇到的糟心事——用行级触发器处理批量插入/删除几千条产品时，逐行触发更新用户的产品计数，这速度慢得简直让人抓狂，毕竟几百上千次重复的更新操作纯纯是冗余消耗。

问题的核心在于行级触发器会为每一条被插入/删除的记录单独触发一次，哪怕这些记录都属于同一个用户。我们的优化思路是改成语句级触发器，让整个批量操作只触发一次，然后在触发器函数里批量处理所有受影响的用户，每个用户只更新一次计数。

下面给你两种优化方案，你可以根据自己的场景选：

方案1：批量重新计算用户产品计数（适合产品表不大的场景） #

这种方法逻辑简单，直接针对本次操作涉及的所有用户重新计算产品总数，不容易出错：

先创建插入操作的批量更新函数 #

CREATE OR REPLACE FUNCTION update_product_count_batch_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 一次性更新所有本次插入涉及的用户的产品数量
    UPDATE users u
    SET product_count = (SELECT COUNT(*) FROM products p WHERE p.user_id = u.user_id)
    WHERE u.user_id IN (SELECT user_id FROM NEW); -- NEW在语句级触发器中是包含所有插入行的临时表
    RETURN NULL; -- 语句级触发器不需要返回NEW/OLD
END;
$$ LANGUAGE plpgsql;

创建对应的语句级触发器 #

CREATE TRIGGER trigger_product_count_after_insert
AFTER INSERT ON products
FOR EACH STATEMENT -- 关键：整个插入语句只触发一次
EXECUTE FUNCTION update_product_count_batch_insert();

同理，处理删除操作的函数和触发器 #

CREATE OR REPLACE FUNCTION update_product_count_batch_delete()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users u
    SET product_count = (SELECT COUNT(*) FROM products p WHERE p.user_id = u.user_id)
    WHERE u.user_id IN (SELECT user_id FROM OLD); -- OLD是包含所有删除行的临时表
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_product_count_after_delete
AFTER DELETE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION update_product_count_batch_delete();

方案2：增量更新计数（适合产品表较大的场景，性能更优） #

如果你的products表数据量很大，每次重新COUNT(*)会比较耗时，那可以改成统计本次操作中每个用户新增/删除的产品数量，直接对users表的计数做加减：

插入操作的增量更新函数 #

CREATE OR REPLACE FUNCTION update_product_count_increment_insert()
RETURNS TRIGGER AS $$
DECLARE
    user_change RECORD;
BEGIN
    -- 统计本次插入中每个用户新增的产品数量
    FOR user_change IN SELECT user_id, COUNT(*) AS added_count FROM NEW GROUP BY user_id LOOP
        UPDATE users
        SET product_count = COALESCE(product_count, 0) + user_change.added_count
        WHERE user_id = user_change.user_id;
        -- COALESCE处理用户还没有任何产品时product_count为NULL的情况
    END LOOP;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

删除操作的增量更新函数 #

CREATE OR REPLACE FUNCTION update_product_count_decrement_delete()
RETURNS TRIGGER AS $$
DECLARE
    user_change RECORD;
BEGIN
    -- 统计本次删除中每个用户减少的产品数量
    FOR user_change IN SELECT user_id, COUNT(*) AS removed_count FROM OLD GROUP BY user_id LOOP
        UPDATE users
        SET product_count = GREATEST(product_count - user_change.removed_count, 0)
        -- GREATEST确保计数不会变成负数
        WHERE user_id = user_change.user_id;
    END LOOP;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

对应的语句级触发器 #

-- 插入触发器
CREATE TRIGGER trigger_product_count_increment_insert
AFTER INSERT ON products
FOR EACH STATEMENT
EXECUTE FUNCTION update_product_count_increment_insert();

-- 删除触发器
CREATE TRIGGER trigger_product_count_decrement_delete
AFTER DELETE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION update_product_count_decrement_delete();

为什么这样能提升性能？ #

不管你插入/删除多少条产品记录，只要是同一个批量操作（比如一次INSERT语句插入1000条），触发器只会触发一次。然后我们通过分组统计，每个受影响的用户只执行一次UPDATE操作，而不是几百上千次，性能提升会非常明显。

如果还要处理UPDATE操作（比如修改产品所属的用户），可以类似地创建语句级触发器，同时处理OLD和NEW中的user_id，先减少原用户的计数，再增加新用户的计数。

内容的提问来源于stack exchange，提问作者Ahmad hamza