也许解决方案很明显,但是我似乎找不到一个好的解决方案.

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

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

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

>选择所有已分配给类别A,B,C,…且LIMIT X,Y的项目
>计算已分配给类别A,…的所有项目

我对如何为上述数据库创建数据库的最初想法是这样的(我猜是经典方法)：

首先,为四个类别中的每个类别创建一个类别表：

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

那么我将有一个项目表：

id   - PK,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性能,因此我使用了真实的数据量(项目表中有100.000条记录,每个类别中有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 ...

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

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

但是性能也不是很好.

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

就是这样.我想不出什么了,除了可能污染很多项目表外,例如category_1_1到category_4_100,每个字段都包含1或0.但这可能会导致select的WHERE子句中出现许多AND且似乎没有也是个好主意.

那么,我有什么选择呢？还有更好的主意吗？

编辑：作为对Cory Petosky的回应,““一个项目在一个类别内可以具有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
    // ...
}