问题描述
线上下面sql查询响应时间,数据量200多万,执行耗时约2s
db.lngReportDocument.find({
"merchantId": xxxx
}).sort( { reportTime: -1 }).limit(1);
表结构:
db.createCollection("lngReportDocument");
db.getCollection("lngReportDocument").createIndex({
merchantId: NumberInt("1")
}, {
name: "idx_merchant_id",
background: true
});
db.getCollection("lngReportDocument").createIndex({
reportTime: NumberInt("1")
}, {
name: "idx_report_time",
background: true
});
分析
查询字段和排序字段都存在索引
执行计划看,走索引正常
db.lngReportDocument.find({
“merchantId”: xxxx
}).sort( { reportTime: -1 }).limit(1).explain();
执行下面查询语句, 走索引且毫秒级别返回
db.lngReportDocument.find().sort({ reportTime:1 }).limit(1).explain();
说明索引没问题
按MerchantId分组统计数据
如果走reportTime索引应该不会这么慢,那只有一种可能数据倾斜太严重,我们语句中要筛选的数据在索引树中间
查询
merchantId:A的reportTime范围为2023-08-08 10:25:44 到2023-09-26 12:59:34
merchantId:B的reportTime范围为2023-11-24 13:46:35 到2023-12-12 14:36:12
验证
– 按A和时间升序查询,数据倾斜在首部
db.lngReportDocument.sort({ reportTime:1 }).find({
“merchantId”: A
}).limit(1);
耗时: 毫秒级
按时间索引顺序筛选merchantId=A,在索引树前面几条就找到想要的数据,limit 1立刻返回
– 按A和时间倒序查询,数据倾斜在中间
db.lngReportDocument.sort({ reportTime:-1 }).find({
“merchantId”: A
}).limit(1);
耗时: 秒级
按时间索引顺序筛选merchantId=A,在索引树中间才找到想要的数据,经历太多次io和比对
– 按B和时间倒序查询,所需的数据倾斜在尾部
db.lngReportDocument.sort({ reportTime:-1 }).find({
“merchantId”: B
}).limit(1);
耗时: 毫秒级
按时间索引倒序筛选merchantId=A,在索引树尾部找到想要的数据 limit 1 立刻返回,经历太多次io和比对
– 按B和时间正序查询,数据倾斜在中间
db.lngReportDocument.sort({ reportTime:1 }).find({
“merchantId”: B
}).limit(1);
耗时: 秒级
按时间索引倒序筛选merchantId=A,在索引树中间才找到想要的数据,经历太多次io和比对
优化方案
方案一 修正数据
让数据按reportTime字段分布平均,可能是某种原因导致A的数据停止上报了
方案二 创建查询字段和排序字段的联合索引
让查询字段和排序字段联合组成索引树,这样查询的时候直接走索引返回,避免merchantId的比对
db.lngReportDocument.createIndex({ reportTime: -1 ,merchantId: 1});