mysql - 在 1 个查询中更新多行及其所有父项的多对多计数器缓存

Question

考虑一个博客应用程序，它包含帖子、类别表和一个将帖子与一个或多个类别链接起来的查找表。类别是分层的。帖子可以分配给任何类别，而不仅仅是叶节点。

类别表有一个 post_count 字段，它缓存分配给特定类别的帖子数。它还具有用于 MPTT 的 parent_id、lft 和 rght 列。

但它还有一个 under_post_count 字段，用于缓存分配给它的不同帖子的数量或它的任何子类别。这很有用，因此您可以显示类别的分层列表，并在其旁边显示分配给它的帖子数，或其子项之一。

我的应用程序已经到了这样的地步，即在创建带有类别的帖子后，编辑类别或删除类别后，我有一个新旧类别的类别 ID 列表，其 post_count 字段需要更新。我希望我接下来可以做的是在一个查询中，更新所有这些已识别类别的 under_post_count 字段，以及它们的所有父级，以及分配给每个类别的不同帖子的数量，或者它的任何 child 。

这是为类别创建表和一些测试数据所需的 SQL:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rght` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `post_count` int(11) NOT NULL DEFAULT '0',
  `under_post_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `categories_posts` (
  `category_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`,`post_id`)
) ENGINE=MyISAM;

INSERT INTO `categories` (`id`, `parent_id`, `lft`, `rght`, `name`) VALUES
(1, NULL, 1, 8, 'Cat 1'),
(4, 1, 2, 3, 'Cat 1.1'),
(5, 1, 4, 5, 'Cat 1.2'),
(6, 1, 6, 7, 'Cat 1.3'),
(2, NULL, 9, 16, 'Cat 2'),
(7, 2, 10, 11, 'Cat 2.1'),
(8, 2, 12, 13, 'Cat 2.2'),
(9, 2, 14, 15, 'Cat 2.3'),
(3, NULL, 17, 24, 'Cat 3'),
(10, 3, 18, 19, 'Cat 3.1'),
(11, 3, 20, 21, 'Cat 3.2'),
(12, 3, 22, 23, 'Cat 3.3');

运行几次为 categories_posts 表创建一些测试数据:

INSERT IGNORE INTO `categories_posts` (`category_id`, `post_id`) 
SELECT `id`, CEILING(10 * RAND()) FROM `categories` ORDER BY RAND() LIMIT 6

谁能解决这个问题，非常感谢您的帮助？

Answer 1

这里有几种给猫剥皮的方法(假设是 5.1 和触发器)

• 您可以从应用层更新所有内容

• 您可以触发对 post_count 的更新来自 categories_posts并触发更新(级联)到 under_post_count来自 categories

• 最后，您可以从 categories_posts 触发所有更新

另外，根据类别的实际数量，您可能不需要反规范化 under_post_count因为用它来获取它应该相当容易且便宜

SELECT c.id, SUM(cc.post_count) 
FROM categories c 
LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
GROUP BY c.id;

获取完全匹配的实际计数是

SELECT c.id, COUNT(*) 
FROM categories c 
LEFT JOIN categories_posts cp ON c.id = cp.post_id 
GROUP BY c.id;

将两者结合起来得到包括层次结构在内的计数

SELECT c.id, COUNT(*) 
FROM categories c 
LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
LEFT JOIN categories_posts cp ON cc.id = cp.post_id
GROUP BY c.id;

编辑

从上面构造更新语句不应该那么难

UPDATE categories 
SET post_count = (SELECT COUNT(*) 
                  FROM categories_posts cp 
                  WHERE cp.post_id = categories.id)

应该适用于 post_count

under_post_count 的情况是不同的，因为 mysql 不喜欢听到在 where 部分提到目标表，因此你必须做一些像这样的怪事

UPDATE categories LEFT JOIN 
       (SELECT c.id, COUNT(*) AS result 
        FROM categories c 
        LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
        INNER JOIN categories_posts cp ON cc.id = cp.post_id
        GROUP BY c.id) AS x ON categories.id = x.id
SET under_post_count = x.result

EDIT2
实际上，上述所有查询都存在错误 - 每当我加入类别和帖子时，我都应该加入 cc.id = cp.category_id而不是 cp.post_id ，然后我没有检查。不想更正...但仅在最后一个查询中

UPDATE categories LEFT JOIN 
       (SELECT c.id, COUNT(*) AS result 
        FROM categories c 
        LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
        INNER JOIN categories_posts cp ON cc.id = cp.category_id
        INNER JOIN posts p ON cp.post_id = p.id
        WHERE p.status = 'published'
        GROUP BY c.id) AS x ON categories.id = x.id
SET under_post_count = x.result,
    post_count = (SELECT COUNT(*) 
                  FROM categories_posts cp 
                  WHERE cp.category_id = categories.id)

EDIT3
只是几点注意事项:

• 以上查询将修复 under_post_count和 post_count无论数据状态如何，
• 如果您的数据访问层被适本地抽象和保护，并且如果您可以确保原子性，那么有些查询会更便宜 - 这些查询只会执行 post_count = post_count +/- 1在适当的状态记录上(类似于 under_post_count )，
• 如果您不能从应用程序级别可靠地模拟触发器，检查您是否需要运行上述查询可能仍然更便宜(即使 mysql 在这方面非常好，但如果您想成为 DB 不可知论者)，或者采用一些策略，您通常只增加/减少计数器并且只定期重新计算数字。