MongoDB Performance Chapter creation index, combined index, unique index, delete index and explain execution plan _mongodb

First, index

MongoDB provides a variety of index support, indexed information is kept in system.indexes, and the default is always to create an index for _id, which uses the same relational database as MySQL. In fact, it can be said that the index is over the data storage system on top of the other layer of system, so the different structure of storage have the same or similar index implementation and use interfaces is not surprising.

1. Basic Index

Create an index on the field age, 1 (ascending);-1 (Descending):

Db.users.ensureIndex ({age:1})

_ID is an index that is automatically created when a table is created, and cannot be deleted by this index. When the system has a lot of data, creating an index is a very time-consuming activity, we can execute in the background, just specify "Backgroud:true" can be.

Db.t3.ensureIndex ({age:1}, {backgroud:true})

2. Document indexing

An index can have any type of field, or even a document:

Db.factories.insert ({name: "WWL", addr: {city: "Beijing", State: "BJ"});
Create an index
db.factories.ensureIndex ({addr:1}) on the addr column
; The following query will use the index
db.factories.find ({addr: {city: "Beijing", State: "BJ"}) that we have just established;
However, the following query will not use the index because the order of the queries is not the same as the order in which the index was established
db.factories.find ({addr: {state: "BJ", City: "Beijing"});

3. Combined Index

As with other database products, MongoDB also has a combined index, we will build a composite index on addr.city and addr.state. When you create a composite index, the 1 after the field is ascending, and-1 is in descending order, whether 1 or 1 is related to the time of the sorting or the specified range of queries.

Db.factories.ensureIndex ({"addr.city": 1, "Addr.state": 1});
The following query uses this index
db.factories.find ({"addr.city": "Beijing", "addr.state": "BJ"});
Db.factories.find ({"addr.city": "Beijing"});
Db.factories.find (). Sort ({"addr.city": 1, "Addr.state": 1});
Db.factories.find (). Sort ({"Addr.city": 1})

4. Unique index

You can create a unique index by simply specifying "Unique:true" in the Ensureindex command. For example, insert 2 records into the table T4:

Db.t4.ensureIndex ({firstname:1, lastname:1}, {unique:true});

5. Forcing the use of indexes

The hint command can force an index to be used.

Db.t5.find ({age:{$lt:}). Hint ({name:1, age:1}). Explain ()

6. Delete Index

Delete all indexes in the T3 table
db.t3.dropIndexes ()
//delete firstname index in T4 table
db.t4.dropIndex ({firstname:1})

Ii. 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, while optimizing indexes.

Db.t5.ensureIndex ({name:1})
 Db.t5.ensureIndex ({age:1})
 Db.t5.find ({age:{$gt:}}, {name:1}). Explain ()
 {
   "cursor": "Btreecursor age_1",
   "nscanned": 0,
   "nscannedobjects": 0,
   "n": 0,
   "Millis": 0,< c9/> "Nyields": 0,
   "nchunkskips": 0,
   "Ismultikey": false,
   "indexonly": false,
   "Indexbounds": { c14/> "Age": [
          [45,1.7976931348623157e+308]
        ]
    }
}

Field Description:

Cursor: Return cursor type (basiccursor or btreecursor)

nscanned: Number of documents scanned

N: Number of documents returned

Millis: Time consuming (milliseconds)

Indexbounds: Index used

Third, optimizer profile

In MySQL, slow query log is often used as our basis for optimizing the database, that in the MongoDB have similar functions? The answer is yes, that is MongoDB database Profiler.

1. Open Profiling function

There are two ways to control Profiling switches and levels, the first of which is directly set in the startup parameters. When you start MongoDB, add the –profile= level. It is also possible to configure the Db.setprofilinglevel (level) command on the client side in real time, Profiler information is stored in system.profile. We can get the current profile level through the Db.getprofilinglevel () command, similar to the following:

Db.setprofilinglevel (2);

The level of the above profile can be 0,1,2 three values, and they mean the following:

0– not open.

Record Slow command (default is >100ms)

2– Record All commands

The profile record slows down the command at Level 1 o'clock, so what's the slow definition? Above we said that the tacit thought that the 100ms, of course, there is a default setting, there are two kinds of setting method and level, one is by adding –slowms boot parameter configuration. The second is to invoke Db.setprofilinglevel with the second argument:

Db.setprofilinglevel (level, slowms)
db.setprofilinglevel (1, 10);

2. Query Profiling Records

Unlike MySQL's slow query log, the MongoDB profile record is directly present in the system DB, and the record location is System.profile, so we'll just have to check the collection record to get our profile records. List profile records with execution times longer than a certain limit (5ms):

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

The MongoDB Shell also provides a concise command show profile, which lists profile records with more than 1ms of recent 5 execution times.

Four, the common performance optimization plan

Create an index

Limit the number of results returned

Query only fields that are used

Adopt capped Collection

Using the server Side Code Execution

Use hint to force indexes

Using profiling

V. Performance monitoring Tools

1. Mongosniff

This tool can be analyzed from the bottom to monitor which commands are sent to the MongoDB to perform: Perform as root:

$./mongosniff--source NET Lo

It then monitors all packet requests to local localhost listening to MongoDB on the default 27017 port.

2.Mongostat

This tool allows you to quickly view statistics field descriptions for a group of MongoDB instances in run:

Insert: Number of inserts per second

Query: Number of queries per second

Update: Amount per second

Delete: Removals per second

Locked: Lock Ration

QR | QW: Client Query Queue Length (read | write)

Ar | AW: Active Client Volume (read | write)

Conn: Number of connections

Time: Current times

It refreshes the status value every second, providing good readability, through which you can observe a whole performance situation.

3. Db.serverstatus

This command is one of the most common and basic commands to view the running status of an instance.

4.db.stats

Db.stats View database state information.

The above is a small set to introduce the MongoDB performance of the creation index, combined index, unique index, delete index and explain implementation of the relevant knowledge, hope to help everyone!