mongodb 2.6.3 использует неправильный индекс

У меня есть запрос, как показано ниже. Очевидно, что лучший индекс — «platform

db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);
keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);
weiboAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);
postTime_1», но mongodb выбирает «weiboAccountID_1», я что-то не так делаю?


db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);



{
    "cursor" : "BtreeCursor weiboAccountID_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 24,
    "nscannedAllPlans" : 634425,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 1,
    "nChunkSkips" : 0,
    "millis" : 2912,
    "indexBounds" : {
        "weiboAccountID" : [
            [
                "2271338624",
                "2271338624"
            ]
        ]
    },
    "allPlans" : [
        {
            "cursor" : "BtreeCursor weiboAccountID_1",
            "isMultiKey" : false,
            "n" : 1,
            "nscannedObjects" : 1,
            "nscanned" : 1,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "weiboAccountID" : [
                    [
                        "2271338624",
                        "2271338624"
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

dealed
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

operatorID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

weiboAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

postTIme_-1",
            "isMultiKey" : false,
            "n" : 1,
            "nscannedObjects" : 1,
            "nscanned" : 62,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "operatorID" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "weiboAccountID" : [
                    [
                        "2271338624",
                        "2271338624"
                    ]
                ],
                "postTIme" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

weiboAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

postTime_-1",
            "isMultiKey" : false,
            "n" : 1,
            "nscannedObjects" : 1,
            "nscanned" : 1,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "weiboAccountID" : [
                    [
                        "2271338624",
                        "2271338624"
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform_keyuserAccountID_dealed_postTime_weiboAccountID",
            "isMultiKey" : false,
            "n" : 1,
            "nscannedObjects" : 1,
            "nscanned" : 634331,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ],
                "weiboAccountID" : [
                    [
                        "2271338624",
                        "2271338624"
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

mid_1",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "mid" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

dealerID_1",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealerID" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor distinctAccountAgg",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "operatorID" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor fansCountUndealed",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "fromType" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "fansCount" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ],
                "usePicture" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor retweetCountUndealed",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "fromType" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "sourceRetweetCount" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ],
                "usePicture" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor interactUndealed",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "fromType" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "userInteractionCount" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ],
                "usePicture" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

dealed
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

dealTime_-1",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "dealTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

dealerID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

postTime_-1",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealerID" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor platform
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

keyuserAccountID
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);

postTime_-1",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "platform" : [
                    [
                        "sina",
                        "sina"
                    ]
                ],
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "postTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor keyuserAccountID_dealed_createTime_-1",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 2,
            "nscanned" : 3,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "keyuserAccountID" : [
                    [
                        "1665337047",
                        "1665337047"
                    ]
                ],
                "dealed" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "createTime" : [
                    [
                        {
                            "$maxElement" : 1
                        },
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        }

mongodb-indexes
person kulama    schedule 22.08.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я не думаю, что другой индекс «лучше». Кажется, что оба индекса (тот, который вы предлагаете, и тот, который использует mongodb) возвращают запрошенный объект с «nscanned:1», поэтому они одинаково идеально подходят для этого запроса. Я говорю, что, поскольку «weiboAccountID» в вашем тестовом наборе данных определяет именно ту запись, которую вы ищете, в этом случае нет необходимости в других индексах — mongodb выберет первое подходящее. Попробуйте добавить больше записей с точно таким же идентификатором weiboAccountID (если дублирующиеся идентификаторы вообще допустимы в вашем наборе данных) и повторно запустите запрос, посмотрите, как это изменит выполнение.

person Levente Dobson    schedule 13.02.2015