aggregation-framework - 使用管道与 foreign/localField 进行慢速查找
问题描述
我正在尝试进行查找阶段，但与使用 localField/foreignField 的常规管道相比，在查找阶段使用管道时它真的很慢。下面的结构经过简化，但具有数组结构。
艺术家收藏（100 位艺术家）
{title: ‘the Beatles’, songs: [ObejctId(1), ObejctId(2), ObejctId(2)]}
….
歌曲集（1000首歌曲）
{_id: 1: title: ‘Help1’}
{_id: 1: title: ‘Come together’}
{_id: 1: title: ‘Something’}
…
我想通过歌曲数组中的 objectID 到歌曲集合中歌曲的 ObjectId 将所有歌曲加入当前艺术家。
我已经看过这篇关于索引的帖子解释了 查找聚合性能不佳
此管道使用 localField/foreignField大约需要60 毫秒。
{ $match: { songs: { $exists: true, $ne: [] } } },
{
$lookup: {
from: 'songs',
localField: 'songs',
foreignField: '_id',
as: 'songs',
},
},
{
$project: {
slug: 1,
title: 1,
songs: 1,
user: 1,
name: 1,
profileImage: 1,
songCount: {
$size: '$songs',
},
},
},
对于相同的数据，此管道大约需要2.5 秒。
{ $match: { songs: { $exists: true, $ne: [] } } },
{
$lookup: {
from: 'songs',
let: {
songIds: '$songs',
},
pipeline: [
{
$match: {
$expr: {
$in: ['$_id', '$$songIds'],
},
},
},
],
as: 'songs',
},
},
{
$project: {
slug: 1,
title: 1,
songs: 1,
user: 1,
name: 1,
profileImage: 1,
songCount: {
$size: '$songs',
},
},
},
我已经查看了索引并在这样的歌曲数组上的艺术家收藏中创建了一个索引
db.artists.createIndex({songs:1});（尝试了 asc/desc）但它似乎在时间上没有任何区别。
使用上面创建的索引运行聚合时.explain('executionStats')，我得到
"winningPlan" : {
"stage" : “IXSCAN",
......
}
两个查询的任何地方。如果我放弃我得到的索引
"winningPlan" : {
"stage" : "COLLSCAN",
......
}
两个查询的任何地方。
但是执行时间没有区别。两者都需要大约 2 秒（管道）秒和 60 毫秒（foreignField）。
添加索引时，我会 "totalKeysExamined" : 865,同时进行查询并且没有索引"totalKeysExamined" : 0.
在比较两个解释结果时，统计数据之间的唯一区别是 executionTimeMillis 和查询本身。
那么索引真的被使用了，还是我做错了什么或者我误解了索引？
Mongo 4.2.8 使用 Robo 3T
来自管道的完整解释，索引约 2.5 秒
/* 1 */
{
"stages" : [
{
"$cursor" : {
"query" : {
"songs" : {
"$exists" : true,
"$ne" : []
}
},
"fields" : {
"name" : 1,
"profileImage" : 1,
"slug" : 1,
"songs" : 1,
"title" : 1,
"user" : 1,
"_id" : 1
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "<collectionName>.artists",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"songs" : {
"$exists" : true
}
},
{
"songs" : {
"$not" : {
"$eq" : []
}
}
}
]
},
"queryHash" : "F267DA83",
"planCacheKey" : "F7BD81F7",
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"songs" : {
"$exists" : true
}
},
{
"songs" : {
"$not" : {
"$eq" : []
}
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"songs" : 1.0
},
"indexName" : "songs_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"songs" : [
"songs"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"songs" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : []
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 99,
"executionTimeMillis" : 2161,
"totalKeysExamined" : 865,
"totalDocsExamined" : 117,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"songs" : {
"$exists" : true
}
},
{
"songs" : {
"$not" : {
"$eq" : []
}
}
}
]
},
"nReturned" : 99,
"executionTimeMillisEstimate" : 0,
"works" : 866,
"advanced" : 99,
"needTime" : 766,
"needYield" : 0,
"saveState" : 7,
"restoreState" : 7,
"isEOF" : 1,
"docsExamined" : 117,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 117,
"executionTimeMillisEstimate" : 0,
"works" : 866,
"advanced" : 117,
"needTime" : 748,
"needYield" : 0,
"saveState" : 7,
"restoreState" : 7,
"isEOF" : 1,
"keyPattern" : {
"songs" : 1.0
},
"indexName" : "songs_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"songs" : [
"songs"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"songs" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 865,
"seeks" : 1,
"dupsTested" : 865,
"dupsDropped" : 748
}
}
}
}
},
{
"$lookup" : {
"from" : "songs",
"as" : "songs",
"let" : {
"songIds" : "$songs"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$in" : [
"$_id",
"$$songIds"
]
}
}
}
]
}
},
{
"$project" : {
"_id" : true,
"title" : true,
"slug" : true,
"profileImage" : true,
"songs" : true,
"user" : true,
"name" : true,
"songCount" : {
"$size" : [
"$songs"
]
}
}
}
],
"serverInfo" : {
"host" : "MacBook-Pro.local",
"port" : 27017,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1.0
}
用 foreignFiel/localField 解释 ~60ms
/* 1 */
{
"stages" : [
{
"$cursor" : {
"query" : {
"songs" : {
"$exists" : true,
"$ne" : []
}
},
"fields" : {
"name" : 1,
"profileImage" : 1,
"slug" : 1,
"songs" : 1,
"title" : 1,
"user" : 1,
"_id" : 1
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "<collectionName>.artists",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"songs" : {
"$exists" : true
}
},
{
"songs" : {
"$not" : {
"$eq" : []
}
}
}
]
},
"queryHash" : "F267DA83",
"planCacheKey" : "F7BD81F7",
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"songs" : {
"$exists" : true
}
},
{
"songs" : {
"$not" : {
"$eq" : []
}
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"songs" : 1.0
},
"indexName" : "songs_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"songs" : [
"songs"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"songs" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : []
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 99,
"executionTimeMillis" : 73,
"totalKeysExamined" : 865,
"totalDocsExamined" : 117,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"songs" : {
"$exists" : true
}
},
{
"songs" : {
"$not" : {
"$eq" : []
}
}
}
]
},
"nReturned" : 99,
"executionTimeMillisEstimate" : 0,
"works" : 866,
"advanced" : 99,
"needTime" : 766,
"needYield" : 0,
"saveState" : 7,
"restoreState" : 7,
"isEOF" : 1,
"docsExamined" : 117,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 117,
"executionTimeMillisEstimate" : 0,
"works" : 866,
"advanced" : 117,
"needTime" : 748,
"needYield" : 0,
"saveState" : 7,
"restoreState" : 7,
"isEOF" : 1,
"keyPattern" : {
"songs" : 1.0
},
"indexName" : "songs_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"songs" : [
"songs"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"songs" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 865,
"seeks" : 1,
"dupsTested" : 865,
"dupsDropped" : 748
}
}
}
}
},
{
"$lookup" : {
"from" : "songs",
"as" : "songs",
"localField" : "songs",
"foreignField" : "_id"
}
},
{
"$project" : {
"_id" : true,
"songs" : true,
"user" : true,
"name" : true,
"profileImage" : true,
"title" : true,
"slug" : true,
"songCount" : {
"$size" : [
"$songs"
]
}
}
},
{
"$project" : {
"_id" : true,
"songs" : true,
"user" : true,
"name" : true,
"profileImage" : true,
"title" : true,
"slug" : true,
"songCount" : {
"$size" : [
"$songs"
]
}
}
},
{
"$match" : {
"songCount" : {
"$not" : {
"$eq" : 0.0
}
}
}
}
],
"serverInfo" : {
"host" : "MacBook-Pro.local",
"port" : 27017,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1.0
}
谢谢！
解决方案
推荐阅读
- sharepoint-2013 - 用于表单的带有 InfoPath 的 SharePoint O365
- python - python中的程序切片
- python - 我想在 Django Rest Framework 中获取 ForeignKey 的 url
- javascript - instance.render 不是 reactjs 中的函数错误
- javascript - Angular 6 setTimeout Module not found: Error: Can't resolve 'timers'
- javascript - Python相当于选择第一个非空参数
- go - 使用 gorilla mux 提供静态 html 文件
- python - Pandas Dataframe - 更改数据结构（重新组织）
- sql-server - EF 2.1 中的播种日期
- google-apps-script - 在 Google 表格插件中，如何检查用户是否已购买订阅？