Mongodb Guide (translation) (23)-developer zone-index (7) index optimization suggestions

First, the indexes in mongodb are similar to those in MySQL. Therefore, many techniques for creating efficient indexes in MySQL are also applicable to mongodb.

Furthermore, it may be even more important that the index optimization recommendations can only be reached. For your application, the best index depends on several important factors, including the expected Query type, read/write frequency, and even the remaining memory of the system. This means that the best strategy is to prepare multiple index configuration schemes for the dataset and then observe the best performance.

Index Policy

Here are some basic principles for creating good indexes.

Create an index for matching queries

If you only query one keyword, you can use a single-key index. For example, you may be searching for the slow loop (slug) published by the blog ):

db.posts.find({ slug : 'state-of-mongodb-2010' })

In this scenario, the unique index based on a single keyword is the best:

db.posts.ensureIndex({ slug: 1 }, {unique: true});

However, it is common to query and sort multiple keywords. In these cases, composite indexes are the most suitable. Here is an example to query the comments that contain the recently added "mongodb" tag:

db.comments.find({ tags : 'mongodb'}).sort({ created_at : -1 });

The appropriate indexes are:

db.comments.ensureIndex({tags : 1, created_at : -1});

Note that if we want to sort "created_at" in ascending order, the efficiency of this index will be very low.

One query for one index

Sometimes we think that multiple indexes will be used for queries with multiple keywords; this is not the case in Mongodb. If your query targets multiple keywords and you want to improve the query efficiency, it is necessary to create a composite index.

Make sure that your index can reside in the memory

Shell provides a command to query the index size of a set:

db.comments.totalIndexSize();
65443

Pay attention to inefficient single-key indexes

Assume that you have a field named "status" and its value is "new" or "processeed ". If you create an index for it, the efficiency will be very low, which means that the index is not helpful in locating records and may occupy a lot of space.
A better method, of course, relies on your query to create a composite index containing inefficient fields. For example, you can create a composite index for "status" and "created_at.

Another option also depends on your use case. You can use a separate set to create a set for each State. With so many suggestions, experiments and benchmarking can help you select the best one.

Use explain

MongoDB has an explain command to check how your query is executed, especially whether an index is used.
You can use explain in the driver or in the shell:

db.comments.find({ tags : 'mongodb'}).sort({ created_at : -1 }).explain();

This will return a lot of useful information, including the number of scanned objects, the query time (unit: milliseconds), the index used, and the final index used.

If you have never used explain, it's time.

Understand explain output

There are three main fields output by the explain command:

• Cursor: the value of cursor can be BasicCursor or BtreeCursor. The second value indicates the index used.
• Nscanned: Number of scanned documents.
• N: query the number of returned documents. You need to make n very close to nscanned. To avoid this problem, you can query and scan all the documents in the set. In this case, nscanned equals to the number of documents in the collection.
• Millis: time-consuming query.

Follow the read/write ratio in your application

It is important because when you add an index, you will be burdened with all insert, update, and delete operations. If your application is busy reading, such as most web applications, the added index is usually a good thing. However, if your application is busy with writing, you should be very careful when adding indexes, because each index will increase the burden on write operations.

Generally, do not be afraid to add indexes. The index should usually be added to complete your query. Remember, there is always a good reason to add a new index and make sure you have compared the alternative policy.

Index features

These examples assume a composite index based on three fields: A, B, and C. To create an index:

db.foo.ensureIndex({a: 1, b: 1, c: 1})

Here are some suggestions for using this index:

1. the sorting column must be the last index Column Used

Good:

• Find (a = 1). sort ()
• Find (a = 1). sort (B)
• Find (a = 1, B = 2). sort (c)

Bad:

• Find (a = 1). sort (c)
• Although c is the last column of the index, a is the last column used, so you can only sort a or B.

2. The range query must also be the last column of the index. This is a principle in above 1.

Good:

• Find (a = 1, B> 2)
• Find (a> 1 and a <10)
• Find (a> 1 and a <10). sort ()

Bad:

• Find (a> 1, B = 2)

3. query and sort the range of only one column

Good:

• Find (a = 1, B = 2). sort (c)
• Find (a = 1, B> 2)
• Find (a = 1, B> 2 and B <4)
• Find (a = 1, B> 2). sort (B)

Bad:

• Find (A> 1, B> 2)
• Find (a = 1, B> 2). Sort (c)

4. Sort the columns with equality (no range) queries to save the index

Assume that you have two queries:

• Find (a = 1, B = 1, D = 1)
• Find (a = 1, B = 1, C = 1, D = 1)

A single index based on a, B, c, and d can satisfy these two queries.

If you want to sort the final values, you may need two indexes.

5. Mongodb's $ ne and $ nin operators are inefficient at using indexes.

• When you need to sort a small number of documents, it is best to read the data to the client for exclusion.