为什么MongoDB不使用Index Intersection？ [英] Why MongoDB doesn't use Index Intersection?
问题描述
我正在尝试重现索引交叉指令的第一个示例（ http：/ /docs.mongodb.org/manual/core/index-intersection/ ）但遇到问题：mongo不使用两个索引
I am trying to reproduce the first example of index intersection instruction (http://docs.mongodb.org/manual/core/index-intersection/) but facing a problem: mongo doesn't uses both indexes
我的步骤：
- 下载mongo（3.0.3）并安装它
- 运行mongod：mongod.exe - -dbpath d：\ data（文件夹为空）
- 运行mongo：mongo.exe
-
添加索引：
- Download mongo (3.0.3) and install it
- Run mongod: mongod.exe --dbpath d:\data (folder is empty)
- Run mongo: mongo.exe
Add indexes:
db.orders.ensureIndex({ qty: 1 })
db.orders.ensureIndex({ item: 1 })
db.orders.getIndexes()
[{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.orders"
},
{
"v" : 1,
"key" : {
"qty" : 1
},
"name" : "qty_1",
"ns" : "test.orders"
},
{
"v" : 1,
"key" : {
"item" : 1
},
"name" : "item_1",
"ns" : "test.orders"
}]
检查查询说明：
Check query explain:
db.orders.find( { item: "abc123", qty: { $gt: 15 } } ).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.orders",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"item" : {
"$eq" : "abc123"
}
},
{
"qty" : {
"$gt" : 15
}
}
]
},
"winningPlan" : {
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"qty" : {
"$gt" : 15
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"item" : 1
},
"indexName" : "item_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"item" : [
"[\"abc123\", \"abc123\"]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"item" : {
"$eq" : "abc123"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"qty" : 1
},
"indexName" : "qty_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"qty" : [
"(15.0, 1.#INF]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "localhost",
"port" : 27017,
"version" : "3.0.3",
"gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105"
},
"ok" : 1
}
正如您所见，winnerPlan仅包含item_1索引。存在包含qty_1索引的rejectedPlans。但是没有包含索引交集的计划。
我知道有很多条件可以选择特定的索引。但在我的情况下，mongo甚至没有计划它！
As you can see winningPlan contains only item_1 index. There is rejectedPlans which contains qty_1 index. But there is no plans which contains index intersection. I know that there are a lot of conditions to select specific index. But in my case mongo doesn't even plans it!
有人能帮帮我吗？
推荐答案
SERVER-3071 JIRA问题但我不能说所有内容仍然与3.0相关。无论如何：
There are some details about the index selection in the SERVER-3071 JIRA issue but I cannot say if all is still relevant for 3.0. Anyway:
MongoDB 3.0.2 似乎不考虑范围查询的索引交互。但它会用于点间隔：
MongoDB 3.0.2 seems not consider index interaction for range query. But it will for point intervals:
> db.orders.find( { item: {$eq : "abc123"}, qty: { $eq: 15 } } ).explain()
...
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"qty" : 1
},
"indexName" : "qty_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"qty" : [
"[15.0, 15.0]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"item" : 1
},
"indexName" : "item_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"item" : [
"[\"abc123\", \"abc123\"]"
]
}
}
]
}
这篇关于为什么MongoDB不使用Index Intersection？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
