存储带有多个类别标签的项目-位屏蔽?

也许解决办法是显而易见的,但我似乎找不到一个好的。

在我即将进行的项目中,将有一个主表,它的数据将被频繁地读取。更新/插入/删除速度不是问题。

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

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

• 选择已分配给类别A、B、C…的所有项目。带极限x,y
• 统计分配给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 ...

并涉及 the category 表,将有4个或更多的lookup/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 性能,我用实际数据量测试了它(在 项目 表中,每个类别中有50-80个关系),但速度没有我预期的快,即使索引已经到位。我也试过用 WHERE EXISTS 而不是 INNER JOIN 选择时。

我的第二个想法是 项目 上面的表将数据反规范化。

读后 this blog post 关于使用位掩码,我尝试了一下,并为每个类别分配了一个位值:

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

所以,如果 项目 被贴上标签 category 1.1 和 category 1.3 ,它有一个位掩码 5 ,然后将其存储在一个字段中 item.bitmask 我可以这样查询:

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

但表现也不太好。

这种位屏蔽方法的问题是:当涉及位运算符时,甚至当涉及到 位掩码 将是类型 BIGINT 我只能处理多达64个关系,但我需要支持多达100个 每类 .

就是这样。除了污染 项目 包含许多字段的表,如 category_1_1 高达 category_4_100 每个包含1或0。但这会导致很多人 AND 在 WHERE select的子句,这似乎也不是一个好主意。

那么,我有什么选择呢?有什么好主意吗?

编辑:作为对CoryPetosky评论的回应,“一个项目在一个类别中可以有50-100个或更多的关系”是什么意思?以下内容:

为了使它更具体, 项目 表表示图像。图像是在情绪分类的其他标准(情绪将是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
    // ...
}