Mongo performance analysisexplain() methods

MongoDB query analysis is an important tool for query statement performance analysis, which can ensure the validity of indexes established by us. Reference documentation

You can set the following parameters:

  • queryPlanner
  • executionStats
  • allPlansExecution

The executionStats pattern is commonly used

The preparatory work

We now have a table with a total of 5584702 pieces of data. The data structure and index status are as follows. Single structure is as follows:

{
	"_id" : ObjectId("5ff8287c47ec3c1813536e5c"),
	"x": 1, // increment"name" : "MACLEAN"."name1" : "MACLEAN"."name2" : "MACLEAN"."name3" : "MACLEAN"
}
Copy the code

Currently, no index is added to mongodb except the default _id index:

> db.testdatas.getIndexes()
[
	{
		"v": 2."key" : {
			"_id": 1}."name" : "_id_"."ns" : "test.testdatas"}]Copy the code

To begin testing

Run the following query statement:

> db.testdatas.find({}).limit(10).sort({x: 1}).explain("executionStats")
Copy the code

Returns the result

{
	"queryPlanner": {// Query plan information"plannerVersion" : 1,
		"namespace" : "test.testdatas"// The table queried by this query"indexFilterSet" : false, // Whether to apply the index to the query"parsedQuery": {},"winningPlan": {// Details of the optimal execution plan"stage" : "SORT"// Optimal execution plan stage"sortPattern" : {
				"x": 1}."limitAmount" : 10,
			"inputStage": {// subphase list"stage" : "SORT_KEY_GENERATOR"."inputStage" : {
					"stage" : "COLLSCAN"."direction" : "forward"}}},"rejectedPlans": [] // Other execution plans},"executionStats": {// Query result execution status"executionSuccess" : true."nReturned": 10, // Number of documents that match the search criteria"executionTimeMillis": 14920, // Total time in milliseconds to select the query plan and execute the query"totalKeysExamined": 0, // Index scan entries"totalDocsExamined": 5584702, // Number of documents scanned"executionStages" : {
			"stage" : "SORT"."nReturned" : 10,
			"executionTimeMillisEstimate" : 14071,
			"works" : 5584716,
			"advanced" : 10,
			"needTime" : 5584705,
			"needYield": 0."saveState" : 44054,
			"restoreState" : 44054,
			"isEOF" : 1,
			"invalidates": 0."sortPattern" : {
				"x": 1}."memUsage" : 1160,
			"memLimit" : 33554432,
			"limitAmount" : 10,
			"inputStage" : {
				"stage" : "SORT_KEY_GENERATOR"."nReturned" : 5584702,
				"executionTimeMillisEstimate" : 11874,
				"works" : 5584705,
				"advanced" : 5584702,
				"needTime": 2."needYield": 0."saveState" : 44054,
				"restoreState" : 44054,
				"isEOF" : 1,
				"invalidates": 0."inputStage" : {
					"stage" : "COLLSCAN"."nReturned" : 5584702,
					"executionTimeMillisEstimate" : 7506,
					"works" : 5584704,
					"advanced" : 5584702,
					"needTime" : 1,
					"needYield": 0."saveState" : 44054,
					"restoreState" : 44054,
					"isEOF" : 1,
					"invalidates": 0."direction" : "forward"."docsExamined": 5584702}}}},"serverInfo" : {
		"host" : "localhost"."port" : 27017,
		"version" : "4.0.8"."gitVersion" : "9b00696ed75f65e1ebc8d635593bed79b290cfbb"
	},
	"ok": 1}Copy the code

ExecutionStats returns a level by level analysis

Step one, analyze executionStats

The field name (Refer to the above query results)
executionTimeMillis Total query statement time. The total time of 5584702 query statements was 14920 ms
totalKeysExamined Index scan entries. If the above result is 0, the index may not be used in this query
totalDocsExamined Number of scanned documents. The result is 5584702, indicating that this query has scanned all documents
nReturned Returns the number of results. The above results return 10

 

Second, analyze executionStages

NReturned, totalKeysExamined, totalDocsExamined

Represents the entry returned by the query, index scan entry, and document scan entry. All of this intuitively affects executionTimeMillis, the less we have to scan the faster.

For a query, our ideal state is: nReturned = totalKeysExamined = totalDocsExamined

Step 3: Analyze the stage state

