Mongodb $和$排序在不同的字段，并获得最后匹配的记录

Question

我收集了大约50条湖泊记录。下面是一个示例文档

{
    "_id" : NumberLong(4253223),
    "locId" : 59,
    "startIpNum" : NumberLong("3287940726"),
    "endIpNum" : NumberLong("3287940761"),
    "maxmind_location" : {
        "locId" : 59,
        "country" : "DK",
        "region" : "",
        "city" : "",
        "postalCode" : "",
        "latitude" : "56.0000",
        "longitude" : "10.0000",
        "metroCode" : "",
        "areaCode" : "n"
    }
}

下面是我要执行的查询。我想从匹配条件中找到最后一条记录。

find({
        $and: [
                 {startIpNum: { $lte: 459950297 }},
                 {endIpNum: { $gte: 459950297 }} 
              ]
      }).sort({_id : -1}).limit(1)

我在startIpNum和endIpNum上有分隔的升序索引。我已经用增量id值代替了_id，像Mysql。

当我做查询没有sort和limit 1。它在0毫秒内给出结果。一旦我把sort(我需要排序，因为我想要最后匹配的记录)查询得到永久挂起。

我也试过下面的查询，但它需要大约700毫秒。与复合
索引{startIpNum :1 , endIpNum : 1 , _id : -1 }，排序_id。

 find({
         startIpNum : { $lte: 459950297 }, 
         endIpNum : { $gte: 459950297 }
      }).sort({
                 startIpNum :1,
                 endIpNum :1 ,
                 _id : -1
              }).limit(1).explain({ verbose : true});

我如何在第一种方法中实现排序。

解释如下。它仍然在扫描370061个索引

db.maxmind.find({startIpNum : { $lte: 459950297 }, endIpNum : { $gte: 459950297 } }).sort({startIpNum :1, endIpNum :1 , _id : -1 }).limit(1).hint("startIpNum_1_endIpNum_1__id_-1").explain( { verbose: true } );
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "yogeshTest.maxmind",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "startIpNum" : {
                        "$lte" : 459950297
                    }
                },
                {
                    "endIpNum" : {
                        "$gte" : 459950297
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 0,
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "startIpNum" : 1,
                        "endIpNum" : 1,
                        "_id" : -1
                    },
                    "indexName" : "startIpNum_1_endIpNum_1__id_-1",
                    "isMultiKey" : false,
                    "direction" : "forward",
                    "indexBounds" : {
                        "startIpNum" : [
                            "[-inf.0, 459950297.0]"
                        ],
                        "endIpNum" : [
                            "[459950297.0, inf.0]"
                        ],
                        "_id" : [
                            "[MaxKey, MinKey]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 433,
        "totalKeysExamined" : 370061,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 430,
            "works" : 370062,
            "advanced" : 1,
            "needTime" : 370060,
            "needFetch" : 0,
            "saveState" : 2891,
            "restoreState" : 2891,
            "isEOF" : 1,
            "invalidates" : 0,
            "limitAmount" : 0,
            "inputStage" : {
                "stage" : "FETCH",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 420,
                "works" : 370061,
                "advanced" : 1,
                "needTime" : 370060,
                "needFetch" : 0,
                "saveState" : 2891,
                "restoreState" : 2891,
                "isEOF" : 0,
                "invalidates" : 0,
                "docsExamined" : 1,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 1,
                    "executionTimeMillisEstimate" : 410,
                    "works" : 370061,
                    "advanced" : 1,
                    "needTime" : 370060,
                    "needFetch" : 0,
                    "saveState" : 2891,
                    "restoreState" : 2891,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "keyPattern" : {
                        "startIpNum" : 1,
                        "endIpNum" : 1,
                        "_id" : -1
                    },
                    "indexName" : "startIpNum_1_endIpNum_1__id_-1",
                    "isMultiKey" : false,
                    "direction" : "forward",
                    "indexBounds" : {
                        "startIpNum" : [
                            "[-inf.0, 459950297.0]"
                        ],
                        "endIpNum" : [
                            "[459950297.0, inf.0]"
                        ],
                        "_id" : [
                            "[MaxKey, MinKey]"
                        ]
                    },
                    "keysExamined" : 370061,
                    "dupsTested" : 0,
                    "dupsDropped" : 0,
                    "seenInvalidated" : 0,
                    "matchTested" : 0
                }
            }
        },
        "allPlansExecution" : [ ]
    },
    "serverInfo" : {
        "host" : "cus360-H81M-S",
        "port" : 27017,
        "version" : "3.0.3",
        "gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105"
    },
    "ok" : 1
}

Answer 1

在发布查询的db.collection.getIndexes()和explain的输出之前，让我们尝试以下操作。我怀疑的是你的{startIpNum :1 , endIpNum : 1 , _id : -1 }不作为一个查询计划。

所以你可以尝试的是强迫MongoDB使用该索引的暗示:

find({
         startIpNum : { $lte: 459950297 }, 
         endIpNum : { $gte: 459950297 }
      }).sort({
                 startIpNum :1,
                 endIpNum :1 ,
                 _id : -1
              }).limit(1).hint({startIpNum :1 , endIpNum : 1 , _id : -1 })

目前，似乎您的查询获取所有匹配的文档，将它们加载到内存中，并在那里对它们进行排序。通过提示，使用您的索引，它将最初以正确的顺序选择文档。