1. Index basis

An index is a structure that sorts the values of one or more columns of a database table, making it faster to query a database. MongoDB indexes are almost identical to traditional relational databases, including some basic query optimization techniques.

  • Command to create an index 
    db.user.ensureIndex({"username": 1})Copy the code
  • Gets the index of the current collection 
    db.user.getIndexes()

Copy the code
  • The command to drop the index is 
    db.user.dropIndex({"username": 1})Copy the code

In MongoDB, we can also create composite indexes such as db.user.ensureIndex({“username”:1, “age”:-1}).

  • The number 1 indicates that the index of the username key is stored in ascending order
  • The number -1 indicates that the index of the AGE key is stored in descending order
  • After the index is created, queries based on username and age will use the index, or queries based on username will use the index, but queries based only on age will not use the composite index. Therefore, 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 the key values in the query criteria is inconsistent with the order in which the composite index is created, MongoDB can intelligently help us adjust that order so that the composite index can be used for the query. Such as:
db.user.find({"age": 30."username": "gogery"})
Copy the code

For the query criteria in the example above, MongoDB dynamically adjusts the order of the query criteria documents prior to retrieval so that the query can use the compound index just created. For the index created above, MongoDB automatically assigns an index name to the newly created index based on its keyname and index direction. You can use the following command to specify an index name during index creation, for example:

db.user.ensureIndex({"username": 1}, {"name":"userindex"})
Copy the code

As the collection grows, you need to index the large number of sorts in the query. If sort is not called on the index’s key, MongoDB needs to extract all the data into memory and sort it. Therefore, when performing indexless sorting, MongoDB will report an error if the amount of data is too large to be sorted in memory.

2. Unique index

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

db.user.ensureIndex({"userid": 1}, {"unique":true})
Copy the code

If a document with a duplicate userID is inserted again, MongoDB will report an error indicating that the duplicate key is inserted, for example:

db.user.insert({"userid": 2})
db.user.insert({"userid": 2})

Copy the code

**E11000 Duplicate key error index: user.user.$userID_1 DUP key **

If the inserted document does not contain the userID key, the value of the key in the document is null. If similar documents are inserted multiple times, MongoDB will report the same error.

If duplicates already exist when creating a unique index, we can use the following command to help us eliminate duplicates when creating a unique index, only the first document that is discovered, for example, delete the unique index that was just created.

db.user.dropIndex({"userid": 1})Copy the code

We can also create composite unique indexes by ensuring that the composite key values are unique. Such as:

db.user.ensureIndex({"userid": 1,"age": 1}, {"unique":true})
Copy the code

3. Index parameters

If you are creating an index for a document with existing data, you can run the following command to enable MongoDB to create the index in the background without blocking other operations. By contrast, creating an index in blocking mode makes the whole creation process more efficient, but MongoDB will not be able to receive other operations at creation time.

db.user.ensureIndex({"username": 1}, {"background":true})
Copy the code

4, Use explain

Explain is a very useful tool to help you get a lot of useful information about your query. You can get query details simply by calling this method on the cursor. Explain returns a document, not the cursor itself. Explain returns statistics on indexes used by the query, time spent, and number of documents scanned.

Explain executionStats queries specific execution times

db.CollectionName.find().explain( "executionStats"  )
Copy the code

Pay attention to the output of the following values: explain. ExecutionStats. ExecutionTimeMillis