MongoDB query analysis

MongoDB query analysis

MongoDB query analysis ensures that the index we recommend is valid and is an important tool for query statement performance analysis.

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

Using explain()

The explain operation provides query information, uses indexes, query statistics, and so on. It helps us optimize the index.

Next we create an index of 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 look at the fields of this result set:

• IndexOnly : The field is true, indicating that we are using an index.
• cursor: Because this query uses indexes, the indexes in MongoDB are stored in the B-tree structure, so this is also using a BtreeCursor type cursor. I f the index is not used, the type of cursor is BasicCursor. T his key also gives you the name of the index you are using, which allows you to view the system.indexes collection under the current database (which is automatically created and mentioned slightly because index information is stored).
• n: The number of documents returned by the current query.
• nscanned/nscannedObjects: Indicates how many documents are scanned in the collection for this query, and our goal is to bring this number and the number of documents returned closer to the better.
• millis: The time, milliseconds, required for the current query.
• IndexBounds: The index used by the current query.

Using hint()

Although The MongoDB query optimizer generally works well, you can also use hints to force MongoDB to use a specified index.

This approach can improve performance in some cases. An indexedcollection and a multi-field query (some fields are already indexed).

The following query instance specifies an index field user_name gender and data:

>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()