前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >Mongodb多键索引之嵌套文档

Mongodb多键索引之嵌套文档

作者头像
徐靖
发布于 2020-08-25 10:49:35
2.9K0
发布于 2020-08-25 10:49:35
举报
文章被收录于专栏：DB说

接上上一篇文档 Mongodb多键索引之数组,本次继续多键索引,到目前为止还没有分析业务中具体使用方式以及需求,只知道需要使用多键索引来满足不同查询,通过一个多键索引来解决不同谓词过滤,具体能否实现以及到底使用数组、嵌套文档还是数组文档方式？目前都是未知数,所以通过学习官方资料以及实际验证来解决如上2个问题.【能不能以及怎么做问题】，同时引出关键问题性能问题,选择一个能做方式满足业务需求,到底性能如何？

【嵌套文档如何使用高效索引查询】

1、集合中随机一条文档信息

关注:item嵌套文档,包括2组key:value的字典格式,name&manufactured

代码语言：javascript
复制
备注:所有数据格式都是一致，查询随机一条用于显示;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来完全匹配嵌套文档

代码语言：javascript
复制
【没有索引查询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;
453

3、如何创建索引来完全匹配嵌套文档

  • 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作为索引来满足各种匹配查询,例如单列查询,设想计划泡汤

代码语言：javascript
复制
【创建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这个索引

代码语言：javascript
复制
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})这个是多键索引，

其他的不是.数组索引是多键索引，嵌套文档索引不是。

代码语言：javascript
复制
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 删除
编程算法
sql

本文分享自 udapp 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

编程算法
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论