2021-02-23 记一次线上索引优化

于 2021-02-24 22:39:00 发布
阅读量110 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/xljiayou_/article/details/114044995
版权

背景

最近在做社区tab开发，新加了一个帖子排序：按回复时间排序。新回复时间是新加的字段。半个月左右后，测试说接口有时候会炸弹（失败），响应比较慢。
数据库使用的mongodb。

问题

我看了查询，写了一个测试查询，expain()，发现使用的索引是其他人创建的索引，我没有专门为查询条件建索引。

解决方案

将查询发给了DBA，向他请教了下，并对两种方案的执行计划进行了对比，记录一下。
查询

db.posts.find({tid:23, c_type:{$in:[1]}, status:{$in:[1,2,3]}, ct:{$gte:1612835946}}).sort({topic_reply_lut:-1}).skip(0).limit(10).explain()

对话
DBA： 你这个最大查询结果是多少？不加limit
我：2-3万
DBA：tid:23, c_type:{$in:[1]}, status:{$in:[1,2,3]} 如果这3个条件筛选出来数据了不大 就可以 (tid,c_type,status,topic_reply_lut,ct) 组合。如果数据量大，(tid,c_type,status,ct,topic_reply_lut)，这样加排序就走不了索引。
DBA：如果加上ct条件，最总出来的结果比较少，排序如果不走索引应该问题也不大。
我：(tid,c_type,status,topic_reply_lut,ct) 这个查询的时候索引可以用到ct吗？
DBA：应该可以
我：加上ct 有2-3w数据
DBA：那就这样加吧。你最好在测试环境试试这两种索引的性能，分别看看执行计划。
DBA：主要就看两个索引，文档和索引分别扫描了多少行， 取limit10 如果都是扫描10行，那就是比较好的。
我：好。（结果在最下面）
DBA：你可以强制让走另一个索引，看看效果。
我：（结果在最下面）
DBA：从这个上就能看出来，这个索引没有完全覆盖，ct范围把后续排序阻断了，导致limit 10也就不起作用了。
DBA：像第二个就是如果最终结果有50000结果，就会每次扫描50000，然后在取10条，性能就很差。
DBA：用limit 必须索引全部覆盖，才能实现 取10条，扫描10条。

我：(tid,c_type,status,topic_reply_lut,ct)，topic_reply_lut在sort里，为什么ct也可以用到索引。
DBA：业务索引ct范围查询是在最后，所以不会影响。覆盖索引中查询条件只要有 范围查询，不等于，非等值，就会截断后续索引。

我：topic_reply_lut没在where里， topic_reply_lut和ct也可以用到所以，这个意思
DBA:但是在一个索引文件里面啊

DBA：你把ct改成=值查询，应该就会走第二个查询，而且应该 都会只扫描 10行
（我验证了确实）
在这里插入图片描述

DBA：正常情况筛选先 where 然后在排序。
我：我查询里是范围查询，截断了，没有用到索引里的topic_reply_lut
DBA：对的，这样就会导致 limit 10也会失效，虽然只返回了10条， 但是会把索引结果查询出来，再取10条，所以一般组合索引顺序，只要是范围或者是非等值的都排最后

DBA：所以你以后看执行计划只用关注这个位置就行，其它的没啥意义。
在这里插入图片描述

我：太香了，学到了。

(tid,c_type,status,topic_reply_lut,ct) 执行计划如下

