慢的MongoDB查询 [英] Slow MongoDB query
问题描述
我是MongoDB的新手，试图了解为什么我的查询这么慢(每个查询30-150秒！).我的数据库包含大约6000万个文档.在查询中，我需要将参数搜索和全文搜索结合起来.这是我要分析的查询:
I am new to MongoDB and trying to understand why are my queries so slow (30-150 seconds per query!). My database contains about 60 million documents. In my queries, I need to combine parametric and fulltext search. This is the query I am trying to analyze:
db.collection.explain("executionStats").find(
{"property.multi.value_title": "Pearson Education (US)",
"pricing.price" : {$gte: 70, $lte: 600},
$text: { $search: "app" }
}).count()
这是解释结果:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.collection",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"property.multi.value_title" : {
"$eq" : "Pearson Education (US)"
}
},
{
"pricing.price" : {
"$lte" : 600
}
},
{
"pricing.price" : {
"$gte" : 70
}
},
{
"$text" : {
"$search" : "app",
"$language" : "none",
"$caseSensitive" : false,
"$diacriticSensitive" : false
}
}
]
},
"winningPlan" : {
"stage" : "COUNT",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"pricing.price" : {
"$lte" : 600
}
},
{
"pricing.price" : {
"$gte" : 70
}
},
{
"property.multi.value_title" : {
"$eq" : "Pearson Education (US)"
}
}
]
},
"inputStage" : {
"stage" : "TEXT",
"indexPrefix" : {
},
"indexName" : "translate.cs.content_text",
"parsedTextQuery" : {
"terms" : [
"app"
],
"negatedTerms" : [ ],
"phrases" : [ ],
"negatedPhrases" : [ ]
},
"textIndexVersion" : 3,
"inputStage" : {
"stage" : "TEXT_MATCH",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "OR",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_fts" : "text",
"_ftsx" : 1
},
"indexName" : "translate.cs.content_text",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
}
}
}
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 0,
"executionTimeMillis" : 29794,
"totalKeysExamined" : 7996,
"totalDocsExamined" : 15992,
"executionStages" : {
"stage" : "COUNT",
"nReturned" : 0,
"executionTimeMillisEstimate" : 27400,
"works" : 7997,
"advanced" : 0,
"needTime" : 7996,
"needYield" : 0,
"saveState" : 1220,
"restoreState" : 1220,
"isEOF" : 1,
"nCounted" : 6,
"nSkipped" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"pricing.price" : {
"$lte" : 600
}
},
{
"pricing.price" : {
"$gte" : 70
}
},
{
"property.multi.value_title" : {
"$eq" : "Pearson Education (US)"
}
}
]
},
"nReturned" : 6,
"executionTimeMillisEstimate" : 27400,
"works" : 7997,
"advanced" : 6,
"needTime" : 7990,
"needYield" : 0,
"saveState" : 1220,
"restoreState" : 1220,
"isEOF" : 1,
"docsExamined" : 7996,
"alreadyHasObj" : 7996,
"inputStage" : {
"stage" : "TEXT",
"nReturned" : 7996,
"executionTimeMillisEstimate" : 27390,
"works" : 7997,
"advanced" : 7996,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1220,
"restoreState" : 1220,
"isEOF" : 1,
"indexPrefix" : {
},
"indexName" : "translate.cs.content_text",
"parsedTextQuery" : {
"terms" : [
"app"
],
"negatedTerms" : [ ],
"phrases" : [ ],
"negatedPhrases" : [ ]
},
"textIndexVersion" : 3,
"inputStage" : {
"stage" : "TEXT_MATCH",
"nReturned" : 7996,
"executionTimeMillisEstimate" : 27390,
"works" : 7997,
"advanced" : 7996,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1220,
"restoreState" : 1220,
"isEOF" : 1,
"docsRejected" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 7996,
//HERE
"executionTimeMillisEstimate" : 27380,
"works" : 7997,
"advanced" : 7996,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1220,
"restoreState" : 1220,
"isEOF" : 1,
"docsExamined" : 7996,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "OR",
"nReturned" : 7996,
"executionTimeMillisEstimate" : 175,
"works" : 7997,
"advanced" : 7996,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1220,
"restoreState" : 1220,
"isEOF" : 1,
"dupsTested" : 7996,
"dupsDropped" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 7996,
"executionTimeMillisEstimate" : 165,
"works" : 7997,
"advanced" : 7996,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1220,
"restoreState" : 1220,
"isEOF" : 1,
"keyPattern" : {
"_fts" : "text",
"_ftsx" : 1
},
"indexName" : "translate.cs.content_text",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
},
"keysExamined" : 7996,
"seeks" : 1,
"dupsTested" : 7996,
"dupsDropped" : 0
}
}
}
}
}
}
}
},
"serverInfo" : {
"host" : "ubuntu2004",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
我无法在文本索引中指定我的语言，因为我的MongoDB不支持该语言.
I couldn't specify my language in the text index, because it is not supported my MongoDB.
我正在查看executionStats部分的时间估计，我注意到在执行 IXSCAN 和 OR 操作之后，每个操作大约花费了170ms，时间突然跳了起来.到"executionTimeMillisEstimate":27380 ，位于 FETCH 部分.
I was looking at the time estimates at the executionStats part, and I noticed that after the IXSCAN and OR operations, that took about 170ms each, the time suddenly jumped to "executionTimeMillisEstimate" : 27380 at the FETCH part.
有人可以解释一下这是什么意思，以及如何提高此查询的性能吗?
Can someone please explain what does it mean and how can I improve the performance of this query?
编辑:这些是我的收藏夹中的索引:
these are indexes on my collection:
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"_fts" : "text",
"_ftsx" : 1
},
"name" : "translate.cs.content_text",
"default_language" : "none",
"language_override" : "none",
"weights" : {
"translate.cs.content" : 1
},
"textIndexVersion" : 3
},
{
"v" : 2,
"key" : {
"property" : 1
},
"name" : "property_1"
},
{
"v" : 2,
"key" : {
"property.multi.value_title" : 1
},
"name" : "property.multi.value_title_1"
}
推荐答案
我的猜测是您的磁盘速度很慢或没有足够的内存来处理此工作负载.
My guess is you either have very slow disk or not enough memory for this workload.
- 如果您使用磁盘，请切换到SSD.
- 如果您使用的是网络附加存储(例如，所有Amazon EBS)，请提高网络速度(例如，更改为高I/O"实例类型).
- 为数据库提供更多的内存.
在 top 中查看I/O等待时间，以验证工作负载是否受I/O约束.
Look at I/O wait time in top to verify the workload is I/O bound.
这篇关于慢的MongoDB查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
