MongoDb Compound Indexes: Performance Based on Order of Keys
The MongoDb documentation provides an example of inventory items to explain why the order of the keys is significant to performance.
It provides a sample data set:
{ "_id" : 1, "item" : "f1", type: "food", quantity: 500 }
{ "_id" : 2, "item" : "f2", type: "food", quantity: 100 }
{ "_id" : 3, "item" : "p1", type: "paper", quantity: 200 }
{ "_id" : 4, "item" : "p2", type: "paper", quantity: 150 }
{ "_id" : 5, "item" : "f3", type: "food", quantity: 300 }
{ "_id" : 6, "item" : "t1", type: "toys", quantity: 500 }
{ "_id" : 7, "item" : "a1", type: "apparel", quantity: 250 }
{ "_id" : 8, "item" : "a2", type: "apparel", quantity: 400 }
{ "_id" : 9, "item" : "t2", type: "toys", quantity: 50 }
{ "_id" : 10, "item" : "f4", type: "food", quantity: 75 }
It compares the performance of 2 similar compound indexes. Notice the order of the keys is reversed to make the point:
db.inventory.createIndex( { quantity: 1, type: 1 } )
db.inventory.createIndex( { type: 1, quantity: 1 } )
It performs the query using the 1st index:
db.inventory.find(
{ quantity: { $gte: 100, $lte: 300 }, type: "food" }
).hint({ quantity: 1, type: 1 }).explain("executionStats")
For the 1st index, the documentation states:
"totalKeysExamined" : 5,
"totalDocsExamined" : 2,
Then, the query for the 2nd index:
db.inventory.find(
{ quantity: { $gte: 100, $lte: 300 }, type: "food" }
).hint({ type: 1, quantity: 1 }).explain("executionStats")
It shows the result:
"totalKeysExamined" : 2,
"totalDocsExamined" : 2,
Finally, it concludes:
For this example query, the compound index { type: 1, quantity: 1 } is more efficient than the compound index { quantity: 1, type: 1 }.
I can see how order can affect performance if the data had many food items and few items satisfying the quantity range, or vice versa . However, I think this is different from the point that is being made in the documentation.
There are 5 items which satisfy the quantity requirement. There are 4 which satisfy the type requirement. I do not understand how the 2nd index results in totalKeysExamined of 2 vs. 5 in the 1st index. I see 4 food items. So, my gut is telling me it's 4 vs. 5.
Hopefully, some additional explanation can clear up my confusion. I want to fully understand the significance of compound index key order.
Thanks.
EDIT:
I see it, now. It helps to visualize the 2 lists under the different scenarios.
Here is the list by type, then quantity:
{ "_id" : 7, "item" : "a1", type: "apparel", quantity: 250 }
{ "_id" : 8, "item" : "a2", type: "apparel", quantity: 400 }
{ "_id" : 10, "item" : "f4", type: "food", quantity: 75 }
{ "_id" : 2, "item" : "f2", type: "food", quantity: 100 }
{ "_id" : 5, "item" : "f3", type: "food", quantity: 300 }
{ "_id" : 1, "item" : "f1", type: "food", quantity: 500 }
{ "_id" : 3, "item" : "p1", type: "paper", quantity: 200 }
{ "_id" : 4, "item" : "p2", type: "paper", quantity: 150 }
{ "_id" : 9, "item" : "t2", type: "toys", quantity: 50 }
{ "_id" : 6, "item" : "t1", type: "toys", quantity: 500 }
Here is the list by quantity, then type:
{ "_id" : 1, "item" : "f1", type: "food", quantity: 500 }
{ "_id" : 6, "item" : "t1", type: "toys", quantity: 500 }
{ "_id" : 8, "item" : "a2", type: "apparel", quantity: 400 }
{ "_id" : 5, "item" : "f3", type: "food", quantity: 300 }
{ "_id" : 7, "item" : "a1", type: "apparel", quantity: 250 }
{ "_id" : 3, "item" : "p1", type: "paper", quantity: 200 }
{ "_id" : 4, "item" : "p2", type: "paper", quantity: 150 }
{ "_id" : 2, "item" : "f2", type: "food", quantity: 100 }
{ "_id" : 10, "item" : "f4", type: "food", quantity: 75 }
{ "_id" : 9, "item" : "t2", type: "toys", quantity: 50 }
Going on Veeram's comments, it seems like with the former index, we need only hit the index once to find a food with the requested quantity. Once we land, we just iterate in order. However, if we have to look first for quantity, we will have a number of different types mixed within, and so we have to skip over the other types to get only the foods.
链接地址: http://www.djcxy.com/p/57922.html上一篇: C＃锁定，属性和权限