In a typical database query scenario for Mongodb, index plays a very important role. Without index, Mongodb needs to scan the entire collection to find a matching document, which is very expensive.

Mongodb index uses b-Tree, a special data structure, with which Mongodb can efficiently match the data to be queried. Take the following figure as an example (from the official) :

Score index can not only support range query efficiently, but also enable MongoDB to return sorted data efficiently.

Mongodb’s indexes are very similar to other database systems. Mongodb’s indexes are defined at the collection level and can be indexed for any single field as well as any sub-field.

The default_id index

By default, Mongodb creates a unique index based on _ID as the primary key of a document when a collection is created. This index cannot be deleted.

Mongo support a variety of ways to create indexes, see the specific way to create the official documentation at https://docs.mongodb.com/manual/indexes/#create-an-index

Single field index

Single field index is the simplest index type in Mongodb. Different from MySQL, the index of Mongodb is in order ascending or descending.

However, for single field indexes, the order of the indexes does not matter because MongoDB supports traversal of single field indexes in any order.

Create a Records collection here:

{
  "_id": ObjectId("570c04a4ad233577f97dc459"),
  "score": 1034,
  "location": { state: "NY", city: "New York" }
}
Copy the code

Then create a single field index:

db.records.createIndex( { score: 1 } )
Copy the code

The above statement creates a ascending index on the Score field of the Collection, which supports the following queries:

db.records.find( { score: 2 } )
db.records.find( { score: { $gt: 10 } } )
Copy the code

Explain of MongoDB can be used to analyze the above two queries:

db.records.find({score:2}).explain('executionStats')
Copy the code

single index on embedded field

In addition, MongoDB supports index creation for Embedded Field:

db.records.createIndex( { "location.state": 1 } )
Copy the code

The embedded Index above supports the following queries:

db.records.find( { "location.state": "CA" } )
db.records.find( { "location.city": "Albany", "location.state": "NY" } )
Copy the code

sort on single index

For single indexes, the MongoDB index itself supports sequential lookup

db.records.find().sort( { score: 1 } )
db.records.find().sort( { score: -1 } )
db.records.find({score:{$lte:100}}).sort( { score: -1 } )
Copy the code

These query statements are sufficient to use index.

Compound index

Mongodb supports indexing multiple fields, called compound Index. The order of fields in a Compound index is critical to the performance of the index. For example, the index {userid:1, Score :-1} is first sorted by userID and then sorted by score in each userID.

Create a Compound index

Create a Products Collection here:

{ "_id": ObjectId(...) , "item": "Banana", "category": ["food", "produce", "grocery"], "location": "4th Street Store", "stock": 4, "type": "cases" }Copy the code

Then create a compound index:

db.products.createIndex( { "item": 1, "stock": 1 } )
Copy the code

The document referenced by this index is sorted first by item, and then, within each item, by stock. The following statements satisfy this index:

db.products.find( { item: "Banana" } )
db.products.find( { item: "Banana", stock: { $gt: 5}})Copy the code

The condition {item: “Banana”} satisfies because the query satisfies the prefix principle.

The compound index needs to meet the prefix principle

Index prefix is a subset of the left prefix of Index fields. Consider the following indexes:

{ "item": 1, "location": 1, "stock": 1 }
Copy the code

This index contains the following index prefix:

{ item: 1 }
{ item: 1, location: 1 }
Copy the code

Compound index can be used as long as the statement satisfies the index prefix principle:

db.products.find( { item: "Banana" } )
db.products.find( { item: "Banana",location:"4th Street Store"} )
db.products.find( { item: "Banana",location:"4th Street Store",stock:4})
Copy the code

In contrast, if the index prefix does not satisfy, the index cannot be used, such as the following field query:

  • the location field
  • the stock field
  • the location and stock fields

Because of the index prefix, if a collection has both {a:1, b:1} and {a:1} indexes, the single index can be removed if there is no need for sparseness or uniqueness.

Sort on Compound index

In a single index, the sort order doesn’t matter, but in a compound index, it doesn’t.

Consider the following scenarios:

db.events.find().sort( { username: 1, date: -1 } )
Copy the code

The Events Collection has a query above, first sorting the results by username, then date descending, or the following query:

db.events.find().sort( { username: -1, date: 1 } )
Copy the code

According to item first-just sort the username, and then to ascending sort date, index:

db.events.createIndex( { "username" : 1, "date"1}) : -Copy the code

Both queries are supported, but the following queries are not:

db.events.find().sort( { username: 1, date: 1 })
Copy the code

The order of sort must be the same as the order in which the index was created. Consistent means it does not have to be the same

{ “username” : 1, “date” : -1 } { “username” : 1, “date” : 1 }
sort( { username: 1, date: -1 } ) support Does not support
sort( { username: -1, date: 1 } ) support Does not support
sort( { username: 1, date: 1 } ) Does not support support
sort( { username: -1, date: -1 } ) Does not support support

}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}

query index
db.data.find().sort( { a: 1 } ) { a: 1 }
db.data.find().sort( { a: -1 } ) { a: 1 }
db.data.find().sort( { a: 1, b: 1 } ) { a: 1, b: 1 }
db.data.find().sort( { a: -1, b: -1 } ) { a: 1, b: 1 }
db.data.find().sort( { a: 1, b: 1, c: 1 } ) { a: 1, b: 1, c: 1 }
db.data.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } ) { a: 1, b: 1 }

Non-index prefix sort

{a: 1, b: 1, C: 1, d: 1} = = a: 1, b: 1, C: 1, d: 1}

Example Index Prefix
r1 db.data.find( { a: 5 } ).sort( { b: 1, c: 1 } ) { a: 1 , b: 1, c: 1 }
r2 db.data.find( { b: 3, a: 4 } ).sort( { c: 1 } ) { a: 1, b: 1, c: 1 }
r3 db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } ) { a: 1, b: 1 }

In table R1 above, the order field is b and c, a is the index field and before b and c, the index can be used; In R3, b is a range query, but a before B also uses equivalence conditions, that is, as long as the fields before b meet equivalence conditions, the other fields can be any condition.

How do I create the correct index

The previous section has covered the main indexing knowledge needed to use MongoDB on a daily basis, but how do you get it right?

Analyze the query statement using Explain

By default, MongoDB provides statements similar to MySQL explain to analyze query statements to help us build indexes correctly. When building indexes, we need to analyze various query conditions according to Explain.

Understand the effect of field order on indexes

The real function of indexes is to help us limit the range of data to be selected. For example, Compound index, how to determine the order of multiple FeILD data, should choose the field that can maximize the scope of data to be searched, so that if the first field can quickly narrow the scope of data to be searched, The subsequent FeILD would match many fewer rows. Consider statements:

{'online_time': {'$lte': present}, 'offline_time': {'$gt': present}, 'online': 1, 'orientation': 'quality'.'id': {'$gt': max_id}}
Copy the code

Consider the following indexes

The index nscanded
r1 {start_time:1, end_time: 1, origin: 1, id: 1, orientation: 1} 12959
r2 {start_time:1, end_time: 1, origin: 1, orientation: 1, id: 1} 2700

The different order of field ID and orientation will result in a huge difference in the number of documents to be scanned, which indicates that the two have a great difference in the scope of data restriction. Priority should be given to the index order that can maximize the scope of data restriction.

Monitoring slow Query

Always analyze slow queries generated by the build environment first time to find and resolve problems early.