我有一个查询：
{"$query"=>{"user_id"=>"512f7960534dcda22b000491", "$or"=>[{"when_tz"=>{"$gte"=>2010-06-24 04:00:00 UTC, "$lt"=>2010-06-25 04:00:00 UTC}}, {"when_tz"=>{"$gte"=>2011-06-24 04:00:00 UTC, "$lt"=>2011-06-25 04:00:00 UTC}}, {"when_tz"=>{"$gte"=>2012-06-24 04:00:00 UTC, "$lt"=>2012-06-25 04:00:00 UTC}}], "_type"=>{"$in"=>["FacebookImageItem", "FoursquareImageItem", "InstagramItem", "TwitterImageItem", "Image"]}}, "$explain"=>true, "$orderby"=>{"when_tz"=>1}}
还有一个索引：
{ user_id: 1, _type: 1, when_tz: 1 }
解释：
{
"cursor": "BtreeCursor user_id_1__type_1_facebook_id_1 multi",
"isMultiKey": false,
"n": 28,
"nscannedObjects": 15094,
"nscanned": 15098,
"nscannedObjectsAllPlans": 181246,
"nscannedAllPlans": 241553,
"scanAndOrder": true,
"indexOnly": false,
"nYields": 12,
"nChunkSkips": 0,
"millis": 2869,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"facebook_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
},
"allPlans": [
{
"cursor": "BtreeCursor user_id_1__type_1_facebook_id_1 multi",
"n": 28,
"nscannedObjects": 15094,
"nscanned": 15098,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"facebook_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1__type_1_twitter_id_1 multi",
"n": 28,
"nscannedObjects": 15094,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"twitter_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1__type_1_instagram_id_1 multi",
"n": 28,
"nscannedObjects": 15094,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"instagram_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1__type_1_foursquare_id_1 multi",
"n": 28,
"nscannedObjects": 15094,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"foursquare_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_phash_1",
"n": 21,
"nscannedObjects": 15097,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"phash": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_aperature_1_shutter_speed_1_when_tz_1",
"n": 25,
"nscannedObjects": 35,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"aperature": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
],
"shutter_speed": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
],
"when_tz": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_image_hash_1",
"n": 22,
"nscannedObjects": 15097,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"image_hash": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_time_zone_guessed_1_when_tz_-1",
"n": 23,
"nscannedObjects": 32,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"time_zone_guessed": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
],
"when_tz": [
[
{
"$maxElement": 1
},
{
"$minElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_time_zone_guessed_1_when_tz_1",
"n": 24,
"nscannedObjects": 33,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"time_zone_guessed": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
],
"when_tz": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_time_zone_guessed_1_when_utc_-1",
"n": 23,
"nscannedObjects": 15097,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"time_zone_guessed": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
],
"when_utc": [
[
{
"$maxElement": 1
},
{
"$minElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_time_zone_guessed_1_when_utc_1",
"n": 24,
"nscannedObjects": 15097,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"time_zone_guessed": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
],
"when_utc": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1_original_shared_item_id_1",
"n": 24,
"nscannedObjects": 15097,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"original_shared_item_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1__type_1_s3_tmp_file_1 multi",
"n": 28,
"nscannedObjects": 15094,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"s3_tmp_file": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1__type_1_processed_-1_uploaded_-1_image_device_1 multi",
"n": 28,
"nscannedObjects": 15094,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"processed": [
[
{
"$maxElement": 1
},
{
"$minElement": 1
}
]
],
"uploaded": [
[
{
"$maxElement": 1
},
{
"$minElement": 1
}
]
],
"image_device": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BtreeCursor user_id_1__type_1_when_tz_1 multi",
"n": 28,
"nscannedObjects": 28,
"nscanned": 15097,
"indexBounds": {
"user_id": [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type": [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"when_tz": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
}
},
{
"cursor": "BasicCursor",
"n": 0,
"nscannedObjects": 15097,
"nscanned": 15097,
"indexBounds": {}
}
],
"server": ""
}
知道如何让它达到指数吗？
MongoDB的文档中有一条关于索引的注释：
- 对于带有$或运算符的查询，$或查询的每个子句都是并行执行的，并且每个子句都可以使用不同的索引
- 对于使用sort（）方法并使用$or运算符的查询查询不能对$或字段使用索引
请尝试用其他表单重写查询。我想知道您查询的$or背后的要求是什么。
您可以尝试使用$hint强制使用索引。将它与$explain结合起来，看看它是否适合你。
我在控制台中运行了您的查询，并创建了一个包含3个字段的索引。使用hint和explain给我提供了以下输出，这告诉我索引在执行查询的三次中都被使用过（每$一次，或者因为这些查询是并行运行的）查询：
db.stack.find(
{"_id":"512f7960534dcda22b000491",
"_type":{"$in":["FacebookImageItem", "FoursquareImageItem", "InstagramItem", "TwitterImageItem", "Image"]},
"$or":
[
{"when_tz":{"$gte":ISODate("2010-06-24T04:00:00.000Z"), "$lt":ISODate("2010-06-25T04:00:00.000Z")}},
{"when_tz":{"$gte":ISODate("2011-06-24T04:00:00.000Z"), "$lt":ISODate("2011-06-25T04:00:00.000Z")}},
{"when_tz":{"$gte":ISODate("2012-06-24T04:00:00.000Z"), "$lt":ISODate("2012-06-25T04:00:00.000Z")}}
]
}
).hint("_id_1__type_1_when_tz_1").explain()
{
"clauses" : [
{
"cursor" : "BtreeCursor _id_1__type_1_when_tz_1 multi",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"_id" : [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type" : [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"when_tz" : [
[
ISODate("2010-06-24T04:00:00Z"),
ISODate("2010-06-25T04:00:00Z")
]
]
}
},
{
"cursor" : "BtreeCursor _id_1__type_1_when_tz_1 multi",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 1,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"_id" : [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type" : [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"when_tz" : [
[
ISODate("2011-06-24T04:00:00Z"),
ISODate("2011-06-25T04:00:00Z")
]
]
}
},
{
"cursor" : "BtreeCursor _id_1__type_1_when_tz_1 multi",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 16,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"_id" : [
[
"512f7960534dcda22b000491",
"512f7960534dcda22b000491"
]
],
"_type" : [
[
"FacebookImageItem",
"FacebookImageItem"
],
[
"FoursquareImageItem",
"FoursquareImageItem"
],
[
"Image",
"Image"
],
[
"InstagramItem",
"InstagramItem"
],
[
"TwitterImageItem",
"TwitterImageItem"
]
],
"when_tz" : [
[
ISODate("2012-06-24T04:00:00Z"),
ISODate("2012-06-25T04:00:00Z")
]
]
}
}
],
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 16
}