如何高效维护SQL数据库中表的统计数据（Stats表）

嘿，我来帮你搞定这个Stats统计数据表的维护问题～你之前那种每次改完原表就全表扫一遍计算平均值的方式，数据量小的时候还行，数据一多肯定越来越慢，而且还得操心Stats表里没对应记录的情况，这里给你几个实用的优化思路：

1. 增量更新代替全量计算 #

与其每次都重新计算整个表的平均值，不如在Stats表里维护累计值和计数，平均值可以实时计算（或者用计算列存储）。这样每次新增/修改/删除数据时，只需要更新累计值和计数，完全避免全表扫描：

首先调整Stats表的结构：

CREATE TABLE Stats (
    identifier VARCHAR(50) PRIMARY KEY,
    totalX DECIMAL(18,2),  -- 存储x的总和
    countX INT,            -- 存储对应identifier的记录数
    averageX DECIMAL(18,2) GENERATED ALWAYS AS (totalX / NULLIF(countX, 0)) STORED  -- 自动计算平均值
);

针对不同操作的处理方式：

• 新增数据时：

INSERT INTO Things (x, y) VALUES (10, 'id1');
-- 自动处理Stats表存在/不存在的情况
INSERT INTO Stats (identifier, totalX, countX)
VALUES ('id1', 10, 1)
ON DUPLICATE KEY UPDATE
totalX = totalX + 10,
countX = countX + 1;

• 修改数据时：

-- 先锁定旧数据避免并发问题，获取旧值
SET @old_x = (SELECT x FROM Things WHERE id = 1 FOR UPDATE);
UPDATE Things SET x = 15 WHERE y = 'id1' AND id = 1;
-- 更新Stats表的累计值
UPDATE Stats
SET totalX = totalX - @old_x + 15
WHERE identifier = 'id1';

• 删除数据时：

SET @del_x = (SELECT x FROM Things WHERE id = 1 FOR UPDATE);
DELETE FROM Things WHERE id = 1;
-- 调整Stats表的总和与计数
UPDATE Stats
SET totalX = totalX - @del_x,
countX = countX - 1
WHERE identifier = 'id1';
-- 可选：如果计数变为0，删除这条冗余记录
DELETE FROM Stats WHERE identifier = 'id1' AND countX = 0;

2. 用触发器自动维护Stats表 #

手动写更新语句容易遗漏，不如用触发器把Stats表的更新逻辑自动化，你操作原表时Stats表会自动同步：

比如针对Things表的INSERT触发器：

DELIMITER //
CREATE TRIGGER after_things_insert
AFTER INSERT ON Things
FOR EACH ROW
BEGIN
    INSERT INTO Stats (identifier, totalX, countX)
    VALUES (NEW.y, NEW.x, 1)
    ON DUPLICATE KEY UPDATE
    totalX = totalX + NEW.x,
    countX = countX + 1;
END //
DELIMITER ;

UPDATE触发器（处理同identifier修改和跨identifier修改两种情况）：

DELIMITER //
CREATE TRIGGER after_things_update
AFTER UPDATE ON Things
FOR EACH ROW
BEGIN
    IF OLD.y = NEW.y THEN
        -- 同一分组内修改x值，只调整总和
        UPDATE Stats
        SET totalX = totalX - OLD.x + NEW.x
        WHERE identifier = OLD.y;
    ELSE
        -- 跨分组修改，需要更新两个分组的统计
        UPDATE Stats
        SET totalX = totalX - OLD.x,
        countX = countX - 1
        WHERE identifier = OLD.y;
        
        INSERT INTO Stats (identifier, totalX, countX)
        VALUES (NEW.y, NEW.x, 1)
        ON DUPLICATE KEY UPDATE
        totalX = totalX + NEW.x,
        countX = countX + 1;
    END IF;
END //
DELIMITER ;

DELETE触发器：

DELIMITER //
CREATE TRIGGER after_things_delete
AFTER DELETE ON Things
FOR EACH ROW
BEGIN
    UPDATE Stats
    SET totalX = totalX - OLD.x,
    countX = countX - 1
    WHERE identifier = OLD.y;
    
    -- 可选：计数为0时删除Stats表的对应记录
    DELETE FROM Stats WHERE identifier = OLD.y AND countX = 0;
END //
DELIMITER ;

3. 跨数据库兼容的“无记录自动插入”方案 #

上面用的INSERT ... ON DUPLICATE KEY UPDATE是MySQL语法，如果你用其他数据库：

• PostgreSQL可以用INSERT ... ON CONFLICT DO UPDATE
• SQL Server可以用MERGE语句

以PostgreSQL为例：

INSERT INTO Stats (identifier, totalX, countX)
VALUES ('id1', 10, 1)
ON CONFLICT (identifier) DO UPDATE
SET totalX = Stats.totalX + EXCLUDED.totalX,
countX = Stats.countX + EXCLUDED.countX;

这样不管Stats表有没有对应记录，都能正确插入或更新，不用额外做判断。

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