Of Stack

MongoDB performance section create index composite index unique index delete index and explain execution plan

  • 2020-06-01 11:14:45
  • OfStack

Index of 1.

MongoDB provides a variety of indexing support, the index information is stored in system.indexes, and the default is always to create an index for _id, which USES a basic relational database like MySQL. In fact, it can be said that the index is another layer system over the data storage system, so it is not surprising that various storage structures have the same or similar index implementation and use interface.

1. Basic index

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


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

_id is an index that is automatically created when a table is created. This index cannot be dropped. When a large amount of data is already in the system, creating an index is a time-consuming task that can be performed in the background by specifying "backgroud:true". 


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

2. Document indexing

Indexes can be any type of field, even a document: 


db.factories.insert( { name: "wwl", addr: { city: "Beijing", state: "BJ" } } );
// in addr  Create an index on a column 
db.factories.ensureIndex( { addr : 1 } );
// The following query will use the index we just created 
db.factories.find( { addr: { city: "Beijing", state: "BJ" } } );
// But the following query will not use the index, because the order of the query is not the same as the order in which the index was created 1 sample 
db.factories.find( { addr: { state: "BJ" , city: "Beijing"} } );

3. Composite index

Like other database products, MongoDB also has a composite index. Next, we will build a composite index on addr.city and addr.state. When creating a composite index, the 1 after the field represents ascending order, and -1 represents descending order, and whether to use 1 or -1 depends mainly on when sorting or when querying within a specified range. 


db.factories.ensureIndex( { "addr.city" : 1, "addr.state" : 1 } );
//  The following queries use 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. Exclusive index

You can create a unique index by specifying "unique:true" in the ensureIndex command. For example, insert two records into table t4: 


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

5. Force indexes

The hint command can force an index to be used. 


db.t5.find({age:{$lt:30}}).hint({name:1, age:1}).explain()

6. Drop the index 


// delete t3  All indexes in the table 
db.t3.dropIndexes()
// delete t4  In the table firstname  The index 
db.t4.dropIndex({firstname: 1})

2. explain execution plan

MongoDB provides an explain command to let us know how the system handles query requests. With the explain command, we can see how the system is using indexes to speed up retrieval, and we can optimize the indexes accordingly. 


db.t5.ensureIndex({name:1})
 db.t5.ensureIndex({age:1})
 db.t5.find({age:{$gt:45}}, {name:1}).explain()
 {
   "cursor" : "BtreeCursor age_1",
   "nscanned" : 0,
   "nscannedObjects" : 0,
   "n" : 0,
   "millis" : 0,
   "nYields" : 0,
   "nChunkSkips" : 0,
   "isMultiKey" : false,
   "indexOnly" : false,
   "indexBounds" : {
   "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 in milliseconds

indexBounds: the index used

3. Optimizer profile

In MySQL, the slow query log is often used as the basis for optimizing the database. Is there a similar function in MongoDB? The answer is yes, that's MongoDB Database Profiler.

1. Turn on profiling

There are two ways to control the switch and level of Profiling. The first is to set it directly in the startup parameters. When MongoDB is enabled, add the wok to profile= level. You can also configure it in real time by calling the db.setProfilingLevel (level) command on the client side, where Profiler information is stored in system.profile. We can get the current Profile level through the db.getProfilingLevel () command, which is similar to the following: 


db.setProfilingLevel(2);

The above profile level can take 0,1,2 and 3 values, which are expressed as follows:

0 do not open

1. Record the slow command (default is > 100ms)

2. Record all commands

Profile records slow commands at level 1, so what is the definition of slow? As mentioned above, the default is 100ms. Of course, there are Settings as well as defaults. There are two ways to set it and level 1. The second is to call db.setProfilingLevel with the second parameter: 


db.setProfilingLevel( level , slowms )
db.setProfilingLevel( 1 , 10 );

2. Query Profiling records

Unlike the slow query log of MySQL, the record of MongoDB Profile is directly stored in the system db, and the record location is system.profile. Therefore, we only need to query the record of Collection to get our Profile record. List the Profile records that have been executed longer than a certain limit (5ms) : 


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

MongoDB Shell also provides a simple command, show profile, that lists the last five Profile records that took longer to execute than 1ms.

4. Common performance optimization schemes

Create indexes

Limit the number of results returned

Query only the fields used

Using capped collection

Server Side Code Execution is adopted

Use Hint to force an index

Using Profiling

5. Performance monitoring tools

1. mongosniff

This tool can monitor from the bottom level which commands are sent to MongoDB to execute, from which it can analyze: execute as root: 


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

It then monitors bits locally to listen for all packet requests from MongoDB on the default port 27017 on localhost.

2.Mongostat

This tool can quickly view the statistics field description of a group of running MongoDB instances:

insert: number of inserts per second

query: queries per second

update: number of updates per second

delete: deletions per second

locked: locking

qr | qw: client query queue length (read | write)

ar | aw: active client volume (read | write)

conn: the number of connections

time: current time

It refreshes the state value once per second, providing good readability. Through these parameters, the performance of a whole can be observed.

3. db.serverStatus

This is one of the most common and basic commands to see the running state of an instance.

4.db.stats

db.stats view database status information.

The above is the site for you to introduce MongoDB performance section index creation, composite index, only 1 index, index removal and explain execution plan related knowledge, I hope to help you!

Related articles: