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

环境

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库时的经验，自己取的名称（叫法）。