Shulou(Shulou.com)06/01 Report--

Explain and Index

There are three modes of explain: queryPlanner, executionStats, and allPlansExecution. In real development, the executionStats pattern is commonly used. This example analyzes this pattern.

> db.createCollection ("person")

{"ok": 1}

> for (var lte 0 db.person.find ({"age": {"$lte": 2000}}) .explain ("executionStats")

{"queryPlanner": {"plannerVersion": 1, "namespace": "test.person", "indexFilterSet": false, "parsedQuery": {"age": {"$lte": 2000}}, "winningPlan": {"stage": "COLLSCAN", "COLLSCAN" means full table scan "filter": {"age": {"$lte": 2000}}, "direction": "forward"} "rejectedPlans": []}, "executionStats": {"executionSuccess": true, "nReturned": 2001, indicating the number of entries returned by the query "executionTimeMillis": 2001, indicating the execution time of the execution statement "totalKeysExamined": 0, "totalDocsExamined": 2000000, indicating the number of entries scanned by the document "executionStages": {"stage": "COLLSCAN" "filter": {"age": {"$lte": 2000}, "nReturned": 2001, "executionTimeMillisEstimate": 530, indicating the overall query time "works": 2000002, "advanced": 2001, "needTime": 1998000, "needYield": 0, "saveState": 15625, "restoreState": 15625, "isEOF": 1, "invalidates": 0, "direction": "forward", "docsExamined": 2000000}} "serverInfo": {"host": "meteor.yeecall.com", "port": 27027, "version": "3.2.8", "gitVersion": "ed70e33130c977bda0024c125b56d159573dbaf0"}, "ok": 1}

> db.person.ensureIndex ({age:1}) creates an index. "1": ascending order according to age; "- 1": descending order according to age

EnsureIndex ({field:1}, {name: "indexName"}) can specify the name of the index when creating the index

You can also create an index in the background: db.collection.ensureIndex ({name:-1}, {background:true})

{"createdCollectionAutomatically": false, "numIndexesBefore": 1, "numIndexesAfter": 2, "ok": 1}

> db.person.find ({"age": {"$lte": 2001}}) .explain ("executionStats")

{"queryPlanner": {"plannerVersion": 1, "namespace": "test.person" returns the queried table "indexFilterSet": false. For whether the query has an indexfilter "parsedQuery": {"age": {"$lte": 2001}}, "winningPlan": {query optimizer details of the optimal execution plan returned by the query "stage": "FETCH" "inputStage": {"stage": "IXSCAN", "keyPattern": {"age": 1}, "indexName": "age_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"age": [[- inf.0, 2001.0] "]}}," rejectedPlans ": []}} "executionStats": {"executionSuccess": true, "nReturned": 2002, indicating the number of entries returned by this query "executionTimeMillis": 28, indicating the overall query time of the query "totalKeysExamined": 2002, indicating the number of entries scanned by the index "totalDocsExamined": 2002, indicating the number of entries scanned by the document "executionStages": {"stage": "FETCH". "nReturned": 2002, "executionTimeMillisEstimate": 10, indicating the time when the query retrieved the result data from document based on index "works": 2003, "advanced": 2002, "needTime": 0, "needYield": 0, "saveState": 15, "restoreState": 15, "isEOF": 1, "invalidates": 0, "docsExamined": 2002, "alreadyHasObj": 0, "inputStage": {"stage": "IXSCAN", "nReturned": 2002 "executionTimeMillisEstimate": 0, indicating the time taken by the query to scan 2002 lines of index "works": 2003, "advanced": 2002, "needTime": 0, "needYield": 0, "saveState": 15, "restoreState": 15, "isEOF": 1, "invalidates": 0, "keyPattern": {"age": 1}, "indexName": "age_1", "isMultiKey": false, "isUnique": false, "isSparse": false: "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"age": ["- inf.0, 2001.0]]}," keysExamined ": 2002," dupsTested ": 0," dupsDropped ": 0," seenInvalidated ": 0}}," serverInfo ": {" host ":" meteor.yeecall.com "," port ": 27027," version ":" 3.2.8 " "gitVersion": "ed70e33130c977bda0024c125b56d159573dbaf0"}, "ok": 1}

>

It is the stage type that affects totalKeysExamined and totalDocsExamined.

The types are listed as follows:

COLLSCAN: full table scan

IXSCAN: index scan

FETCH: retrieves the specified document according to the index

SHARD_MERGE: merge the returned data of each shard

SORT: indicates that sorting is done in memory

LIMIT: use limit to limit the number of returns

SKIP: skip with skip

IDHACK: query for _ id

SHARDING_FILTER: query sharded data through mongos

COUNT: use db.coll.explain (). Count () and so on for count operation

Stage return when COUNTSCAN:count does not use Index for count

The stage return when COUNT_SCAN:count uses Index for count

SUBPLA: the stage return of the $or query that did not use the index

TEXT: stage return when querying with full-text index

PROJECTION: qualifies the return of stage when a field is returned

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.