Mongodb:$ HINT的性能影响 [英] Mongodb: Performance impact of $HINT
问题描述
我有一个使用复合索引和_id排序的查询。复合索引在索引末尾有_id,它可以正常工作,直到我在查询中添加 $ gt
子句。
ie,
初始查询
db.colletion.find({field1:blabla, field2:blabla})。sort({_ id:1}
后续查询
db.colletion.find({field1:blabla,field2:blabla,_ id:{$ gt:ObjetId('...')} })。排序({_ id:1}
我注意到的是有时我的复合索引没有被使用。 ,Mongo使用默认值
BtreeCursor _id _
<为了避免这种情况,我在光标上添加了一个HINT。我想知道是否会有任何性能影响?因为该集合已经有索引但是Mongo决定使用不同的索引来提供我的查询。
我注意到的一件事是当我使用提示时
:QueryOptimizerCursor,
n:1,
nscannedObjects:2,
nscanned:2,
nscannedObjectsAllPlans:2,
nscannedAllPlans:2,
scanAndOrder:false,
nYields:0,
nChunkSkips:0,
millis:0,
服务器:aaa-VirtualBox:27017,
filterSet:false
所用时间更快>毫秒
比没有提示时提供相同的查询
cursor:BtreeCursor _id_,
isMultiKey:false ,
n:1,
nscannedObjects:1,
nscanned:1,
nscannedObjectsAllPlans:3,
nscannedAllPlans:3 ,
scanAndOrder:false,
indexOnly:false,
nYields:0,
nChunkSkips:0,
millis:3 ,
使用我正在忽略的HINT是否需要权衡利弊?大型集合的性能是否相同?
是否可以指定您创建的复合索引。我的名声不多,所以我不能在评论中提出这个问题。
但我确实对你的问题有所了解。
Mongo使用名为Equality-Sort-Range的属性,其行为方式不同。考虑下面的情况 -
你的文件很少有字段{name:string,pin:六位数,SSN:九位数}你有两个索引 - {name:1,pin:1,ssn:1 }和第二个索引是{name:1,ssn:1,pin:1}现在考虑以下查询:
-
db.test.find({name:XYZ,pin:123456})。sort({ssn:1})
此查询将使用第一个索引,因为我们在继续中有复合索引。名称,pin,ssn是延续的。 -
db.test.find({name:XYZ,pin:{$ gt:123456}} ).sort({ssn:1})
您将期望在此查询中使用第一个索引。但令人惊讶的是,此查询将使用秒索引,因为它在引脚上有一个范围操作。
Equality-sort-range属性表示查询规划器将使用服务字段上的索引 - equality-sort-range更好。第二个查询在引脚上有范围,因此将使用第二个索引,而第一个查询在所有字段上都相等,因此将使用第一个索引。
I have a query that uses compound index with sort on "_id". The compound index has "_id" at the end of the index and it works fine until I add a $gt
clause to my query.
i.e, Initial query
db.colletion.find({"field1": "blabla", "field2":"blabla"}).sort({_id:1}
Subsequent queries
db.colletion.find({"field1": "blabla", "field2":"blabla", _id:{$gt:ObjetId('...')}}).sort({_id:1}
what I am noticing is that there are times when my compound index is not used. Instead, Mongo uses the default
"BtreeCursor _id_"
To avoid this, I have added a HINT to the cursor. I'd like to know if there is going to be any performance impact? since the collection already had the index but Mongo decided to use a different index to serve my query.
One thing I noticed is that when I use the hint
"cursor" : "QueryOptimizerCursor",
"n" : 1,
"nscannedObjects" : 2,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"server" : "aaa-VirtualBox:27017",
"filterSet" : false
time taken is faster > millis
than when it serves the same query without hint
"cursor" : "BtreeCursor _id_",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 3,
"nscannedAllPlans" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 3,
Is there a trade off of using HINT which I am overlooking? Will this performance be the same on a large collection?
Can you please specify the compound index you have created. I don't have much reputation so i couldn't ask this in comment. But i do have a possible anwer to your question. Mongo uses a property called "Equality-Sort-Range" which behaves in a different manner. Consider below situation- You have few documents with fields {name : string, pin : six digit number, SSN : nine digit number} and you have two indices as - {name: 1, pin: 1, ssn: 1} and second index is {name: 1, ssn :1, pin :1} now consider below queries:
db.test.find({name: "XYZ", pin: 123456"}).sort({ssn: 1})
This query will use the first index because we have compound index in continuation. Name, pin, ssn are in continuation.db.test.find({name: "XYZ", pin: {$gt :123456"}}).sort({ssn: 1})
You will expect that first index will be used in this query. But surprisingly seconds index will be used by this query because it has a range operation on pin.
Equality-sort-range property says that query planner will use the index on field which serve - "equality-sort-range" better. Second query has range on pin so second index will be used while first query has equality on all fields so first index will be used.
这篇关于Mongodb:$ HINT的性能影响的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!