也许解决方案很明显，但我似乎找不到合适的解决方案。

在我接下来的项目中，会有一个主表，它的数据会被频繁读取。更新/插入/删除速度不是问题。

该主表中的项目与 4 个或更多类别相关联。一个项目可以有 50 - 100 或更多的关系在一个类别中。

将对数据库执行的最常见操作:

• 选择所有已分配给类别 A、B、C 的所有项目，X、Y 为 LIMIT
• 计算已分配到类别 A、B、C 的所有项目，...

我对如何为上述内容创建数据库的第一个想法是这样的(我猜是经典方法):

首先，我为四个类别中的每一个创建一个 category 表:

id   - PK, int(11), index   
name - varchar(100)

然后我将有一个item表:

id   - PK, int(11), index
... some more data fields, about 30 or so ...

为了关联类别表，将有 4 个或更多查找/MM 表，如下所示:

id_item     - int(11)
id_category - int(11)

查询看起来像这样:

select
item.*

from
item

inner mm_1 on mm_1.id_item = item.id
inner join cat_1 on cat_1.id = mm_1.id_category and cat_1.id in (1, 2, ... , 100)

inner mm_2 on mm_2.id_item = item.id
inner join cat_2 on cat_2.id = mm_2.id_category and cat_2.id in (50, 51, ... , 90)

当然，上述方法对 MM 表有效，但由于应用程序应提供非常好的 SELECT 性能，我使用真实世界的数据量(item 中的 100.000 条记录)对其进行了测试 表，每个类别中有 50 - 80 个关系)，但它没有我预期的那么快，即使有索引也是如此。我还尝试在选择时使用 WHERE EXISTS 而不是 INNER JOIN。

我的第二个想法是只使用上面的 item 表对数据进行反规范化。

看完this blog post关于使用位掩码，我试了一下并为每个类别分配了一个位值:

category 1.1 - 1
category 1.2 - 2
category 1.3 - 4
category 1.4 - 8
... etc ...

因此，如果一个 item 被标记为 category 1.1 和 category 1.3，它的位掩码为 5，然后我将其存储在字段 item.bitmask 中，我可以像这样查询它:

select count(*) from item where item.bitmask & 5 = 5

但是性能也不是很好。

这种位掩码方法的问题:当涉及位运算符时，mysql 不使用任何索引，即使 item.bitmask 的类型为 BIGINT 我也只能处理最多 64 个关系，但我需要支持最多 100 个每个类别。

就是这样。除了可能用许多字段污染 item 表之外，我想不出更多的东西，例如 category_1_1 到 category_4_100 每个包含 1 或0. 但这可能会导致 select 的 WHERE 子句中出现许多 AND，这似乎也不是一个好主意。

那么，我有哪些选择？还有更好的想法吗？

编辑:作为对 Cory Petosky 评论““一个项目在一个类别中可以有 50 - 100 或更多关系”是什么意思。”的回应？是什么意思？

为了使它更具体，item 表表示一个图像。图像是按情绪分类的其他标准之一(情绪将是 4 个类别之一)。所以它看起来像这样:

Image:
     - Category "mood":
         - bright
         - happy
         - funny
         - ... 50 or so more ...
     - Category "XYZ":
         - ... 70 or so more ...

如果我的图像表是 C# 中的一个类，它将如下所示:

public class Image {
    public List<Mood> Moods; // can contain 0 - 100 items
    public List<Some> SomeCategory; // can contain 0 - 100 items
    // ...
}

最佳答案

这个呢(伪代码):

Item (image)
    Id         PK, int(11)
    Name       varchar(100)

Category (mood, xyz)
    Id         PK, int(11)
    Name       varchar(100)

Relations (happy, funny)
    Id         PK, int(11)
    Name       varchar(100)

ItemCategories
    Id         PK, int(11)
    ItemId     FK, int(11)
    CategoryId FK, int(11)

ItemCategoryRelations
    ItemCategoriesId FK, int(11)
    RelationId       FK, int(11)

SELECT *
  FROM Item 
  JOIN ItemCategories ON Item.Id = ItemCategories.ItemId
 WHERE ItemCategories.CategoryId IN (1, 2, ..., 10)

以下版本少用一张表，但不支持没有关系的类别，关系不能复用。因此，如果符合您的数据结构要求，它才有效:

Item (image)
    Id         PK, int(11)
    Name       varchar(100)

Category (mood, xyz)
    Id         PK, int(11)
    Name       varchar(100)

Relations (happy, funny)
    Id         PK, int(11)
    CategoryId FK, int(11)
    Name       varchar(100)

ItemRelations 
    ItemId     FK, int(11)
    RelationId FK, int(11)

SELECT *
  FROM Item 
  JOIN ItemRelations ON Item.Id = ItemRelations.ItemId
  JOIN Relations ON Relations.Id = ItemRelations.RelationsId
 WHERE Relations.CategoryId IN (1, 2, ..., 10)

关于mysql - 存储标有多个类别的项目 - 位掩码？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/1845513/