我有大约一百万个文档在一个集合中(随机生成的)。
示例文档:
{
"loc": {
"lat": 39.828475,
"lon": 116.273542
},
"phone": "",
"keywords": [
"big",
"small",
"biggest",
"smallest"
],
"prices": [
{
"category": "uRgpiamOVTEQ",
"list": [
{
"price": 29,
"name": "ehLYoPpntlil"
}
]
},
{
"category": "ozQNmdwpwhXPnabZ",
"list": [
{
"price": 96,
"name": "ITTaLHf"
},
{
"price": 88,
"name": "MXVgJFBgtwLYk"
}
]
},
{
"category": "EDkfKGZSou",
"list": [
{
"price": 86,
"name": "JluoCLnenOqDllaEX"
},
{
"price": 35,
"name": "HbmgMDfxCOk"
},
{
"price": 164,
"name": "BlrUD"
},
{
"price": 106,
"name": "LOUcsMDeaqVm"
},
{
"price": 14,
"name": "rDkwhN"
}
]
}
],
}
无索引搜索
> db.test1.find({"prices.list.price": { $gt: 190 } }).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 541098,
"nscannedObjects" : 1005584,
"nscanned" : 1005584,
"nscannedObjectsAllPlans" : 1005584,
"nscannedAllPlans" : 1005584,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 8115,
"nChunkSkips" : 0,
**"millis" : 13803,**
"server" : "localhost:27017",
"filterSet" : false
}
带有索引:
> db.test1.ensureIndex({"prices.list.price":1,"menu.list.name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.test1.find({"prices.list.price": { $gt: 190 } }).explain()
{
"cursor" : "BtreeCursor prices.list.price_1_prices.list.name_1",
"isMultiKey" : true,
"n" : 541098,
"nscannedObjects" : 541098,
"nscanned" : 868547,
"nscannedObjectsAllPlans" : 541098,
"nscannedAllPlans" : 868547,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 16852,
"nChunkSkips" : 0,
**"millis" : 66227,**
"indexBounds" : {
"menu.list.price" : [
[
190,
Infinity
]
],
"menu.list.name" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"server" : "localhost:27017",
"filterSet" : false
}
您有没有想法为什么建立索引后的搜索比未建立索引的慢?
此外,我将使用:
db.test1.find( { loc : { $near : [39.876045, 32.862245]}}) (需要2d索引)
db.test1.find({ keywords:{$in: [ "small", "another" ] }}) (对于关键字使用索引)
db.test1.find({"prices.list.name":/.s./ }) (不需要建立索引,因为我将使用正则表达式)