Directing index

In typical Mongodb database query scenarios, index plays a very important role. If there is no index, Mongodb needs to scan the entire collection to find a matching document, which is very expensive.

Mongodb index uses b-tree this special data structure, with the help of the index Mongodb can efficiently match the data to be queried, as shown in the following figure (from the official) :

Score index not only supports range query efficiently, but also enables MongoDB to return sorted data efficiently.

Mongodb indexes are similar to other database systems. Mongodb indexes are defined at the collection level and can be indexed to any single field or any sub-field.

The default_id index

When a collection is created, Mongodb creates a default unique index based on _id as the primary key of document, which cannot be deleted.

Mongo support a variety of ways to create indexes, see the specific way to create the official documentation docs.mongodb.com/manual/inde…

Single field index

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

For a single field index, however, the order of the index doesn’t matter, because MongoDB supports traversing a single field index in any order.

Create a Records collection here:

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

Then create a single field index:

Db.records.createindex ({score: 1}) copies the codeCopy the code

The above statement creates a ascending index on the score field of the collection, which supports the following query:

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

The above two queries can be analyzed using MongoDB’s Explain:

Db.records.find ({score:2}).explain('executionStats') copies the codeCopy the code

single index on embedded field

MongoDB also supports index creation for embedded fields:

Db.records.createindex ({"location.state": 1}) copies the codeCopy 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"}) copies the codeCopy the code

sort on single index

For single index, since MongoDB index itself supports sequential lookup, so for single index

db.records.find().sort( { score: 1 } ) db.records.find().sort( { score: 1}) db. Records. The find ({score: {$lte: 100}}), sort (} {score: - 1) duplicate codeCopy the code

All of these queries are indexed.

Compound index

Mongodb supports indexing multiple fields, called compound Indexes. The order of fields in a Compound Index has a critical impact on index performance, such as indexes {userID :1, score:-1} sorting first by userID and then by score within 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"} copies the codeCopy the code

Then create a compound Index:

Db.products.createindex ({"item": 1, "stock": 1}) copies the codeCopy the code

The documents referenced by index are sorted first by item, and then, within each item, by stock. The following statements satisfy the index:

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

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

Using a compound index needs to meet the prefix principle

Index prefix refers to the left prefix subset of Index fields. Consider the following indexes:

{"item": 1, "location": 1, "stock": 1} Copies the codeCopy the code

This index contains the following index prefix:

{item: 1} {item: 1, location: 1} copies the codeCopy the code

So you can use compound Index statements that satisfy 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 codeCopy the code

If the index prefix is not specified, the index cannot be used, as in the following field query:

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

Due to the index prefix, if a collection has both {a:1, b:1} and {a:1} indexes, the single index can be removed if the two indexes are not sparse or unique.

Sort on Compound index

As mentioned earlier, the sort order of single indexes doesn’t matter, but compound indexes are completely different.

Consider the following scenarios:

Db.events.find ().sort({username: 1, date: -1}) copies the codeCopy the code

The events collection has a descending order for the result (username) and a descending order for the result (date descending).

Db.events.find ().sort({username: -1, date: 1}) copies the codeCopy the code

Order by descending (select descending from user);

Db.events.createindex ({"username" : 1, "date" : -1}) replicates the codeCopy the code

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

Db.events.find ().sort({username: 1, date: 1}) copies the codeCopy the code

That is, the order of sort must be the same as that in which the index was created

The sort order must be the same as that of the index, or even if reversed. The following table clearly lists the types of query statements that compound Index satisfies:

Sort of non-index prefix

Consider the index {a: 1, b: 1, C: 1, d: 1}, even if the sorted field does not meet the index prefix, it is ok, but the precondition is that the index field before the sorted field must be equivalent condition.

Table R1 above has a sort field b and c, a is an index field and can be used before b and C; In the sorting of R3, B is the range query, but A before B also uses the equivalence condition, that is, as long as the field before the sorting of fields meets the equivalence condition, the other fields can be any condition.

How to build the correct index

The previous article basically covered the main index knowledge needed for daily use of MongoDB, but how to establish the correct index?

Analyze query statements using Explain

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

Understand the effect of field order on indexes

The real function of the index is to help us limit the selection of data, such as Compound Index how to determine the order of multiple feild, should be the first field that can maximize the narrowing of the search range of data, so that if the first field can quickly narrow the search range of data, Then subsequent feild matches will have fewer rows. Consider statements:

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

Consider the following indexes

The difference in the order of field ID and orientation will lead to a huge difference in the number of documents to be scanned, indicating that the two pairs have a great difference in the restricted scope of data. Therefore, priority should be given to the index order that can maximize the limited scope of data.

Monitoring Slow Query

Always analyze the slow queries generated by the generation environment in the first time to find and solve problems in advance.

Wecatch: Juejin. Im/Post / 5ad1D2… The copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please indicate the source.