mongodb数据库50w数据优化实例 通过慢查询和建立索引加快查询速度

q
qimoe
发布于 9 个月前

我常使用mongodb来做数据库,但是随着数据量的剧增,查询变得越来越慢,于是这个时候就需要我们通过慢查询来找出慢的原因,并且通过建立索引的方式加快数据查询的速度。

第一步、开启mongodb慢查询日志

可以直接在mongoshell中开启慢查询日志。

db.setProfilingLevel(2)
//或
db.setProfilingLevel(1, { slowms: 20 })
// 也可直接先选择一个数据库,然后开启日志,只需要先use db
use querydata
db.setProfilingLevel(2)
// 或
db.setProfilingLevel(1, { slowms: 20 })
// 后面可以关闭慢查询,设置成0即可。
db.setProfilingLevel(0)

我们来看看官网对setProfilingLevel的介绍:

db.setProfilingLevel()方法具有以下形式:

db.setProfilingLevel(<Level>,<Options>)

参数

参数

类型

描述

level

integer

配置数据库探查器级别。提供以下探查器级别:

level

描述

0

探查器已关闭,并且不收集任何数据。这是默认的探查器级别。

1

剖析器收集的数据花费的时间超过的值slowms

2

探查器收集所有操作的数据。

由于无法在mongos上使用概要分析, db.setProfilingLevel()因此不能用于将概要分析级别设置0以外的值在一个mongos 实例上。

options

document or integer

可选的。接受整数或options对象。如果将整数值作为options参数而不是对象作为参数传递,则该值将分配给slowms。提供以下选项:

slowms

默认值:100

类型:integer

慢查询的操作时间阈值,单位为毫秒。运行时间超过此阈值的操作被认为是缓慢的

logLevel设置为0,MongoDB的记录速度慢 ,在所确定的速度诊断日志slowOpSampleRate操作 。从MongoDB 4.2开始,副本集的辅助副本记录所有花费比慢操作阈值更长的时间来应用的oplog条目消息,而不管采样率如何。

在较高的logLevel设置下,所有操作都将在诊断日志中显示,而与它们的延迟无关,但以下情况除外:次要对象记录慢速oplog条目消息。辅助服务器仅记录慢速操作日志条目;增加logLevel不会记录所有操作日志条目。

对于mongod实例,该设置会影响诊断日志和探查器(如果启用)。

对于mongos情况下,该设置会影响诊断日志,而不是探查,因为分析是不可用的。

注意

此参数影响与配置文件选项相同的设置slowOpThresholdMs

sampleRate

默认值:1.0

类型:double

应当分析或记录的慢速操作的一部分。 sampleRate接受介于0和1之间(含)的值。

对于mongod实例,该设置会影响诊断日志和探查器(如果启用)。

对于mongos情况下,该设置会影响诊断日志,而不是探查,因为分析是不可用的。

注意

此参数影响与配置选项相同的设置 slowOpSampleRate

filter

类型:对象

一个过滤器表达式,用于控制分析和记录哪些操作。过滤器表达式中的字段可以是探查器输出中的任何字段

对于mongod实例,该设置会影响诊断日志和探查器(如果启用)。

对于mongos情况下,该设置会影响诊断日志,而不是探查,因为分析是不可用的。

有关用于控制记录的操作的过滤器的示例,请参阅《设置过滤器以确定分析的操作》

版本4.4.2中的新功能。

注意

设置性能过滤器后, slowms和 sampleRate选项不会影响诊断日志或探查器。

第二步、开启慢查询之后,查询日志情况

本节显示对system.profile 集合的查询示例。有关查询输出的说明,请参见 Database Profiler Output

要返回system.profile 集合中最新的10条日志条目,请运行类似于以下内容的查询:

db.system.profile.find().limit(10).sort( { ts : -1 } ).pretty()

要返回除命令操作($cmd)之外的所有其他操作,请运行类似于以下内容的查询:

db.system.profile.find( { op: { $ne : 'command' } } ).pretty()

要返回特定集合的操作,请运行类似于以下内容的查询。此示例返回mydb数据库 test集合中的操作:

db.system.profile.find( { ns : 'mydb.test' } ).pretty()

要返回比5毫秒慢的操作,请运行类似于以下内容的查询:

db.system.profile.find( { millis : { $gt : 5 } } ).pretty()

要返回特定时间范围内的信息,请运行类似于以下内容的查询:

