带有过滤器的 Mongo 日期范围索引 [英] Mongo date range index with filters
问题描述
我们有以下查询
db.Comment.find(
{
$and: [
{ reportCount: { $gt: 0 } },
{ assignee: { $exists: false } },
{ creationDate: { $gt: new Date(1507831097809) } },
{ creationDate: { $lt: new Date(1508522297966) } },
{ siteId: 'MAIN' },
{ parent: { $exists: false } },
{ status: 'ACTIVE' }
]
})
.sort({ creationDate: 1 })
我们有一个索引
{
"v" : 2,
"key" : {
"creationDate" : 1,
"reportCount" : 1,
"label" : 1
}
}
这是explain结果:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "myNameSpace",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"creationDate" : 1.0,
"reportCount" : 1.0,
"label" : 1.0
},
"indexName" : "creationDate_1_reportCount_1_label_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"creationDate" : [],
"reportCount" : [],
"label" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"creationDate" : [
"(new Date(1507831097809), new Date(1508522297966))"
],
"reportCount" : [
"(0.0, inf.0]"
],
"label" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
},
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"parent" : 1.0
},
"indexName" : "parent_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"parent" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"parent" : [
"[null, null]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"assignee" : 1.0
},
"indexName" : "assignee_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"assignee" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"assignee" : [
"[null, null]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"siteId" : 1.0,
"updatedDate" : 1.0,
"label" : 1.0
},
"indexName" : "siteId_1_updatedDate_1_label_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"siteId" : [],
"updatedDate" : [],
"label" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"siteId" : [
"[\"MAIN\", \"MAIN\"]"
],
"updatedDate" : [
"[MinKey, MaxKey]"
],
"label" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"parent" : 1.0
},
"indexName" : "parent_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"parent" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"parent" : [
"[null, null]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"assignee" : 1.0
},
"indexName" : "assignee_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"assignee" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"assignee" : [
"[null, null]"
]
}
}
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 19,
"executionTimeMillis" : 8,
"totalKeysExamined" : 533,
"totalDocsExamined" : 56,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 19,
"executionTimeMillisEstimate" : 0,
"works" : 534,
"advanced" : 19,
"needTime" : 513,
"needYield" : 0,
"saveState" : 20,
"restoreState" : 20,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 56,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 56,
"executionTimeMillisEstimate" : 0,
"works" : 533,
"advanced" : 56,
"needTime" : 476,
"needYield" : 0,
"saveState" : 20,
"restoreState" : 20,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"creationDate" : 1.0,
"reportCount" : 1.0,
"label" : 1.0
},
"indexName" : "creationDate_1_reportCount_1_label_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"creationDate" : [],
"reportCount" : [],
"label" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"creationDate" : [
"(new Date(1507831097809), new Date(1508522297966))"
],
"reportCount" : [
"(0.0, inf.0]"
],
"label" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 533,
"seeks" : 477,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"ok" : 1.0
}
查询仍然需要 700-800 毫秒才能返回数据.如何更改索引以使查询运行得更快?不要考虑 "keysExamined" : 533, "seeks" : 477, 这个数据.这只是测试数据.
The query is still taking 700-800 ms to return the data. How can I change the index to make the query run faster? Don't consider "keysExamined" : 533, "seeks" : 477, This data. This is just test data.
看起来它使用了索引但只有索引中的第一个字段?还有 multuKey 是假的吗?
Looks like its using an index but only the first field in the index? Also multuKey is false?
推荐答案
解释计划输出的几个关键点:
A few key points from the explain plan output:
- 查询处理以下属性:
siteId、status、creationDate、reportCount、assignee、parent - 获胜计划分为两个阶段:
- IX_SCAN 使用
creationDate_1_reportCount_1_label_1，它使用对creationDate和reportCount的索引查找来识别 56 个文档，然后将这些文档转发到 FETCH 阶段 - FETCH 从 IX_SCAN 阶段接收 56 个文档，然后询问这些文档以应用
siteId、status、assignee和parent过滤器.这次审讯导致37份文件被丢弃，导致19份文件被退回.
- The query addresses the following attributes:
siteId, status, creationDate, reportCount, assignee, parent - The winning plan has two stages:
- IX_SCAN uses
creationDate_1_reportCount_1_label_1, this uses indexed lookups oncreationDateandreportCountto identify 56 documents which are then forwarded to the FETCH stage - FETCH receives 56 documents from the IX_SCAN stage and then interrogates these documents to apply the
siteId,status,assigneeandparentfilters. This interrogation causes 37 documents to be discarded resulting in 19 document to be returned.
因此，您的索引仅涵盖查询中 6 个属性中的 2 个，而查询中的其余 4 个属性是通过检查文档而不是索引来应用的.如果您希望此查询完全被索引覆盖，则创建以下索引:
So, your index covers just 2 of the 6 attributes in your query and the remaining 4 attributes in your query are applied by examining the documents not the index. If you want this query to be fully index covered then create the following index:
db.collection.createIndex( {siteId: 1, status: 1, creationDate: 1, reportCount: 1, assignee: 1, parent: 1} )如果您使用该索引重新运行，那么您应该会发现 (a) MongoDB 选择了该索引，并且 (b) IX_SCAN 阶段转发的文档数量与您的 find 调用返回的文档数量相同.
If you re run with this index in place then you should find that (a) MongoDB chooses this index and (b) the number of documents forwarded by the IX_SCAN stage is the same as the number of documents returned by your find call.
我说应该找到"是因为这里还有其他方面可能会导致 MongoDB 选择不同的索引，例如使用
$nor和排序阶段 (creationDate: 1).我建议调整索引并在每次调整后使用解释on"运行，并在executionStats子文档中查找这些关键项目:I say "should find" because there are other aspects here which might result in MongoDB choosing a different index e.g. use of
$norand the sort stage (creationDate: 1). I would recommend tweaking the index and running with explain 'on' after each tweak and looking for these key items in theexecutionStatssub document:- "n 返回"
- "totalKeysExamined"
- "totalDocsExamined"
一个简单的经验法则是:
totalKeysExamined越接近nReturned，totalDocsExamined越接近于零......越好您的索引覆盖范围.A simple rule of thumb is this: the closer
totalKeysExaminedis tonReturnedand the closertotalDocsExaminedis to zero ... the better your index coverage.还有一个索引成本的问题(在对写入时间和索引存储的影响方面)，所以我建议考虑您的非功能性需求 - 如果没有完整的索引覆盖，是否可以实现您想要的经过时间?如果没有，那么您应该继续进行经验测试，但准备根据
explain()输出告诉您的内容调整您的选择.There is also the question of the cost of an index (in terms of impact on write times and index storage) so I'd suggest considering your non functional requirements - can your desired elapsed times be achieved without full index coverage? If not, then you should proceed with empirical testing but be prepared to tweak your choice in reponse to what the
explain()output tells you.这篇关于带有过滤器的 Mongo 日期范围索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
- IX_SCAN uses
- IX_SCAN 使用
