英文:
What is best index to optimize this mongodb aggregate pipeline Query?
问题
尝试了几个索引，但它会扫描集合中的所有文档。我只需要保持扫描的文档/返回文档的比例在1000以下（这将有助于减少Atlas警报，提醒比率已超过1000）。另外，我需要知道在聚合查询中使用hint是否是一个好选择。
"pipeline": [
{
"$match": {
"status": {
"$in": [
"STATUS"
]
},
"$and": [
{
"pId": {
"$nin": [
null,
null
]
}
},
{
"bId": {
"$nin": [
null,
null
]
}
},
{
"dueDate": {
"$nin": [
null,
null
]
}
},
{
"proposedPaymentDate": {
"$nin": [
null,
null
]
}
},
{
"amount": {
"$gt": 0
}
}
],
"pInvoiceId": {
"$exists": false
}
}
},
{
"$sort": {
"pId": 1,
"amount": -1
}
},
{
"$lookup": {
"from": "pcollection",
"localField": "pId",
"foreignField": "lId",
"as": "p"
}
},
{
"$unwind": {
"path": "$p"
}
},
{
"$match": {
"p.aId": {
"$in": [
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
}
]
}
}
},
{
"$skip": 400
},
{
"$limit": 400
}
]
英文:
Tried few indexes but it goes scans all the documents in collection. I just need to keep the scanned docs / return docs ratio under 1000. (this will helps to reduce the atlas alerts mentioning the ratio has exceed the 1000). Also I need to know usage of hint in aggregate query is a good option or not.
"pipeline": [
{
"$match": {
"status": {
"$in": [
"STATUS"
]
},
"$and": [
{
"pId": {
"$nin": [
null,
null
]
}
},
{
"bId": {
"$nin": [
null,
null
]
}
},
{
"dueDate": {
"$nin": [
null,
null
]
}
},
{
"proposedPaymentDate": {
"$nin": [
null,
null
]
}
},
{
"amount": {
"$gt": 0
}
}
],
"pInvoiceId": {
"$exists": false
}
}
},
{
"$sort": {
"pId": 1,
"amount": -1
}
},
{
"$lookup": {
"from": "pcollection",
"localField": "pId",
"foreignField": "lId",
"as": "p"
}
},
{
"$unwind": {
"path": "$p"
}
},
{
"$match": {
"p.aId": {
"$in": [
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
},
{
"$oid": "xxxxxxxxxxxxxxxxxxxxxxxx"
}
]
}
}
},
{
"$skip": 400
},
{
"$limit": 400
}
],
答案1
得分: 2
您的条件并不太有意义，您可以尝试以下条件：
[
{
"$match": {
"status": "STATUS",
"amount": { "$gt": 0 },
"pInvoiceId": { "$exists": false }
}
},
{ "$sort": { "pId": 1, "amount": -1 } },
{
"$lookup": {
"from": "pcollection",
"localField": "pId",
"foreignField": "lId",
"pipeline": [
{
"$match": {
"p.aId": {
"$in": [
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" }
]
}
}
}
],
"as": "p"
}
},
{ "$unwind": { "path": "$p" } },
{ "$skip": 400 },
{ "$limit": 400 }
]
然后最佳索引应该是 { status: 1, pInvoiceId: 1, amount: -1, pId: 1 }。
英文:
Your condition does not make much sense, you can try this one:
[
{
"$match": {
"status": "STATUS",
"amount": { "$gt": 0 },
"pInvoiceId": { "$exists": false }
}
},
{ "$sort": { "pId": 1, "amount": -1 } },
{
"$lookup": {
"from": "pcollection",
"localField": "pId",
"foreignField": "lId",
pipeline: [
{
"$match": {
"p.aId": {
"$in": [
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" },
{ "$oid": "xxxxxxxxxxxxxxxxxxxxxxxx" }
]
}
}
}
],
"as": "p"
}
},
{ "$unwind": { "path": "$p" } },
{ "$skip": 400 },
{ "$limit": 400 }
]
Then the best index should be { status: 1, pInvoiceId: 1, amount: -1, pId: 1 }
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论