MongoDB query analysis

MongoDB query analysis can ensure whether the index we recommend is effective and is an important tool for query statement performance analysis.

Commonly used functions for MongoDB query analysis are: explain() and hint().

Use explain()

The explain operation provides query information, usage index and query statistics, etc. It is helpful for us to optimize the index.

Next we create indexes for gender and user_name in the users collection:

>db.users.ensureIndex({gender:1,user_name:1})
</p>
<p>现在在查询语句中使用 explain ：</p>
<pre>
>db.users.find({gender:"M"},{user_name:1,_id:0}).explain()

The above explain() query returns the following results:

{
   "cursor" : "BtreeCursor gender_1_user_name_1",
   "isMultiKey" : false,
   "n" : 1,
   "nscannedObjects" : 0,
   "nscanned" : 1,
   "nscannedObjectsAllPlans" : 0,
   "nscannedAllPlans" : 1,
   "scanAndOrder" : false,
   "indexOnly" : true,
   "nYields" : 0,
   "nChunkSkips" : 0,
   "millis" : 0,
   "indexBounds" : {
      "gender" : [
         [
            "M",
            "M"
         ]
      ],
      "user_name" : [
         [
            {
               "$minElement" : 1
            },
            {
               "$maxElement" : 1
            }
         ]
      ]
   }
}

Now, let’s take a look at this Fields of the result set:

• indexOnly: The field is true, which means we use the index.

• cursor: Because this query uses an index, and the index in MongoDB is stored in a B-tree structure, so this also uses a cursor of type BtreeCursor. If no index is used, the cursor type is BasicCursor. This key will also give you the name of the index you are using. Through this name, you can view the system.indexes collection under the current database (the system automatically creates it, because it stores index information, this will be mentioned later) to get the detailed information of the index. .

• #n: The number of documents returned by the current query.

• nscanned/nscannedObjects: Indicates how many documents in the collection have been scanned by the current query. Our goal is to make this value more consistent with the number of returned documents. The closer the better.

• millis: The time required for the current query, in milliseconds.

• indexBounds: The specific index used by the current query.

Using hint()

Although the MongoDB query optimizer generally works very well, you can also use hints to force MongoDB to use a specific index.

This method will improve performance in some cases. Take an indexed collection and perform a multi-field query (some fields are already indexed).

The following query example specifies the use of gender and user_name index fields to query:

>db.users.find({gender:"M"},{user_name:1,_id:0}).hint({gender:1,user_name:1})

You can use the explain() function to analyze the above query:

>db.users.find({gender:"M"},{user_name:1,_id:0}).hint({gender:1,user_name:1}).explain()