Use explain() to view the execution plan of the mongodb query statement
Similar to relational databases such as MySQL and Oracle, MongoDB calculates the optimal query plan for each query statement through the query optimizer, including selected indexes, query time, scanned records, number of scanned indexes, alternative execution plans, etc. information. This article introduces using explain() to view the execution plan of a single query statement.
explain()Syntax
Use explain() to obtain the query plan of the query statement. The syntax is as follows
db.collections.explain().
For example, view the execution plan of db.orders.find()
db.orders.explain().find()
return
{
"explainVersion" : "2",
"queryPlanner" : {
"namespace" : "meituan.orders",
"indexFilterSet" : false,
"parsedQuery" : {
\t\t\t
},
"queryHash" : "E475932B",
"planCacheKey" : "66AC8600",
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"queryPlan" : {
"stage" : "COLLSCAN",
"planNodeId" : 1,
"filter" : {
\t\t\t\t\t
},
"direction" : "forward"
},
"slotBasedPlan" : {
"slots" : "$$RESULT=s4 env: { s1 = TimeZoneDatabase(Antarctica/Rothera...Antarctica/Vostok) (timeZoneDB), s3 = 1699514675958 (NOW), s2 = Nothing (SEARCH_META) }" ,
"stages" : "[1] scan s4 s5 none none none none lowPriority [] @"8e5238a3-24a2-4d90-ba2c-34c78b61f5ea" true false "
}
},
"rejectedPlans" : [ ]
},
"command" : {
"find" : "orders",
"filter" : {
\t\t\t
},
"batchSize" : 1000,
"projection" : {
\t\t\t
},
"$readPreference" : {
"mode" : "primary"
},
"$db" : "meituan"
},
"serverInfo" : {
"host" : "XXXXXX-W11",
"port" : 27017,
"version" : "7.0.1",
"gitVersion" : "425a0454d12f2664f9e31002bbe4a386a25345b5"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : 104857600,
"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
"internalQueryProhibitBlockingMergeOnMongoS" : 0,
"internalQueryMaxAddToSetBytes" : 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600,
"internalQueryFrameworkControl" : "trySbeEngine"
},
"ok" : 1
}
explain()Three output modes
explain() returns the contents of the execution plan according to queryPlanner by default. In addition to queryPlanner mode, there are also executionStats mode and allPlansExecution mode. How to use the three modes is as follows.
Default (queryPlanner) mode
For the data update statements remove(), findAndModify(), and update(), after adding explain(), the data update statement will not actually be executed, but will only return the execution plan used in the query.
When Aggregation contains $out and $merge statements, only the default mode, which is queryPlanner mode, can be used. executionStats mode and allPlansExecution mode cannot be used.
When explain() is used, it needs to be placed in front of the query or update statement.
The two query statements of db.collection.explain().find() and db.collection.find().explain() are different. db.collection.explain().find() can also add more query modification statements in the query chain.
Such as db.collection.explain().find().limit(1)
Db.collection.explain().find().count().hint(), etc.
db.collection.find().explain() returns the cursor of the query execution plan. You need to use next() to obtain the execution plan. No further query modification statements can be added later.
Use db.collection.explain().help() to get more usage and help information of explain
-
db.collection.explain().find() db.collection.explain("queryPlanner").find()executionStats mode
-
db.collection.explain("executionStatus").find()allPlansExecution mode
-
db.collection.explain("allPlansExecution").find()The content returned by the three modes is different. queryPlanner returns the execution plan selected in the query optimizer, such as data query mode, full table scan COLLSCAN, index scan IXSCAN, etc. When you choose to use an index query, return the index required for the query. In “executionStats” mode, in addition to returning the execution plan, it also returns the execution information of the query according to the execution plan, including execution time, the number of indexes scanned, the number of documents scanned, and the execution information of each query stage. “allPlanExecution” mode, in addition to execution plans and execution information, also includes statistical information of all execution plans during execution plan selection, including execution statistics of alternatives. By viewing the execution plan, you can check whether the database is executing as expected and provide a database query optimization plan. For example, add hint(), delete or add indexes, modify the index definition method, etc. to improve query efficiency.
Explain()Usage scenarios
explain() is suitable for the following types of queries and data update statements.
- aggregate()
- find()
- distinct()
- count()
- remove()
- findAndModify()
- group()
- update()
- mapReduce()
-
For the data update statements remove(), findAndModify(), and update(), after adding explain(), the data update statement will not actually be executed, but will only return the execution plan used in the query.
When Aggregation contains $out and $merge statements, only the default mode, which is queryPlanner mode, can be used. executionStats mode and allPlansExecution mode cannot be used.
When explain() is used, it needs to be placed in front of the query or update statement.
The two query statements of db.collection.explain().find() and db.collection.find().explain() are different. db.collection.explain().find() can also add more query modification statements in the query chain.
Such as db.collection.explain().find().limit(1)
Db.collection.explain().find().count().hint(), etc.
db.collection.find().explain() returns the cursor of the query execution plan. You need to use next() to obtain the execution plan. No further query modification statements can be added later.
Use db.collection.explain().help() to get more usage and help information of explain
-
db.order.explain().help()
Explainable operations
.aggregate(…) – explain an aggregation operation
.count(…) – explain a count operation
.distinct(…) – explain a distinct operation
.find(…) – get an explainable query
.findAndModify(…) – explain a findAndModify operation
.group(…) – explain a group operation
.mapReduce(…) – explain a mapReduce operation
.remove(…) – explain a remove operation
.update(…) – explain an update operation
Explainable collection methods
.getCollection()
.getVerbosity()
.setVerbosity(verbosity)
Use db.collection.explain().find().help() to obtain the query modifier and explain cursor information.
Such as db.order.explain().find().help()
Explain query methods
.finish() – sends explain command to the server and returns the result
.forEach(func) – apply a function to the explain results
.hasNext() – whether this explain query still has a result to retrieve
.next() – alias for .finish()
Explain query modifiers
.addOption(n)
.batchSize(n)
.comment(comment)
.collation(collationSpec)
.count()
.hint(hintSpec)
.limit(n)
.maxTimeMS(n)
.max(idxDoc)
.min(idxDoc)
.readPref(mode, tagSet)
.showDiskLoc()
.skip(n)
.sort(sortSpec)
explain()Implementation mechanism
In terms of implementation, explain() encapsulates the mongodb explain command
db.runCommand(
{
explain:
, verbosity:
, comment:
}
)
like
db.runCommand(
{
explain: { count: “products”, query: { quantity: { $gt: 50 } } },
verbosity: “queryPlanner”
}
)
Compare explain() with $indexStats.
Both commands can be used to view index and execution information defined in a collection. However, the two commands each have their own focus and usage scenarios.
explain() returns the execution plan of the query statement, including data scanning method, full table scan (COLLSCAN), index scan (IXSCAN), etc. It also includes the execution time of the query statement, the number of scanned documents, the number of scanned index keys, etc. Through explain(), technicians can find out whether the query needs to add an index, modify the existing index, or add hint() to guide the query optimizer to choose a better execution plan. Optimize for a single query.
$indexStatus, counts all index definitions and usage in a single collection. Contains detailed information about the index definition, index hit information, etc. Optimizations can be made for less used indexes.
In actual application, the return results of explain() and $indexStats need to be used in combination.