mysql - 实体-属性-值表(EAV 表)是否需要单独的 ID 字段作为主键或复合主键(如entityId+attributeId)？

Question

这是我的 EAV 表结构(我知道 EAV 很糟糕，但我需要存储的属性数量超过一万个，因此规范化表不起作用)

Table name - propertyAssign

entityId - int - indexed
attributeId - smallint - composite index with valueId
valueId - smallint - composite index with attributeId

我只需要通过两种方式查询这个表。

1. 从 propertyAssign 中选择 attributeId、valueId，其中实体Id=x
2. 从 propertyAssign 中选择实体 ID，其中 attributeId=x 且 valueId=y

所以我相应地添加了索引。

问题 => 我需要在此处添加主键吗？

Answer 1

Renzo 的答案涵盖了选择#1，但不是

select entityId from propertyAssign where attributeId=x and valueId=y

这需要

INDEX(attributeId, valueId, entityId)

将会

• 高效，因为它完美地处理 WHERE 子句，并且
• 效率更高，因为 INDEX 包含所需的所有字段(“覆盖索引”)。

是的，这实际上使表的大小加倍(数据+PK，然后是包含所有数据的索引)。但这比必须对 Select #2 进行表扫描要好得多。

闻起来像 attributeId 和 valueId 是指向具有实际字符串和值的“标准化”表的链接？完成代码所需的 JOIN 在哪里？如果您在单独的SELECT 中执行此操作，则效率低于JOIN，因为它需要两次(或三次？)到服务器的往返。

EAV 是一种非常糟糕的设计模式；祝你好运。

编辑

提到的两个 SELECT 将从这两个索引中受益:

 INDEX(entityId, attributeId, valueId) -- for Select #1
 INDEX(attributeId, valueId, entityId) -- for Select #2

并且，由于该三元组是唯一的，因此一个或另一个INDEX也可能是PRIMARY KEY。现在，选择哪个...

当INSERTing时，让PK以entityId开头使得实体的所有三元组“聚集”在一起。这会加快 INSERT 和 SELECT #1 的速度。所以我投票赞成PK。将另一个作为 PK 不会加速 INSERT。这是因为创建具有大量属性的实体会导致大量的分散写入。

两个 SELECT 中的每一个都由一个或另一个索引进行最佳处理；因此 SELECT 尽可能快。好吧，我忽略了您规范化属性名称和值的事实。这是我稍后会咬你的，并提出更难听的问题。

我说这是一个糟糕的设计，部分原因是非常相似的模式的基准。压力测试在表中填充的内容超出了可以缓存的内容。插入速率不能超过每秒 7 个实体。那是在 RAID strip 磁盘满负荷运行的情况下。标准化属性等导致大量随机磁盘命中。