Versi
Mongo3.2.10
Soalan
Pertanyaan geo pengelompokan padat mengembalikan 30 rekod terkini, yang memerlukan pengimbasan 20,000+ rekod.
Untuk set titik yang sama, cuba buat pertanyaan indeks 2d dan indeks 2dsphere, dan kecekapan pelaksanaan tidak jauh berbeza.
Laksanakan kenyataan
Persekitaran pelaksanaan pernyataan 1
indeks 2d
{
"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)
Versi cangkerang MongoDB: 3.2.8
menyambung ke: 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
}
selamat tinggal
Persekitaran pelaksanaan pernyataan 2
2dsfera indeks
{
"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)
Versi cangkerang MongoDB: 3.2.8
menyambung ke: 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
}
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Oleh kerana indeks 2d menyokong pertanyaan sfera itu sendiri, yang mempunyai masalah prestasi besar, saya terutamanya melihat hasil kedua menggunakan indeks 2dsphere.
Berdasarkan pertanyaan, terangkan hasil, kerja itu biasa. Dalam rajah di bawah, bulatan ialah maxDistance bagi pertanyaan dan segi empat sama ialah tempat data dibaca. Koordinat dalam gambar adalah koordinat GPS Jika data adalah koordinat Marikh, lokasi yang dipaparkan akan menjadi berat sebelah.
Mungkin dalam julat 1 kilometer ini, pengedaran data sangat tertumpu, tetapi ia jauh dari titik awal Apabila $nearSphere pada mulanya menganggarkan ketumpatan titik dan tidak dapat mencari data di sekelilingnya, ia akan berkembang ke maxDistance Untuk perkara ini, tiada pendekatan yang sangat baik, penyelesaian yang mungkin juga akan merendahkan prestasi pertanyaan biasa. Adalah disyorkan untuk memuat turun Kompas MongoDB dan lihat pengedaran data loc untuk mengesahkan ini. Atau jika data lokasi dalam radius 5 kilometer boleh diterbitkan, saya boleh mengeluarkannya secara tempatan dan memahami di mana masalahnya. Secara umumnya, jika pengedaran titik permulaan pertanyaan adalah serupa dengan pengedaran data, hasilnya tidak akan terlalu melampau secara purata.
Akhirnya, pertanyaan explain bukan had 30, tapi had 1, tapi sifat masalahnya sepatutnya sama. Versi yang anda gunakan telah disusun oleh anda sendiri, dan tidak sepatutnya ada perubahan pada kod berkaitan 2dshpere.
Kami sentiasa menyusun kod itu sendiri. Versi ini tidak mengubah suai logik berkaitan indeks 2d atau 2dsfera.
Anda betul, logik kod penjejakan kami juga mendapati terdapat titik tumpat jauh sedikit dari titik pertanyaan. Algoritma meningkatkan saiz langkah adalah terlalu liar dan melompat terus ke saiz langkah maksimum.
Sama ada data abstrak boleh diberikan, saya perlu memohon, tetapi ia bukan masalah besar.
Saya tidak pernah menggunakan kompas mongo sebelum ini. Saya akan mencubanya.
Masalah dengan had 1 dan had 30 mungkin disebabkan oleh kesilapan dalam kenyataan yang saya siarkan semasa mengumpul data. Walau bagaimanapun, saya telah menguji kedua-duanya dan hasilnya adalah serupa.
Malah, anda boleh mempertimbangkan untuk membuka antara muka untuk algoritma penghampiran kabur, membenarkan pengguna memilih untuk melepaskan ketepatan yang ketat, yang akan membawa kepada peningkatan eksponen dalam prestasi. Inilah idea pengoptimuman yang kami ada di sini