也许解决方案很明显,但是我似乎找不到一个好的解决方案.
在我即将进行的项目中,将有一个主表,其数据将被频繁读取.更新/插入/删除速度不是问题.
该主表中的项目与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 Moods; // can contain 0 - 100 items
public List SomeCategory; // can contain 0 - 100 items
// ...
}