Mongodb多键索引之嵌套文档
接上上一篇文档 Mongodb多键索引之数组,本次继续多键索引,到目前为止还没有分析业务中具体使用方式以及需求,只知道需要使用多键索引来满足不同查询,通过一个多键索引来解决不同谓词过滤,具体能否实现以及到底使用数组、嵌套文档还是数组文档方式？目前都是未知数,所以通过学习官方资料以及实际验证来解决如上2个问题.【能不能以及怎么做问题】，同时引出关键问题性能问题,选择一个能做方式满足业务需求,到底性能如何？
【嵌套文档如何使用高效索引查询】
1、集合中随机一条文档信息
关注:item嵌套文档,包括2组key:value的字典格式,name&manufactured
备注:所有数据格式都是一致，查询随机一条用于显示;findOne类似关系型
数据库Oracle rownum<=1 or mysql limit 1
xiaoxu:PRIMARY> db.survey.findOne();
{
"_id" : ObjectId("5f2ff576eb7de181ebe814f9"),
"item" : {
"name" : "Katie",
"manufactured" : 16
},
"ratings" : [
16,
116
],
"user_id" : 16,
"boolean" : true,
"added_at" : ISODate("2020-08-09T13:09:10.791Z"),
"number" : 662
}
xiaoxu:PRIMARY>2、通过item来完全匹配嵌套文档
【没有索引查询1条doc时间为453ms】
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie","manufactured" : 16}}).count();
1
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie","manufactured" : 16}}).pretty();
{
"_id" : ObjectId("5f2ff576eb7de181ebe814f9"),
"item" : {
"name" : "Katie",
"manufactured" : 16
},
"ratings" : [
16,
116
],
"user_id" : 16,
"boolean" : true,
"added_at" : ISODate("2020-08-09T13:09:10.791Z"),
"number" : 662
}
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie","manufactured" : 16}}).explain("executionStats").executionStats;
{
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 465,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1000019,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"item" : {
"$eq" : {
"name" : "Katie",
"manufactured" : 16
}
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 430,
"works" : 1000021,
"advanced" : 1,
"needTime" : 1000019,
"needYield" : 0,
"saveState" : 7812,
"restoreState" : 7812,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1000019
}
}
【执行时间453ms】
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie","manufactured" : 16}}).explain("executionStats").executionStats.executionTimeMillis;
4533、如何创建索引来完全匹配嵌套文档
- item作为一个整体创建索引,类似数组创建索引，此时不是多键索引
整体匹配语句区别:
1、db.survey.find({item:{name:"Katie","manufactured" : 16}})--走item索引
2、db.survey.find({"item.name":"Katie","item.manufactured" : 16}})--不走索引
备注:2个语句都是查询name=Katie and manufactured=16.如果语句2走索引,需要item.name:1,item.manufactured:1这种匹配方式创建索引，而不是item整体创建索引.从这里发现想创建item作为索引来满足各种匹配查询,例如单列查询,设想计划泡汤
【创建item嵌套文档索引-作为整体，此时时间为0】
备注:从执行中发现:"isMultiKey" : false,说明不是多键索引
xiaoxu:PRIMARY> db.survey.createIndex({item:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 7,
"numIndexesAfter" : 8,
"ok" : 1
}
xiaoxu:PRIMARY>
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie","manufactured" : 16}}).explain("executionStats").executionStats;
{
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"item" : 1
},
"indexName" : "item_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"item" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"item" : [
"[{ name: \"Katie\", manufactured: 16.0 }, { name: \"Katie\", manufactured: 16.0 }]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie","manufactured" : 16}}).explain("executionStats").executionStats.executionTimeMillis;
0
【第二种格式无法使用索引】
xiaoxu:PRIMARY> db.survey.find({"item.name":"Katie","item.manufactured" : 16}).explain("executionStats").executionStats;
{
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 451,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1000019,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"item" : {
"$eq" : {
"name" : "Katie",
"manufactured" : 16
}
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 390,
"works" : 1000021,
"advanced" : 1,
"needTime" : 1000019,
"needYield" : 0,
"saveState" : 7812,
"restoreState" : 7812,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1000019
}
}
【创建与查询匹配索引】
db.survey.createIndex({"item.name":1,"item:manufactured":1})
xiaoxu:PRIMARY> db.survey.find({"item.name":"Katie","item.manufactured" : 16}).explain("executionStats").executionStats;
{
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 405,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1000019,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"item.manufactured" : {
"$eq" : 16
}
},
{
"item.name" : {
"$eq" : "Katie"
}
}
]
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 330,
"works" : 1000021,
"advanced" : 1,
"needTime" : 1000019,
"needYield" : 0,
"saveState" : 7812,
"restoreState" : 7812,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1000019
}
}
xiaoxu:PRIMARY> db.survey.find({"item.name":"Katie","item.manufactured" : 16}).pretty();
{
"_id" : ObjectId("5f2ff576eb7de181ebe814f9"),
"item" : {
"name" : "Katie",
"manufactured" : 16
},
"ratings" : [
16,
116
],
"user_id" : 16,
"boolean" : true,
"added_at" : ISODate("2020-08-09T13:09:10.791Z"),
"number" : 662
}
xiaoxu:PRIMARY> db.survey.find({"item.name":"Katie","item.manufactured" : 16}).count();
1
4、如何创建索引来匹配嵌套文档部分值
匹配单个字段区别:
1、db.survey.find({item:{name:"Katie"}})
2、db.survey.find({"item.name":"Katie"}})
第一个逻辑是匹配1个完整嵌套文档只包括name=“Katie“，只有1组key:value字典.
第二个逻辑是匹配item这个嵌套文档值name等于Katie，是否有其他key:value则不关心.
这个逻辑有类似数组:item:[3]与 item:3意义.
1、第一SQL查询没有结果且用到item:1这个索引
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie"}}).count();
0
xiaoxu:PRIMARY> db.survey.find({item:{name:"Katie"}}).explain("executionStats").executionStats;
{
"executionSuccess" : true,
"nReturned" : 0,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 0,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"item" : 1
},
"indexName" : "item_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"item" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"item" : [
"[{ name: \"Katie\" }, { name: \"Katie\" }]"
]
},
"keysExamined" : 0,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
xiaoxu:PRIMARY>2、第2SQL查询返回8万多激励且没有用到item:1这个索引
备注:只有创建createIndex({"item.name":1})索引或者
createIndex({"item.name" : 1,"item.manufactured":1})索引.
createIndex({"item.name" : 1,"item.manufactured":1})这个是多键索引，
其他的不是.数组索引是多键索引，嵌套文档索引不是。
xiaoxu:PRIMARY> db.survey.find({"item.name":"Katie"}).count();
83106
xiaoxu:PRIMARY> db.survey.find({"item.name":"Katie"}).explain("executionStats").executionStats;
{
"executionSuccess" : true,
"nReturned" : 83106,
"executionTimeMillis" : 403,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1000019,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"item.name" : {
"$eq" : "Katie"
}
},
"nReturned" : 83106,
"executionTimeMillisEstimate" : 300,
"works" : 1000021,
"advanced" : 83106,
"needTime" : 916914,
"needYield" : 0,
"saveState" : 7812,
"restoreState" : 7812,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1000019
}
}
xiaoxu:PRIMARY> db.survey.createIndex({"item.name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 5,
"numIndexesAfter" : 6,
"ok" : 1
}
xiaoxu:PRIMARY> db.survey.find({"item.name":"Katie"}).explain("executionStats").executionStats;
{
"executionSuccess" : true,
"nReturned" : 83106,
"executionTimeMillis" : 86,
"totalKeysExamined" : 83106,
"totalDocsExamined" : 83106,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 83106,
"executionTimeMillisEstimate" : 70,
"works" : 83107,
"advanced" : 83106,
"needTime" : 0,
"needYield" : 0,
"saveState" : 649,
"restoreState" : 649,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 83106,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 83106,
"executionTimeMillisEstimate" : 30,
"works" : 83107,
"advanced" : 83106,
"needTime" : 0,
"needYield" : 0,
"saveState" : 649,
"restoreState" : 649,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"item.name" : 1
},
"indexName" : "item.name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"item.name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"item.name" : [
"[\"Katie\", \"Katie\"]"
]
},
"keysExamined" : 83106,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
xiaoxu:PRIMARY>【总结】
嵌套文档与数组对比:
- 数组索引是多键索引,嵌套文档索引不是,除非拆分多个创建组合索引则是
- 完整匹配整个数组与嵌套文档写法类似，数组支持元素顺序不一致匹配 嵌套文档不支持
- 数组支持点索引顺序创建索引,例如item.0:1,嵌套文档也支持item.name
一个基于位置创建索引，一个基于名称
文章开头提到,项目中一个字段中包括多个类型或者状态，创建一个索引来实现，
貌似数组可能更满足需求,嵌套文档需要创建对每个嵌套字段创建索引，违背创建
一个索引初衷，也可能设计本身就不合理 。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-08-18，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
腾讯云开发者
