mongodb 索引优化再优化【前缀大致相同】

环境

mongodb:3.4
Robomongo:1.0

前言

在没有建立索引的情况下,当查询排序请求,并且skip值很大时,就会报如下错误:

Sort operation used more than the maximum 33554432 bytes of RAM.

意思是说,排序操作不能超过内存32M

解决方式大概2种:
方法一、是把32Mmongodb限制进行调整,调大些。

db.adminCommand({setParameter:1, internalQueryExecMaxBlockingSortBytes:335544320})

方法二、建立索引。

这里我只谈第二种方法

因为方法一、是治标不治本

建索引

先来看看我的业务情况:

这里写图片描述

图片中三个排序字段afchange_ratio,afchange_amount,change_amount,的查询条件都是一样的;

最开始我建立的索引是这样的:

db.t_event_inc_api.ensureIndex({event_name:1,declare_date:1,ranking:1,afchange_ratio:-1}, {background:1})
db.t_event_inc_api.ensureIndex({event_name:1,declare_date:1,ranking:1,afchange_amount:-1}, {background:1})
db.t_event_inc_api.ensureIndex({event_name:1,declare_date:1,ranking:1,change_amount:-1}, {background:1})

代码里的查询语句是:

db.t_event_inc_api.find({declare_date:{$gte:new Date("2007/01/01"), $lte:new Date("2018/01/08")}, event_name:"增减持", ranking:1}).sort({afchange_ratio:-1}).skip(2429).limit(30)

之所以这样建立,是因为他们筛选条件都是一样的,只是后面的排序条件不一样。
结果呢:

不管我怎么查询,都走第一个索引。
也就是event_name_1_declare_date_1_ranking_1_afchange_ratio_-1这个索引。

想想也是,我上面创建的三个索引,前缀(前三个字段)都是一样的。

优化

既然是因为前缀相同造成的,我们就不能让它有那么多的相同前缀。

之后我创建如下索引:

db.t_event_inc_api.ensureIndex({afchange_ratio:-1,event_name:1,declare_date:1,ranking:1}, {background:1})
# 其他两个同理

虽然基本需求满足了:三个字段的排序都走自己的索引;但是这就是最优的吗?显然不是,因为把排序字段放在索引的最前面,mongodb将会先去排序,再去过滤筛选。

执行如下代码时,需要的时间大概为1秒:

db.t_event_inc_api.find({declare_date:{$gte:new Date("2007/01/01"), $lte:new Date("2018/01/08")}, event_name:"增减持", ranking:1}).sort({afchange_ratio:-1}).skip(2429).limit(30).explain()

再优化

后来我把索引又做了次调整:
这个是把一个筛选字段放最前面;这样就是先筛选再排序:

db.t_event_inc_api.ensureIndex({event_name:1,afchange_ratio:-1,declare_date:1,ranking:1}, {background:1})
# 其他两个同理

比对了下,这个更快些。

执行如下代码时,只需要0.176秒。

db.t_event_inc_api.find({declare_date:{$gte:new Date("2007/01/01"), $lte:new Date("2018/01/08")}, event_name:"增减持", ranking:1}).sort({afchange_ratio:-1}).skip(2429).limit(30).explain()

我也试过如下索引:

db.t_event_inc_api.ensureIndex({event_name:1,declare_date:1,afchange_ratio:-1,ranking:1}, {background:1})

这个索引慢,效果不如前面两个。

总结

当建索引时,如果遇到查询条件都一样,只是排序字段不一样时,我们需要注意前缀问题

建议第一个字段为筛选字段,第二个是你要排序的字段,其余放后面。
筛选字段(第一个字段)要特别注意,最好是放短语筛选,而不是范围筛选的字段。

比如:
event_name:"业绩线索",这就是短语筛选

declare_date:{$gte:new Date("2007/01/01"), $lte:new Date("2018/01/08")}
这个就是范围筛选

说明:

短语筛选和范围筛选,官网文档并没有这个说法,这个是我结合之前学习es库时的经验,自己取的名称(叫法)。

相关文章
相关标签/搜索