Indexing and Query Optimizer - Kevin Hanson, 10gen

MongoDB LA 2012

MongoDB supports a wide range of indexing options to enable fast querying of your data. In this talk we’ll cover how indexing works, the various indexing options, and cover use cases where each might be useful

Transcript

1. Kevin  Hanson  -­‐  kevin@10gen.com Indexing Monday, January 23, 12
2. So... What’s an Index and Why Do We Need One? Monday, January 23, 12
3. As We Insert Data, We Build a Linked List... 1 2 3 4 5 6 7 Monday, January 23, 12
4. Finding X=7 1 2 3 4 5 6 7 Monday, January 23, 12
5. 7 Documents Looked At 1 2 3 4 5 6 7 Monday, January 23, 12
6. Tree Lookup X=7 1 2 3 4 7 6 5 Only 3 Documents Checked Monday, January 23, 12
7. Query for {a: 7} {...}  {...}  {...}  {...}  {...}  {...}  {...}  {...}  {...}  {...}  {...} [-­‐∞,  5) [5,  10) [10,  ∞) [5,  7) [7,  9) [9,  10) [10,  ∞)  buckets [-­‐∞,  5)  buckets With  Index Without  index  -­‐  Table  Scan Index  on  a:  ascending Monday, January 23, 12
8. •Frequently  used  queries •Low  response  time •Sorting  on  a  Field •Avoid  full  collection  scans When We Want Indexes... Monday, January 23, 12
9. Creating Indexes in MongoDB Monday, January 23, 12
10. db.blogs.ensureIndex({author:  1}) 1  =  ascending -­‐1  =  descending An  index  on  _id  is  automatic. Indexing a Field Monday, January 23, 12
11. Compound Indexes (Multiple Fields) db.blogs.save({    author:  "James",    ts:  new  Date() }); db.blogs.ensureIndex({author:  1,  ts:  -­‐1}) Monday, January 23, 12
12. db.blogs.save({    ...    stats  :  {  views:  0,                          followers:  0  } }); db.blogs.ensureIndex({"stats.followers":  -­‐1}) db.blogs.find({"stats.followers":  {$gt:  500}}) Indexing Embedded Documents Monday, January 23, 12
13. Things to Know About Indexes... • Indexes Maintain Order Index  on  a:  ascending,  b:  descending {a:  0,  b:  9} {a:  2,  b:  0} {a:  7,  b:  1} {a:  3,  b:  7} {a:  3,  b:  2} {a:  3,  b:  5} {a:  9,  b:  1} db.blogs.ensureIndex({author:  1}) 1  =  ascending -­‐1  =  descending Monday, January 23, 12
14. Things to Know About Indexes... • Indexes Maintain Order • Indexes Will Slow Writes • Indexes Take Up Space • Index Maintenance Will Cause Writes Monday, January 23, 12
15. Things to Know About Indexes... • Indexes Maintain Order • Indexes Will Slow Writes • Indexes Take Up Space • Index Maintenance Will Cause Writes • “unique” Parameter Forces Uniqueness db.blogs.save({    ...    title:  "My  first  blog" });   db.blogs.ensureIndex({title:  1},  {unique:  true}) Monday, January 23, 12
16. More Advanced Indexes Monday, January 23, 12
17. db.blogs.save({    ...    tags:  ["mongodb",  "NoSQL"] }); db.blogs.ensureIndex({tags:  1}) db.blogs.find({tags:  "NoSQL"}) Multikeys Monday, January 23, 12
18. db.blogs.save({    ...    comments:  [          {author:  "Kevin",  editor  :  “Katy”},        {author:  "Blayze",  editor  :  “Kristen”},        {author:  "Cody",  editor  :  “Selen”}] }); db.blogs.ensureIndex({"comments.author":  1}) db.blogs.find({"comments.author":  "Kevin"}) Indexing Embedded Arrays Monday, January 23, 12
19. • Query resolved in index only • Eliminated Need to Pull Documents from DB • Need to exclude _id from items projected db.blogs.save({    ...    author:  "Kevin",    editor:  "Katy", });   db.blogs.ensureIndex({author:  1,  editor:  1}); db.blogs.find({author:  "Kevin"},                            {editor:  1,  _id:0})) Covered Indexes Monday, January 23, 12
20. • Key value included if and only if the value is present • Reduces size of index • Limited to a single field db.blogs.ensureIndex({loc:  1},  {sparse:  true}) //  loc  key  stored  for  Blayze  &  Cody  only db.blogs.save({author:  "Kevin"}) db.blogs.save({author:  "Blayze",  loc:  null}) db.blogs.save({author:  "Cody",  loc:  "AZ"}) Sparse Indexes Monday, January 23, 12
21. • Null and not-present are different db.blogs.ensureIndex({url:1},                                          {sparse:true,unique:true}); db.blogs.save({url:"www.10gen.com"})                                         //  Can  only  have  a  single  null  value db.blogs.save({url:null})                                                               db.blogs.save({url:null}) E11000  duplicate  key  error  index: test.blogs.$url_1    dup  key:  {  :  null  } //  Can  have  multiple  missing  values db.blogs.save({author:"Sally"}) db.blogs.save({author:"Rick"}) Unique Sparse Indexes Monday, January 23, 12
22. db.blogs.save({    loc:  {  long:  40.739037,  lat:  40.739037  } }); db.blogs.save({    loc:  [40.739037,  40.739037] }); db.blogs.ensureIndex({"loc":  "2d"}) Geospatial • Geo hash stored in B-Tree • First two values indexed Monday, January 23, 12
23. db.blogs.getIndexes() db.blogs.totalIndexSize() db.blogs.dropIndex({"author":  1}) Other Index Operations Monday, January 23, 12
24. Self Quiz: When Are Indexes Used? Index  on  {a:  1} db.c.find({a:  0}) db.c.find({a:  {$in:  [0,  2]}}) db.c.find({a:  {$gt:  5}}) db.c.find().sort({a:  -­‐1}) db.c.count({a:  0}) db.c.find({b:  0}).sort({a:  -­‐1}) Monday, January 23, 12
25. db.c.ensureIndex({x:1}) db.c.ensureIndex({y:-­‐1}) db.c.find({x:  10,  y:  “foo”})    scan    index  on  x    index  on  y remember terminated Picking the Best Query Plan Monday, January 23, 12
26. Query Performance Analysis & Using Indexes for Faster Queries Monday, January 23, 12
27. Using the Profiler db.setProfilingLevel(  level  )   0  ==  off   1  ==  slow  operations  (>100ms) 2  ==  all  operations Monday, January 23, 12
28. db.system.profile.find({millis:{$gt:5}}); {   "ts"  :  "Thu  Jan  29  2009  15:21:27  GMT-­‐0500   (EST)"  ,     "info"  :  "query  test.foo  ntoreturn:0   exception    bytes:53"  ,     "millis"  :  88 } Profiler Output Monday, January 23, 12
29. db.blogs.find({title:"My  blog"}).explain(); {        "cursor"  :  "BasicCursor",        "indexBounds"  :  {}        "nscanned"  :  57594,        "nscannedObjects"  :  57594,        "n"  :  3,        "millis"  :  108 } Explain - Scan all documents Monday, January 23, 12
30. {        "cursor"  :  "BtreeCursor  title_1",        "indexBounds"  :  {...},        "nscanned"  :  123,        "nscannedObjects"  :  123,        "n"  :  3,        "millis"  :  4 } db.blogs.ensureIndex({title:  1}); db.blogs.find({title:  "My  blog"}).explain(); Explain - Index used Monday, January 23, 12
31. {        "cursor"  :  "BtreeCursor  x_1",        "indexBounds"  :  {...},        "nscanned"  :  123,        "nscannedObjects"  :  123,        "n"  :  10,        "millis"  :  4,        "indexOnly"  :  true } db.blogs.ensureIndex({title:  1}); db.blogs.find({title:  "My  blog"},                            {title:  1,  _id:  0}).explain(); Explain - Covered Index used Monday, January 23, 12
32. 9 6 {_id:4,x:6} 1 2 3 4 5 6 6 {_id:5,x:6} {_id:1,x:6} db.c.find({x:  6}) Index Internals - Equality match Monday, January 23, 12
33. db.c.find({x:6}).explain() {   "cursor"  :  "BtreeCursor  x_1",   "nscanned"  :  3,   "nscannedObjects"  :  3,   "n"  :  3,   "millis"  :  1,   "nYields"  :  0,   "nChunkSkips"  :  0,   "isMultiKey"  :  false,   "indexOnly"  :  false,   "indexBounds"  :  {     "x"  :  [       [         6,         6       ]     ]   } } Equality match Monday, January 23, 12
34.   "indexBounds"  :  {     "x"  :  [       [         6,         6       ]     ]   } Equality match Exact match will have an inclusive range Monday, January 23, 12
35.   "nscanned"  :  3,   "nscannedObjects"  :  3,   "n"  :  3,   Equality match Number of matching documents is 3 Monday, January 23, 12
36. 1 2 3 4 5 6 6 6 9 Equality match db.c.find({x:  6}) Monday, January 23, 12
37. 9 1 2 3 4 5 6 6 6 {y:4,x:6} {y:5,x:6} {y:1,x:6} Full document match db.c.ensureIndex({x:  1}) db.c.find({x:  6,  y:  1}) Monday, January 23, 12
38. db.c.find({x:  6,  y:  1}).explain() {   "cursor"  :  "BtreeCursor  x_1",   "nscanned"  :  3,   "nscannedObjects"  :  3,   "n"  :  1,   "millis"  :  1,   "nYields"  :  0,   "nChunkSkips"  :  0,   "isMultiKey"  :  false,   "indexOnly"  :  false,   "indexBounds"  :  {     "x"  :  [       [         6,         6       ]     ]   } } Full document match Monday, January 23, 12
39. "indexBounds" : { "x" : [ [ 6, 6 ] ] } Full document match Exact match will have an inclusive range Monday, January 23, 12
40.   "nscanned"  :  3,   "nscannedObjects"  :  3,   "n"  :  1, Full document match Documents for all matching keys scanned, but only one document matched on non- index keys Monday, January 23, 12
41. 8 1 2 3 4 5 6 7 9 Range match db.c.ensureIndex({x:  1}) db.c.find({x:{$gte:4,  $lte:7}}) Monday, January 23, 12
42. db.c.find(  {x:{$gte:4,$lte:7}}  ).explain() {   "cursor"  :  "BtreeCursor  x_1",   "nscanned"  :  4,   "nscannedObjects"  :  4,   "n"  :  4,   "millis"  :  1,   "nYields"  :  0,   "nChunkSkips"  :  0,   "isMultiKey"  :  false,   "indexOnly"  :  false,   "indexBounds"  :  {     "x"  :  [       [         4,         7       ]     ]   } } Range match Monday, January 23, 12
43. "indexBounds"  :  {     "x"  :  [       [         4,         7       ]     ] Range match Range match will have an inclusive range Monday, January 23, 12
44.   "nscanned"  :  4,   "nscannedObjects"  :  4,   "n"  :  4, Range match Number of matching documents is 4 Monday, January 23, 12
45. 1 2 3 4 5 6 7 8 9 Range match Monday, January 23, 12
46. insert    query  update  delete  getmore  command  flushes  mapped    vsize        res  locked  %  idx  miss  %              5345            0            0            0              0              1              0    8.75g    11.1g      132m                1                    2                    230            0            0            0              0              1              0    8.75g    11.1g      132m                1                    7                1740          20            0            0              0              2              0    8.75g    11.1g      132m                3                  19            0        120            0            0              0              1              0    8.75g    11.1g      132m                0                  17            0        117            0            0              0              1              0    8.75g    11.1g      132m                0                  16            0        119            0            0              0              1              0    8.75g    11.1g      132m                0                  17       Mongostat Monday, January 23, 12
47. The 5-Second Review •  Indexes  Speed  Up  Data  Retrieval •  So  use  them! •  Indexes  Take  Up  Space  and  Slow  Writes •  So  only  use  them  when  you  need  them! •  Not  Sure? •  Use  Query  Profiling  and  Explain Monday, January 23, 12
48. @mongodb conferences,  appearances,  and  meetups http://www.10gen.com/events http://bit.ly/mongoF   Facebook                    |                  Twitter                  |                  LinkedIn http://linkd.in/joinmongo download at mongodb.org Kevin Hanson kevin@10gen.com Monday, January 23, 12