Use mongodb indexes and use explain and profile Optimization

Indexes are used to accelerate the query speed. Each item has a one-sidedness. At the same time, additional overhead is generated for each insert, update, and delete operation. Indexes sometimes cannot solve the problem of slow query. Generally, if more than half of the results in the set are returned, the full table scan is more efficient than the query index. Creating too many indexes will slow insertion and occupy

Indexes are used to accelerate the query speed. Each item has a one-sidedness. At the same time, additional overhead is generated for each insert, update, and delete operation. Indexes sometimes cannot solve the problem of slow query. Generally, if more than half of the results in the set are returned, the full table scan is more efficient than the query index. Creating too many indexes will slow insertion and occupy

Indexes are used to accelerate the query speed. Each item has a one-sidedness. At the same time, additional overhead is generated for each insert, update, and delete operation. Indexes sometimes cannot solve the problem of slow query. Generally, if more than half of the results in the set are returned, the full table scan is more efficient than the query index. Creating too many indexes will lead to a very slow insertion and will also occupy a lot of space. You can use the explain and hint tools for analysis. If the index has a direction, the reverse order is still ascending. The default maximum number of indexes for each set is 64.

1. View Indexes

> db.jiunile_events.getIndexes();[        {                "v" : 1,                "key" : {                        "_id" : 1                },                "ns" : "jiunile_login.jiunile_events",                "name" : "_id_"        },        {                "v" : 1,                "key" : {                        "stmp" : -1                },                "ns" : "jiunile_login.jiunile_events",                "name" : "stmp_-1"        },        {                "v" : 1,                "key" : {                        "uid" : 1,                        "stmp" : -1                },                "ns" : "jiunile_login.jiunile_events",                "name" : "uid_1_stmp_-1"        }]

There are three indexes in this instance. _ id is the index automatically created during table creation and cannot be deleted. Uid_1_stmp _-1 is a composite index. 1 indicates ascending order, and-1 indicates descending order.

2. index creation parameters include:

optionvaluesdefaultbackgroudtrue/falsefalsedropDupstrue/falsefalseuniquetrue/falsefalsesparsetrue/falsefalse

Regular index creation

> db.jiunile_posts.ensureIndex({pid:1});

When there is a large amount of data, it takes a lot of time to create an index. You can specify it to be executed in the background. You only need to specify "backgroud: true. For example

> db.jiunile_posts.ensureIndex({pid:1}，{backgroud:true});

3. the embedded index is the key to create an index for embedded documents, which is no different from the normal key to create an index.

> db.jiunile_posts.ensureIndex({"post.date":1})

4. Document Indexing

> db.jiunile_comments.insert({cid:222, properties:{user:'jiunile', email:'jiunile@jiunile.com'}})> db.jiunile_comments.ensureIndex({properties:1})

5. combined index

> db.jiunile_comments.ensureIndex({"properties.user":1,"properties.email":1})

The following query uses this index

> db.jiunile_comments.find({"properties.user":'jiunile',"properties.email":'jiunile@jiunile.com'})> db.jiunile_comments.find({"properties.user":'jiunile'})> db.jiunile_comments.find().sort({"properties.user":1})

Composite indexes can be used only when the subquery matches the index prefix.

6. For a unique index, you only need to specify "unique: true" in the name of ensureIndex.

> db.jiunile_posts.ensureIndex({pid:1},{unique:true})

If you create an index for an existing set and some data already exists, creating a unique index will fail. You can use dropDups to retain the first document, and then duplicate documents will be deleted. This method is used with caution.

> db.jiunile_posts.ensureIndex({pid:1},{unique:true, dropDups:true})

7. The Force Index hint command can force an index to be used.

> db.jiunile_posts.find({pid:{$lt:333}}).hint({pid:1,date:1})

8. Delete indexes to delete all indexes in the Set:

> db.collection.dropIndexes()

Delete an index of a collection:

> db.collection.dropIndex({x:1})

9. Re-Indexing

> db.collection.reIndex()> db.runCommand({reIndex:'collection'})

Re-indexing locks the set. When you use the repair command to restore the database, the index is rebuilt.

10. Full-text index mongodb full-text index was introduced in version 2.4, which will be discussed later.

11. The explain Execution Plan uses the explain command to return the index information, time consumption, number of scanned documents, and other statistics used for the query.

> db.jiunile_events.find({uid:178620830}).explain(){        "cursor" : "BtreeCursor uid_1_stmp_-1",        "isMultiKey" : false,        "n" : 2,        "nscannedObjects" : 2,        "nscanned" : 2,        "nscannedObjectsAllPlans" : 2,        "nscannedAllPlans" : 2,        "scanAndOrder" : false,        "indexOnly" : false,        "nYields" : 0,        "nChunkSkips" : 0,        "millis" : 4,        "indexBounds" : {                "uid" : [                        [                                178620830,                                178620830                        ]                ],                "stmp" : [                        [                                {                                        "$maxElement" : 1                                },                                {                                        "$minElement" : 1                                }                        ]                ]        },        "server" : "jiunile-191155:27017"}

Field description:

Cursor: returned cursor type isMultiKey: used combination index n: returned document quantity nscannedObjects: Number of scanned documents nscanned: Number of checked documents or index entries scanAndOrder: sort indexOnly: nYields in memory: The number of times nChunkSkips: millis: time-consuming (millisecond) indexBounds: Used Index server: server host name to query the number of read locks waiting for write operation execution

