MongoDB查询不对带有文本字段的复合索引使用前缀 [英] Mongodb query does not use prefix on compound index with text field
问题描述
我在收藏夹上创建了以下索引:
I've created the following index on my collection:
db.myCollection.createIndex({
user_id: 1,
name: 'text'
})
如果我尝试查看包含两个字段的查询的执行计划,如下所示:
If I try to see the execution plan of a query containing both fields, like this:
db.getCollection('campaigns').find({
user_id: ObjectId('xxx')
,$text: { $search: 'bla' }
}).explain('executionStats')
我得到以下结果:
...
"winningPlan" : {
"stage" : "TEXT",
"indexPrefix" : {
"user_id" : ObjectId("xxx")
},
"indexName" : "user_id_1_name_text",
"parsedTextQuery" : {
"terms" : [
"e"
],
"negatedTerms" : [],
"phrases" : [],
"negatedPhrases" : []
},
"inputStage" : {
"stage" : "TEXT_MATCH",
"inputStage" : {
"stage" : "TEXT_OR",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user_id" : 1.0,
"_fts" : "text",
"_ftsx" : 1
},
"indexName" : "user_id_1_name_text",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {}
}
}
}
}
...
如文档所述, MongoDB可以使用索引前缀来执行索引查询.
As stated in the documentation, MongoDB can use index prefixes to perform indexed queries.
由于user_id
是上述索引的前缀,所以我希望仅通过user_id
进行的查询会使用该索引,但是如果我尝试以下操作:
Since user_id
is a prefix for the index above, I'd expect that a query only by user_id
would use the index, but if I try the following:
db.myCollection.find({
user_id: ObjectId('xxx')
}).explain('executionStats')
我得到:
...
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"user_id" : {
"$eq" : ObjectId("xxx")
}
},
"direction" : "forward"
},
...
因此,它根本不使用索引并执行完整的集合扫描.
So, it is not using the index at all and performing a full collection scan.
推荐答案
通常,MongoDB可以使用索引前缀来支持查询,但是包括地理空间或文本字段的复合索引是
In general MongoDB can use index prefixes to support queries, however compound indexes including geospatial or text fields are a special case of sparse compound indexes. If a document does not include a value for any of the text index field(s) in a compound index, it will not be included in the index.
为了确保正确的结果对于前缀搜索,将在稀疏复合索引上选择替代查询计划:
In order to ensure correct results for a prefix search, an alternative query plan will be chosen over the sparse compound index:
如果索引稀疏会导致查询和排序操作的结果集不完整,则MongoDB将不会使用该索引,除非hint()明确指定索引.
If a sparse index would result in an incomplete result set for queries and sort operations, MongoDB will not use that index unless a hint() explicitly specifies the index.
在MongoDB 3.4.5中设置一些测试数据以演示潜在问题:
Setting up some test data in MongoDB 3.4.5 to demonstrate the potential problem:
db.myCollection.createIndex({ user_id:1, name: 'text' }, { name: 'myIndex'})
// `name` is a string; this document will be included in a text index
db.myCollection.insert({ user_id:123, name:'Banana' })
// `name` is a number; this document will NOT be included in a text index
db.myCollection.insert({ user_id:123, name: 456 })
// `name` is missing; this document will NOT be included in a text index
db.myCollection.insert({ user_id:123 })
然后,强制使用复合文本索引:
Then, forcing the compound text index to be used:
db.myCollection.find({user_id:123}).hint('myIndex')
结果仅包含带有索引文本字段name
的单个文档,而不是预期的三个文档:
The result only includes the single document with the indexed text field name
, rather than the three documents that would be expected:
{
"_id": ObjectId("595ab19e799060aee88cb035"),
"user_id": 123,
"name": "Banana"
}
该异常应在MongoDB文档中更清楚地突出显示;在MongoDB问题跟踪器中监视/更新 DOCS-10322 .
This exception should be more clearly highlighted in the MongoDB documentation; watch/upvote DOCS-10322 in the MongoDB issue tracker for updates.
这篇关于MongoDB查询不对带有文本字段的复合索引使用前缀的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!