MongoDB Performance Chapter-Create indexes, composite indexes, unique indexes, delete indexes and explain execution plans

first, the index

MongoDB provides a variety of index support, the index information is saved in System.indexes, and the default is always to create an index for _ID, its index uses the basic and MySQL and other relational database. In fact, it can be said that the index is above the data storage system on the other layer of the system, so the various structures of different storage have the same or similar index implementation and use of interfaces and not surprisingly. 1. Base index

Create an index on the age of the field, 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. When the system already has a lot of data, creating an index is a very time-consuming activity, we can execute it in the background, just specify "Backgroud:true".

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

2. Document Indexing

Indexes can be any type of field, even documents:

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

3. Combined Index

As with other database products, MongoDB also has a composite index, and below we will build the combined index on addr.city and addr.state. When creating a composite index, the 1 after the field indicates ascending, 1 means descending, 1 or 1 is mainly related to the time of sorting or querying within the specified range.

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. Deleting an index

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

Ii. explain implementation plan

MongoDB provides a explain command to let us know how the system handles query requests. With the explain command, we can look at how the system uses indexes to speed up retrieval, while optimizing indexes in a targeted way.

  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: Returns the 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, the slow query log is often used as the basis for us to optimize the database, is there a similar function in MongoDB? The answer is yes, that's the MongoDB database Profiler. 1. Turn on the profiling function

There are two ways to control the Profiling switch and level, the first is directly in the startup parameters directly set. When you start MongoDB, add the –profile= level. You can also invoke the Db.setprofilinglevel (level) command on the client to configure it in real time, and the 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 take 0,1,2 three values, they say the meaning is as follows: 0– do not turn on the-X record slow command (default is >100ms)--Record all commands

The profile record will record the slow command at Level 1 o'clock, so what is this slow definition? Above we say that the default is the 100ms, of course there are settings, there are two ways to set the method and level, one is to add the –slowms boot parameter configuration. The second is to add the second parameter when calling Db.setprofilinglevel:

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

2. Query Profiling Records

Unlike MySQL's slow query log, MongoDB profile records are directly present in the system DB, recording location system.profile, so we can only query this collection record to get our profile record. List the profile records that have an execution time longer than a certain limit (5ms):

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

MongoDB Shell also provides a relatively concise command show profile, which lists the last 5 execution times over 1ms of profile records. iv. commonly used performance optimization scheme to create an index-qualified return number of results only the fields used by the query are capped collection with the server Side Code execution using hint, forcing the use of the index with profiling

Five, performance monitoring tools 1. Mongosniff

This tool can monitor from the bottom to what commands are sent to MongoDB for execution, from which it can be analyzed: EXECUTE as root:

$./mongosniff--source NET Lo

It then monitors all packet requests to local mongodb with localhost listening on the default 27017 port. 2.Mongostat

This tool provides a quick view of statistics field descriptions for a set of running MongoDB instances: Insert: Inserts per second query: number of queries per second update: Updates per second Delete: volume per second locked: Lock volume QR | QW: Client Query Queue Length (read | write) ar | AW: Active Client Volume (Read | write) Conn: number of connections time: current

It refreshes the status value every second, providing good readability, which allows you to observe the performance of a whole. 3. Db.serverstatus

This command is one of the most commonly used and most basic commands to view the running state of an instance. 4.db.stats

Db.stats View the database status information.


from:http://blog.csdn.net/loveyunwt/article/details/8067480