MongoDB 聚合查询优化

一、性能分析

1. 参考文档

• https://developer.aliyun.com/article/777617
• https://blog.csdn.net/leshami/article/details/53521990?spm=a2c6h.12873639.article-detail.5.ec165124uwgMuO
• 官方： https://www.mongodb.com/docs/manual/reference/explain-results/?spm=a2c6h.12873639.article-detail.4.ec165124uwgMuO#executionstats

2. 性能分析工具、命令

• 函数explain(verbose),参数verbose有以下3种值:

  1. queryPlanner(默认)
     MongoDB运行查询优化器对当前的查询进行评估并选择一个最佳的查询计划
  2. executionStats
     mongoDB运行查询优化器对当前的查询进行评估并选择一个最佳的查询计划进行执行
     在执行完毕后返回这个最佳执行计划执行完成时的相关统计信息
  3. allPlansExecution
     即按照最佳的执行计划执行以及列出统计信息
     如果有多个查询计划，还会列出这些非最佳执行计划部分的统计信息

• 聚合查询

  • db.collection.explain(verbose).aggregate();

• 查询

  • db.collection.find().explain(verbose);

3. 参数说明情参考上述文档

二、问题描述

1. 测试查询数据列表耗时

 --- PASS: TestListChainedNft/NoQueryParams (45.14s)

三、排查当前SQL执行过程

1. 命令

• 执行命令

  > db.ethNftTokenHolders.explain("executionStats").aggregate([{$lookup:{from: "ethNftMeta",localField: "token_id",foreignField: "token_id",as: "match_index"}},{$match:{"chain_id":65534}},{$skip:1},{$limit:10}]);
  

