评论

记录云开发lookup多表嵌套查询优化

可以尽可能把过滤条件前置,从而减少查询量减少耗时

最近在优化一个云开发用lookup实现的多表嵌套查询SQL,发现测试环境最大的表数据量还不到2W,但是整体查询耗时竟然要2,3s,这让我觉得有些意外,能加的索引也都加了,有点头大。。。

主要是以下几个表:

  1. user-card-list(清单表,大概1.9W条)
  2. user-comment(评论表,大概500条)
  3. user-info(用户信息表,大概9000条)
  4. black-list(黑名单表,12条)

具体的业务是分页查询出清单,并关联出发布清单的用户信息、清单评论信息、评论者的用户信息,同时过滤掉黑名单用户。在测试SQL的过程中发现在pipeline中用到的父表字段其实不会使用索引,如果是用localField/foreignField关联表时索引可以生效,但是遗憾的是这种方式不可用嵌套查询...

写惯了关系型数据库SQL,通常是会把分页以及过滤条件写在SQL最后的位置。但是在非关系型数据库,如果尽量把过滤条件或者分页的SQL前置,可能会有意想不到的效果。

下面的这个SQL我把match跟limit前置后查询速度从2,3s降到了3,400ms,有点苦笑不得[捂脸]。。

在这里蛮记录一下。或许对看到的小伙伴可以有一点点小启发。

db.collection('user-card-list').aggregate()
    .lookup({
        from: 'black-list',
        let: {
            openid: '$openid'//将变量openid的值等于user-card-list表的openid,在pipeline可以使用,let需要和pipeline一起使用
        },
        pipeline: $.pipeline()
            .match(_.expr($.and([
                $.eq(['$openid', '$$openid']),
            ])))
            .done(),
        as: 'blackList',
    })
    .addFields({
        inBlackList: $.gt([$.size('$blackList'), 0]),
        //排序字段,由公开时间+ID组成
        cursor: $.concat(['$lightAt', '', '$_id']),
    })
    .match(_.expr($.and(
        $.eq(['$light', 'Y']),
        //$.gt(['$cursor', '2023-05-20 23:58:23ozzW05Gch7jMMhsn1r_SWLGdGtF0_add_1563634289607'])
        $.or([
            //当前清单的发布用户不在黑名单中,直接展示
            $.eq(['$inBlackList', false]),
            //当前清单的发布用户在黑名单中,且是本人浏览时,直接展示
            $.and([
                $.eq(['$inBlackList', true]),
                $.eq(['$openid', 'ozzW05Gch7jMMhsn1r_SWLGdGtF0']),
            ])
        ])
    )))
    //按cursor降序排序
    .sort({cursor: -1})
    //分页前置,提升查询速度
    .limit(30)
    .lookup({
        from: 'user-comment',
        let: {
            id: '$_id'//将变量id的值等于user-card-list表的_id,在pipeline可以使用,let需要和pipeline一起使用
        },
        pipeline: $.pipeline()
            .match(_.expr($.eq(['$belongTo', '$$id'])))
            //按createAt降序
            .sort({createAt: -1})
            .lookup({
                from: 'user-info',
                let: {
                    replyOpenid: '$replyOpenid'//将变量replyOpenid的值等于user-comment表的replyOpenid,在pipeline可以使用,let需要和pipeline一起使用
                },
                pipeline: $.pipeline()
                    .match(_.expr($.eq(['$openid', '$$replyOpenid'])))
                    .done(),
                as: 'replyUserInfoList',
            })

            .done(),
        as: 'userCommentList',
    })

    .project({
        momentContent: 1,
        author: 1,
        cursor: 1,
        lightAt: 1,
        comments: $.reverseArray('$commentsReverse'),
        userCommentList: 1,
        likes: 1,
        wishes: 1,
        time: '$lightAt',
    })
    .end();


最后一次编辑于  2023-05-26  
点赞 4
收藏
评论
登录 后发表内容