mongodb 使用sort时的索引利用问题探究

在实际操作mongodb的过程中，发现将find与sort结合使用时，mongodb优先使用sort查询条件可以利用的索引，只有当sort查询条件没有索引可用时，才尝试利用find查询条件中的索引。

mongodb版本2.6.3

> db.version()
2.6.3

下面用命令行进行实际操作演示

collection中有如下记录

> db.TestOrder.find()
{ "_id" : ObjectId("56dfb5af9f436cc02505fac0"), "cust_id" : 12, "amount" : 199, "status" : "A", "create_time" : "14512345678" }
{ "_id" : ObjectId("56dfb61a9f436cc02505fac2"), "cust_id" : 121, "amount" : 1999, "status" : "A", "create_time" : "14512345679" }
{ "_id" : ObjectId("56dfb6359f436cc02505fac3"), "cust_id" : 119, "amount" : 23999, "status" : "B", "create_time" : "14512345680" }
{ "_id" : ObjectId("56dfb6419f436cc02505fac4"), "cust_id" : 1192, "amount" : 2999, "status" : "B", "create_time" : "14512345681" }
{ "_id" : ObjectId("56dfb64d9f436cc02505fac5"), "cust_id" : 11921, "amount" : 2992, "status" : "B", "create_time" : "14512345683" }
{ "_id" : ObjectId("56dfb9089f436cc02505fac8"), "cust_id" : 1000, "amount" : 399, "status" : "A", "create_time" : "14312345678" }
{ "_id" : ObjectId("56dfb9089f436cc02505fac9"), "cust_id" : 1100, "amount" : 599, "status" : "C", "create_time" : "14212345680" }

现有索引

创建了cust_id升序索引，create_time降序索引。

> db.TestOrder.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "test.TestOrder"
    },
    {
        "v" : 1,
        "key" : {
            "cust_id" : 1
        },
        "name" : "cust_id_1",
        "ns" : "test.TestOrder"
    },
    {
        "v" : 1,
        "key" : {
            "create_time" : -1
        },
        "name" : "create_time_-1",
        "ns" : "test.TestOrder"
    }
]

仅使用find查询

利用上了cust_id升序索引

> db.TestOrder.find({cust_id:{$in:[12,121,1000,1100]}}).explain()
{
    "cursor" : "BtreeCursor cust_id_1",
    "isMultiKey" : false,
    "n" : 4,
    "nscannedObjects" : 4,
    "nscanned" : 5,
    "nscannedObjectsAllPlans" : 4,
    "nscannedAllPlans" : 5,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "cust_id" : [
            [
                12,
                12
            ],
            [
                121,
                121
            ],
            [
                1000,
                1000
            ],
            [
                1100,
                1100
            ]
        ]
    },
    "server" : "Test_Node003:27020",
    "filterSet" : false,
    "stats" : {
        "type" : "FETCH",
        "works" : 5,
        "yields" : 0,
        "unyields" : 0,
        "invalidates" : 0,
        "advanced" : 4,
        "needTime" : 0,
        "needFetch" : 0,
        "isEOF" : 1,
        "alreadyHasObj" : 0,
        "forcedFetches" : 0,
        "matchTested" : 0,
        "children" : [
            {
                "type" : "IXSCAN",
                "works" : 4,
                "yields" : 0,
                "unyields" : 0,
                "invalidates" : 0,
                "advanced" : 4,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 1,
                "keyPattern" : "{ cust_id: 1.0 }",
                "boundsVerbose" : "field #0['cust_id']: [12.0, 12.0], [121.0, 121.0], [1000.0, 1000.0], [1100.0, 1100.0]",
                "isMultiKey" : 0,
                "yieldMovedCursor" : 0,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0,
                "matchTested" : 0,
                "keysExamined" : 5,
                "children" : [ ]
            }
        ]
    }
}

同时使用find、sort查询

利用上了create_time索引进行全表排序，未用上cust_id索引。

