我有一个用户关系表,它基本上存储了用户的所有单向关系。例如,用户A关注用户B;用户 X阻止用户 Y
用户 ID:
用户的 id 是bigints。这不像我认为使用整数真的会遇到瓶颈(除非我的应用程序增长到超过 20 亿用户,那就是:)),但据我了解,如果我整合社交网络注册,Facebook 使用 bigints对于他们的用户 ID,因此我必须将用户的 ID 保持为 bigint。
索引:
起初我想为两个用户(相关和相关)创建一个复合索引,但是由于两个索引都是 bigint,意味着每个 8 个字节,存储这样一个怪异的索引不是浪费吗?我最终只留下了一个列索引,但不确定在这种情况下这是否是最明智的决定。
我还想到了一个带有覆盖索引的变体 - 但在这种情况下,relation_type(follow, block) 不会成为索引/过滤的一部分,所以也许我应该将它包含在复合索引中,但是我会有 8+8+ 4字节的索引。在这种情况下,我不清楚空间和时间之间的权衡。对此事有任何想法将不胜感激。
第一个变体(单列索引):
CREATE TABLE user_relations (
relating_user_id bigint NOT NULL,
related_user_id bigint NOT NULL,
relation_type smallint NOT NULL,
created_at default current_timestamp,
PRIMARY KEY (relating_user_id),
FOREIGN KEY (relation_type) references user_relations_types (id)
-- will having only one index affect the performance really ?
-- should I include the type into the composite type ?
);
第二种变体(使用覆盖索引并省略 PK):
CREATE TABLE user_relations (
relating_user_id bigint NOT NULL,
related_user_id bigint NOT NULL,
relation_type smallint NOT NULL,
created_at default current_timestamp,
FOREIGN KEY (relation_type) references user_relations_types (id),
CREATE UNIQUE INDEX relating_user_related_user_relation_type_idx ON user_relations
(relating_user_id, related_user_id) INCLUDE (relation_type);
-- should I include the type into the composite type ?
);
索引使用空间,16 字节的密钥无需担心。
user_relations
所以你应该在 on上定义主键(relating_user_id, related_user_id)
。如果您需要按 搜索relation_type
,将列放入列表将无济于事INCLUDE
,因为此类列不能用作索引扫描的过滤器。我看到两个选项:
除了上面建议的主键之外,在
(relating_user_id, relation_type)
和上有额外的索引(related_user_id, relation_type)
。如果您想拥有更少的索引,请定义主键
(relating_user_id, relation_type, related_user_id)
并仅定义一个附加索引。如果数据库允许同一用户之间存在几种不同的关系,也许您可以接受它。要定义哪些索引的决定因素应该是您必须经常有效地服务的查询。设计这些查询,问题会变得更容易。