假设我想将用户提供的数据存储在名为的表中post
。
user_id
用户数据按列隔离select sum(likes_count) where user_id = $
应该很快select * from post where id = $
应该很快
您能否帮助从性能、索引和典型用例的角度比较一下这些模式?
注意:char(24) 值是 MongoDB 的 ObjectId 十六进制字符串。它们在整个数据库中是唯一的。
方案一:
使用PRIMARY KEY (id)
和二级索引(user_id)
CREATE TABLE posts_v1 (
id CHAR(24) NOT NULL,
user_id CHAR(24) NOT NULL,
likes_count INT,
PRIMARY KEY (id),
KEY 'user_id_idx' (user_id)
);
使用PRIMARY KEY (user_id, id)
和辅助唯一索引(id)
方案 2:
CREATE TABLE posts_v2 (
id CHAR(24) NOT NULL,
user_id CHAR(24) NOT NULL,
likes_count INT,
PRIMARY KEY (user_id, id),
UNIQUE KEY 'id_idx' (id)
);
问题:
1- 在哪些情况下,一种模式的性能会优于另一种模式?例如,按 user_id、id 或两者过滤的查询。
2-posts_v2
使用 进行查询时,架构是否使用较少的 InnoDB 页面读取user_id
?我假设在 的情况下posts_v2
,表在物理上按 分组user_id
,因此我们可以用较少的页面扫描检索所有用户帖子,我的假设正确吗?
3- 数据完整性和维护:这两种设计对数据完整性、维护或未来模式演变是否有任何影响?
4- 用例:哪些典型的用例或工作负载可能有利于一种设计而不是另一种设计?
我感谢您提供的任何见解或建议!
从问题 3 开始 - 数据完整性:不,这两个模式并不相同。事实上,它们差别太大,以至于其他问题实际上并不相关。
带有 PK 的表
id
在字段中将具有唯一值id
:但带有 PK 的表在字段
user_id,id
中可以有重复的值:id
但是如果要比较单场PK和多场PK的话,单场PK通常表现更好,当然也有例外,但是多场表现更好的情况很少见。
对你的问题更实际的回答是:首先,决定
natural key
你的情况是什么,它是一个多字段还是一个单字段?从业务角度而不是从性能角度考虑,选择更好的。之后,你可以unique index
在另一个字段(或多个字段)上添加辅助字段。这样,你将获得两种类型查询的覆盖范围——通过一个或多个字段,无论它们是 PK 还是索引的一部分。模式 2 适合这些查询。
PRIMARY KEY (user_id, id)
是加快“SUM”查询速度的关键。在 InnoDB 中,PRIMARY KEY
与数据聚类。select sum(likes_count) FROM ... where user_id = $
只需遍历表的子集即可获得SUM
该用户的。select * from post where id = $
是“点查询”。因此,如果 PK 的第一列是二级索引,那么速度会足够快id
。(PK 会更快,但我们谈论的是毫秒级。)“用户数据通过 user_id 列进行隔离”——你想要多少“隔离”?上面的语句说明你已经有足够的性能了。
至于那 4 个问题...
(user_id, ...)
有助于 SUM 查询时提到了这一点。