Mongodb查询分析器
- 动态相关项目中涉及到数据量大和吞吐量的接口，例如关注页面动态，附近动态，这部分数据都是存储在mongodb中，在线上数据中分类两个mongodb集合存储
- 其中关注动态基于扩散写的设计，数据量已经快到 8 亿。
- 附近动态并没有写扩散的数据扩大，并且曝光量相比关注页面少一点，并且重要的是有TTL，因此附近动态相关的表数据50万以上
- 在大数据量查询下，优化查询速度是提高系统性能的一个关键点，在Mysql中我们有explan 诊断工具，来帮助我们来对sql进行分析，同样的Mongodb中获取查询相关信息的诊断工具之一就是explain() 函数，在《MongoDB权威指南》（我只看了索引这一章）书中解释：
explain会返回查询使用的索引情况(如果有的话)，耗时及扫描文档数的统计信息。.....使用explain就会得到当前查询所使用的索引，消耗了多少时间，已经数据库需要扫描多少文档才能得到结果。
- 但是书中使用的Mongodb应该是3.2 版本之前的，因为给出的索引分析案例是如下：
{
"cursor" : "BtreeCursor gender_1_user_name_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 0,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 0,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : true,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"gender" : [
[
"M",
"M"
]
],
"user_name" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
- 我们在自己数据库中得到完全不一样的输出：
//db.moment.explain(executionStats).find({"subscriberID" : NumberLong(1884831336), "type" : { "$in" : [1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19] } }).sort({"auditTime" : -1});
{
"queryPlanner": {
"mongosPlannerVersion": 1,
"winningPlan": {
"stage": "SINGLE_SHARD",
"shards": [{
"shardName": "cmgo-hgz5ixhj_0",
"connectionString": "cmgo-hgz5ixhj_0/100.120.151.75:7004,100.120.152.197:7000,100.93.47.81:7016",
"serverInfo": {
"host": "TENCENT64.site",
"port": 7016,
"version": "3.2.10-cmongo-",
"gitVersion": "nogitversion"
},
"plannerVersion": 1,
"namespace": "moment.moment",
"indexFilterSet": false,
"parsedQuery": {
"$and": [{
"subscriberID": {
"$eq": NumberLong(1884831336)
}
},
{
"type": {
"$in": [
1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]
}
}]
},
"winningPlan": {
"stage": "SORT",
"sortPattern": {
"auditTime": -1
},
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH",
"filter": {
"type": {
"$in": [
1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]
}
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"momentID": 1
},
"indexName": "idx_objectid_momentid",
"isMultiKey": false,
"isUnique": true,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "forward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"momentID": ["[MinKey, MaxKey]"]
}
}
}
}
},
"rejectedPlans": [{
"stage": "FETCH",
"inputStage": {
"stage": "SORT_MERGE",
"sortPattern": {
"auditTime": -1
},
"inputStages": [{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[19.0, 19.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[18.0, 18.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[17.0, 17.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[16.0, 16.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[15.0, 15.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[14.0, 14.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[13.0, 13.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[12.0, 12.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[11.0, 11.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[10.0, 10.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[8.0, 8.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[7.0, 7.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[6.0, 6.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[5.0, 5.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[4.0, 4.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[3.0, 3.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[2.0, 2.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"type": 1,
"auditTime": 1
},
"indexName": "subscriberID_1_type_1_auditTime_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "backward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"type": ["[1.0, 1.0]"],
"auditTime": ["[MaxKey, MinKey]"]
}
}]
}
},
{
"stage": "SORT",
"sortPattern": {
"auditTime": -1
},
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"subscriberID": 1,
"unread": 1,
"type": 1
},
"indexName": "subscriberID_1_unread_1_type_1",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "forward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"unread": ["[MinKey, MaxKey]"],
"type": ["[1.0, 1.0]", "[2.0, 2.0]", "[3.0, 3.0]", "[4.0, 4.0]", "[5.0, 5.0]", "[6.0, 6.0]", "[7.0, 7.0]", "[8.0, 8.0]", "[10.0, 10.0]", "[11.0, 11.0]", "[12.0, 12.0]", "[13.0, 13.0]", "[14.0, 14.0]", "[15.0, 15.0]", "[16.0, 16.0]", "[17.0, 17.0]", "[18.0, 18.0]", "[19.0, 19.0]"]
}
}
}
}
}]
}]
}
},
"executionStats": {
"nReturned": 71,
"executionTimeMillis": 4,
"totalKeysExamined": 71,
"totalDocsExamined": 71,
"executionStages": {
"stage": "SINGLE_SHARD",
"nReturned": 71,
"executionTimeMillis": 4,
"totalKeysExamined": 71,
"totalDocsExamined": 71,
"totalChildMillis": NumberLong(2),
"shards": [{
"shardName": "cmgo-hgz5ixhj_0",
"executionSuccess": true,
"executionStages": {
"stage": "SORT",
"nReturned": 71,
"executionTimeMillisEstimate": 0,
"works": 146,
"advanced": 71,
"needTime": 73,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"invalidates": 0,
"sortPattern": {
"auditTime": -1
},
"memUsage": 13682,
"memLimit": 33554432,
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"nReturned": 0,
"executionTimeMillisEstimate": 0,
"works": 73,
"advanced": 0,
"needTime": 1,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"invalidates": 0,
"inputStage": {
"stage": "FETCH",
"filter": {
"type": {
"$in": [
1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]
}
},
"nReturned": 71,
"executionTimeMillisEstimate": 0,
"works": 72,
"advanced": 71,
"needTime": 0,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"invalidates": 0,
"docsExamined": 71,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 71,
"executionTimeMillisEstimate": 0,
"works": 72,
"advanced": 71,
"needTime": 0,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"invalidates": 0,
"keyPattern": {
"subscriberID": 1,
"momentID": 1
},
"indexName": "idx_objectid_momentid",
"isMultiKey": false,
"isUnique": true,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "forward",
"indexBounds": {
"subscriberID": ["[1884831336, 1884831336]"],
"momentID": ["[MinKey, MaxKey]"]
},
"keysExamined": 71,
"dupsTested": 0,
"dupsDropped": 0,
"seenInvalidated": 0
}
}
}
}
}]
}
},
"ok": 1
}
-
我看到我们测试库的版本是：3.4.21，既然和书上不一样，去官网看看吧，反正书上也是官网的翻译，Mongodb中explain()函数的网址，中文的Mongodb中explain() 网址
-
如上官网中发现，在3.2版本后新支持了一部分函数的分析，并且在预发规则上也有一点变化
//之前
db.collection.<method(...)>.explain()
//现在
db.collection.explain().<method(...)>
-
并且在explain(）函数中还支持参数选择各种模式的分析方式，如下：
-
分为三种模式
- queryPlanner（默认模式）
- executionStats：返回关于查询过程中统计信息，包括使用的随意，扫描的文档数已经查查处理所用时间（毫秒计算）
- allPlansExecution：查看计划选择期间收集的部分执行统计信息。
-
我们主要用的是excutionStats模式来进行分析，如上最长的那个explain返回的json为例：
-
对queryPlanner分析
- queryPlanner : 标识queryPlanner的返回
- queryPlanner.namespace:该值返回的是该query所查询的表
- queryPlanner.indexFilterSet:针对该query是否有indexfilter
- queryPlanner.winningPlan:查询优化器针对该query所返回的最优执行计划的详细内容。
- queryPlanner.winningPlan.stage:最优执行计划的stage，这里返回是SORT，可以理解为通过返回的index位置去检索具体的文档，并且在内存中进行了排序（stage有多个模式，将在后文中进行解释）。
- queryPlanner.winningPlan.inputStage:用来描述子stage，并且为其父stage提供文档和索引关键字。
- queryPlanner.winningPlan.inputStage.stage: 此处是SORT_KEY_GENERATOR，这个在官网上找了好久只找到一个issues，最后在stackoverflow上找到了我需要的答案，最关键的一句话： means in-memory sorting，在内存中排序的
- queryPlanner.winningPlan.keyPattern:所扫描的index内容，此处是"subscriberID" : 1,“momentID” : 1
- queryPlanner.winningPlan.indexName：winning plan所选用的index。这里选用的idx_objectid_momentid 索引
- queryPlanner.winningPlan.isMultiKey是否是Multikey，此处返回是false，如果索引建立在array上，此处将是true。
- queryPlanner.winningPlan.direction：此query的查询顺序，此处是forward，如果查询顺序与设置顺序相反则必然是backward，此部分以下会用案例解释。
- queryPlanner.winningPlan.indexBounds:winningplan所扫描的索引范围,如果没有制定范围就是[MaxKey, MinKey]，这主要是直接定位到mongodb的chunck中去查找数据，加快数据读取。（chunk是指一个集合数据中的子集，在分片的时候，每个分区就是一个chunck）
- queryPlanner.rejectedPlans：其他执行计划（非最优而被查询优化器reject的）的详细返回，其中具体信息与winningPlan的返回中意义相同
-
对executionStats中时间分析
- executionTimeMillis : 指我们预计的执行时间，越小越好，其中有三个这个时间数据，分别如下：
- 第一层， executionStats.executionTimeMillis： 该query的整体查询时间。
- 第二层，executionStats.executionStages.executionTimeMillisEstimate： 该查询根据index去检索document获得71条数据的时间。
- 第三层，executionStats.executionStages.inputStage.executionTimeMillisEstimate：此处没有，该查询扫描71行index所用时间。
- executionTimeMillis : 指我们预计的执行时间，越小越好，其中有三个这个时间数据，分别如下：
-
对executionStats中扫描数量的分析
- nReturned、totalKeysExamined、totalDocsExamined，分别代表查询返回条数，索引扫描条数，文档扫描条数，这些都是直观地影响到executionTimeMillis，我们需要扫描的越少速度越快。
- 对应一个查询，理想状态下是 nReturned=totalKeysExamined=totalDocsExamined，也就是我们需要的数据都在索引数据文档中找到了
-
对stage状态分析
- stage的类型会影响totalKeysExamined和totalDocsExamined的最终扫描值
- COLLSCAN：全表扫描
- IXSCAN：索引扫描
- FETCH：根据索引去检索指定document
- SHARD_MERGE：将各个分片返回数据进行merge
- SORT：表明在内存中进行了排序
- SORT_KEY_GENERATOR ： 表名在内存中排序
- LIMIT：使用limit限制返回数
- SKIP：使用skip进行跳过
- IDHACK：针对_id进行查询
- SHARDING_FILTER：通过mongos对分片数据进行查询
- COUNT：利用db.coll.explain().count()之类进行count运算
- COUNTSCAN：count不使用Index进行count时的stage返回
- COUNT_SCAN：count使用了Index进行count时的stage返回
- SUBPLA：未使用到索引的$or查询的stage返回
- TEXT：使用全文索引进行查询时候的stage返回
- PROJECTION：限定返回字段时候stage的返回
- SINGLE_SHARD： 查询落到单个分片上查询
-
我们生产中期望得到的是：
- Fetch+IDHACK
- Fetch+IXSCAN
- Limit+（Fetch+IXSCAN）
- PROJECTION+IXSCAN
- SINGLE_SHARD+IXSCAN
- COUNT_SCAN
-
不希望看到的：
- COLLSCAN，全表
- SORT，使用排序但是没有命中索引，内存排序
- 不要用SKIP去做跳过操作
- SUBPLA，没有用到索引的or命令
- COUNTSCAN，没有用到索引的count
-
关于上面direction是backward的原因，有必要说明：
-
我们在没有设置排序字段时候，因为走的索引是idx_objectid_momentid ：{“subscriberID” : 1,“momentID” : 1}，两个字段索引都是顺序创建的。如下在默认无指定排序情况下，momentID是顺序排列的。
-
如果我们强制这条语句走 subscriberID_1_type_1_auditTime_1 索引，那么他肯定是backround，因为你查询的顺序和你索引创建的顺序正好是相反的：
//db.moment.explain("executionStats").find({"subscriberID" : NumberLong(1884831336), "type" : { "$in" : [1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19] } }).sort({"auditTime": -1}).hint({"subscriberID" : 1,"type" : 1,"auditTime" : 1});
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "cmgo-hgz5ixhj_0",
"connectionString" : "cmgo-hgz5ixhj_0/100.120.151.75:7004,100.120.152.197:7000,100.93.47.81:7016",
"serverInfo" : {
"host" : "TENCENT64.site",
"port" : 7016,
"version" : "3.2.10-cmongo-",
"gitVersion" : "nogitversion"
},
"plannerVersion" : 1,
"namespace" : "moment.moment",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"subscriberID" : {
"$eq" : NumberLong(1884831336)
}
},
{
"type" : {
"$in" : [
1,
2,
3,
4,
5,
6,
7,
8,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19
]
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT_MERGE",
"sortPattern" : {
"auditTime" : -1
},
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[19.0, 19.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[18.0, 18.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[17.0, 17.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[16.0, 16.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[15.0, 15.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[14.0, 14.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[13.0, 13.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[12.0, 12.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[11.0, 11.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[10.0, 10.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[8.0, 8.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[7.0, 7.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[6.0, 6.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[5.0, 5.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[4.0, 4.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[3.0, 3.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[2.0, 2.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[1.0, 1.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
}
]
}
},
"rejectedPlans" : [ ]
}
]
}
},
"executionStats" : {
"nReturned" : 71,
"executionTimeMillis" : 3,
"totalKeysExamined" : 71,
"totalDocsExamined" : 71,
"executionStages" : {
"stage" : "SINGLE_SHARD",
"nReturned" : 71,
"executionTimeMillis" : 3,
"totalKeysExamined" : 71,
"totalDocsExamined" : 71,
"totalChildMillis" : NumberLong(1),
"shards" : [
{
"shardName" : "cmgo-hgz5ixhj_0",
"executionSuccess" : true,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 71,
"executionTimeMillisEstimate" : 0,
"works" : 161,
"advanced" : 71,
"needTime" : 89,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 71,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "SORT_MERGE",
"nReturned" : 71,
"executionTimeMillisEstimate" : 0,
"works" : 160,
"advanced" : 71,
"needTime" : 89,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"auditTime" : -1
},
"dupsTested" : 71,
"dupsDropped" : 0,
"inputStages" : [
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[19.0, 19.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[18.0, 18.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[17.0, 17.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[16.0, 16.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[15.0, 15.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[14.0, 14.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[13.0, 13.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 6,
"executionTimeMillisEstimate" : 0,
"works" : 7,
"advanced" : 6,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[12.0, 12.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 6,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[11.0, 11.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[10.0, 10.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[8.0, 8.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[7.0, 7.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[6.0, 6.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[5.0, 5.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 3,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 8,
"executionTimeMillisEstimate" : 0,
"works" : 9,
"advanced" : 8,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[4.0, 4.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 8,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 25,
"executionTimeMillisEstimate" : 0,
"works" : 26,
"advanced" : 25,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[3.0, 3.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 25,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 28,
"executionTimeMillisEstimate" : 0,
"works" : 29,
"advanced" : 28,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[2.0, 2.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 28,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[1884831336, 1884831336]"
],
"type" : [
"[1.0, 1.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
]
}
}
}
]
}
},
"ok" : 1
}
-
以上语句也就是我们动态中用的第二多的一条语句，当关注页面查看第二页动态时候，会带上_id，这个时候我们用hint 命令强制他走了 subscriberID_1_type_1_auditTime_1 索引，此时他的查询是FETCH + SORT_MERGE，其中SORT_MERGE 也是在内存进行排序并且合并得到最好结果。
-
以上分析起来索引应该有优化的空间，例如我建立以下索引：
db.moment.createIndex({"subscriberID" : 1,"type" : 1,"auditTime" : -1 })
- 看起来完美契合我们的查询：
db.moment.explain("executionStats").find({ "subscriberID" : NumberLong(2000103230), "type" : { "$in" : [1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19] } }).sort({"auditTime": -1});
//得到的执行结果：
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "moment.moment",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"subscriberID" : {
"$eq" : NumberLong(2000103230)
}
},
{
"type" : {
"$in" : [
1,
2,
3,
4,
5,
6,
7,
8,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19
]
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"auditTime" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"type" : {
"$in" : [
1,
2,
3,
4,
5,
6,
7,
8,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19
]
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"unread" : 1
},
"indexName" : "subscriberID_1_unread_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"unread" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"unread" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT_MERGE",
"sortPattern" : {
"auditTime" : -1
},
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[1.0, 1.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[2.0, 2.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[3.0, 3.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[4.0, 4.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[5.0, 5.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[6.0, 6.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[7.0, 7.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[8.0, 8.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[10.0, 10.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[11.0, 11.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[12.0, 12.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[13.0, 13.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[14.0, 14.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[15.0, 15.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[16.0, 16.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[17.0, 17.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[18.0, 18.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : -1
},
"indexName" : "subscriberID_1_type_1_auditTime_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[19.0, 19.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
}
]
}
},
{
"stage" : "SORT",
"sortPattern" : {
"auditTime" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"type" : {
"$in" : [
1,
2,
3,
4,
5,
6,
7,
8,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19
]
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"momentID" : 1
},
"indexName" : "idx_objectid_momentid",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"momentID" : [ ]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"momentID" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"auditTime" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"unread" : 1,
"type" : 1
},
"indexName" : "subscriberID_1_unread_1_type_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"unread" : [ ],
"type" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"unread" : [
"[MinKey, MaxKey]"
],
"type" : [
"[1.0, 1.0]",
"[2.0, 2.0]",
"[3.0, 3.0]",
"[4.0, 4.0]",
"[5.0, 5.0]",
"[6.0, 6.0]",
"[7.0, 7.0]",
"[8.0, 8.0]",
"[10.0, 10.0]",
"[11.0, 11.0]",
"[12.0, 12.0]",
"[13.0, 13.0]",
"[14.0, 14.0]",
"[15.0, 15.0]",
"[16.0, 16.0]",
"[17.0, 17.0]",
"[18.0, 18.0]",
"[19.0, 19.0]"
]
}
}
}
}
},
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT_MERGE",
"sortPattern" : {
"auditTime" : -1
},
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[19.0, 19.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[18.0, 18.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[17.0, 17.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[16.0, 16.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[15.0, 15.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[14.0, 14.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[13.0, 13.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[12.0, 12.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[11.0, 11.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[10.0, 10.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[8.0, 8.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[7.0, 7.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[6.0, 6.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[5.0, 5.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[4.0, 4.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[3.0, 3.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[2.0, 2.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"subscriberID" : 1,
"type" : 1,
"auditTime" : 1
},
"indexName" : "subscriberID_1_type_1_auditTime_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"type" : [ ],
"auditTime" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"type" : [
"[1.0, 1.0]"
],
"auditTime" : [
"[MaxKey, MinKey]"
]
}
}
]
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 4,
"executionTimeMillis" : 1,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 12,
"advanced" : 4,
"needTime" : 6,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"auditTime" : -1
},
"memUsage" : 764,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 4,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"type" : {
"$in" : [
1,
2,
3,
4,
5,
6,
7,
8,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19
]
}
},
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 4,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"subscriberID" : 1,
"unread" : 1
},
"indexName" : "subscriberID_1_unread_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"subscriberID" : [ ],
"unread" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subscriberID" : [
"[2000103230, 2000103230]"
],
"unread" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "mongodb-0",
"port" : 27017,
"version" : "3.4.21",
"gitVersion" : "ba2e818de44c9a331ce09a624780a2bd841cf6e4"
},
"ok" : 1
}
- 我们看到结果依然是 SORT + SORT_KEY_GENERATOR，意思是我们依然需要在内存中排序，原因在于当我们建立：{“subscriberID” : 1,“type” : 1,“auditTime” : -1 } 索引的时候，MongoDB会按照如下方式给我吗组织索引数据：
- 优先按subscriberID 的顺序，
- 然后 type顺序
- 最后才是 auditTime 的逆序，如下案例
mongos> db.moment.find({ "subscriberID" : NumberLong(1896740278), "type" : { "$in" : [1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19] } });
{ "_id" : ObjectId("5fd09738fb1a7b001444e64b"), "type" : 1, "momentID" : NumberLong(257617647), "memberID" : NumberLong(1519251653), "subscriberID" : NumberLong(1896740278), "auditStatus" : 0, "unread" : true, "createTime" : ISODate("2020-12-09T09:21:59Z"), }
{ "_id" : ObjectId("60a3a82062c4680014c7dd21"), "type" : 1, "momentID" : NumberLong(405945302), "subscriberID" : NumberLong(1896740278), "unread" : true, "auditTime" : ISODate("2021-05-18T11:42:25Z"), "createTime" : ISODate("2021-05-18T11:28:11Z"), }
{ "_id" : ObjectId("60a65571d359c60014c6149c"), "type" : 1, "momentID" : NumberLong(406114684), "subscriberID" : NumberLong(1896740278), "unread" : true, "auditTime" : ISODate("2021-05-20T12:26:25Z"), "createTime" : ISODate("2021-05-20T12:10:39Z"), }
{ "_id" : ObjectId("60d3000178512e001443830f"), "type" : 1, "momentID" : NumberLong(408785569), "subscriberID" : NumberLong(1896740278), "unread" : true, "auditTime" : ISODate("2021-06-23T09:33:53Z"), "createTime" : ISODate("2021-06-23T09:26:52Z"), }
{ "_id" : ObjectId("60e6d944ccc7d000148edac2"), "type" : 1, "momentID" : NumberLong(409971805), "subscriberID" : NumberLong(1896740278), "unread" : true, "auditTime" : ISODate("2021-07-08T10:53:56Z"), "createTime" : ISODate("2021-07-08T10:45:38Z"), }
{ "_id" : ObjectId("60eb97d3f12ad4001491f83f"), "type" : 1, "momentID" : NumberLong(410215242), "subscriberID" : NumberLong(1896740278), "unread" : true, "auditTime" : ISODate("2021-07-12T01:16:03Z"), "createTime" : ISODate("2021-07-12T01:07:27Z"), }
{ "_id" : ObjectId("61016cc1b572ea001497550f"), "type" : 1, "momentID" : NumberLong(411549007), "subscriberID" : NumberLong(1896740278), "unread" : true, "auditTime" : ISODate("2021-07-28T14:42:09Z"), "createTime" : ISODate("2021-07-28T14:42:09Z"), }
-
可以看到在objectId相同的时候，他是按照momentID的顺序排列，但是auditTime的顺序只有在momentID相同的时候才是逆序，实际按索引来看他还是乱序的。因此我们在对时间排序的时候，他依然需要在内存排序。
-
那么我们应该创建如下的索引：
db.moment.createIndex({"subscriberID" : 1,"auditTime" : -1 ,"type" : 1})
db.moment.createIndex({"type" : 1,"auditTime" : -1,"subscriberID" : 1})
-
因为我们是分片的数据库，按照subscriberID分片，因此，这两个索引效果是一样的。但是两个都创建的话，MongoDB会给你选择第一个，排序优先
-
在我们项目中，查询关注动态部分有两个查询语句，一条查询第一页，一条查询之后的页面数据，带有_id，占比如下：
-
总的查询数：2,007,778
- 第二页以及以后的查询数：584258
- 第二页查询占比28% 左右，也就是大多其实都是在第一页查看，由此看来索引还是有优化空间的。
370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
