MONGODB CREATE Index

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 an index on the addr column  db.factories.ensureIndex ({addr:1}); // The following query will use the index we just created  db.factories.find ({addr: {city: ' Beijing ', state: ' BJ '}}); // However, the following query will not use the index because the order of the queries is different from the order in which the indexes are 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}); // This index is used in the following query.  db.factories.find ({"addr.city": "Beijing", "addr.state": "BJ"});d B.factories.find ({"addr.city": "Beijing });d B.factories.find (). Sort ({"addr.city": 1, "Addr.state": 1});d B.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:

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 in T4 table ({firstname:1})

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,      false,      false,      "Indexbounds": {      "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: The 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 above profile level can take 0,1,2 three values, they say the meaning is as follows:

1. 0– not open
2. --Record slow command (default = >100ms)
3. --Record all orders

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.

Four, the commonly used performance optimization scheme

1. Create an index
2. Limit the number of returned results
3. Querying only the fields that are used
4. Using Capped collection
5. Using the server Side Code execution
6. Use hint to force the use of indexes
7. With profiling

Five, performance monitoring tool 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 the statistics field descriptions for a set of running MongoDB instances:

• Insert: Insertion amount per second
• Query: Number of queries per second
• Update: Volume per second
• Delete: The amount of deletes 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

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.

MONGODB CREATE Index