最近在優(yōu)化一個(gè)云開發(fā)用lookup實(shí)現(xiàn)的多表嵌套查詢SQL，發(fā)現(xiàn)測(cè)試環(huán)境最大的表數(shù)據(jù)量還不到2W，但是整體查詢耗時(shí)竟然要2，3s，這讓我覺得有些意外，能加的索引也都加了，有點(diǎn)頭大。。。

主要是以下幾個(gè)表：

1. user-card-list（清單表，大概1.9W條）
2. user-comment（評(píng)論表，大概500條）
3. user-info（用戶信息表，大概9000條）
4. black-list（黑名單表，12條）

具體的業(yè)務(wù)是分頁查詢出清單，并關(guān)聯(lián)出發(fā)布清單的用戶信息、清單評(píng)論信息、評(píng)論者的用戶信息，同時(shí)過濾掉黑名單用戶。在測(cè)試SQL的過程中發(fā)現(xiàn)在pipeline中用到的父表字段其實(shí)不會(huì)使用索引，如果是用localField/foreignField關(guān)聯(lián)表時(shí)索引可以生效，但是遺憾的是這種方式不可用嵌套查詢...

寫慣了關(guān)系型數(shù)據(jù)庫(kù)SQL，通常是會(huì)把分頁以及過濾條件寫在SQL最后的位置。但是在非關(guān)系型數(shù)據(jù)庫(kù)，如果盡量把過濾條件或者分頁的SQL前置，可能會(huì)有意想不到的效果。

下面的這個(gè)SQL我把match跟limit前置后查詢速度從2，3s降到了3，400ms，有點(diǎn)苦笑不得[捂臉]。。

在這里蠻記錄一下。或許對(duì)看到的小伙伴可以有一點(diǎn)點(diǎn)小啟發(fā)。

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]),
        //排序字段，由公開時(shí)間+ID組成
        cursor: $.concat(['$lightAt', '', '$_id']),
    })
    .match(_.expr($.and(
        $.eq(['$light', 'Y']),
        //$.gt(['$cursor', '2023-05-20 23:58:23ozzW05Gch7jMMhsn1r_SWLGdGtF0_add_1563634289607'])
        $.or([
            //當(dāng)前清單的發(fā)布用戶不在黑名單中，直接展示
            $.eq(['$inBlackList', false]),
            //當(dāng)前清單的發(fā)布用戶在黑名單中，且是本人瀏覽時(shí)，直接展示
            $.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();