最近在优化一个云开发用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();