db.system.profile.find({
  ts : {
    $gt: new ISODate("2012-12-09T03:00:00Z"),
    $lt: new ISODate("2012-12-09T03:40:00Z")
  }
}).pretty()

下面的示例查看时间范围,user 从输出中取消该字段以使其更易于阅读,并根据每个操作花费的时间对结果进行排序:

db.system.profile.find({
  ts : {
    $gt: new ISODate("2011-07-12T03:00:00Z"),
    $lt: new ISODate("2011-07-12T03:40:00Z")
  }
}, { user: 0 }).sort( { millis: -1 } )

显示最近五次活动

在已启用概要分析的数据库上,外壳程序中的帮助程序将显示5个最近执行的操作,这些操作至少花费1毫秒来执行。 从外壳发出,如下所示:

show profile

探查器开销

启用分析后,分析会影响数据库性能,尤其是在配置为2的 分析级别或使用低值且分析级别为1的情况下。分析还会消耗磁盘空间,因为它既会记录到system.profile 集合中,还会记录到磁盘上MongoDB logfile。在生产部署上配置和启用探查器之前,请仔细考虑所有性能和安全隐患。

第三步、分析日志

我的案例如下:

> db.system.profile.find().limit(10).sort( { ts : -1 } ).pretty()
{
	"op" : "query",
	"ns" : "querydata.system.profile",
	"command" : {
		"find" : "system.profile",
		"filter" : {
			
		},
		"limit" : 10,
		"singleBatch" : false,
		"sort" : {
			"ts" : -1
		},
		"lsid" : {
			"id" : UUID("56a3c76e-3643-462b-b452-1a00a4849ec8")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 0,
	"docsExamined" : 304,
	"hasSortStage" : true,
	"cursorExhausted" : true,
	"numYield" : 0,
	"nreturned" : 10,
	"queryHash" : "395500AB",
	"planCacheKey" : "395500AB",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(1)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		}
	},
	"flowControl" : {
		
	},
	"responseLength" : 8858,
	"protocol" : "op_msg",
	"millis" : 0,
	"planSummary" : "COLLSCAN",
	"execStats" : {
		"stage" : "SORT",
		"nReturned" : 10,
		"executionTimeMillisEstimate" : 0,
		"works" : 317,
		"advanced" : 10,
		"needTime" : 306,
		"needYield" : 0,
		"saveState" : 0,
		"restoreState" : 0,
		"isEOF" : 1,
		"sortPattern" : {
			"ts" : -1
		},
		"memLimit" : 104857600,
		"limitAmount" : 10,
		"type" : "simple",
		"totalDataSizeSorted" : 269112,
		"usedDisk" : false,
		"inputStage" : {
			"stage" : "COLLSCAN",
			"nReturned" : 304,
			"executionTimeMillisEstimate" : 0,
			"works" : 306,
			"advanced" : 304,
			"needTime" : 1,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"direction" : "forward",
			"docsExamined" : 304
		}
	},
	"ts" : ISODate("2021-01-16T16:48:58.552Z"),
	"client" : "127.0.0.1",
	"appName" : "MongoDB Shell",
	"allUsers" : [
		{
			"user" : "admin",
			"db" : "admin"
		}
	],
	"user" : "admin@admin"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"_id" : {
						"$in" : [
							ObjectId("5f969e85ee3680299115c833"),
							ObjectId("5f969e85ee3680299115c834")
						]
					}
				}
			},
			{
				"$addFields" : {
					"id" : "$_id"
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("a8311544-acc3-41d6-a9bf-adc3aabd36bd")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 3,
	"docsExamined" : 2,
	"cursorExhausted" : true,
	"numYield" : 0,
	"nreturned" : 2,
	"queryHash" : "5A33D66A",
	"planCacheKey" : "1CBBCBDE",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(2)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		}
	},
	"flowControl" : {
		
	},
	"responseLength" : 521,
	"protocol" : "op_msg",
	"millis" : 0,
	"planSummary" : "IXSCAN { _id: 1 }",
	"ts" : ISODate("2021-01-16T16:48:25.449Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"person" : ObjectId("5f969e85ee3680299115c844")
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("a8311544-acc3-41d6-a9bf-adc3aabd36bd")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 0,
	"docsExamined" : 466558,
	"cursorExhausted" : true,
	"numYield" : 466,
	"nreturned" : 2,
	"queryHash" : "DF101415",
	"planCacheKey" : "DF101415",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(468)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(468)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(467)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(467)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		}
	},
	"flowControl" : {
		
	},
	"storage" : {
		
	},
	"responseLength" : 489,
	"protocol" : "op_msg",
	"millis" : 368,
	"planSummary" : "COLLSCAN",
	"ts" : ISODate("2021-01-16T16:48:25.448Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"_id" : {
						"$in" : [
							ObjectId("5f969e85ee3680299115c835"),
							ObjectId("5f969e85ee3680299115c836")
						]
					}
				}
			},
			{
				"$addFields" : {
					"id" : "$_id"
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("136c7133-782a-4693-bdea-56a3e4de6898")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 3,
	"docsExamined" : 2,
	"cursorExhausted" : true,
	"numYield" : 0,
	"nreturned" : 2,
	"queryHash" : "5A33D66A",
	"planCacheKey" : "1CBBCBDE",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(2)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		}
	},
	"flowControl" : {
		
	},
	"responseLength" : 519,
	"protocol" : "op_msg",
	"millis" : 0,
	"planSummary" : "IXSCAN { _id: 1 }",
	"ts" : ISODate("2021-01-16T16:48:25.447Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"person" : ObjectId("5f969e85ee3680299115c845")
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("136c7133-782a-4693-bdea-56a3e4de6898")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 0,
	"docsExamined" : 466558,
	"cursorExhausted" : true,
	"numYield" : 466,
	"nreturned" : 2,
	"queryHash" : "DF101415",
	"planCacheKey" : "DF101415",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(468)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(468)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(467)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(467)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		}
	},
	"flowControl" : {
		
	},
	"storage" : {
		
	},
	"responseLength" : 487,
	"protocol" : "op_msg",
	"millis" : 359,
	"planSummary" : "COLLSCAN",
	"ts" : ISODate("2021-01-16T16:48:25.446Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"_id" : {
						"$in" : [
							ObjectId("5f969e85ee3680299115c82d"),
							ObjectId("5f969e85ee3680299115c82e")
						]
					}
				}
			},
			{
				"$addFields" : {
					"id" : "$_id"
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("475d00eb-26e6-43c9-9af7-05c3025c7eb9")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 3,
	"docsExamined" : 2,
	"cursorExhausted" : true,
	"numYield" : 0,
	"nreturned" : 2,
	"queryHash" : "5A33D66A",
	"planCacheKey" : "1CBBCBDE",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(2)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		}
	},
	"flowControl" : {
		
	},
	"responseLength" : 528,
	"protocol" : "op_msg",
	"millis" : 0,
	"planSummary" : "IXSCAN { _id: 1 }",
	"ts" : ISODate("2021-01-16T16:48:25.336Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"person" : ObjectId("5f969e85ee3680299115c841")
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("475d00eb-26e6-43c9-9af7-05c3025c7eb9")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 0,
	"docsExamined" : 466558,
	"cursorExhausted" : true,
	"numYield" : 470,
	"nreturned" : 2,
	"queryHash" : "DF101415",
	"planCacheKey" : "DF101415",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(472)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(472)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(471)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(471)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		}
	},
	"flowControl" : {
		
	},
	"storage" : {
		
	},
	"responseLength" : 496,
	"protocol" : "op_msg",
	"millis" : 722,
	"planSummary" : "COLLSCAN",
	"ts" : ISODate("2021-01-16T16:48:25.334Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"_id" : {
						"$in" : [
							ObjectId("5f969e85ee3680299115c831"),
							ObjectId("5f969e85ee3680299115c832")
						]
					}
				}
			},
			{
				"$addFields" : {
					"id" : "$_id"
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("ac1e533a-7ed4-47bd-a5c6-0498853b5fac")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 3,
	"docsExamined" : 2,
	"cursorExhausted" : true,
	"numYield" : 0,
	"nreturned" : 2,
	"queryHash" : "5A33D66A",
	"planCacheKey" : "1CBBCBDE",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(2)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		}
	},
	"flowControl" : {
		
	},
	"responseLength" : 542,
	"protocol" : "op_msg",
	"millis" : 0,
	"planSummary" : "IXSCAN { _id: 1 }",
	"ts" : ISODate("2021-01-16T16:48:25.319Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"person" : ObjectId("5f969e85ee3680299115c843")
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("ac1e533a-7ed4-47bd-a5c6-0498853b5fac")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 0,
	"docsExamined" : 466558,
	"cursorExhausted" : true,
	"numYield" : 474,
	"nreturned" : 2,
	"queryHash" : "DF101415",
	"planCacheKey" : "DF101415",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(476)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(476)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(475)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(475)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		}
	},
	"flowControl" : {
		
	},
	"storage" : {
		
	},
	"responseLength" : 510,
	"protocol" : "op_msg",
	"millis" : 699,
	"planSummary" : "COLLSCAN",
	"ts" : ISODate("2021-01-16T16:48:25.318Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}
{
	"op" : "command",
	"ns" : "querydata.personvalues",
	"command" : {
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"_id" : {
						"$in" : [
							ObjectId("5f969e85ee3680299115c82f"),
							ObjectId("5f969e85ee3680299115c830")
						]
					}
				}
			},
			{
				"$addFields" : {
					"id" : "$_id"
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("5a763c90-5502-4c8b-b407-fd27eeeb119c")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 3,
	"docsExamined" : 2,
	"cursorExhausted" : true,
	"numYield" : 0,
	"nreturned" : 2,
	"queryHash" : "5A33D66A",
	"planCacheKey" : "1CBBCBDE",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(2)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(2)
			}
		}
	},
	"flowControl" : {
		
	},
	"responseLength" : 521,
	"protocol" : "op_msg",
	"millis" : 0,
	"planSummary" : "IXSCAN { _id: 1 }",
	"ts" : ISODate("2021-01-16T16:48:25.307Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}

我先找到其中一个明显耗时很长的来优化,millis就是指耗时,我找到一个出来:

{
	"op" : "command",
	"ns" : "querydata.personvalues", //操作的数据库和集合
	"command" : {  // 命令
		"aggregate" : "personvalues",
		"pipeline" : [
			{
				"$match" : {
					"person" : ObjectId("5f969e85ee3680299115c843")
				}
			}
		],
		"cursor" : {
			
		},
		"lsid" : {
			"id" : UUID("ac1e533a-7ed4-47bd-a5c6-0498853b5fac")
		},
		"$db" : "querydata"
	},
	"keysExamined" : 0, //mongodb是否使用索引,并且扫描多少个索引。
	"docsExamined" : 466558, //mongodb为了完成操作,检索了多少数据,这里我们检索了46个数据,明显没做索引。
	"cursorExhausted" : true,
	"numYield" : 474, // 让步次数,操作时让其他的操作完成的次数。
	"nreturned" : 2, // 返回数据量
	"queryHash" : "DF101415",
	"planCacheKey" : "DF101415",
	"locks" : {
		"ReplicationStateTransition" : {
			"acquireCount" : {
				"w" : NumberLong(476)
			}
		},
		"Global" : {
			"acquireCount" : {
				"r" : NumberLong(476)
			}
		},
		"Database" : {
			"acquireCount" : {
				"r" : NumberLong(475)
			}
		},
		"Collection" : {
			"acquireCount" : {
				"r" : NumberLong(475)
			}
		},
		"Mutex" : {
			"acquireCount" : {
				"r" : NumberLong(1)
			}
		}
	},
	"flowControl" : {
		
	},
	"storage" : {
		
	},
	"responseLength" : 510,
	"protocol" : "op_msg",
	"millis" : 699, // 从 MongoDB 开始到结束耗费的时间
	"planSummary" : "COLLSCAN",
	"ts" : ISODate("2021-01-16T16:48:25.318Z"),
	"client" : "127.0.0.1",
	"allUsers" : [
		{
			"user" : "querydata",
			"db" : "querydata"
		}
	],
	"user" : "querydata@querydata"
}

如此我们就一目了然,我需要给querydata.personvalues中的person字段加索引,于是我们去mongoshell中加索引。

第四步、增加索引

官网推荐使用db.collection.createIndex() 来增加索引。

类似命令行如下:

db.collection.createIndex(
  {
      "a": 1
  },
  {
      unique: true,
      sparse: true,
      expireAfterSeconds: 3600
  }
)

参数详见:官网文档

在这里,我需要给personvalues集合中的person字段加索引:

db.personvalues.createIndex({"person":1})

创建之后,我们来查询下索引情况:

db.personvalues.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_"
	},
	{
		"v" : 2,
		"key" : {
			"person" : 1
		},
		"name" : "person_1"
	}
]
db.personvalues.totalIndexSize()
8466432

这样,我们就优化好了一个数据库查询。

相关文章推荐
评论区
暂未开放
相关文章推荐