记录云开发lookup多表嵌套查询优化
最近在优化一个云开发用lookup实现的多表嵌套查询SQL，发现测试环境最大的表数据量还不到2W，但是整体查询耗时竟然要2，3s，这让我觉得有些意外，能加的索引也都加了，有点头大。。。 主要是以下几个表： user-card-list（清单表，大概1.9W条）user-comment（评论表，大概500条）user-info（用户信息表，大概9000条）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();
