Use of MongoDB indexes and explain

Index Base

An index is a structure that sorts the values of one or more columns in a database table, allowing us to query the database more quickly. MongoDB's index is almost identical to the traditional relational database, which includes some basic query optimization techniques. index-built commands

Db.user.ensureIndex ({"username": 1})  

gets the index of the current collection

command to delete an index

Db.user.dropIndex ({"username": 1})  

In MongoDB, we can also create composite indexes, such as:
The number 1 indicates that the index of the username key is stored in ascending order, and 1 indicates that the index of the age key is stored in descending form.

Db.user.ensureIndex ({"username": 1, "Age":-1})  

After the index is created, queries based on username and age will use the index, or username-based queries will use the index, but only the age-based query would not use the composite index. So you can say that if you want to use a composite index, you must include the first N index columns in the composite index in the query criteria. However, if the order of key values in the query condition is inconsistent with the order of creation in the composite index, MongoDB can intelligently help us adjust that order so that the composite index can be used by the query. Such as:

For the query criteria in the example above, MongoDB will dynamically adjust the order of the query criteria document before retrieving it so that the query can use the composite index that was just created.
For the index created above, MongoDB automatically assigns an index name to the newly created index based on the index's KeyName and index direction, and the following command assigns an index name to the index when it is created, such as:

Db.user.ensureIndex ({"username": 1},{"name": "Userindex"})   

as the collection grows, you need to index a large number of sorts in the query. If you do not call sort on the key of the index, MongoDB needs to extract all the data into memory and sort. So when you do a no-index sort, if the amount of data is too large to sort in memory, MongoDB will error. Unique index

Indexes created by default are not unique indexes. The following example creates a unique index, such as

If you insert the UserID duplicate document again, MongoDB will make an error to prompt you to insert a duplicate key, such as:

Db.user.insert ({"userid": 5})  

E11000 Duplicate key error index:user.user. $userid _1 dup key: {: 5.0}

If the inserted document does not contain a UserID key, the value of the key in the document is null, and if a similar document is inserted more than once, MongoDB will report the same error, such as:

Db.user.insert ({"Userid1": 5}) 
Db.user.insert ({"Userid1": 5})  

E11000 Duplicate key error index:user.user. $userid _1 dup key: {: null}

If duplicate entries already exist when creating a unique index, we can help us to eliminate duplicate documents when creating a unique index, preserving only the first document found, such as:
First delete the unique index you just created

Insert test data to ensure that duplicate keys exist in the collection.

Db.user.remove ()     
Db.user.insert ({"userid": 5}) 
Db.user.insert ({"userid": 5})      

Re-create a unique index

Db.user.ensureIndex ({"userid": 1},{"unique": true})   

We can also create a composite unique index, which guarantees that the composite key value is unique. Such as:

Db.user.ensureIndex ({"userid": 1, "Age": 1},{"unique": true})     

some parameters of the index

If you are creating an index on a document that already has data, you can execute the following command so that MongoDB creates the index in the background so that it does not block other operations when it is created. However, creating an index in a blocking way can make the entire creation process more efficient, but MongoDB will not be able to receive other operations when it is created.

using explain

Explain is a very useful tool that will help you get a lot of useful information about your queries. As long as the method is called on the cursor, the query details can be obtained. Explain returns a document instead of the cursor itself. Such as:

Explain returns the statistics for the index used by the query, time-consuming, and number of scanned documents. explain executionstats query for specific execution times

Db.tablename.find (). Explain ("Executionstats")