12. Enable the profiling function to view the profiling level:

> db.getProfilingLevel()0

Set the profiling level:

> db.setProfilingLevel( level , slowms )> db.setProfilingLevel(2,10){ "was" : 0, "slowms" : 100, "ok" : 1 }

The profile level can be 0, 1, and 2. The meaning is as follows:

0-do not enable default level

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

2-record all commands

13. querying the profiling record MongoDB Profile record directly exists in the system db, and the record location is system. profile.

> db.system.profile.find().sort({$natural:-1}).limit(1){ "ts" : ISODate("2013-07-18T09:56:59.546Z"), "op" : "query", "ns" : "jiunile_event.jiunile_events", "query" : { "$query" : { "uid" : 161484152, "stmp" : { "$gt" : 0 } }, "$orderby" : { "stmp" : -1 } }, "ntoreturn" : 0, "ntoskip" : 0, "nscanned" : 35, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : NumberLong(354), "w" : NumberLong(0) }, "timeAcquiringMicros" : { "r" : NumberLong(3), "w" : NumberLong(3) } }, "nreturned" : 35, "responseLength" : 7227, "millis" : 0, "client" : "10.1.242.209", "user" : "" }> db.system.profile.find().pretty().limit(1){        "ts" : ISODate("2013-07-18T09:53:40.103Z"),        "op" : "query",        "ns" : "jiunile_event.jiunile_event_friends",        "query" : {                "_id" : 195794232        },        "ntoreturn" : 1,        "idhack" : true,        "keyUpdates" : 0,        "numYield" : 0,        "lockStats" : {                "timeLockedMicros" : {                        "r" : NumberLong(45),                        "w" : NumberLong(0)                },                "timeAcquiringMicros" : {                        "r" : NumberLong(3),                        "w" : NumberLong(5)                }        },        "responseLength" : 20,        "millis" : 0,        "client" : "10.1.22.199",        "user" : ""}

Field description:

Ts: when to run this command op: Operation Type query: detailed information of this command responseLength: returned result set size ntoreturn: the actual returned result set millis: The command execution time, in milliseconds

14. Modifying the profiling size capped Collections is more efficient than normal Collections. Capped Collections is an efficient Collection type, which has the following features:

A. fixed size; Capped Collections must be created in advance and set the size:

> db.createCollection("collection", {capped:true, size:100000}) 

B. Capped Collections can be used for insert and update operations, but cannot be used for delete operations. You can only use the drop () method to delete the entire Collection.

C. Insert-based sorting by default. If the query is not sorted, the query is always returned in the insert order.

D. FIFO. If the size of the Collection is exceeded, the FIFO algorithm is used. The new record replaces the First insert record.

> db.setProfilingLevel(0){ "was" : 2, "slowms" : 10, "ok" : 1 }>> db.getProfilingLevel()0> db.system.profile.drop()true> db.createCollection("system.profile",{capped:true, size: 1000000}){ "ok" : 1 }> db.system.profile.stats(){        "ns" : "jiunile_event.system.profile",        "count" : 0,        "size" : 0,        "storageSize" : 1003520,        "numExtents" : 1,        "nindexes" : 0,        "lastExtentSize" : 1003520,        "paddingFactor" : 1,        "systemFlags" : 0,        "userFlags" : 0,        "totalIndexSize" : 0,        "indexSizes" : {        },        "capped" : true,        "max" : 2147483647,        "ok" : 1}

Source: http://www.ttlsa.com/html/1661.html

Indexes are used to accelerate the query speed. Each item has a one-sidedness. At the same time, additional overhead is generated for each insert, update, and delete operation. Indexes sometimes cannot solve the problem of slow query. Generally, if more than half of the results in the set are returned, the full table scan is more efficient than the query index. Creating too many indexes will lead to a very slow insertion and will also occupy a lot of space. You can use the explain and hint tools for analysis. If the index has a direction, the reverse order is still ascending. The default maximum number of indexes for each set is 64. 1. Check the index. There are three indexes in this instance. _ id is the index automatically created during table creation and cannot be deleted. Uid_1_stmp _-1 is a composite index. 1 indicates ascending order, and-1 indicates descending order. 2. index creation parameters: When a regular index is created with a large amount of data, it takes a lot of time to create an index. You can specify it to be executed in the background. You only need to specify "backgroud: true. For example, 3. the embedded index does not have any difference between the key creation index of embedded documents and the normal key creation index. 4. document Indexing 5. the queries below the composite index will use this index to compose multiple values. This composite index can be used only when the subquery matches the index prefix. 6. For a unique index, you only need to specify "unique: true" in the name of ensureIndex. If you create an index for an existing set and some data already exists, creating a unique index will fail. You can use dropDups to retain the first document, and then duplicate documents will be deleted. This method is used with caution. 7. The Force Index hint command can force the use of an index 8. Delete the index to delete all the indexes in the Set: delete an index in the Set: 9. Re-indexing the index will lock the set. When you use the repair command to restore the database, the index is rebuilt. 10. Full-text index mongodb full-text index was introduced in version 2.4, which will be discussed later. 11. The explain Execution Plan uses the explain command to return the index information, time consumption, number of scanned documents, and other statistics used for the query. Field Description: 12. enable the profiling function to view the profiling level: Set the profiling level: the profile level can be 0, 1, and 2. The meaning is as follows: 0-do not enable the default Level 1-record slow Command [...]