MySQL中如何自动维护一对多关联表的计数（替代COUNT查询）

问题背景 #

在MySQL场景下，需要实现一对多关联表的自动计数：新增关联条目时计数自增，删除时自减。直接用COUNT()查询在百万级数据高频查询场景下性能开销极大，目前通过后端Python配合额外计数表维护，但希望找到更优的自动方案，优先考虑MySQL原生支持。由于涉及老旧代码库及外部API依赖，大规模修改代码风险高，更倾向通过调整MySQL表结构解决。

示例表结构：

create table object
(
    object_id       int auto_increment   primary key,
    name            varchar(120)         not null
);

create table item
(
    item_id       varchar(63)         not null,
    object_id int                     not null,
    primary key (object_id, item_id)
);
insert into object (name) VALUES ("hello");
insert into item (item_id, object_id) VALUES
      ("item1", 1),
      ("item2", 1),
      ("item3", 1),
      ("item4", 1);

现有后端维护方案依赖额外计数表+Python逻辑，存在原子性风险（比如item插入成功但计数更新失败），且代码改动成本高。

最优方案：主表新增计数字段 + 触发器（零代码改动） #

直接在object表中新增计数字段，通过MySQL触发器自动维护，完全不需要修改后端代码，完美适配你的需求。

1. 调整主表结构 #

给object表新增item_count字段，用于存储对应item的数量：

ALTER TABLE object ADD COLUMN item_count BIGINT NOT NULL DEFAULT 0;

2. 创建插入触发器 #

当item表新增记录时，自动给对应object的计数加1：

DELIMITER //
CREATE TRIGGER trg_item_insert_increment
AFTER INSERT ON item
FOR EACH ROW
BEGIN
    UPDATE object 
    SET item_count = item_count + 1 
    WHERE object_id = NEW.object_id;
END //
DELIMITER ;

3. 创建删除触发器 #

当item表删除记录时，自动给对应object的计数减1：

DELIMITER //
CREATE TRIGGER trg_item_delete_decrement
AFTER DELETE ON item
FOR EACH ROW
BEGIN
    UPDATE object 
    SET item_count = item_count - 1 
    WHERE object_id = OLD.object_id;
END //
DELIMITER ;

4. 初始化历史数据计数 #

如果已经存在历史关联数据，需要先同步现有计数：

UPDATE object o
JOIN (
    SELECT object_id, COUNT(*) AS cnt 
    FROM item 
    GROUP BY object_id
) i ON o.object_id = i.object_id
SET o.item_count = i.cnt;

方案优势 #

• 零代码改动：完全基于MySQL原生实现，不需要修改任何后端逻辑，规避老旧代码库的改动风险
• 原子性保障：触发器在item操作的事务内执行，事务成功则计数更新，事务回滚则计数也回滚，不会出现数据不一致
• 极致性能：查询计数直接从object表读取，无需执行COUNT()聚合查询，百万级数据下性能提升显著

备选方案：物化视图（MySQL 8.0.19+适用） #

如果使用MySQL 8.0.19及以上版本，可以用物化视图自动维护计数，适合对实时性要求不是极高的场景。

创建物化视图 #

CREATE MATERIALIZED VIEW mv_item_counter
AS SELECT object_id, COUNT(*) AS counter
FROM item
GROUP BY object_id
WITH DATA;

设置自动刷新（可选） #

通过事件调度器定期刷新视图数据：

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;

-- 每天凌晨自动刷新
DELIMITER //
CREATE EVENT evt_refresh_item_counter
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
BEGIN
    REFRESH MATERIALIZED VIEW mv_item_counter;
END //
DELIMITER ;

方案特点 #

• 不需要修改主表结构，但实时性不如触发器方案，适合非实时查询场景
• 物化视图的刷新会有一定性能开销，需根据业务场景调整刷新频率

注意事项 #

• 触发器性能：超高并发场景下，触发器的UPDATE操作可能带来行锁竞争，可结合批量插入/删除优化，一般场景下无压力
• 权限控制：建议限制item_count字段的手动修改权限，只允许触发器更新，避免人为操作导致计数不一致

内容的提问来源于stack exchange，提问作者Federico Baù