How it works. Indexes | Kirill Duborenko

Kirill Duborenko
Meetup #6

Transcript

1. How it works Indexes Kirill Duborenko Epam Systems
2. Index A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.
3. Index (Example) B • B-tree • Bitmap index C • Compressed suffix array G • Grid (spatial index) I • Incremental encoding • Database index • Indexed file I cont. • Inverted index K • K-d tree M • M-tree O • Octree P • Priority R-tree Q • Quadtree R cont. • R* tree • R+ tree • Reverse index S • Substring index U • UB-tree X • X-tree Z • Z-order curve
4. B-tree Structure
5. B-tree Insert Example 1
6. B-tree Insert Example 1 2
7. B-tree Insert Example 2 3 1
8. B-tree Insert Example 2 3 4 1
9. B-tree Insert Example 2 4 3 1 5
10. B-tree Insert Example 2 4 3 1 5 6
11. B-tree Insert Example 4 3 1 7 5 6 2
12. B-tree Insert Example 4 3 1 7 8 5 6 2
13. B-tree Insert Example 4 3 1 7 5 6 8 2 9
14. B-tree Search 4 5 3 1 7 6 8 2 9 x = 5
15. B-tree Search 4 5 1 2 9 3 <= x <= 7 3 8 6 7
16. MongoDB Indexes Api • db.people.ensureIndex( {title : 1}, {sparse : true} ) • db.stats() • db.collection.stats() • db.collection.getIndexes() • db.system.indexes.find()
17. Types of Indexes • Basic/Simple • Compound • Unique • Sparse • Index on Array field • Shard key
18. Types of Indexes (Primary Key) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } 1 2 3
19. Types of Indexes (Basic) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } null 4 5 ensureIndex( { x:1 } )
20. Types of Indexes (Compound) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } 5 6 4 5 null ensureIndex( { y:1, x:1 } )
21. Types of Indexes (Compound) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } 5 6 5 4 null ensureIndex( { y:1, x:-1 } )
22. Types of Indexes (Unique) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } 5 6 ensureIndex( { y:1, x:1 }, { unique:true } )
23. Types of Indexes (Sparse) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } 4 5 ensureIndex( { y:1, x:1 }, { sparse:true } )
24. Types of Indexes (By Array Field) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } 3 3 4 2 null 5 8
25. Types of Indexes (Shard Key) { _id: 1, x: 4, y: 5, z:[3,null] } { _id: 2, x: 5, y: 5, z: [4,8,2] } { _id: 3, x: null, y: 6, z:[5,3] } 0 .. 2 3 .. 5
26. Techniques • Covered Indexes MongoDB can return data from the index only when the query only involves keys which are present in the index. Not inspecting the actual documents can speed up responses considerably since the index is compact in size and usually fits in RAM, or is sequentially located on disk. • Sparse + Unique Index You can combine sparse with unique to produce a unique constraint that ignores documents with missing fields.
27. Geospatial Indexes db.people.ensureIndex({loc:"2d"}) • Document examples: { loc: [10, 10] } { loc: { a:10, b:10 } } • Query examples: find({loc:{$near:[9,9]}}) runCommand({getNear:...}) find({loc:{$within:{$center:[...]}}}) find({loc:{$within:{$box:[...]}}}) find({loc:{$within:{$poligon:[...]}}})
28. Geospatial Indexes (Limitations) • You may only have 1 geospatial index per collection, for now • In compound index geospatial part should be first • Sharding on a geo-key isn't recommended MongoDB can't use two-dimensional index to route queries • Operation $near is not working on sharded collection Use "getNear" command rather than $near
29. "Count" Problem • Performance "Count" operation iterates over all matched values in index to calculate its value. Sometime it can takes too much time • "Count" ignores ordering Actual count of retrieved documents can be different than result of "cont" operation: db.t.save({x:1}) db.t.save({x:1,y:2}) db.t.save({x:1,y:3}) db.t.find().sort({y:1}).toArray().length // 2 db.t.find().sort({y:1}).count() // 3
30. Indexes + Sharding Machine 1 Machine 2 Machine 3 Alabama → Arizona Colorado → Florida Arkansas → California Indiana → Kansas Idaho → Illinois Georgia → Hawaii Maryland → Michigan Kentucky → Maine Minnesota → Missouri Montana → Montana Nebraska → New Jersey Ohio → Pennsylvania New Mexico → North Dakota Rhode Island → South Dakota Tennessee → Utah Vermont → West Virgina Wisconsin → Wyoming
31. Tools • "Explain" method will display "explain plan" information about a query from the database db.coll.find({...}).explain() • Dex, the Index Bot Dex is a MongoDB performance tuning tool that compares queries to the available indexes in the queried collection(s) and generates index suggestions based on simple heuristics.
32. How it works Indexes Kirill Duborenko Epam Systems