mysql - 使用来自另一个的 count(*) 值更新大表

Question

我有两张 table 。项目具有字段 id、title、number。另一个表(列表)中的相关字段(列表)是一个带有逗号分隔值的字段，这些值是项目的 ID。我希望 items.number 与相关 ID 出现在列表表中的次数相同。例如，此查询可以很好地实现此目的...

update items set number = (select count(*) from lists where list like concat('%',items.id,'%')) where title > 'Z'

但是如果我想更新整个项目(或者甚至只是以“Y”开头的标题，比起以 Z 开头的 200 个要多得多)，我会得到错误 - 要么“MySQL 服务器有消失了”或“查询执行被中断”。我认为这是因为查询要求太高。有没有更好的方法可以在服务器不崩溃的情况下实现这一目标？

Answer 1

嗯，糟糕的性能是您为设计糟糕的数据结构付出的代价。这是您的查询:

update items
    set number = (select count(*)
                  from lists
                  where list like concat('%',items.id,'%')
                 )
     where title > 'Z';

list like 语句建议您将项目 ID 存储为列表顺便说一下，您应该在语句中包含分隔符，这样您就不会像 1 matching 这样出现不匹配'10,11,12':

where concat(',', list, ',') like concat('%,', items.id, ',%')

(假设逗号作为分隔符)，或者:

where find_in_set(items.id, list) > 0;

也就是说，我想不出一种方法来加快这部分查询的速度。正确的解决方案是有一个关联表，称为 ListItems，每个列表和列表中的项目一行。然后您可以使用等值连接和索引来加速查询。