环境
mongodb:3.4
Robomongo:1.0
前言
在没有建立索引的情况下,当查询排序请求,并且skip
值很大时,就会报如下错误:
Sort operation used more than the maximum 33554432 bytes of RAM.
意思是说,排序操作不能超过内存32M
。
解决方式大概2
种:
方法一、是把32M
的mongodb
限制进行调整,调大些。
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库时的经验,自己取的名称(叫法)。