做机械设计兼职的网站,南京网站推广营销公司,广东省深圳市龙华区,西安旅游必去十大景点Mongodb查询分析器
动态相关项目中涉及到数据量大和吞吐量的接口#xff0c;例如关注页面动态#xff0c;附近动态#xff0c;这部分数据都是存储在mongodb中#xff0c;在线上数据中分类两个mongodb集合存储其中关注动态基于扩散写的设计#xff0c;数据量已经快到 8 亿…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是否有indexfilterqueryPlanner.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” : 1queryPlanner.winningPlan.indexNamewinning 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是指一个集合数据中的子集在分片的时候每个分区就是一个chunckqueryPlanner.rejectedPlans其他执行计划非最优而被查询优化器reject的的详细返回其中具体信息与winningPlan的返回中意义相同 对executionStats中时间分析 executionTimeMillis : 指我们预计的执行时间越小越好其中有三个这个时间数据分别如下 第一层 executionStats.executionTimeMillis 该query的整体查询时间。第二层executionStats.executionStages.executionTimeMillisEstimate 该查询根据index去检索document获得71条数据的时间。第三层executionStats.executionStages.inputStage.executionTimeMillisEstimate此处没有该查询扫描71行index所用时间。 对executionStats中扫描数量的分析 nReturned、totalKeysExamined、totalDocsExamined分别代表查询返回条数索引扫描条数文档扫描条数这些都是直观地影响到executionTimeMillis我们需要扫描的越少速度越快。对应一个查询理想状态下是 nReturnedtotalKeysExaminedtotalDocsExamined也就是我们需要的数据都在索引数据文档中找到了 对stage状态分析 stage的类型会影响totalKeysExamined和totalDocsExamined的最终扫描值COLLSCAN全表扫描IXSCAN索引扫描FETCH根据索引去检索指定documentSHARD_MERGE将各个分片返回数据进行mergeSORT表明在内存中进行了排序SORT_KEY_GENERATOR 表名在内存中排序LIMIT使用limit限制返回数SKIP使用skip进行跳过IDHACK针对_id进行查询SHARDING_FILTER通过mongos对分片数据进行查询COUNT利用db.coll.explain().count()之类进行count运算COUNTSCANcount不使用Index进行count时的stage返回COUNT_SCANcount使用了Index进行count时的stage返回SUBPLA未使用到索引的$or查询的stage返回TEXT使用全文索引进行查询时候的stage返回PROJECTION限定返回字段时候stage的返回SINGLE_SHARD 查询落到单个分片上查询 我们生产中期望得到的是 FetchIDHACKFetchIXSCANLimitFetchIXSCANPROJECTIONIXSCANSINGLE_SHARDIXSCANCOUNT_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% 左右也就是大多其实都是在第一页查看由此看来索引还是有优化空间的。