> db.TestOrder.find({cust_id:{$in:[12,121,1000,1100]}}).sort({create_time:-1}).explain()
{
    "cursor" : "BtreeCursor create_time_-1",
    "isMultiKey" : false,
    "n" : 4,
    "nscannedObjects" : 7,
    "nscanned" : 7,
    "nscannedObjectsAllPlans" : 11,
    "nscannedAllPlans" : 12,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "create_time" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    },
    "server" : "Test_Node003:27020",
    "filterSet" : false,
    "stats" : {
        "type" : "FETCH",
        "works" : 9,
        "yields" : 0,
        "unyields" : 0,
        "invalidates" : 0,
        "advanced" : 4,
        "needTime" : 3,
        "needFetch" : 0,
        "isEOF" : 1,
        "alreadyHasObj" : 0,
        "forcedFetches" : 0,
        "matchTested" : 4,
        "children" : [
            {
                "type" : "IXSCAN",
                "works" : 7,
                "yields" : 0,
                "unyields" : 0,
                "invalidates" : 0,
                "advanced" : 7,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 1,
                "keyPattern" : "{ create_time: -1.0 }",
                "boundsVerbose" : "field #0['create_time']: [MaxKey, MinKey]",
                "isMultiKey" : 0,
                "yieldMovedCursor" : 0,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0,
                "matchTested" : 0,
                "keysExamined" : 7,
                "children" : [ ]
            }
        ]
    }
}

删除sort查询条件中的索引

删除create_time降序索引

> db.TestOrder.dropIndex({create_time:-1})
{ "nIndexesWas" : 3, "ok" : 1 }

再次同时使用find、sort查询

因为sort查询无索引可用，mongodb利用上了cust_id索引。

> db.TestOrder.find({cust_id:{$in:[12,121,1000,1100]}}).sort({create_time:-1}).explain()
{
    "cursor" : "BtreeCursor cust_id_1",
    "isMultiKey" : false,
    "n" : 4,
    "nscannedObjects" : 4,
    "nscanned" : 5,
    "nscannedObjectsAllPlans" : 4,
    "nscannedAllPlans" : 5,
    "scanAndOrder" : true,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "cust_id" : [
            [
                12,
                12
            ],
            [
                121,
                121
            ],
            [
                1000,
                1000
            ],
            [
                1100,
                1100
            ]
        ]
    },
    "server" : "Test_Node003:27020",
    "filterSet" : false,
    "stats" : {
        "type" : "SORT",
        "works" : 11,
        "yields" : 0,
        "unyields" : 0,
        "invalidates" : 0,
        "advanced" : 4,
        "needTime" : 5,
        "needFetch" : 0,
        "isEOF" : 1,
        "forcedFetches" : 0,
        "memUsage" : 424,
        "memLimit" : 33554432,
        "children" : [
            {
                "type" : "KEEP_MUTATIONS",
                "works" : 5,
                "yields" : 0,
                "unyields" : 0,
                "invalidates" : 0,
                "advanced" : 4,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 1,
                "children" : [
                    {
                        "type" : "FETCH",
                        "works" : 5,
                        "yields" : 0,
                        "unyields" : 0,
                        "invalidates" : 0,
                        "advanced" : 4,
                        "needTime" : 0,
                        "needFetch" : 0,
                        "isEOF" : 1,
                        "alreadyHasObj" : 0,
                        "forcedFetches" : 0,
                        "matchTested" : 0,
                        "children" : [
                            {
                                "type" : "IXSCAN",
                                "works" : 4,
                                "yields" : 0,
                                "unyields" : 0,
                                "invalidates" : 0,
                                "advanced" : 4,
                                "needTime" : 0,
                                "needFetch" : 0,
                                "isEOF" : 1,
                                "keyPattern" : "{ cust_id: 1.0 }",
                                "boundsVerbose" : "field #0['cust_id']: [12.0, 12.0], [121.0, 121.0], [1000.0, 1000.0], [1100.0, 1100.0]",
                                "isMultiKey" : 0,
                                "yieldMovedCursor" : 0,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0,
                                "matchTested" : 0,
                                "keysExamined" : 5,
                                "children" : [ ]
                            }
                        ]
                    }
                ]
            }
        ]
    }
}