MySQL |自动将相关表作为计数器计数(一对多)

Question

问题

有没有一种方法可以自动计数相关表(一对多)，其中计数将自动递增/递减，具体取决于我们是否添加或删除相关项。

所以，我显然可以只做一个计数，但出于性能原因，它花费了数百万条记录，而且是多次查询。作为我的解决方案，而不是每次计数，我实际上会创建一个计数器，其中在添加新的相关项目时添加1，或在删除或取消引用另一个项目时删除1

为了做到这一点，我可以创建另一个用作计数器的表，并在不计数的情况下查询该表。

有没有更好的方法，最好是自动的

示例

模式

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);

对象"；你好"；有4个项目：

select count(*) from item where object_id = 1;
-- ouput: 4

然而，我发现作为一种变通方法，我可以创建一个计数器(使用后端Python)，每次执行CRUD操作时，计数器都会更新。例如：

计数器模式

create table item_counter
(
counter bigint                  NOT NULL DEFAULT 0,
object_id int                   NOT NULL  primary key
)

因此，现在我们可以在中的ORM中做这样的事情(同样，它将在Python中处理，但这并不重要，只是举个例子：

-- create object AND item_counter
insert into object (name) VALUES ("hello");
-- create in the same time a counter 
insert into item_counter (object_id) VALUES ((SELECT object_id FROM object where name = "hello"));
-- create items
insert into item (item_id, object_id) VALUES  ("item1", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item2", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item3", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item4", 2);
update item_counter set counter = counter + 1 where object_id = 2;
-- select the counter instead
select counter from item_counter where object_id = 2;

如果是在python中，它看起来会像

# pseudo ORM code 
class ItemORM:
def save(self, item_id, object_id):
self.orm.save(item_id, object_id)
counter = self.orm.get_counter(object_id)
counter.add()

那么，有没有更好的方法，尤其是MySQL可以自动完成的事情呢？

更多上下文

如果你想知道为什么，让我们假设我在一个大型代码库中工作，许多遗留代码和API依赖于外部客户端使用，并且目前没有计数器实现但我的想法只是克服它。因此，更改大部分代码是非常危险的，相反，调整MYSQL表可能是更好的解决方案。

Answer 1

MySQL本身并没有任何自动完成您所描述的操作的方法。这取决于您实现一个解决方案，或者使用MySQL的附加技术。

我至少能想到以下解决方案：

• 使用触发器来维护计数器。INSERT时递增，DELETE时递减。如果并发插入和删除的速率非常高，您可能会遇到吞吐量问题。

  create trigger ti after insert on item
  for each row 
  update item_counter 
  set counter = counter + 1
  where object_id = NEW.object_id;
  create trigger td after delete on item
  for each row
  update item_counter 
  set counter = counter - 1
  where object_id = OLD.object_id;
  

  演示：https://dbfiddle.uk/tgJM0I4m

• 使用具体化视图。PlanetScale最近提供了一款类似物化视图的产品(截至2022年11月的测试版)，但它比这更复杂。阅读他们的描述：https://planetscale.com/blog/how-planetscale-boost-serves-your-sql-queries-instantly

• 使用数据仓库。像MySQL这样的OLTP数据库通常将表存储为行，因此它针对CRUD类型的查询进行了优化。数据仓库或OLAP数据库将表存储为列，所以像您描述的聚合查询这样的聚合查询是它的优势。但这意味着要建立一个独立的数据库技术，并不断地将MySQL数据同步到数据仓库。

• WARP是一个MySQL 8.0存储引擎，它似乎结合了列存储和物化视图。看见https://www.leapdb.com/我和创建WARP的开发人员合作过，他非常出色。