Optimization of MongoDB Index _mongodb

MongoDB is a database based on distributed file storage. Written by the C + + language. Designed to provide scalable, high-performance data storage solutions for WEB applications. The MongoDB index is almost the same as the index of a relational database. The MongoDB query optimizer can use this data structure to quickly pair documents in a collection (collection) (collection) To search and sort. To be exact, these indexes are implemented by B-tree indexes. On the command line, you can create an index by calling the Ensureindex () function, which specifies a field to multiple fields that require an index, and the following describes how the MongoDB index optimizes

I. Introduction to the Index

For example, the following data

Db.refactor.insert ({"username": "Refactor", "Age":, "IsActive": true})
Db.refactor.insert ({"username": " Refactor ", age": A, "IsActive": false})
Db.refactor.insert ({"username": "AAAAA", "Age": "IsActive": false})
Db.refactor.insert ({"username": "AAAAA", "age":%, "IsActive": true})
Db.refactor.insert ({"username": " Sssssss ", age":, "IsActive": true})
Db.refactor.insert ({"username": "Tttttt", "Age":, "IsActive": true})
Db.refactor.insert ({"username": "Tttttt", "Age": Si, "isactive": true})
Db.refactor.insert ({"username": " BBBBB "," Age ":," IsActive ": false})
Db.refactor.insert ({" username ":" Rrrrr "," Age ":," IsActive ": true})
Db.refactor.insert ({"username": "Rrrrr", "Age": Si, "isactive": false})

To search by the username key, you can create an index on this key to increase the query speed.

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

To search by the Username,age key, you can create an index on this key to increase the query speed.

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

The document passed to Ensureindex is a set of keys with a value of 1 or 1, 1 is ascending, and-1 is descending, which indicates the direction in which the index was created. If the index has only one key, the orientation is irrelevant.

If you have multiple keys, you have to consider the direction of the index.

Second, MongoDB with the monitoring, according to these monitoring information, can be done as the basis for optimization

1. Explain implementation plan

MongoDB provides a explain command to learn how the system handles query requests. With the explain command, we can see how the system uses indexes to speed up retrieval and to optimize indexes.

A few key field descriptions

Cursor: Return cursor type (basiccursor or btreecursor)
nscanned: Number of documents scanned
N: Number of documents returned
Millis: Time consuming (milliseconds)
Indexbounds: Index used

For example

SQL Code

>db.order.ensureindex ({"User.uid": 1})

>db.order.find ({"Status": 1.0, "User.uid": {$gt: 2663199.0}}). Explain () 
{ 
"cursor": "Btreecursor user.uid_1", 
"nscanned": 337800, 
"nscannedobjects": 337800, 
"n": 337800, 
"Millis": 1371, 
"Nyields": 0, 
"nchunkskips": 0, 
"Ismultikey": false, 
" Indexonly ": false, 
" Indexbounds ": { 
" user.uid ": [ 
[ 
2663199, 
1.7976931348623157e+308 
] 
] 
} 
}

2, Optimizer profile

The slow query log in MySQL is often used as the basis for our database optimization does it have a similar function in MongoDB? The answer is yes, that's mongodbdatabaseprofiler. So MongoDB not only has but also some more detailed information than the slowquerylog of MySQL.
MongoDB the slow statement to be output, which exists in Db.system.profile. Similar to MySQL's slowlog configuration, you need to set parameters, MONGO will output slow statements to profile. There are two parameters to control the output of profile
Db.setprofilinglevel (LEVEL,SLOWMS);

The default is 0 does not output 1 by the second parameter time valve value (in milliseconds) output 2 all output. Usually we open the parameters in the test environment when tuning. In the production environment generally does not output profile.

Like what

> Db.system.profile.find ({millis:{$gt: 1000}})

You can output a slow statement with a query time greater than 1 seconds.

The meaning of the values of the profile output is

TS: Command Execution time
Info: The contents of the command
Query: Representing queries
Order.order: A library and collection that represents a query
Reslen: The result set size returned, the byte number
nscanned: Number of scanned records
Nquery: Back is the query condition
Nreturned: Returns the number of records and when
Millis: The time spent

If you find that the time is longer, then you need to optimize.

Like what

(1), the number of nscanned is very large, or close to the total number of records, then the index query may not be used.

(2), Reslen is very large, it is possible to return unnecessary fields.

(3), nreturned is very large, then there may be no restrictions on the query.

Index selection mechanism of MongoDB

The MongoDB Optimizer chooses a better index in the comparison.

First, it gives the query a preliminary "best index";

Second, if the best index does not exist it will try to choose the best performing index;

Finally, the optimizer remembers all the options for similar queries (only to large-scale file changes or changes to the index).

Then the optimizer is how to define the "best index" of the query. The best index must contain all the fields in the query that can be filtered and sorted. In addition, any fields that are used for range scanning, as well as sort fields, must be sorted after the fields that make the equivalent query. If there are different best indexes, then MONGO will randomly select.

Iv. Index Summary of MongoDB

1. Equivalence test

Add all fields that need to be tested in an index, in any order.

2. Sort fields (Ascending/descending problem for multiple-sorted fields)

Add fields to the index sequentially, based on the order of the queries.

3. Range Filtration

Adds a range filter field to the index from low to high in the base of the field (the number of different values for the field in collection).

4, if the index of equivalence or range query field can not filter out the collection more than 90% of the document, then it is better to remove the index estimate.

5. Indexing allows data to be obtained through key fields, enabling quick query and updating of data. However, it is important to note that indexes also add some burden to the system when inserting and deleting them. When inserting data into the collection, the indexed fields must be added to the B-tree, so the index is suitable for reading far more than the written dataset, and for frequently written collections, in some cases the index has side effects. But most collections are frequently read collections, so collections are useful in most cases.

6. If the data collection is relatively small (typically less than 4M), the data can be returned using sort () without having to establish an index. In this case, do the joint use of limit () and sort ().

On the optimization of MongoDB index to introduce so many people, I hope to help you!