代码干货 | MongoDB查询优化：从 10s 到 10ms

来源：互联网发布：淘宝佛珠刀具价格大全编辑：程序博客网时间：2024/06/03 18:06

本文来源于阿里云-云栖社区，原文点击这里。

苏先生反馈线上某条查询很慢（10+ seconds），语句相当于

db.myColl.find({app:"my_app",requestTime:{$gte:1492502247000,$lt:1492588800000}}).sort({_id:-1}).limit(1)

myColl这个collection中的记录内容类似于：

{ "_id" : ObjectId("58fd895359cb8757d493ce60"), "app" : "my_app", "eventId" : 141761066, "requestTime" : NumberLong("1493010771753"), "scene" : "scene01" }{ "_id" : ObjectId("58fd895359cb8757d493ce52"), "app" : "my_app", "eventId" : 141761052, "requestTime" : NumberLong("1493010771528"), "scene" : "scene02" }{ "_id" : ObjectId("58fd895359cb8757d493ce36"), "app" : "my_app", "eventId" : 141761024, "requestTime" : NumberLong("1493010771348"), "scene" : "scene03" }{ "_id" : ObjectId("58fd895359cb8757d493ce31"), "app" : "my_app", "eventId" : 141761019, "requestTime" : NumberLong("1493010771303"), "scene" : "scene01" }{ "_id" : ObjectId("58fd895359cb8757d493ce2d"), "app" : "my_app", "eventId" : 141761015, "requestTime" : NumberLong("1493010771257"), "scene" : "scene01" }{ "_id" : ObjectId("58fd895259cb8757d493ce10"), "app" : "my_app", "eventId" : 141760986, "requestTime" : NumberLong("1493010770866"), "scene" : "scene01" }{ "_id" : ObjectId("58fd895259cb8757d493ce09"), "app" : "my_app", "eventId" : 141760979, "requestTime" : NumberLong("1493010770757"), "scene" : "scene01" }{ "_id" : ObjectId("58fd895259cb8757d493ce02"), "app" : "my_app", "eventId" : 141760972, "requestTime" : NumberLong("1493010770614"), "scene" : "scene03" }{ "_id" : ObjectId("58fd895259cb8757d493cdf1"), "app" : "my_app", "eventId" : 141760957, "requestTime" : NumberLong("1493010770342"), "scene" : "scene02" }{ "_id" : ObjectId("58fd895259cb8757d493cde6"), "app" : "my_app", "eventId" : 141760946, "requestTime" : NumberLong("1493010770258"), "scene" : "scene01" }

相关的索引有：

[    {        "v" : 1,        "key" : {            "_id" : 1        },        "name" : "_id_",        "ns" : "myDatabase.myColl"    },    {        "v" : 1,        "key" : {            "responseTime" : -1        },        "name" : "idx_responseTime_-1",        "ns" : "myDatabase.myColl"    },    {        "v" : 1,        "key" : {            "app" : 1,            "scene" : 1,            "eventId" : -1,            "requestTime" : -1        },        "name" : "idx_app_1_scene_1_eventId_-1_requestTime_-1",        "ns" : "myDatabase.myColl"    }]

慢查询就是在myColl中查找符合[1492502247000, 1492588800000)这个时间范围的所有记录，**以下描述中称这条查询为bad query**。

如果去掉$lt:1492588800000这个约束条件，查找[1492502247000, +∞)这个时间范围，就会很快（milliseconds）。

>>>展开全文

阅读全文

0 0