This is the fifth day of my participation in the First Challenge 2022

preface

These days in the review of node part of the knowledge, as we all know, database is very important, especially the background, TODAY I take you to review a wave of monDB index and explain use. Let’s take a look.

Use of MongoDB indexes and Explain

Introduction to mongodb Indexes

An index is a structure that sorts the values of one or more columns of a database table, allowing us to query the database

More convenient. MongoDB indexes are almost identical to traditional relational databases, including some basic queries

Poll optimization techniques. Here are some examples to show some commands:

Here is the command to create the index:

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

Get the index of the current collection:

db.user.getIndexes()

The command to drop an index is:

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

In MongoDB, we can also create composite indexes, such as:

The number 1 indicates that the indexes of the username key are stored in ascending order, and -1 indicates that the indexes of the age key are stored in descending order. This is similar to a relational database. For example, in SQL, the ascending order is ASC and descending order is DESC. But the theory is the same.

db.user.ensureIndex({"username":1, "age":-1})

After the index is created, it will be used by queries based on username and age, or it will be used by queries based on username, but will not be used by queries based only on age. But mongodb also has a solution.

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 the order so that the composite index can be used for the query. Such as:

db.user.find({"age": 30, "username": "stephen"})

For the query criteria in the example above, MongoDB dynamically adjusts the order of the query criteria document before retrieving it

Order so that the query can use the compound index you just created.

For the index created above, MongoDB creates the new index based on its keyname and index direction

An index name is automatically assigned. The following command can specify an index name when the index is created, for example:

db.user.ensureIndex({“username”:1},{“name”:”userindex”})

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. So when you do index-less sort, if the amount of data is too large

If you cannot sort in memory, MongoDB will report an error.

2. Unique index

We use a lot of unique indexes.

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

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

If a document with a duplicate userID is inserted again, MongoDB will report an error prompting you to insert a duplicate key, such as

db.user.insert({"userid":5})；
db.user.insert({"userid":5}) E11000 duplicate key error index: user.user.$userID_1 DUP key: {:5.0 }
Copy the code

If the inserted document does not contain the userID key, the value of that key in the document is null, if multiple inserts are similar

MongoDB will return 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 }Copy the code

If duplicates already exist when creating a unique index, we can use the following command to help us create a unique index

When indexing, duplicate documents are eliminated and only the first document discovered is retained, for example:

Delete the unique index you just created.

Copy the code

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})

Copy the code

Db.user. ensureIndex({” userID “:1},{“unique”:true}); Such as:

db.user.ensureIndex({“userid”:1,”age”:1},{“unique”:true})

Some parameters of the index

If you are creating indexes for documents with existing data, you can run the following command to enable MongoDB to create them in the background

Build indexes so that creation does not block other operations. However, by contrast, creating an index in blocking mode will result in integer

The create process is more efficient, but MongoDB will not receive additional operations at create time.

db.user.ensureIndex({“username”:1},{“background”:true})

4. Use explain

Explain is a very useful tool to help you get a lot of useful information about your query. Just call the cursor

This way, you can get the query details. Explain returns a document, not the cursor itself. For example, explain returns statistics on indexes used by the query, time spent, and number of documents scanned.

Explain the execution of the executionStats query

time

db.tablename.find().explain( “executionStats” )

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

conclusion

Mongodb is generally easier to use than a relational database, but the gist of mongodb is to use a non-relational database to complete the relational database things, but there are a lot of details we need to explore. Or the same words, step by step, come on!