在我的网站上,对帖子的回复是根据一种算法进行排序的,该算法取决于多种因素:反应、回复质量、用户声誉、报告、回复回复等等……(这些只是几个例子)
在大多数帖子中,对回复进行排序很快,但有些帖子有 20 万条回复,并且还在增长,并且对所有这些回复进行排序需要超过 1 秒。
我知道VIRTUAL GENERATED
专栏。然而:
只有当算法依赖于同一个表的列时,我才能创建一个索引
VIRTUAL GENERATED
列。但是,该算法取决于其他表中的因素,例如用户信誉。
不幸的是,如果一个表有一个
VIRTUAL GENERATED
列,就不可能再这样做了ALTER ONLINE TABLE
。由于该表每天都在增长,因此带来了巨大的不便。
我有什么替代方案来优化这种复杂的算法,以便快速/实例地对增长很多的帖子的回复进行排序?
Reddit 如何按“最佳/最佳”非常快速地对成千上万的回复进行排序?
这些主要是几年前的帖子,但仍在积极回复。
我正在考虑维护某种“物化视图”,它会在每个新回复或回复发生某些事情时更新(如反应等)。然后根据此视图的键对回复进行排序(与其余部分合并)。但是,这会大大增加所需的存储空间和数据库上的更新/插入。这是一个合理的解决方案吗?
(我使用 InnoDB,每个表的文件,没有分区。MariaDB 10.8。)
当您插入一行(回复等)时,将排序所需的所有信息收集到一个单独的表格中。正如您建议的“物化”视图;但你必须做所有的工作。
建议存储回复应该涉及调用存储过程,该存储过程将在当前表中插入信息,并在类似视图的表中添加/替换行。
该表将只有排序所需的列;如果可能的话,它将有一个索引指标,使得排序变得微不足道。
如果您需要
RANK()
orDENSE_RANK()
,PERCENTILE()
那将相当容易计算——假设您有 MySQL 8.0 或 MariaDB 10.2。查看几乎没有停机时间
pt-online-schema-change
的方法。ALTER TABLE
提高性能的一种方法是改变
PRIMARY KEY
实现集群。听起来好像(user_id, thread_id, post_id)
会有帮助。(我假设“post_id”包括帖子和回复)。通常人们有
PRIMARY KEY(id)
,这会导致诸如“回复”之类的事情散布在整个表格中。这会导致大量的 I/O。集群将它们聚集在一起,从而大大减少了 I/O。