Indexing with MongoDB | Kirill Duborenko

Kirill Duborenko
Meetup #3

1. Indexing with MongoDB Kirill Duborenko EPAM Systems
2. Indexing with MongoDB • Indexes overview • Integrated queries profiling tool (explain) • Indexes in examples
3. Type of indexes • Simple index (B-Tree) • Primary key (_id index) • Indexes on embedded fields • Compound indexes • Indexes on array fields
4. Indexes modifiers • Sparse {sparse: true} • Unique {unique: true} • Drop duplicates {unique: true, dropDups: true} • Background {background:true}
5. Index building in replica • Primary node In background, for backgound indexes • Secondary node In v2.1.0 – in background, for backgound indexes In v2.0.x and earlier – always in foreground • Recovering node Always in foreground
6. Indexing with MongoDB • Indexes overview • Integrated queries profiling tool (explain) • Indexes in examples
7. Overview > db.coll.find(query).explain() { "cursor" : "BasicCursor", "nscanned" : 1000, "nscannedObjects" : 1000, "n" : 145, "millis" : 0, … }
8. Explain result (part 1) • “cursor” - type of cursor (options: “BasicCursor” and “BtreeCursor”) • “nscanned” - count of scanned items (for BasicCursor – documents, for BtreeCursor – index items) • “nscannedObjects” - number of documments scanned • “n” - count of matched documents
9. Explain result (part 2) • “nYields” - count of interrupts during query execution • “millis” - time of query execution • “scanAndOrder” - true, in case if in-memory sorting required • “indexOnly” - covered index is used • “isMultiKey” - index over array is used • “clauses” - used for iteration over more than one index
10. Index Bounds { "cursor" : "BtreeCursor a1_1 multi", ..., "indexBounds" : { "a1" : [ [7,8], [13,20] ] } }
11. Indexing with MongoDB • Indexes overview • Integrated queries profiling tool (explain) • Indexes in examples
12. Iterating over two indexes db.coll.find({ $or:[ { v1:{ $gte:10, $lte:39 } }, { v3:{ $gte:35, $lte:49 } } ] }).explain() { "clauses" : [ { "cursor" : "BtreeCursor v1_1", "nscanned" : 30, "n" : 30, ... }, { "cursor" : "BtreeCursor v3_1", "nscanned" : 15, "n" : 10, ... } ] "nscanned" : 45, "nscannedObjects" : 45, "n" : 40, "millis" : 0 }
13. $or operator and sorting db.coll.find({ $or:[ { v1:{ $gte:10, $lte:39 } }, { v3:{ $gte:35, $lte:49 } } ] }).sort({v2: 1}) .explain() { "cursor" : "BasicCursor", "nscanned" : 100, "nscannedObjects" : 100, "n" : 40, "scanAndOrder" : true, "millis" : 0, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { } }
14. Compound indexes (Part 1) • Query: db.coll.find( {v1:8,v2:8}).sort({v3:1}) • Index: db.coll.ensureIndex( {v1:1, v2:1, v3:1}) • Result: Index-defined order
15. Compound indexes (Part 2) • Query: db.coll.find( {v1:{$in:[8, 4]},v2:8}) .sort({v3:1}) • Index: db.coll.ensureIndex( {v1:1, v2:1, v3:1}) • Result: In-memory sorting
16. Compound indexes (Part 3) • Query: db.coll.find( {v1:{$in:[8, 4]},v2:8}) .sort({v3:1}) • Index: db.coll.ensureIndex( {v2:1, v3:1}) • Result: Index-defined order
17. Full-Text Search • Document representation: { "v1" : "First string", "v2" : "Second string", "v3" : [{ "v4" : "String in nested list item" }], "_w" : ["first", "string", "second", "nested", "list", "item"] } • Query representation: {"_w" : { $all : ["nested", "item"] }}
18. Unique constraints (Part 1) • Goals: – Unlimited count of unique constraints in one collection – Ability to define scope of uniqueness • Solution: – Sparse and Unique index – List of unique tokens: _unique: [ "<scope1>$<value1>", "<scope2>$<value2>" ]
19. Unique constraints (Part 2) Example: { "t" : "t1", "v1" : "tAg", "_u" : [ "t1$tag" ] }
20. Resources • Example scripts: https://github.com/kircul/mongodb- indexes-examples • MongoDB: http://www.mongodb.org/ • Indexes in MongoDB (Official documentation): http://www.mongodb.org/display/DOCS/Indexes • Explain operation (Official documentation): http://www.mongodb.org/display/DOCS/Explain
21. Indexing with MongoDB Kirill Duborenko EPAM Systems