社群 學習 工具庫 休閒
搜尋
繁体中文
MongoDB3.2.10 geo查詢過慢的問題
阿神
阿神 2017-05-24 11:31:32
0
2
913

版本

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.8
connecting 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.8
connecting 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

}

阿神
阿神

闭关修行中......

全部回覆(2)
Ty80
Ty802017-05-24 11:33:32 2樓

因為 2d 索引支援球面查詢本身就有很大的效能問題，我主要看第二個用 2dsphere 索引的結果。

從查詢explain結果來看，工作是正常的。下圖圓圈是查詢的maxDistance，方格是資料被讀到的地方。圖中的座標是GPS座標，如果資料是火星座標，顯示的地方會有偏差。

可能是在這1公里範圍裡，數據分佈很集中，但是離初始點比較遠，$nearSphere 在最初估計點密度時在周圍找不到數據，就會擴大到maxDistance，對於這一點沒有特別好的辦法，可能的解決方案也會讓普通的查詢效能下降。建議下載 MongoDB Compass，看看 loc 的資料分佈，確認這一點。或者如果能把方圓5公里的位置資料公佈出來，我可以在本地復現，就能明白問題到底在哪裡了。一般來說，如果查詢的起始點的分佈與資料分佈相似，平均來看，結果不會這麼極端。

最後，explain 的查詢不是 limit 30，而是 limit 1，不過問題性質應該是一樣的。使用的版本是你自己編譯的，應該沒有改2dshpere相關的程式碼吧。

點贊 +0
新增回覆
世界只因有你
世界只因有你2017-05-24 11:33:32 1樓

我們這邊一直是自己編譯的程式碼。這個版本是沒有修改過2d 或 2dsphere 索引相關邏輯的。
你說的沒錯，我們追蹤程式碼邏輯也是發現離查詢點距離稍微遠有一坨很密集的點。而步長遞增的演算法太狂野直接就跳到最大步長了。
抽象後的資料是否能夠給出，我需要申請一下，不過問題不大。
mongo compass沒有用過，如此神器，我學習一下。
limit 1 和limit 30 的問題，可能是我在採集資料時貼的語句疏忽了。不過這兩種都測過，結果差不多的。
其實可以考慮開放一個模糊近似演算法的接口，讓使用者選擇放棄嚴格的正確性，帶來效能上的倍增。我們這邊是這麼優化的思路

點贊 +0
新增回覆
熱門專題
更多>
熱門文章
熱門教學
更多>
相關教學
熱門推薦
最新課程
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板