问题描述
版本Mongo3.2.10
问题稠密集下geo查询返回最近的30条，需要扫描20000+的记录。同样的点集，尝试建立2d索引和2dsphere索引查询，执行效率相差不大。
执行语句语句1的执行环境2d index
{'v' : 1,'key' : {'lag' : '2d'},'name' : 'lag','min' : -180,'ns' : 'mobike_default.mBKBikeInfo','bits' : 26,'max' : 180},
db.coll.find({lag:{’$nearSphere’:[120.993965,31.449034 ], ’$maxDistance’:7.83927971443699e-05*2}}).limit(30).explain(true)
MongoDB shell version: 3.2.8connecting to: 10.108.93.135:7004/admin{
'queryPlanner' : { 'plannerVersion' : 1, 'namespace' : '{db}.{coll}', 'indexFilterSet' : false, 'parsedQuery' : {'lag' : { '$nearSphere' : [120.993965,31.449034 ], '$maxDistance' : 0.0000783927971443699} }, 'winningPlan' : {'stage' : 'LIMIT','limitAmount' : 30,'inputStage' : { 'stage' : 'GEO_NEAR_2D', 'keyPattern' : {'lag' : '2d' }, 'indexName' : 'lag', 'inputStages' : [{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11D200000000), BinData(128, E33C11D2FFFFFFFF)]','[BinData(128, E33C11D300000000), BinData(128, E33C11D33FFFFFFF)]','[BinData(128, E33C11D360000000), BinData(128, E33C11D36FFFFFFF)]','[BinData(128, E33C11D370000000), BinData(128, E33C11D37FFFFFFF)]','[BinData(128, E33C11D380000000), BinData(128, E33C11D3BFFFFFFF)]','[BinData(128, E33C11D3C0000000), BinData(128, E33C11D3FFFFFFFF)]','[BinData(128, E33C11D688000000), BinData(128, E33C11D68BFFFFFF)]','[BinData(128, E33C11D6A0000000), BinData(128, E33C11D6AFFFFFFF)]','[BinData(128, E33C11D800000000), BinData(128, E33C11D8FFFFFFFF)]','[BinData(128, E33C11D900000000), BinData(128, E33C11D9FFFFFFFF)]','[BinData(128, E33C11DA10000000), BinData(128, E33C11DA1FFFFFFF)]','[BinData(128, E33C11DA40000000), BinData(128, E33C11DA7FFFFFFF)]','[BinData(128, E33C11DB00000000), BinData(128, E33C11DB3FFFFFFF)]','[BinData(128, E33C11DB40000000), BinData(128, E33C11DB7FFFFFFF)]','[BinData(128, E33C11DC00000000), BinData(128, E33C11DC3FFFFFFF)]','[BinData(128, E33C11DC80000000), BinData(128, E33C11DCBFFFFFFF)]' ]} }},{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : [ ]} }},{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : [ ]} }},{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11D340000000), BinData(128, E33C11D34FFFFFFF)]','[BinData(128, E33C11D350000000), BinData(128, E33C11D35FFFFFFF)]','[BinData(128, E33C11D680000000), BinData(128, E33C11D683FFFFFF)]','[BinData(128, E33C11D684000000), BinData(128, E33C11D687FFFFFF)]','[BinData(128, E33C11D68C000000), BinData(128, E33C11D68FFFFFFF)]' ]} }},{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : [ ]} }},{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11CD40000000), BinData(128, E33C11CD7FFFFFFF)]','[BinData(128, E33C11D690000000), BinData(128, E33C11D69FFFFFFF)]','[BinData(128, E33C11D6B0000000), BinData(128, E33C11D6BFFFFFFF)]','[BinData(128, E33C11DA00000000), BinData(128, E33C11DA0FFFFFFF)]','[BinData(128, E33C11DA20000000), BinData(128, E33C11DA2FFFFFFF)]','[BinData(128, E33C11DA30000000), BinData(128, E33C11DA3FFFFFFF)]' ]} }},{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11C7FC000000), BinData(128, E33C11C7FFFFFFFF)]','[BinData(128, E33C11CDD0000000), BinData(128, E33C11CDDFFFFFFF)]','[BinData(128, E33C11D62AA00000), BinData(128, E33C11D62AAFFFFF)]','[BinData(128, E33C11DE00000000), BinData(128, E33C11DE03FFFFFF)]' ]} }},{ 'stage' : 'FETCH', 'inputStage' : {'stage' : 'IXSCAN','keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11C7C0000000), BinData(128, E33C11C7CFFFFFFF)]','[BinData(128, E33C11C7D0000000), BinData(128, E33C11C7DFFFFFFF)]','[BinData(128, E33C11C7E0000000), BinData(128, E33C11C7EFFFFFFF)]','[BinData(128, E33C11C7F0000000), BinData(128, E33C11C7F3FFFFFF)]','[BinData(128, E33C11C7F4000000), BinData(128, E33C11C7F7FFFFFF)]','[BinData(128, E33C11C7F8000000), BinData(128, E33C11C7FBFFFFFF)]','[BinData(128, E33C11CDC0000000), BinData(128, E33C11CDCFFFFFFF)]','[BinData(128, E33C11CDE0000000), BinData(128, E33C11CDEFFFFFFF)]','[BinData(128, E33C11CDF0000000), BinData(128, E33C11CDFFFFFFFF)]','[BinData(128, E33C11D620000000), BinData(128, E33C11D623FFFFFF)]','[BinData(128, E33C11D624000000), BinData(128, E33C11D627FFFFFF)]','[BinData(128, E33C11D628000000), BinData(128, E33C11D628FFFFFF)]','[BinData(128, E33C11D629000000), BinData(128, E33C11D629FFFFFF)]','[BinData(128, E33C11D62A000000), BinData(128, E33C11D62A3FFFFF)]','[BinData(128, E33C11D62A400000), BinData(128, E33C11D62A7FFFFF)]','[BinData(128, E33C11D62A800000), BinData(128, E33C11D62A8FFFFF)]','[BinData(128, E33C11D62A900000), BinData(128, E33C11D62A9FFFFF)]','[BinData(128, E33C11D62AB00000), BinData(128, E33C11D62ABFFFFF)]','[BinData(128, E33C11D62AC00000), BinData(128, E33C11D62AFFFFFF)]','[BinData(128, E33C11D62B000000), BinData(128, E33C11D62BFFFFFF)]','[BinData(128, E33C11D62C000000), BinData(128, E33C11D62FFFFFFF)]','[BinData(128, E33C11DA80000000), BinData(128, E33C11DABFFFFFFF)]','[BinData(128, E33C11DAC0000000), BinData(128, E33C11DAFFFFFFFF)]','[BinData(128, E33C11DB80000000), BinData(128, E33C11DBBFFFFFFF)]','[BinData(128, E33C11DE04000000), BinData(128, E33C11DE07FFFFFF)]','[BinData(128, E33C11DE08000000), BinData(128, E33C11DE0BFFFFFF)]','[BinData(128, E33C11DE0C000000), BinData(128, E33C11DE0FFFFFFF)]' ]} }} ]} }, 'rejectedPlans' : [ ]},'executionStats' : { 'executionSuccess' : true, 'nReturned' : 30, 'executionTimeMillis' : 185, 'totalKeysExamined' : 24613, 'totalDocsExamined' : 24599, 'executionStages' : {'stage' : 'LIMIT','nReturned' : 30,'executionTimeMillisEstimate' : 160,'works' : 24664,'advanced' : 30,'needTime' : 24633,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'limitAmount' : 30,'inputStage' : { 'stage' : 'GEO_NEAR_2D', 'nReturned' : 30, 'executionTimeMillisEstimate' : 160, 'works' : 24663, 'advanced' : 30, 'needTime' : 24633, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 0, 'invalidates' : 0, 'keyPattern' : {'lag' : '2d' }, 'indexName' : 'lag', 'searchIntervals' : [{ 'minDistance' : 0, 'maxDistance' : 305.746339475419, 'maxInclusive' : false, 'nBuffered' : 5, 'nReturned' : 5},{ 'minDistance' : 305.746339475419, 'maxDistance' : 306.16860060551534, 'maxInclusive' : false, 'nBuffered' : 0, 'nReturned' : 0},{ 'minDistance' : 306.16860060551534, 'maxDistance' : 307.01312286570794, 'maxInclusive' : false, 'nBuffered' : 0, 'nReturned' : 0},{ 'minDistance' : 307.01312286570794, 'maxDistance' : 308.70216738609315, 'maxInclusive' : false, 'nBuffered' : 0, 'nReturned' : 0},{ 'minDistance' : 308.70216738609315, 'maxDistance' : 312.0802564268636, 'maxInclusive' : false, 'nBuffered' : 0, 'nReturned' : 0},{ 'minDistance' : 312.0802564268636, 'maxDistance' : 318.8364345084046, 'maxInclusive' : false, 'nBuffered' : 0, 'nReturned' : 0},{ 'minDistance' : 318.8364345084046, 'maxDistance' : 332.3487906714865, 'maxInclusive' : false, 'nBuffered' : 0, 'nReturned' : 0},{ 'minDistance' : 332.3487906714865, 'maxDistance' : 359.37350299765023, 'maxInclusive' : false, 'nBuffered' : 24594, 'nReturned' : 25} ], 'inputStages' : [{ 'stage' : 'FETCH', 'nReturned' : 5, 'executionTimeMillisEstimate' : 0, 'works' : 8, 'advanced' : 5, 'needTime' : 2, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 5, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 5,'executionTimeMillisEstimate' : 0,'works' : 8,'advanced' : 5,'needTime' : 2,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11D200000000), BinData(128, E33C11D2FFFFFFFF)]','[BinData(128, E33C11D300000000), BinData(128, E33C11D33FFFFFFF)]','[BinData(128, E33C11D360000000), BinData(128, E33C11D36FFFFFFF)]','[BinData(128, E33C11D370000000), BinData(128, E33C11D37FFFFFFF)]','[BinData(128, E33C11D380000000), BinData(128, E33C11D3BFFFFFFF)]','[BinData(128, E33C11D3C0000000), BinData(128, E33C11D3FFFFFFFF)]','[BinData(128, E33C11D688000000), BinData(128, E33C11D68BFFFFFF)]','[BinData(128, E33C11D6A0000000), BinData(128, E33C11D6AFFFFFFF)]','[BinData(128, E33C11D800000000), BinData(128, E33C11D8FFFFFFFF)]','[BinData(128, E33C11D900000000), BinData(128, E33C11D9FFFFFFFF)]','[BinData(128, E33C11DA10000000), BinData(128, E33C11DA1FFFFFFF)]','[BinData(128, E33C11DA40000000), BinData(128, E33C11DA7FFFFFFF)]','[BinData(128, E33C11DB00000000), BinData(128, E33C11DB3FFFFFFF)]','[BinData(128, E33C11DB40000000), BinData(128, E33C11DB7FFFFFFF)]','[BinData(128, E33C11DC00000000), BinData(128, E33C11DC3FFFFFFF)]','[BinData(128, E33C11DC80000000), BinData(128, E33C11DCBFFFFFFF)]' ]},'keysExamined' : 8,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }},{ 'stage' : 'FETCH', 'nReturned' : 0, 'executionTimeMillisEstimate' : 0, 'works' : 1, 'advanced' : 0, 'needTime' : 0, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 0, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 0,'executionTimeMillisEstimate' : 0,'works' : 1,'advanced' : 0,'needTime' : 0,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : [ ]},'keysExamined' : 0,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }},{ 'stage' : 'FETCH', 'nReturned' : 0, 'executionTimeMillisEstimate' : 0, 'works' : 1, 'advanced' : 0, 'needTime' : 0, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 0, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 0,'executionTimeMillisEstimate' : 0,'works' : 1,'advanced' : 0,'needTime' : 0,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : [ ]},'keysExamined' : 0,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }},{ 'stage' : 'FETCH', 'nReturned' : 0, 'executionTimeMillisEstimate' : 0, 'works' : 2, 'advanced' : 0, 'needTime' : 1, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 0, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 0,'executionTimeMillisEstimate' : 0,'works' : 2,'advanced' : 0,'needTime' : 1,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11D340000000), BinData(128, E33C11D34FFFFFFF)]','[BinData(128, E33C11D350000000), BinData(128, E33C11D35FFFFFFF)]','[BinData(128, E33C11D680000000), BinData(128, E33C11D683FFFFFF)]','[BinData(128, E33C11D684000000), BinData(128, E33C11D687FFFFFF)]','[BinData(128, E33C11D68C000000), BinData(128, E33C11D68FFFFFFF)]' ]},'keysExamined' : 2,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }},{ 'stage' : 'FETCH', 'nReturned' : 0, 'executionTimeMillisEstimate' : 0, 'works' : 1, 'advanced' : 0, 'needTime' : 0, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 0, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 0,'executionTimeMillisEstimate' : 0,'works' : 1,'advanced' : 0,'needTime' : 0,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : [ ]},'keysExamined' : 0,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }},{ 'stage' : 'FETCH', 'nReturned' : 0, 'executionTimeMillisEstimate' : 0, 'works' : 3, 'advanced' : 0, 'needTime' : 2, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 0, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 0,'executionTimeMillisEstimate' : 0,'works' : 3,'advanced' : 0,'needTime' : 2,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11CD40000000), BinData(128, E33C11CD7FFFFFFF)]','[BinData(128, E33C11D690000000), BinData(128, E33C11D69FFFFFFF)]','[BinData(128, E33C11D6B0000000), BinData(128, E33C11D6BFFFFFFF)]','[BinData(128, E33C11DA00000000), BinData(128, E33C11DA0FFFFFFF)]','[BinData(128, E33C11DA20000000), BinData(128, E33C11DA2FFFFFFF)]','[BinData(128, E33C11DA30000000), BinData(128, E33C11DA3FFFFFFF)]' ]},'keysExamined' : 3,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }},{ 'stage' : 'FETCH', 'nReturned' : 0, 'executionTimeMillisEstimate' : 0, 'works' : 3, 'advanced' : 0, 'needTime' : 2, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 0, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 0,'executionTimeMillisEstimate' : 0,'works' : 3,'advanced' : 0,'needTime' : 2,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11C7FC000000), BinData(128, E33C11C7FFFFFFFF)]','[BinData(128, E33C11CDD0000000), BinData(128, E33C11CDDFFFFFFF)]','[BinData(128, E33C11D62AA00000), BinData(128, E33C11D62AAFFFFF)]','[BinData(128, E33C11DE00000000), BinData(128, E33C11DE03FFFFFF)]' ]},'keysExamined' : 3,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }},{ 'stage' : 'FETCH', 'nReturned' : 24594, 'executionTimeMillisEstimate' : 90, 'works' : 24597, 'advanced' : 24594, 'needTime' : 2, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'docsExamined' : 24594, 'alreadyHasObj' : 0, 'inputStage' : {'stage' : 'IXSCAN','nReturned' : 24594,'executionTimeMillisEstimate' : 30,'works' : 24597,'advanced' : 24594,'needTime' : 2,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'keyPattern' : { 'lag' : '2d'},'indexName' : 'lag','isMultiKey' : false,'isUnique' : false,'isSparse' : false,'isPartial' : false,'indexVersion' : 1,'direction' : 'forward','indexBounds' : { 'lag' : ['[BinData(128, E33C11C7C0000000), BinData(128, E33C11C7CFFFFFFF)]','[BinData(128, E33C11C7D0000000), BinData(128, E33C11C7DFFFFFFF)]','[BinData(128, E33C11C7E0000000), BinData(128, E33C11C7EFFFFFFF)]','[BinData(128, E33C11C7F0000000), BinData(128, E33C11C7F3FFFFFF)]','[BinData(128, E33C11C7F4000000), BinData(128, E33C11C7F7FFFFFF)]','[BinData(128, E33C11C7F8000000), BinData(128, E33C11C7FBFFFFFF)]','[BinData(128, E33C11CDC0000000), BinData(128, E33C11CDCFFFFFFF)]','[BinData(128, E33C11CDE0000000), BinData(128, E33C11CDEFFFFFFF)]','[BinData(128, E33C11CDF0000000), BinData(128, E33C11CDFFFFFFFF)]','[BinData(128, E33C11D620000000), BinData(128, E33C11D623FFFFFF)]','[BinData(128, E33C11D624000000), BinData(128, E33C11D627FFFFFF)]','[BinData(128, E33C11D628000000), BinData(128, E33C11D628FFFFFF)]','[BinData(128, E33C11D629000000), BinData(128, E33C11D629FFFFFF)]','[BinData(128, E33C11D62A000000), BinData(128, E33C11D62A3FFFFF)]','[BinData(128, E33C11D62A400000), BinData(128, E33C11D62A7FFFFF)]','[BinData(128, E33C11D62A800000), BinData(128, E33C11D62A8FFFFF)]','[BinData(128, E33C11D62A900000), BinData(128, E33C11D62A9FFFFF)]','[BinData(128, E33C11D62AB00000), BinData(128, E33C11D62ABFFFFF)]','[BinData(128, E33C11D62AC00000), BinData(128, E33C11D62AFFFFFF)]','[BinData(128, E33C11D62B000000), BinData(128, E33C11D62BFFFFFF)]','[BinData(128, E33C11D62C000000), BinData(128, E33C11D62FFFFFFF)]','[BinData(128, E33C11DA80000000), BinData(128, E33C11DABFFFFFFF)]','[BinData(128, E33C11DAC0000000), BinData(128, E33C11DAFFFFFFFF)]','[BinData(128, E33C11DB80000000), BinData(128, E33C11DBBFFFFFFF)]','[BinData(128, E33C11DE04000000), BinData(128, E33C11DE07FFFFFF)]','[BinData(128, E33C11DE08000000), BinData(128, E33C11DE0BFFFFFF)]','[BinData(128, E33C11DE0C000000), BinData(128, E33C11DE0FFFFFFF)]' ]},'keysExamined' : 24597,'dupsTested' : 0,'dupsDropped' : 0,'seenInvalidated' : 0 }} ]} }, 'allPlansExecution' : [ ]},'serverInfo' : { 'host' : '{host}', 'port' : 7004, 'version' : '0.0.1-fake-', 'gitVersion' : 'nogitversion'},'ok' : 1
}bye
语句2的执行环境2dsphere index
{'v' : 1,'key' : {'loc' : '2dsphere'},'name' : 'loc_2dsphere','ns' : 'mobike_default.kdytbl','2dsphereIndexVersion' : 3}
db.kdytbl.find({loc:{ $nearSphere: { $geometry: { type : 'Point',coordinates : [120.993965,31.449034]},$maxDistance:1000}}}).limit(30).explain(true)
MongoDB shell version: 3.2.8connecting to: 10.108.93.135:7004/admin{
'queryPlanner' : { 'plannerVersion' : 1, 'namespace' : '{db}.{coll}', 'indexFilterSet' : false, 'parsedQuery' : {'loc' : { '$nearSphere' : {'$geometry' : { 'type' : 'Point', 'coordinates' : [120.993965,31.449034 ]},'$maxDistance' : 1000 }} }, 'winningPlan' : {'stage' : 'LIMIT','limitAmount' : 1,'inputStage' : { 'stage' : 'GEO_NEAR_2DSPHERE', 'keyPattern' : {'loc' : '2dsphere' }, 'indexName' : 'loc_2dsphere', 'inputStage' : {'stage' : 'FETCH','inputStage' : { 'stage' : 'IXSCAN', 'keyPattern' : {'loc' : '2dsphere' }, 'indexName' : 'loc_2dsphere', 'isMultiKey' : false, 'isUnique' : false, 'isSparse' : false, 'isPartial' : false, 'indexVersion' : 1, 'direction' : 'forward', 'indexBounds' : {'loc' : [ '[3819052484010180608, 3819052484010180608]', '[3868592079911256064, 3868592079911256064]', '[3869225398608855040, 3869225398608855040]', '[3869235294213505024, 3869235294213505024]', '[3869235569091411968, 3869235569091411968]', '[3869235579828830209, 3869235581976313855]', '[3869235581976313856, 3869235581976313856]', '[3869235586271281152, 3869235586271281152]', '[3869235637810888704, 3869235637810888704]', '[3869235689350496256, 3869235689350496256]', '[3869235689350496257, 3869235697940430847]', '[3869235697940430849, 3869235706530365439]', '[3869235706530365441, 3869235740890103807]', '[3869235740890103809, 3869235749480038399]', '[3869235758069972992, 3869235758069972992]', '[3869235766659907585, 3869235775249842175]', '[3869235775249842176, 3869235775249842176]', '[3869235775249842177, 3869235783839776767]', '[3869235792429711360, 3869235792429711360]', '[3869235818199515137, 3869235826789449727]', '[3869235826789449728, 3869235826789449728]', '[3869235827863191552, 3869235827863191552]', '[3869235827863191553, 3869235828400062463]', '[3869235828400062465, 3869235828936933375]', '[3869235828936933377, 3869235831084417023]', '[3869235831084417024, 3869235831084417024]', '[3869235831084417025, 3869235833231900671]', '[3869237493236760576, 3869237493236760576]', '[3869238317870481408, 3869238317870481408]', '[3869238386589958144, 3869238386589958144]', '[3869238403769827328, 3869238403769827328]', '[3869238416654729216, 3869238416654729216]', '[3869238419875954688, 3869238419875954688]', '[3869238420681261056, 3869238420681261056]', '[3869238420815478785, 3869238420949696511]', '[3869238420949696513, 3869238423097180159]', '[3869238423097180161, 3869238425244663807]', '[3869238425244663808, 3869238425244663808]', '[3869238425244663809, 3869238427392147455]', '[3869238438129565696, 3869238438129565696]', '[3869238446719500289, 3869238455309434879]', '[3869238455309434881, 3869238457456918527]', '[3869238457456918529, 3869238459604402175]', '[3869238459604402176, 3869238459604402176]', '[3869238461751885825, 3869238463899369471]', '[3869238472489304064, 3869238472489304064]', '[3869238524028911616, 3869238524028911616]', '[3869238592748388352, 3869238592748388352]', '[3869242990794899456, 3869242990794899456]', '[3869436504841388032, 3869436504841388032]', '[3869717979818098688, 3869717979818098688]', '[3873095679538626560, 3873095679538626560]'] }} }} }, 'rejectedPlans' : [ ]},'executionStats' : { 'executionSuccess' : true, 'nReturned' : 1, 'executionTimeMillis' : 195, 'totalKeysExamined' : 24627, 'totalDocsExamined' : 24615, 'executionStages' : {'stage' : 'LIMIT','nReturned' : 1,'executionTimeMillisEstimate' : 190,'works' : 24646,'advanced' : 1,'needTime' : 24644,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'limitAmount' : 1,'inputStage' : { 'stage' : 'GEO_NEAR_2DSPHERE', 'nReturned' : 1, 'executionTimeMillisEstimate' : 190, 'works' : 24645, 'advanced' : 1, 'needTime' : 24644, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 0, 'invalidates' : 0, 'keyPattern' : {'loc' : '2dsphere' }, 'indexName' : 'loc_2dsphere', 'searchIntervals' : [{ 'minDistance' : 0, 'maxDistance' : 1000, 'maxInclusive' : true, 'nBuffered' : 24615, 'nReturned' : 1} ], 'inputStage' : {'stage' : 'FETCH','nReturned' : 24615,'executionTimeMillisEstimate' : 100,'works' : 24627,'advanced' : 24615,'needTime' : 11,'needYield' : 0,'saveState' : 192,'restoreState' : 192,'isEOF' : 1,'invalidates' : 0,'docsExamined' : 24615,'alreadyHasObj' : 0,'inputStage' : { 'stage' : 'IXSCAN', 'nReturned' : 24615, 'executionTimeMillisEstimate' : 30, 'works' : 24627, 'advanced' : 24615, 'needTime' : 11, 'needYield' : 0, 'saveState' : 192, 'restoreState' : 192, 'isEOF' : 1, 'invalidates' : 0, 'keyPattern' : {'loc' : '2dsphere' }, 'indexName' : 'loc_2dsphere', 'isMultiKey' : false, 'isUnique' : false, 'isSparse' : false, 'isPartial' : false, 'indexVersion' : 1, 'direction' : 'forward', 'indexBounds' : {'loc' : [ '[3819052484010180608, 3819052484010180608]', '[3868592079911256064, 3868592079911256064]', '[3869225398608855040, 3869225398608855040]', '[3869235294213505024, 3869235294213505024]', '[3869235569091411968, 3869235569091411968]', '[3869235579828830209, 3869235581976313855]', '[3869235581976313856, 3869235581976313856]', '[3869235586271281152, 3869235586271281152]', '[3869235637810888704, 3869235637810888704]', '[3869235689350496256, 3869235689350496256]', '[3869235689350496257, 3869235697940430847]', '[3869235697940430849, 3869235706530365439]', '[3869235706530365441, 3869235740890103807]', '[3869235740890103809, 3869235749480038399]', '[3869235758069972992, 3869235758069972992]', '[3869235766659907585, 3869235775249842175]', '[3869235775249842176, 3869235775249842176]', '[3869235775249842177, 3869235783839776767]', '[3869235792429711360, 3869235792429711360]', '[3869235818199515137, 3869235826789449727]', '[3869235826789449728, 3869235826789449728]', '[3869235827863191552, 3869235827863191552]', '[3869235827863191553, 3869235828400062463]', '[3869235828400062465, 3869235828936933375]', '[3869235828936933377, 3869235831084417023]', '[3869235831084417024, 3869235831084417024]', '[3869235831084417025, 3869235833231900671]', '[3869237493236760576, 3869237493236760576]', '[3869238317870481408, 3869238317870481408]', '[3869238386589958144, 3869238386589958144]', '[3869238403769827328, 3869238403769827328]', '[3869238416654729216, 3869238416654729216]', '[3869238419875954688, 3869238419875954688]', '[3869238420681261056, 3869238420681261056]', '[3869238420815478785, 3869238420949696511]', '[3869238420949696513, 3869238423097180159]', '[3869238423097180161, 3869238425244663807]', '[3869238425244663808, 3869238425244663808]', '[3869238425244663809, 3869238427392147455]', '[3869238438129565696, 3869238438129565696]', '[3869238446719500289, 3869238455309434879]', '[3869238455309434881, 3869238457456918527]', '[3869238457456918529, 3869238459604402175]', '[3869238459604402176, 3869238459604402176]', '[3869238461751885825, 3869238463899369471]', '[3869238472489304064, 3869238472489304064]', '[3869238524028911616, 3869238524028911616]', '[3869238592748388352, 3869238592748388352]', '[3869242990794899456, 3869242990794899456]', '[3869436504841388032, 3869436504841388032]', '[3869717979818098688, 3869717979818098688]', '[3873095679538626560, 3873095679538626560]'] }, 'keysExamined' : 24627, 'dupsTested' : 0, 'dupsDropped' : 0, 'seenInvalidated' : 0} }} }, 'allPlansExecution' : [ ]},'serverInfo' : { 'host' : '{host}', 'port' : 7004, 'version' : '0.0.1-fake-', 'gitVersion' : 'nogitversion'},'ok' : 1
}
问题解答
回答1：因为 2d 索引支持球面查询本身就有很大的性能问题，我主要看第二个用 2dsphere 索引的结果。
从查询explain结果来看，工作是正常的。下图中圆圈是查询的maxDistance，方格是数据被读到的地方。图中的坐标是GPS坐标，如果数据是火星坐标，显示的地方会有偏差。
可能是在这1公里范围里，数据分布很集中，但是离初始点比较远，$nearSphere 在最初估计点密度时在周围找不到数据，就会扩大到 maxDistance，对于这一点没有特别好的办法，可能的解决方案也会让普通的查询性能下降。建议下载 MongoDB Compass，看一下 loc 的数据分布，确认这一点。或者如果能把方圆5公里的位置数据公布出来，我可以在本地复现，就能明白问题到底在哪里了。一般来说，如果查询的起始点的分布与数据分布相似，平均来看，结果不会这么极端。
最后，explain 的查询不是 limit 30，而是 limit 1，不过问题性质应该是一样的。使用的版本是你自己编译的，应该没有改2dshpere相关的代码吧。
我们这边一直是自己编译的代码。这个版本是没有修改过2d 或者 2dsphere 索引相关逻辑的。你说的没错，我们跟踪代码逻辑也是发现离查询点距离稍微远有一坨很密集的点。而步长递增的算法太狂野直接就跳到最大步长了。抽象后的数据是否能够给出，我需要申请一下，不过问题不大。mongo compass没有用过，如此神器，我学习一下。limit 1 和limit 30 的问题，可能是我在采集数据时贴的语句疏忽了。不过这两种都测过，结果差不多的。其实可以考虑开放一个模糊近似算法的接口，让用户选择放弃严格的正确性，带来性能上的成倍提升。我们这边是这么个优化的思路