post:PRIMARY> db.posts.find({tid:3, c_type:{$in:[1]}, status:{$in:[1,2,3]}, ct:{$gte:1600000000}}).sort({topic_reply_lut:-1}).skip(0).limit(10).explain(executionStats=true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "post.posts",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"tid" : {
						"$eq" : 3
					}
				},
				{
					"ct" : {
						"$gte" : 1600000000
					}
				},
				{
					"c_type" : {
						"$in" : [
							1
						]
					}
				},
				{
					"status" : {
						"$in" : [
							1,
							2,
							3
						]
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "LIMIT",
			"limitAmount" : 10,
			"inputStage" : {
				"stage" : "FETCH",
				"inputStage" : {
					"stage" : "SORT_MERGE",
					"sortPattern" : {
						"topic_reply_lut" : -1
					},
					"inputStages" : [
						{
							"stage" : "IXSCAN",
							"keyPattern" : {
								"tid" : 1,
								"c_type" : 1,
								"status" : 1,
								"topic_reply_lut" : -1,
								"ct" : -1
							},
							"indexName" : "tid_1_c_type_1_status_1_topic_reply_lut_-1_ct_-1",
							"isMultiKey" : false,
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 1,
							"direction" : "forward",
							"indexBounds" : {
								"tid" : [
									"[3.0, 3.0]"
								],
								"c_type" : [
									"[1.0, 1.0]"
								],
								"status" : [
									"[1.0, 1.0]"
								],
								"topic_reply_lut" : [
									"[MaxKey, MinKey]"
								],
								"ct" : [
									"[inf.0, 1600000000.0]"
								]
							}
						},
						{
							"stage" : "IXSCAN",
							"keyPattern" : {
								"tid" : 1,
								"c_type" : 1,
								"status" : 1,
								"topic_reply_lut" : -1,
								"ct" : -1
							},
							"indexName" : "tid_1_c_type_1_status_1_topic_reply_lut_-1_ct_-1",
							"isMultiKey" : false,
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 1,
							"direction" : "forward",
							"indexBounds" : {
								"tid" : [
									"[3.0, 3.0]"
								],
								"c_type" : [
									"[1.0, 1.0]"
								],
								"status" : [
									"[2.0, 2.0]"
								],
								"topic_reply_lut" : [
									"[MaxKey, MinKey]"
								],
								"ct" : [
									"[inf.0, 1600000000.0]"
								]
							}
						},
						{
							"stage" : "IXSCAN",
							"keyPattern" : {
								"tid" : 1,
								"c_type" : 1,
								"status" : 1,
								"topic_reply_lut" : -1,
								"ct" : -1
							},
							"indexName" : "tid_1_c_type_1_status_1_topic_reply_lut_-1_ct_-1",
							"isMultiKey" : false,
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 1,
							"direction" : "forward",
							"indexBounds" : {
								"tid" : [
									"[3.0, 3.0]"
								],
								"c_type" : [
									"[1.0, 1.0]"
								],
								"status" : [
									"[3.0, 3.0]"
								],
								"topic_reply_lut" : [
									"[MaxKey, MinKey]"
								],
								"ct" : [
									"[inf.0, 1600000000.0]"
								]
							}
						}
					]
				}
			}
		},
		"rejectedPlans" : [
			{
				"stage" : "SORT",
				"sortPattern" : {
					"topic_reply_lut" : -1
				},
				"limitAmount" : 10,
				"inputStage" : {
					"stage" : "SORT_KEY_GENERATOR",
					"inputStage" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"tid" : 1,
								"c_type" : 1,
								"status" : 1,
								"ct" : -1,
								"topic_reply_lut" : -1
							},
							"indexName" : "tid_1_c_type_1_status_1_ct_-1_topic_reply_lut_-1",
							"isMultiKey" : false,
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 1,
							"direction" : "forward",
							"indexBounds" : {
								"tid" : [
									"[3.0, 3.0]"
								],
								"c_type" : [
									"[1.0, 1.0]"
								],
								"status" : [
									"[1.0, 1.0]",
									"[2.0, 2.0]",
									"[3.0, 3.0]"
								],
								"ct" : [
									"[inf.0, 1600000000.0]"
								],
								"topic_reply_lut" : [
									"[MaxKey, MinKey]"
								]
							}
						}
					}
				}
			}
		]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 10,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 12,
		"totalDocsExamined" : 10,
		"executionStages" : {
			"stage" : "LIMIT",
			"nReturned" : 10,
			"executionTimeMillisEstimate" : 0,
			"works" : 23,
			"advanced" : 10,
			"needTime" : 12,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"limitAmount" : 10,
			"inputStage" : {
				"stage" : "FETCH",
				"nReturned" : 10,
				"executionTimeMillisEstimate" : 0,
				"works" : 22,
				"advanced" : 10,
				"needTime" : 12,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 0,xQ
				"invalidates" : 0,
				"docsExamined" : 10,
				"alreadyHasObj" : 0,
				"inputStage" : {
					"stage" : "SORT_MERGE",
					"nReturned" : 10,
					"executionTimeMillisEstimate" : 0,
					"works" : 22,
					"advanced" : 10,
					"needTime" : 12,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 0,
					"invalidates" : 0,
					"sortPattern" : {
						"topic_reply_lut" : -1
					},
					"dupsTested" : 11,
					"dupsDropped" : 0,
					"inputStages" : [
						{
							"stage" : "IXSCAN",
							"nReturned" : 9,
							"executionTimeMillisEstimate" : 0,
							"works" : 9,
							"advanced" : 9,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 0,
							"invalidates" : 0,
							"keyPattern" : {
								"tid" : 1,
								"c_type" : 1,
								"status" : 1,
								"topic_reply_lut" : -1,
								"ct" : -1
							},
							"indexName" : "tid_1_c_type_1_status_1_topic_reply_lut_-1_ct_-1",
							"isMultiKey" : false,
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 1,
							"direction" : "forward",
							"indexBounds" : {
								"tid" : [
									"[3.0, 3.0]"
								],
								"c_type" : [
									"[1.0, 1.0]"
								],
								"status" : [
									"[1.0, 1.0]"
								],
								"topic_reply_lut" : [
									"[MaxKey, MinKey]"
								],
								"ct" : [
									"[inf.0, 1600000000.0]"
								]
							},
							"keysExamined" : 9,
							"dupsTested" : 0,
							"dupsDropped" : 0,
							"seenInvalidated" : 0
						},
						{
							"stage" : "IXSCAN",
							"nReturned" : 2,
							"executionTimeMillisEstimate" : 0,
							"works" : 2,
							"advanced" : 2,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 0,
							"invalidates" : 0,
							"keyPattern" : {
								"tid" : 1,
								"c_type" : 1,
								"status" : 1,
								"topic_reply_lut" : -1,
								"ct" : -1
							},
							"indexName" : "tid_1_c_type_1_status_1_topic_reply_lut_-1_ct_-1",
							"isMultiKey" : false,
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 1,
							"direction" : "forward",
							"indexBounds" : {
								"tid" : [
									"[3.0, 3.0]"
								],
								"c_type" : [
									"[1.0, 1.0]"
								],
								"status" : [
									"[2.0, 2.0]"
								],
								"topic_reply_lut" : [
									"[MaxKey, MinKey]"
								],
								"ct" : [
									"[inf.0, 1600000000.0]"
								]
							},
							"keysExamined" : 2,
							"dupsTested" : 0,
							"dupsDropped" : 0,
							"seenInvalidated" : 0
						},
						{
							"stage" : "IXSCAN",
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 0,
							"works" : 1,
							"advanced" : 0,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 1,
							"invalidates" : 0,
							"keyPattern" : {
								"tid" : 1,
								"c_type" : 1,
								"status" : 1,
								"topic_reply_lut" : -1,
								"ct" : -1
							},
							"indexName" : "tid_1_c_type_1_status_1_topic_reply_lut_-1_ct_-1",
							"isMultiKey" : false,
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 1,
							"direction" : "forward",
							"indexBounds" : {
								"tid" : [
									"[3.0, 3.0]"
								],
								"c_type" : [
									"[1.0, 1.0]"
								],
								"status" : [
									"[3.0, 3.0]"
								],
								"topic_reply_lut" : [
									"[MaxKey, MinKey]"
								],
								"ct" : [
									"[inf.0, 1600000000.0]"
								]
							},
							"keysExamined" : 1,
							"dupsTested" : 0,
							"dupsDropped" : 0,
							"seenInvalidated" : 0
						}
					]
				}
			}
		},
	},
	"serverInfo" : {
		"host" : "sgal-omg-test-db01",
		"port" : 27048,
		"version" : "3.2.7",
		"gitVersion" : "4249c1d2b5999ebbf1fdf3bc0e0e3b3ff5c0aaf2"
	},
	"ok" : 1
}

