MongoDB practice (7) Index and performance

I. Index

MongoDB provides a variety of indexes to support that index information is stored in system.indexes and always defaults to _ id

Create an index.

1. Basic index

Create index 1 (ascending order) and-1 (descending order) on field age

The above example shows that there are a total of two indexes, of which _ id is the index created automatically when the table is created. This index is not allowed.

Enough to delete.

Creating an index is a very time-consuming task when the system already has a lot of data, and we can do it in the background just by specifying

"backgroud:true" is fine.

Db.stu.ensureIndex ({age:1}, {background:1})

2. Document indexing

The index can be any type of field or even a document

Db.stu.insert ({_ id:3,name:'user2',age:12,addr: {city:'beijing',state: "BJ"}}) db.stu.insert ({_ id:4,name:'user2',age:12,addr: {city:'shanghai',state: "SH"}}) db.stu.ensureIndex ({addr:1})

The following query will use the index we just built

Db.stu.find ({addr: {city:'beijing',state:'BJ'}}) "_ id": 3, "name": "user2", "age": 12, "addr": {"city": "beijing", "state": "BJ"}}

However, the following query cannot find the result.

Db.stu.find ({addr: {state:'BJ',city:'beijing'}})

3. Combined index

Like other database products, MongoDB also has a composite index, which we will use in addr.city and addr.state.

Create a composite index on the. When creating a composite index, the 1 after the field indicates ascending order-1 indicates whether the descending order is 1 or

The use of-1 is mainly related to sorting or querying within a specified range.

Db.stu.ensureIndex ({'addr.city':1,'addr.state':1})

This index is used in the following queries

4. Unique index

You can create a unique index simply by specifying "unique:true" in the ensureIndex command.

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

When creating a unique index, a unique index cannot be established if there are two identical pieces of data in the table.

5. Force the use of indexes

The hint command can force the use of an index.

Db.stu.ensureIndex ({name:1,age:1})

Then the index cannot be used below

Use hint to force the use of indexes

6. Delete the index

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

II. Explain implementation Plan

MongoDB provides an explain command to let us know how the system handles query requests. Using the explain command

We can well observe how the system uses the index to speed up retrieval and optimize the index.

Several key field descriptions:

Cursor: returns the cursor type (BasicCursor or BtreeCursor)

Nscanned: number of documents scanned

N: number of documents returned

Millis: time consuming (milliseconds)

IndexBounds: the index used

Third, optimizer profile

In MySQL, slow log is often used as the basis for us to optimize the database. Is there anything similar in MongoDB?

What about the function? The answer is yes. That's MongoDBDatabaseProfiler. So MongoDB not only has but also

There is also some more detailed information than MySQL's SlowQueryLog.

1. Enable the Profiling function

There are two ways to control the switch and level of Profiling. The first is to set it directly in the startup parameters.

Add the-profile= level when you start MongoDB.

You can also call the db.setProfilingLevel command on the client side to configure the Profiler information to be saved in real time

In system.profile. We can get the current Profile level through the db.getProfilingLevel () command.

The level of profile can take three values of 012, and the meaning they express is as follows

0-do not open

1-record slow commands (default is > 100ms)

2-record all commands

The Profile record records slow commands at level 1, so what is the definition of slow? As we mentioned above, the default is

Of course, 100ms can be set by default, and its setting method is the same as its level. One is enabled by adding-slowms.

Dynamic parameter configuration. The second is to add a second parameter when calling db.setProfilingLevel

Db.setProfilingLevel (1 dint 10)

2. Query Profiling records

Unlike the slow log of MySQL, the MongoDBProfile record is directly stored in the record location in the system db.

System.profile, so we only need to query the record of this Collection to get our Profile record.

I did. List Profile records whose execution time is longer than a certain limit (5ms)

Db.system.profile.find ({millis: {$gt: 5}}) {"op": "command", "ns": "test.$cmd", "command": {"count": "orders", "query": {}, "fields": {}}, "ntoreturn": 1, "keyUpdates": 0, "numYield": 0 "lockStats": {"timeLockedMicros": {"r": NumberLong (11494), "w": NumberLong (0)}, "timeAcquiringMicros": {"r": NumberLong (4), "w": NumberLong (5)}}, "responseLength": 48, "millis": 11, "ts": ISODate ("2013-11-16T13:52:38.391Z"), "client": "127.0.0.1" "allUsers": [], "user": ""}

You can use the following command to query the latest one

Db.system.profile.find () .sort ({$natural:-1}) .limit (1)

MongoDBShell also provides a relatively concise command showprofile that lists the last 5 execution times exceeding

Profile record of 1ms.

The Profiling function will definitely affect the efficiency, but it's not too serious because it uses system.profile to remember.

While system.profile is a cappedcollection, this kind of collection has some limitations and characteristics in operation.

But more efficient.