"MongoDB" (i)--a summary of the MONDODB index

Guide

Creating indexes for data helps improve the performance of query data, and this article summarizes the rules you should follow to create a MongoDB index.

I divide these rules into four categories :

1. "Query"
2. "Sort"
3. "RAM"
4. "Selectivity" query

Db.collection.find ({})

This section is focused on the query section in queries, the find operation, how we create the index, and what queries can make the index valid.
　　
Total rule: The index contains all the fields in find.
"Specific rules":
1. If all queries use the same, unique field, create an index that contains only one field.
"Example":d B.collection.find ({x: "}), then the index is {x:1}
　 　　　　　　　
2. Create a composite index to support queries that contain multiple fields. A composite index supports a query that has all the indexed fields or a subset of them, from the beginning of a subset, something similar to a prefix.
"Example": if the index is {x:1,y:1,z:1}, then it supports find query conditions of {x: "},{x:", Y: "} and {x:", y: ", Z:"}

"Note": The subset here is somewhat different from the subset in mathematics. The subset here has a professional name of index Prefix, the index prefix, so it is implied that there is a sequence from the go. You can think of an index as a word, and then index prefix is the part of the index that is intercepted from the trip.

　　　　　　　　　　　　 Sort

Db.collection.find (). Sort ({})

This section is focused on the sort section of the query, explaining how we should create the index for the fields to be sorted, or how to sort to make the indexes that are created valid.

"Rules":
1. If the index is a unique field and the sort in the query uses the field, the index is valid either in ascending or descending order.
"Example": if the index is {x:1},sort ({x:1}) or ({x:-1}) can be.
　　　　　
2. If the index is a composite field, the sort direction (ascending or descending) of the field in sort is exactly the same as the index, or each field is the opposite of the index.
"Example": if the index is {x:1,y:-1}, sort ({x:1,y:-1}) or sort ({x:-1,y:1})
　　　　　
3. If the index is a composite field, and if the field in the sort part of the query is a whole field or subset of the index (consistent with the meaning above), then the field in Find () satisfies the 2nd rule of query that we said in the previous section: ' A composite index supports a query that has all the indexed fields or a subset of them, from the beginning of a subset, something similar to a prefix. ’
　　　　　
4. If the index is a qualified field, but the field in the sort part of the query is not all the fields or subsets of the index, the field in Find must be the field before the field in sort in the index.
"Example": Index is {x:1,y:1,z:1}, if sort ({y:1}), then find must be the field before Y in the index, including Y, that is ({x:1,y:1}) RAM

The index size needs to be less than the ram size to avoid querying from disk.
Calculate the size of index:

Db.collection.totalIndexSize ()

In practice, you should calculate the size of index on all collection and make ram greater than the total index size.
　　 selectivity

When creating an index, be sure to be as selective as possible, and after you do not create an index, you still need to scan the entire collection.
For example, if a field in collection is status, and the value of the field is ' A ' (Active) or ' D ' (Delete), this is a very low selectivity if the index is the field, and you can create an index in conjunction with another word. or let the value of status be distributed more widely. Guide Drawing Summary