(tid,c_type,status,ct,topic_reply_lut)执行计划如下：

ost:PRIMARY> db.posts.find({tid:3, c_type:{$in:[1]}, status:{$in:[1,2,3]}, ct:{$gte:1600000000}}).sort({topic_reply_lut:-1}).skip(0).limit(10).hint({"tid":1,"c_type":1,"status":1,"ct":-1,"topic_reply_lut":-1}).explain(executionStats=true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "post.posts",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"tid" : {
						"$eq" : 3
					}
				},
				{
					"ct" : {
						"$gte" : 1600000000
					}
				},
				{
					"c_type" : {
						"$in" : [
							1
						]
					}
				},
				{
					"status" : {
						"$in" : [
							1,
							2,
							3
						]
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "SORT",
			"sortPattern" : {
				"topic_reply_lut" : -1
			},
			"limitAmount" : 10,
			"inputStage" : {
				"stage" : "SORT_KEY_GENERATOR",
				"inputStage" : {
					"stage" : "FETCH",
					"inputStage" : {
						"stage" : "IXSCAN",
						"keyPattern" : {
							"tid" : 1,
							"c_type" : 1,
							"status" : 1,
							"ct" : -1,
							"topic_reply_lut" : -1
						},
						"indexName" : "tid_1_c_type_1_status_1_ct_-1_topic_reply_lut_-1",
						"isMultiKey" : false,
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"tid" : [
								"[3.0, 3.0]"
							],
							"c_type" : [
								"[1.0, 1.0]"
							],
							"status" : [
								"[1.0, 1.0]",
								"[2.0, 2.0]",
								"[3.0, 3.0]"
							],
							"ct" : [
								"[inf.0, 1600000000.0]"
							],
							"topic_reply_lut" : [
								"[MaxKey, MinKey]"
							]
						}
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 10,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 50,
		"totalDocsExamined" : 47,
		"executionStages" : {
			"stage" : "SORT",
			"nReturned" : 10,
			"executionTimeMillisEstimate" : 0,
			"works" : 62,
			"advanced" : 10,
			"needTime" : 51,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"sortPattern" : {
				"topic_reply_lut" : -1
			},
			"memUsage" : 5574,
			"memLimit" : 33554432,
			"limitAmount" : 10,
			"inputStage" : {
				"stage" : "SORT_KEY_GENERATOR",
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"works" : 51,
				"advanced" : 0,
				"needTime" : 3,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "FETCH",
					"nReturned" : 47,
					"executionTimeMillisEstimate" : 0,
					"works" : 50,
					"advanced" : 47,
					"needTime" : 2,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"docsExamined" : 47,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 47,
						"executionTimeMillisEstimate" : 0,
						"works" : 50,
						"advanced" : 47,
						"needTime" : 2,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"keyPattern" : {
							"tid" : 1,
							"c_type" : 1,
							"status" : 1,
							"ct" : -1,
							"topic_reply_lut" : -1
						},
						"indexName" : "tid_1_c_type_1_status_1_ct_-1_topic_reply_lut_-1",
						"isMultiKey" : false,
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"tid" : [
								"[3.0, 3.0]"
							],
							"c_type" : [
								"[1.0, 1.0]"
							],
							"status" : [
								"[1.0, 1.0]",
								"[2.0, 2.0]",
								"[3.0, 3.0]"
							],
							"ct" : [
								"[inf.0, 1600000000.0]"
							],
							"topic_reply_lut" : [
								"[MaxKey, MinKey]"
							]
						},
						"keysExamined" : 50,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			}
		}
	},
	"serverInfo" : {
		"host" : "sgal-omg-test-db01",
		"port" : 27048,
		"version" : "3.2.7",
		"gitVersion" : "4249c1d2b5999ebbf1fdf3bc0e0e3b3ff5c0aaf2"
	},
	"ok" : 1
}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

dlian丶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值