V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
ikaros
V2EX  ›  程序员

数据库查询时频繁变更的权重字段作为查询排序字段的最佳实践是?

  •  
  •   ikaros · 2023-01-30 15:00:09 +08:00 · 1584 次点击
    这是一个创建于 423 天前的主题,其中的信息可能已经有所发展或是发生改变。

    比如社区帖子排序,假如我们的主题列表中有一个代表权重的字段,权重计算规则简化如下:

    1. 创建主题时使用当前 unix 时间戳
    2. 每当有非楼主回复时更新该时间戳为当前 unix 时间戳

    现在查询帖子列表时根据权重排序.

    但是如果频繁更新索引列的值会严重影响性能,那么这种场景的最佳实践是什么呢?

    16 条回复    2023-02-01 13:55:44 +08:00
    eason1874
        1
    eason1874  
       2023-01-30 15:03:54 +08:00   ❤️ 1
    典型的写少读多,读写分离呗

    别整什么奇技淫巧了,稳健第一,性能不够就加机器。做社区,机器成本是最不值一提的
    corcre
        2
    corcre  
       2023-01-30 15:20:56 +08:00   ❤️ 1
    频繁修改单个字段?丢 Redis 里面🐶
    ikaros
        3
    ikaros  
    OP
       2023-01-30 15:28:11 +08:00
    @eason1874 一开始可能没有那么多资源,想找个实现简单,后续又好拓展的,典型的既要...又要...
    corcre
        4
    corcre  
       2023-01-30 15:48:45 +08:00   ❤️ 1
    简单点的就分冷热库咯, 写个 job 每天把超过 x 个月的老帖子&回贴归档到另一个库里面, 不是什么大论坛这点应能应该能处理过来吧, 反正我猜超过半个月的帖子就没几个人会看了
    totoro52
        5
    totoro52  
       2023-01-30 15:52:26 +08:00   ❤️ 1
    根据你的实际 ops 来吧,如果流量少的话一个 mysql 绰绰有余了, 不要想着花样了,如果真要就如同一楼说的读写分离
    fkdog
        6
    fkdog  
       2023-01-30 16:31:34 +08:00   ❤️ 3
    流量小,那就加机器,不要折腾。

    流量很大,那就上 mq ,每一次更新操作就发一条消息,
    消费方一次批量从队列里取 n 条消息,然后根据帖子 id 把消息分组,每组里取最新的那个时间戳用来更新 db 。
    update thread set last_update=123456789 where id=10000 and last_update<123456789; (考虑多个消费节点,故判断下 last_update 是否需要更新)。
    这样对于热门顶贴,多次更新操作都可以被合并为一条 sql 。
    sky857412
        7
    sky857412  
       2023-01-30 17:11:23 +08:00   ❤️ 1
    加个 log 表,创建帖子和非楼主回复时,增加 log ,然后 log 表,根据帖子 id 进行 distinct ,根据时间排序就是最新的了吧
    546L5LiK6ZOt
        8
    546L5LiK6ZOt  
       2023-01-30 18:00:19 +08:00   ❤️ 1
    好奇更新索引和更新普通列性能差别有多大

    如果更新成为瓶颈,一般都是要分库分表。如果影响查询,就像 1 楼说的读写分离。加个从库也不需要很多资源吧。
    piku
        9
    piku  
       2023-01-30 18:40:45 +08:00   ❤️ 1
    没理解帖子热度排序为啥要写数据库。十几年前 asp 的论坛,这部分是个内存数组,几乎是一直在变化
    ikaros
        10
    ikaros  
    OP
       2023-01-30 19:39:01 +08:00
    @piku 这个始终得有个地方做持久化, 不然冷启动的时候怎么排序呢?
    piku
        11
    piku  
       2023-01-30 20:11:27 +08:00   ❤️ 1
    @ikaros 您说的完全正确。但是考虑这玩意一直运行。当时冷启或重启后是没数据的(这个框框是空白)。随着有访问量,才出现的数据。
    如果是能容忍回滚的数据,隔一段时间一保存也是可行的。
    wangxiaoaer
        12
    wangxiaoaer  
       2023-01-30 20:15:26 +08:00   ❤️ 1
    @piku #11 我觉得这种方式挺合理,热度前 N 条数据可以定时持久化以备冷启动。

    除非整个站的默认排序就是按权重,就麻烦了。
    matrix1010
        13
    matrix1010  
       2023-01-30 21:58:20 +08:00   ❤️ 1
    回复肯定也存在某张表里吧,而且这个回复表应该有创建时间字段吧。如果可以接受延迟就每 5 秒扫一下回复表,找到更新的帖子然后批量更新帖子表。当然回复表肯定不是全表扫,每次扫完把最后一个 id 记下来,下次从这个 id 开始。
    dusu
        14
    dusu  
       2023-01-30 23:00:39 +08:00 via iPhone   ❤️ 1
    我的建议用位吧
    多设计点冗余位
    满足自己的排序需求即可
    例如 20 位长度的整型:

    3 位回帖人数 /4 回帖时间天数 /5 位发帖时间(距开站天数)/…

    等都可以当成排序因子设计
    这样可以达到热帖 时间筛选等多方需求
    当用户回复的帖子越后
    更新排序的因子就变越小
    甚至可能不会有变化
    自行变通控制即可

    不过考虑频繁更新的话
    肯定加个 redis 用 zset 之类的更合适
    一是性能稳定 列表不需要数据库排序 直接拿主键快
    二是回扫写回库里也还方便
    CEBBCAT
        15
    CEBBCAT  
       2023-01-31 12:36:24 +08:00
    本来想的是 Redis ZSet ,楼主提出持久化、冷启动后问题变得有挑战了起来,不错的问题👍
    siteshen
        16
    siteshen  
       2023-02-01 13:55:44 +08:00
    redis sorted set ,甚至还能方便增加其他排序因子:

    recent_score = timestamp
    hot_score = timestamp + commentCount * 600
    valuable_score = timestamp + valuable * likes * 6000

    何谓冷启动?如果是刚上新程序或者程序崩溃之类导致,启动时重新加载计算写入就是。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3801 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 10:35 · PVG 18:35 · LAX 03:35 · JFK 06:35
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.