记录云开发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();