The field name (Refer to the above query results)
stage Query the plan type. The above result SORT indicates that a SORT is done in memory
inputStage Used to describe substages and provide document and index keywords for their parent stages

So what influences totalKeysExamined and totalDocsExamined? Is the type of stage. The types are listed as follows:

Document SHARD_MERGE merge data returned by fragments in memory LIMIT: use LIMIT to LIMIT the number of returned data SKIP: SHARDING_FILTER: query fragmented data through Mongos COUNT: use db.col.explain ().count() to calculate COUNT. COUNTSCAN: The count does not use the Index to the stage when the count returned COUNT_SCAN: count using the Index on the stage when the count returned SUBPLA: not used to the Index of $stage returns TEXT or queries: Stage return PROJECTION: The return of stage when a field is qualified to be returned

Execute the same query after adding the index

Add a {x: 1} index to the collection

> db.testdatas.createIndex({x: 1})
{
	"createdCollectionAutomatically" : false."numIndexesBefore" : 1,
	"numIndexesAfter": 2."ok": 1}Copy the code

Execute the same query as above:

> db.testdatas.find({}).limit(10).sort({x: 1}).explain("executionStats")
Copy the code

Return result:

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.testdatas"."indexFilterSet" : false."parsedQuery": {},"winningPlan" : {
			"stage" : "LIMIT"."limitAmount" : 10,
			"inputStage" : {
				"stage" : "FETCH"."inputStage" : {
					"stage" : "IXSCAN"."keyPattern" : {
						"x": 1}."indexName" : "x_1"."isMultiKey" : false."multiKeyPaths" : {
						"x": []},"isUnique" : false."isSparse" : false."isPartial" : false."indexVersion": 2."direction" : "forward"."indexBounds" : {
						"x" : [
							"[MinKey, MaxKey]"}}}},"rejectedPlans": []},"executionStats" : {
		"executionSuccess" : true."nReturned" : 10,
		"executionTimeMillis" : 1,
		"totalKeysExamined" : 10,
		"totalDocsExamined" : 10,
		"executionStages" : {
			"stage" : "LIMIT"."nReturned" : 10,
			"executionTimeMillisEstimate": 0."works": 11."advanced" : 10,
			"needTime": 0."needYield": 0."saveState": 0."restoreState": 0."isEOF" : 1,
			"invalidates": 0."limitAmount" : 10,
			"inputStage" : {
				"stage" : "FETCH"."nReturned" : 10,
				"executionTimeMillisEstimate": 0."works" : 10,
				"advanced" : 10,
				"needTime": 0."needYield": 0."saveState": 0."restoreState": 0."isEOF": 0."invalidates": 0."docsExamined" : 10,
				"alreadyHasObj": 0."inputStage" : {
					"stage" : "IXSCAN"."nReturned" : 10,
					"executionTimeMillisEstimate": 0."works" : 10,
					"advanced" : 10,
					"needTime": 0."needYield": 0."saveState": 0."restoreState": 0."isEOF": 0."invalidates": 0."keyPattern" : {
						"x": 1}."indexName" : "x_1"."isMultiKey" : false."multiKeyPaths" : {
						"x": []},"isUnique" : false."isSparse" : false."isPartial" : false."indexVersion": 2."direction" : "forward"."indexBounds" : {
						"x" : [
							"[MinKey, MaxKey]"]},"keysExamined" : 10,
					"seeks" : 1,
					"dupsTested": 0."dupsDropped": 0."seenInvalidated": 0}}}},"serverInfo" : {
		"host" : "localhost"."port" : 27017,
		"version" : "4.0.8"."gitVersion" : "9b00696ed75f65e1ebc8d635593bed79b290cfbb"
	},
	"ok": 1}Copy the code

The results were analyzed after indexing

Step one, analyze executionStats

ExecutionTimeMillis: Total query time: 1ms totalKeysExamined: index scan entry 10 totalDocsExamined: document scan entry 10

Second, analyze executionStages

NReturned = 10 totalKeysExamined = 10 totalDocsExamined = 10 nReturned = totalKeysExamined = totalDocsExamined

Step 3: Analyze the stage state

Limit+ (Fetch+ixscan)

Good query composition

For general queries, we recommend stage combinations (using indexes whenever possible) :

Fetch+IDHACK Fetch+ IXSCAN Limit+ (Fetch+ IXscan) PROJECTION+ IXscan SHARDING_FITER+ IXscan COUNT_SCAN