• 返回执行过程

  {
  "explainVersion" : "1",
  "stages" : [
  	{
  		"$cursor" : {
  			"queryPlanner" : {
  				"namespace" : "foundation.ethNftTokenHolders",
  				"indexFilterSet" : false,
  				"parsedQuery" : {
  					"chain_id" : {
  						"$eq" : 65534
  					}
  				},
  				"queryHash" : "D1B82B8E",
  				"planCacheKey" : "59DE9157",
  				"maxIndexedOrSolutionsReached" : false,
  				"maxIndexedAndSolutionsReached" : false,
  				"maxScansToExplodeReached" : false,
  				"winningPlan" : {   // 这儿是选择的最佳查询计划
  					"stage" : "LIMIT",
  					"limitAmount" : 10,
  					"inputStage" : {
  						"stage" : "SKIP",
  						"skipAmount" : 0,
  						"inputStage" : {
  							"stage" : "COLLSCAN",   // 文档扫描(Collection Scan)即:未命中索引,全表扫描
  							"filter" : {
  								"chain_id" : {
  									"$eq" : 65534
  								}
  							},
  							"direction" : "forward"
  						}
  					}
  				},
  				"rejectedPlans" : [ ]
  			},
  			"executionStats" : {   // 具体执行并统计信息
  				"executionSuccess" : true,
  				"nReturned" : 10,  // 执行返回的文档数
  				"executionTimeMillis" : 47, 执行时间（ms）
  				"totalKeysExamined" : 0, // 索引扫描条数
  				"totalDocsExamined" : 11, // 文档扫描条数
  				"executionStages" : {  // 执行步骤,返回最佳执行计划执行完成时的相关统计信息
  					"stage" : "LIMIT",
  					"nReturned" : 10,
  					"executionTimeMillisEstimate" : 0,
  					"works" : 13,
  					"advanced" : 10,
  					"needTime" : 2,
  					"needYield" : 0,
  					"saveState" : 1,
  					"restoreState" : 1,
  					"isEOF" : 1,
  					"limitAmount" : 10,
  					"inputStage" : {
  						"stage" : "SKIP",
  						"nReturned" : 10,
  						"executionTimeMillisEstimate" : 0,
  						"works" : 12,
  						"advanced" : 10,
  						"needTime" : 2,
  						"needYield" : 0,
  						"saveState" : 1,
  						"restoreState" : 1,
  						"isEOF" : 0,
  						"skipAmount" : 0,
  						"inputStage" : {
  							"stage" : "COLLSCAN",
  							"filter" : {
  								"chain_id" : {
  									"$eq" : 65534
  								}
  							},
  							"nReturned" : 11,
  							"executionTimeMillisEstimate" : 0,
  							"works" : 12,
  							"advanced" : 11,
  							"needTime" : 1,
  							"needYield" : 0,
  							"saveState" : 1,
  							"restoreState" : 1,
  							"isEOF" : 0,
  							"direction" : "forward",
  							"docsExamined" : 11
  						}
  					}
  				}
  			}
  		},
  		"nReturned" : NumberLong(10),
  		"executionTimeMillisEstimate" : NumberLong(0)
  	},
  	{
  		"$lookup" : {
  			"from" : "ethNftMeta",
  			"as" : "match_index",
  			"localField" : "token_id",
  			"foreignField" : "token_id"
  		},
  		"totalDocsExamined" : NumberLong(54530),
  		"totalKeysExamined" : NumberLong(0),
  		"collectionScans" : NumberLong(20),
  		"indexesUsed" : [ ],
  		"nReturned" : NumberLong(10),
  		"executionTimeMillisEstimate" : NumberLong(47)
  	}
  ],
  "serverInfo" : {
  	"host" : "8e8a7b4cfab5",
  	"port" : 27017,
  	"version" : "5.0.4",
  	"gitVersion" : "62a84ede3cc9a334e8bc82160714df71e7d3a29e"
  },
  "serverParameters" : {
  	"internalQueryFacetBufferSizeBytes" : 104857600,
  	"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
  	"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
  	"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
  	"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
  	"internalQueryProhibitBlockingMergeOnMongoS" : 0,
  	"internalQueryMaxAddToSetBytes" : 104857600,
  	"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
  },
  "command" : {
  	"aggregate" : "ethNftTokenHolders",
  	"pipeline" : [
  		{
  			"$lookup" : {
  				"from" : "ethNftMeta",
  				"localField" : "token_id",
  				"foreignField" : "token_id",
  				"as" : "match_index"
  			}
  		},
  		{
  			"$match" : {
  				"chain_id" : 65534
  			}
  		},
  		{
  			"$skip" : 1
  		},
  		{
  			"$limit" : 10
  		}
  	],
  	"cursor" : {
  
  	},
  	"$db" : "foundation"
  },
  "ok" : 1
  

}
```

2. 分析

• 文档扫描(Collection Scan)即:未命中索引,全表扫描。显然全表扫描，没有使用索引

四、优化

1. 管道优化策略

• ( $projector $unsetor $addFieldsor $set)+$match序列优化
• $sort+$match序列优化
• $redact+$match序列优化
• $project/ $unset+$skip序列优化
• $sort+$limit聚结
• $limit+$limit聚结
• $skip+$skip聚结
• $match+$match聚结
• $lookup+$unwind聚结
• 参考官方文档管道聚合优化
  • https://www.mongodb.com/docs/manual/core/aggregation-pipeline-optimization/#std-label-aggregation-pipeline-optimization-indexes-and-filters

2. 增加查询条件的索引

• 增加索引

  • 普通索引

    # 创建索引名字 1正序，-1 倒序
    db.col.createIndex({"title":1}) 
    

  • 聚合索引

    # 多个字段创建索引（关系型数据库中称作复合索引)
    db.col.createIndex({"title":1,"description":-1})
    

• 查询索引

  # 查看索引
  db.col.getIndexes();
  
  # 查看集合索引大小
  db.col.totalIndexSize();
  
  

• 删除索引

  # 删除集合所有索引;
  db.col.dropIndexes();
  
  # 删除集合指定索引;
  db.col.dropIndex("索引名称");
  

• 文档

  • https://www.runoob.com/mongodb/mongodb-indexing.html

五、优化结果

TestListChainedNft/NoQueryParams (1.80s)

END