php - 大规模的一对多性能
问题描述
我已经建立了一个与post
具有一对多关系的新闻提要post_reaction
。这个概念很简单,一个帖子可以被点赞,每个点赞都存储在post_reaction
表格中,旁边还有点赞者和反应类型(点赞、喜欢等)
一切正常™,但是随着事情的扩展,性能下降,即随着post_reaction
表的增长。
出于测试目的,我生成了 200 个帖子,并给每个帖子 1,000 个回复。这导致post_reaction
表中存储了 200,000 个总反应。
我的 Twig 模板提供了一个帖子列表,限制为 20 个。当模板迭代显示每个帖子时,它会调用post.reactions|length
来计算反应的数量。这将执行以下数据库查询:
SELECT
t0.reaction AS reaction_1,
t0.id AS id_2,
t0.created AS created_3,
t0.post_id AS post_id_4,
t0.user AS user_5
FROM
post_reaction t0
WHERE
t0.post_id = ?
对于我正在渲染的 20 个帖子,每次运行此查询平均需要 4-7 毫秒。这总计约 100 毫秒的数据库查询,只是为了计算帖子。
这似乎并不算太糟糕,但是我们观察到在应用程序中处理这么多数据时会产生一些开销。
查看整个请求的分析器,我们看到以下内容: 我们在此请求中的总处理时间为585 毫秒
components/news_post.html.twig
post.reactions|length
是触发数据库查询的调用组件。如果我们在不询问反应的情况下发起相同的请求,我们会观察到以下情况。
我们在这个请求中的总处理时间是179 毫秒
快 406 毫秒/69.4%。我相信这主要归因于学说中的开销,而它将 20,000 行处理为对象,仅供我们稍后计算。
为了减轻这种情况,我想看看将反应加入我的帖子查询是否会有所帮助。
SELECT
p0_.replies_allowed AS replies_allowed_0,
p0_.highlight_date AS highlight_date_1,
p0_.title AS title_2,
p0_.content AS content_3,
p0_.id AS id_4,
p0_.created AS created_5,
p0_.updated AS updated_6,
p0_.news_feed_id AS news_feed_id_7,
p0_.created_by_id AS created_by_id_8,
p0_.updated_by_id AS updated_by_id_9
FROM
post p0_
INNER JOIN post_reaction p1_ ON (p1_.post_id = p0_.id)
WHERE
p0_.news_feed_id = ?
ORDER BY
CASE WHEN p0_.highlight_date > ? THEN 0 ELSE 1 END ASC,
p0_.created DESC
LIMIT
20
但是,它会导致LIMIT 20
查询中的子句出现问题,因为由于此数据集中的反应数量,加入反应只允许返回一篇帖子。
我不确定我是否应该继续开发一种使加入成为可能的方法,或者探索一种替代方法,无论是什么。理想情况下,我想减少 406 毫秒的额外执行时间,因为它几乎占总页面处理时间的 70%,只是为了计算喜欢..
编辑:根据要求,输出show create table post_reaction
CREATE TABLE `post_reaction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`post_id` int(11) DEFAULT NULL,
`user` int(11) DEFAULT NULL,
`reaction` int(11) NOT NULL,
`reaction_timestamp` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_1B3A8E564B89032C` (`post_id`),
KEY `IDX_1B3A8E568D93D649` (`user`),
CONSTRAINT `FK_1B3A8E564B89032C` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`),
CONSTRAINT `FK_1B3A8E568D93D649` FOREIGN KEY (`user`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=200786 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
解决方案
(第二个查询)不要
JOIN
因为post_reaction
你没有使用它的任何列。的复杂性
ORDER BY
使得不可能更快地查看所有 1000 个反应。因此,LIMIT
对性能的影响很小。请提供
SHOW CREATE TABLE post_reaction
,我们可以在那里进行一些改进。但是你当然需要一些以.开头的索引post_id
。PRIMARY KEY
通过重新排列从该列开始,我们可能会得到一些改进。(我对控制器或树枝一无所知。 它们似乎是昂贵的部分?)
更多的
“计算 [for each post] 的反应次数”——这是一个不会花费很长时间的单个 SQL 查询:
SELECT post_id,
COUNT(*) AS reaction_count
FROM post_reaction
GROUP BY post_id;
不遍历帖子;一次没有 20 个;只需简单地通过该表中的索引即可完成所有操作。
我在 92 个国家/地区的 50 万个城市的表上尝试了等效查询。耗时 0.13 秒。
这里的教训是,当要求 SQL 在很多行上做很多相同的事情时,它会发光。
推荐阅读
- node.js - 我们如何从单个不和谐频道加载所有消息?
- java - Jmeter使用查询参数调用rest url
- design-patterns - 终端和非终端符号
- kotlin - 为什么编译器认为这个 if 语句是一个表达式?
- python - 如何获取包含列表或值的熊猫列的唯一值?
- sql-server - 停用缓冲池 SQL Server 2017
- javascript - NestJs - 无法在 RolesGuard 中获取用户上下文
- android - 使用 CardView + RecyclerView 的 ListView 不起作用
- c++ - Qt5 复制(复制)一个可执行文件
- metabase - 使用 Cloud Identity-Aware 代理替换网站的 Google 登录