MongoDB涵盖的查询无法满足来自索引的查询 [英] MongoDB covered query is not able to satisfy query from Index
问题描述
这是我的环境详细信息: 我从此处获取了示例MongoDB集合或数据库.它创建了一个餐厅集合.一个文档如下所示:
Here is the my environment details: I got the sample MongoDB collection or database from here. It creates a restaurant collection. One document looks like following:
{
"_id" : ObjectId("5a6292fd6b741ed385c94524"),
"address" : {
"building" : "97-22",
"coord" : [
-73.8601152,
40.7311739
],
"street" : "63 Road",
"zipcode" : "11374"
},
"borough" : "Queens",
"cuisine" : "Jewish/Kosher",
"grades" : [
{
"date" : ISODate("2014-11-24T00:00:00.000Z"),
"grade" : "Z",
"score" : 20
},
{
"date" : ISODate("2013-01-17T00:00:00.000Z"),
"grade" : "A",
"score" : 13
},
{
"date" : ISODate("2012-08-02T00:00:00.000Z"),
"grade" : "A",
"score" : 13
},
{
"date" : ISODate("2011-12-15T00:00:00.000Z"),
"grade" : "B",
"score" : 25
}
],
"name" : "Tov Kosher Kitchen",
"restaurant_id" : "40356068"
}
我创建了两个索引，例如:
I created two indexes like:
db.restaurants.createIndex({"restaurant_id" : 1}, {"name" : "restaurantsid"})
db.restaurants.createIndex({"address.zipcode" : 1}, {"name" : "zipcode"})
索引如下:
> db.restaurants.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.restaurants"
},
{
"v" : 2,
"key" : {
"address.zipcode" : 1
},
"name" : "zipcode",
"ns" : "test.restaurants"
},
{
"v" : 2,
"key" : {
"restaurant_id" : 1
},
"name" : "restaurantsid",
"ns" : "test.restaurants"
}
]
我想在这里实现的是，在以下查询中，我需要restaurant_id的整个列表，或者只需要zipCodes.我知道有一个索引可以保存在它的内存中.我写了这样的查询:
What I want to achieve here is that I need whole list of restaurant_id's or just zipCodes in the following query. I know there is a index living in memory for it. I wrote a query like this:
db.restaurants.find({}, {"address.zipcode" : 1, "_id" : 0}).explain()
结果是:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.restaurants",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"address.zipcode" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "714fc0d524cf",
"port" : 27017,
"version" : "3.6.0",
"gitVersion" : "a57d8e71e6998a2d0afde7edc11bd23e5661c915"
},
"ok" : 1
}
它总是给我舞台":"COLLSCAN".
It always gives me "stage" : "COLLSCAN".
因此，现在让我们获取所有对象ID. MongoDB在ObjectId上创建一个默认索引.
So now lets just get all the object id's. MongoDB creates one default index on ObjectId's.
db.restaurants.find({}, {"_id" : 1}).explain()
结果是:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.restaurants",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 1
},
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "714fc0d524cf",
"port" : 27017,
"version" : "3.6.0",
"gitVersion" : "a57d8e71e6998a2d0afde7edc11bd23e5661c915"
},
"ok" : 1
}
奇怪的是，MongoDB没有命中索引，但是我需要的所有数据都保存在索引中.为什么获奖计划总是"COLLSCAN"?
Its weird that MongoDB does not hit the index however all the data I need, lives in index. Why the winning plan is "COLLSCAN" always?
restaurantid索引也会发生同样的情况.也许我想念一些东西.
在编写查询时，我还照顾了"_id" : 0.
Same happens with restaurantsid index. Maybe I am missing something.
I also took care of "_id" : 0 while writing query.
我必须每次都使用hint()来使用这样的索引:
I have to use hint() everytime to make it use of index like this:
> db.restaurants.explain("executionStats").find({}, {"address.zipcode" : 1, "_id" : 0}).hint("zipcode")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.restaurants",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"address.zipcode" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"address.zipcode" : 1
},
"indexName" : "zipcode",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.zipcode" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.zipcode" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 25359,
"executionTimeMillis" : 79,
"totalKeysExamined" : 25359,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "PROJECTION",
"nReturned" : 25359,
"executionTimeMillisEstimate" : 63,
"works" : 25360,
"advanced" : 25359,
"needTime" : 0,
"needYield" : 0,
"saveState" : 199,
"restoreState" : 199,
"isEOF" : 1,
"invalidates" : 0,
"transformBy" : {
"address.zipcode" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 25359,
"executionTimeMillisEstimate" : 43,
"works" : 25360,
"advanced" : 25359,
"needTime" : 0,
"needYield" : 0,
"saveState" : 199,
"restoreState" : 199,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"address.zipcode" : 1
},
"indexName" : "zipcode",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.zipcode" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.zipcode" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 25359,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "714fc0d524cf",
"port" : 27017,
"version" : "3.6.0",
"gitVersion" : "a57d8e71e6998a2d0afde7edc11bd23e5661c915"
},
"ok" : 1
}
在命令行中使用MongoDB提示很好，但是当有应用程序运行并且它们无法使用其代码中的hint()时，这样做很不好.为什么MongoDB如果不使用hint()便无法单独使用正确的索引?
Using MongoDB hint at command line is fine but its bad when there are applications running and they can't use hint() from their code. Why MongoDB is not able to use right Index on its own without the use of hint()?
推荐答案
好吧，在3.6中针对您的特定情况引入了一个新标记.默认情况下它是禁用的.
Alright there is a new flag introduced in 3.6 for the specific case you've. It is disabled by default.
如果您想使用索引，请设置此标志
Set this flag if you like to use index
mongod --setParameter internalQueryPlannerGenerateCoveredWholeIndexScans=1
More info here & here
这篇关于MongoDB涵盖的查询无法满足来自索引的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
