database - Mongodb不使用对象数组的索引
问题描述
我试图通过对嵌套对象数组进行查找查询来获取集合中的少量文档。嵌套对象已编入索引,但查找查询未使用索引来获取文档。
这是文档的结构。
"_id" : ObjectId("5bc6498c1ec4062983c4f4ef"),
"appId" : ObjectId("5bbc775036021bea06d9bbc2"),
"status" : "active",
"segmentations" : [
{
"name" : "ch-1",
"values" : [
'true'
],
"type" : "string"
},
{
"name" : "browerInfo",
"values" : [
"Firefox"
],
"version" : [
"62.0"
],
"majorVersion" : [
"62"
],
"type" : "string"
},
{
"name" : "OS",
"values" : [
"Ubuntu"
],
"type" : "string"
},
{
"name" : "lastVisitTime",
"values" : [
1539721615231.0
],
"type" : "number"
}
]
}
这是索引字段。
{
"v" : 2,
"key" : {
"appId" : 1,
"status" : 1,
"segmentations.name" : 1,
"segmentations.values" : 1
},
"name" : "SEGMENT_INDEX",
"ns" : "test.Collname"
}
下面是我正在执行的查找查询
db.Collname.find({
appId: ObjectId("5c6a8ef544ff62c73bdb98fc"),
"segmentations.name": 'ch-1',
'segmentations.values': 'true',
status: 'active'
}, {})
我尝试使用获取查询执行信息
<above query>.explain("executionStats")
结果是
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.Collname",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"appId" : {
"$eq" : ObjectId("5c6a8ef544ff62c73bdb98fc")
}
},
{
"segmentations.name" : {
"$eq" : "ch-1"
}
},
{
"segmentations.values" : {
"$eq" : "true"
}
},
{
"status" : {
"$eq" : "active"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"segmentations.values" : {
"$eq" : "true"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"appId" : 1.0,
"status" : 1.0,
"segmentations.name" : 1.0,
"segmentations.values" : 1.0
},
"indexName" : "SEGMENT_INDEX",
"isMultiKey" : true,
"multiKeyPaths" : {
"appId" : [],
"status" : [],
"segmentations.name" : [
"segmentations"
],
"segmentations.values" : [
"segmentations",
"segmentations.values"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"appId" : [
"[ObjectId('5c6a8ef544ff62c73bdb98fc'), ObjectId('5c6a8ef544ff62c73bdb98fc')]"
],
"status" : [
"[\"active\", \"active\"]"
],
"segmentations.name" : [
"[\"ch-1\", \"ch-1\"]"
],
"segmentations.values" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : []
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 28176,
"executionTimeMillis" : 72,
"totalKeysExamined" : 28176,
"totalDocsExamined" : 28176,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"segmentations.values" : {
"$eq" : "true"
}
},
"nReturned" : 28176,
"executionTimeMillisEstimate" : 70,
"works" : 28177,
"advanced" : 28176,
"needTime" : 0,
"needYield" : 0,
"saveState" : 220,
"restoreState" : 220,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 28176,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 28176,
"executionTimeMillisEstimate" : 10,
"works" : 28177,
"advanced" : 28176,
"needTime" : 0,
"needYield" : 0,
"saveState" : 220,
"restoreState" : 220,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"appId" : 1.0,
"status" : 1.0,
"segmentations.name" : 1.0,
"segmentations.values" : 1.0
},
"indexName" : "SEGMENT_INDEX",
"isMultiKey" : true,
"multiKeyPaths" : {
"appId" : [],
"status" : [],
"segmentations.name" : [
"segmentations"
],
"segmentations.values" : [
"segmentations",
"segmentations.values"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"appId" : [
"[ObjectId('5c6a8ef544ff62c73bdb98fc'), ObjectId('5c6a8ef544ff62c73bdb98fc')]"
],
"status" : [
"[\"active\", \"active\"]"
],
"segmentations.name" : [
"[\"ch-1\", \"ch-1\"]"
],
"segmentations.values" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 28176,
"seeks" : 1,
"dupsTested" : 28176,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "sys3029",
"port" : 27017,
"version" : "4.0.9",
"gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765"
},
"ok" : 1.0
}
我可以从 executionStats 看到“segmentations.values”字段未在“IXSCAN”阶段使用。“segmentations.values”上有一个额外的过滤阶段。IXSCAN 阶段只用了 10 毫秒,而 FILTER 阶段用了 50 毫秒。
我不明白为什么该字段不包含在 IXSCAN 阶段。我的收藏有大约 320 万份文档,由于这个问题,查询执行时间比预期的要长。
请帮我解决问题。先感谢您。
请建议我如果我需要更改我的数据库结构,如果在 mongodb 中不可能,您可以建议一些其他支持上述操作的数据库。
解决方案
以下查询将使用您的两个数组字段的索引:
.find({
appId: ObjectId("5c6a8ef544ff62c73bdb98fc"),
segmentations:{$elemMatch:{name: 'ch-1',values: 'true'}},
status: 'active'
}, {})
如果您不使用 $elemMatch,MongoDB 可以将数组项键的边界与“segmentations.name”的边界或“ segmentations.values”的边界复合,但不能同时使用两者。为了将“segmentations.name”的边界与“segmentations.values”的边界复合,查询必须使用$elemMatch。
将来自同一数组的索引键的边界组合在一起:
- 索引键必须共享相同的字段路径,但不包括字段名称,
- 并且查询必须在该路径上使用 $elemMatch 指定字段上的谓词。
我建议您阅读有关multikey-index-bounds和$elemMatch的 mongodb 文档。
推荐阅读
- nginx - 如何将实时视频源从 Raspberry Pi 流式传输到远程服务器并让服务器成为流的中心?
- python - How do I put a bunch of pictures into a video using python?
- typescript - How do I hide the Jest types so I can use Mocha?
- javascript - i am generating a dropdown menu using the DOM and filling the options with data received from an API
- r - 导入多个文件,然后将计算输出到一个文件中
- php - php 执行 npm 运行构建
- sql - 如何使用来自另一个表的查询更新表 Microsoft Access
- python - Python钢琴瓷砖机器人
- c - 程序按用户打印出基本详细信息。输出中不断出现错误
- javascript - Javascript selenium,等到页面完全加载